Dynamically generate sql query in code behid using c#

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

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.
     
     

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

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(sqlQuery,Conn);
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....

Hi Datta,

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

@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="Col1,col2,col3,.....col30";
ColValues="Val1,val2,val3,.....val30";

If is there any problem then tell me....

@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("table","ColName","ColValue","condition",INSERT);
}
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?

@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 condition,string querytype)
  {
  int count = 0;
  Obj.GetConnection();
  SqlCommand cmd = new SqlCommand();
  cmd.CommandText = SQLQUERY(table,ColumnName,ColumnValue,condition,querytype);
  cmd.CommandType = CommandType.Text;
  count = cmd.ExecuteNonQuery();
  return count;
  }

@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?

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. 

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 < ColumnSetName.Length; 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 condition,string querytype)
  {
  int count = 0;
  Obj.GetConnection();
  SqlCommand cmd = new SqlCommand();
  cmd.CommandText = SQLQUERY(table,ColumnName,ColumnValue,condition,querytype);
  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 values,wherecondition,table...
Can you suggest better way? Want to call ExecuteQuery method

Hi Datta,

How do you validate the input data?

Hi..

       You can try this code...

private enum QueryType
{
INSERT,
UPDATE,
DELETE
}

string TabName="tbCustomer";
string ColNames="Cust_Id,Cust_Name,Cust_Address,Cust_Salary";
string ColValues="01,Datta,Mumbai,77000";
string WhereCon="Cust_Id=01";

GenericQuery(TabName,ColNames,ColValues,WhereCon,QueryType.INSERT);
GenericQuery(TabName,ColNames,ColValues,WhereCon,QueryType.UPDATE);
GenericQuery(TabName,ColNames,ColValues,WhereCon,QueryType.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;

}

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 ("Text",dt,"sid");
            TextBox2.DataBindings.Add ("Text",dt,"sname");
            TextBox3.DataBindings.Add ("Text",dt,"smarks");
            TextBox4.DataBindings.Add ("Text",dt,"saddress");
            TextBox5.DataBindings.Add ("Text",dt,"year");
            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 Record",this.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

@Suthish,

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

 
 using SqlCommandBuilder you can achieve this task..