How do I: Programmatically build SQLCommand to Read Save Delete.

Question

Hello:

I would like to programmatically build an sql command to Read, Save and Delete records.

My sample table has the following fields: SEQNO, Company_ID, Name, Address_1, Address_2, Address_3. SEQNO is auto-incremetal. The others are just strings.

I know its just a few fields and I can just type it and put it in the program, but I will have a lot more fields and tables, that is why I want to do it programmatically.

theLizard · Answer

I am looking at things now will be with you asap.

Gustavo · Answer

Lizard: Was I suppose to do something or just wait for you. You are probably busy.

Gustavo · Answer

Lizard: I guess the CreateDate and CreateTime can be changed to CreateDateTiime. I see your point. The 'Note' ??? I have one (main) note per record. But I also have additional notes in a seperate 'Note' table that keeps all additional notes for all tables.

theLizard · Answer

Yes yes datecreated also contains the time created so date and time can be extracted separately you do not need 2 fields for this. I have put notes as an index because I think that notes should be stored in a seperate table which can then be categorized and all notes for a given identity can be viewed at the same time in a single location or on the form relative to the note type.

Gustavo · Answer

Lizard: I dont understand. You changed: [CreateDate] [datetime] NOT NULL [Note] [int] NULL The 'Note field is text that the user can type. I need the 'CreateDate' and 'CreateTime' seperate. You removed the 'CreateTime'.

theLizard · Answer

It is what I expected these fields do not need to be varchar I have changed them to int all these should do is point to the records in the tables. your data entry form will show these as Combo Box's. I have changed table to this USE [IPPilot] GO /****** Object: Table [dbo].[Company] Script Date: 03/23/2010 06:27:51 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Company]( [SEQNO] [int] NOT NULL [Status_ID] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL [Class_ID] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL [Group_ID] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL [Type_ID] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL [CreateBy] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL [CreateDate] [datetime] NOT NULL [Note] [int] NULL [Company_ID] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL [Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL [Address_1] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL [Address_2] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL [Address_3] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL [City] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL [State_ID] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL [Zip_ID] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL [Phone] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL [Fax] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL [WebSite] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF

Gustavo · Answer

Lizard: I am just playing around with some code. I am sure you have a better way. Here is what I have the program doing. I enclosed a word document of what the program gives me and what I want to have it do. If I can just get the column names I can write code to fill the changes in... I think.

Gustavo · Answer

Status_ID will be codes that are validated against the Status table. Status_ID = 0 Description= Invalid Status_ID = 1 Description= Valid Status_ID = -11 Description= Valid but will be reported to manager. Class_ID will be codes that are validated against the Class table. Class_ID = HQ Description= Headquarter Class_ID = DIV Description= Division Class_ID = LOC Description= Location Group_ID will be codes that are validated against the Group table. Group_ID = PLUM Description= Plumbing Group_ID = PIZZ Description= Pizza Shop Group_ID = AUTO Description= Automotive Dealer Type_ID will be codes that are validated against the Type table. Type_ID = S Description= Small Type_ID = M Description= Medium Type_ID = L Description= Large

theLizard · Answer

These are varchar(10) what are they used for give an example of the data you will store i.e. Status_ID = Active what can it hold? Status_ID Class_ID Group_ID Type_ID

theLizard · Answer

The sample code is probably ok for all fields in a table but that is not what needs to be done. as far as a select statement goes we do not need a table depending on factors to be determined we can give the function a list of fields or use the global * to get all fields so the sample code you posted is equivalent to simply saying  SELECT* FROM Company ; Give me some time to look at the company table i will put together some code and post.

Gustavo · Answer

Lizard: This is what I changed the code to. It give me an error at the marked statement. public void BuildSelect( string DBCommandText DataTable DBDataTable) { ClassDB DBConn = new ClassDB (); // Connection(); createAdapter(); MessageBox .Show( DBCommandText= + DBCommandText); DBCommand.CommandText = DBCommandText; DBDataReader = DBCommand.ExecuteReader(); DataTable table = new DataTable (); // try { string sql =  ; foreach ( DataColumn column in DBDataTable.Columns) // { if (sql.Length > 0) sql +=   ; sql += column.ColumnName; } MessageBox .Show( sql= + sql); } catch ( Exception Error) { MessageBox .Show( ClassDB: CATCH: BuildSelect:  + Error); } finally { // DO NOT TERMINATE HERE } }

Gustavo · Answer

Lizard: I found this program code and what I wanted to do is to implement this. What do you think? public static string BuildAllFieldsSQL ( DataTable table ) { string sql =  ; foreach ( DataColumn column in table.Columns ) { if ( sql.Length > 0 ) sql +=   ; sql += column.ColumnName; } return sql; } But I cant figure out how to pass it the DataTable table.

Gustavo · Answer

Lizard: Ok here is the Company tables.

theLizard · Answer

Ok. while you are doing that think about what can be left as null and what cannot because we can deal with those sorts of things when building sql.

Gustavo · Answer

Lizard: OK I will do it to the Compnay and another. Will take me a while.

theLizard · Answer

I understand but there is more to building an sql statement than just looping through the table. I know that you want to see things happening but it is really important to get your tables organized with the right data types so please do that with company post it back to me and I will show you how to proceed.

Gustavo · Answer

Lizard: I dount have all the fields in all my filles/tables yet. But here is the Company & User. My idea was to loop thru the datatable.colums and build the sql statement.

theLizard · Answer

Ok then STOP right here. Before you can go any further you MUST know what each field will contain. id's should be of type int date should be datetime so you don't need a create date and a create time. length of varchars is not important at this time. I know I may be saying what you already know but these need to be done before you can write code for them. You cannot create a dynamic build function without having the correct functions to determine what to do for what type of field. Also send me another table like you have done with this I want to compare fields that you may have duplicated... Do one table first then in sql server (MSSMSE if you dont have this get it its free) right click on the table name -> Script Table As -> Clipboard then paste into a text document or here. While I look at things do the other tables it will take time but they need to be done otherwise you will be writing code that then needs to be changed to something completely different to what you may think. Ok? this is what the Script Table As will do.. USE [temp] GO /****** Object: Table [dbo].[users] Script Date: 03/22/2010 14:36:06 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[users]( [id] [int] IDENTITY(11) NOT NULL [name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL [pwd] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF

Gustavo · Answer

Lizard: I took a snipit of it. I hope its good enough. The SEQNO is the id of the record and auto-increment. The fields that end with *_ID are linked to other tables for validation and to be shown as combobox. In this example the table name is Company and the Company number is Company_ID. The fields are at a lenght of 50 for now. They will be chnaged later. the date and time is just a text for now.

theLizard · Answer

Forget the code example take one table that you have with ALL the fields and list them in a text file and post it I want to see the field name the data type etc. I also want to see how you will show these on a form ie. which will be TextBoxes or other type of control this will help me understand how to do things so that I can show you the easy way. My common practice for identity fields (auto increment) is to simply call it id when when the id needs to be referanced in child tables I call them table Id table being the parent table so if you have table called client and a table called transaction then client has id the transaction has id and clientId if transaction has dependencies on other tables for example a table that stores notes then you would also have in transaction a field that stores the notes id notesId. Lets look at the client table it may have a dependency on say a postcode in this case you may have a table called postcodes so the client table would also have a field called postcodeId This way you are firstly not duplicating fields and secondly saving on space this is how relational database's work so if you change the field value in one it will show in all records that is linked to it by it's id. You will understand more as we go..

theLizard · Answer

Forget the code example take one table that you have with ALL the fields and list them in a text file and post it I want to see the field name the data type etc. I also want to see how you will show these on a form ie. which will be TextBoxes or other type of control this will help me understand how to do things so that I can show you the easy way. My common practice for identity fields (auto increment) is to simply call it id when when the id needs to be referanced in child tables I call them table Id table being the parent table so if you have table called client and a table called transaction then client has id the transaction has id and clientId if transaction has dependencies on other tables for example a table that stores notes then you would also have in transaction a field that stores the notes id notesId. Lets look at the client table it may have a dependency on say a postcode in this case you may have a table called postcodes so the client table would also have a field called postcodeId This way you are firstly not duplicating fields and secondly saving on space this is how relational database's work so if you change the field value in one it will show in all records that is linked to it by it's id. You will understand more as we go..

Gustavo · Answer

BTW: This is the code I already have. I have the error marked. public void BuildSelect( string DBCommandText string TableCommand string TableName) { // DBConn.BuildSelect(DBCommand textBoxTableCommand); try { ClassDB DBConn = new ClassDB (); // Connection(); createAdapter(); DBCommandText = SELECT * FROM [ + TableName + ] LIMIT 1; DBCommand.CommandText = DBCommandText; // DBDataReader = DBCommand.ExecuteReader(); // // //DataTable DBDataTable = new DataTable(); //DBDataAdapter.Fill(DBDataTable); //---------------------------------------------------- TableCommand =  ; TableCommand += SELECT ; TableCommand +=  * ; TableCommand +=  FROM ; TableCommand +=  [ + TableName + ] ; MessageBox .Show( TableCommand=  + TableCommand); // DataTable DBDataTable = new DataTable (); // foreach ( DataColumn TableColumn in DBDataTable.Columns) { TableCommand +=  [ + TableColumn.ColumnName + ] ; } // MessageBox .Show( TableCommand =  + TableCommand); // } catch { MessageBox .Show( ClassDB: CATCH: BuildSelect() ); } finally { MessageBox .Show( ClassDB: FINALLY: BuildSelect() ); }

How do I: Programmatically build SQLCommand to Read, Save, Delete.