In this appendix, we'll answer some of the commonly asked questions related to ADO.NET. Most of these questions were originally asked on C# Corner discussion forums (www.c-sharpcorner.com) or other newsgroups.
Q: | FAQ 1: How can I obtain a database schema programmatically? |
|
Q: | FAQ 2: How can I obtain table schema using a Command object? |
|
Q: | FAQ 3: How can I get rid of the plus (+) sign in a DataGrid? |
|
Q: | FAQ 4: How do I hide a DataTable column? |
|
Q: | FAQ 5: How can I insert dates in a SQL statement? |
|
Q: | FAQ 6: How can I get columns names and their data types from a DataSet? |
|
Q: | FAQ 7: How can I find out how many rows are in a DataGrid? |
|
Q: | FAQ 8: How do I implement paging in ADO.NET? |
|
Q: | FAQ 9: How do I implement transactions in the OleDb data provider? |
|
Q: | FAQ 10: How can I create AutoIncrement columns? |
|
Q: | FAQ 11: How can I copy a DataSet's contents? |
|
Q: | FAQ 12: How can I use Count (*) to count number of rows? |
|
Q: | FAQ 13: How do I get a database schema using a DataSet programmatically? |
|
Q: | FAQ 14: How does SELECT DISTINCT work, and how is it related to duplicates? |
|
Q: | FAQ 15: How do I read and write to the Windows Registry in .NET? |
|
Q: | FAQ 16: How can I obtain ODBC Data Source Names (DSNs)? |
|
Q: | FAQ 17: How can I read and write bitmaps or BLOB data? |
|
Q: | FAQ 18: What are DiffGrams and how do they relate to DataSets? How do I read and write DiffGrams using a DataSet? |
|
Q: | FAQ 19: How does CommandBehavior work? |
|
Q: | FAQ 20: How can I count total number of records in a DataReader? |
|
Q: | FAQ 21: How can I find out the end of file in a DataReader? |
|
Q: | FAQ 22: How can I sort records using a DataReader? |
|
Q: | FAQ 23: What's the fastest way to figure out connection strings for different data providers? |
|
Answers
A: | If you're using the OleDb data provider, you can use the GetOleDbSchemaTable method of the OleDbConnection object. Unfortunately, the Sql data provider doesn't support this method. However, you can use sysobjects as a table name in your SELECT statement, which returns all the tables stored in a database for SQL Server. | ||||||||||||||||||||||||||||||||||||||||||||||||||||
A: | You can get a database table schema by executing a SqlCommand object. Actually, the IDataReader object helps you to return a table schema with the help of its GetSchemaTable method. You know that the ExecuteReader method of SqlCommand returns an IDataReader (or SqlDataReader) object. When you want a table schema, you need to pass CommandBehavior.SchemaOnly as the only argument of ExecuteReader, which returns a DataTable with the schema of a database table. Listing C-1 reads a database table schema, displays it in a DataGrid control, and lists all the columns of a table in a ListBox control. Listing C-1: Getting a Database Table Schema Using SqlCommand
conn = New SqlConnection(connectionString) conn.Open() sql = "SELECT * FROM Employees" Dim cmd As SqlCommand = New SqlCommand(sql, conn) Dim reader As IDataReader = _ cmd.ExecuteReader(CommandBehavior.SchemaOnly) Dim schemaTable As DataTable = New DataTable() schemaTable = reader.GetSchemaTable DataGrid1.DataSource = schemaTable Dim rowCollection As DataRowCollection = _ reader.GetSchemaTable().Rows Dim row As DataRow For Each row In rowCollection str = row("ColumnName") ListBox1.Items.Add(str) Next conn.Close() The GetSchemaTable method returns meta-data about each column. Table C-1 defines the metadata and column order.
| ||||||||||||||||||||||||||||||||||||||||||||||||||||
A: | When you fill a DataGrid control using a DataSet's DefaultViewManager, the DataGrid control displays a plus (+) sign, which lets you expand it and see all the available tables in a collection. You can easily avoid the + sign by not binding a DataGrid with the DefaultViewManager. Instead, you can read each DataTable from the collection and bind them programmatically. Say you have a DataSet called ds. You can use the following method:
dataGrid1.DataSource = ds.Tables["TableName"]; or you can use the following:
dataGrid1.DataSource = ds.Tables[index]; | ||||||||||||||||||||||||||||||||||||||||||||||||||||
A: | The Columns property of a DataTable represents a collection of columns in a DataTable. You can get a DataColumn from this collection by using a column index or the name of the column. Setting the Visible property to false hides a column, and setting it to true displays the column:
DataTable.Columns(index).Visible = false; | ||||||||||||||||||||||||||||||||||||||||||||||||||||
A: | The following code shows how to construct a SQL statement with date values in it. You can simply execute this query:
Dim sql As String = "INSERT INTO Table (col1, [date], col2)" & _ " VALUES(4588, #01/02/02#, 'some value')" | ||||||||||||||||||||||||||||||||||||||||||||||||||||
A: | It's a common need when you want to find out how many columns (and their names and data types) that a DataSet or a DataTable contains. You can get DataTable columns through the Columns property, which returns all columns as a collection. If a DataSet has multiple tables, you use the DataSet.Tables property, which returns a collection of DataTable objects as a collection of DataTables. To get a DataTable from a collection, you can either use the table name or use the table index. Once you get a collection of columns of a DataTable, you can read each column one by one and get DataColumn member values. To find out the data type of a DataColumn, you can use the DataType property. If you want to compare whether a column is a string, integer, or other type, you can compare its DataType property with Type.GetType(). Listing C-2 reads a DataTable's columns and checks if a column is a string type. Listing C-2: Reading DataSet Columns and Their Data Types
' Add Table columns to the dropdownlistbox Dim cols As DataColumnCollection = ds.Tables(0).Columns Dim col As DataColumn For Each col In cols searchDropDown.Items.Add(col.ColumnName) ' add only string type columns, otherwise If (col.DataType Is Type.GetType("System.String")) Then ' do something Else ' else do something End If Next | ||||||||||||||||||||||||||||||||||||||||||||||||||||
A: | Each control has a BindingManager object associated with it. You can use the Count property of BindingManager:
Dim rows As Integer = _ dtGrid.BindingContext(dtGrid.DataSource, _ dtGrid.DataMember).Count | ||||||||||||||||||||||||||||||||||||||||||||||||||||
A: | Paging is a processing of fetching a number of rows as a page (a subset of data) instead of all rows from a data source. You can achieve paging in two ways in ADO.NET: by using a DataAdapter and by using a SQL statement. Paging Using a DataAdapter The Fill method of DataAdapter provides an overloaded form where you can ask the DataAdapter to return only a selected number of rows in a DataSet. The overloaded form of the Fill method is as follows:
Overloads Public Function Fill(ByVal dataSet As DataSet, _ ByVal startRecord As Integer, _ ByVal maxRecords As Integer, _ ByVal srcTable As String _ ) As Integer where dataSet is a DataSet to fill with records and, if necessary, schema; startRecord is the zero-based record number to start with; maxRecords is the maximum number of records to retrieve; and srcTable is the name of the source table to use for table mapping. Listing C-3 shows a function that returns a DataSet filled with the records based on the page size passed in the method. Listing C-3: GetPageData Method
Function GetPagedData(ByVal da As SqlDataAdapter, _ ByVal idx As Integer, ByVal size As Integer) As DataSet Dim ds As DataSet = New DataSet() Try da.Fill(ds, idx, size, "Orders") Catch e As Exception MessageBox.Show(e.Message.ToString()) End Try Return ds End Function Paging Using the SELECT TOP SQL Statement Besides the Fill method of a DataAdapter, you can even use a SELECT SQL statement to retrieve the number of records from a table. You use the SELECT TOP statement for this purpose. The following statement selects the top 10 records from the Customers table. You set the SELECT statement as the SELECT TOP statement:
"SELECT TOP 10 CustomerID, CompanyName, " & _ " ContactName FROM Customers ORDER BY CustomerID" Implementing Paging Finally, let's see how to use both the previously discussed methods to implement paging in ADO.NET. The sample application looks like Figure C-1. In this figure, you can enter the number of rows you want to load in a page. The Load Page button loads the first page. The Previous Page and Next Page buttons load those pages from database. When you check the SELECT TOP check box, the program uses the SELECT TOP method; otherwise, it uses the DataAdapter's Fill method. Listing C-4 implements paging using both methods. As you can see from this code, the Load Page button click event handler reads the page size, creates and opens a new connection, and checks whether the SELECT TOP check box is checked. If it is, it calls the GetTopData method; otherwise, it calls the GetPagedData method. Listing C-4: Implementing Paging in ADO.NET
Private Sub LoadPageBtn_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles LoadPageBtn.Click pageSize = Convert.ToInt32(TextBox1.Text.ToString()) ' Create and open a connection conn = New SqlConnection(connectionString) conn.Open() ' Find out total number of records Dim cmd As SqlCommand = New SqlCommand() ' Assign the SQL Insert statement we want to execute to the CommandText cmd.CommandText = "SELECT Count(*) FROM Customers" cmd.Connection = conn ' Call ExecuteNonQuery on the Command Object to execute insert totalRecords = cmd.ExecuteScalar() ' Create data adapter sql = "SELECT CustomerID, CompanyName, ContactName " & _ "FROM Customers ORDER BY CustomerID" adapter = New SqlDataAdapter(sql, conn) ds = New DataSet() ' If SELECT TOP check box is checked If CheckBox1.Checked Then selectTop = True Else selectTop = False End If ' if SELECT TOP is checked If selectTop Then GetTopData("", 0) DataGrid1.DataSource = custTable Else ds = GetPagedData(adapter, curIndex, pageSize) curIndex = curIndex + pageSize DataGrid1.DataSource = ds.DefaultViewManager End If End Sub ' Get a page using SELECT TOP statement Public Shared Sub GetTopData(ByVal selectCmd As String, ByVal type As Integer) ' First time load first TOP pages If (selectCmd.Equals(String.Empty)) Then selectCmd = "SELECT TOP " & pageSize & " CustomerID, CompanyName, " & _ " ContactName FROM Customers ORDER BY CustomerID" End If totalPages = CInt(Math.Ceiling(CDbl(totalRecords) / pageSize)) adapter.SelectCommand.CommandText = selectCmd Dim tmpTable As DataTable = New DataTable("Customers") Dim recordsAffected As Integer = adapter.Fill(tmpTable) ' If the table does not exist, create it. If custTable Is Nothing Then custTable = tmpTable.Clone() ' Refresh the table if at least one record is returned. If recordsAffected > 0 Then Select Case type Case 1 currentPage = currentPage + 1 Case 2 currentPage = currentPage - 1 Case Else currentPage = 1 End Select ' Clear the rows and add new results. custTable.Rows.Clear() ' Import rows from temp tabke to custTable Dim myRow As DataRow For Each myRow In tmpTable.Rows custTable.ImportRow(myRow) Next ' Preserve the first and last primary key values. Dim ordRows() As DataRow = custTable.Select("", "CustomerID ASC") firstVisibleCustomer = ordRows(0)(0).ToString() lastVisibleCustomer = ordRows(custTable.Rows.Count - 1)(0).ToString() End If End Sub ' Previous page button click Private Sub PrePageBtn_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles PrePageBtn.Click ' if SELECT TOP is checked If selectTop Then sql = "SELECT TOP " & pageSize & " CustomerID, CompanyName, " & _ " ContactName FROM Customers " & _ "WHERE CustomerID < '" & firstVisibleCustomer & "' ORDER BY CustomerID" GetTopData(sql, 1) Else ds = GetPagedData(adapter, curIndex, pageSize) curIndex = curIndex - pageSize DataGrid1.DataSource = ds.DefaultViewManager End If End Sub ' Next page button click Private Sub NextPageBtn_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles NextPageBtn.Click ' if SELECT TOP is checked If selectTop Then sql = "SELECT TOP " & pageSize & " CustomerID, CompanyName, " & _ " ContactName FROM Customers " & _ "WHERE CustomerID > '" & lastVisibleCustomer & "' ORDER BY CustomerID" GetTopData(sql, 2) Else ds = GetPagedData(adapter, curIndex, pageSize) curIndex = curIndex + pageSize DataGrid1.DataSource = ds.DefaultViewManager End If End Sub The GetTopData method uses the SELECT TOP SQL query to get the top records, creates a DataTable custTable, and reads data in this table from the main DataTable tmpTable, which has all records from the database table. Now, if you see the Previous Page and Next Page button click handlers, you'll see that it's just a matter of calling GetTopData and GetPagedData with the current record number and number of records to be fetched. You can download the complete source code from the Downloads section of the Apress Web site (www.apress.com) for more details. | ||||||||||||||||||||||||||||||||||||||||||||||||||||
A: | A transaction is a set of SQL SELECT , INSERT, UPDATE, and DELETE statements that are dependent on one another. For instance, say you can't delete and update some data until it's added to the database. So, you combine all required SQL statements and execute them as a single transaction. If there are no errors during the transaction, all modifications in the transaction become a permanent part of the database. If errors are encountered, none of the modifications are made to the database. If a transaction was successful, the transaction was committed; otherwise, the transaction was rolled back. For example, in a banking application where funds are transferred from one account to another, one account is credited an amount and another account is debited the same amount simultaneously. Because computers can fail because of power outages, network outages, and so on, it's possible to update a row in one table but not in the related table. If your database supports transactions, you can group database operations into a transaction to prevent database inconsistency resulting from these outages. In ADO.NET, the Connection and Transaction objects handle transactions. Follow these steps to perform transactions:
| ||||||||||||||||||||||||||||||||||||||||||||||||||||
A: | Auto-incrementing columns are columns whose value increases automatically when a new record is added to the table. To create an auto-incrementing column, you set the DataColumn.AutoIncrement property to true. The DataColumn will then start with the value defined in the AutoIncrementSeed property, and with each row added, the value of the AutoIncrement column increases by the value held in the AutoIncrementStep property of the column:
Dim col1 As DataColumn = DataTable.Columns.Add("CustomerID", typeof(Int32)) workColumn.AutoIncrement = true workColumn.AutoIncrementSeed = 1000 workColumn.AutoIncrementStep = 10 | ||||||||||||||||||||||||||||||||||||||||||||||||||||
A: | There are occasions when you don't want to mess with the original DataSet's data and instead want to copy data somewhere and work with it. The DataSet provides a Copy method that you can use to copy the data of a DataSet to a second DataSet. When you copy data, you can copy in different ways: You can create an exact copy of a DataSet including its schema, data, row state information, and row versions. You can also copy only affected rows of a DataSet to a second DataSet. Another case may be when you only want to copy a DataSet schema, not the data. To create an exact copy of the DataSet including a DataSet schema and data, you can use the Copy method. For example:
Dim ds2 As DataSet = ds1.Copy() The GetChanges method gets the affected rows of a DataSet. The GetChanges method takes a DataRowState object, which has members including Added, Modified, and Deleted. Using this method, you can create a new DataSet from an existing DataSet with all rows that have been modified, or you can specify new added, deleted, or modified rows. To create a copy of a DataSet that includes schema and only the data representing the Added, Modified, or Deleted rows, use the GetChanges method of the DataSet. You can also use GetChanges to return only rows with a specified row state by passing a DataRowState value when calling GetChanges. The following code shows how to pass a DataRowState when calling GetChanges:
' Copy all changes. Dim ds2 As DataSet = ds1.GetChanges() ' Copy only new rows. Dim ds2 As DataSet = ds1.GetChanges(DataRowState.Added) ' Copy only updated rows. Dim ds2 As DataSet = ds1.GetChanges(DataRowState.Modified) ' Copy only deleted rows. Dim ds2 As DataSet = ds1.GetChanges(DataRowState.Deleted) The Clone method of a DataSet creates a new DataSet from an existing DataSet without copying its data:
Dim ds2 As DataSet = ds1.Clone() | ||||||||||||||||||||||||||||||||||||||||||||||||||||
A: | Getting the number of records using a DataSet is pretty simple. A DataSet is a set of DataTable objects, and a DataTable's Rows property represents the number of records in a DataTable. The following code retrieves the number of rows of the first table of a DataSet. Similarity, you can retrieve the number of records from other tables if a DataSet contains more than one DataTable object:
Dim ds As DataSet = New DataSet() adapter.Fill(ds, "Employees") Dim counter As Integer = ds.Tables(0).Rows.Count MessageBox.Show(counter.ToString()) Retrieving the number of records using a DataReader is little tricky. You use the SELECT Count * SQL statement and execute the SQL statement using the ExecuteScalar method of the Command object. The following code returns the number of records using the Count * statement:
Dim cmd As OleDbCommand = _ New OleDbCommand("SELECT Count(*) FROM Orders", conn) Dim counter As Integer = CInt(cmd.ExecuteScalar()) MessageBox.Show(counter.ToString()) | ||||||||||||||||||||||||||||||||||||||||||||||||||||
A: | Usually when you work with databases, you already know the database schema such as database tables, table columns, and column properties. What if you don't know the database schema, and you need to get a database's tables, columns, and column properties programmatically? In this sample we show you how to access a database schema programmatically. As you can see from Figure C-2, we created a Windows application with one TextBox control, three Button controls, and two ListBox controls. The Browse button lets you browse .mdb databases on your machine. The Get Tables button then reads the database tables and adds them to the first list box. The Get Table Schema button returns the table columns and the properties of the selected table in list box. Listing C-6 shows the source code for this application. As you can see, the BrowseBtn_Click handler browses Access databases on the machine, fills the selected database name to the text box, and sets dbName as the database name, which is a string type of variable defined as follows:
private string dbName = ""; Listing C-6: Reading a Database Schema Programmatically
Private Sub Browse_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click Dim fdlg As OpenFileDialog = New OpenFileDialog() fdlg.Title = "C# Corner Open File Dialog" fdlg.InitialDirectory = "c:\\" fdlg.Filter = "All files (*.*)|*.mdb|" & _ "MS-Access Database files (*.mdb)|*.mdb" fdlg.FilterIndex = 2 fdlg.RestoreDirectory = True If fdlg.ShowDialog() = DialogResult.OK Then TextBox1.Text = fdlg.FileName dbName = fdlg.FileName End If End Sub The GetOleDbSchemaTable method of OleDbConnection returns a DataTable object containing database tables. As you can see from the GetTableBtn_Click handler listed in Listing C-7, we set the DataSet to the left list box with the DisplayMember property set to TABLE_NAME. We also set DisplayMember because we show only one column of the DataTable in the list box. Listing C-7: Getting a Database's Tables from a SQL Server Database
Private Sub GetTables_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles GetTables.Click ' Connection string Dim strDSN As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" + dbName ' Create a connection and open it Dim conn As OleDbConnection = New OleDbConnection(strDSN) Try conn.Open() ' Call GetOleDbSchemaTable to get the schema data table Dim dt As DataTable = _ conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() _ {Nothing, Nothing, Nothing, "TABLE"}) ' Set DataSource and DisplayMember properties ' of the list box control ListBox1.DataSource = dt.DefaultView ListBox1.DisplayMember = "TABLE_NAME" Catch exp As Exception MessageBox.Show(exp.Message.ToString()) Finally ' Close the connection conn.Close() conn.Dispose() End Try End Sub Listing C-8 shows the GetSchemaBn_Click event handler that returns the columns and properties of a database table. You read the database table using SELECT * and use a DataTable to get columns. The DataColumn class defines the member for a table column properties such as allow null, auto number, unique, column data type, column name, and so on. Listing C-8: Getting a Database Table Schema
Private Sub GetTableSchema_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles GetTableSchema.Click ' Get the selected item text of list box Dim selTable As String = ListBox1.GetItemText(ListBox1.SelectedItem) ' Connection string Dim strDSN As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" + dbName ' Create and open connection Dim conn As OleDbConnection = New OleDbConnection(strDSN) Try conn.Open() Dim strSQL As String = "SELECT * FROM " + selTable ' Create data adapter Dim adapter As OleDbDataAdapter = New OleDbDataAdapter(strSQL, conn) ' Create and fill data set Dim dtSet As DataSet = New DataSet() adapter.Fill(dtSet) Dim dt As DataTable = dtSet.Tables(0) ' Add items to the list box control ListBox2.Items.Add("Column Name, DataType, Unique," & _ " AutoIncrement, AllowNull") ListBox2.Items.Add("=====================================") Dim i As Integer For i = 0 To dt.Columns.Count - 1 Dim dc As DataColumn dc = dt.Columns(i) ListBox2.Items.Add(dc.ColumnName.ToString() + _ " , " + dc.DataType.ToString() + _ " ," + dc.Unique.ToString() + " ," + dc.AutoIncrement.ToString() & _ " ," + dc.AllowDBNull.ToString()) Next Catch exp As Exception MessageBox.Show(exp.Message) Finally conn.Close() conn.Dispose() End Try End Sub | ||||||||||||||||||||||||||||||||||||||||||||||||||||
A: | It's not a big deal to select unique rows from a database table using SELECT DISTINCT, but somehow this question gets asked a lot. So, it's not a bad idea to talk about procedure. You can use the SELECT DISTINCT SQL statement to select distinct records from a database. This is useful when you want to return only one record corresponding to a criterion. Listing C-9 returns distinct records from the Employees table ordered by the last name. Listing C-9: Selecting Distinct Rows from a Database Table
Private Sub DistinctRows_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles DistinctRows.Click ' Connection string Dim strDSN As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\\Northwind.mdb" ' Create a connection and open it Dim conn As OleDbConnection = New OleDbConnection(strDSN) Try conn.Open() ' Call GetOleDbSchemaTable to get the schema data table Dim sql As String = "SELECT DISTINCT(LastName)" & _ "FROM Employees ORDER BY LastName" Dim adapter As OleDbDataAdapter = New OleDbDataAdapter(sql, conn) Dim ds As DataSet = New DataSet() adapter.Fill(ds, "Employees") DataGrid1.DataSource = ds.DefaultViewManager Catch exp As Exception MessageBox.Show(exp.Message.ToString()) Finally ' Close the connection conn.Close() conn.Dispose() End Try End Sub | ||||||||||||||||||||||||||||||||||||||||||||||||||||
A: | There are many cases when you need to read and store data in the Windows Registry. A common case is storing your program's serial number and activation code in the Windows Registry. The installation package of your program writes a serial number and activation code to the Windows Registry, and when the user runs program, it checks whether the code matches and runs the program. The Windows Registry is a central database for application configuration settings and other information required by the applications. Actually, there's nothing else you can do with the Windows Registry besides reading its data and writing data to it. .NET Framework Library provides two classes (Registry and RegistryKey) to work with the Registry.
Using the Registry Class The Registry class contains members to provides access to Registry keys. Table C-2 described the Registry class members.
For example, if you want to access the HKEY_LOCAL_MACHINE key, you need to call the Registry.LocalMachine member, which returns a RegistryKey type:
RegistryKey pRegKey = Registry.LocalMachine; Using the RegistryKey Class The RegistryKey class contains members to add, remove, replace, and read Registry data. Table C-3 defines some of its common properties.
Table C-4 describes the RegistryKey class methods.
Adding a Key and Value to Registry Let's see how to add data to the Registry. You use CreateSubKey to add a new key to the Registry and call the SetValue method to write a value and key. The following code does this:
' Create a new key under HKEY_LOCAL_MACHINE\Software as MCBInc Dim key As RegistryKey = Registry.LocalMachine.OpenSubKey("Software", True) ' Add one more sub key Dim newkey As RegistryKey = key.CreateSubKey("MCBInc") ' Set value of sub key newkey.SetValue("MCBInc", "NET Developer") Retrieving Data from the Registry Now let's see how to use these remove the keys and their values from the Registry. The GetValue method returns the value of a subkey in the form of Object. The following code reads the value of the CenteralProcessor\0 subkey and writes it to the console:
' Retrieve data from other part of the registry ' find out your processor Dim pRegKey As RegistryKey = Registry.LocalMachine pRegKey = pRegKey.OpenSubKey("HARDWARE\\DESCRIPTION\\System\\CentralProcessor\\0") Dim val As Object = pRegKey.GetValue("VendorIdentifier") Console.WriteLine("The central processor of this machine is:" + val) Deleting Data You can use the DeleteValue method to delete the value of a subkey. DeleteSubKey deletes the defined subkey. DeleteSubKey deletes the subkey with its data:
Dim delKey As RegistryKey = Registry.LocalMachine.OpenSubKey("Software", True) delKey.DeleteValue("MCBInc") Dim delKey As RegistryKey = Registry.LocalMachine.OpenSubKey("Software", True) delKey.DeleteSubKey("MCBInc") Examining the Final Source Code Listing C-10 shows the entire source code for reading and writing to and from the Windows Registry. Listing C-10: Reading and Writing to and from the Windows Registry
Public Sub ReadWriteRegistry() ' Create a new key under HKEY_LOCAL_MACHINE\Software as MCBInc Dim key As RegistryKey = _ Registry.LocalMachine.OpenSubKey("Software", True) ' Add one more sub key Dim newkey As RegistryKey = key.CreateSubKey("MCBInc") ' Set value of sub key newkey.SetValue("MCBInc", "NET Developer") ' Retrieve data from other part of the registry ' find out your processor Dim pRegKey As RegistryKey = Registry.LocalMachine Dim keyPath As String = _ "HARDWARE\\DESCRIPTION\\System\\CentralProcessor\\0" pRegKey = pRegKey.OpenSubKey(keyPath) Dim val As Object = pRegKey.GetValue("VendorIdentifier") Console.WriteLine("The Processor of this machine is:" + val) ' Delete the key value Dim delKey As RegistryKey = _ Registry.LocalMachine.OpenSubKey("Software", True) delKey.DeleteSubKey("MCBInc") End Sub | ||||||||||||||||||||||||||||||||||||||||||||||||||||
A: | Do you remember programming ODBC before .NET was introduced? The best way to access databases through ODBC was to use ODBC Data Source Names (DSNs). You still use ODBC DSNs to access a database by using the ODBC data provider. How do you read all available ODBC DSNs? You do this through the Windows Registry. All ODBC data sources are stored in the Windows Registry under LocalMachine\ODBC\ODBC.INI\ODBC Data Sources and CurrentUser\Software\ODBC\ODBC.INI\ODBC Data Sources keys. You use the following code:
Imports Microsoft.Win32 You just saw how to read and write to the Windows Registry in FAQ 15. Listing C-11 shows the code module that reads the ODBC system and user DSNs and adds them to a ListBox control. Listing C-11: Reading ODBC DSNs
Private Sub ReadODBCSNs() Dim str As String Dim rootKey As RegistryKey, subKey As RegistryKey Dim dsnList() As String rootKey = Registry.LocalMachine str = "SOFTWARE\\ODBC\\ODBC.INI\\ODBC Data Sources" subKey = rootKey.OpenSubKey(str) dsnList = subKey.GetValueNames() ListBox1.Items.Add("System DSNs") ListBox1.Items.Add("================") Dim dsnName As String For Each dsnName In dsnList ListBox1.Items.Add(dsnName) Next subKey.Close() rootKey.Close() ' Load User DSNs rootKey = Registry.CurrentUser str = "SOFTWARE\\ODBC\\ODBC.INI\\ODBC Data Sources" subKey = rootKey.OpenSubKey(str) dsnList = subKey.GetValueNames() ListBox1.Items.Add("================") ListBox1.Items.Add("User DSNs") ListBox1.Items.Add("================") For Each dsnName In dsnList ListBox1.Items.Add(dsnName) Next subKey.Close() rootKey.Close() End Sub | ||||||||||||||||||||||||||||||||||||||||||||||||||||
A: | Often you may need to save user images in a database and then read back from a database when needed. For an example, we'll save an author's photo in a database so it can be read later to display in the author's article. The Northwind database's Employees table has a Photo field that stores images of employees. You can use this table for testing your code if you want. For this example, though, we'll create our own database. To make it simple, we created a new AppliedAdoNet.mdb Access database and added a Users table to it. The database table schema looks like Figure C-3. Access stores BLOB objects as OLE Object data types. To make the application a little more interactive and user friendly, we created a Windows application, added a TextBox control, three Button controls, and a PictureBox control. The final form looks like Figure C-4. As you can pretty much guess from this figure, the Browse Image button allows users to browse for bitmap files. The Save Image button saves opened file in the database, and the Read Image button reads the first row of the database table, saves binary data as a bitmap, and displays the image in a PictureBox control. Before writing code on the button clicks, define following variables:
' User defined variables Private curImage As Image = Nothing Private curFileName As String = Nothing Private connectionString As String = _ "Provider=Microsoft.Jet.OLEDB.4.0; " & _ "Data Source=C:\\AppliedAdoNet.mdb" Private savedImageName As String _ = "C:\\ImageFromDb.BMP" Also, don't forget to add references to the System.IO and System.Data.OleDb namespaces:
Imports System.Data.OleDb Imports System.IO Listing C-12 shows the Browse button click code, which simply browses bitmap files and saves the filename in the curFileName variable. Listing C-12: Browse Button Click Event Handler
Private Sub BrowseBtn_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles BrowseBtn.Click Dim openDlg As OpenFileDialog = New OpenFileDialog() openDlg.Filter = "All Bitmap files|*.bmp" Dim filter As String = openDlg.Filter openDlg.Title = "Open a Bitmap File" If (openDlg.ShowDialog() = DialogResult.OK) Then curFileName = openDlg.FileName TextBox1.Text = curFileName End If End Sub The Save Image button code shown in Listing C-13 first creates a FileStream object from the bitmap file, opens a connection with the database, adds a new DataRow, set its values, and saves the row back to database. Listing C-13: Save Image Button Click Event Handler
Private Sub SaveImageBtn_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles SaveImageBtn.Click If TextBox1.Text Is String.Empty Then MessageBox.Show("Browse a bitmap") Return End If ' Read a bitmap contents in a stream Dim fs As FileStream = New FileStream(curFileName, _ FileMode.OpenOrCreate, FileAccess.Read) Dim rawData() As Byte = New Byte(fs.Length) {} fs.Read(rawData, 0, System.Convert.ToInt32(fs.Length)) fs.Close() ' Construct a SQL string and a connection object Dim sql As String = "SELECT * FROM Users" Dim conn As OleDbConnection = New OleDbConnection() conn.ConnectionString = connectionString ' Open connection If conn.State <> ConnectionState.Open Then conn.Open() End If ' Create a data adapter and data set Dim adapter As OleDbDataAdapter = _ New OleDbDataAdapter(sql, conn) Dim cmdBuilder As OleDbCommandBuilder = _ New OleDbCommandBuilder(adapter) Dim ds As DataSet = New DataSet("Users") adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey ' Fill data adapter adapter.Fill(ds, "Users") Dim userDes As String = _ "Mahesh Chand is a founder of C# Corner " userDes += "Author: 1. A Programmer's Guide to ADO.NET;" userDes += ", 2. Applied ADO.NET. " ' Create a new row Dim row As DataRow = ds.Tables("Users").NewRow() row("UserName") = "Mahesh Chand" row("UserEmail") = "mcb@mindcracker.com" row("UserDescription") = userDes row("UserPhoto") = rawData ' Add row to the collection ds.Tables("Users").Rows.Add(row) ' Save changes to the database adapter.Update(ds, "Users") ' Clean up connection If conn Is Nothing Then If conn.State = ConnectionState.Open Then conn.Close() End If ' Dispose connection conn.Dispose() End If MessageBox.Show("Image Saved") End Sub Once data is saved, the next step is to read data from the database table, save it as a bitmap again, and view the bitmap on the form. You can directly view an image using the Graphics.DrawImage method or by using a PictureBox control. In this case, we'll use a PictureBox. Listing C-14 shows the code for reading binary data. As you can see, the code simply opens a connection, creates a DataAdapter, fills a DataSet, and gets the first row of the Users table. Now if you want to read all images, you may want to modify your application or make a loop through all rows. Listing C-14: Reading Binary Data
Private Sub UseReaderBtn_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles UseReaderBtn.Click ' Construct a SQL string and a connection object Dim sql As String = "SELECT UserPhoto FROM Users" Dim conn As OleDbConnection = New OleDbConnection() conn.ConnectionString = connectionString ' Open connection If conn.State <> ConnectionState.Open Then conn.Open() End If Dim cmd As OleDbCommand = New OleDbCommand(sql, conn) Dim fs As FileStream Dim bw As BinaryWriter Dim bufferSize As Integer = 300000 Dim outbyte(300000 - 1) As Byte Dim retval As Long Dim startIndex As Long = 0 Dim pub_id As String = "" Dim reader As OleDbDataReader = _ cmd.ExecuteReader(CommandBehavior.SequentialAccess) ' Read first record reader.Read() fs = New FileStream(savedImageName, _ FileMode.OpenOrCreate, FileAccess.Write) bw = New BinaryWriter(fs) startIndex = 0 retval = reader.GetBytes(0, 0, outbyte, 0, bufferSize) bw.Write(outbyte) bw.Flush() ' Close the output file. bw.Close() fs.Close() reader.Close() ' Display image curImage = Image.FromFile(savedImageName) PictureBox1.Image = curImage PictureBox1.Invalidate() ' Clean up connection If conn.State = ConnectionState.Open Then conn.Close() ' Dispose connection conn.Dispose() End If End Sub Once a row is read, you get the data stored in the UserPhoto column (Image column) in a stream and save it as a bitmap file. Later you can view that bitmap file in the PictureBox control by setting its Image property to the filename. Now, you probably want to see this program in action. You can select any image by clicking the Browse Image button, which lets you browse images. Once you've selected a file, you need to save it by clicking the Save Image button. To read the image, simply click the Read Image button. This creates a temporary bitmap file named ImageFromDb.BMP file in c:// folder. You may want to change your path to C:\\. The final output looks like Figure C-5. | ||||||||||||||||||||||||||||||||||||||||||||||||||||
A: | The following sections discuss the basics of DiffGrams and how a DataSet utilizes the DiffGram format in this context. DiffGrams and DataSet There are occasions when you want to compare the original data with the current data to get the changes made to the original data. A common example of this is saving data on Web Forms applications. When working with Web-based data-driven applications, you read data using a DataSet, make some changes to the data, and send data back to the database to save the final data. Sending an entire DataSet may be a costly affair, especially when there are thousands of records in a DataSet. In this scenario, the best practice is to find out the updated rows of a DataSet and send only those rows back to the database instead of the entire DataSet. This is where the DiffGrams are useful.
A DiffGram is an XML format used to identify the current and original versions of data elements. Because the DataSet uses XML to store and transfer data, it also uses DiffGrams to keep track of the original data and the current data. When a DataSet is written as a DiffGram, not only does the DiffGram stores original and current data, it also stores row versions, error information, and their orders. DiffGram XML Format The XML format for a DiffGram has three parts: data instance, DiffGram before, and DiffGram errors. The <DataInstance> tag represents the data instance part of a DiffGram, which represents the current data. The DiffGram before is represented by the <diffgr:before> tag, which represents the original version of the data. The <diffgr:errors> tag represents the DiffGram errors part, which stores the errors and related information. The DiffGram itself is represented by the tag <diffgr:diffgram>. Listing C-15 represents the XML skeleton of a DiffGram. Listing C-15: A DiffGram Format
<?xml version="1.0"?> <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <DataInstance> </DataInstance> <diffgr:before> </diffgr:before> <diffgr:errors> </diffgr:errors> </diffgr:diffgram> The <diffgr:before> sections only store the changed rows, and the <diffgr:errors> section only stores the rows that had errors. Each row in a DiffGram is identified with an ID, and these three sections communicate through this ID. For example, if the ID of a row is Id1, and it has been modified and has errors, the <diffgr:errors> stores those errors. Besides the previously discussed three sections, a DiffGram uses other elements (see Table C-5).
There are two more elements a DataSet-generated DiffGram can have, and these elements are RowOrder and Hidden. RowOrder is the row order of the original data and identifies the index of a row in a particular DataTable. Hidden identifies a column as having a ColumnMapping property set to MappingType.Hidden. Listing C-16 reads data from the Employees tables and writes in an XML document in DiffGram format. Listing C-16: Reading DiffGrams
Dim connectionString As String = _ "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\\Northwind.mdb" Dim sql As String = _ "SELECT EmployeeID, FirstName, LastName, Title FROM Employees" Dim conn As OleDbConnection = Nothing Dim ds As DataSet = Nothing ' Create and open connection conn = New OleDbConnection(connectionString) If conn.State <> ConnectionState.Open Then conn.Open() End If ' Create a data adapter Dim adapter As OleDbDataAdapter = New OleDbDataAdapter(sql, conn) ' Create and fill a DataSet ds = New DataSet("TempDtSet") adapter.Fill(ds, "DtSet") ' Write XML in DiffGram format ds.WriteXml("DiffGramFile.xml", XmlWriteMode.DiffGram) ' Close connection If conn.State = ConnectionState.Open Then conn.Close() End If MessageBox.Show("Done") The output of Listing C-16 looks like Figure C-6. Now if you update data, you'll see new additions to the XML file with <diffgr:before> and <DataInstance> tags; if any errors occur during the update, the entries will go to the <diffgr:errors> section. You can use the ReadXml method to read XML documents in DiffGram format. The first parameter of ReadXml is the XML document name, and the second parameter should be XmlReadMode.DiffGram:
' Create a DataSet Object Dim ds As DataSet = New DataSet() ' Fill with the data ds.ReadXml("DiffGramFile.xml", XmlReadMode.DiffGram) Using the GetChanges Method The GetChanges method of DataSet can retrieve the rows that have been modified since the last time DataSet was filled, saved, or updated. The GetChanges method returns a DataSet's objects with modified rows. The GetChanges method can take either no argument or one argument of type DataRowState. The DataRowState enumeration defines the DataRow state, which can be used to filter a DataSet based on the types of rows. Table C-6 describes the DataRowState members.
Listing C-17 copies only modified rows of ds to a new DataSet called tmpDtSet. Listing C-17: Reading Only Modified Rows of a DataSet
' See if DataSet has changes or not If Not ds.HasChanges(DataRowState.Modified) Then Exit Sub Dim tmpDtSet As DataSet ' GetChanges for modified rows only. tmpDtSet = ds.GetChanges(DataRowState.Modified) If tmpDtSet.HasErrors Then MessageBox.Show("DataSet has errors") Exit Sub End If adapter.Update(tmpDtSet) Now you can use the new DataSet to bind it to data-bound controls or send back to the database to store results. | ||||||||||||||||||||||||||||||||||||||||||||||||||||
A: | Besides the default ExecuteReader method of SqlCommand or other data provider command objects, an overloaded form of ExecuteReader takes an argument of type CommandBehavior enumeration. The CommandBehavior enumeration provides a description of the results of the query and its effect on the database. We've used the SchemaOnly and SequentialAccess members of CommandBehavior members in the samples discussed in FAQ 2. However, the CommandBehavor enumeration has few more members (see Table C-7).
| ||||||||||||||||||||||||||||||||||||||||||||||||||||
A: | Unlike an ADO recordset, the DataReader doesn't provide any property or method that returns the total number of records fetched through a SELECT statement. There are two common ways to find out the total number of records fetched by a DataReader. First, you can use a SELECT COUNT * statement, which was discussed in FAQ 12. Second, you can loop through all the records and store the value in an increment counter. This method is not recommended when there are thousands of records. | ||||||||||||||||||||||||||||||||||||||||||||||||||||
A: | Unlike an ADO recordset, the DataReader doesn't support EOF either. If there aren't any records left in a DataReader, the Read method of the DataReader returns false. | ||||||||||||||||||||||||||||||||||||||||||||||||||||
A: | A DataReader doesn't provide any property or method to sort the records. The easiest way to sort DataReader records is using the ORDER BY clause with the SELECT statement. The ORDER BY clause followed by ASC or DESC sorts in ascending or descending order, respectively. For example, if you want to sort records based on the Name column in descending order, you use the following SQL statement:
SELECT * FROM Table ORDER BY NAME DESC | ||||||||||||||||||||||||||||||||||||||||||||||||||||
A: | The following Uniform Resource Locator (URL) lists the connection string for different data providers: www.able-consulting.com/ado_conn.htm. |