How do I: Create a class to do some database functions?

Gustavo

Hello:

I am trying to clean my code and would like to put some of it in a class. Below is my code and what I would like to do. Hope its not too much to post.

Main program:

Currently I have this to call the connection:

private void buttonDBConnect_Click(object sender, EventArgs e)

{

DBServer = textBoxProfileServer.Text;

DBDatabase = textBoxProfileDatabase.Text;

DBLogin_ID = textBoxProfileLogin_ID.Text;

DBPassword = textBoxProfilePassword.Text;

ClassDB.Connection(DBServer, DBDatabase, DBLogin_ID, DBPassword);

}

This fills my dataGrid:

private void buttonLoadGrid_Click(object sender, EventArgs e)

{

DBCommand =

" SELECT"

+ " [Name]"

+ " ,[Address_1]"

+ " FROM"

+ " [Company]"

;

ClassDB.Select(DBCommand);

SqlDataAdapter DBDataAdapter = new SqlDataAdapter(ClassDB.DBSelect, ClassDB.DBConnect);

DataSet DBDataSet = new DataSet();

DBDataAdapter.Fill(DBDataSet);

dataGridViewGrid.DataSource = DBDataSet.Tables[0];

dataGridViewGrid.Refresh();

}

Class:

This does the connection:

public static void Connection(string DBServer, string DBDatabase, string DBLogin_ID, string DBPassword)

{

//MessageBox.Show("DBClass: Connection");

DBConnect =

@"Server=" + DBServer + ";"

+ "Database=" + DBDatabase + ";"

+ "User ID=" + DBLogin_ID + ";"

+ "Password=" + DBPassword + ";";

//MessageBox.Show("DBConnect: With:" + DBConnect);

try

{

//MessageBox.Show("TRY...");

SqlConnection DBConnection = new SqlConnection();

DBConnection.ConnectionString = DBConnect;

DBConnection.Open();

DBConnection.GetSchema();

}

catch

{

//MessageBox.Show("CATCH...");

}

finally

{

//MessageBox.Show("FINALLY...");

}

//////////////////////////////////////////////////////////////////////////////////////////////////

What I would like to do is to call my DBClass.LoadGrid. By calling it like this: DBClass.LoadGrid(DBCommand);

This is what I have in my DBClass.LoadGrid now. What should I have in it?

public static void LoadGrid(string DBCommand)

{

//MessageBox.Show("DBLoadGrid: With:" + DBCommand);

try

{

//MessageBox.Show("TRY...");

DBLoadGrid = DBCommand;

}

catch

{

//MessageBox.Show("CATCH...");

}

finally

{

//MessageBox.Show("FINALLY...");

}

37 Replies

Know the answer? Post it — somebody with the same question will find it here.

theLizard16y ago
Accepted answer

private void buttonLoadGrid_Click(object sender, EventArgs e)
{

DBCommand =
" SELECT"
+ " [Name]"
+ " ,[Address_1]"
+ " FROM"
+ " [Company]"
;
ClassDB.Select(DBCommand);
//
SqlDataAdapter DBDataAdapter = new SqlDataAdapter(ClassDB.DBSelect, ClassDB.DBConnect);
DataSet DBDataSet = new DataSet();
DBDataAdapter.Fill(DBDataSet);
//
dataGridViewGrid.DataSource = DBDataSet.Tables[0];
//dataGridViewGrid.Refresh();
}

all the above could be replaced with

ClassDB con = new ClassDB();

con.connect();

dataGridViewGrid.DataSource = con.GridData(DBCommand).Tables[0];

con.Terminate();

//------------------------------------------------
in your class
//------------------------------------------------
public DataSet GridData(string DBCommand)
   {
    SqlDataAdapter DBDataAdapter = new SqlDataAdapter(ClassDB.DBSelect, ClassDB.DBConnect);

    DataSet DBDataSet = new DataSet();
    DBDataAdapter.Fill(DBDataSet);
return(DBDataSet);

//note please do error handling..

   }

private void buttonLoadGrid_Click(object sender, EventArgs e)
{
ClassDB con = new ClassDB();

con.connect();

dataGridViewGrid.DataSource = con.GridData(DBCommand).Tables[0];

con.Terminate();

}

something along these lines..
theLizard16y ago
I am pleased, I have had a quick look at your class and it looks ok, I will take a closer look tomorrow but remember, for every one way of doing something better there is a better way of doing something.

I am sure that you now know how much easier your task of creating your application has become, having the class functions in a single location that fills whatever control you send will reduce the amount of code you write and make you application leaner and meaner, it will execute faster and take up less memory space, you can have multiple forms showing but executing one load function for all with a single line of code on the form.

I hope others who have been reading this thread have also learned.

Good night...
Gustavo16y ago
Lizard:

Thank you so much. I REALLY appreciate your help. I learned a lot. I will spend tomorrow reading over the code and really understand it.
theLizard16y ago
Good, now you have the foundation to load any control, just create overload functions and replace DataGridView with the control you want to load

but instead of calling the functions GridData( ...,...)

maybe call them

load(string DBCommand, DataGridView dg)
load(string DBCommand, ComboBox cb)
load(string DBCommand, ListBox lb)
load(string DBCommand, CheckedBoxList cbl)

You get the picture...

UI am off to bed good night,
Gustavo16y ago
Lizard:

Here is the ClassDB
Gustavo16y ago
Lizard:
GOT IT.....

I changed the SetupDataAdapter to:
private static SqlDataAdapter SetupDataAdapter()
{
return new SqlDataAdapter(ClassDB.DBCommand.ToString(), ClassDB.DBConnect);
}

and removed the line:
//DBDataAdapter.SelectCommand = DBCommand;

Thanks you so much...
theLizard16y ago
try this

cmd.CommandText = DBCommand;
DBDataAdapter.SelectCommand = cmd;

in place of this

DBDataAdapter.SelectCommand = "SELECT * FROM [Compnay]";

//<<
Gustavo16y ago
Lizard:

Ok, I will play around with the SetupDataAdapter and see what I can do.

Later I will zip the ClassDB up and post it.
theLizard16y ago
Ok, I think if you change SetupDataAdapter you should be ok, I would like to see your entire class so that I can get a better picture of what you have as a whole.
Gustavo16y ago
Lizard:

The actual error is: Cannot implicitily convert type 'string' to 'System.Data.SqlClient.SqlCommand'

I will try to chnage the SqlDataAdapter .
theLizard16y ago
Ok,

I personally do not use DataAdapters, what you could do is adjust the SetupDataAdapter to include an sql statement.

private static SqlDataAdapter SetupDataAdapter(string sql)
    {
        // Assuming all the default settings, create a SqlDataAdapter working
        // current database in SQL Server.
        //System.Configuration.AppSettingsReader asr = new System.Configuration.AppSettingsReader();

        return new SqlDataAdapter(sql, con.ConnectionString);
    }

see what that does.

but is this the actual error message //<<
You may need to play around until you have the right implementation methods.
Gustavo16y ago
Lizard:

Well, there is a problem with the line: DBDataAdapter.SelectCommand = DBCommand.

I have tried the following:
DBDataAdapter.SelectCommand = DBCommand;   //<<< Can not string to System.Data.SqlClient.SqlCommand
DBDataAdapter.SelectCommand.CommandText = "SELECT * FROM [Compnay]";   //<<<{"Object reference not set to an instance of an object."}
DBDataAdapter.SelectCommand = "SELECT * FROM [Compnay]";   //<<
theLizard16y ago
Yes, but if you have gone with the single line call from your application you will need to instantiate an sql connection.

So in the function you need to do this

public void LoadGridView(string DBCommand, DataGridView DBDataGridView)
{
//MessageBox.Show("LoadGrid...");

ClassDB DBConnection = new ClassDB();
DBConnection.Connection();
SetupDataAdapter();
DBDataAdapter.SelectCommand = DBCommand.

NOTE: whatever you do to get the connection string must also be done here...

try
{
DataSet DBDataSet = new DataSet();
DBDataAdapter.Fill(DBDataSet);
DBDataGridView.DataSource = DBDataSet.Tables[0];
//return (DBDataSet); // you do not need to do this because whatever you do to the grid here will reflect in the grid on the form where you called this function from, the reason this happens is that you are working on the actual grid by reference, it is like having the code in this function on the form you are calling it from itself. This is why I have said the function does not need to know who owns the grid.

//Terminate();

}
catch
{
//MessageBox.Show("CATCH: LoadGrid");
return null;
}
finally
{
//MessageBox.Show("Finally: LoadGrid");
}

DBConnection.Terminate(); //must do this here,
By doing this, you have a self contained function that will instantiate and release a new connection when you need to load any grid from any form.
Gustavo16y ago
Lizard:

Yes... dataGridViewGrid.DataSource = DBConnection.LoadGridView(DBCommand, dataGridViewGrid).Tables[0]; Was wrong and I have already corrected it.

Dont I have to call SetupDataAdapter() somewhere?
Gustavo16y ago
Lizard:

I found where the error is, I think.
I added:
    private static SqlDataAdapter SetupDataAdapter()
    {
        // Assuming all the default settings, create a SqlDataAdapter working
        // current database in SQL Server.
        //System.Configuration.AppSettingsReader asr = new System.Configuration.AppSettingsReader();

        return new SqlDataAdapter("", con.ConnectionString);
    }

But dont I have to call it from somewhere? SetupDataAdapter() ???
theLizard16y ago
If I am understanding what you ave done, you don't need to do this

dataGridViewGrid.DataSource = DBConnection.LoadGridView(DBCommand, dataGridViewGrid).Tables[0];

all you would need is

DBConnection.LoadGridView(DBCommand, dataGridViewGrid);

dataGridViewGrid being the grid on the form you want to populate.
Gustavo16y ago
Lizard:
Yes, I understand. Now I have an error in the main program at line:
dataGridViewGrid.DataSource = DBConnection.LoadGridView(DBCommand, dataGridViewGrid).Tables[0];
{"Object reference not set to an instance of an object."} <<< I have to figure out why. I think its the "dataGridViewGrid".
theLizard16y ago
Good, now remove

SqlDataAdapter DBDataAdapter = new SqlDataAdapter(ClassDB.DBSelect, ClassDB.DBConnect);

in the class declaration

SqlDataAdapter da = null;

in connect()

da = new SqlDataAdapter();

    private static SqlDataAdapter SetupDataAdapter()
    {
        // Assuming all the default settings, create a SqlDataAdapter working
        // current database in SQL Server.
        //System.Configuration.AppSettingsReader asr = new System.Configuration.AppSettingsReader();

        return new SqlDataAdapter("", con.ConnectionString);
    }

then in GridData

da.SelectCommand = DBCommand;

do you understand?
Gustavo16y ago
Lizard:

Ok, I changed it to accept the DBCommand and the datagridview and it still works.
Now what do you want me to do?

I did remove the: ClassDB.Connection();
and put it in the ClassDB.

This is what my code looks like now:
Main program:
private void buttonLoadGrid_Click(object sender, EventArgs e)
{
DBCommand =
" SELECT"
+ " [Name]"
+ " ,[Address_1]"
+ " FROM"
+ " [Company]"
;
ClassDB DBConnection = new ClassDB();
ClassDB.Connection();
dataGridViewGrid.DataSource = DBConnection.LoadGridView(DBCommand, dataGridViewGrid).Tables[0];
//ClassDB.Terminate();
//
//ClassDB.LoadGridView(DBCommand, dataGridViewGrid);
//
}

Class:
public DataSet LoadGridView(string DBCommand, DataGridView DBDataGridView)
{
//MessageBox.Show("LoadGrid...");
try
{
Connection();
SqlDataAdapter DBDataAdapter = new SqlDataAdapter(DBCommand, ClassDB.DBConnect);
DataSet DBDataSet = new DataSet();
DBDataAdapter.Fill(DBDataSet);
DBDataGridView.DataSource = DBDataSet.Tables[0];
return (DBDataSet);
//Terminate();
}
catch
{
//MessageBox.Show("CATCH: LoadGrid");
return null;
}
finally
{
//MessageBox.Show("Finally: LoadGrid");
}
}

I had to comment out the Terminate, because its giving me an error now.
theLizard16y ago
Ok, when you get the grid working the others will be a breeze, but try to implement the DataAdaptor in a diferent way.

SqlDataAdapter da = new SqlDataAdapter(ClassDB.DBSelect, ClassDB.DBConnect);

da could be a class global, you only need one data adapter for each instantiated class of the dbclass so in you connect function you could initialize your data adapter so it can be available to any other function during the life of the class.

In my post you should see how it is done.
Gustavo16y ago
Lizard:

Ok, I will stop the work on the ListBox and get back to making the Grid better. Thanks.
theLizard16y ago
Good, Now consider this

con.GridData(DBCommand, dataGridViewGrid)

in this case the function in your class could do all the work.

private void buttonLoadGrid_Click(object sender, EventArgs e)
{
ClassDB con = new ClassDB();

con.connect();

con.GridData(DBCommand, dataGridViewGrid);

con.Terminate();

}

public void GridData(string DBCommand, DataGridView d)
   {
    SqlDataAdapter da = new SqlDataAdapter(ClassDB.DBSelect, ClassDB.DBConnect);

    DataSet ds = new DataSet();
    da.Fill(ds);
    d.DataSource = ds.Tables[0];

//note please do error handling..

   }

this is the same as doing what I sugested in my previous example.

also consider that with careful design you could get the whole thing with one line of code in your application

myClass.GridData(DBCommand, myGridView);
Gustavo16y ago
Lizard:

Thanks...it worked. It took me a while to make it work with my variables, but it works. Now I have to study it and really understand it so I can go my ListBox next. It should be very close to the Grid.

Thanks again.
Gustavo16y ago
Lizard:

OK... I got the connection to work. Basically it was what I had there. I added yout routine to terminate. But I use my own variable names. Now I will work on the next routine:
This is what I have in my main program. I need to replace it with something in the DBClass.

private void buttonLoadGrid_Click(object sender, EventArgs e)
{
DBCommand =
" SELECT"
+ " [Name]"
+ " ,[Address_1]"
+ " FROM"
+ " [Company]"
;
ClassDB.Select(DBCommand);
//
SqlDataAdapter DBDataAdapter = new SqlDataAdapter(ClassDB.DBSelect, ClassDB.DBConnect);
DataSet DBDataSet = new DataSet();
DBDataAdapter.Fill(DBDataSet);
//
dataGridViewGrid.DataSource = DBDataSet.Tables[0];
//dataGridViewGrid.Refresh();
}
theLizard16y ago
Ok, then these are the only things you need to test the connection parts.

using System;
using System.Data;
using System.Data.Common;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Configuration;

///

/// Summary description for sql
///

public class sqlCon
{
    public SqlConnection con = null;
    public SqlCommand cmd = null;
    public sqlCon()
        {
        cmd = new SqlCommand();
        }
    //-----------------------------------------------------------------
    public void connect(string constr)
        {
        con = new SqlConnection(constr);
        con.Open();
        }
    //-----------------------------------------------------------------
    public void terminate() //this is important, with each open you should terminate the connection.
        {

        try
            {
            if (cmd != null)
                {
                cmd.Connection.Close();
                cmd.Connection.Dispose();
                }
            }
        catch (Exception err)
            {
            }
        try
            {
            if (con != null)
                {
                con.Close();
                con.Dispose();
                }
            }
        catch (Exception err)
            {
            }
    }
    //-----------------------------------------------------------------

}

no need to have static strings, the whole idea of the functions within the class is that they are dynamic, the functions should not care about who owns a list box or who wants a connection, these only exist in the instance of time they are needed, outside that time they mean nothing.

A function to load a grid should not be hard coded with the number of columns, that can be determined by the number of fields in the result set and the type of column data to assign to a column can be determined by the field data type (SqlDataType) of the result set.

But these are for leaerning later on...
Gustavo16y ago
Lizard:

I am not trying to use all of your code. I have a seperate class that I am using. I am trying to do one part at a time. The first one will be the connection. When I get that working I will try another part.
In my class (ClassDB) I am using static public string... not sure if I should use them as static or not.

Im working on it now, will let you know what happens.
theLizard16y ago
Good, but can I suggest that rather than creating another class by cutting and pasting, try to understand how the class I sent you works.

start with a simple form with just a chekedBoxList nothing else.

in the form load event do

sqlCon con = new sqlCon();

con.connect(); //this is assuming that you have a config file that AppSettingsReader can get to if not, overload this function in sqlCon class

    //-----------------------------------------------------------------
    public void connect()
        {
        System.Configuration.AppSettingsReader asr = new System.Configuration.AppSettingsReader();

        con = new SqlConnection((string)asr.GetValue("TheCottageWonthaggiConnectionString", typeof(string)));
        con.Open();
        con.GetSchema();
        }

with this
    public void connect(string constr)
        {

        con = new SqlConnection(constr);
        con.Open();
        }

//

Put a break point and step through the code

sqlCon con = new sqlCon();

con.connect();

or

con.connect("your connection string");

con.loadCheckList(CheckedBoxList l, "SELECT * FROM Users ORDER BY name", "name")   //do whatecver in the select statement and give the name of the field from the table you want the list filled with.

con.terminate();

You should only have these four lines of code on the form to fill the CheckedLBoxList, try changing CheckedBoxList to a ComboBox.
Gustavo16y ago
Lizard:

I found the issue with the "CheckBoxList". Should it be CheckedListBox?
The CheckBoxList is from System.Web.UI.WebControls, I am not using that and its not in you code.

I did cut and past the right line form your code.
Gustavo16y ago
Lizard:

Ok, for now I commented out the foillowing:
public class app_user
{
    public string name;
    public string pwd;
    public string whatever;

public void app_user()
    {
    }
}

About the CheckBoxList... I did a cut/past of the code you gave me.
//-----------------------------------------------------------------
public void loadCheckList(CheckBoxList l, string s, string fieldName)
{
sqlCon con = new sqlCon();
l.Items.Clear();
try
{
con.connect();
con.command(s);
con.ExecuteReader();
while (con.read())
{
l.Items.Add(con.get("fieldName"));
}
}
catch (Exception err)
{
}
finally
{
con.terminate();
}
}
//-----------------------------------------------------------------

I have the following using, as I cut/paste form your code:

using System;
using System.Data;
using System.Data.Common;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
theLizard16y ago

//example only should not be in sqlCon class.

public class app_user
{
    public string name;
    public string pwd;
    public string whatever;

public void app_user()
    {
    }
}

The example that you are talking about app_user() should be in its own class file, if you don't know how to do that, then first learn before advancing to next level.

BTW, even if the above is in with the sqlCon class it compiled ok with me.

public void loadCheckList(CheckBoxList l, string s, string fieldName)

The type or namespace name 'CheckBoxList' could not be found.

then look at why? do you have using System.Windows.Forms; in the class as I have given, should it be CheckBoxList or CheckedBoxList are you using the class as I have given it or have you cut an pasted into something else!

Apart from being able to write a program you need to be able to debug it, this is one such time that you need to debug the code. You also need to be able tio understand what functions do

Do you know what this means loadCheckList(CheckBoxList l, string s, string fieldName) in relation to the code contained within it's scope.

Explain l (CheckedBoxList)   what is l
Explain s,
Explain fieldName
Gustavo16y ago
Lizard:

I have cut/paste your class. I still get the following errors.
public void app_user()
{
}
'app_user': member names cannot be the same as their enclosing type.

public void loadCheckList(CheckBoxList l, string s, string fieldName)

The type or namespace name 'CheckBoxList' could not be found.
theLizard16y ago
The class starts at the top of the page and ends at the bottom, the whole example I sent is the class.

Make it know what ids is, but to do this you need to understand how it works and to do that you need to understand programming.

If it does not understand CheckBoxList work out why, are you missing a using blah blah.

Instead of trying to solve multiple problems at the same time just do one at a time, get one thing to work properly before tackling another. Instead of posting 3, 4 5 questions at a time ask one get it right then do the next

The class I sent you has ALL the answers either direct or examples of how to do things, you just need to think independently.

I am not going to do any work for you but what you want to do using your example could be a simple as DBDataAdapter.Fill( dbclass.getDatat(DBCommand));

but this means you would need to work out what to do in the class of course the way I would do it is simply dbclass.getData(grid, DBCommand);
Gustavo16y ago
Lizard:

I also get errors at:
public void app_user() <<< It does not like: app_user()
public void loadCheckList(CheckBoxList l, string s, string fieldName) <<< It does not like: CheckBoxList l
Gustavo16y ago
Lizard:

I have looked at your code.

Where does the class start and end? Does it stop right before the: public void getUser(string s, app_user user)

If so, in: public void loadDropDown(ComboBox cb, string s, string fieldName)
the: return (ids); gives me an error because it does not know 'ids'.

If I can get your code to compile, I can then try to use it and implement it.
theLizard16y ago
Another example of what I mean, I have already given you a database class and examples of how to do things, and again you are ignoring it, that's ok, it's your choice after all.

Anyway, I completely understand what you want and it is so simple it's not funny but it is not the way you are going about it.

Alas if you studied and took the time to workout the class I sent you all these things would be clear to you but you can lead a horse to water but you cannot make them drink it.
Gustavo16y ago
Sam:

Currently I have these lines in my main porgram:
                This fills my dataGrid:
        private void buttonLoadGrid_Click(object sender, EventArgs e)
        {
            DBCommand =
                " SELECT"
                + " [Name]"
                + " ,[Address_1]"
                + " FROM"
                + " [Company]"
                ;
            ClassDB.Select(DBCommand);
            //
            SqlDataAdapter DBDataAdapter = new SqlDataAdapter(ClassDB.DBSelect, ClassDB.DBConnect);
            DataSet DBDataSet = new DataSet();
            DBDataAdapter.Fill(DBDataSet);
            //
            dataGridViewGrid.DataSource = DBDataSet.Tables[0];
            dataGridViewGrid.Refresh();
        }

I would like to just have this if possible:

ClassDB.LoadGrid(DBCommand);

So it would do the select and load or return something that I can fill the grid with. I am probably not explaining it correctly.
I would like to put the following code into the class instead of having it in the main program many times.

            SqlDataAdapter DBDataAdapter = new SqlDataAdapter(ClassDB.DBSelect, ClassDB.DBConnect);
            DataSet DBDataSet = new DataSet();
            DBDataAdapter.Fill(DBDataSet);
Sam Hobbs16y ago
I don't know what you need to have in LoadGrid; I think you need to tell us, so I don't understand the question.