Using DataAdapters and stored procedures with Sybase and ODBC.NET

• Download demo project - 9.38 Kb

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 app variables
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();

    //Commands for the data adapter
    OdbcCommand _cmdSel = new OdbcCommand();    //Select command
    OdbcCommand _cmdIns = new OdbcCommand();    //Insert
    OdbcCommand _cmdDel = new OdbcCommand();    //Delete
    OdbcCommand _cmdUpd = new OdbcCommand();    //Update

    //ODBC.NET Connection string - 
    //this is a standard ODBC connection string
    //Replace DRIVER, SERVER ,USER etc details 
    //as necessary for your particular setup
    OdbcConnection _cn = new OdbcConnection
                        ("DRIVER={MERANT 3.60 32-BIT Sybase};
                        SERVER=ODBC1;UID=odbc;
                        PWD=odbc;DATABASE=myodbc;");

    //You can explicitly call the Open method here - 
    //but if you do this you must remember to 
    //explicitly Close the connection in your code
    //_cn.Open();

    //Assign commands to the Data Adapter
    _da.InsertCommand = _cmdIns;
    _da.UpdateCommand = _cmdUpd;
    _da.SelectCommand = _cmdSel;
    _da.DeleteCommand = _cmdDel;


    //Configure commands

    //Select command - straight forward 
    //call to a parameterless stored proc
    _cmdSel.CommandText = "{call odbc_sel}";
    _cmdSel.CommandType = CommandType.StoredProcedure;
    _cmdSel.Connection = _cn;

    //Insert command - stored procedure that inserts 
    //a new row and returns the DB Primary Key for that
    //row as a Return value. The procedure takes 7 input parameters. 
    //Note the question marks used as parameter place holders. 
    //Make sure you put all the brackets in,
    //otherwise you'll get all sorts of weird error messages
    _cmdIns.CommandText = "{?=call odbc_ins (?, ?, ?, ?, ?, ?, ?)}";
    _cmdIns.CommandType = CommandType.StoredProcedure;
    _cmdIns.Connection = _cn;

    //Setup insert command parameters - parameters must be 
    //declared in order. ODBC.NET does not 
    //recognise parameter names, so you must get 
    //these in the correct order !

    //Return value from stored procedure - note that I 
    //have put the field name into this parameter.
    //By doing this ADO.NET can map the return value 
    //back to the dataset automatically - which means
    //my dataset will pick up the 
    //new DB primary key for this row - Kewl :P
    _cmdIns.Parameters.Add(new OdbcParameter("RETURN_VALUE",
        OdbcType.SmallInt,2,ParameterDirection.ReturnValue,
        false,0,0,"registrar_id",DataRowVersion.Current,null));
            
    //Input params - all fairly straight forward.
    _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"));
            

    //Delete command
    _cmdDel.CommandText = "{call odbc_del (?)}";
    _cmdDel.CommandType = CommandType.StoredProcedure;
    _cmdDel.Connection = _cn;

    _cmdDel.Parameters.Add(new OdbcParameter("registrar_id",
                        OdbcType.SmallInt,2,"registrar_id"));

    //Update Command
    _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"));
            
    //Fill the data adapter - it will open & 
    //close the connection automatically, unless
    //you have explicitly called the 
    //Open method on the connection object.
    _da.Fill(_ds);

    //The PK column is read only - the values are generated by 
    //the insert stored proc. Added this line
    //to enforce this in the grid. In a real app you'd 
    //probably want to hide the PK column from the users
    _ds.Tables[0].Columns[0].ReadOnly = true;


    //Once the adapter is filled we bind the grid 
    //datasource to the DataAdapter's Dataset.
    //Remembering of course that the dataset is a 
    //collection of DataTables - so we need to tell the
    //grid which Table we want it to use.
    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
    {
        //Call update on the data adapter - the adapter 
        //should take care of flushing the changes
        //we have made back to the database i.e. it will call 
        //the relevant Insert / Update / Delete
        //commands required to sync the DB to the contents of the grid.
        _da.Update(_ds);


        //One problem I have found is that the first call 
        //to Update seems to work fine, however
        //subsequent calls to Update fail and I get an error 
        //that I have not set any values for the
        //stored proc params - clearly something breaks in 
        //the binding between command params and grid data
        //held in the dataset. I haven't figured this out :(
        //I also have the DataDirect Sybase driver which doesn't 
        //suffer this problem - so clearly there's
        //either a bug or you need to add 
        //some extra code for ODBC.NET calls.

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