A few misc tricks with ADO.NET 2.0
I also have this posted at http://www.knowdotnet.com/articles/miscadonet.html (with a lot better formatting ;-))
Eariler tonight I started playing with some more ADO.NET 2.0 code and just started mixing it up. It appears the ASP.NET 2.0 team has done such a good job with ASP.NET 2.0 that you don't really need to write any code for it anymore, so I can spend my time elsewhere. There are two new ADO.NET 2.0 Features that are pretty cool - although I probably won't be using the first much:
Generic DB Providers:
private
DbConnection cn;
private
void button1_Click(object sender, EventArgs e)
{
DbProviderFactory myFactory = DbProviderFactories.GetFactory("System.Data.SqlClient");
DbDataSourceEnumerator eNum = myFactory.CreateDataSourceEnumerator();
DataTable providers = eNum.GetDataSources();
dgv.DataSource = providers;
//At this point, it shows Bill_2k3 - the only instance of SQL Server
//Running internally on my network
using (cn = myFactory.CreateConnection())
{
cn.ConnectionString =
"Data Source=localhost;Integrated Security=SSPI;Initial Catalog=xxxxxxx";
try
{
cn.Open();
DbCommand cmd = myFactory.CreateCommand();
cmd.Connection = cn;
cmd.CommandText =
"SELECT * FROM Customers";
DbDataReader dr = cmd.ExecuteReader();
}
//Notice we're catching a Generic DBException object
catch (DbException ex)
{
Debug.Assert(false, ex.ToString());
}
finally {
cn.Close();
}
}
}
New DataReader Functionality:
As you can see, it's a pretty generic impelementation. The only thing that's really hard coded is the GetFactory method, but that's just b/c I didn't have a UI that I wanted to use. I could have selected something out of a grid or ListView for instance, passed it into a method and used that value as a parameter.
Two things come to mind. One is that connection strings vary from provider to provider so you do need to know a little about what you are using. The next is that you don't have very limited functionality. For instance, there are NO events other than .Dispose for DbConnection. That's not surprising but it's worth mentioning - after all, how would Access implement the InfoMessage event when it's specific to SqlServer?
The Next neat piece is the ability to populate a DataTable from a DataReader. Now, you may be aware that the DataAdapter objects use DataReaders behind the scenes to fill DataTables/DataSets, so what' the big deal? Well, you don't need an Adapter at all to populate a DataTable. For read only situation where you need an IEnumerable object for instance, you can take advantage of the performance benefits and flexibilty associated with this:
private
DbConnection cn;
private
void button1_Click(object sender, EventArgs e)
{
DbProviderFactory myFactory = DbProviderFactories.GetFactory("System.Data.SqlClient");
DbDataSourceEnumerator eNum = myFactory.CreateDataSourceEnumerator();
DataTable providers = eNum.GetDataSources();
dgv.DataSource = providers;
//At this point, it shows Bill_2k3 - the only instance of SQL Server
//Running internally on my network
using (cn = myFactory.CreateConnection())
{
DbConnectionStringBuilder csb = new DbConnectionStringBuilder(;
cn.ConnectionString =
"Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind";
try
{
cn.Open();
DbCommand cmd = myFactory.CreateCommand();
cmd.Connection = cn;
cmd.CommandText =
"SELECT * FROM Customers";
DbDataReader dr = cmd.ExecuteReader();
//cn.Close();
DataTable dt = new DataTable("BillsTest");
Debug.WriteLine(dt.Rows.Count.ToString());// Returns 0 proving it's empty
dt.Load(dr);
Debug.WriteLine(dt.Rows.Count.ToString());// Returns 91
//That's right, we just filled a DataTable without a DataAdapter and without iterating through
//something to fill it.
DbDataReader drTwo = dt.GetDataReader();
Debug.Assert(drTwo.HasRows);
while (drTwo.Read())
{
lb.Items.Add((
string)drTwo.GetString(0));
Debug
.WriteLine(lb.Items.Count.ToString());// Returns 91
}
Debug.WriteLine(dt.Rows.Count.ToString());// Returns 91
}
//Notice we're catching a Generic DBException object
catch (DbException ex)
{
Debug.Assert(false, ex.ToString());
}
cn.Close();
}
}
I think what I'm doing here is pretty straightforward so I'm not going to go into too much depth. Basically, you can turn a DataTable into a DataReader or vice versa. The .Load() method takes a DataReader object and loads it into a DataTable. .GetDataReader does the exact opposite. Notice though that I commented out the .Close() command right after ExecuteReader. If I don't do this, everything will still work, at least in the sense of no Exceptions. However you still need an open connection if you want data back from the db. So by uncommenting that line, the program will still run, it just won't load any data into the listbox. I'm a bit uncomfortable with this b/c it could be easy to overlook - God knows I've done it with DataReaders enough in the earlier versions of the framework and the exception was the main thing that let me know about it.