Scalability with .NET Podcast - Broadcast #2

Subscribe

Download url: http://aspnetpodcast.com/CS11/blogs/asp.net_podcast/archive/2005/11/08/podcast-2.aspx 

Pablo Castro
Article on Asynchronous ADO.NET.
Managed Data Access
Please Buy a copy of our book!

Scott Guthrie
IIS7 with Scott Guthrie

Code Samples:

BeginExecuteNonQuery

        Dim strSql As String
        Dim strCn As String = Me.gstrAsyncString
        Dim iResult As IAsyncResult
        sqlCm = New SqlCommand()
        sqlCn = New SqlConnection(strCn)
        sqlCn.Open()
        sqlCm.Connection = sqlCn
        sqlCm.CommandType = CommandType.Text
        strSql = "insert into tblAsyncExecuteNonQuery ( ClientDateEntered) values ('" & DateTime.Now.ToString() & "')"
        sqlCm.CommandText = strSql
        Me.gdtStart = GetTickCount()
        iResult = sqlCm.BeginExecuteNonQuery(New System.AsyncCallback(AddressOf ExecuteNonQueryCallback), sqlCm)

    Private Sub ExecuteNonQueryCallback(ByVal iRes As IAsyncResult)
        Dim sqlCm As SqlCommand = CType(iRes.AsyncState, SqlCommand)
        Try
            sqlCm.EndExecuteNonQuery(iRes)
            Me.gdtEnd = GetTickCount()
            Dim timeDsplay As New DisplayTimeDelegate(AddressOf DisplayTime)
            Me.Invoke(timeDsplay)

        Catch sqlExc As SqlException
            'Perform some type of operation to notify someone that an error has occurred.
            'If an exception occurs, show it to the user.
        Finally
            If sqlCn.State <> ConnectionState.Closed Then
                sqlCn.Close()
            End If
            sqlCn.Dispose()
            sqlCn = Nothing
            sqlCm.Dispose()
            sqlCm = Nothing
        End Try

    End Sub

BeginExecuteReader

        Dim strSql As String
        Dim strCn As String = gstrAsyncString
        Dim iResult As IAsyncResult
        sqlCm = New SqlCommand()
        sqlCn = New SqlConnection(strCn)
        sqlCn.Open()
        sqlCm.Connection = sqlCn
        sqlCm.CommandType = CommandType.Text
        strSql = "select count(*) from tblAsyncExecuteNonQuery"
        sqlCm.CommandText = strSql
        Me.gdtStart = GetTickCount()
        iResult = sqlCm.BeginExecuteReader(New System.AsyncCallback(AddressOf ExecuteDataReaderCallback), sqlCm, CommandBehavior.Default)

    Private Sub ExecuteDataReaderCallback(ByVal iRes As IAsyncResult)
        Dim sqlDr As SqlDataReader
        Dim sqlCm As SqlCommand = CType(iRes.AsyncState, SqlCommand)
        Try
            sqlDr = sqlCm.EndExecuteReader(iRes)
            Me.gdtEnd = GetTickCount()
            sqlDr.Read()
            Me.giNum = Convert.ToInt32(sqlDr(0))
            Dim cntDsplay As New DisplayCountDelegate(AddressOf DisplayCount)
            Me.Invoke(cntDsplay)
            sqlDr.Close()
            sqlDr.Dispose()
            sqlDr = Nothing

        Catch sqlExc As SqlException
            'Perform some type of operation to notify someone that an error has occurred.
            'If an exception occurs, show it to the user.
        Finally

           If sqlCn.State <> ConnectionState.Closed Then
                sqlCn.Close()
            End If
            sqlCn.Dispose()
            sqlCn = Nothing
            sqlCm.Dispose()
            sqlCm = Nothing
        End Try

SqlConnectionStringBuilder

        Dim sqlCn As New SqlConnection()
        Dim scsbObj As New SqlClient.SqlConnectionStringBuilder(Me.gstrSyncString)
        Try
            scsbObj.Password = Me.txtPassword.Text
            SqlConnection.ChangePassword(scsbObj.ConnectionString, Me.txtNewPassword.Text)

            scsbObj.Password = Me.txtNewPassword.Text
            sqlCn.ConnectionString = scsbObj.ConnectionString
            sqlCn.Open()
            Me.gstrSyncString = scsbObj.ConnectionString

            MsgBox("Succesful logon with a new password.")
        Catch sqlEx As SqlException
            'Do something with this error.
            MsgBox("Error: " & sqlEx.Message.ToString())
        Finally

            If sqlCn.State <> ConnectionState.Closed Then
                sqlCn.Close()
            End If
            sqlCn.Dispose()
            sqlCn = Nothing

        End Try

DataTypes:

        Dim i As Integer
        Dim strSql As String = "select * from tblTest where 1=2"
        Dim scsbObj As New SqlConnectionStringBuilder(Me.gstrSyncString)
        Dim sqlCn As New SqlConnection(scsbObj.ConnectionString)
        Dim sqlCm As New SqlCommand(strSql, sqlCn)
        Dim sqlDr As SqlDataReader
        Dim dtData As DataTable

        Try
            sqlCn.Open()
            sqlDr = sqlCm.ExecuteReader(CommandBehavior.SchemaOnly)
            dtData = sqlDr.GetSchemaTable()
            Me.dgvColumns.DataSource = dtData
            Me.dgvColumns.Visible = True

            '            sqlDr.Read()
            For i = 0 To (sqlDr.FieldCount - 1)
                MsgBox(sqlDr.GetName(i) + vbCrLf + "SqlType: " + _
                    Convert.ToString(sqlDr.GetProviderSpecificFieldType(i)) + vbCrLf + _
                    "Database Type: " + Convert.ToString(sqlDr.GetDataTypeName(i)) + vbCrLf + _
                    ".NET Type: " + Convert.ToString(sqlDr.GetFieldType(i)) + vbCrLf)
            Next
            If Not (sqlDr.IsClosed = True) Then
                sqlDr.Close()
            End If
            sqlDr.Dispose()
            sqlDr = Nothing
        Catch sqlExc As SqlException
            MsgBox("Error: " & sqlExc.Message.ToString())
        Finally
            sqlCm.Dispose()
            sqlCm = Nothing
            If sqlCn.State <> ConnectionState.Closed Then
                sqlCn.Close()
            End If
            sqlCn.Dispose()
            sqlCn = Nothing
        End Try

    End Sub