You asked: Sharp+Develop+ODBC+example

I had that question appear in my logs a couple of times, and I assume, these are (beginning) .Net developers who want to have examples of accessing database servers via ODBC in C#. If you thought this was going to be ‘plain and simple, drag and drop in Visual Studio’ (or SharpDevelop), you are partly wrong. As in everything in programming, you end up doing a lot of coding yourself because of certain limitations.

First of all, Visual Studio 2005 (SharpDevelop might) by default does not have the (visual) ODBC controls installed in the ‘Toolbox’. You need to add them yourself (there should be 4 of them). After doing that, you’ll find out that using these ‘visual’ controls is not as ‘visual as that fancy commercial promised’. Worse, you read in the Framework documentation that:

While the OdbcDataReader is being used, the associated OdbcConnection is busy serving the OdbcDataReader, and no other operations can be performed on the OdbcConnection other than closing it

Or:

Due to the limitations of native ODBC drivers, only one DataTable is ever returned when you call FillSchema. This is true even when executing SQL batch statements from which multiple DataTable objects would be expected.

There’s generally two ways to get and retrieve data using the .Net 2.0 Framework: the first one is to use the earlier mentioned DataReader, the second one is the ‘in-memory cache’ DataSet/DataTable. Most of time, you’ll end up using a combination of both: when there’s lots of data involved you may want to skip the DataSet and go for the DataReader (wrap it in an object for example). If you want to keep data in memory and want to keep connections open (persistent), you may want to keep close attention to the first quote I mentioned above.

But the good stuff is right here (warning: untested code ahead! All disclaimers apply). If you’re not into programming, you may want to skip this:

The DataReader

 
            OdbcConnection n = new OdbcConnection();
            OdbcCommand nc = new OdbcCommand();

            int id = 0;
            string sqlstring = "SELECT * from DATA";           


            n.ConnectionString = "DSN=Hello;";
            n.Open();

            try
            {
                nc.CommandText = sqlstring;
                nc.Connection = n;
                OdbcDataReader nr = nc.ExecuteReader();
                try
                {
                    // Read table row by row...                   
                    while (nr.Read())
                    {
                        id = (Convert.IsDBNull(nr["fid"]) 
                            ? 0 
                            : Convert.ToInt32(nr["fid"]));
                        // do something with data.
                    }
                }
                finally
                {
                    nr.Close(); // Close reader...
                }
            }
            finally
            {
               // dispose anything you don't want to use...   
            }

Note that I do an extra check to see if the actual field value for ‘fid’ is NULL: obviously, this ia something you need to take care of as a programmer. Make sure to close your DataReader appropriately. If you plan to ‘reuse’ connections, you may want to add an extra check if the Connection is open or closed.

The DataTable

            OdbcConnection oc = new OdbcConnection();
            OdbcCommand ocommand = new OdbcCommand();
            OdbcDataAdapter ocdata = new OdbcDataAdapter();
            DataTable internaltable = new DataTable();

            oc.ConnectionString = "DSN=CONNECTIONHUH";
            ocommand.CommandText = "SELECT * from DATA";
            ocommand.Connection = oc;

            ocdata.SelectCommand = ocommand;
      
            // Load all rows in memory...
            ocdata.Fill(internaltable); 

            for (int i = 0; i < internaltable.Rows.Count; i++)
            {
                id = 
                    (Convert.IsDBNull(
                       internaltable.Rows[i]["pdata"])
                      ? 0
                      : Convert.ToInt32(
                      internaltable.Rows[i]["pdata"]));
                // Do something with all that data in memory...
            }

While both examples do the same, the main difference between the two is that the DataTable example will load every chunk of row into memory. If you need persistent data, a DataTable comes in handy since it allows looking up data in memory easily (how to do this, is something you should figure out yourself). However, if you design your code the right way, you can literally achieve the same using a combination of a DataReader and a List class.

So to summarize, the DataTable option is extremely flexible (slow, but 'easy to use') when you have plenty of memory available ('optimistic scenario'). In other cases, you'll probably end up using a DataReader (wrapped in an object): it's fast, efficient and bound only to the imagination of the developer.