Follow me on Twitter at Twitter.com/wbm
FYI, I'm blogging most of my stuff over at More Wally now.
You might want to add my rss feed to your reader at:http://morewally.com/cs/blogs/wallym/rss.aspx
Scalability with .NET Podcast - Broadcast #2 - Wallace B. McClure

Wallace B. McClure

All About Wally McClure - The musings of Wallym on Web, HTML5, Mobile, Xamarin.iOS, Xamarin.Android, and Windows Azure.

News

Visual Studio Magazine Column Personal Blog

.NET

Book Authors

Business

Family

Friends

Georgia Tech Bloggers

Personal

Archives

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

Posted: May 31 2005, 05:37 PM by Wallym | with 4 comment(s)
Filed under:

Comments

mousse said:

thanks OODLES for the example code, and the asynchronous article link is excellent as well. don't let the following criticism indicate i'm less than very happy to be listening to you: the podcast audio didn't tell me anything worthwhile beyond just reading the code. info akin to the asynchronous article would be what i'd like to hear more of. how and why to consider the example code, plus any caveats.

as for future podcasts, here are some things i'd be curious to hear about:
-master pages & any other assimilated wisdom from the .text/communityserver group
-i don't think i have to mention i LOVE hearing about new features/advantages dealing with asp.net/sqlserver/iis interoperability, just like this asynchronous dbcall functionality.
-critical differences between 05 and 03 that impact design
-visual studio timesaving/design tricks
-this is kind of vacuous, but a big-picture view of managing tables->datasets->bizrules->display-databinding, within the mindset of a inter/ranet application. in 03, i had some trouble designing around null values & binding, among other things, and took a more manually-coded angle than i'd have liked. business-logic most especially, i deal with very conditional (lots of permutations) rules that reference a lot of data the page doesn't neccessarily display.

thanks again sr wally, i always enjoy catching your scoops.
# June 2, 2005 2:34 PM

Paul Schaeflein said:

IMHO, it is not a podcast unless you have an enclosure tag on the RSS feed...
# June 2, 2005 4:09 PM