Ado.Net - this page teaches the basics and reminds on some best practices
(current on Nov 2002)
|
Ado.Net - this page teaches the basics and reminds on some best practices (current on Nov 2002) |
|
All these notes are taken from the microsoft docn. It is a crib sheet for data access and forms. If you want to go to the original then please follow the links, or just go to the bottom of the page and follow the links there.
You can use datasets or datacommands. This page only worries about datasets. But best practices recons that stateless components/code such as web services and web pages should probably use datacommands, while GUI's should use DataSet's.
Before diving into the text, take a glance at an example file: TheMain.cs
Connections connect to the database using a connection string. A dataset is a description of tables and columns, relationships and pkeys. A dataadaptor needs a connection and a select query (or set of queries). Once a dataadaptor has the select query it can Fill the dataset. You can now access the content of the dataset by table name, row number and column name. You can use a command builder to generate the update and delete commands of the dataadaptor.
Additionally, you can bind properties of form components to the column value of the 'current' row within a table in a data set. The current row is set using the CurrencyManager. Every form keeps CurrencyManagers for every bound dataset within it. Lists and so on can be bound to the dataset tables, text boxes and so on can be bound to columns. You can even display a column, preserving a mapping to the value, so the user sees a nice view of the rows. All of this is built in and easy to use (once you have done it two or three times).
The DataSet tied to the GUI components will keep a record of items which have changed, been added and been deleted. You can use the dataadaptor to comit the dataset back into the database - using the commandbuilder to create the sql needed.
OK, there is loads of detail later in the page, but no single example anywhere of a full implementation. And I may get round to this. But until then here is some natural language showing what you must do.
create a form create the components in the form on_loading the form: Define a dataset using vs.net - table(s), constraints, fkeys, pkeys etc, any paging needed? Connect to the database using a connection string Create a dataadaptor giving it the connection and a select statement Use the dataadaptor to populate the dataset Use a commandBuilder to set the insert,update and delete commands of the dataadaptor Create a guicopy of the dataset Add validating event handlers to column and row update events Use GUID's and NOT autoincrement pkeys otherwise you will need a stored proc for inserts. Bind the form components to the guicopy of the form Add event handlers to reload the guicopy (ie a revert button) Add a store button event handler which: Starts a transaction creates a dataset for child tables first uses the dataadaptor to update the db completes the transaction accepts all changes to the local dataset Handles concurrency error and roles back the transaction if there are errors Add other buttons and menus etc.
Visual Studio lets you do create DataSets graphically. These have XML schemas. The data sets are NOT tied to a database, they are simply a description of objects. So, VS.Net will create objects that match the XML schema. You must still handle filling the dataset with data.
Data Sets can contain more than one table. DataSets created with VS.Net are 'typed' which means you can access column names and they will be correctly typed - eg
// C# This accesses the CustomerID column in the first row of
the Customers table.
string s = dsCustomersOrders1.Customers[0].CustomerID;
The equivalent 'un-typed' dataset access would be (you create untyped ones in code)
string s = (string) dsCustomersOrders1.Tables["Customers"].Rows[0]["CustomerID"];
Data sets support paging.... use it! Well use it if you have a large data set... Paging is done via properties.
SQLConnection class need a connection string, and there are loads of formats for this. There are two kinds
SqlConnection is quicker!
// bad version - mixed mode sqlserver authentication, uid and passwd (blank sa account passwd no less!) String con_str= "Server=192.168.0.16\\TALLPRIV;database=talltest;uid=sa;pwd=;"; // Other version using integrated security. String con_str2= "Provider=SQLOLEDB.1;Data Source=MySQLServer;Initial Catalog=NORTHWIND;Integrated Security=SSPI";
SqlConnection con = new SqlConnection(); con.ConnectionString=con_str;
Data Sets will open and close the connections as it needs to - you do not have to manage this. BUT, you may also open and close them yourself if you want. And ...
From the best practices stuff: "Always explicitly close your Connection or DataReader objects when you are finished using them." Which is kinda wierd because Ado.Net can does this for you automatically - by making a best guess. But stick with best practice and close them yourself!
Connection pooling is supported as part of the .Net framework automatically. "If you are using the SqlConnection class, connection pooling is managed implicitly, but also provides options that allow you to manage pooling yourself. For more information, see Connection Pooling for the SQL Server .NET Data Provider."
Connection objects support transactions with a BeginTransaction method that creates a transaction object (an OleDbTransaction or SqlTransaction object). The transaction object in turn supports methods that allow you to commit or roll back the transactions.
Here is the only example I found on msdn of transaction and dataadaptors. I changed it because theirs would never work, and I have yet to test this, because I've not seen anyone else do this....
//C# - a rubbish example from msdn, I do not believe it works.
public void RunSqlTransaction(SqlDataAdapter da, SqlConnection myConnection, DataSet ds)
{
myConnection.Open();
SqlTransaction myTrans = myConnection.BeginTransaction();
da.InsertCommand.Transaction = myTrans;
da.UpdateCommand.Transaction = myTrans;
da.DeleteCommand.Transaction = myTrans;
try {
da.Update(ds);
myTrans.Commit();
Console.WriteLine("Update successful.");
} catch(Exception e) {
try {
myTrans.Rollback();
} catch (SqlException ex) {
if (myTrans.Connection != null) {
Console.WriteLine("An exception of type " + ex.GetType() +
" was encountered while attempting to roll back the transaction.");
}
}
Console.WriteLine(e.ToString());
Console.WriteLine("Update failed.");
}
myConnection.Close();
}
More notes I found are from this bit of msdn:
"For a .NET Framework object to participate in an automatic transaction, the .NET Framework class must be registered with Windows 2000 Component Services. However, not all transactions are automatic. The activities you perform when programming transactions depend on the transaction model you choose. The common language runtime supports both manual and automatic transaction models."
The automated transaction stuff is really really rubbish when
compared with java - embedding it in the OS is bad IMHO. Take a read
of it and weep. I guess this is why all ms projects resort to stored procs.
This is not my own work, just a summary of others, look at the other page for details.
'when working with auto-incrementing columns at a data source and auto-incrementing columns in a DataSet, you create the column in the DataSet with an AutoIncrementStep of -1 and an AutoIncrementSeed of 0, as well as ensuring that your data source generates auto-incrementing identity values starting from 1 and incrementing with a positive step value. As a result, the DataSet generates negative numbers for auto-incremented values that do not conflict with the positive auto-increment values generated by the data source.'
However, this will not solve the problem of how to ensure your local dataset has the correct pkeys after insert. That is a problem solved by a trick shown below. The other way to do it is to use the update callback - as shown in the example code.
'Change the insert command text to include a select command as well, and make sure the insert command's UpdatedRowSource is set to UpdateRowSource.FirstReturnedRecord. You must also edit the datasourve to make sure that the column is not marked read only.
"INSERT INTO eg_tab(name, addr) VALUES (@name, " +
"@addr); SELECT id, name, addr" +
" FROM eg_tab WHERE (id =@@IDENTITY)";
If you're using SQL Server 7.0 or earlier, use @@IDENTITY instead of SCOPE_IDENTITY(). For more information on the difference between these features, see SQL Server Books OnLine.'
When sending a null value as a Parameter value in a command to the database, you cannot use null (Nothing in Visual Basic® .NET). Instead you need to use DBNull.Value. For example:
//C#
SqlParameter param = new SqlParameter("@Name", SqlDbType.NVarChar,
20);
param.Value = DBNull.Value;
A dataadapter takes the connection and a query, and from this populates a dataset, or possibly many queries. Note that the query still has to be written even though you used vs.net to create the dataset by dragging tables etc.
string query = "SELECT id, description FROM CONSOURCE";
SqlDataAdapter da = new SqlDataAdapter(query, con);
da.Fill(dsCustomersOrders1, "CONSource");
SQLCommandBuilder objects can be used to autogenerate update, delete commands etc. It does this from the selects - no joins please. You simply give it the data adaptor and it does its business.
SqlCommandBuilder bld = new SqlCommandBuilder(da);
da.InsertCommand = bld.GetInsertCommand();
da.UpdateCommand = bld.GetUpdateCommand();
da.DeleteCommand = bld.GetDeleteCommand();
System.Console.WriteLine(da.InsertCommand.CommandText);
System.Console.WriteLine(da.UpdateCommand.CommandText);
System.Console.WriteLine(da.DeleteCommand.CommandText);
Windows Forms and data is pretty fine when using data sets!
Each form has a binding context which manages all the property and currency managers within it. A PropertyManager maps a property of a GUI component to a column value. A CurrencyManager records the position within a DataSet - ie the row we are on. You can have many CurrencyManagers, you can even create your own BindingContexts for sub-panels/tabs etc.
eg:
text1.DataBindings.Add(new Binding("Text", ds."Customers.Name");
This binds the Text property of a text box to the Name column of the Cusomers table.
this.BindingContext[dspubs1,"authors"].Position=1;
this -> the form
BindingContext -> managers all the CurrencyManager classes in the form, and we are indexing the authors table within the dspubs1 dataset. Setting the Position to 1. So any gui component bound to the authors table will update to the new row (i.e. position 1).
Other ways of binding data to Gui components:
// C#
comboBox1.DataSource = DataSet1.Customers;
comboBox1.DisplayMember = "FirstName";
this.BindingContext[dataSet1.Customers].Position = 1;
Basically, lists and combo boses etc can display names of things, and keep a mapping internally to the pkey. Ands this is done using DataSource, DisplayMember and ValueMember properties, followed by adding a data binding. Take a look at the links for full info.
listBox1.DataBindings.Add("SelectedValue", OrderDetailsTable, "ItemID");
When a dataset is bound to the GUI then you need do nothing. The GUI will update the dataset itself. But, as this is a crib sheet, here is how to do it in code (for typed datasets):
// C# update - with begin/end to turn off constraint checking
dsCustomers1.Customers[4].BeginEdit();
dsCustomers1.Customers[4].CompanyName = "Wingtip Toys";
dsCustomers1.Customers[4].City = "Buffalo";
dsCustomers1.Customers[4].EndEdit();
// C# Insert
DataRow anyRow = DatasetName.ExistingTable.NewRow();
anyRow.FirstName = "Jay";
anyRow.LastName = "Stevens";
ExistingTable.Rows.Add(anyRow);
// C# Delete
DsCustomers1.Customers.Rows[0].Delete();
You can add event handlers to track column, row and table changes. You can add validation to column, row and tables, you can accept changes to rows, tables and datasets. You can suspend checking, resume checking, merge datasets and so on.
"By default, each change to a column therefore raises four events: first the ColumnChanging and ColumnChanged events for the specific column being changed, and then the RowChanging and RowChanged event. If multiple changes are being made to the row, the events will be raised for each change.
Note The data row's BeginEdit method turns off the RowChanging and RowChanged
events after each individual column change. In that case, the event is not raised
until the EndEdit method has been called, when the RowChanging and RowChanged
events are raised just once. For more information, see Suspending Update Constraints.
The event you choose depends on how granular you want the validation to be.
If it is important that you catch an error immediately when a column is changed,
build validation using the ColumnChanging event. Otherwise, use the RowChanging
event, which might result in catching several errors at once. Additionally,
if your data is structured in such a way that the value of one column is validated
based on the contents of another column then you should perform your validation
during the RowChanging event."
When you want to persist the changes you have to Update the database, and then discard change info from the dataset - ie update, and then accept changes.
// C# - how to update and accept changes - noddy version (see
below)
OleDbDataAdapter1.Update(Dataset1, "Customers");
Dataset1.Customers.AcceptChanges(); // or, if needed use Fill, or use the refresh
the data set option in the adaptor
If your dataset contains multiple tables, you have to update them individually by calling the Update method of each data adapter separately. If the tables have a parent-child relationship, it is likely that you will have to send updates to the database in a particular order.
In many circumstances, after updating a data source, you will want to refresh the dataset by repopulating it. Doing so has several benefits:
It refreshes the timestamp on records in the dataset, in case you want to use the timestamp for concurrency control. You can refresh a dataset manually by calling an adapter's Fill method after calling its Update method. OR set the Refresh the DataSet option in the Advanced SQL Generations Options dialog box - means the adaptor uses the SELECT statement to refresh the dataset after the update has occurred. It is generated when you set the Refresh the DataSet option in the Advanced SQL Generations Options dialog box.
Example of updating the db :
// C# A better update function, with child tables first, and error handling
void Update_Attempt()
{ // start a transaction? How do you use transactions and data adaptors? Hmmmm. // none of their standard eg's shows transactions and dataadaptors. maybe ms are too thick for this? // take a look at my previous example with transactions.
// Set ds1 to contain only deleted records from the child table.
DataTable ds1 =
anyDataset.ChildTableName.GetChanges(DataRowState.Deleted);
// Set ds2 to contain only new records from the child table.
DataTable ds2 =
anyDataset.ChildTableName.GetChanges(DataRowState.Added);
// Set ds3 to contain only edited records from the child table.
DataTable ds3 =
anyDataset.ChildTableName.GetChanges(DataRowState.Modified);
try {
DataAdapter2.Update(ds1);
DataAdapter1.Update(anyDataset, "ParentTable");
DataAdapter2.Update(ds2);
DataAdapter2.Update(ds3);
anyDataset.AcceptChanges();
ds1.Dispose();
ds2.Dispose();
ds3.Dispose();
} catch (DBConcurrencyException ex) {
string customErrorMessage = ex.Message + ex.Row[0].ToString();
MessageBox.Show(customErrorMessage, ex.GetType().ToString());
} catch (Exception x) {
MessageBox.Show(x.Message, x.GetType().ToString());
}
}
Or dirty data, is a big concern, and one you have to code for in all cases, so make sure you do, and do it as in the link above! Basically they say catch the exception and do something - either discarding the users changes, or allowing them to overwrite the db via loading a new dataset from the db and merging the local one into it and then calling update again.
Note about updates: If your application does not use the RowUpdated or ColumnUpdated event handlers to catch errors during the update process, you will need to find the error programmatically.
// C#
private void Find_Errors2() {
DataSet anyDataset = null;
foreach (DataTable dt in anyDataset.Tables) {
foreach (DataRow dr in dt.Rows){
if (dr.HasErrors) {
// Row found! Add code to reconcile error.
dr.ClearErrors();
// Re-attempt the update.?
}
}
}
}
This crib sheet is the result of about 6 hours reading, mainly from the MSDN site. The links are in the page above, and also 'stuff' to do with this is below:
Read this first! And read ALL subsections.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vbcondatanavigationinwindowsforms.asp
And datasource connection stuff:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vboriadonetconnections.asp
And data binding info:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vbconinterfacesrelatedtodatabinding.asp
DataGrid usage: http://support.microsoft.com/default.aspx?scid=kb;en-us;Q316303
Creating a lookup form:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vbtskcreatinglookuptableforlistboxorcomboboxcontrol.asp
DataSet updates and how it works
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vbconusingdatacommandversususingdatasets.asp