Structured Query Language = DDL + DML + DCL + DTL

Data Defination Language = Create, Alter, Drop

Data Manipulation Language = Insert, Delete, Update, Select

Data Control Language = Grant, Revoke

Data Transaction Language = Commit, Rollback, SavePoint

Managed Provider

It is a .Net component implementing certain standard set of interfaces provided by Microsoft, for a specific type of backend.

Following are the four managed providers which are build in along with MS.NET Framework.

• 1. System.Data.SqlClient Manage Provider for SqlServer

  2. System.Data.OracleClient– Manage Provider for Oracle

  3. System.Data.Oledb -Manage Provider- Can be used for any database which has Oledb provider

  4. System.Data.Odbc – Manage Provider for Odbc drivers – This can be used for all databases but it would be slow compared to a managed provider specific to a given database.

Imporatant Objects in Managed Provider:

Connection: Connection object encapsulates the functionaly of establishing a communication path over the sockets.

In .Net, Connections are ‘Pooled’. We can have by default upto 100 simultaneous connections without closing but obviously we have to close every connection Opened only then it can be reused from Pool i.e keeps the connection with the database open for the short period of time. The Connection Pooling can be customized by setting appropriate parameters in ConnectionString of Connection Object and this cannot be done for Oledb and ODBC managed providers.

Note: for two or more connection objects to share a common connection in the pool their connectionstrings must match,

Command: Command Object can be used for execution of any type of Sql Statement including Stored Procedures.

DataReader: When “Select” or similar type of sql statement is executed a new cursor (memory area in backend) is created on the back end. This Cursor contains primary key or indexed key of the table on which the statement is executed for the records which have qualified the condition in the where clause of the select statement. It also has a pointer, which is by default positioned at BOF.

The cursor and pointers functionality is encapsulated by the “DataReader” object created in the front end. Using this object, we can move the pointer in the forward direction getting one record from backend into the front-end at the time.

The cursor managed by Data Reader is read-only and forward-only

In VS.NET: To Create a new Database

Goto ServerExplorer  Right Click on Data Connections  Create New Sql Server Database  Server Name as .\SqlExpress  New Database name as MSNETDB

Create the following table in the database using Server Explorer

Emp (EmpId, EmpName, EmpSalary) table in backend.

EmpId – Int, PrimaryKey and also set Identity = True in Property of EmpId field

EmpName – Varchar(50) – AllowNull = False (Uncheck)

EmpSalary – Money – AllowNull = True (Check)

Right Click on EMP table and select Show Table Data  Enter some default data.

Connection string formats for SQL Server

1. “Data Source=.\sqlexpress;Initial Catalog=MSNETDemoDb;Integrated Security=True”

2. “Data Source=.\sqlexpress;Database= MSNETDemoDb; User Id=sa; Password=dss”

3. “Server=.\sqlexpress;Database= MSNETDemoDb;uid=sa;pwd=dss”

Note: Data Source or Server are same

Initial Catalog or Database are same

User Id or uid are same

Password or pwd are same

We should either give “Intergrated Security” or uid / pwd

The ConnectionString format depends on the managed provider used for connecting to database.

www.connectionstrings.com : to get connectionstring for all database.

Step 1. Add to the project a file by name App.config (Application Configuration File)

<configuration>

<connectionStrings>

<add name=”csMSNETDB” connectionString=”Server=.\sqlexpress;database=MSNETDB;Integrated Security=True”/>

</connectionStrings>

</configuration>

Step 2. Add Reference to System.Configuration (Project  Reference  .NET Tab  Select System.ConfigurationOK)

Step 3. Add to the project a class by name Helper (Helper.vb)

class Helper

{

        public static string ConnectionString

       {

        get {

              return      System.Configuration.ConfigurationManager.ConnectionStrings[“csMSNETDB“].ConectionString;

                 }

           }

}

Step 4: To Establish Connection

using System.Data.SqlClient

In button click:

SqlConnection con = new SqlConnection();

con.ConnectionString = Helper.ConnectionString;

try

{

                con.Open();

              //executing commands….

}

finally

{

                 if (con.State == ConnectionState.Open)

                con.Close();

}

//Steps to execute any SQL Statement

1. Create a Connection

2. Create a Command

3. Bind the Command to Connection

4. Initialize Command with SQL Statement to execute

5. Open the Connection

6. Execute the Command

7. Close the Connection

To Insert a record in the table (btnInsert_Click)

//1. Create a Connection

SqlConnection con = new SqlConnection(Helper.ConnectionString);

//2. Create a Command

SqlCommand cmd = new SqlCommand();

//3. Bind the Command to Connection

cmd.Connection = con;

//4. Initialize Command with SQL Statement to execute

string name = txtName.Text.Replace(“‘”,“””);

decimal salary = decimal.Parse(txtSalary.Text);

cmd.CommandText = “Insert into Emp(EmpName, EmpSalary) Values(‘” + name + “‘,” + salary + “)”;

//Note: In and SQL Statement the value of every Varchar field must be enclosed in single quotes. But if the value itself has single quote then it should be replaced with two single quotes.

MessageBox.Show(cmd.CommandText);

cmd.CommandType = CommandType.Text;

//5. Open the Connection

con.Open();

//6. Execute the Command

cmd.ExecuteNonQuery();

MessageBox.Show(“Inserted…”);

//7. To Fetch the last inserted EmpId

cmd.CommandText = “Select @@Identity“;**

txtID.Text = cmd.ExecuteScalar().ToString();

//8. Close the Connection

con.Close();

**Note: Over a given connection if an insert statement is executed on a table with Identity Column, the backend sql-server for that connection initializes a parameter by name “@@Identity” with the value of Identity column for the last inserted record.

For btnUpdate_Click:

cmd.CommandText = “Update Emp set EmpName='” + txtName.Text + “‘, EmpSalary=” + txtSalary.Text +

” where EmpId=” + txtId.Text

For btnDelete_Click

cmd.CommandText = “Delete from Emp where EmpId=” + txtId.Text;

SQL Command Execution Methods

1. int ExecuteNonQuery(): Used for execution of those statements which do not return any data from backend to frontend. – return.

2. object ExecuteScalar() – Used for execution of statements which return only one value.- return Object.

3. SqlDataReader ExecuteReader() – Used for execution of statements which return multiple rows and columns i.e. a set of records.

To Get all the records of the Employee table

private void btnGetAllEmps_Click(. . . { SqlConnection con = new SqlConnection(); con.ConnectionString = Helper.ConnectionString; SqlCommand cmd = new SqlCommand(“Select * from Emp”, con); cmd.CommandType = CommandType.Text; con.Open(); SqlDataReader dr = cmd.ExecuteReader(); string str = “”; while (dr.Read()) //Read return false when the pointer on the cursor has reached EOF { str += dr[0].ToString() + “\t”; int indName = dr.GetOrdinal(“EmpName”); str += dr.GetString(indName) +”\t”; int indSalary = dr.GetOrdinal(“EmpSalary”); if (dr.IsDBNull(indSalary)) str += “—-\n”; else str += dr.GetDecimal(indSalary) + “\n”; } dr.Close(); con.Close(); MessageBox.Show(str); }

Private Sub btnGetEmps_Click(. . .) Dim con As New SqlConnection con.ConnectionString = Helper.ConnectionString Dim cmd As New SqlCommand() cmd.Connection = con cmd.CommandType = CommandType.Text cmd.CommandText = “Select * from Emp” Dim dr As SqlDataReader con.Open() dr = cmd.ExecuteReader() Dim str As String = “” While (dr.Read()) str &= dr(0).ToString() & vbTab Dim indName As Integer indName = dr.GetOrdinal(“EmpName”) str &= dr.GetString(indName) & vbTab Dim indSalary As Integer indSalary = dr.GetOrdinal(“EmpSalary”) If (dr.IsDBNull(indSalary)) Then str &= vbCrLf Else str &= dr.GetDecimal(indSalary) & vbCrLf End If End While dr.Close() : con.Close() MsgBox(str) End Sub

Note: Its recommended to use the GetX (X is datatype for example GetInt32()) methods for fetching data from the datareader because here we don’t have to cast unlike in case of using indexed property which returns “Object” and has to be casted

Table: UserProfile(PKUserId, UserName, Password)

private void btnLogin_Click(object sender, EventArgs e)

{

SqlConnection con = new SqlConnection(Helper.ConnectionString);

SqlCommand cmd = new SqlCommand();

cmd.Connection = con;

cmd.CommandText = “Select PKUserId, Password From UserProfile where Username='” + txtUsername.Text + “‘”;

con.Open();

SqlDataReader dr = cmd.ExecuteReader();

if (!dr.Read() || dr[“Password”].ToString() != txtPassword.Text)

MessageBox.Show(“Invalid u/p”);

else.

MessageBox.Show(“Valid – UserId=” + dr[“PKUserId”]);

con.Close();

}

MARS (Important for Interviews)

By Default we can have only one data reader opened at a time on one connection object.

If MultipleActiveResultSets=True is added to the connection string then more than one data reader can be opened at the same time on the single connection object. It’s a feature in Ado.net 2.0

Note: The result of one select statement should not overlap with the result of another statement.

Department (DeptId, DeptName)

Employee (EmpId, EmpName, EmpSalary, DeptId);

Private Sub btnGetDeptEmp_Click(. . .) Handles btnDeptGetEmp.Click

Dim con As New SqlConnection

con.ConnectionString = “Data Source=.\sqlexpress;Initial Catalog=MSNETDemoDb;Integrated Security=TRUE;

MultipleActiveResultSets=True“

Dim cmdDept As New SqlCommand

cmdDept.CommandText = “Select * from Department”

cmdDept.CommandType = CommandType.Text

cmdDept.Connection = con

Dim drDept As SqlDataReader

con.Open()

drDept = cmdDept.ExecuteReader()

Dim str As String = “”

While (drDept.Read())

Dim cmdEmp As New SqlCommand

cmdEmp.CommandText = “Select * from Employee where Deptid=” & drDept(“DeptId”)

cmdEmp.CommandType = CommandType.Text

cmdEmp.Connection = con

Dim drEmp As SqlDataReader

drEmp = cmdEmp.ExecuteReader() //This line fails if MARS is not mentioned in connection string.

While (drEmp.Read())

str &= drDept(“DeptName”) & vbTab

str &= drEmp(“EmpId”) & vbTab

str &= drEmp(1) & vbTab

str &= drEmp.GetDecimal(2) & vbCrLf

End While

drEmp.Close()

End While

drDept.Close()

MsgBox(str)

con.Close()

End Sub

Prepared Statement

An Sql Statement on its first execution is compiled, optimized and an execution plan is created for it. If the statement is marked as prepared then for further usage this execution plan is cached by the backend, so that the same plan can be reused for the subsequent request for the same statement but different data.

Note: The prepared statement must be framed using the paramenters. But every parametized statement need not be prepared.

1. Create a Connection object and set its ConnectionString Property

2. Create a Command object and set its Connection, CommandType and CommandText Properties.

3. The CommandText must include place holders for the parameters

4. For every placeholder in the statement, create a Parameter Object.

5. Add all the parameters to the Parameters collection of command object.

6. Open the Connection

7. Prepare the execution plan using Prepare() method of command object.

8. Set the values for parameters and Execute the Command

9. Repeat step 8 for different values of parameters

10. Close the connection.

Class Emp

Public Name As String

Public Salary As Decimal

Public Sub New(ByVal name As String, ByVal salary As Decimal)

Me.Name = name

Me.Salary = salary

End Sub

End Class

Dim con As New SqlConnection(Helper.ConnectionString) Dim cmd As New SqlCommand cmd.Connection = con cmd.CommandType = CommandType.Text cmd.CommandText = “Insert into Emp(EmpName,EmpSalary) Values (@EmpName, @EmpSalary)“ Dim parEmpName As SqlParameter parEmpName = New SqlParameter(“@EmpName”, SqlDbType.VarChar, 50) Dim parEmpSalary As SqlParameter parEmpSalary = cmd.CreateParameter() parEmpSalary.ParameterName = “@EmpSalary” parEmpSalary.SqlDbType = SqlDbType.Money cmd.Parameters.Add(parEmpName) cmd.Parameters.Add(parEmpSalary) Dim lstEmps As New List(Of Emp) lstEmps.Add(New Emp(“DE1”, 10000)) lstEmps.Add(New Emp(“DE2”, 20000)) lstEmps.Add(New Emp(“DE3”, 30000)) lstEmps.Add(New Emp(“DE4”, 20000)) con.Open() cmd.Prepare() ‘Prepares the execution plan For Each emp As Emp In lstEmps parEmpName.Value = emp.Name parEmpSalary.Value = emp.Salary cmd.ExecuteNonQuery() ‘The prepared execution plan is used here… Next con.Close()

SqlConnection con = new SqlConnection(Helper.ConnectionString); SqlCommand cmd = new SqlCommand(); cmd.Connection = con; cmd.CommandText = “insert into Emp(EmpName,EmpSalary) Values(@Name,@Salary)”; SqlParameter parName = new SqlParameter(“@Name”, SqlDbType.VarChar, 50); SqlParameter parSalary = new SqlParameter(“@Salary”, SqlDbType.Money); cmd.Parameters.Add(parName); cmd.Parameters.Add(parSalary); List<Emp> lstEmps = new List<Emp>(); lstEmps.Add(new Emp(“A1”, 10000)); lstEmps.Add(new Emp(“A2”, 20000)); lstEmps.Add(new Emp(“A3”, 30000)); lstEmps.Add(new Emp(“A4”, 40000)); lstEmps.Add(new Emp(“A5”, 50000)); con.Open(); cmd.Prepare();‘Prepares the execution plan foreach (Emp emp in lstEmps) { parName.Value = emp.Name; parSalary.Value = emp.Salary; cmd.ExecuteNonQuery();‘The prepared execution plan is used here… } con.Close();

1. In Sql Server the parameters must begin with @, In Oracle just the name is sufficient but for OleDb and Odbc Providers we need to use “?” as place holder for all the parameters and while adding the parameters a proper order must be maintained.

2. If the SQL Statement has to be executed only once, don’t mark is Prepared because the backend takes extra time for preparation of execution plan and this would become overhead the plan is not reused.

3. If the parameter is of type varchar or string and if the value of it has single quote, don’t replace that single quote with two single quotes (unlike in a simple statement with data directly embed into it).

Stored Procedures

It is a precompiled set of sql statement which are compiled in native form and stored in the backend.

Advantages:

1. They are faster in execution because they are precompiled and stored in backend in native form of that backend.

2. Reduces network traffic because they are executed in backend the data used by them is also in backend.

3. Its easy to update logic/code in them because its stored only at one place i.e in database.

Sequence of Steps for Executing the Stored Procedure in front end application

1. Create a Connection object and set its ConnectionString Property.

2. Create a Command object and set its Connection, CommandType and CommandText Properties.

3. For every parameter in the prepared statement / stored procedure, create a Parameter Object and set its ParemeterName, DataType, Size and Direction properties.

4. Add the Parameters to the Parameters collection of Command Object.

5. Set the value for all Input Parameters.

6. Open the Connection

7. Execute the Command using the appropriate Execute Method.

8. Close the Connection

9. Retrieve the value from output parameters.

CREATE PROCEDURE GetSalary(@Id int,@Sal money output ) AS

Begin

Select @Sal=EmpSalary from Emp where EmpId=@Id

End

Note: In SQL Server every OUTPUT parameter is also treated as IN parameter.

Example 1: Code to Execute a SP – GetSalary – (IN VB.NET)

SqlConnection con = new SqlConnection(Helper.ConnectionString);

SqlCommand cmd = new SqlCommand(“GetSalary“, con); //GetSalary is name of SP

cmd.CommandType = CommandType.StoredProcedure;

SqlParameter parId = cmd.Parameters.Add(“@Id”, SqlDbType.Int); //Name and Type of Parameter must be same as in SP

SqlParameter parSalary = cmd.Parameters.Add(“@Sal”, SqlDbType.Money);

parSalary.Direction = ParameterDirection.Output;

parId.Value = txtId.Text;

con.Open();

cmd.ExecuteNonQuery();

con.Close();

if (parSalary.Value == DBNull.Value)

txtSalary.Text = “”;

else

txtSalary.Text = parSalary.Value.ToString();

In C#

SqlConnection con = new SqlConnection(Helper.ConnectionString);

SqlCommand cmd = new SqlCommand();

cmd.Connection = con;

cmd.CommandText = “GetSalary“;

cmd.CommandType = CommandType.StoredProcedure;

SqlParameter parId, parSalary;

parId = new SqlParameter(“@Id”, SqlDbType.Int);

parSalary = new SqlParameter(“@Sal”, SqlDbType.Money);

parSalary.Direction = ParameterDirection.Output;

cmd.Parameters.Add(parId);

cmd.Parameters.Add(parSalary);

parId.Value = int.Parse(txtID.Text);

con.Open();

cmd.ExecuteNonQuery();

con.Close();

if (parSalary.Value == DBNull.Value)

txtSalary.Text = “”;

else

txtSalary.Text = parSalary.Value.ToString();

2^nd Example: To execute the Stored Procedure – InsertEmp:

CREATE PROCEDURE InsertEmp(@Id int output, @Name varchar(50), @Salary money) AS

Begin

Insert into Emp(EmpName, EmpSalary) values (@Name,@Salary)

Select @Id = @@Identity

End

SqlConnection con = new SqlConnection(Helper.ConnectionString);

SqlCommand cmd = new SqlCommand(“InsertEmp”, con);

cmd.CommandType = CommandType.StoredProcedure;

SqlParameter parId, parName, parSalary;

parId = cmd.Parameters.Add(“@Id”, SqlDbType.Int);

parId.Direction = ParameterDirection.Output;

parName = cmd.Parameters.Add(“@name”, SqlDbType.VarChar, 50);

parSalary = cmd.Parameters.Add(“@salary”, SqlDbType.Money);

parName.Value = txtName.Text;

parSalary.Value = txtSalary.Text;

con.Open();

cmd.ExecuteNonQuery();

con.Close();

txtId.Text = parId.Value.ToString();

Create Procedure GetEmployees AS

Begin

Select Count(*) from Emp

Select EmpId, EmpName, EmpSalary from Emp

End

3^rd Example: Code to Execute the SP – GetEmployees .

Dim con As New SqlConnection(“Data Source=.\sqlexpress;Initial Catalog=MSDemoDb;Integrated Security=TRUE”)

Dim cmd As New SqlCommand

cmd.CommandText = “GetEmployees“

cmd.CommandType = CommandType.StoredProcedure

cmd.Connection = con

con.Open()

Dim dr as SqlDataReader = cmd.ExecuteReader()

dr.Read() ‘Moves to the only record in the first cursor created by execution of the stored procedure.

MsgBox(dr(0)) ‘Shows the total number of records in the Emp table.- i.e result of “Select Count(*) from Emp”

dr.NextResult() ‘Moves the Pointer to the second cursor. i.e “Select EmpId,EmpName,EmpSalary from Emp”

Dim str As String = “”

While (dr.Read())

str &= dr(“EmpId”) & vbTab

str &= dr(1) & vbTab

Dim ind As Integer

ind = dr.GetOrdinal(“EmpSalary”) ‘Return the Index of the column EmpSalary

If (dr.IsDBNull(ind)) Then

str &= “–” & vbCrLf

Else

str &= dr.GetDecimal(ind) & vbCrLf

End If

End While

MsgBox(str)

dr.Close()

con.Close()

End Sub

Note: If required cmd.CommandText can have more than one sql statement separated by “;”. This feature may not be supported by a all databases.

Transaction

Its a group of Sql Statements to be executed as one unit to modify the state of database. Backend is responsible for managing the transactions.

To bind all the Command objects to a single Transaction do the following

1. On the Connection object begin the transaction (con.BeginTransaction), this returns the reference to the Transaction object (trans) encapsulating transaction created in the backend.

2. All the Command objects, Transaction Property must be set to the above Transaction object.

3. In Try block execute all the Commands

4. If the exception is not thrown the last statement of the try must Commit (trans.Commit())the transaction, but if the exception is thrown the Catch must execute Rollback (trans.Rollback()) on the transaction object.

Private Sub btnTransaction_Click(. . .) Handles btnTransaction.Click

Dim con As New SqlConnection(“Data Source=.\sqlexpress;Initial Catalog=MSNetDemoDb;Integrated Security=TRUE”)

Dim cmd1, cmd2 As SqlCommand

cmd1 = New SqlCommand()

cmd2 = New SqlCommand()

cmd1.Connection = con

cmd2.Connection = con

cmd1.CommandText = “Insert into Emp(EmpName,EmpSalary) Values(‘A1’,10000)”

cmd2.CommandText = “Update Emp set EmpSalary = EmpSalary = 1000” //has error…

//cmd2.CommandText = “Update Emp set EmpSalary = EmpSalary + 1000” //without error

con.Open()

Dim trans As SqlTransaction

trans = con.BeginTransaction() ‘Starts a new transaction over the connection

cmd1.Transaction = trans ‘All the commands belonging to the transaction MUST have their Transaction property set.

cmd2.Transaction = trans

Try

cmd1.ExecuteNonQuery()

cmd2.ExecuteNonQuery()

trans.Commit() ‘ If no exception is thrown the transaction is commited.

Catch ex As Exception

MsgBox(ex.Message)

trans.Rollback() ‘If exception is thrown the transaction is rolledback.

End Try

con.Close()

End Sub

For Practicing:

1. Execute the above code as it is.

2. Note that because of error in upadate statement, transaction will rollback and the new record will not existing in Emp table.

3. Execute the above code after replacing “=” with “+” in the Update statement.

4. Note that the transaction is commited and a record is inserted in Emp table and also the salary is updated.

Note: Once a transaction has begin over a connection, a command without its transaction property set, cannot be executed on that connection unless either the transaction is committed or rolledback.

Managing Transaction in ADO.NET 2.0

1. Add reference to System.Transactions (Project  Add Reference)

2. On top of file mention “using System.Transactions” (CS) or “Imports System.Transactions” (VB).

3. Enclose the block of code for execution of all commands in “using” block of TransactionScope object as below

SqlConnection con = new SqlConnection(Helper.ConnectionString);

SqlCommand cmd1, cmd2;

cmd1 = new SqlCommand();

cmd2 = new SqlCommand();

cmd1.Connection = cmd2.Connection = con;

cmd1.CommandText = “Insert into Emp(EmpName,EmpSalary) Values(‘A1’,10000)”;

cmd2.CommandText = “Update Emp set EmpSalary = EmpSalary = 1000”;

//cmd2.CommandText = “Update Emp set EmpSalary = EmpSalary + 1000”;

using (TransactionScope ts = new TransactionScope())

{

con.Open(); //Connection must be opened inside the using block only

cmd1.ExecuteNonQuery();

cmd2.ExecuteNonQuery();

ts.Complete(); //If not executed automatically the transaction is rolledback

con.Close();

}

Advantage of using this over con.BeginTransactin

TransactionScope can encapsulate Distributed Transactions (while using more than one database) i.e. within TransactionScope all the commands executed fall under one transaction irrespective of their Connection (which can be same or different)

Async Execution of Command (Sql Statement)

Dim dr As SqlDataReader

Dim cmd As New SqlCommand

Dim ar As IAsyncResult = cmd.BeginExecuteReader() ‘Begins the Asynchronous call

MessageBox.Show(“Continue….”)

If (ar.IsCompleted) Then ‘Check if the return value is available.

dr = cmd.EndExecuteReader(ar) ‘Ends the Asynchronous call and provides the return value.

End If

Between BeginX and EndX methods frontend can execute code which is not dependent on the result of Sql Statement.

How Generic code can be written – Example for execution of Stored Procedure.

Class DBUtil

Public Shared Function ExecuteSPNonQuery(ByVal spName As String, ByVal ParamArray params() As SqlParameter)

As Integer

Dim con As New SqlConnection(Helper.ConnectionString)

Dim cmd As New SqlCommand(spName, con)

cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.AddRange(params)

Try

con.Open()

Return cmd.ExecuteNonQuery()

Finally

con.Close()

End Try

End Function

Public Shared Function ExecuteSPDataReader(ByVal spName As String,

ByVal ParamArray params() As SqlParameter) As SqlDataReader

Dim con As New SqlConnection(Helper.ConnectionString)

Dim cmd As New SqlCommand(spName, con)

cmd.CommandType = CommandType.StoredProcedure

For Each p As SqlParameter In params

cmd.Parameters.Add(p)

Next

con.Open()

Return cmd.ExecuteReader(CommandBehavior.CloseConnection)

Because CommandBehavior.CloseConnection is used as parameter for ExecureReader the Connection will be automatically closed when the DataReader returned to the calling method is closed.

End Function

End Class

Private Sub btnExecuteSP_Click(. . .) Handles btnExecuteSP.Click

Dim cs As String = “server=.\sqlexpress;database=msnet;integrated security=true”

Dim spName As String = “GetSalary”

Dim pId As SqlParameter = New SqlParameter(“@Id”, SqlDbType.Int)

pId.Direction = ParameterDirection.Input

Dim pSalary As SqlParameter = New SqlParameter(“@sal”, SqlDbType.Money)

pSalary.Direction = ParameterDirection.Output

pId.Value = CInt(txtId.Text)

DBUtil.ExecuteSPNonQuery(cs, spName, pId, pSalary)

If (Not IsDBNull(pSalary.Value)) Then txtSalary.Text = pSalary.Value

End Sub

Using Factory class for writing Provider Independent code. (only in 2.0)

private void btnSelectEmployees_Click(object sender, EventArgs e)

{

System.Data.Common.DbProviderFactory fac;

fac = System.Data.Common.DbProviderFactories.GetFactory(“System.Data.SqlClient”);

IDbConnection con = fac.CreateConnection();

con.ConnectionString = Helper.ConnectionString;

IDbCommand cmd = fac.CreateCommand();

cmd.CommandText = “Select * from Emp”;

cmd.Connection = con;

con.Open();

IDataReader dr = cmd.ExecuteReader();

string str = “”;

while (dr.Read())

{

int indEmpId = dr.GetOrdinal(“EmpId”);

str += dr.GetInt32(indEmpId) + “\t”;

int indEmpName = dr.GetOrdinal(“EmpName”);

str += dr.GetString(indEmpName) + “\t”;

int indEmpSalary = dr.GetOrdinal(“EmpSalary”);

if (dr.IsDBNull(indEmpSalary))

str += “\n”;

else

str += dr.GetDecimal(indEmpSalary) + “\n”;

}

dr.Close();

con.Close();

MessageBox.Show(str);

}

Important Members of SqlConnection:

Properties: ConnectionString

Methods: Open, Close, BeginTransaction

Important Members of SqlCommand:

Properties: Connection, CommandType, CommandText, Parameters

Methods: ExecuteNonQuery, ExecuteScalar, ExecuteReader, CreateParameter, Prepare

Important Members of SqlDataReader:

Properties: IndexProperty – [ ]

Methods: Read, GetOrdinal, GetInt32, GetString…..GetX (X is datatype), IsDbNull,Close

Important Members of SqlParameter:

Properties: Value, ParameterName, SqlDbType, Size, Direction

Methods:

Important Members of SqlTransaction:

Properties:

Methods: Commit, Rollback