Eureka!

Say you are changing information in you app against a dataset, then you can view the changes you have made at any time by utilitising various tracking methods of the dataset. These include .HasChanges(), .GetChanges(), etc.

Transaction processing is an absolutely vital part of developing database applications. It allows for commits and rollbacks. So, in the event that something unforseen should occur, you can roll back all your SQL statements and handle the exception, then make the necessary adjustments, and run the scripts again. Here is a short example:

Dim objConn As New SqlConnectionDim objComm As New SqlCommandDim objTran As SqlTransaction

Try   objConn.ConnectionString = "server=(local);database=northwind;integrated security=SSPI"   objConn.Open()   objTran = objConn.BeginTransaction(IsolationLevel.ReadCommitted)   objComm.Connection = objConn   objComm.CommandType = CommandType.Text   objComm.CommandText = "INSERT INTO blog (entry) VALUES ('" + txtEntry.Text + "')"   objComm.Transaction = objTran   objComm.ExecuteNonQuery()   objTran.Commit()   objConn.Close()   LoadBlog()Catch objError As Exception   objTran.Rollback()   MessageBox.Show(objError.Message)End Try

You will immediately notice that I roll back any SQL queries that happened after the transaction object was set if any error should occur. This means that if anything goes wrong, the database does not get updated. You can see where this is going: for maintenance, batch processes, etc., you should always be using transaction processes, so that if one statement should fail, it wont corrupt other dependant data.

1. When working with datareaders and stored procedures, and your stored procedure returns a paramter value, you must close the datareader before you can access the parameter value. Microsoft says its not a bug, but missing functionality is just as bad as erroneous funcitonality. So: BUG!
2. You can call .ExecuteScalar on queries that return multiple rows (for some reason, eventhough it makes no sense), however, it will only return the first field of the first row.
3. You can bind datareaders to web objects when in ASP.NET, however, you can only bind datareaders to datasets, data tables, and data views when working in VB.NET.
4. You must ‘prime’ a datareader before accessing its content, like so:

   While reader.Read()[...]End While

5. When retrieving data from datareaders using the various get methods, only the SQL data type get methods allow for NULL values. Using the get methods uses less overhead, since the data is returned as that explicit type and not as an object.
6. Multiple selects can be executed at the same time using a single data reader. This can be usefull for minimizing resource usage when initializing data when opening an application or loading a web app. Consider the following (note reader.NextResult(), which moves to the next SQL statement in the datareader results):

   comm.CommandText = "SELECT * FROM customers;SELECT * FROM products"reader = comm.ExecuteReaderWhile reader.Read()   [...]End WhileIf (reader.NextResult()) Then   While reader.Read()       [...]   End WhileEnd If

When connection to a database, or even when executing queries, inserts, updates, and so forth, one should ALWAYS attempt to catch the SQL exceptions apart from the regular catching. This provides more detailed feedback than the regular exception handling:

        Try           'SQL processing here       Catch objError As SqlException           'this will catch all errors on the SQL side (SELECT, INSERT, UPDATE, etc.)           'note that this does not handle errors opening and closing the database           MessageBox.Show(objError.Number + ": " + objerror.Message)       Catch objError As Exception           'errors when opening and closing the database are handled here           MessageBox.Show(objError.ToString + ":" + objError.Message)       End Try

Weirdly enough, in the new .Net it’s OK to close a closed connection (can’t close anything you haven’t opened?!), but it raises an error if you try to open an open connection! In my opinion you shouldn’t rely on being able to close a closed connection in your programming,

    Private Sub btnOpen_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOpen.Click        Try            If (objConn.State <> ConnectionState.Open) Then                objConn.Open()            End If        Catch objError As Exception            MessageBox.Show(objError.Message)        End Try    End Sub

    Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click        Try            If (objConn.State <> ConnectionState.Closed) Then                objConn.Close()            End If        Catch objError As Exception            MessageBox.Show(objError.Message)        End Try    End Sub

Here objConn is a SQLConnection object from the toolbar dragged onto a webform. The two functions are fired by two buttons on the windows from denoting an open event and a close event.

In order to correctly connect to your database, you’ll need a connectionstring. There are so many different types of connections you can create, that you might be stumped for a bit if you’re required to create a strange connection. Well, here’s the solution to all that: this website lists all (or very nearly) of the possible connection strings you could ever want: http://www.carlprothman.net/Default.aspx?tabid=81 or http://www.connectionstrings.com. All you need to do now is look up what you need to connect to, and this site provides how you need to create your connection string! Nifty!

A very powerful and extremely simple tool to convert your SQL results into an XML file is the following:

    Dim objDS As New DataSet

    Private Sub btnLoad_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoad.Click        Dim objConn As New SqlConnection        Dim objDA As SqlDataAdapter

        Try            objConn.ConnectionString = "server=(local);database=northwind;integrated security=SSPI"            objConn.Open()            objDA = New SqlDataAdapter("SELECT * FROM customers ORDER BY companyname", objConn)            objDA.Fill(objDS, "customers")            objConn.Close()            dgData.DataSource = objDS.Tables("customers")        Catch objError As Exception            MessageBox.Show(objError.Message)        End Try    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click        Try            objDS.WriteXml("c:projectscustomers.xml")        Catch objError As Exception            MessageBox.Show(objError.Message)        End Try

Using this method you can save any table in your dataset to an XML file. This can come in handy when transfering data to clients, or even to coworkers. Not to mention to make a backup of your data before making changes.

In order to use data while disconnected from a database, it needs to be loaded in one form or other from the database and kept in memory or stored into a different object. The following code illustrates how to do this by using a datagrid on a windows form. This takes the previous connection how-to a step further, because we are closing the connection after the info is loaded, then allowing for changes to the data in the dataset, and afterwards we can save the newly changed data after opening a new connection again. This example is using a windows form with a datagrid (dgData), a button to load the data (btnLoad) and a button to save the data (btnSave):

    Dim objDS As New DataSet

    Private Sub btnLoad_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoad.Click        Dim objConn As New SqlConnection        Dim objDA As SqlDataAdapter

        Try            objConn.ConnectionString = "server=(local);database=northwind;integrated security=SSPI"            objConn.Open()            objDA = New SqlDataAdapter("SELECT * FROM customers ORDER BY companyname", objConn)            objDA.Fill(objDS, "customers")            objConn.Close()            dgData.DataSource = objDS.Tables("customers")        Catch objError As Exception            MessageBox.Show(objError.Message)        End Try    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click        Dim objConn As New SqlConnection        Dim objDA As SqlDataAdapter        Dim objBuilder As SqlCommandBuilder

        Try            objConn.ConnectionString = "server=(local);database=northwind;integrated security=SSPI"            objConn.Open()            'the following is being used as a template to create the update, delete, and instert commands            objDA = New SqlDataAdapter("SELECT * FROM customers ORDER BY companyname", objConn)            objDA.Fill(objDS, "customers")            objBuilder = New SqlCommandBuilder(objDA)            objDA.DeleteCommand = objBuilder.GetDeleteCommand            objDA.InsertCommand = objBuilder.GetInsertCommand            objDA.UpdateCommand = objBuilder.GetUpdateCommand

            objDA.Update(objDS, "customers")            objConn.Close()        Catch objError As Exception            MessageBox.Show(objError.Message)        End Try    End Sub

This scenario comes in handy when developing applications that remote users may use. However, the inherent complexity comes into play when the user connects again and attempts to update records that have been changed since they were originally downloaded by this user.

The following example illustrates how to connect to a database using a connectionstring. We’re also using a datareader to access multiple rows, and a scalar to access a single value.

        Dim objConn As New SqlConnection        Dim objComm As New SqlCommand        Dim objReader As SqlDataReader        Dim strText As String

        Try            objConn.ConnectionString = "server=(local);database=northwind;integrated security=SSPI"            objConn.Open()            MessageBox.Show("Connection Success!")            objComm.Connection = objConn            objComm.CommandType = CommandType.Text            objComm.CommandText = "SELECT @@VERSION"            MessageBox.Show(objComm.ExecuteScalar())            objComm.CommandText = "SELECT companyname FROM customers ORDER BY companyname"            objReader = objComm.ExecuteReader            'read must be done first to prime the data reader.             'if trying to access item(0) prior to doing a read an exceptionwill occur!            While objReader.Read                strText += objReader.Item(0) + NewLine            End While            MessageBox.Show(strText)        Catch objError As Exception            MessageBox.Show(objError.Message)        End Try

As you can see this is a fairly simple example using the Northwind database. More info will be soon to come on creating your own connectionstrings, etc.

While working on my latest .NET project I ran into the problem of how to specify the program to use Sub main() as the startup procedure. I had originally created my project as a Windows Form application, so the sub main() settings were not set by defeault.

The first thing I did was to change the project preferences (Project > Properties > General) settings Startup Object to Sub Main. This was a given. Next I created a new class containing my Sub Main(). I could not get the program to start up in this manner.

After some googling I found that the Sub Main() needs to be placed inside a module in the following manner:

ModuleSub Main()        [...]End SubEnd Module

After that everything worked like a charm.