The release number of this article has been CHS306636
For Microsoft Visual Basic .NET versions of this article, see
CHS301075.
This task content
summary
Require how to run commands How to use parameters complete code list reference
SUMMARY This article describes how to connect to the database using the ActiveX Data Object (ADO) .NET and run the command.
Back to top
Requirements The recommended hardware, software, network structure, and service pack required:
Microsoft Windows 2000 Professional, Windows 2000 Server, Windows 2000 Advanced Server or Windows NT 4.0 Server Microsoft Visual Studio .NET This article assumes that you are familiar with the following topics:
Database term structured query language (SQL)
Back to top
How to run commands to issue a command to perform operations on the data store. Commands include any statements that can be emitted to the database. be usable
OLEDBCOMMAND or
The SQLCommand class gets commands to the backend data storage area.
OLEDBCommand can be a data store. This article covers the ADO.NET
SQLCLIENT class (for connecting to SQL Server) and
OLEDB class (for any database with OLE DB or ODBC available driver), but in general, the code of these two classes is the same.
Use ADO to pass
Command,
CONNECTION or
The Recordset object issued a command. In ADO.NET, only
Command object (
SQLCOMMAND or
OLEDBCOMMAND "Run command.
To run the command, follow these steps:
Open Visual Studio .NET. Create a console application in the C #. Make sure the project contains references to System.Data namespaces, if not, add a reference. Use the USING statement to System and System.Data namespace, so that you don't need to limit the declarations in these namespaces in the code. It can also include system.data.sqlclient or system.data.oledb, depending on which one you want to use. Using system;
Using system.data;
Using system.data.sqlclient; requires a connection string before creating a connection to the database. The connection string contains all information required to establish a database connection, including server name, database name, user ID, and password. For example, the following connection string points to the local SQL Server, the account is "sa", the password is empty: for the OLEDB connection: provider = sqloledb.1; user id = sa; initial catalog = public; data source = (local) For SQLClient connections : User ID = sa; initial catalog = pubs; data source = (local) Note: If you need more help when you determine the connection string of the database, search "Connectionstring" in the MSDN library, the URL is: http: / /Search.microsoft.com/us/dev/default.asp Visual Studio will create a static class and an empty main () process. During this process, declare a string variable and store the corresponding connection string of the database: Class class1 {
Static void main (string [] args)
{
String sconnectionstring =
"User ID = sa; initial catalog = public; data source = (local)";
}
} Use this connection string, create new OLEDBCONNECTION or SQLCONNECTION objects, and call it Open method to establish a connection with the database: SqlConnection Objconn = New SqlConnection; SCONNECTION
Objconn.Open (); create a SQLCommand or OLEDBCommand object and passed into the command to run and the connection object created in the previous step. The following sample code will be incorporated into 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); after creating a SQLCOMMAND or OLEDBCOMMAND object, the ExecuteNonQuery method can be called to run the command it represent. EXECUTENONQUERY is designed for commands that do not return any results (such as DELETE, UPDATE, and INSERT statements). If the statement does not trigger any exceptions at runtime, the command has been successfully executed for the database. Objcmd.executenonQuery (); Save Project. From the debug menu, click Start and then run the command.
Back to top
How to use parameters run commands in the database (such as UPDATE, INSERT, and DELETE statements or calls to stored procedures), these commands are typically parametric, which allows only one command to be created, and can be implemented by inserting different values. Multiple execution. Consider the delete statement corresponding to the INSERT statement used above: String SSQL = "Delete from Employee Where EMP_ID = @EMP_ID" This delete statement ("@emp_id") represents a parameter, each runs the command This parameter can be replaced with different values.
To use parameters in the command, follow these steps:
Create an OLEDBCONNECTION or SQLCONNECTION object like you in the first series of steps. Replace the value to placeholders (for example, "@EMP_ID" or "@fname") so that the command text uses parameters. For example, see the delete statement above. Create an OLEDBCOMMAND or SQLCOMMAND object and passed into the connection object created in step 1 and command text with parameter placeholders. For each parameter, add a parameter object in the parameter collection of the command object. For each parameter, you must specify name and data type: objcmd.parameters.add ("@ EMP_ID", SqldbType.char, 9); stored procedures can have some parameters, these parameters return value and output parameters. Before running the query, you must also set the value for each input parameter: objcmd.parameters ["@ EMP_ID"]. Value = "msd12923f"; run inquiry as follows: TRY
{
Objcmd.executenonquery ();
}
Catch (System.exception E)
{
Console.writeLine (E.MESSAGE);
}
Console.WriteLine ("Record deleted");
Back to top
Complete code list
Using system;
Using system.data;
Using system.data.sqlclient;
///
/// summary description for class1.
///
Class class1
{
Static void main (string [] args)
{
AddRecord ();
REMOVERECORD ();
Pause ();
}
Static void pause ()
{
Console.writeline ("Press Enter to Continue ....");
Console.readline ();
}
static void addrecord ()
{
String sconnectionstring = "User ID = sa; initial catalog = public; data source = (local)";
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);
Try
{
Objcmd.executenonquery ();
}
Catch (System.exception E)
{
Console.writeLine (E.MESSAGE);
}
Console.writeline ("Record Added");
}
Static void transoveRecord ()
{
String sconnectionstring = "User ID = sa; initial catalog = public; data source = (local)";
SqlConnection Objconn = New SqlConnection; SconnectionsTRING
Objconn.open ();
String ssql = "delete from Employee WHERE EMP_ID = @emp_id";
SQLCommand Objcmd = New Sqlcommand (SSQL, Objconn);
Objcmd.parameters.add ("@ Emp_ID", SqldbType.char, 9);
Objcmd.Parameters ["@ Emp_ID"]. value = "msd12923f";
Try
{
Objcmd.executenonquery ();
}
Catch (System.exception E)
{
Console.writeLine (E.MESSAGE);
}
Console.WriteLine ("Record deleted");
}
}
Back to top
Refer to more information about using the ADO.NET, the database command, and stored procedures, please visit the following Microsoft Web site:
SQL Server 2000 stored procedure http://msdn.microsoft.com/library/psdk/sql/cm_8_des_07_31vb.htm "In-depth discussion" MSDN Voice Column http://msdn.microsoft.com/voices/data.asp ado Ado.nethttp://msdn.microsoft.com/library/techart/adonetdev.htm msdn online .net developer center http://msdn.microsoft.com/net
Back to top
The information in this article applies to:
Microsoft Visual C # .NET (2002)
Reserved: 2002-2-21 (1.0) Keyword kbhowto kbhowtomaster kbnokeyword KB306636 KBAUDDEVELOPER
