Dynamically generate sql query in code behid using c#

Question

Hi I want to generate dynamic SQL query in code behind. For updateinsertdelete. I want to write this in data access layer. How to write this. I want to pass the no of columns and values and tablename and where necessary the where condition. How do i do this please help.

Disha Kale · Answer

Nice code thank you its use full for me

Nethra R S · Answer

I want to write a single method which can handle all the things dynamically(ie it will build the sql query dynamically)

Suthish Nair · Answer

Nethra use parameterized queries instead of concatenating strings.

Datta Kharad · Answer

Hi Nethra Q: Are you know column names of your table which you want add it? 1 Case: You have to set col values according to table col names. 2 Case: If your col names are hard code then you can add col names manually and set their values.

Nethra R S · Answer

Hi Datta That is long process and what is the gurantee that there will be no data type mismatch ie For ex: ColumnName=Phone+Name;(just an example ok) ColumnValue=txtname.text+txtphone.text; Then wrong data will be entered for wrong column right? Even if the value is inserted in the sql table then it will be wrong isn`t it? For Phone Column your name will be saved and for name your phone no will be saved

Datta Kharad · Answer

Hi Nethra string sqlQuery = Select top(0) from tableName; // top(0) gives u just the schema ie. column name from table OR string sqlQuery=Select column_name* from information_schema.columns where table_name = 'YourTableName'; conn.Open(); SqlCommand cmd = new SqlCommand(sqlQueryConn); SqlDataReader reader = cmd.ExecuteReader(); DataTable dt = new DataTable(); dt.Load(reader); //fill the string variable (ColumnNames) with column name string ColumnNames = string.Empty; foreach(DataColumn column in dt.Columns) { if (string.IsNullOrEmpty(ColumnNames)) { ColumnNames = column; } else { ColumnNames = ColumnNames +   + column; } } //You will get 30 Column Names in variable (ColumnNmaes with comma seperator) Inform me....

Nethra R S · Answer

Hi Datta Those ColumnNames are the sql table columns.. Please can you tell me how to pass all 30 columns

Datta Kharad · Answer

@Nethra Ok..One thing tell me where is your columns name? i mean.. in which your 30 names are present either datatable or dataset or array. First you have to get columns name and their value in string or stringbuilder with comma seperator. Once you get columns name and their values then you can pass these parameter to method. Example: ColNames=Col1col2col3.....col30; ColValues=Val1val2val3.....val30; If is there any problem then tell me....

Nethra R S · Answer

@Datta Suppose i have some 30 columns and their values.. How to pass them as ColumnName and ColumnValue. Since the ColumnName will have a long string.. At button click event(save button) { string ColName=Name+Address;(should i add 30 column names like this ?) string ColValue=txtName.text+txtaddress.text; obj.ExecuteQuery(tableColNameColValueconditionINSERT); } Do you mean i should call ExecuteQuery method like this? How to add all the 30 column names to ColumnName and pass ColumnName to the ExecuteQuery method?

Datta Kharad · Answer

@Nethra Simply You want to just double click on button and write the calling function with parameter. protected void Button1_Click(object sender EventArgs e) { ExecuteQuery (TabName ColNames ColValues WhereCon QueryType.INSERT); ExecuteQuery (TabName ColNames ColValues WhereCon QueryType.UPDATE); ExecuteQuery (TabName ColNames ColValues WhereCon QueryType.DELETE); } //Here your function definition public int ExecuteQuery(string table string ColumnName string ColumnValue string conditionstring querytype) { int count = 0; Obj.GetConnection(); SqlCommand cmd = new SqlCommand(); cmd.CommandText = SQLQUERY(tableColumnNameColumnValueconditionquerytype); cmd.CommandType = CommandType.Text; count = cmd.ExecuteNonQuery(); return count; }

Nethra R S · Answer

@Datta  I am not understanding your answer.. I said i want to call the ExecuteQuery method that i have written.. See the text in red color. from aspx code behid i want to call the method at button click event.. How can i do that?

Datta Kharad · Answer

Hi Nethra You can try this... input type =button value =CallMethod onclick =ExecuteQuery(Pass parameter) /> OR I just give example of getimage: ExecuteQuery (DataBinder.Eval(Container.DataItem Stock).ToString()) %>'/> I think this will help you. If your query will solve then inform me. If you have got any other way also tell me.

Nethra R S · Answer

Hi Datta I have written the code like this.. In your method you are connecting to database in same method but i am splitting it like this: private object SQLQUERY(string Table string ColumnName string ColumnValue string condition string squeryType) { string SQLQuery = string.Empty; if (squeryType == QueryType.INSERT) { SQLQuery = INSERT INTO '+Table+'('+ColumnName+') VALUES ('+ColumnValue+'); } else if (squeryType == QueryType.UPDATE) { string[] ColumnSetName = ColumnName.Split(''); string[] ColumnSetValue = ColumnValue.Split(''); string SetCondition = string.Empty; for (int count = 0; count { if (string.IsNullOrEmpty(SetCondition)) { SetCondition = ColumnSetName[count] + = + ColumnSetValue[count]; } else { SetCondition = SetCondition +  + ColumnSetName[count] + = + ColumnSetValue[count]; } } SQLQuery = UPDATE ' + Table + ' SET ' + SetCondition + ' WHERE ' + condition + '; } else if (squeryType == QueryType.DELETE) { SQLQuery = DELETE '+Table+' WHERE '+condition+'; } return SQLQuery; } //After Generating the query connect to database and execute the query public int ExecuteQuery(string table string ColumnName string ColumnValue string conditionstring querytype) { int count = 0; Obj.GetConnection(); SqlCommand cmd = new SqlCommand(); cmd.CommandText = SQLQUERY(tableColumnNameColumnValueconditionquerytype); cmd.CommandType = CommandType.Text; count = cmd.ExecuteNonQuery(); return count; } ======================================================== Now how to call this in the page behind in aspx page? I want to pass the column names and column valueswhereconditiontable... Can you suggest better way? Want to call ExecuteQuery method

Nethra R S · Answer

Hi Datta How do you validate the input data?

Datta Kharad · Answer

Hi.. You can try this code... private enum QueryType { INSERT UPDATE DELETE } string TabName=tbCustomer; string ColNames=Cust_IdCust_NameCust_AddressCust_Salary; string ColValues=01DattaMumbai77000; string WhereCon=Cust_Id=01; GenericQuery(TabNameColNamesColValuesWhereConQueryType. INSERT ); GenericQuery(TabNameColNamesColValuesWhereConQueryType. UPDATE ); GenericQuery(TabNameColNamesColValuesWhereConQueryType. DELETE ); private object GenericQuery (string sTabName string sColNames string sColValues string sWhereCon string `sQueryType) { string SQLQuery=string.Empty; if(sQueryType==QueryType.INSERT) { SQLQuery=Insert into +sTabName+ ('+sColNames+') Values ('+sColValues+'); } else if(sQueryType==QueryType.UPDATE) { string[] sColSetName=sColNames.Split(''); string[] sColSetVal= sColValues.Split(''); string sSetCondition=string.Empty; for(int iCount=0;iCount { if (string.IsNullOrEmpty(sSetCondition)) { sSetCondition = sColSetName[iCount] + = + sColSetVal[iCount]; } else { sSetCondition = sSetCondition +   + sColSetName[iCount] + = + sColSetVal[iCount]; } } SQLQuery=Update +sTabName+  Set +sSetCondition+ Where +sWhereCon+; } else if(sQueryType==QueryType.DELETE) { SQLQuery=Delete from +sTabName+ Where  +sWhereCon+ ; } Con.Open(); Cmd.Connection = Con; Cmd.CommandText = SQLQuery; object Ret; Ret = CommandTrn.ExecuteNonQuery(); return Ret; }

Satyapriya Nayak · Answer

Hi Nethra Try this... using System; using System.Drawing; using System.Collections; using System.ComponentModel; using System.Windows.Forms; using System.Data; using System.Data.SqlClient; namespace Ado_Project_Data_Insert_Update_Save_Delete_Using_Disconnected_Mode_In_CSharp { /// /// Summary description for Form1. /// public class Form1 : System.Windows.Forms.Form { SqlConnection con=new SqlConnection(workstation id=\HOME-Z8CKE1NER2\;packet size=4096;user id=sa;initial catalog=Dotn + et;persist security info=False); SqlCommand com; SqlDataAdapter sqlda; DataSet ds=new DataSet(); SqlCommandBuilder objcom; String str; int flag; DataTable dt; DataRow dr; internal System.Windows.Forms.Button btnclose; internal System.Windows.Forms.Button btnload; internal System.Windows.Forms.Button btndelete; internal System.Windows.Forms.Button btnsave; internal System.Windows.Forms.Button btnmodify; internal System.Windows.Forms.Button btnadd; internal System.Windows.Forms.Button btnlast; internal System.Windows.Forms.Button btnprevious; internal System.Windows.Forms.Button btnnext; internal System.Windows.Forms.Button btnfirst; internal System.Windows.Forms.Label Label5; internal System.Windows.Forms.Label Label4; internal System.Windows.Forms.Label Label3; internal System.Windows.Forms.Label Label2; internal System.Windows.Forms.Label Label1; internal System.Windows.Forms.TextBox TextBox5; internal System.Windows.Forms.TextBox TextBox4; internal System.Windows.Forms.TextBox TextBox3; internal System.Windows.Forms.TextBox TextBox2; internal System.Windows.Forms.TextBox TextBox1; internal System.Windows.Forms.DataGrid dataGrid1; /// /// Required designer variable. /// private System.ComponentModel.Container components = null; public Form1() { // // Required for Windows Form Designer support // InitializeComponent(); // // TODO: Add any constructor code after InitializeComponent call // } /// /// Clean up any resources being used. /// /// protected override void Dispose( bool disposing ) { if( disposing ) { if (components != null) { components.Dispose(); } } base.Dispose( disposing ); } #region Windows Form Designer generated code /// /// Required method for Designer support - do not modify /// the contents of this method with the code editor. /// private void InitializeComponent() { this.btnclose = new System.Windows.Forms.Button(); this.btnload = new System.Windows.Forms.Button(); this.dataGrid1 = new System.Windows.Forms.DataGrid(); this.btndelete = new System.Windows.Forms.Button(); this.btnsave = new System.Windows.Forms.Button(); this.btnmodify = new System.Windows.Forms.Button(); this.btnadd = new System.Windows.Forms.Button(); this.btnlast = new System.Windows.Forms.Button(); this.btnprevious = new System.Windows.Forms.Button(); this.btnnext = new System.Windows.Forms.Button(); this.btnfirst = new System.Windows.Forms.Button(); this.Label5 = new System.Windows.Forms.Label(); this.Label4 = new System.Windows.Forms.Label(); this.Label3 = new System.Windows.Forms.Label(); this.Label2 = new System.Windows.Forms.Label(); this.Label1 = new System.Windows.Forms.Label(); this.TextBox5 = new System.Windows.Forms.TextBox(); this.TextBox4 = new System.Windows.Forms.TextBox(); this.TextBox3 = new System.Windows.Forms.TextBox(); this.TextBox2 = new System.Windows.Forms.TextBox(); this.TextBox1 = new System.Windows.Forms.TextBox(); ((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit(); this.SuspendLayout(); // // btnclose // this.btnclose.Location = new System.Drawing.Point(368 292); this.btnclose.Name = btnclose; this.btnclose.Size = new System.Drawing.Size(88 23); this.btnclose.TabIndex = 41; this.btnclose.Text = Form Close; this.btnclose.Click += new System.EventHandler(this.btnclose_Click); // // btnload // this.btnload.Location = new System.Drawing.Point(480 244); this.btnload.Name = btnload; this.btnload.Size = new System.Drawing.Size(96 23); this.btnload.TabIndex = 40; this.btnload.Text = Load Records; this.btnload.Click += new System.EventHandler(this.btnload_Click); // // dataGrid1 // this.dataGrid1.DataMember = ; this.dataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText; this.dataGrid1.Location = new System.Drawing.Point(304 16); this.dataGrid1.Name = dataGrid1; this.dataGrid1.Size = new System.Drawing.Size(312 160); this.dataGrid1.TabIndex = 39; // // btndelete // this.btndelete.Location = new System.Drawing.Point(280 268); this.btndelete.Name = btndelete; this.btndelete.TabIndex = 38; this.btndelete.Text = Delete; this.btndelete.Click += new System.EventHandler(this.btndelete_Click); // // btnsave // this.btnsave.Location = new System.Drawing.Point(192 268); this.btnsave.Name = btnsave; this.btnsave.TabIndex = 37; this.btnsave.Text = Save; this.btnsave.Click += new System.EventHandler(this.btnsave_Click); // // btnmodify // this.btnmodify.Location = new System.Drawing.Point(104 268); this.btnmodify.Name = btnmodify; this.btnmodify.TabIndex = 36; this.btnmodify.Text = Modify; this.btnmodify.Click += new System.EventHandler(this.btnmodify_Click); // // btnadd // this.btnadd.Location = new System.Drawing.Point(16 268); this.btnadd.Name = btnadd; this.btnadd.TabIndex = 35; this.btnadd.Text = Add; this.btnadd.Click += new System.EventHandler(this.btnadd_Click); // // btnlast // this.btnlast.Location = new System.Drawing.Point(280 220); this.btnlast.Name = btnlast; this.btnlast.TabIndex = 34; this.btnlast.Text = Last; this.btnlast.Click += new System.EventHandler(this.btnlast_Click); // // btnprevious // this.btnprevious.Location = new System.Drawing.Point(192 220); this.btnprevious.Name = btnprevious; this.btnprevious.TabIndex = 33; this.btnprevious.Text = Previous; this.btnprevious.Click += new System.EventHandler(this.btnprevious_Click); // // btnnext // this.btnnext.Location = new System.Drawing.Point(104 220); this.btnnext.Name = btnnext; this.btnnext.TabIndex = 32; this.btnnext.Text = Next; this.btnnext.Click += new System.EventHandler(this.btnnext_Click); // // btnfirst // this.btnfirst.Location = new System.Drawing.Point(16 220); this.btnfirst.Name = btnfirst; this.btnfirst.TabIndex = 31; this.btnfirst.Text = First; this.btnfirst.Click += new System.EventHandler(this.btnfirst_Click); // // Label5 // this.Label5.Location = new System.Drawing.Point(8 148); this.Label5.Name = Label5; this.Label5.TabIndex = 30; this.Label5.Text = Year; // // Label4 // this.Label4.Location = new System.Drawing.Point(8 116); this.Label4.Name = Label4; this.Label4.TabIndex = 29; this.Label4.Text = Student Address; // // Label3 // this.Label3.Location = new System.Drawing.Point(8 84); this.Label3.Name = Label3; this.Label3.TabIndex = 28; this.Label3.Text = Student Marks; // // Label2 // this.Label2.Location = new System.Drawing.Point(8 52); this.Label2.Name = Label2; this.Label2.TabIndex = 27; this.Label2.Text = Student Name; // // Label1 // this.Label1.Location = new System.Drawing.Point(8 20); this.Label1.Name = Label1; this.Label1.TabIndex = 26; this.Label1.Text = Student Id; // // TextBox5 // this.TextBox5.Location = new System.Drawing.Point(128 148); this.TextBox5.Name = TextBox5; this.TextBox5.TabIndex = 25; this.TextBox5.Text = ; // // TextBox4 // this.TextBox4.Location = new System.Drawing.Point(128 116); this.TextBox4.Name = TextBox4; this.TextBox4.TabIndex = 24; this.TextBox4.Text = ; // // TextBox3 // this.TextBox3.Location = new System.Drawing.Point(128 84); this.TextBox3.Name = TextBox3; this.TextBox3.TabIndex = 23; this.TextBox3.Text = ; // // TextBox2 // this.TextBox2.Location = new System.Drawing.Point(128 52); this.TextBox2.Name = TextBox2; this.TextBox2.TabIndex = 22; this.TextBox2.Text = ; // // TextBox1 // this.TextBox1.Location = new System.Drawing.Point(128 20); this.TextBox1.Name = TextBox1; this.TextBox1.TabIndex = 21; this.TextBox1.Text = ; // // Form1 // this.AutoScaleBaseSize = new System.Drawing.Size(5 13); this.ClientSize = new System.Drawing.Size(664 334); this.Controls.Add(this.btnclose); this.Controls.Add(this.btnload); this.Controls.Add(this.dataGrid1); this.Controls.Add(this.btndelete); this.Controls.Add(this.btnsave); this.Controls.Add(this.btnmodify); this.Controls.Add(this.btnadd); this.Controls.Add(this.btnlast); this.Controls.Add(this.btnprevious); this.Controls.Add(this.btnnext); this.Controls.Add(this.btnfirst); this.Controls.Add(this.Label5); this.Controls.Add(this.Label4); this.Controls.Add(this.Label3); this.Controls.Add(this.Label2); this.Controls.Add(this.Label1); this.Controls.Add(this.TextBox5); this.Controls.Add(this.TextBox4); this.Controls.Add(this.TextBox3); this.Controls.Add(this.TextBox2); this.Controls.Add(this.TextBox1); this.Name = Form1; this.Text = Form1; this.Load += new System.EventHandler(this.Form1_Load); ((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).EndInit(); this.ResumeLayout(false); } #endregion /// /// The main entry point for the application. /// [STAThread] static void Main() { Application.Run(new Form1()); } private void Form1_Load(object sender System.EventArgs e) { str = select * from student; com = new SqlCommand(str con); sqlda = new SqlDataAdapter(com); ds = new DataSet(); sqlda.Fill(ds student); dt = ds.Tables[student]; TextBox1.DataBindings.Add (Textdtsid); TextBox2.DataBindings.Add (Textdtsname); TextBox3.DataBindings.Add (Textdtsmarks); TextBox4.DataBindings.Add (Textdtsaddress); TextBox5.DataBindings.Add (Textdtyear); dataGrid1.DataSource =ds; dataGrid1.DataMember =student; } private void btnfirst_Click(object sender System.EventArgs e) { this.BindingContext[dt].Position=0; } private void btnnext_Click(object sender System.EventArgs e) { this.BindingContext[dt].Position+=1; } private void btnprevious_Click(object sender System.EventArgs e) { this.BindingContext[dt].Position-=1; } private void btnlast_Click(object sender System.EventArgs e) { this.BindingContext[dt].Position =dt.Rows.Count-1; } private void btnadd_Click(object sender System.EventArgs e) { flag=1; TextBox1.Text = ; TextBox2.Text = ; TextBox3.Text = ; TextBox4.Text = ; TextBox5.Text = ; TextBox1.Focus(); } private void showall() { con.Open (); str = select * from student; com = new SqlCommand(str con); sqlda = new SqlDataAdapter(com); ds = new DataSet(); sqlda.Fill(ds student); con.Close(); dataGrid1.DataSource =ds; dataGrid1.DataMember =student; } private void btnmodify_Click(object sender System.EventArgs e) { flag=2; dr=dt.Rows[this.BindingContext[dt].Position]; TextBox1.Focus(); } private void btnsave_Click(object sender System.EventArgs e) { if (flag==1) { dr = dt.NewRow(); dr[sid] = TextBox1.Text; dr[sname] = TextBox2.Text; dr[smarks] = TextBox3.Text; dr[saddress] = TextBox4.Text; dr[year] = TextBox5.Text; dt.Rows.Add(dr); objcom= new SqlCommandBuilder(sqlda); sqlda.Update(ds student); sqlda.Fill(ds student); MessageBox.Show(Record Sucessfully Inserted); } else if (flag==2) { dr.BeginEdit(); dr[sid] = TextBox1.Text; dr[sname] = TextBox2.Text; dr[smarks] = int.Parse(TextBox3.Text); dr[saddress] = TextBox4.Text; dr[year] = TextBox5.Text; dr.EndEdit(); objcom = new SqlCommandBuilder(sqlda); sqlda.Update(ds student); sqlda.Fill(ds student); MessageBox.Show(Record Sucessfully Modified); } showall(); } private void btndelete_Click(object sender System.EventArgs e) { if( MessageBox.Show (do u 1 2 delete the Recordthis.Text MessageBoxButtons.YesNo  MessageBoxIcon.Question )== DialogResult.Yes ) { dr = dt.Rows[this.BindingContext[dt].Position]; dr.Delete(); objcom = new SqlCommandBuilder(sqlda); sqlda.Update(ds student); sqlda.Fill(ds student); MessageBox.Show(Record Sucessfully Deleted); showall(); } } private void btnclose_Click(object sender System.EventArgs e) { Application.Exit(); this.Close(); } } } Thanks If this post helps you mark it as answer

Nethra R S · Answer

@Suthish I do not want to use the sqlcommandbuilder. Can you please suggest some alternative?

Suthish Nair · Answer

using SqlCommandBuilder you can achieve this task..