Microsoft KB Archive/317559

Knowledge Base

HOW TO: Reuse the SqlCommand and OleDbCommand Objects in Visual C# .NET

Article ID: 317559

Article Last Modified on 9/3/2003

---

APPLIES TO

• Microsoft ADO.NET 1.1
• Microsoft ADO.NET 1.0
• Microsoft Visual C# .NET 2003 Standard Edition
• Microsoft Visual C# .NET 2002 Standard Edition

---

This article was previously published under Q317559

For a Microsoft Visual Basic .NET version of this article, see 317150.

This article refers to the following Microsoft .NET Framework Class Library namespaces:

• System.Data
• System.Data.SqlClient
• System.Data.OleDb

IN THIS TASK

SUMMARY

• Description of the Technique
• Steps to Reuse the SqlCommand Object
• Troubleshooting

REFERENCES

SUMMARY

This step-by-step article describes how to reuse the SqlCommand object in your Visual C# .NET code.

back to the top

Description of the Technique

You can reuse Command objects (SqlCommand or OleDbCommand) in code. That is, you can create one Command object and then run different commands on that object.

Commands are issued against databases to take actions against data stores. Commands include any statement that can be issued against a database. You can use the OleDbCommand or the SqlCommand class to get a command for your data store.

In Microsoft ActiveX Data Objects (ADO), you can issue commands through the Command, the Connection, or the Recordset object. In Microsoft ADO.NET, only the Command objects (SqlCommand or OleDbCommand) run commands.

back to the top

Steps to Reuse a SqlCommand Object

1. Create a new Windows application in Visual C# .NET as follows:
   1. Start Microsoft Visual Studio .NET.
   2. On the File menu, point to New, and then click Project.
   3. In the New Project dialog box, click Visual C# Projects under Project Types, and then click Windows Application under Templates. Form1 is added to the project by default.
2. Verify that your project contains a reference to the System.Data namespace. If it does not, add a reference to this namespace.
3. Drag a Button control from the toolbox to Form1. Change the Name property of the button to btnTest.

4. Use the using statement on the System and the System.Data namespaces so that you are not required to qualify declarations in those namespaces later in your code. Add the following code to the "General Declarations" section of Form1:

   using System;
   using System.Data;
   using System.Data.SqlClient;
                       

5. Copy and paste the following code in the btnTest_Click event:

   try
   {
      string myConnString = "User ID=myUID;password=myPWD;Initial Catalog=pubs;Data Source=mySQLServer";
       string mySelectQuery = "SELECT * FROM Titles";
       SqlConnection myConnection = new SqlConnection(myConnString);
       SqlCommand myCommand = new SqlCommand(mySelectQuery, myConnection);
       myConnection.Open();
       SqlDataReader myReader = myCommand.ExecuteReader();
       while (myReader.Read())
       {
           //Process data.
       }
       myReader.Close(); //Close the reader. 
   
       myCommand.CommandText = "SELECT * FROM Sales";
       SqlDataReader myReader1 = myCommand.ExecuteReader();
       while (myReader1.Read())
       {
           //Process data.
       }
       myReader1.Close(); //Close the reader. 
   
       String myInsertQuery = "INSERT INTO Employee " +
             "(emp_id, fname, minit, lname, job_id, job_lvl, pub_id, hire_date)" +
             "VALUES ('MSD12923F', 'JP', 'W', 'Mackenzie', " +
             "10, 82,'0877','2001-01-01')";
       myCommand.CommandText = myInsertQuery;
       myCommand.ExecuteNonQuery();
   
       //You can use the command any number of times.
   
       myConnection.Close();
   }
   catch (Exception ex)
   { 
       MessageBox.Show(ex.ToString());
   }
                       

6. Modify the connection string (myConnString) as appropriate for your environment.
7. Save your project. On the Debug menu, click Start to run your project.

back to the top

Troubleshooting

• While the SqlDataReader object is in use, the associated SqlConnection object serves the SqlDataReader, and you cannot perform any other operations on the SqlConnection object other than to close it. This is true until you call the Close method of the SqlDataReader object. This means, for example, that you cannot retrieve output parameters until after you call Close.For additional information about how to handle output parameters, click the article number below to view the article in the Microsoft Knowledge Base:

  308621 PRB: Output Parameters Are Not Returned When You Run an ADO.NET Command in Visual C# .NET

• If the method that runs a SqlCommand generates a SqlException exception, the SqlConnection remains open if the severity level is 19 or less. If the severity level is 20 or greater, the server usually closes the SqlConnection. However, you can reopen the connection and continue.

back to the top

REFERENCES

For additional information, click the article numbers below to view the articles in the Microsoft Knowledge Base:

313590 INFO: Roadmap for ADO.NET

313480 INFO: Roadmap for .NET Data Providers

311274 HOW TO: Handle Multiple Results by Using the DataReader in Visual C# .NET

306636 HOW TO: Connect to a Database and Run a Command by Using ADO.NET and Visual C# .NET

back to the top

Keywords: kbhowtomaster kbsqlclient kbsystemdata KB317559

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.