Database, Creating In-Memory Data Tables Manually

Problem

You want to manage some data in a database-table-like fashion, but the source data is not coming from a database, or from anything that looks like a table.

Solution

Build a DataTable manually, and fill in all the table details and data yourself.

Discussion

The following code builds a simple table of state information and adds two records:

Dim stateTable As DataTable = New DataTable("UnitedStates")

 ' ----- Use the abbreviation as the primary key.
 Dim priKeyCol(0) As Data.DataColumn
 priKeyCol(0) = stateTable.Columns.Add("ShortName", GetType(String))
 stateTable.PrimaryKey = priKeyCol

 ' ----- Add other data columns.
 stateTable.Columns.Add("FullName", GetType(String))
 stateTable.Columns.Add("Admission", GetType(Date))
 stateTable.Columns.Add("Population", GetType(Long))

Database, Storing the Results of a Query in Memory

Problem

While a data reader is fast and convenient, you would like to keep the retrieved data around for a while, even when you are disconnected from the database or other data source.

Solution

Use the data reader to bring the results into a data set. The DataSet object represents one or more in-memory database tables, each with its records stored in a separate DataTable object.

Discussion

The following code loads all records from the Table1 table into a DataSet object, creating a DataTable object named Table1 within that data set:

' ----- Connect to the database.
 Dim  
connectionString As String = _
    "Data Source=MySystem\SQLEXPRESS;" & _
    "Initial Catalog=MyDatabase;Integrated Security=true"
 Dim theDatabase As New SqlClient.SqlConnection(connectionString)
 theDatabase.Open()

 ' ----- Prepare the SQL statement for use by the data set.
 Dim sqlStatement As New SqlClient.SqlCommand( _
    "SELECT * FROM Table1", theDatabase)

 ' ----- Create the adapter that links the SQL Server-
 '       specific connection and  
command objects with
 '       the database-neutral data set.
 Dim theAdapter As New SqlClient.SqlDataAdapter(sqlStatement)

 ' ----- Create the data set and fill it with the results
 '       of the  
query.
 Dim disconnectedSet As New Data.DataSet
 theAdapter.Fill(disconnectedSet, "Table1")

 ' ----- Clean up.
 theAdapter = Nothing
 sqlStatement = Nothing
 theDatabase.Close()
 theDatabase.Dispose()

Database, Using Transaction

Problem

You need to issue multiple database updates in the context of a single, atomic transaction.

Solution

Use an ADO.NET transaction to envelop the various SQL statements that need to be processed as a unit.

Discussion

The following block of code connects to a database via ADO.NET and makes several database updates within a single transaction:

' ----- Connect to the database.
 Dim connectionString As String = _
    "Data Source=MySystem\SQLEXPRESS;" & _
    "Initial Catalog=MyDatabase;Integrated Security=true"
 Dim theDatabase As New SqlClient.SqlConnection(connectionString)
 theDatabase.Open( )

 ' ----- Create a command object that will hold each
 '       processed SQL statement.
 Dim sqlStatement As New SqlClient.SqlCommand
 sqlStatement.Connection = theDatabase

Database, Using Stored Procedures

Problem

You need to use a stored procedure in your database, and you're not sure how to specify values for its input and output parameters.

Solution

Use the command object's Parameters collection to set and retrieve stored procedure argument values.

Discussion

Here's a simple SQL Server stored procedure that does nothing more than retrieve a field from a table given its ID value:

CREATE PROCEDURE GetRecordName
    @PriKey int,
    @NameResult varchar(50) OUT
 AS
 BEGIN
    -- Given an ID value, return the RecordName field.
    SET @NameResult =
       (SELECT RecordName FROM Table1 WHERE ID = @PriKey);
 END

Format the DataGridView

Formatting the .NET 1.x DataGrid ranges from awkward to nearly impossible. However, thanks to its multi-layered model, formatting the DataGridView is far easier. This model builds on a single class, the DataGridViewCellStyle, which encapsulates key formatting properties. You can assign different DataGridViewCellStyle objects to separate rows, columns, or even distinct cells.

---

Note: By using a few simple style properties, you can configure the appearance of the entire grid, individual columns, or rows with important data.

---

How do I do that?

The DataGridView already looks better than the DataGrid in its default state. For example, you'll notice that the column headers have a modern, flat look and become highlighted when the user moves the mouse over them. However, there's much more you can do with the help of the DataGridViewCellStyle class.

The DataGridViewCellStyle collects all the formatting properties of the DataGridView. It defines appearance-related settings (e.g., color, font), and data formatting (e.g., currency, date formats). All in all, the DataGridViewCellStyle provides the following key properties: