How do I: Pass a DataTable from a Class to the Main program?

Question

Hello:

I have a class that get me a record and I want it to be passed back to the main program. I can not get it to work. Below is the part of the Class and the main program:

Class:

public DataTable ReadRecord(string DBCommand)

{

Connection();

SetupDataAdapter();

DBDataAdapter = new SqlDataAdapter(DBCommand, DBConnect);

try

{

DataTable DBDataTable = new DataTable();

DBDataAdapter.Fill(DBDataTable);

foreach (DataRow DBDataRow in DBDataTable.Rows) // GOOD - IT WORKS

{

TheID = DBDataRow["Name"].ToString();

MessageBox.Show("TheID=" + TheID);

}

Terminate();

return (DBDataTable);

}

catch

{

return null;

}

finally

{

}

Main:

private void buttonDBSelectRecord_Click(object sender, EventArgs e)

{

DBCommand =

" SELECT"

+ " [Name]"

+ " ,[Address_1]"

+ " FROM"

+ " [Company]"

+ " WHERE"

+ " [Company_ID] = " + "'000000'"

;

ClassDB DBConnection = new ClassDB();

ClassDB.Connection();

DBConnection.ReadRecord(DBCommand);

foreach (DataRow DBDataRow in DBDataTable.Rows) // BAD

{

TheID = DBDataRow["Name"].ToString();

MessageBox.Show("Login...TheID=" + TheID);

}

theLizard · Answer

Georgio one thing at a time I have had a better look at your class and have made some mods. using System; using System.Collections; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Data.SqlClient; using System.Text; using System.Windows.Forms; // why do you have these here using System.Reflection; using System.Drawing; using System.Drawing.Printing; using System.Security.Cryptography; using System.IO; namespace lizardMarquee { public class ClassDB { //These are not needed unless there is a specific use for them //--------------------------------------------------------------- public string DBServer; public string DBDatabase; public string DBLogin_ID; public string DBPassword; // public string DBSelect; public string DBInsert; public string DBUpdate; public string DBDelete; public string DBClose; // public string DBReadRecord; public string DBLoadGrid; public string DBLoadCombo; public string DBLoadList; // //--------------------------------------------------------------- //These should be private //--------------------------------------------------------------- private string dBConnect; private SqlConnection DBConnection = null; private SqlDataReader DBDataReader = null; private SqlCommand DBCommand = null; private SqlParameter DBParameter = null; private SqlDataAdapter DBDataAdapter = null; // public ClassDB() { DBCommand = new SqlCommand(); DBParameter = new SqlParameter(); } public ClassDB(string strCon) { DBConnect = strCon; DBCommand = new SqlCommand(); DBParameter = new SqlParameter(); Connection(); } // // public void Connection(string DBServer string DBDatabase string DBLogin_ID string DBPassword) public void Connection() { //DBConnect = @Data Source=.\SQLEXPRESS;Initial Catalog=temp;Integrated Security=True; /* @Server= + DBServer + ; + Database= + DBDatabase + ; + User ID= + DBLogin_ID + ; + Password= + DBPassword + ;; */ // try { DBConnection = new SqlConnection(DBConnect); DBConnection.Open(); DBDataAdapter = SetupDataAdapter(); } catch { } finally { } } //make this a property of the class and use it to set your connection public string DBConnect { get { return (dBConnect); } set { dBConnect = value; } } public void Connection(string blank) { try { DBConnection = new SqlConnection(dBConnect); DBConnection.Open(); SetupDataAdapter(); } catch { } finally { } } // public void Terminate() { try { if (DBCommand != null) { DBCommand.Connection.Close(); DBCommand.Connection.Dispose(); } } catch (Exception Error) { } finally { } // try { if (DBConnection != null) { DBConnection.Close(); DBConnection.Dispose(); } } catch { } finally { } } // // public void LoadGridView(string sDBCommand DataGridView DBDataGridView) { Connection(); DBDataAdapter = SetupDataAdapter(); DBCommand.CommandText = sDBCommand; //these must be in this order DBDataAdapter.SelectCommand = DBCommand; // DBDataAdapter.SelectCommand.Connection = DBConnection; // try { DataSet DBDataSet = new DataSet(); DBDataAdapter.Fill(DBDataSet); DBDataGridView.DataSource = DBDataSet.Tables[0]; Terminate(); //return (DBDataSet); } catch { //return null; this not needed } finally { } } // private SqlDataAdapter SetupDataAdapter() { return new SqlDataAdapter(); } // } } if you adopt the above all you would need to do in your form is this. ClassDB con = new ClassDB(@Data Source=.\SQLEXPRESS;Initial Catalog=temp;Integrated Security=True); //instantiate & connect con.LoadGridView(SELECT name Address FROM Company WHERE Company_id = ' 00000' ORDER BY name dataGridView); con.Terminate(); I have compiled and loaded a grid view with the modified code it all works so you should be able to do the same by changing just the Initial Catalog = your database name. ReadRecord is something you would do if you want to manually load a grid view rather than use a DataAdapeter so these things are for learning when you are more experienced and confident for you wanting to manually load controls.

Gustavo · Answer

Lizard: Ok thanks. I have changed everythign but now I have an issue with the Connection(). I dont see where you want me to connect it if you have the connection variables commented out. I will play with it. Night.

theLizard · Answer

I am a bit tired now so I am of to sleep after you do hings post the code I will look at it in the morning..

Gustavo · Answer

OK

theLizard · Answer

Ok I am happy with your progress you do not need anything in the class to be static in fact make sure that there is no static parts. also make the next reply a new thread because this one is getting too long but we will continue the theme ok?

Gustavo · Answer

Lizard: Its the 'static' stuff that I used that is messing it up. Let me try to get rid of all of them without messing up the other programs.

theLizard · Answer

? I tried with the: ClassDB con = new ClassDB(); and all the other likes with con.* they give me an error. Error 18 Member 'ICEPack.ClassDB.Connection()' cannot be accessed with an instance reference; qualify it with a type name instead I don't understand what you mean. if you do this ClassDB con = new ClassDB(); you should not get the error above if you have done this ICEPack.ClassDB.Connection() to connect all you would need to do is con.Connection();

Gustavo · Answer

Lizard: Yes I still have the original code you sent me. I will look at it.

Gustavo · Answer

Lizard: I tried with the: ClassDB con = new ClassDB(); and all the other likes with con.* they give me an error. Error 18 Member 'ICEPack.ClassDB.Connection()' cannot be accessed with an instance reference; qualify it with a type name instead

theLizard · Answer

Ok thats good. Do you still have the original code for the class i sent sqlCon? if so look at the functions of that incorporate this type of code sqlCon con = new sqlCon(); int count = 0; try { con.connect(); con.command(s); con.ExecuteReader(); while (con.read()) DBDataReader = con.DBCommand.ExecuteReader(); { d.Items.Add(con.get(heading)); DBDataReader[Field_2].ToString() Array.Resize(ref ids ids.Length+1); ids[ids.Length-1] = con.get(id); } } while this is not absolutely necessary it simplifies things making it a little easier to read the code.

Gustavo · Answer

Lizard: Yes I understand. I will change some of the variables so I dont mess it up. I will make the changes now.

theLizard · Answer

You don't need these any more. DBDataAdapter.SelectCommand = DBCommand; DBDataAdapter.SelectCommand.Connection = DBConnection; and they are not used... before I go any further you need to make sure that you do not have conflicting variable names dont use DBCommand or DBCommandText in function calls like this public void LoadListBox( string DBCommandText ListBox DBListBox string Field_1 string Field_2) they are confusing with others with the same name. this string DBCommandText is an sql statement to be executed so call it what it is i simply call it sql also youi don't need to use [ ] in your sql. moving on when you have removed referances to DataAdapter here do the same in the combo box function rather than test for if(FIELD_2 != ) just make your sql select records thar are not  SELECT field list FROM table WHERE FIELD_2 != '' ORDER BY field this way all you need to do int the while(reader.read()) is DBListBox.Items.Add(DBDataReader[Field_1].ToString()   DBDataReader[Field_2]); because the only records you would be reading are those that satisfy the criteria do you understand what I mean?

Gustavo · Answer

Lizard: Ok I made it work. I changed some variables to ones that I am using is that OK? Also I had to change the line: DBListBox.Items.Add(DBDataReader[Field_1].ToString()   DBDataReader[Field_2]); I like that line: ! String .IsNullOrEmpty public void LoadListBox( string DBCommandText ListBox DBListBox string Field_1 string Field_2) { Connection(); DBCommand.CommandText = DBCommandText; DBDataAdapter.SelectCommand = DBCommand; DBDataAdapter.SelectCommand.Connection = DBConnection; DBListBox.Items.Clear(); try { DBDataReader = DBCommand.ExecuteReader(); while (DBDataReader.Read()) { if (! String .IsNullOrEmpty(DBDataReader[Field_2].ToString())) DBListBox.Items.Add(DBDataReader[Field_1].ToString() +   + DBDataReader[Field_2]); } } catch ( Exception err) { MessageBox .Show( CATCH... + err); } finally { Terminate(); } }

theLizard · Answer

Good public void LoadListBox(string sDBCommand ListBox DBListBox string Field_1 string Field_2) { ClassDB con = new ClassDB(); DBListBox.Items.Clear(); try { con.Connection(); con.DBCommand.CommandText = sDBCommand; DBDataReader = con.DBCommand.ExecuteReader(); while (DBDataReader.Read()) { if(!String.IsNullOrEmpty(DBDataReader[Field_2].ToString())) DBListBox.Items.Add(DBDataReader[Field_1].ToString()   DBDataReader[Field_2]); } } catch (Exception err) { } finally { con.Terminate(); } Make this work but only change this function but don't think that this is the final version it is not. I will show you a better way after you have learned this way ok?

Gustavo · Answer

Lizard: Sure... I would like to get rid of the DataAdapter and the table. Teach me.

theLizard · Answer

Ok I have had a look at you code you have done well with the ComboBox and ListBox and I can see that you are picking things up. If i told you that you do not need to use DataAdapters or tables to do exacatly the same what would you say do you want to learn how to do that? I half suspected that you wanted to populate text boxes what if I told you you could do that easily without using data tables? would you want to learn how to do that? Remember there is ALWAYS a better way of doing things.

Gustavo · Answer

Lizard: OK I will be patient. I will look at your code again and implement what I have missed.

theLizard · Answer

Ok but be patient I know I am repeating my self but when a function is working 100% ok it can be left to be used without doing any other work to it. Same with combo and list boxes. The power of what you are doing with this class will not be evident until it is like the class I sent you where you can read write and get individual fields with little more than a couple of lines of code in your application.

Gustavo · Answer

Lizard: BTW: The reason I wanted to do a ReadRecord was so I can populate the textBoxs on the form. I guess I can pass a GridView with one row and take out what I want. Unless you have a better idea?

Gustavo · Answer

Lizard: Here is the ClassDB.

Gustavo · Answer

Lizard: FYI: I changed too much to your code and its not working now. I will have to bring it back from a backup and step-by-step put your code back in.

theLizard · Answer

Perfect now do the ComboBox and Listbox aslso explane exactly what you want to do with the table in the main form I suspect you don not even need to use a table.

Gustavo · Answer

Lizard: Ok good... I will do the Terminate() every time. Yes the grid is perfect.

theLizard · Answer

As long as the grid is working fine do the combo and list then send the code  after that we can tackle the tabl ok?

theLizard · Answer

At the bottom of your post about the main program. Do I have to do a con.Terminate() its already in the DBClass? YES if you have changed your class according to my suggestions you will need to terminate() even if it is for the sole purpose of getting you to do this without thinking it is to ensure that you do not forget to terminate when it is absolutely necessary. the terminate function only kills things if it needs to if there is nothing to kill it won't kill anything. This is what I have in my main porgram now: public void buttonLoadGrid_Click( object sender EventArgs e) { DBCommand =  SELECT +  [Name] +  [Address_1] +  FROM +  [Company] ; ClassDB DBConnection = new ClassDB (); ClassDB .Connection(); DBConnection.LoadDataGridView(DBCommand dataGridViewGrid); DBConnection.Terminate(); //make sure there is nothing left open. } This is ok the grid is loading correct?

Gustavo · Answer

Lizard: 1) About the: DBDataAdapter = SetupDataAdapter(); Yes that one I understood I though we can do that. 2) For now I just want to return the DBDataGridView so I can populate a grid in the main program. This is the code in the main program for the Grid. public void buttonLoadGrid_Click( object sender EventArgs e) { DBCommand =  SELECT +  [Name] +  [Address_1] +  FROM +  [Company] ; ClassDB DBConnection = new ClassDB (); ClassDB .Connection(); DBConnection.LoadDataGridView(DBCommand dataGridViewGrid); } 3) I will update my code with your suggestions and send it as soon as I am done with the ComboBox and the ListBox... Unless you want me to leave those alone?

theLizard · Answer

But last night I got the GridView ComboBox ListBox to work. I was stuck on the ReadRecord. In the DBClass I was able to print out the colums of the record but it will not pass back to the main program. this is the reason you should take one step at a time what you want to do could be as simple as the grid but first you need to FULLY understand how things work. what do you want to do on the main form with a table! SetupDataAdapter(); DBDataAdapter = new SqlDataAdapter (DBCommand DBConnect); these two lines is basically the same as doing this DBDataAdapter = SetupDataAdapter(); one step at a time I have a working system to check your code so send me your updated code based on my suggestion and explain what you want to do wth the data table is there a better way!!

Gustavo · Answer

Lizard: I took everything out (//Commented) in the LoadGridView and added your code. It works fine. I will go and do the same changes to the ComboBox and ListBox now...correct? At the bottom of your post about the main program. Do I have to do a con.Terminate() its already in the DBClass? This is what I have in my main porgram now: public void buttonLoadGrid_Click( object sender EventArgs e) { DBCommand =  SELECT +  [Name] +  [Address_1] +  FROM +  [Company] ; ClassDB DBConnection = new ClassDB (); ClassDB .Connection(); DBConnection.LoadDataGridView(DBCommand dataGridViewGrid); }

Gustavo · Answer

Lizard: I have not implemented your new changes yet. But last night I got the GridView ComboBox ListBox to work. I was stuck on the ReadRecord. In the DBClass I was able to print out the colums of the record but it will not pass back to the main program.

theLizard · Answer

Take out everything you do not need for the grid they can go back after you have the grid working 100% correctly get that right the rest will be easier to understand and do.

theLizard · Answer

and it wont work it cannot work. One thing at a time. have you got the grid working 100%

Gustavo · Answer

Lizard: I just printed out your code. I will lok at it and implement the changes. yes I knwo I have some variables and other stuff in there that is not needed. I will take some out. If I take some of them out now then will ness up the main program in some areas where they do the updatedelete insert. I will get back to you when I get it cleaned up. Thanks

Gustavo · Answer

Hirendra: Thanks for the reply but it styill does not work.

Hirendra Sisodiya · Answer

you can try this: make changes in main: private void buttonDBSelectRecord_Click( object sender EventArgs e) { DBCommand =  SELECT +  [Name] +  [Address_1] +  FROM +  [Company] +  WHERE +  [Company_ID] =  + '000000' ; ClassDB DBConnection = new ClassDB (); ClassDB .Connection(); Datatable DBDataTable = DBConnection.ReadRecord(DBCommand); foreach ( DataRow DBDataRow in DBDataTable.Rows) // BAD { TheID = DBDataRow[ Name ].ToString(); MessageBox .Show( Login...TheID= + TheID); } } thanks