Introduction
Do you want/need to use ODBC.NET? Call stored procedures? Populate a DataGrid
on a Windows Form? Do your stored procs have return values or output parameters?
If the answer is YES then read on....
Having played around with .NET for a while, I thought it was finally time to get down to some serious coding. Most of my work involves writing and maintaining desktop client-server apps. For some reason the fashion is to be call these "n-tier" apps these days. Whatever - they all boil down to the same thing. Get data from the database onto the screen, let the user do something with it and write the changes back to the DB. OK - fairly vanilla stuff, or so I thought. So here's what I wanted to achieve :
- Win forms application with a
DataGrid
on it
- ODBC.NET database layer, talking to Sybase via the Merant 3.60 driver (the most recent driver my company provides !)
- Stored procedures to perform
Select
, Insert
, Update
and Delete
DataAdapter
to hook the grid to the database, using the Select/Insert/Update/Delete command properties to fire the stored procs.
- Insert stored procedure returning Primary Key from DB and inserted into the
DataSet
Background
Microsoft added ODBC support to .NET as a post-release download. Out of the box .NET ships with Sql Server, Oracle and OLE-DB drivers. I'm sure Microsoft would love ODBC to roll over and die, however, this is unlikely to happen and I suspect ODBC will be here for many years to come. That said, I do believe Microsoft is right to try and ween people off of ODBC, I'd prefer the "Cold Turkey" approach, but the folks at Microsoft are far too accommodating to large corporate accounts to ever do that. So if, like me, your company has an ODBC habit it just can't kick then read on.
Using the code
I have wrapped up an entire project in the code download (see link at top of article). I'll go through some of the code in this article, but I recommend that you download and look at the sample application. I have only tried this using the Merant 3.60 Sybase ODBC driver, connecting to a Sybase 11.9.2 server. You'll have to substitute your own particular ODBC connection string.
The application consists of a single Windows Form with a DataGrid
and Button
on it. You will need to have the ODBC.NET library installed (this can be downloaded from the Microsoft web site here ). And make sure you add Microsoft.Odbc
as a reference to your project. Now we can start on the code.
First off - get your using statements in...
.
.
using System.Data;
using System.Data.Common;
using Microsoft.Data.Odbc;
.
.
We need a DataSet
and an OdbcDataAdapter
. Remember DataAdapter
's are specific to the database library, the DataSet
is part of System.Data
and can be used with any of the database specific libraries - look in the ADO.NET documentation for more info on this.
The DataSet
and DataAdapter
are private to the form.
private OdbcDataAdapter _da;
private DataSet _ds;
For simplicity I've put the bulk of my code in the Form Load event.
private void Form2_Load(object sender, System.EventArgs e)
{
_da = new OdbcDataAdapter();
_ds = new DataSet();
OdbcCommand _cmdSel = new OdbcCommand();
OdbcCommand _cmdIns = new OdbcCommand();
OdbcCommand _cmdDel = new OdbcCommand();
OdbcCommand _cmdUpd = new OdbcCommand();
OdbcConnection _cn = new OdbcConnection
("DRIVER={MERANT 3.60 32-BIT Sybase};
SERVER=ODBC1;UID=odbc;
PWD=odbc;DATABASE=myodbc;");
_da.InsertCommand = _cmdIns;
_da.UpdateCommand = _cmdUpd;
_da.SelectCommand = _cmdSel;
_da.DeleteCommand = _cmdDel;
_cmdSel.CommandText = "{call odbc_sel}";
_cmdSel.CommandType = CommandType.StoredProcedure;
_cmdSel.Connection = _cn;
_cmdIns.CommandText = "{?=call odbc_ins (?, ?, ?, ?, ?, ?, ?)}";
_cmdIns.CommandType = CommandType.StoredProcedure;
_cmdIns.Connection = _cn;
_cmdIns.Parameters.Add(new OdbcParameter("RETURN_VALUE",
OdbcType.SmallInt,2,ParameterDirection.ReturnValue,
false,0,0,"registrar_id",DataRowVersion.Current,null));
_cmdIns.Parameters.Add(new OdbcParameter("registrar_name",
OdbcType.VarChar,50,"registrar_name"));
_cmdIns.Parameters.Add(new OdbcParameter("file_dir_name",
OdbcType.VarChar,50,"file_dir_name"));
_cmdIns.Parameters.Add(new OdbcParameter("reg_holder_col",
OdbcType.SmallInt,2,"reg_holder_col"));
_cmdIns.Parameters.Add(new OdbcParameter("account_col",
OdbcType.SmallInt,2,"account_col"));
_cmdIns.Parameters.Add(new OdbcParameter("shareholding_col",
OdbcType.SmallInt,2,"shareholding_col"));
_cmdIns.Parameters.Add(new OdbcParameter("loadcode_col",
OdbcType.SmallInt,2,"loadcode_col"));
_cmdIns.Parameters.Add(new OdbcParameter("total_cols_in_file",
OdbcType.SmallInt,2,"total_cols_in_file"));
_cmdDel.CommandText = "{call odbc_del (?)}";
_cmdDel.CommandType = CommandType.StoredProcedure;
_cmdDel.Connection = _cn;
_cmdDel.Parameters.Add(new OdbcParameter("registrar_id",
OdbcType.SmallInt,2,"registrar_id"));
_cmdUpd.CommandText = "{call odbc_upd (?,?,?,?,?,?,?,?)}";
_cmdUpd.CommandType = CommandType.StoredProcedure;
_cmdUpd.Connection = _cn;
_cmdUpd.Parameters.Add(new OdbcParameter("registrar_id",
OdbcType.SmallInt ,2,"registrar_id"));
_cmdUpd.Parameters.Add(new OdbcParameter("registrar_name",
OdbcType.VarChar,50,"registrar_name"));
_cmdUpd.Parameters.Add(new OdbcParameter("file_dir_name",
OdbcType.VarChar,50,"file_dir_name"));
_cmdUpd.Parameters.Add(new OdbcParameter("reg_holder_col",
OdbcType.SmallInt,2,"reg_holder_col"));
_cmdUpd.Parameters.Add(new OdbcParameter("account_col",
OdbcType.SmallInt,2,"account_col"));
_cmdUpd.Parameters.Add(new OdbcParameter("shareholding_col",
OdbcType.SmallInt,2,"shareholding_col"));
_cmdUpd.Parameters.Add(new OdbcParameter("loadcode_col",
OdbcType.SmallInt,2,"loadcode_col"));
_cmdUpd.Parameters.Add(new OdbcParameter("total_cols_in_file",
OdbcType.SmallInt,2,"total_cols_in_file"));
_da.Fill(_ds);
_ds.Tables[0].Columns[0].ReadOnly = true;
this.dataGrid1.DataSource = _ds.Tables[0];
}
Last but not least - Update. The Form's button is used to call Update on the DataAdapter
. When you call Update, the DataAdapter
will take care of everything for you - Inserts, Updates and Deletes. This is a very cool feature of ADO.NET.
private void button1_Click(object sender, System.EventArgs e)
{
try
{
_da.Update(_ds);
}
catch (OdbcException ex)
{
Console.WriteLine(ex.Message.ToString());
}
catch (Exception ex)
{
Console.WriteLine(ex.Message.ToString());
}
}
Points of interest
As I noted in the comments of the source code, I have found an annoying problem when calling update more than once. I have tried the same code with the DataDirect Sybase provider and multiple updates work fine, so clearly something's not working as advertised (if anyone else has this problem and knows how to fix it please email me).
Ironically this problem did reveal a very nice touch in the DataGrid
- when the update fails the grid displays a warning icon in the first fixed column. When you hover the mouse over this, a help text bubble appears with the error message in it, nice :)
History
- 29 Jan 2003
I have discovered the solution to the problem I mention above. You need to use a DataTableMapping
/DataColumnMapping
collection and map the database fields to the DataSet
/ DataTable
fields. The documentation doesn't seem to explicitly state this - I discovered it by accident when I wanted to change the column headings in my grid. When I have more time I'll update the source code, to show this change.