Re-use of SqlConnection and SqlCommand ?

Hi,

When using the following controls...

System.Data.SqlClient.SqlConnection
System.Data.SqlClient.SqlCommand

If I want to change my SQL command and execute the query once again what cleanup do I need to do first?

Do I need close and dispose the SqlConnection?Do I need to dispose the SqlCommand?

Can I use the SqlConnection for more than one SqlCommand?

Thanks,
Scott

you can use sqlconnection over and over. the rule on dispose, is if the class implements idisposable, you should call dispose. I believe both of these do, so you should. I would suggest us the "using" syntax and then idispose will be called for you and you don't have to worry about it.

|||Thanks.

Forgive my ignorance, what is the 'using' syntax?
I would google it, but 'using' is too generic of a word to get anything useful.

Here is some sample code of mine...

System.Data.SqlClient.SqlConnection con_dupProjectId =new System.Data.SqlClient.SqlConnection(); con_dupProjectId.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["RIIDBudget"].ToString(); con_dupProjectId.Open(); System.Data.SqlClient.SqlCommand cmd_dupProjectId =new System.Data.SqlClient.SqlCommand(); cmd_dupProjectId.Connection = con_dupProjectId; cmd_dupProjectId.CommandText ="select * from tblEORSummary where project_id in " +"(select project_id from tblEORSummary group by project_id having count(project_id) >1) " +"order by project_id"; System.Data.SqlClient.SqlDataReader rdr_dupProjectId = cmd_dupProjectId.ExecuteReader();

|||

Sorry, not a C# expert, but it's something like this:

using (System.Data.SqlClient.SqlConnection con_dupProjectId =new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["RIIDBudget"].ToString()))

{
con_dupProjectId.Open();
using (System.Data.SqlClient.SqlCommand cmd_dupProjectId =new System.Data.SqlClient.SqlCommand())

{
cmd_dupProjectId.Connection = con_dupProjectId;
cmd_dupProjectId.CommandText ="select * from tblEORSummary where project_id in " +
"(select project_id from tblEORSummary group by project_id having count(project_id) >1) " +
"order by project_id";
System.Data.SqlClient.SqlDataReader rdr_dupProjectId = cmd_dupProjectId.ExecuteReader();

}

}

|||

Thanks, back to the original question...

I tried the following...

System.Data.SqlClient.SqlConnection con_RIID =new System.Data.SqlClient.SqlConnection();
 con_RIID.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["RIIDBudget"].ToString();
 con_RIID.Open();
 System.Data.SqlClient.SqlCommand cmd_dupProjectId =new System.Data.SqlClient.SqlCommand();
 cmd_dupProjectId.Connection = con_RIID;
 cmd_dupProjectId.CommandText ="select * from tblEORSummary1025 where project_id in " +
"(select project_id from tblEORSummary1025 group by project_id having count(project_id) >1) " +
"order by project_id";
 System.Data.SqlClient.SqlDataReader rdr_dupProjectId = cmd_dupProjectId.ExecuteReader();
int prev_project_id = -1;
int project_id = 0;
while (rdr_dupProjectId.Read())
 {
 project_id = (int)rdr_dupProjectId["project_id"];
if (prev_project_id == project_id)// this way we skip the first record
 {
string current_fiscal_year = (string)rdr_dupProjectId["current_fiscal_year"];
int external_system_id = (int)rdr_dupProjectId["external_system_id"];
int eor_summary_id = (int)rdr_dupProjectId["eor_summary_id"];
// 2) Creat new record in project_main with the new project_id
 System.Data.SqlClient.SqlCommand cmd_insertProjectMain =new System.Data.SqlClient.SqlCommand();
 cmd_insertProjectMain.Connection = con_RIID;
 cmd_insertProjectMain.CommandText ="insert project_main1025 (long_title, short_title, pi_name, task_area, riid_plan_number, plan_number, principal_investigator, co_principal_investigator, " +
"pi_institute, pi_division, new_start, program_element, apc_id, pi_office, pi_phone, pi_lab_room, bsl_level_required, active_animal_protocol, " +
"fiscal_year, project_type, project_source, division, external_system_id, person_id, editable) " +
"select long_title, short_title, pi_name, task_area, riid_plan_number, plan_number, principal_investigator, co_principal_investigator, " +
"pi_institute, pi_division, 0, program_element, apc_id, pi_office, pi_phone, pi_lab_room, bsl_level_required, active_animal_protocol, " +
"'" + current_fiscal_year +"', " +
"project_type, project_source, division, external_system_id, person_id, editable " +
"from project_main1025 " +
"where project_id =" + project_id;
 cmd_insertProjectMain.ExecuteNonQuery();
 ...

First time inside the 'if' clause I get an error...

"There is already an open DataReader associated with this Command which must be closed first."

The error occurs on the last line of the code I have posted.

|||

You can not have a datareader open on a connection, and then try to use a command that uses that same connection (It's tied up with the datareader).

Either read everything you want from the datareader, then close the datareader, then iterate through your data

or

Use a dataset instead of a datareader and iterate through that

or

Declare, initialize, and open a 2nd connection object, and associate the 2nd command with that connection instead.