Introduction to Database Access in .Net
By David M. Woods
Published September 15, 2006, 2:20 pm in News.
Microsoft's flagship development environment includes a wide assortment of ways to read and write to a database. This article gives an overview of many of the available options, along with some pros and cons.
It is presumed that you have some basic familiarity with both DotNet and SQL. In all examples, VB.Net will be used.
Also, it is presumed that you have your database connection string.
Add References
Depending on what type of database you are connecting to, you will need to add an appropriate assembly to your project as a Reference. (An "assembly" is a compiled component such as a DLL.) Go to the Project Explorer, right click on References, and select "Add reference." Once an assembly has been added to your project, you can view its details in the Object Browser.
The DotNet framework includes the System.Data.dll assembly, which includes several namespaces that will be used. (A "namespace" is a collection of classes.) Of particular interest is the System.Data.OleDb namespace, which includes classes for generic database access, and the System.Data.SqlClient namespace, with classes specially optimized for Microsoft Sql Server.
Other developers have created assemblies for certain database managers. For example, if you need to connect to a MySql database, a downloadable Dll assembly is available.
Although not required, it is recommended to include an "Imports" statement as the very first line in a VB.net module to simplify the creation of database objects. Without the "imports" statement, this would be required to create a connection object:
dim MyCon as New System.Data.SqlClient.SqlConnectionHowever, by including this line at the top of the module:
Imports System.Datathe same connection object can be created with:
dim MyCon as New SqlClient.SqlConnectionOverview of Data Access objects
The DotNet framework includes several objects that you will need for database access. Which objects you will use depends largely on what you need to do.
You will notice that many data access objects can be added to a form module either as a drag-and-drop visual object from the toolbar, or exclusively in code. In most cases, there is not much difference; once a visual object is dropped, you can refer to it (and its class members) by object name just as if it had been created in code. The main advantage of visual objects is that you can set their properties in the Properties window. In this article, we will create all data objects in code.
Here are the most important data access object classes:
- Connection. Establishes a connection to the data source.
- Command. Used to create and execute SQL commands.
- DataReader. A forward-only, read-only data stream.
- DataSet. A scrollable, writable representation of data.
- DataAdapter. Controls data into and out of a DataSet.
Connection
All data access starts with the connection object. It is assumed that your connection string has already been composed. Using a Sql Server database as an example, here is code to set up your connection:
Imports System.Data
. . .
dim MyCon as New SqlClient.SqlConnection
MyCon.ConnectionString = "connection string"
MyCon.Open()
Alternatively, you can delay issuing the "Open" method until right before the data access statement, if a persistent database connection is not wanted. Note also that the Connection class contains a "Close( )" method. (The DataAdapter, to be discussed below, opens and closes connections automatically.)
Reading data via ExecuteScalar
The easiest and fastest way to read data is via the ExecuteScalar method. It's rather limited, however; you can only read a single column value from a single row. (More robust tools for reading and writing to be discussed below.)
You will need an SQL query that just gets one value. For example:
select MyCol from MyTable where TheKey = SomeValueAn aggregation query will also work:
select sum(MyNumCol) from MyTableAssuming that the connection object has been established and opened, and that we are reading from a Sql Server database, and the Imports statement described above is in place, here is sample code:
dim ComObj1 as New SqlClient.Command
dim SQL as string
dim VAR1
SQL = "select . . . "
ComObj1.CommandText = SQL
ComObj1.Connection = MyCon
Var1 = ComObj1.ExecuteScalarReading data via the DataReader
To read multiple columns from multiple rows, the DataReader is a good tool. It's still limited to read-only and forward-only, but it is highly optimized for that type of access. Another major limitation of the DataReader is that DotNet only allows one open reader per connection.
You will need an SQL select query, such as:
select C1,C2,C3 from MyTableAgain assuming the same setup as above, here is sample code:
dim ComObj1 as New SqlClient.Command
dim DRead1 as SqlClient.SqlDataReader
SQL = "select . . . "
ComObj1.CommandText = SQL
ComObj1.Connection = MyCon
DataReader = ComObj1.ExecuteReader()
while DRead1.Read()
Var1 = DRead1.Item("C1")
Var2 = DRead1.Item("C2")
Var3 = DRead1.Item("C3")
. . .
end while
DRead1.close()The "Read( )" method of the DataReader class moves to the next row, retrieves the data, and returns a True if more rows exists, otherwise returns False. Note that the "New" clause was not required to instantiate the DataReader object, since the "ExeuteReader( )" method performed that task.
Finally, note that the DataReader must be closed via the "Close( )" method when done!
Accessing data without an SQL string
SQL queries are not the only way to access data in DotNet. Another option is the "Table Direct" technique, which will retrieve all columns, all rows. Use the "CommandType" property and enumerator. (The default command type is "Text", which means an SQL string.)
ComObj1.CommandText = "MyTableName"
ComObj1.CommandType = CommandType.TableDirect
DRead1 = ComObj1.ExecuteReader()Stored procedures can also be used in place of the SQL statement. Again, you will need to use the "CommandType" enumerator:
ComObj1.CommandText = "MyStoredProc"
ComObj1.CommandType = CommandType.StoredProcedure
DataReader = ComObj1.ExecuteReader()
. . .If the stored procedure has parameters, however, you will need to use the Parameter class. (See below.)
Writing Data with the Command object
The easiest way to write data is to use the "ExecuteNonQuery( )" method of the Command Object. The SQL statement, in this case, would be either an SQL update, insert, or delete command, or a non-recordset-returning stored procedure.
ComObj1.CommandText = "update statement"
ComObj1.ExecuteNonQuery()If a stored procedure is used to write the output, be sure to use the correct "CommandType" property.
Parameters
Now lets complicate things up a bit by introducing the Parameter class. Using parameters for database access requires a bit more code to set it all up. However, it has several advantages:
- It's more "elegant". You don't have to compose SQL strings at runtime, and it moves much of the details of reading and updating a database out of your "read" or "update" routines.
- It encodes values properly; for example, if a character string contains an apostrophe.
- It's the only way to call stored procedures that have parameters.
- They work well to update DataSets using DataAdapters (more on that below)
The easiest way to create parameters is via the "Add( )" method of the Parameters class:
With ComObj1.Parameters
.Add("@ParamName", Type, Length)
.Add( . . .)
End withIn SQL Server, parameter names are always prefixed by the at sign @. Other databases may require a different prefix; MySql uses the question mark ?.
The "type" value must be valid for whatever database you are using. Enumerators are available for most databases. For SQL Server, use the "SqlDbType" enumerator. You will also need to specify the data length. For SQL Server, you can get the correct length of numeric fields by viewing the table design in the SQL Enterprise Manager. (Type "integer", for example, has a length of 4.) Here is an example:
.Add("@ParamName", SqlDbType.int, 4)In the CommandText property, substitute the parameter names. Do not use quotes around non-numeric parameters. Example:
select * from MyTable where MyColumn = @ParamName
update MyTable set MyColumn = @ParamName, ...To use output or return-value parameters, you will need to set the Direction property. The ParameterDirection enumerator includes value options for Input, Output, and ReturnValue. (The default is "Input").
.Parameters.Item("@Param").Direction = parameterDirection.OutputThe Value property of a Parameter can be used either to retrieve the value of an output parameter (after the command object is executed), or fill in a value (before the command object is executed):
Variable = ComObj1.Parameters.Item("@Param").Value
. . .
ComObj1.Parameters.Item("@Param").Value = ExpressionTo call a stored procedure with parameters, you must create the parameters, assign the Value and/or ParameterDirection properties where appropriate, and set the CommandText and CommandType property on the Command object. In this example the type "int" is used, and the stored proc is non-recordset returning:
With ComObj1
.Connection = MyCon
.CommandText = "MyStoredProc"
.CommandType = CommandType.StoredProcedure
.Parameters.Add("@Param1", SqlDbType.int, 4)
.Parameters.Item("@Param1").Value = Expression
.ExecuteNonQuery()
End withWhen creating parameters for a Command object that will be used to update a DataSet, you will need to use a special overloaded version of the "Add( )" method that has a fourth parameter for the field name. (More details below on topic "Writing Data Using a DataSet".)
.Parameters.Add(ParamName, Type, Length, FieldName)Reading data using a DataSet
We now come to a much more robust tool for DotNet data access: the DataSet. Unlike the previous techniques, the DataSet is scrollable and writable.
The DataSet replaces the "RecordSet" object found in earlier Microsoft development products. It is actually a disconnected "stateless" representation of data. Furthermore, it can hold multiple data tables, making a DataSet act like a "mini-database." In this article, however, we will limit our discussion to a simple 1-table DataSet.
The DataSet object actually uses XML behind-the-scenes to store its data. In fact, the DataSet class includes methods to read and write directly to XML files. XML details are also beyond the scope of this article.
To read and write data to and from a DataSet, you must use a DataAdapter. Like some of other data access classes described above, there are optimized versions of the DataAdapter for specified database managers. For this discussion, we will assume your database is SQL Server, and thus will use the SqlDataAdapter class.
Here is sample code to set up and populate a DataSet, again using the same setup assumptions as before, and further assuming that the Command object has been initialized:
Dim MyAdapt as New SqlClient.SqlDataAdapter
Dim MyDataSet as New DataSet
Dim SQL as string
SQL = "select . . . "
ComObj1.CommandText = SQL
MyDataAdapt.SelectCommand = ComObj1
MyAdapt.Fill (MyDataSet, "MyTable")The "Fill( )" method does all the work of populating the DataSet. The table name ("MyTable" in the above example) is created on the spot; it will be referred to subsequently.
The DataAdapter also handles connection management for you. If the connection was closed prior to issuing the "Fill" method, it opens it, then closes it when finished.
Now that you have your DataSet, what can we do with it? One excellent tool for displaying DataSet data is the DataGrid. Drag and drop a DataGrid object onto a DotNet form, set it's name, size, and shape, and add these lines:
MyDataGrid1.DataSource = MyDataSet
MyDataGrid1.DataMember = "MyTable"The DataGrid will create a default format for itself based on existing column names.
To programmatically read data from the DataSet, you will need to use two new classes: DataTable and DataRow. Here is sample code to read all data rows, one at a time, and assign column values to variables:
Dim MyDTable as DataTable
Dim MyDRow as DataRow
MyDTable = MyDataSet.Tables("MyTable")
For each MyDRow in MyDTable.rows
Var1 = MyDRow.Item("Column1")
Var2 = MyDRow.Item("Column2")
. . .
NextRoll your own DataSet
As demonstrated above, a DataSet can be populated directly from a query statement. Another way is to populate it directly from an XML file (beyond the scope of this article.) But still another option is to build a DataSet "from scratch". Here is sample code to create a DataTable object with some columns, and add it to a DataSet:
dim MyDataSet as New DataSet
dim MyDTable as DataTable
with MyDTable
.TableName = "MyTable"
.Columns.Add("Col1")
.Columns.Add("Col2")
.Columns.Add("Col3")
. . .
End with
MyDataSet.Tables.Add("MyTable")Writing data using a DataSet
You can, of course, save DataSet changes by composing a SQL update query and executing it via the ExecuteNonQuery( ) method, as demonstrated above. But that is a rather messy way of doing things, so DotNet provides a much more elegant way to update DataSet data by using the DataAdapter.
Here is a quick overview of the process:
1. Create Command objects to update, insert, and delete data rows.
2. Add parameters to these Command objects.
3. Attach these Command objects to a DataAdapter.
4. Modify the data in the DataSet
5. Update the database via the "Update( )" method
Now for the details:
1. Create command objects. You will need an object for updating, one for inserting, and one for deleting. Here is sample code:
dim UpdCom as New SqlClient.Command1
dim SQL as string
SQL = "update MyTable set Col1=123"
UpdCom.Connection = MyCon
UpdCom.CommandText = SQLOf course, you probably will want to soft-code the update statement. To do so, you will need to insert parameterized values into the update statement. If using SQL Server, parameters will begin with the at sign @. (Remember not to put quotes around non-numeric parameter values.) Change the SQL statement to:
SQL = "update MyTable set Col1=@Value1"Furthermore, you probably will want to include a "where" clause so that the update statement does not update every row in the table. You will need to use a parameterized value in the "where" clause, also:
SQL = "update MyTable set Col1=@Value1 where TheKey=@Key1"If inserting and deleting of rows will occur, create Command objects for them:
dim MyInsCom as New SqlClient.Command
SQL = "insert into MyTable values (@Val1,@Val2,@Val3)"
MyInsCom.Connection = MyCon
MyInsCom.CommandText = SQL
dim MyDelCom as New SqlClient.Command
SQL = "delete from MyTable where TheKey=@Key1"
MyDelCom.Connection = MyCon
MyDelCom.CommandText = SQLOf course, stored procedures could be used instead of SQL strings. Set the "CommandType" property as appropriate.
2. Create parameters. For each parameter in the command text or on the stored proc, you will need create a parameter object (see "Parameters" above). However, you must use the 4-value overloaded version of the "Add( )" method of the Parameters class. The 4th value is the name of column in the table to update. Example:
.Parameters.Add("@Param1", SqlDbType.int, 4, "Col1") 3. Attach objects to DataAdapter.
Assign values to the UpdateCommand, InsertCommand, and DeleteCommand properties of the DataAdapter. Example:
With MyDataAdapt
.UpdateCommand = MyUpdCom
.InsertCommand = MyInsCom
.DeleteCommand = MyDelCom
End With 4. Modify the Data.
All changes must be made to a DataRow object, which means it must be correctly instantiated. The easiest way is if you know the row number (the first row is number zero). In this example, the row number is 5:
dim MyDRow as DataRow
MyDRow = MyDTable.Rows(5)If you don't know the row number, you can search for it:
For Each MyDRow In MyDTable.Rows
ConditionalStatment . . .
Next MyDRowNow make data modifications. To change a value for an existing row:
MyDRow.Item("ColumnName") = expressionTo insert a new row:
MyDRow = MyDTable.NewRow()
MyDRow.Item("ColumnName") = expression
. . .
MyDTable.Rows.Add(MyDRow)To delete a row:
MyDRow.Delete()5. Update the database. Call the "Update( )" method of the DataAdpater:
MyDAdapter.Update (MyDataSet, "MyDTable")Do not manually update the parameter values of the update, insert, or delete command objects. The Update( ) method "senses" changes and applies them.
Also, make sure that the DataTable contains all the columns affected by the update, insert, and delete commands, even if they are invisible. Also, make sure these columns are populated when the DataSet is filled.
Finally, remember that the DataAdapter automatically opens and closes the Connection object.
Summary
This has been a quick overview of how to do the most common types of database input and output in DotNet. Obviously, there are many, many other features and details in this incredible development tool that have not been touched upon. If you are fairly new to DotNet, or to object-oriented programming, hopefully this article has helped you get your feet wet.
Comments & Trackbacks
No Comments/Trackbacks for this post yet...
This post has 94 feedbacks awaiting moderation...
Leave a comment