title: SQL Transaction with ADO.Net
body: <p>I am new to Database interection with C#, I am trying to writing 10000 records in database in a loop with the help of SqlCommand and SqlConnection objects with the help of SqlTransaction and committing after 5000. It is taking 10 seconds to processed.</p>
  
  <pre><code>SqlConnection myConnection = new SqlConnection("..Connection String..");
  myConnection.Open();
  SqlCommand myCommand = new SqlCommand();
  myCommand.CommandText = "exec StoredProcedureInsertOneRowInTable Param1, Param2........";
  myCommand.Connection = myConnection;
  SqlTransaction myTrans = myConnection.Begintransaction();
  for(int i=0;i&lt;10000;i++)
  {
  mycommand.ExecuteNonQuery(); 
  if(i%5000==0) 
  {
  myTrans.commit();  
  myTrans = myConnection.BeginTransaction();  
  mycommand.Transaction = myTrans;
  }
  }
  </code></pre>
  
  <p>Above code is giving me only 1000 rows write/sec in database.</p>
  
  <p>But when i tried to implement same logic in SQL and execute it on Database with SqlManagement Studio the it gave me 10000 write/sec.
  When I compare the behaviour of above two approch then it showes me that while executing with ADO.Net there is large number of Logical reads.</p>
  
  <p>my questions are:
  1. <strong>Why there is logical reads in ADO.Net execution?</strong>
  2. Is tansaction have some hand shaking?
  3. Why they are not available in case of management studio?
  4. If I want very fast insert transactions on DB then what will be the approach? .</p>
  
  <p><em><strong>Updated Information about Database objects</em></strong></p>
  
  <p><strong>Table:</strong> tbl_FastInsertTest
  No Primary Key, Only 5 fields first three are type of int (F1,F2,F3) and last 2(F4,F5) are type varchar(30)</p>
  
  <p><strong>storedprocedure:</strong> </p>
  
  <pre><code>create proc stp_FastInsertTest 
  { 
  @nF1 int,
   @nF2 int,
   @nF3 int,
   @sF4 varchar(30),
   @sF5 varchar(30) 
   }
   as  
   Begin
   set NoCOUNT on
         Insert into tbl_FastInsertTest
         {
           [F1],
           [F2],
           [F3],
           [F4],
           [F5]
         }
         Values
         {
           @nF1,
           @nF2,
           @nF3,
           @sF4,
           @sF5,
         } end
   --------------------------------------------------------------------------------------
  </code></pre>
  
  <h2>     <strong>SQL Block Executing on SSMS</strong></h2>
  
  <p>--When I am executing following code on SSMS then it is giving me more than 10000 writes per second but when i tried to  execute same STP on ADO than it gave me 1000 to 1200 writes per second</p>
  
  <h2>--while reading no locks</h2>
  
  <pre><code>begin trans 
  declare @i int 
  set @i=0
  
  While(1&lt;&gt;0) 
  begin  
   exec stp_FastInsertTest 1,2,3,'vikram','varma'  
   set @i=@i+1 
  
   if(@i=5000)   
    begin    
     commit trans   
     set @i=0
     begin trans
    end
  
   end
  </code></pre>