Page 98 - DCAP312_WEB_TECHNOLOGIES_II

Web Technologies-II

Notes For OleDb connections:
Provider=SQLOLEDB.1;User ID=<UID>;Initial Catalog=pubs;Data Source=(local)
For SqlClient connections:
User ID=<UID>;Initial Catalog=pubs;Data Source=(local)
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.
class Class1
{
static void Main(string[] args)
{
string sConnectionString =
“User ID=<UID>;Initial Catalog=pubs;Data Source=(local)”;
}
}
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();

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:
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 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.
objCmd.ExecuteNonQuery();

5.3.1 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:
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 command.

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.

92 LOVELY PROFESSIONAL UNIVERSITY