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

Question

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...");

}

theLizard · 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..

theLizard · Answer

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

theLizard · Answer

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.

theLizard · Answer

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

Gustavo · Answer

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.

theLizard · Answer

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

Gustavo · Answer

Lizard: Here is the ClassDB

Gustavo · Answer

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

theLizard · Answer

try this cmd.CommandText = DBCommand; DBDataAdapter.SelectCommand = cmd; in place of this DBDataAdapter.SelectCommand = SELECT * FROM [Compnay] ; //

Gustavo · Answer

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.

theLizard · Answer

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.

Gustavo · Answer

Lizard: The actual error is: Cannot implicitily convert type 'string' to 'System.Data.SqlClient.SqlCommand' I will try to chnage the SqlDataAdapter .

theLizard · Answer

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.

Gustavo · Answer

Lizard: Well there is a problem with the line: DBDataAdapter .SelectCommand = DBCommand. I have tried the following: DBDataAdapter.SelectCommand = DBCommand; // DBDataAdapter.SelectCommand.CommandText = SELECT * FROM [Compnay] ; // DBDataAdapter.SelectCommand = SELECT * FROM [Compnay] ; //

theLizard · Answer

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.

Gustavo · Answer

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?

Gustavo · Answer

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() ???

theLizard · Answer

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.

Gustavo · Answer

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

theLizard · Answer

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?

Gustavo · Answer

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.

theLizard · Answer

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.

Gustavo · Answer

Lizard: Ok I will stop the work on the ListBox and get back to making the Grid better. Thanks.

theLizard · Answer

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);

Gustavo · Answer

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.

Gustavo · Answer

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(); }

Gustavo · Answer

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.

Gustavo · Answer

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.

Gustavo · Answer

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;

theLizard · Answer

//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

Gustavo · Answer

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.

theLizard · Answer

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);

Gustavo · Answer

Lizard: I also get errors at: public void app_user() public void loadCheckList(CheckBoxList l string s string fieldName) CheckBoxList l

Gustavo · Answer

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.

theLizard · Answer

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.

Gustavo · Answer

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 Hobbs · Answer

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.