Appendix C: ADO.NET Frequently Asked Questions


Overview

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?

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.

Q:

FAQ 2: How can I obtain table schema using a Command object?

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. 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. idatareader meta-data column name description columnname this is the name of the column. columnordinal this is the ordinal of the column. this is zero for the bookmark column of the row, if any. other columns are numbered starting with one. this column can't contain a null value. columnsize this is the length of the column. numericprecision if providertype is a numeric data type, this is the maximum precision of the column. numericscale if providertype is dbtype_decimal or dbtype_numeric , this is the number of digits to the right of the decimal point. otherwise, this is a null value. datatype this is the data type of the column (the .net framework data type). providertype this is the indicator of column's data type. islong you can use this to find out if a column contains a binary long object (blob) that contains very long data. allowdbnull this is true if a column can contain null values; otherwise, it's false . isreadonly this is true if a column is read only; otherwise, it's false . isrowversion this is true if the column contains a persistent row identifier that can't be written to, and it has no meaningful value except to identity the row. isunique this is true if the column has unique values, which means no two rows can have same values iskeycolumn this is true if the column is one of a set of columns in the rowset that, taken together, uniquely identify the row. isautoincrement this is true if a column is auto-incrementing. baseschemaname this is the name of the schema in the data store that contains the column. this is a null value if you can't determine the base schema name. basecatalogname this is the name of the catalog in the data store that contains the column. this is a null value if you can't determine the base catalog name. basetablename this is the name of the table or view in the data store that contains the column. this is a null value if you can't determine the base table name. basecolumnname this is the name of the column in the data store. this might be different from the column name returned in the columnname column if you used an alias.

Q:

FAQ 3: How can I get rid of the plus (+) sign in a DataGrid?

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];

Q:

FAQ 4: How do I hide a DataTable column?

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;

Q:

FAQ 5: How can I insert dates in a SQL statement?

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')`

Q:

FAQ 6: How can I get columns names and their data types from a DataSet?

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 datatable s. 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. 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

Q:

FAQ 7: How can I find out how many rows are in a DataGrid?

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

Q:

FAQ 8: How do I implement paging in ADO.NET?

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. 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. paging in an ado.net application 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. 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 iftotalpages = 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 ifend 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.

Q:

FAQ 9: How do I implement transactions in the OleDb data provider?

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: 1. after creating an instance of a connection class (no matter what data provider you choose), you call the begintransaction method to start a transaction. the begintransaction method returns a transaction object: dim tran as oledbtransaction = conn.begintransaction() 2. create command objects with the active transaction, set its transaction property as the current transaction, and call its execute method: dim cmd as oledbcommand = new oledbcommand() cmd.connection = conn cmd.transaction = tran 3. call the commit and rollback methods to commit and roll back transactions using this code: tran.commit() tran.rollback() listing c-5 shows you how to use transactions in the oledb data provider. transactions in the oledb data provider sub main() dim str as string dim connectionstring as string = _ `provider=microsoft.jet.oledb.4.0; data source=f:\\appliedadonet.mdb` ' create and open a connection dim conn as oledbconnection = _ new oledbconnection(connectionstring) conn.open() ' start a transaction dim tran as oledbtransaction = conn.begintransaction() dim cmd as oledbcommand = new oledbcommand() cmd.connection = conn cmd.transaction = tran try str = `insert into users (username, userdescription)` & _` values ('new user', 'new description')` cmd.commandtext = str cmd.executenonquery() str = `update users set username = 'updated user' where ` & _ ` username = 'new user'` cmd.commandtext = str cmd.executenonquery() str = `delete * from users where username = 'updated user'` cmd.commandtext = str cmd.executenonquery() ' commit transaction tran.commit() console.writeline(`changes saved.`) catch e as exception' rollback transaction tran.rollback() console.writeline(e.message.tostring()) console.writeline(`no changes were made to database.`) finallyconn.close()conn.dispose() end try end sub

Q:

FAQ 10: How can I create AutoIncrement columns?

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

Q:

FAQ 11: How can I copy a DataSet's contents?

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()

Q:

FAQ 12: How can I use Count (*) to count number of rows?

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())

Q:

FAQ 13: How do I get a database schema using a DataSet programmatically?

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. getting a database schema programmatically 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 = ``; 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. 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. 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 exceptionmessagebox.show(exp.message)finallyconn.close()conn.dispose()end tryend sub

Q:

FAQ 14: How does SELECT DISTINCT work, and how is it related to duplicates?

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. 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

Q:

FAQ 15: How do I read and write to the Windows Registry in .NET?

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. the registry classes are defined in the microsoft.win32 namespace. before using these classes, you need to add a reference to this namespace. using the registry class the registry class contains members to provides access to registry keys. table c-2 described the registry class members. the registry class members member description currentuser stores information about user preferences localmachine stores configuration information for the local machine classesroot stores information about types (and classes) and their properties users stores information about the default user configuration performancedata stores performance information for software components currentconfig stores non-user-specific hardware information dyndata stores dynamic data 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. the registrykey class properties property description name name of the key subkeycount number of children of the key valuecount count of values in the key table c-4 describes the registrykey class methods. the registrykey class methods method description close closes the key createsubkey creates a new subkey if not in existence or opens it deletesubkey deletes a subkey deletesubkeytree deletes subkey and its children deletevalue deletes the value of a key getsubkeyname returns an array of strings that contains all the subkey names getvalue returns the value of a subkey getvaluename retrieves an array of strings that contains all the value names associated with a key opensubkey opens a subkey setvalue sets the value of a subkey 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. 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

Q:

FAQ 16: How can I obtain ODBC Data Source Names (DSNs)?

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. 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

Q:

FAQ 17: How can I read and write bitmaps or BLOB data?

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. users table schema 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. reading and writing images in a database final form 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. 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. save image button click event handler private sub saveimagebtn_click(byval sender as system.object, _ byval e as system.eventargs) handles saveimagebtn.clickif 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. 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 ifdim 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 . displaying a bitmap after reading data from a database

Q:

FAQ 18: What are DiffGrams and how do they relate to DataSets? How do I read and write DiffGrams using a DataSet?

the following sections discuss the basics of diffgram s 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 diffgram s are useful. do you remember the getchanges method of a dataset ? this method returns the rows that have been modified in the current version in the form of a dataset . this is how a dataset knows the modified rows. 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 diffgram s 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 . a diffgram format -?xml version=`1.0`?- -diffgr:diffgramxmlns: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 ). the diffgram elements element description id this is the diffgram id, which is usually in the format of [tablename][rowidentifier] . for example: -customers diffgr: parented this is the parent row of the current row, which is usually in the format of [tablename][rowidentifier] . for example: -orders diffgr:parent haschanges this identifies a row in the -datainstance- block as modified. the haschanges element can have one of the three values: inserted , modified , or descent . the value inserted means an added row, modified means a modified row, and descent means the children of a parent row have been modified. haserrors this identifies a row in the -datainstance- block with a rowerror . the error element is placed in the -diffgr:errors- block. error this contains the text of the rowerror for a particular element in the -diffgr:errors- block. 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. reading diffgram s 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 . diffgram format 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. the datarowstate members member description added add added rows to a datarowcollection of a dataset and acceptchanges has not been called. deleted all the deleted rows. detached rows were created but not added to the row collection. either waiting for the addition to or removal from the collection. modified modified rows and acceptchanges has not been called. unchanged unchanged rows since last acceptchanges was called. listing c-17 copies only modified rows of ds to a new dataset called tmpdtset . 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.

Q:

FAQ 19: How does CommandBehavior work?

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 ). the commandbehavior enumeration member description closeconnection the connection object associated with the datareader is closed when the datareader is closed. default default option. no effects. hence, calling executereader with commandbehavior.default returns the same result as calling executereader with no parameters. keyinfo returns column and primary key information. you should use this option when you need to read the information about a table's columns and primary key. schemaonly returns column information only. useful when you want to know a table schema. we used this option in faq 2 to read a database table schema. sequentialaccess used to read rows that contain columns with large binary values. the sequentialaccess option allows the datareader to read data in a stream. we use this option in listing c-14 , where we read binary data. singleresult returns a single result set. singlerow returns a single row.

Q:

FAQ 20: How can I count total number of records in a DataReader?

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.

Q:

FAQ 21: How can I find out the end of file in a DataReader?

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 .

Q:

FAQ 22: How can I sort records using a DataReader?

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

Q:

FAQ 23: What's the fastest way to figure out connection strings for different data providers?

the following uniform resource locator (url) lists the connection string for different data providers: www.able-consulting.com/ado_conn.htm .

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

start example
 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() 
end example

The GetSchemaTable method returns meta-data about each column. Table C-1 defines the metadata and column order.

Table C-1: IDataReader Meta-Data

COLUMN NAME

DESCRIPTION

ColumnName

This is the name of the column.

ColumnOrdinal

This is the ordinal of the column. This is zero for the bookmark column of the row, if any. Other columns are numbered starting with one. This column can't contain a null value.

ColumnSize

This is the length of the column.

NumericPrecision

If ProviderType is a numeric data type, this is the maximum precision of the column.

NumericScale

If ProviderType is DBTYPE_DECIMAL or DBTYPE_NUMERIC, this is the number of digits to the right of the decimal point. Otherwise, this is a null value.

DataType

This is the data type of the column (the .NET Framework data type).

ProviderType

This is the indicator of column's data type.

IsLong

You can use this to find out if a column contains a Binary Long Object (BLOB) that contains very long data.

AllowDBNull

This is true if a column can contain null values; otherwise, it's false.

IsReadOnly

This is true if a column is read only; otherwise, it's false.

IsRowVersion

This is true if the column contains a persistent row identifier that can't be written to, and it has no meaningful value except to identity the row.

IsUnique

This is true if the column has unique values, which means no two rows can have same values

IsKeyColumn

This is true if the column is one of a set of columns in the rowset that, taken together, uniquely identify the row.

IsAutoIncrement

This is true if a column is auto-incrementing.

BaseSchemaName

This is the name of the schema in the data store that contains the column. This is a null value if you can't determine the base schema name.

BaseCatalogName

This is the name of the catalog in the data store that contains the column. This is a null value if you can't determine the base catalog name.

BaseTableName

This is the name of the table or view in the data store that contains the column. This is a null value if you can't determine the base table name.

BaseColumnName

This is the name of the column in the data store. This might be different from the column name returned in the ColumnName column if you used an alias.

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

start example
 ' 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 
end example

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

start example
 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 
end example

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.

click to expand
Figure C-1: Paging in an ADO.NET application

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

start example
 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 
end example

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:

  1. After creating an instance of a Connection class (no matter what data provider you choose), you call the BeginTransaction method to start a transaction. The BeginTransaction method returns a Transaction object:

     Dim tran As OleDbTransaction = conn.BeginTransaction() 

  2. Create Command objects with the active transaction, set its Transaction property as the current transaction, and call its Execute method:

     Dim cmd As OleDbCommand = New OleDbCommand() cmd.Connection = conn cmd.Transaction = tran 

  3. Call the Commit and Rollback methods to commit and roll back transactions using this code:

     tran.Commit() tran.Rollback() 

    Listing C-5 shows you how to use transactions in the OleDb data provider.

    Listing C-5: Transactions in the OleDb Data Provider

    start example
     Sub Main() Dim str As String Dim connectionString As String = _     "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=F:\\AppliedAdoNet.mdb" ' Create and open a connection Dim conn As OleDbConnection = _   New OleDbConnection(connectionString) conn.Open() ' Start a transaction Dim tran As OleDbTransaction = conn.BeginTransaction() Dim cmd As OleDbCommand = New OleDbCommand() cmd.Connection = conn cmd.Transaction = tran Try     str = "INSERT INTO Users (UserName, UserDescription)" & _      " VALUES ('New User', 'New Description')"     cmd.CommandText = str     cmd.ExecuteNonQuery()     str = "UPDATE Users SET UserName = 'Updated User' WHERE " & _           " UserName = 'New User'"     cmd.CommandText = str     cmd.ExecuteNonQuery()     str = "DELETE * FROM Users WHERE UserName = 'Updated User'"     cmd.CommandText = str     cmd.ExecuteNonQuery()     ' Commit transaction     tran.Commit()     Console.WriteLine("Changes saved.")     Catch e As Exception      ' Rollback transaction       tran.Rollback()       Console.WriteLine(e.Message.ToString())       Console.WriteLine("No changes were made to database.")     Finally        conn.Close()        conn.Dispose()     End Try End Sub 
    end example

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.

click to expand
Figure C-2: Getting a database schema programmatically

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

start example
 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 
end example

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

start example
 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 
end example

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

start example
 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 
end example

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

start example
 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 
end example

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.

Note

The Registry classes are defined in the Microsoft.Win32 namespace. Before using these classes, you need to add a reference to this namespace.

Using the Registry Class

The Registry class contains members to provides access to Registry keys. Table C-2 described the Registry class members.

Table C-2: The Registry Class Members

MEMBER

DESCRIPTION

CurrentUser

Stores information about user preferences

LocalMachine

Stores configuration information for the local machine

ClassesRoot

Stores information about types (and classes) and their properties

Users

Stores information about the default user configuration

PerformanceData

Stores performance information for software components

CurrentConfig

Stores non-user-specific hardware information

DynData

Stores dynamic data

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-3: The RegistryKey Class Properties

PROPERTY

DESCRIPTION

Name

Name of the key

SubKeyCount

Number of children of the key

ValueCount

Count of values in the key

Table C-4 describes the RegistryKey class methods.

Table C-4: The RegistryKey Class Methods

METHOD

DESCRIPTION

Close

Closes the key

CreateSubKey

Creates a new subkey if not in existence or opens it

DeleteSubKey

Deletes a subkey

DeleteSubKeyTree

Deletes subkey and its children

DeleteValue

Deletes the value of a key

GetSubKeyName

Returns an array of strings that contains all the subkey names

GetValue

Returns the value of a subkey

GetValueName

Retrieves an array of strings that contains all the value names associated with a key

OpenSubKey

Opens a subkey

SetValue

Sets the value of a subkey

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

start example
 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 
end example

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

start example
 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 
end example

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.


Figure C-3: Users table schema

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.

click to expand
Figure C-4: Reading and writing images in a database final form

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

start example
 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 
end example

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

start example
 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 
end example

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

start example
 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 
end example

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.

click to expand
Figure C-5: Displaying a bitmap after reading data from a database

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.

Note

Do you remember the GetChanges method of a DataSet? This method returns the rows that have been modified in the current version in the form of a DataSet. This is how a DataSet knows the modified rows.

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

start example
 <?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> 
end example

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).

Table C-5: The DiffGram Elements

ELEMENT

DESCRIPTION

id

This is the DiffGram ID, which is usually in the format of [TableName][RowIdentifier]. For example: <Customers diffgr:>.

parented

This is the parent row of the current row, which is usually in the format of [TableName][RowIdentifier]. For example: <Orders diffgr:parent>.

hasChanges

This identifies a row in the <DataInstance> block as modified. The hasChanges element can have one of the three values: inserted, modified, or descent. The value inserted means an added row, modified means a modified row, and descent means the children of a parent row have been modified.

hasErrors

This identifies a row in the <DataInstance> block with a RowError. The error element is placed in the <diffgr:errors> block.

error

This contains the text of the RowError for a particular element in the <diffgr:errors> block.

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

start example
 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") 
end example

The output of Listing C-16 looks like Figure C-6.

click to expand
Figure C-6: DiffGram format

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.

Table C-6: The DataRowState Members

MEMBER

DESCRIPTION

Added

Add added rows to a DataRowCollection of a DataSet and AcceptChanges has not been called.

Deleted

All the deleted rows.

Detached

Rows were created but not added to the row collection. Either waiting for the addition to or removal from the collection.

Modified

Modified rows and AcceptChanges has not been called.

Unchanged

Unchanged rows since last AcceptChanges was called.

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

start example
 ' 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) 
end example

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).

Table C-7: The CommandBehavior Enumeration

MEMBER

DESCRIPTION

CloseConnection

The Connection object associated with the DataReader is closed when the DataReader is closed.

Default

Default option. No effects. Hence, calling ExecuteReader with CommandBehavior.Default returns the same result as calling ExecuteReader with no parameters.

KeyInfo

Returns column and primary key information. You should use this option when you need to read the information about a table's columns and primary key.

SchemaOnly

Returns column information only. Useful when you want to know a table schema. We used this option in FAQ 2 to read a database table schema.

SequentialAccess

Used to read rows that contain columns with large binary values. The SequentialAccess option allows the DataReader to read data in a stream. We use this option in Listing C-14, where we read binary data.

SingleResult

Returns a single result set.

SingleRow

Returns a single row.

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.




Applied ADO. NET(c) Building Data-Driven Solutions
Applied ADO.NET: Building Data-Driven Solutions
ISBN: 1590590732
EAN: 2147483647
Year: 2006
Pages: 214

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net