Can I get some feedback / mentoring?

[Zinderin]

I'm learning C# on my own (I know C, Pascal, and about 8 proprietary languages) ... I'm stuck with VS2005 Pro (no budget) so I can't upgrade and I can't use Linq. I'm using Access just for simplicity at this point.

What I'm trying to do, is what I've done in other languages ... build a utility class that does DB work for me. e.g. Pass it a listbox, combobox, datagrid, memo, etc and tell it what data to fill it with by passing the sql command with the call.

I'd like some opinions on if I'm headed in the right direction, and if there's code in this first method, that can be moved up in the class (out of the method itself to eliminate repetition in code) but still available to the other methods I'll be adding.

Or do I have redundant code? Or am I making something more difficult than it is?

Be warned, if you start throwing advanced, cryptic, C# shorthand at me ... I'm going to ask you to explain what I can't research and understand on the web. :)

Here's the class so far;

class DBUtilies
    {
        public string strDBFile;  //see the constructor...
        public string strConnStr; //see the constructor...

        public DBUtilies()
        {
            strDBFile = @"C:\C#Projects\FE Informant\FEDB.mdb";
            strConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + strDBFile;
        }

        public void FillListBox(ListBox ls, string sqlCommand)
        {

            int icnt;  // tracks the record count returned.
            int x;     // a loop variable.

            //Set up the connection and adapters...
            OleDbConnection myConn = new OleDbConnection();
            myConn.ConnectionString = strConnStr;
            OleDbCommand sqlCmd = new OleDbCommand(sqlCommand);
            OleDbDataAdapter dbAdapter = new OleDbDataAdapter();
            dbAdapter.SelectCommand = sqlCmd;
            DataSet ds = new DataSet();
            ds.Clear();

            // Open the connection ....
            myConn.Open();
            sqlCmd.Connection = myConn;
            
            // Get the data and see how many records we got ....
            dbAdapter.Fill(ds, "dbrecs");
            icnt = ds.Tables[0].Rows.Count;

            // Fill the ListBox ...
            if (icnt > 0)
            {
                ls.Items.Clear();
                for (x = 0; x < icnt; x++)
                {
                    ls.Items.Add(ds.Tables[0].Rows[x][0].ToString());
                }
            }

            // Close the connection and toss it...
            myConn.Close();
            myConn.Dispose();
        }
    }

What the above class allows me to do is just have two lines of code to handle any listbox, combobox, etc. that I need to fill. Example:

DBUtilies dbu = new DBUtilies();
    dbu.FillListBox(listBox1,"SELECT sDesc FROM BookClass");

BTW ... before you beat me up about try/catch and error handling, that will all be added later, I'm trying to keep it simple to get my head wrapped around all this stuff. :)

Thanks guys.

[ddanbe]

Hi,
I'm not that much of a DB guy and still learning C# too.
Two things:
1) If you would want to do something with LINQ, you could download VS C# 2008 express. It is still free I guess.
2) Trash the declaration of your loop variable on line 16! Change line 39 like this:

for (int x = 0; x < icnt; x++)
{
// now loop var x stays local to the loop
}

What you did was something you retained from the previous century, I too had to unlearn that habbit.;)

[nick.crane]

Hi, I have a few points that you might want to consider.

If you only ever expect a single table result from the SQL query, then you can use a DataTable instead of a DataSet.

The data collection part could be moved out to a private function that takes the SQL string and returns a DataTable.
Also as you always expect a list of values then consider returning an array (or list).
That way you can use AddRange when adding to a list.

Make DBFile and ConnString in to properties to make changing them easier, or use an Application Setting property.

Look in to generics and generic functions as this might help reduce some of your code.
(Although I have found that it can sometimes lead to unnecessary complexity.)

Nice question BTW:)

[Zinderin]

Hi,
I'm not that much of a DB guy and still learning C# too.
Two things:
1) If you would want to do something with LINQ, you could download VS C# 2008 express. It is still free I guess.
2) Trash the declaration of your loop variable on line 16! Change line 39 like this:

for (int x = 0; x < icnt; x++)
{
// now loop var x stays local to the loop
}

What you did was something you retained from the previous century, I too had to unlearn that habbit.;)

Excellent point ... old habits die slow. I did that without thinking about it, and it looked so natural I didn't even see it. Thanks!

[Zinderin]

Hi, I have a few points that you might want to consider.

If you only ever expect a single table result from the SQL query, then you can use a DataTable instead of a DataSet.

The data collection part could be moved out to a private function that takes the SQL string and returns a DataTable.
Also as you always expect a list of values then consider returning an array (or list).
That way you can use AddRange when adding to a list.

Make DBFile and ConnString in to properties to make changing them easier, or use an Application Setting property.

Look in to generics and generic functions as this might help reduce some of your code.
(Although I have found that it can sometimes lead to unnecessary complexity.)

Nice question BTW:)

I will do both (app settings and looking at LISTS) ... I'm clearly am not giving LISTS enough attention. I had another question (totally different issue) and someone pointed me to LISTS. I will repost the code after I've made the changes and know its working.

It does seem you are saying that tables are more simplified than datasets ... is that true? I saw the datasets as the more simple, and tables for the more complex queries that return multiple columns. Do I have this backwards?

Thanks again!!!!

[nick.crane]

Do I have this backwards?

Perhaps. A DataSet is a container for multiple DataTables and a DataTable for multiple DataRows. Each DataRow contains the data for the DataColumns associated with its parent DataTable.
For most simple queries the result is in a DataTable.
(Even a simple scalar request is sometimes returned in as a single column one row table.)
Hope this helps.