too much for stored procedure execution

  • Thread starter Thread starter Keith O
  • Start date Start date
K

Keith O

Hi,

I'm trying to write efficient C# code to execute a stored procedure.

The stored procedure takes in 5 arguments. I'm doing a foreach loop to write
a lot, and I mean a lot of data to the DB.

Is the following the most efficient and best way to write tons and tons of
data into the DB using a stored procedure that required 5 arguments?
Remember the code below is called numerous times.




foreach (MyClass obj in CollectionOfObjects)
{
try
{
SqlCommand cmd = new SqlCommand("MyStoredProcedure",sqlcon);
cmd.CommandType = CommandType.StoredProcedure;

SqlParameter param;

param = cmd.Parameters.Add("@first_name", SqlDbType.VarChar);
param.Value = obj.first_name;

param = cmd.Parameters.Add("@last_name", SqlDbType.VarChar);
param.Value = obj.last_name;

param = cmd.Parameters.Add("@a", SqlDbType.Int);
param.Value = obj.a;

param = cmd.Parameters.Add("@b", SqlDbType.Int);
param.Value = obj.b;

param = cmd.Parameters.Add("@c", SqlDbType.Int);
param.Value = obj.c;

cmd.ExecuteNonQuery();
}
catch {}
}



Thanks
 
Hi,
I'm trying to write efficient C# code to execute a stored procedure.

The stored procedure takes in 5 arguments. I'm doing a foreach loop to
write a lot, and I mean a lot of data to the DB.

Is the following the most efficient and best way to write tons and
tons of data into the DB using a stored procedure that required 5
arguments? Remember the code below is called numerous times.

foreach (MyClass obj in CollectionOfObjects)
{
try
{
SqlCommand cmd = new SqlCommand("MyStoredProcedure",sqlcon);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param;

param = cmd.Parameters.Add("@first_name", SqlDbType.VarChar);
param.Value = obj.first_name;
param = cmd.Parameters.Add("@last_name", SqlDbType.VarChar);
param.Value = obj.last_name;
param = cmd.Parameters.Add("@a", SqlDbType.Int);
param.Value = obj.a;
param = cmd.Parameters.Add("@b", SqlDbType.Int);
param.Value = obj.b;
param = cmd.Parameters.Add("@c", SqlDbType.Int);
param.Value = obj.c;
cmd.ExecuteNonQuery();
}
catch {}
}
Click to expand...




I'd reuse everything:
SqlCommand cmd = new SqlCommand("MyStoredProcedure",sqlcon);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param;
cmd.Parameters.Add("@first_name", SqlDbType.VarChar);
cmd.Parameters.Add("@last_name", SqlDbType.VarChar);
cmd.Parameters.Add("@a", SqlDbType.Int);
cmd.Parameters.Add("@b", SqlDbType.Int);
cmd.Parameters.Add("@c", SqlDbType.Int);
Click to expand...
foreach (MyClass obj in CollectionOfObjects)
{
try
{

cmd.Parameters("@first_name").Value = obj.first_name;
cmd.Parameters("@last_name").Value = obj.last_name;
cmd.Parameters("@a").Value = obj.a;
cmd.Parameters("@b").Value = obj.b;
cmd.Parameters("@c").Value = obj.c;
cmd.ExecuteNonQuery();
}
catch {}
}
Click to expand...

Otherwise, if you need more performance, roll everything into an xml document,
pass that into the db and handle it there.
 
You want fast - this is fast!

/* Bulk insert into work table */

string sql = "BULK INSERT " + _workTbl + " FROM '" + fiTXT.FullName + "' " +

"WITH ( FIELDTERMINATOR = '|', ROWTERMINATOR = '\\n' )";

int rowsAffected = ExecuteSQLSrvr(new SqlCommand(sql));
 
XML != Fast

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Back
Top