too much for stored procedure execution

[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

 

[Ryan Trudelle-Schwarz]

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 {}
}

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);

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 {}
}

Otherwise, if you need more performance, roll everything into an xml document,
pass that into the db and handle it there.

 

[Jim Rand]

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));

 

[William \(Bill\) Vaughn]

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.
__________________________________