LEANING: ADO.NET - TOP10

LEANING: ADO.NET

Sponsor Link

Sponsor Link

Objectives

            •Introduce Microsoft® ADO.NET

            •Show the evolution of ADO to ADO.NET

            •Introduce the primary components of ADO.NET

Contents



ADO.NET Core Concepts and Architecture

¡The ADO.NET Object Model
¡The DataSet and Data Views

¡Managed Providers

Core Concepts and Architecture


The ADO.NET Object Model

¡Objects of System.Data
¡.NET data providers

ADO.NET namespace hierarchy


¡Organizes the object model
¡Includes:
System.Data
System.Data.OleDb

System.Data.SqlClient

ADO.NET-related Namespaces





ADO  .NET-related Namespace Example
System.Data
System.Data.OleDb

System.Data.SqlClient

ADO .NET - Objects






ADO .NET Data Providers - Connection


The Connection object creates the connection to the database. Microsoft Visual Studio .NET provides two types of Connection classes:
¡SqlConnection object, which is designed specifically to connect to Microsoft SQL Server 7.0 or later

¡OleDbConnection object, which can provide 
    connections to a wide range of database types like Microsoft Access and Oracle. 

Connection String to Microsoft Access
      Imports System.Data
       Public Class Form1
      Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
      Dim con As New OleDb.OleDbConnection
      con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source =C:\AddressBook.mdb”            ‘For MS access 2003
     ‘con.ConnectionString = "PROVIDER=Microsoft.ACE.oledb.12.0;Data Source =AddressBook.mdb"                 ‘For MS access 2007 or later
 con.Open()
End sub

End Class



Connection String to Microsoft SQL Server with SQL Server Authentication


        Imports System.Data.SqlClient
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
  Dim con As New SqlConnection
  con.ConnectionString = server=DBS;uid=sa;pwd=123;database=AddressBook‘”
 con.Open()
End sub
End Class

Connection String to Oracle Database Server


Imports System.Data
Private Sub cmdLogin_Click(sender As Object, e As EventArgs) Handles cmdLogin.Click
 Dim con As New OleDb.OleDbConnection
        Try
            strOraConn = "Server=" & TxtSID.Text & ";" & "User ID=" & TxtLoginName.Text & ";" & "Password=" & TxtPassword.Text & ";"
            OConn = New OracleConnection(strOraConn)
            OConn.Open()
            Me.Hide()
            Main.Show()
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            OConn.Close()
        End Try
    End Sub


ADO .NET Data Providers - Command


Command objects are used to execute 

commands to a database across a data 



onnection. The Command objects can be 



used to execute stored procedures on the 



database, SQL commands, or return complete tables



directly. Command objects provide three methodsthat are 

used to execute commands on the database:



ExecuteNonQuery: Executes commands that have no   return 



 values such as INSERT,   UPDATE or DELETE


ExecuteScalar: Returns a single value from a database     



query (Select Statement)


ExecuteReader: Returns a result set by way of a           



DataReader object 


ExecuteNonQuery-Example


Imports System.Data.SqlClient

Public Class FrmStudents

    Dim cn As SqlConnection

    Dim sqlStudentCmd As SqlCommand

 Private Sub FrmStudents_Load(ByVal sender 

As System.Object, ByVal e As 

System.EventArgs) Handles MyBase.Load

 cn = New SqlConnection("Server=DBS; 
                
  Database=AddressBook;UID=sa;pwd=123")
  cn.Open()

    End Sub

Private Sub btnSave_Click(ByVal sender As System.Object, 

ByVal e As System.EventArgs) Handles btnSave.Click

        If txtStudentID.Text = "" Then
            MsgBox("Please enter studentID")
            Me.txtStudentID.Focus()
            Exit Sub
        End If

        If txtStudentName.Text = "" Then
            MsgBox("Please enter student name")
            txtStudentName.Focus()
            Exit Sub
        End If

  If txtPhone.Text = "" Then
            MsgBox("Please enter phone number")
            txtPhone.Focus()
            Exit Sub
        End If
        Call AddData() 

    End Sub

 Sub AddData()

        sqlStudentCmd = New SqlCommand
        sqlStudentCmd.CommandType = CommandType.Text
        Dim SqlText As String
        Dim RowEffected As Integer = 0

      SqlText = "Insert into Students values('" & txtStudentID.Text & "','" & txtStudentName.Text & "','" & txtSex.Text & "','" & txtDateOfBirth.Text & "','" & txtPhone.Text & "')"

        sqlStudentCmd.CommandText = SqlText
        sqlStudentCmd.Connection = cn
        RowEffected = sqlStudentCmd.ExecuteNonQuery()

        If RowEffected > 0 Then
            MsgBox("Insert Data is successful", MsgBoxStyle.Information)
            Call clear()
            sqlStudentCmd.Dispose()
        End If

    End Sub


ExecuteScalar -Example






Imports System.Data.SqlClient

Public Class FrmScarlar

Dim cn As SqlConnection

Dim sqlStudentCmd As SqlCommand

Private Sub btnShow_Click(ByVal sender As 

   System.Object, ByVal e As System.EventArgs) 

   Handles btnShow.Click

   Dim sqlcmd As New 

   SqlClient.SqlCommand("Select count(studentID)

   from students", cn)
        
        Dim total As Double
        total = sqlcmd.ExecuteScalar
        MsgBox(total.ToString)

End Sub


ExecuteReader -Example

Imports System.Data.SqlClient

Public Class FrmReader

    Dim cn As SqlConnection

    Dim sqlStudentCmd As SqlCommand

    Private Sub FrmReader_Load(ByVal 

     sender As System.Object, ByVal e As 
  
     System.EventArgs) Handles 

     MyBase.Load

     cn = New SqlConnection("Server=DBS; 
         Database=AddressBook;UID=sa;pwd=12

    3")

    cn.Open()

End Sub



Private Sub btnShow_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnShow.Click        Dim cmd As New SqlCommand("Select studentID, StudentName, Sex     from students", cn)        Dim sqldataReaderStu As SqlDataReader        sqldataReaderStu = cmd.ExecuteReader        Do While sqldataReaderStu.Read             MsgBox(sqldataReaderStu.Item(0) & Space(2) &
  sqldataReaderStu.GetString(1) & Space(2) &
  sqldataReaderStu.Item(2))        Loop    End Sub

End Class


Data Command with Store Prpcedure

Suppose that you have a store procedure as script below


Create Proc [dbo].[ProNewStudent] @StID varchar(10),
@StName varchar(50),@Sex varchar(1),@DOB Datetime,
@Phone varchar(10), @sms varchar(50) OUTPUT
As
  If Exists (Select * from students
  Where studentID=LTrim(@StID))
  SET @sms='Ready'
  Else
  Begin
  Insert into students values
   (@stID,@StName,@Sex,@DOB,@Phone)
  Set @sms='Not yet'
  end


Imports System.Data.SqlClient
Public Class FrmStoreProcedure
    Dim cn As SqlConnection
    Dim sqlStudentCmd As SqlCommand
    Private Sub FrmStoreProcedure_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        cn = New SqlConnection("Server=DBS;Database=AddressBook;UID=sa;pwd=123")
        cn.Open()
    End Sub

Sub newStudent()
        sqlStudentCmd = New SqlCommand
        sqlStudentCmd.CommandType =CommandType.StoredProcedure
        sqlStudentCmd.CommandText = "ProNewStudent"
        sqlStudentCmd.Connection = cn
        sqlStudentCmd.Parameters.Add("@StID",
        SqlDbType.VarChar, 10).Value = txtStudentID.Text
        sqlStudentCmd.Parameters.Add("@StName",
        SqlDbType.VarChar, 50).Value = txtStudentName.Text
        sqlStudentCmd.Parameters.Add("@Sex",
        SqlDbType.VarChar, 1).Value = txtSex.Text
        sqlStudentCmd.Parameters.Add("@DOB",
        SqlDbType.DateTime).Value = txtDateOfBirth.Text
       sqlStudentCmd.Parameters.Add("@Phone", SqlDbType.VarChar, 10).Value = 
       txtPhone.Text
        sqlStudentCmd.Parameters.Add("@sms", SqlDbType.VarChar,
        50).Direction = ParameterDirection.Output
        sqlStudentCmd.ExecuteNonQuery()
        Dim getoutPut As String
        getoutPut = sqlStudentCmd.Parameters("@sms").Value
        If getoutPut = "Ready" Then
            MsgBox(txtStudentID.Text + " is already exist")
        Else
            MsgBox("Save record is successful")
        End If
        sqlStudentCmd.Dispose()
    End Sub


Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Call newStudent()
        Call clear()
    End Sub
End Class


Data Sets and Data Adapters



Data Sets and Data Adapters-Example


Imports System.Data
   Public Class Form1
    Dim con As New OleDb.OleDbConnection
    Dim ds As New DataSet
    Dim da As OleDb.OleDbDataAdapter
    Dim sql As String

    Private Sub Form1_Load(
ByVal sender As System.Object, ByVal e As
                             System.EventArgs) Handles MyBase.Load
        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data
       Source = C:\AddressBook.mdb"
        con.Open()
        sql = "SELECT * FROM tblContacts"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "AddressBook")
        txtFirstName.Text = ds.Tables("AddressBook").Rows(0).Item(1)
        txtSurname.Text = ds.Tables("AddressBook").Rows(0).Item(2)
    End Sub
End Class

NET Data providers-


Connection Object
Command Object
Data Adapter Object
DataTable Object



















First