Inserting into a table  
Author Message
LuckyL





PostPosted: Visual C# General, Inserting into a table Top

Hi

I'm using Microsoft SQL 2005. When i write to the table from C# i would ususaly do something like

String SQLStr="INSERT INTO [Test].[dbo].[DHACT] ([GLAccountCode] ,[GLAccountName] ,[AccountType] ,[AccountOrder] ,[Father],[AccountBalance],[Heading]) " +

"VALUES " +

"('"+ txtAddAcctCode.Text +"' " +

",'"+txtAddAccName.Text + "'" +

",1" +

",2" +

",'" + cmbAddActType.Text + "'" +

",0.00" +

",'Y')";

SqlCommand cmd = new SqlCommand(SQLStr, conn);

cmd.ExecuteNonQuery();

 

what i would like to know is the following:

  •  if i have one where i have multiple insert statements, but one insert statement fails. How would one rollback
  • Also, is there a more safe way to write the above insert statement
  • When using ado.net does one need to worry about database locking  

Thanks in advance 




Visual C#19  
 
 
boban.s





PostPosted: Visual C# General, Inserting into a table Top

1. Yes, using transactions. You need to execute all commands in one transaction and if any fails you will execute rollback command or if all succed, commit command. You can do this either using ADO.NET SqlTransaction object or from .NET 2.0 you can use also TransactionScope from System.Transactions namespace. For your case first one will be better.
2. Yes, if you use command parameters instead of building Values string.
3. If you like to worry about locking when doing sql operations, you can use SqlTransaction and when creating instance from connection object using BeginTransactio you can Specify IsolationLevel, or if you use TransactionScope you can use TransactionOptions to define IsolationLevel.

 
 
LuckyL





PostPosted: Visual C# General, Inserting into a table Top

Hi

Thanks for the response.

On the following

1) I like your answer and assume you mean this is the best way.

SqlCommand cmd = conn.CreateCommand(); //new SqlCommand(SQLStr, conn);

SqlTransaction transaction;

// Start a local transaction.

transaction = conn.BeginTransaction("InsertRecord");

cmd.Connection = conn;

cmd.Transaction = transaction;

try

{

cmd.CommandText = SQLStr;

cmd.ExecuteNonQuery();

transaction.Commit();

}

catch (Exception ex)

{

transaction.Rollback();

}

2) Not sure what you mean, can you provide a sample

3) Can you give me some info on what IsolationLevel does Maybe a code sample

Thanks in advance



 
 
boban.s





PostPosted: Visual C# General, Inserting into a table Top

Yes that is the way of using SqlTransaction. But mostly is used when you are executing more then one command that you want commands to be connected (either all executes or none of them). For that you set to all of the commands the Transaction property to be the created SqlTransaction for connection. You can set IsolationLevel when creating transaction. Default isolation level is defined on your Sql Server Instance and by default is ReadCommited. If you want to change that you can use some other isolation level:

SqlTransation tr = conn.BeginTransaction(IsolationLevel. , "InsertRecord"); or
SqlTransation tr = conn.BeginTransaction(IsolationLevel. );

Isolation level can be: ReadUncommited(lowest), ReadCommited (default for SQL server), RepeatableRead (high isolation), Serializable(highest), also from 2005 you can use Snapshot(which is on row based).