SqlConnection conn;
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
}
}
protected void btnConnect_Click(object sender, EventArgs e)
{
if (txtUserID.Text == “” && txtPassword.Text == “”)
{
conn = new SqlConnection();
string strConn = “Data Source=” + txtSQLName.Text + “; Initial Catalog=” + txtDBName.Text + “; Integrated Security=True”;
conn.ConnectionString = strConn;
conn.Open();
BindTable();
BindProcedure();
}
else
{
conn = new SqlConnection();
string strConn = “Data Source=” + txtSQLName.Text + “; Initial Catalog=” + txtDBName.Text + “; user id=” + txtUserID.Text + “;password=” + txtPassword.Text + “; Integrated Security=True”;
conn.ConnectionString = strConn;
conn.Open();
BindTable();
BindProcedure();
}
}
public void BindTable()
{
string str = “select Table_name from information_schema.tables”;
SqlDataAdapter adp = new SqlDataAdapter(str, conn);
DataSet ds = new DataSet();
adp.Fill(ds);
ddlTable.DataSource = ds;
ddlTable.DataTextField = “table_name”;
ddlTable.DataValueField = “table_name”;
ddlTable.DataBind();
}
public void BindProcedure()
{
string str = “select name from sys.procedures where object_definition(object_id) not like ‘%sp%'”;
SqlDataAdapter adp = new SqlDataAdapter(str, conn);
DataSet ds = new DataSet();
adp.Fill(ds);
ddlProcedure.DataSource = ds;
ddlProcedure.DataTextField = “name”;
ddlProcedure.DataValueField = “name”;
ddlProcedure.DataBind();
}
It will genrate insert store procedure, write below script on Generate button OnClick event
protected void btnGenerate_Click(object sender, EventArgs e)
{
conn = new SqlConnection();
string strConn = “Data Source=” + txtSQLName.Text + “; Initial Catalog=” + txtDBName.Text + “; user id=” + txtUserID.Text + “;password=” + txtPassword.Text + “; Integrated Security=True”;
conn.ConnectionString = strConn;
conn.Open();
string strTbl = “select column_name, data_type, character_maximum_length from information_schema.columns where columnproperty(object_id(table_name),column_name, ‘IsIdentity’)=0 and table_name='” + ddlTable.SelectedValue.ToString() + “‘”;
SqlDataAdapter adp = new SqlDataAdapter(strTbl, conn);
SqlCommand cmd1 = new SqlCommand(strTbl, conn);
SqlDataReader rdr;
rdr = cmd1.ExecuteReader();
DataTable schemaTable = rdr.GetSchemaTable();
string strPro;
ArrayList pra = new ArrayList();
ArrayList pram = new ArrayList();
string pp;
while (rdr.Read())
{
if (rdr[1].ToString() == “int”)
{
pp = “@” + rdr[0].ToString() + ” ” + “int”;
pra.Add(pp);
pram.Add(“@” + rdr[0].ToString());
}
else
{
pp = “@” + rdr[0].ToString() + ” ” + rdr[1].ToString() + “(” + rdr[2].ToString() + “)”;
pra.Add(pp);
pram.Add(“@” + rdr[0].ToString());
}
}
rdr.Close();
string ch = ddlGenerate.SelectedValue.ToString();
if (ch == “Insert Procedure”)
{
strPro = (“Create procedure ins” + ddlTable.SelectedValue.ToString() + ” (“);
for (int j = 0; j < pra.Count; j++)
{
strPro += (pra[j]);
if (j == pra.Count – 1)
{
}
else
{
strPro+=”,”;
}
}
strPro+=”) As “;
strPro +=”Insert into ” + ddlTable.SelectedValue.ToString() + ” values (“;
for (int j = 0; j < pra.Count; j++)
{
strPro += (pram[j]);
if (j == pram.Count – 1)
{
}
else
{
strPro += “,”;
}
}
strPro +=”)”;
Response.Write(strPro);
SqlCommand cmd = new SqlCo
