Handling deadlocks in ado net

In this video we will discuss how to handle deadlock errors in an ADO.NET application.

Text version of the video
http://csharp-video-tutorials.blogspot.com/2015/08/handling-deadlocks-in-adonet.html

Slides
http://csharp-video-tutorials.blogspot.com/2015/08/handling-deadlocks-in-adonet_31.html

All SQL Server Text Articles
http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html

All SQL Server Slides
http://csharp-video-tutorials.blogspot.com/p/sql-server.html

All Dot Net and SQL Server Tutorials in English
https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd

All Dot Net and SQL Server Tutorials in Arabic
https://www.youtube.com/c/KudvenkatArabic/playlists

To handle deadlock errors in ADO.NET
1. Catch the SqlException object
2. Check if the error is deadlock error using the Number property of the SqlException object

Stored Procedure 1 Code
Alter procedure spTransaction1
as
Begin
Begin Tran
Update TableA Set Name = ‘Mark Transaction 1′ where Id = 1
Waitfor delay ’00:00:05’
Update TableB Set Name = ‘Mary Transaction 1’ where Id = 1
Commit Transaction
End

Stored Procedure 2 Code
Alter procedure spTransaction2
as
Begin
Begin Tran
Update TableB Set Name = ‘Mark Transaction 2′ where Id = 1
Waitfor delay ’00:00:05’
Update TableA Set Name = ‘Mary Transaction 2’ where Id = 1
Commit Transaction
End

WebForm1.aspx.cs code

namespace Demo
{
public partial class WebForm1 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{}

protected void Button1_Click(object sender, EventArgs e)
{
try
{
string cs = ConfigurationManager.ConnectionStrings[“DBCS”].ConnectionString;
using (SqlConnection con = new SqlConnection(cs))
{
SqlCommand cmd = new SqlCommand(“spTransaction1”, con);
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
cmd.ExecuteNonQuery();
Label1.Text = “Transaction successful”;
Label1.ForeColor = System.Drawing.Color.Green;
}
}
catch (SqlException ex)
{
if (ex.Number == 1205)
{
Label1.Text = “Deadlock. Please retry”;
}
else
{
Label1.Text = ex.Message;
}
Label1.ForeColor = System.Drawing.Color.Red;
}
}
}
}

WebForm2.aspx.cs code
namespace Demo
{
public partial class WebForm1 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{}

protected void Button1_Click(object sender, EventArgs e)
{
try
{
string cs = ConfigurationManager.ConnectionStrings[“DBCS”].ConnectionString;
using (SqlConnection con = new SqlConnection(cs))
{
SqlCommand cmd = new SqlCommand(“spTransaction2”, con);
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
cmd.ExecuteNonQuery();
Label1.Text = “Transaction successful”;
Label1.ForeColor = System.Drawing.Color.Green;
}
}
catch (SqlException ex)
{
if (ex.Number == 1205)
{
Label1.Text = “Deadlock. Please retry”;
}
else
{
Label1.Text = ex.Message;
}
Label1.ForeColor = System.Drawing.Color.Red;
}
}
}
}

Link for all dot net and sql server video tutorial playlists
https://www.youtube.com/user/kudvenkat/playlists?sort=dd&view=1

Link for slides, code samples and text version of the video
http://csharp-video-tutorials.blogspot.com/2015/08/handling-deadlocks-in-adonet.html