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 make by default 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.
Command: Command Object can be used for execution of any type of Sql Statement.
DataReader: When “Select” or similar type of sql statement is executed a new cursor (memory are 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
Goto ServerExplorer Æ Right Click on Data Connections Æ Create New Sql Server Database Æ Server Name as .\SqlExpress Æ New Database name as MSNET
Create the following table in that database.
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
Right Click on EMP table and select Show Table Data Æ Enter some default data.
• Note: On top of file mention Imports System.Data.SqlClient To Execute Insert / Delete / Update Statements
Deccansoft Software Services – MS.NET ADO.NET
Dim con As New SqlConnection
con.ConnectionString = "Data Source=.\sqlexpress;Initial Catalog=MSNET;Integrated Security=True"
‘con.ConnectionString = "Data Source=.\sqlexpress;Database=MSNET;User id=sa;Password=dss"
‘con.ConnectionString = "Server=.\sqlexpress;Database=MSNET;uid=sa;pwd=dss"
Dim cmd As New SqlCommand
cmd.CommandText = "insert into Emp(EmpName,EmpSalary)
values('" & txtName.Text.Replace("'", "''") & "'," & txtSalary.Text & ")"
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.
'cmd.CommandText = "Delete from Emp where empid=" & txtId.Text
'cmd.CommandText = "Update Emp Set EmpName = ‘” & txtName.Text.Replace("'", "''") &
“’, EmpSalary=” & txtSalary.Text & “where EmpId=" & txtId.Text cmd.CommandType = CommandType.Text
cmd.Connection = con Try
con.Open()
MsgBox(cmd.ExecuteNonQuery()) ‘Used for execution of those Statements which do not return any data.
cmd.CommandText = "Select @@Identity" ‘Used for fetching the last set Identity column value on the current con.
MsgBox(“Last Inserted EmpId as set by backend “ + cmd.ExecuteScalar) Finally
If (con.State = ConnectionState.Open) Then con.Close() End Try
End Sub
Private Sub btnSelect_Click(. . .) Handles btnSelect.Click Dim con As New SqlConnection
con.ConnectionString = "Data Source=.\sqlexpress;Initial Catalog=MSNET1;Integrated Security=TRUE"
Dim cmd As New SqlCommand
'cmd.CommandText = "select empname from emp where empid=1"
cmd.CommandText = "select max(empid) from emp"
cmd.CommandType = CommandType.Text cmd.Connection = con
con.Open()
MsgBox(cmd.ExecuteScalar) ‘Used for execution of statements which return only one value.
con.Close() End Sub
Private Sub btnGetEmp_Click(. . .) Handles btnGetemp.Click Dim dr As SqlDataReader
dr = GetEmployees() Dim str As String = ""
While (dr.Read()) 'Read return false if EOF is reached.
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() 'Here is the connection is Closed
'con.Close() 'This is not required if CommandBehavior.CloseConnection is mentioned when the statement is executed.
End Sub
Private Function GetEmployees() As SqlDataReader Dim con As New SqlConnection()
con.ConnectionString = "Data Source=.\sqlexpress;Initial Catalog=MSNETDemoDb;Integrated Security=TRUE"
Dim cmd As New SqlCommand
cmd.CommandText = "select EmpId,EmpName,EmpSalary from Emp"
cmd.CommandType = CommandType.Text cmd.Connection = con
Deccansoft Software Services – MS.NET ADO.NET
Dim dr As SqlDataReader con.Open()
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection Or CommandBehavior.SingleRow)
'If the CommandBehavior.CloseConnection is mentioned the Connection is automatically closed when the data reader is closed.
Return dr End Function MARS
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 new 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);
Note: One Department can have more than one Employee.
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() 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 catched by the backend so that the same plan can be reused for the subsequent request for the same statement but different values for parameters.
Note: The prepared statement must be framed using the paramenters.
Note: 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.
Private Sub btnInsParam_Click(. . .) Handles btnInsParam.Click
Dim con As New SqlConnection("Data Source=.\sqlexpress;Initial Catalog=MSNETDemoDb;Integrated Security=TRUE") Dim cmd As New SqlCommand
cmd.CommandText = "Insert into emp(EmpName,EmpSalary) values(@EmpName,@EmpSalary)"
cmd.CommandType = CommandType.Text
Deccansoft Software Services – MS.NET ADO.NET
'For every place holder in the sql statement a parameter object must be created and added to the parameters collection.
Dim parEmpName As 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 dic As New Dictionary(Of String, Decimal) dic.Add("DE1", 10000)
dic.Add("DE2", 2000) dic.Add("DE3", 30000) dic.Add("DE4", 40000)
'Set the value for the parameters.
con.Open()
cmd.Prepare() 'Prepares the execution plan for the sql statement.
For Each kv As KeyValuePair(Of String, Decimal) In dic parEmpName.Value = kv.Key 'Get Name
parEmpSalary.Value = kv.Value ' Get Salary cmd.ExecuteNonQuery()
Next
con.Close() End Sub
Note: 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.
Stored Procedures
It is a precompiled set of sql statement which are compiled in native form and stored in the backend. They are very fast on their first execution also.
Advantages:
1. They are very fast in execution because they are precompiled and stored in backend in native form of that backend.
2. Reduces network trafic 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.
Note:
As far as possible always try to execute all the sql statements using stored procedures.
Every Input Parameter must have a value before the command is executed. If required we can set the to DbNull.Value.
In SQL-Server every out parameter is also input.
Sample Stored Procedure
Create Procedure GetSalary(@Id int,@Sal money out) AS Begin
Select @Sal=EmpSalary from Emp where EmpId=@Id End
--- Private Sub btnSPDemo_Click(. . .) Handles btnSPDemo.Click
Dim con As New SqlConnection("Data Source=.\sqlexpress;Initial Catalog=MSNetDemoDb;Integrated Security=TRUE") Dim cmd As New SqlCommand
cmd.CommandText = "GetSalary"
cmd.CommandType = CommandType.StoredProcedure cmd.Connection = con
Dim parId As New SqlParameter("@Id", SqlDbType.Int) Dim parSal As New SqlParameter("@Sal", SqlDbType.Money) parSal.Direction = ParameterDirection.Output
parSal.Value = DBNull.Value cmd.Parameters.Add(parId) cmd.Parameters.Add(parSal) con.Open()
parId.Value = txtId.Text cmd.ExecuteNonQuery() MsgBox(parSal.Value)
Deccansoft Software Services – MS.NET ADO.NET
con.Close() End Sub
Create Procedure GetEmployees AS Begin
Select Count(*) from Emp
Select EmpId, EmpName, EmpSalary from Emp End
Private Sub btnGetEmployeesStoredProcedure_Click(. . .) Handles btnInsParam.Click
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()
dr = 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 or may not be supported by a give database.
Using Factory class for writing Provider Independent code. (only in 2.0) Dim con As IDbConnection
Dim fac As System.Data.Common.DbProviderFactory
fac = System.Data.Common.DbProviderFactories.GetFactory( “System.Data.SqlClient") con = fac.CreateConnection()
con.ConnectionString = ""
Dim cmd As IDbCommand cmd = con.CreateCommand() Dim par As IDataParameter par = cmd.CreateParameter() Dim dr As IDataReader dr = cmd.ExecuteReader
Transaction: Its a group of Sql Statements to de executed as one unit to modify the state of database.
ACID (Atomacity, Consistancy, Isolation and Durability)
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()
Deccansoft Software Services – MS.NET ADO.NET
cmd2.Connection = con
cmd1.CommandText = "Insert into Emp(EmpName,EmpSalary) Values(‘A1’,10000)"
cmd2.CommandText = "Update Emp set EmpSalary = EmpSalary = 1000"
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() 'This will fail
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
While Practising:
On Execution of above code by default we will get an error and the transaction is rolledback and then Execute the above code replacing “=” with “+” in the update statement and see that transaction is now commited and a record is inserted in Emp table.
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.
How Generic code can be written – Example for execution of Stored Procedure.
Class DBUtil
Public Shared Function ExecuteSPNonQuery(ByVal connectionString As String, ByVal spName As String, ByVal ParamArray params() As SqlParameter) As Integer Dim con As New SqlConnection(connectionString)
Dim cmd As New SqlCommand(spName, con)
cmd.CommandType = CommandType.StoredProcedure For Each p As SqlParameter In params
cmd.Parameters.Add(p) Next
Try
con.Open()
Return cmd.ExecuteNonQuery() Finally
con.Close() End Try 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
Async Execution of Command Dim dr As SqlDataReader Dim cmd As New SqlCommand
Dim ar As IAsyncResult = cmd.BeginExecuteReader() ‘Begins the Asynchronous call MessageBox.Show(“Continuee….”)
If (ar.IsCompleted) Then ‘Checks if the return value is available.
dr = cmd.EndExecuteReader(ar) ‘Ends the Asynchronous call and provides the return value.
End If End Sub