reusing connection and command object

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

reusing connection and command object

Author

Topic

turk
Starting Member

5 Posts

Posted - 2004-09-07 : 09:20:49

I have written the following code, and it works, but I can't believe that there isn't a better way to do this than to close the connection and reopen it. I would appreciate any input.

Thanks.

string strConnection = ConfigurationSettings.AppSettings["strSqlConn"];
SqlConnection objConnection = new SqlConnection(strConnection);
string strQuery = "select WBS from actionActionItem where WBS is not null order by WBS";
SqlCommand objCommand = new SqlCommand(strQuery,objConnection);

//populate WBS box with WBS numbers from actionActionItem
objConnection.Open();
ddlWBS.DataSource = objCommand.ExecuteReader();
ddlWBS.DataTextField = "WBS";
ddlWBS.DataBind();
objCommand.Connection.Close();

//populate developer box with names
objConnection.Open();
strQuery = "select MemberName, TeamMemberID from adminTeamMember where (ReportsToTeamMemberID is not null and EndDate is null) or TeamMemberID = 5 order by MemberName";
objCommand.CommandText = strQuery;
ddlDeveloperName.DataSource = objCommand.ExecuteReader();
ddlDeveloperName.DataTextField = "MemberName";
ddlDeveloperName.DataValueField = "TeamMemberID";
ddlDeveloperName.DataBind();

objConnection.Close();

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-07 : 09:51:27

that is because you're using DataReader. DataReader open a connection and holds it open until you close it.
so u could close it at the end.

other way is to use DataAdapter which does this automaticaly.

Go with the flow & have fun! Else fight the flow

turk
Starting Member

5 Posts

Posted - 2004-09-07 : 10:41:38

quote:
Originally posted by spirit1

that is because you're using DataReader. DataReader open a connection and holds it open until you close it.
so u could close it at the end.

other way is to use DataAdapter which does this automaticaly.

I know that this is a stupid questions, but how do I close the DataReader without closing the command object or connection object?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-07 : 11:05:14

SqlDataReader.Close()

you can open a conn at the begining, do the stuff with it and then close it at the end.
2 datareaders can't be open at the same time.

look up SqlDataReader and SqlConnection in MSDN.

Go with the flow & have fun! Else fight the flow

samuelhon
Starting Member

5 Posts

Posted - 2004-09-07 : 12:01:35

Hi

What you could do (and probably should) is use a stored proc and pop both queries in there. Then you can use one DataReader and use NextResult to switch between the select statements

Sam

turk
Starting Member

5 Posts

Posted - 2004-09-07 : 13:25:56

quote:
Originally posted by spirit1

SqlDataReader.Close()

you can open a conn at the begining, do the stuff with it and then close it at the end.
2 datareaders can't be open at the same time.

look up SqlDataReader and SqlConnection in MSDN.

Go with the flow & have fun! Else fight the flow

I appreciate your sticking with me here. I understand that I need to close the DataReader. My problem is that since I didn't explicity declare and call a DataReader, what syntax do I use to close it?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-07 : 14:08:02

this is the way to do it properly. that way you don't have to open and close the conn.


SqlDataReader myReader = myCommand.ExecuteReader();
ddl.DataSource = myReader;
myReader.Close();

Go with the flow & have fun! Else fight the flow

turk
Starting Member

5 Posts

Posted - 2004-09-07 : 14:31:07

quote:
Originally posted by spirit1

this is the way to do it properly. that way you don't have to open and close the conn.

SqlDataReader myReader = myCommand.ExecuteReader();
ddl.DataSource = myReader;
myReader.Close();

Go with the flow & have fun! Else fight the flow

Great. Thanks for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources