HOW TO: Connect to a Database and Run a Command by Using ADO.NET and Visual J# .NET (322045)

SUMMARY

This article describes how to use ADO.NET to connect to a database and to run a command (such as the UPDATE, the INSERT or the DELETE command) by using a Visual J# .NET console application.

back to the top

Requirements

The following list outlines the recommended hardware, software, network infrastructure, and service packs that are required:

• Microsoft Windows 2000 Professional, Windows 2000 Server, Windows 2000 Advanced Server, or Windows NT 4.0 Server
• Microsoft Visual Studio .NET
• Microsoft Visual J# .NET

This article assumes that you are familiar with the following topics:

• Database terminology
• SQL

back to the top

How to Run a Command

Commands are issued against databases to take actions against data stores and to include any statement that can be issued against a database. You can use the OleDbCommand or the SqlCommand classes to get a command to your data store, and OleDbCommand can be specific to the data store. This article demonstrates both the SqlClient class (to connect to a computer that is running Microsoft SQL Server) and the OleDb class (for any database that has an OLE DB or ODBC driver available) within ADO.NET. However, the code is generally the same for both.

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

To run a command, follow these steps:

1. Follow these steps to create a new console application in Visual J# .NET:
   1. Start Visual Studio .NET.
   2. On the File menu, point to New, and then click Project.
   3. In the New Project dialog box, click Visual J# Projects under Project Types, and then click Console Application under Templates.
2. Make sure that your project contains a reference to the System.Data namespace, and add a reference if it does not.
3. Use the import statement on the System.Data.SqlClient and the System.Data.OleDb namespaces so that you do not have to qualify declarations in those namespaces later in your code.

   import System.Data.SqlClient.*;
   import System.Data.OleDb.*;
   import System.Data.*;
   					

4. Before you can create a connection to a database, you must have a connection string. Connection strings contain all of the information that you need to establish a database connection, including the server name, the database name, the user ID, and the password. Avoid using the built-in SA account. For example, the following connection string points to a local computer that is running SQL Server with an account <username> and a strong password <strong password>:
   
   For OleDb connections:

   Provider=SQLOLEDB.1;User ID=<username>;Password=<strong password>;Initial Catalog=pubs;Data Source=(local)
   						

   For SqlClient connections:

   User ID=<username>;Initial Catalog=pubs;Data Source=(local)
   						

   NOTE: If you need more assistance determining the connection string for your database, search for "ConnectionString" in the Microsoft Developer Network (MSDN) Library at the following Microsoft Web site:

   http://search.microsoft.com/us/dev/default.asp

5. Visual Studio creates a static class and an empty Main() procedure. Declare a string variable, and store the appropriate connection string for your database in this procedure:

   public static void main(String[] args)
   	{
   		// 
   		// TODO: Add code to start application here
   		// 
   		String sConnectionString = "User ID=<username>;Initial Catalog=pubs;Data Source=mysource1;Password=<strong password>";
   		
   	}
   					

6. Using this connection string, create a new OleDbConnection or SqlConnection object, and call its Open method to establish a connection to your database:

   SqlConnection objConn = new SqlConnection(sConnectionString);
   objConn.Open(); 
   					

7. Create a SqlCommand or an OleDbCommand object, and pass in the command that you want to run and the connection object that you created in the previous step. The following sample code passes in the INSERT statement:

   String sSQL = "INSERT INTO Employee " + "(emp_id, fname, minit, lname, job_id, job_lvl,pub_id, hire_date) " + "VALUES ('MSD12923F', 'Duncan', 'W', 'Mackenzie',10,82,'0877','2001-01-01')";
   SqlCommand objCmd = new SqlCommand(sSQL,objConn); 
   					

8. After you create the SqlCommand or the OleDbCommand object, you can call the ExecuteNonQuery method to run the command that it represents. ExecuteNonQuery is designed for commands that do not return any results (such as the DELETE, the UPDATE, and the INSERT statements). If the Execute statement runs without throwing an exception (see the following code), the command has been executed successfully against the database.

   objCmd.ExecuteNonQuery();
   					

9. Modify the connection string as appropriate for your environment, and then save your project. On the Debug menu, click Start to run your command against the database.

back to the top

How to Use Parameters

When you run commands against a database (such as the UPDATE, the INSERT, and the DELETE statements or calls to stored procedures), these commands are frequently parameterized. This allows the command to be created one time but executed multiple times with different values that are inserted instead of parameters. Consider the corresponding DELETE statement to the INSERT statement that is used in the previous section:

String sSQL  = "DELETE FROM Employee WHERE emp_id = @emp_id";
				

The parameter name ("@emp_id") in this DELETE statement represents a parameter that you can replace with different values each time you run the command.

To use parameters with your command, follow these steps:

1. Create your OleDbConnection or SqlConnection object, as you did in the "How to Run a Command" section.
2. Replace the values with placeholders (for example, "@emp_id" or "@fname") so that your command text uses parameters. See the DELETE statement before these steps for an example.
3. Create your OleDbCommand or SqlCommand object, and pass in the connection object that you created in the first step and the command text that contains the parameter placeholders.
4. Get the command object's parameter collection:

   SqlParameterCollection ParamColl = objCmd.get_Parameters();
   					

5. For each parameter, add a parameter object to the command object's parameters collection. For each parameter, you must specify a name and data type.

   ParamColl.Add("@emp_id",SqlDbType.Char,9);
   					

6. Stored procedures can have parameters that return values and output parameters. You must also set a value for each input parameter before you can run the query:

   ParamColl.get_Item("@emp_id").set_Value((System.String)"MSD12923F");
   					

7. Run the query as follows:

   try 
   {
   	objCmd.ExecuteNonQuery();
   }
   catch (System.Exception e) 
   {
   	Console.WriteLine(e.Message);
   }
   Console.WriteLine("Record Deleted");
   					

back to the top

Complete Code Listing

package ConsoleApplication1;

import System.Data.SqlClient.*;
import System.Data.OleDb.*;
import System.Data.SqlDbType;

//    Summary description for Class1.
public class Class1
{
	public Class1()
	{
		// 
		// TODO: Add constructor logic here.
		// 
	}

	public static void main(String[] args)
	{
		// 
		// TODO: Add code to start application here.
		// 
		AddRecord();
		RemoveRecord();
		Pause();
	}
	static void Pause()
	{
		System.Console.WriteLine("Press Enter To Continue....");
		System.Console.Read();
	}
	static void AddRecord()
	{
		try 
		{
		String sConnectionString = "User ID=<username>;Initial Catalog=pubs;Data Source=(local);Password=<strong password>";
		SqlConnection objConn = new SqlConnection(sConnectionString);
		objConn.Open();
		String sSQL = "INSERT INTO Employee " + 
			"(emp_id, fname, minit, lname, job_id, job_lvl, pub_id, hire_date) " + 
			"VALUES ('MSD12923F', 'Duncan', 'W', 'Mackenzie', 10, 82,'0877','2001-01-01')";

		SqlCommand objCmd = new SqlCommand(sSQL,objConn);

			objCmd.ExecuteNonQuery();
		}
		catch(SqlException ex)
		{
		System.Console.WriteLine(ex.get_Message());
		}
		catch (System.Exception e) 
		{
			System.Console.WriteLine(e.get_Message());
		}
		System.Console.WriteLine("Record Added");
		//System.Console.Read();
	}
	static void RemoveRecord()
	{
		try 
		{
		String sConnectionString = "User ID=<username>;Initial Catalog=pubs;Data Source=(local);Password=<strong password>";
		SqlConnection objConn = new SqlConnection(sConnectionString);
		objConn.Open();
		String sSQL = "DELETE FROM Employee WHERE emp_id = @emp_id";
		SqlCommand objCmd = new SqlCommand(sSQL,objConn);
		SqlParameterCollection ParamColl = objCmd.get_Parameters();
		ParamColl.Add("@emp_id", SqlDbType.Char,9);
		ParamColl.get_Item("@emp_id").set_Value((System.String)"MSD12923F");   

    

			objCmd.ExecuteNonQuery();
		}
		catch(SqlException ex)
		{
			System.Console.WriteLine(ex.get_Message());
		}
		catch (System.Exception e) 
		{
			System.Console.WriteLine(e.get_Message());
		}
		
		System.Console.WriteLine("Record Deleted");
		
	}
}