Multi Form Multi Tables: What would be the best way to update the tables?

Question

Hello:

I have around 50 tables and forms, with around 30 fields per table/form. I will need to do Read/Insert/Update the tables.

Instead of writing code as below (sample), is there a simpler way? As below, I might have to add 90+ lines in the programs and might have to be re-used in some other programs, if needed.

Note: I might have some mistakes in the code below, but I hope you get the idea.

----------------------------------------------------------------------------------------------------------------------------

READ:

SELECT Fields01, Fields02, Field03...Field30 FROM Table WHERE Field1 = '12345'

textBox01.Text = ReadRecord["Field01"].ToString();

textBox01.Text = ReadRecord["Field02"].ToString();

textBox01.Text = ReadRecord["Field03"].ToString();

...

textBox01.Text = ReadRecord["Field30"].ToString();

----------------------------------------------------------------------------------------------------------------------------

UPDATE:

SELECT Fields01, Fields02, Field03...Field30 FROM Table WHERE Field1 = '12345'

textBox01.Text = ReadRecord["Field01"].ToString();

textBox01.Text = ReadRecord["Field02"].ToString();

textBox01.Text = ReadRecord["Field03"].ToString();

...

textBox01.Text = ReadRecord["Field30"].ToString();

Then.... Entry of fields in form... Then...

UPDATE Table SET Field01 = textBox01.Text , Field02 = textBox02.Text, Field03= textBox02.Text...Field30= textBox30.Text

----------------------------------------------------------------------------------------------------------------------------

INSERT :

textBox01.Text = "";

textBox02.Text = "";

textBox03.Text = "";

...

textBox30.Text = "";

Then.... Entry of fields in form... Then...

INSERT Table (Field01, Field02, Field03...Field30) VALUES (textBox01.Text, textBox02.Text, textBox03.Text... textBox30.Text)

theLizard · Answer

My mistake I misread your problem. This is where we can have a dynamic function if you are able to send the function 1 or 2 or more fields then instead of specifying the fields separately in the call like this LoadComboBox( string DBCommandText ComboBox DBComboBox string Field_1 string Field_2) we can do somthing like this LoadComboBox( string DBCommandText ComboBox DBComboBox string [] Fields ) { while(reader.read()) { string s = ; for(int i=0; i { s += formatValue( DBDataReader[Fields[i]].ToString() ) s+= (i } DBComboBox.Items.Add(s ); } } string[] fields = {Company_id Name} or simply string[] fields = {Company_id} you will work it out...

theLizard · Answer

yes start a new thread. The read record is dependant on the other things so that can wait a little. Can't do the build? stuff just yet to do this stuff we need to know other things first so post all the fields of a (1) table (record) that you want to read save or delete. this part will be show you how to do things for ALL tables in your application. Remember one step at a time when you have finished this app you will have learned how to create any application faster than using any databound control because all the functions we are creating will work in any application using databases you do create. You will even be able to create an application where all you know is the connection string and from this you will be able to load a treeview with the tables fields field data types and keys of the catalog for the connection string. Your application will not need to know anything about the database.

Gustavo · Answer

Lizard: What I have left is: SELECT INSERT UPDATE and DELETE. But I would like to do those after I have the BuildSelect BuildInsert BuildUpdate and BuildDelete.

Gustavo · Answer

Lizard: The ReadRecord routine is done. I think its pretty clean. Code listed below: BTW: YOu want to create another thread? public void ReadRecord( string DBCommandText DataTable DBDataTable) { ClassDB DBConn = new ClassDB (); // Connection(); createAdapter(); DBCommand.CommandText = DBCommandText; DBDataReader = DBCommand.ExecuteReader(); try { DBDataReader = DBCommand.ExecuteReader(); } catch ( Exception Error) { //MessageBox.Show(ClassDB: CATCH: LoadDataGridView:  + Error); // DO NOT CATCH } finally { // DO NOT TERMINATE HERE } }

theLizard · Answer

Great what is your next step...

Gustavo · Answer

Lizard: Yep its cool. I will be keeping the delimiter to comma for all my applications. But I will put the code in there for future references. Thanks

theLizard · Answer

Do what you think you are comfortable with you could also do the same for ListBox if you have not already decided to. Can you see how one function will now load any combo box? as an enhancement you can do this LoadComboBox( string DBCommandText ComboBox DBComboBox string [] Fields  string delimiter ) s+= (i delimiter : ); Can you now imagine what else can be done!.

Gustavo · Answer

Lizard: Ok got it to work. This was fun and learned a few things...LOL. I could not get the formatValue to work. I guess it can wait. I changed your code to the following: I did not need the -1 and the Count to Count(). I tested it with 12 and 3 fields. Do you want me to implement this into the comboBox or wait till the ListBox enhancements are complete? while (DBDataReader.Read()) { string TheString =  ; for ( int i=0; i { TheString += DBDataReader[Fields[i]].ToString(); TheString += (i   :  ); } DBListBox.Items.Add(TheString); }

theLizard · Answer

This is also why I wanted you to hold back on the other things loading records can be done in similar fashion (but different)

theLizard · Answer

yes we are now talking dynamic. ps change (i

Gustavo · Answer

Lizard: Ok let me work it out. Will get back to you when I have it working. That will be cool so I can send as many fields as I want...

Gustavo · Answer

Lizard: Ok I took out: DataTable DBDataTable = new DataTable (); DBDataAdapter.Fill(DBDataTable); and createadapter(); It still does not work. I tried also with a: DBDataReader = DBCommand.ExecuteReader(); and it still does not work.

Gustavo · Answer

Lizard: I did put the .ToString(). But it still does NOT work if I pass: DBConn.LoadListBox(DBCommand listBoxList Company_ID   ); But it DOES work if I pass: DBConn.LoadListBox(DBCommand listBoxList Company_ID  Name );

theLizard · Answer

whenever we are using a data reader we do not need a data adapter so you can get rid of these DataTable DBDataTable = new DataTable (); DBDataAdapter.Fill(DBDataTable); and createadapter();

theLizard · Answer

first try putting a . ToString() at the end of this DBDataReader[Field_2] but we can treat those cases. this can be done in a number of ways but to keep it simple and always be available we can create a function that will fix the problem from this ComboBox.Items.Add(DBDataReader[Field_1].ToString() +   + DBDataReader[Field_2]); to this ComboBox.Items.Add(formattedValue(DBDataReader[Field_1].ToString()) +   + formattedValue(DBDataReader[Field_2]).ToString()); private string formattedValue(string field) { //we could also format the string any way that we like for argument sake if it is a double value // formattedValue(double field) r---> return( field.ToString(#####0.00)); return( String.IsNullOrEmpty(field)?  : field); } Ok.

Gustavo · Answer

Lizard: I just put in your code and I get the error: LoadComboBox: System.NullReferenceException: Object reference not set to an instance of an object. Did I forget something? Here is the code that in there now: public void LoadComboBox( string DBCommandText ComboBox DBComboBox string Field_1 string Field_2) { ClassDB DBConn = new ClassDB (); DBComboBox.Items.Clear(); // Connection(); createAdapter(); DBCommand.CommandText = DBCommandText; try { DataTable DBDataTable = new DataTable (); DBDataAdapter.Fill(DBDataTable); // //foreach (DataRow DBDataRow in DBDataTable.Rows) //{ // TheID = DBDataRow[Field_1].ToString(); // TheList = TheID; // if (Field_2 != ) // { // TheDescription = DBDataRow[Field_2].ToString(); // TheList +=   + TheDescription; // } // DBComboBox.Items.Add(TheList); //} // while (DBDataReader.Read()) { if (! String .IsNullOrEmpty(DBDataReader[Field_2].ToString())) DBComboBox.Items.Add(DBDataReader[Field_1].ToString() +   + DBDataReader[Field_2]); } // Terminate(); } catch ( Exception Error) { MessageBox .Show( ClassDB: CATCH: LoadComboBox:  + Error); } finally { Terminate(); }

Gustavo · Answer

Lizard: From the main program. I the ListBox routine I pass this: DBConn.LoadListBox(DBCommand listBoxList Company_ID  Name ); But if I pass this it will mess it up: DBConn.LoadListBox(DBCommand listBoxList Company_ID   );

theLizard · Answer

I dont understand what you mean where do you pass the blank?

Gustavo · Answer

Lizard: I did the: while (DBDataReader.Read()) { if (!String.IsNullOrEmpty(DBDataReader[Field_2].ToString())) ComboBox.Items.Add(DBDataReader[Field_1].ToString() +   + DBDataReader[Field_2]); } But: I pass a  (blank) to the routine it messed it up. So I put it back with the if/else. What I want to do is if I pass 1 value then the comboBox will only show that column if I pass 2 fields then show both with a selerator of a ''. Does your code do that? The BuildSelect can wait. I was just doing it to keep me busy.

theLizard · Answer

@Sam Giorgio will be a better programmer for his effort he is learning to do things that you take for granted he will develop better applications by knowing how to do things him self rather than having an IDE do the work which MUST ultimately control how and what you are able to do taking away the CONTROL you sometimes need. Forgot Sam are you able to write algorithms to dynamically create insert update and other sql given just the table name? or is this too hard for you to do!

theLizard · Answer

load combo box should be like this as well while (DBDataReader.Read()) { if (!String.IsNullOrEmpty(DBDataReader[Field_2].ToString())) ComboBox.Items.Add(DBDataReader[Field_1].ToString() +   + DBDataReader[Field_2]); } basically you can just copy the code from list box to combo box. From the BuildSelect I now know your are learning tip you do not need the brackets  [ or ]  these are primarily used if you have field names that are the same as system names eg if you had a field in your table called user then you would use the square brackets like '[user]' but if you changed user to appUser you would not need the brackets. Allother fields TableCommand +=  [ + TableColumn.ColumnName + ]; But lets just wait a little for that lets go to the insert update and delete parts. all you need for these is this one function (change the vars to yours good practice) this function should not care what you want just do it or fail. public bool execSQL(string s) { bool success = true; //is Always true until an exception is encountered... sqlCon con = new sqlCon(); try { con.connect(); //connect to db con.command(s); //set the command text con.beginTrans(); //begin the transaction con.write(); //write the transaction this is another function you should implement see my original code. con.commitTrans();//commit it to db if success } catch (Exception err) { con.rollbackTrans(); //if one part fails they ALL fail this is especially true in MULTI statement sql success = false; //show that it failed } finally { con.terminate(); } return (success); the build parts should be made as dynamic functions to be handled by a different class remember I had a con.GetSchema()! this will be used for doing what you are trying to do in your BuildSelect or BuildUpdate etc. The first thing I have noticed in BuildInsert is one very IMPORTANT thing that you are NOT doing (I know it is early stages) depending on the field type the value part of the sql statement should have the appropriate token (I call them tokens you don't need to) eg TableCommand = ; TableCommand += INSERT; TableCommand +=  [ + TableName + ]; TableCommand +=  (; TableCommand +=  fieldname...; TableCommand +=  ); TableCommand +=  VALUES; TableCommand +=  (; at this point you need to know what data type the field value is so that you can do the necessary things string token = ; switch(fieldNameDataType(fieldName)) //this will go and fetch the data type for the field and return it's type. Also this would be done in its own function so that you only get the combined token fieldvalue token '2010/10/03' { case SqlString: //these are not the real data type names we will use the correct ones when we get there. case SqlText: case SqlDate: token = '; //single quote break; } TableCommand += token +  fieldvalue... + token [+ ] ; TableCommand +=  ); do you undestand what I mean?

Sam Hobbs · Answer

Yes there absolutely definintely are much easier ways to do what you are doing. The idea to create a program/routine to generate SELECT/INSERT/UPDATE/DELETE commands is nothing new. That capability already exists in VS and is much more advanced and powerful than you can accomplish. It is a little late to switch to the easy way to do it. You and theLizard are too quick for me. I think you would have accomplished more by now if you had invested more time in learning. Learning takes time in the front-end and saves time in the back-end.

Gustavo · Answer

Lizard: I just looked at it and I think I am done. What did I miss? I am enclosing a recent zip. It has some new routines at the bottom that I started working on to keep me busy. I will stop work on these for now.

Gustavo · Answer

Lizard: Ok I thought we where going to only do the ListBox routine. OK I will fix up the ComboBox and look at the ReadRecord.

theLizard · Answer

Giorgio one thing at a time from the last zip you posted you have not finished the sql connection class you need to change the combo box ReadRecord needs to be looked at. I already said that once the class is finished these other things will be easier.

Gustavo · Answer

FYI: I think I will try to create a program/routine to generate SELECT/INSERT/UPDATE/DELETE commands. (Will try...)

Multi Form, Multi Tables: What would be the best way to update the tables?