I have not done any VB6 work for a long time. Recently, I found that I need to a little bit of VB6 ADO code to an old application. So I have to re-research something that I was doing everyday years ago. I will record the results here so I don't have to do the same research again.

The first is regarding parameterized query (or someone call it prepared statement). Basically, ADO will use sp_executesql to send the command with the parameters. The execution plan is cached for the connection. If we send another command with same set of parameters (each parameter could have a different value), SQL server will reuse the execution plan.

To create a command that will send prepared statements, we need to use ? in place each parameter that we want to parameterize. For example:

cmd.CommandText = "select * from application where FIRST_NAME = ? and LAST_NAME = ? and BIRTH_DATE = ?"

Then we need to create the parameters, like:

    cmd.Parameters.Append cmd.CreateParameter("FIRST_NAME", adVarChar, adParamInput, 20)
    cmd.Parameters.Append cmd.CreateParameter("LAST_NAME", adVarChar, adParamInput, 20)
    cmd.Parameters.Append cmd.CreateParameter("BIRTH_DATE", adDBTimeStamp)

When we need to execute the command, we can pass in the parameters like this:

    cmd.Parameters("FIRST_NAME").value = Left$(strFirstName, 20)
    cmd.Parameters("LAST_NAME").value = Left$(strLastName, 20)
    cmd.Parameters("BIRTH_DATE").value = ConvertDate(vDateBirth)

Note that if the value we pass in is too long, we will get an error. Also, we need to pass in value of every parameter, even if it is null. Otherwise, we will get:

-2147467259-[Microsoft][ODBC SQL Server Driver]Invalid use of default parameter

Lastly, if we have a text column, here is the way to create the parameter:

    cmd.Parameters.Append cmd.CreateParameter("RESPONSE_STRING", adLongVarChar, adParamInput, -1)

Then here is the way to pass in the value in chunks:

Private Sub WriteTextToTextParameter(param As ADODB.Parameter, value As String)
    Const BLOCK_SIZE = 2048
    Dim l As Integer
    Dim pos As Integer
    l = Len(value)
    If l = 0 Then
        param.value = Null
    Else
        Do While pos < l
            If (pos + BLOCK_SIZE) < l Then
                param.AppendChunk (Mid$(value, pos + 1, BLOCK_SIZE))
            Else
                param.AppendChunk (Mid$(value, pos + 1))
            End If
            pos = pos + BLOCK_SIZE
        Loop
    End If
End Sub

 

I did some research. We currently use DB2 ODBC Provider through ADO (which in turn uses ODBC Provider for OLEDB – MSDASQL). Both ODBC and OLEDB have roles in connection pooling.

By default, DB2 ODBC connection pooling is on with timeout = 60 sec. That is, a connection will be removed from the pool if the connection is not used for 60 sec. There is no max/min bound for ODBC connection pool; it is unbounded. OLEDB resource pooling for MSDASQL is also on. That means that connection could be pooled on both at OLEDB level and at ODBC level. The behavior of OLEDB resource pool is different to ODBC. It does not have max/min bound either. However, OLEDB will drop the entire pool when there are no active connections. In order to keep the pool, Microsoft suggests having code to keep one connection open (see http://msdn2.microsoft.com/en-us/library/ms810829.aspx).

IBM suggests that we disable OLEDB resource pooling for MSDASQL (see http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/ad/c0010958.htm) so that it does not interfere with ODBC resource pooling. By default, both OLEDB resource pooling and ODBC connection pooling on.

By default, neither DataReader.GetSchema() nor DataTable.PrimaryKey contains the info of the Primary Key after executing a data select command.

To retrieve Primary Key using DataReader with data, use:

SqlSelectCommand1.ExecuteReader(CommandBehavior.KeyInfo)

To retrieve Primary Key using DataReader without data, use:

SqlSelectCommand1.ExecuteReader(CommandBehavior.KeyInfo Or CommandBehavior.SchemaOnly)

To retrieve Primary Key using DataReader with data, use:

SqlDataAdapter1.MissingSchemaAction = MissingSchemaAction.AddWithKey

SqlDataAdapter1.Fill(ds)

To retrieve Primary Key using DataReader with data, use:

SqlDataAdapter1.FillSchema(ds, SchemaType.Mapped)

In part 3 of the article, we enhanced the DataGridHelper control to support custom columns.

I wrote this article for aspalliance. It is regarding of building a asp.net server control called DataGridHelper that takes care much of the repetitive coding in asp.net 1.x. Published for less than a week, the article is already among 1/3 of the articles in number of views and on the Popular Articles list. The Part 2 is coming soon.

It is generously easier to use DataSet. We can use automatic paging. We can sort and filter DataSet using DataView. DataSet is excellent for dealing with small recordsets. DataSet can be cached and used more than once though do not forget to refresh the cache when updated. Large recordset is a problem because DataSet will load the entire recordset into memory.

We needs a little bit more code with DataReader if we need paging and sorting. We have to sort in the SQL server. We can only use custom paging when we build to DataReader. Also, all the automatically generated bound columns are read-only in edit mode.

The asp.net datagrid suppports built-in paging or custom paging. Built-in paging is easy to use, but it requires the datasource to support ICollection, such as a datatable. Note, a datareader does not support ICollecton and thus cannot be used with built-in paging. The problem with a large recordset is that it takes lots of memory to load the recordset into a object that support ICollection. We can page through large recordsets using custom paging. There are fundamentally two ways. The first way is a client-side technique. It uses a firehose recordset and skip through the recordset and then only use the records of interest. The skipped records will go to the client-side but this may not be a big problem if the connectivity between the web server and database server is good. The second method is to use a server-side technique so that the record will never go the the client side. This technique requires additional server resources.

This article contains a comprehensive survey.

This site contains a large number of datagrid related links.

This article has a comprehensive summary.

In the past weekend, I set up the Visual Studio 2005 CTP March 2004 edition. It was not a smooth ride. I am still slowly converting code written with PDC bits. Here are some notes.

1. The readme file is extremely valuable. Have a copy ready all the time and it would save lots of time fighting know bugs.

2. VS2005 CTP is not compatible with Yukon beta 1. Yukon would not start. I had to unintall Yukon beta 1 and installed SQL2K with sp3.

3. IIS Manager would not run due to a corrupted registry key. See the readme file for fix.

4. Web Admin would not run. There are no known fixes.

5. master attribute of Pages element in web.config file had been renamed as masterPageFile.

6. app.sitemap has been renamed web.sitemap.

7. Change ConfigurationSettings.ConnectionStrings[connectionStringName] to ConfigurationSettings.ConnectionStrings[connectionStringName].ConnectionString.

8. GridView: columnfields collection now becomes columns collection.

9. DetailView: columnfields collection now becomes fields colleciton.

I just converted some code that updates database with dynamic sql to stored procedure calls. It add many lines of code. The previous code use SqlCommandBuilder to build the insert, update and delete statements from the select statement. The SqlCommandBuilder needs an extra trip to the SQL server to fectch the meta data.

To convert the dynamic SQL call to stored procedure call, we need to create the SqlCommand objects manually and populates its parameters collection. It is important to set the SourceColumn property of the parameter because the SqlDataAdaptor uses the SourceColumn to map the dataset columns to the parameters of the stored procedure.