Back to the top

Requirements

• 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

• Database terminology
• SQL

Back to the top

How to Run a Command

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://msdn.microsoft.com/en-us/support/default.aspx (http://msdn.microsoft.com/en-us/support/default.aspx)
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

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

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

Back to the top