How to Lock a Database table through c# code?

Question

Hi I have a database in SQLSERVER with some columns and it is meant for multiple access.Now i don't want a user to interrupt the database if some other user is modifying that database.I want to write the code through c# ans not through SQL. please help me out.

tomcy thomas · Answer

I tested in .Net 2008 3.5 C# sql server 2000 and its running fine.. add the namespace using System.Data.SqlClient; private void Transaction() { string ConString = Data Source=Server1;Initial Catalog=pubs;User ID=sa;Password=sa;; SqlConnection con = new SqlConnection(ConString); con.Open();//need to open connection first SqlCommand cmd = con.CreateCommand() ;//command from Connection SqlTransaction tr = con.BeginTransaction(IsolationLevel.Serializable MyTrans); cmd.CommandText = insert into AgeTable ([Name]age) values ('tom'99); cmd.Connection = con; cmd.Transaction = tr; cmd.ExecuteNonQuery(); tr.Commit(); con.Close(); }

Master Billa · Answer

HI You need use the Transaction property private void Trans() { SqlTransaction tn = null; try { SqlConnection con = new SqlConnection(con string); SqlCommand cmd = new SqlCommand(); cmd.CommandText = query; cmd.Connection = con; con.Open(); //create a transaction tn = con.BeginTransaction(IsolationLevel.Serializable); cmd.ExecuteNonQuery(); //if success commit the transaction tn.Commit(); } catch { if (tn != null) { //if error occurs rollback the transaction tn.Rollback(); } } } Thank you

Nir Bar · Answer

You should work with SqlTransaction class (in System.Data.SqlClient namespace) (Pseudo) code should be like: SqlConnection connection = new SqlConnection(connectionString) connection.Open(); SqlCommand command = connection.CreateCommand(); SqlTransaction transaction; // Start a local transaction. transaction = connection.BeginTransaction( SampleTransaction ); // Must assign both transaction object and connection // to Command object for a pending local transaction command.Connection = connection; command.Transaction = transaction; command.CommandText = Insert into Region (RegionID RegionDescription) VALUES (100 'Description') ; command.ExecuteNonQuery(); command.CommandText = Insert into Region (RegionID RegionDescription) VALUES (101 'Description') ; command.ExecuteNonQuery(); // Attempt to commit the transaction. transaction.Commit(); Console.WriteLine( Both records are written to database. ); Please check Accpeted Answer if this post helps you Nir