This step-by-step article shows you how to use ADO.NET 2005
or ADO.NET to connect to a database and to run a command.
following list outlines the recommended hardware, software, network
infrastructure, and service packs that you need:
Microsoft Windows Server 2003, Microsoft Windows 2000
Professional, Windows 2000 Server, Windows 2000 Advanced Server, or Microsoft
Windows NT 4.0 Server
Microsoft Visual Studio 2005 or Microsoft Visual Studio
article assumes that you are familiar with the following topics:
Structured Query Language (SQL)
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
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:
Follow these steps to create a new console application in
Microsoft Visual C# 2005 or in Microsoft Visual C# .NET:
Start Microsoft Visual Studio 2005 or Microsoft Visual
On the File menu, point to New, and then click Project.
In the New Project dialog box, click Visual C# Projects under Project Types, and then click Console Application under Templates.
Note In Visual Studio 2005, click Visual C# under Project Types in the New Project dialog box, and then click Console Application under Templates.
Make sure that your project contains a reference to the System.Data namespace, and add a reference if it does not.
Use the using statement on the System and System.Data namespaces so that you do not have to qualify declarations in
those namespaces later in your code. You can also include System.Data.SqlClient or System.Data.OleDb, depending on which one you are using.
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. For example, the following
connection string points to a local computer that is running SQL
For OleDb connections:
Note User ID <UID> must have appropriate permissions to perform
these operations on the database.
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);
Create a SqlCommand or 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:
After you create the SqlCommand or 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, UPDATE, and INSERT statements). If the statement runs without
throwing an exception (see the following code), the command has been executed
successfully against the database.
Save your project. On the Debug menu, click Start to run your command against the database.
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 than you can replace with different values each time you run the
To use parameters with your command, follow these steps:
Create your OleDbConnection or SqlConnection object, as you did in the "How to
run a command" section.
Replace 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.
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
For each parameter, add a parameter object to the command
object's parameters collection. For each parameter, you must specify a name and