The Essentials

The world of databases is a big one, and in .NET there s much to learn. Many books have been written on this topic alone. Here, however, we re just dedicating a few core bullet points to ensure that everyone gets the feel of how ADO.NET works, whether you re moving from the VB6 school or are a seasoned professional who s already well accustomed to its trials and tribulations.

If you re brand new to the world of databases, you ll probably feel this is all a bit too much, too soon. In that case, I d recommend you check out a more gradual introduction, such as the whole database walkthrough in Karl Moore s Visual Basic .NET: The Tutorials (ISBN 1-59059-021-X).

Once again, before we begin this section, let me remind you: don t worry if you can t understand every single point. The Developer Secrets are completely self-contained and don t rely on your knowledge of what follows . But, of course, it helps.

• Databases are just a storage place for information.

• You can edit databases ”create tables, add data, set up stored procedures, design relationships, and so on ”through the Visual Studio .NET Server Explorer. Click on View Server Explorer to access, right-click on the Data Connections node and select Add Connection to connect into a database, then expand and click to perform edits or make additions. The Servers node also has inherent support for SQL Server databases.

• To quickly bind information from a table to a grid (the DataGrid control) in either Windows forms or Web forms, simply drag and drop your table from the Server Explorer, add a DataGrid control and set its DataSource property, then fill and bind in code. These techniques are demonstrated in the tips, Seven Steps to a Quick, Editable Windows Grid (follow the instructions up to Step 6 and Nine Steps to a Quick, Editable Web Grid (follow the instructions to Step 5).

• To bind data to controls on a Windows form, drag and drop your table from the Server Explorer onto your Windows form. Right-click on the DataAdapter object that is added, select Generate DataSet, change the suggested DataSet name if required, and then click OK. Add controls to your form to display information from the table, then for each, click on the dropdown menu for the Text property, underneath the (DataBindings) section. In the dropdown menu, drill down to your DataSet, then table, finally selecting the field you want to bind to. The form is now set up for data binding. Here is a bunch of sample code snippets for making it all work together:

   ' Fill DataSet with info from DB  DataAdapter1.Fill(DataSet11)  ' Move forward one record, updating bound fields  BindingContext(DataSet11, "TableName").Position += 1  ' Move backward  BindingContext(DataSet11, "TableName").Position -= 1  ' Add new record  BindingContext(DataSet11, "TableName").AddNew()  ' Delete record  Dim intIndex As Integer  intIndex = BindingContext(DataSet11, "TableName").Position  BindingContext(DataSet11, "TableName").RemoveAt(intIndex)  ' Update DB with any DataSet changes  DataAdapter1.Update(DataSet11) 

• Most databases talk in a version of Structured Query Language (SQL). Here are samples of common SQL statements, for easy reference:

   SELECT * FROM customers  INSERT INTO customers (id, name, address)     VALUES(123, 'Joshua Kadison', '101 Bime Street, Texas')  UPDATE customers     SET balance = 899.12     WHERE id = 123  DELETE FROM customers     WHERE id = 123 

• Part of the .NET Framework is ADO.NET, the new non-COM version of ADO (ActiveX Data Objects). ADO.NET contains classes that allow you to interact with databases in code. You may, for example, create a new table or retrieve information about a customer with a SQL statement.

• All the ADO.NET classes are stored under the System.Data namespace. This namespace is further split into two main namespaces: SqlClient , which contains classes designed and optimized to work with SQL Server, and OleDb , which contains classes for working with OLE databases such as Access and Oracle.

• The only real difference between classes in the SqlClient and OleDb classes is the prefix (Sql or OleDb in front of the classes) and the way you declare the connection string. (SQL Server doesn t require you to insert a provider: it knows it s SQL Server, whereas, with OLE DB, you need to specify which database driver to use.) Most times, you can simply edit these two items and you ll be able to convert code snippets between the two formats.

• If you don t have SQL Server on your machine, you can always download and distribute the free poor man s version, Microsoft Desktop Engine (MSDE). To do this, run InstMSDE.exe from your Microsoft Visual Studio .NET 2003\SDK\v1.1\Samples\Setup\msde folder (if running VS .NET 2003, Everett ) or Microsoft Visual Studio .NET\FrameworkSDK\Samples\Setup\ MSDE (if running VS .NET 2002). This tool lacks the visual editors, and it tends to bottleneck at a certain level of performance.

• In ADO.NET, you can choose between two main methods of working with databases in code. The first involves creating a Command object, which holds a SQL statement, and executing it directly against the database. You may also retrieve results into a VB6-style open , forward-only, read-only Recordset called a DataReader. This is typically used for quick, lightweight operations. The second method involves creating a DataAdapter, then using the SQL statement behind this to fill a DataSet, which is like a VB6 Recordset that has the ability to store multiple, possibly related tables (think shaped Recordsets). You can then update information in the DataSet and pass it back to the DataAdapter to update the appropriate records. This is more useful for relational data that could require more complicated editing.

• Here is a code template using the Command method of executing a SQL statement, written for SQL Server (the period in the connection string is a method of referring to your local database server):

   Dim MyConnection As New SqlClient.SqlConnection _      ("server=.;database=MySample;trusted_connection=true")  Dim MyCommand As New SqlClient.SqlCommand( _  "INSERT INTO MyTable(val1, val2) VALUES('My', 'Sample')", MyConnection)  MyConnection.Open()  MyCommand.ExecuteNonQuery()  MyConnection.Close() 

• Here is a code template using the Command method of retrieving information into a DataReader, written for SQL Server:

   Dim MyCommand As New SqlClient.SqlCommand( _      "SELECT * FROM MyTable", MyConnection)  Dim MyReader As SqlClient.SqlDataReader  MyReader.Open()  MyReader = MyCommand.ExecuteReader  Do While MyReader.Read = True      MessageBox.Show(MyReader.Item("MyColumnName"))  Loop  MyReader.Close()  MyConnection.Close() 

• Here we find a selection of typical template code using the more feature-rich, yet memory-expensive DataAdapter method of manipulating data ”this time using an Access database:

   ' General object declarations  Dim objConnection As New OleDb.OleDbConnection( _     "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\mydb.mdb")  Dim objDataAdapter As New OleDb.OleDbDataAdapter( _     "Select * from MyTable", objConnection)  Dim objCommandBuilder As New OleDb.OleDbCommandBuilder( _     objDataAdapter)  Dim objDataSet As New DataSet(), objRow As DataRow  ' Fill your DataSet with schema information  objDataAdapter.FillSchema(objDataSet, SchemaType.Source, "MyTable")  ' Fill your DataSet with the result of your SQL statement  objDataAdapter.Fill(objDataSet, "MyTable")  ' Add a record  objRow = objDataSet.Tables("MyTable").NewRow  objRow.Item("ColumnName") = "Value"  objDataSet.Tables("MyTable").Rows.Add(objRow)  ' Find and edit a row  objRow = objDataSet.Tables("MyTable").Rows.Find("PrimaryKeyValue")  objRow.Item("ColumnName") = "Value"  ' Delete a row  objRow.Delete()  ' Update the backend database  objDataAdapter.Update(objDataSet) 

• The DataAdapter method of working allows you to fill your DataSet with multiple tables, then create relationships among them. Here is a code template showing how you may work with that relationship in code:

   ' Setup relationship  objDataSet.Relations.Add("RelationshipName", _     objDataSet.Tables("ParentTableName").Columns("PrimaryKeyName"), _     objDataSet.Tables("ChildTableName").Columns("ForeignKeyName")  ' Get access to first row  objParentRow = objDataSet.Tables("ParentTableName").Rows(0)  ' Loop through each child record for this parent row  For Each objChildRow In objParentRow.GetChildRows("RelationshipName")     MessageBox.Show(objChildRow.Item("AnyColumnName"))  Next 

• To summarize the main objects used in the previous code snippets: the Connection object provides the link to the database; the DataAdapter object provides the communications link between the database and DataSet; the DataSet object is generic (not specific to any particular type of database) and holds multiple tables of editable in-memory database information, allowing you to set up relationships among the tables; the DataReader object is a onetable, forward-only, read-only version of a DataSet; and a Command object holds a SQL statement that you can execute directly against a database.

• A transaction refers to a bunch of statements that must be completed as a whole or not at all. These are used in the database world to ensure the integrity of your data. Here is a code template for implementing transactions, using the SQL Server classes:

   Dim MyTransaction As SqlClient.SqlTransaction  MyTransaction = MyConnection.BeginTransaction  Try     ' Perform processing here, perhaps adding or     ' deleting data via Command objects.     ' Be sure to set the Transaction property of your     ' Command object(s) equal to the MyTransaction object     MyTransaction.Commit()  Catch     ' Errors occured, so rollback any changes made     MyTransaction.Rollback()  End Try 

• You can also use parameters and call stored procedures using the Command object. See the help index for more information.

• Full instruction on using all the ADO.NET objects listed here is given in Karl Moore s Visual Basic .NET: The Tutorials , which is also available from Apress at www.apress.com.