protected void Page_Load(object sender, EventArgs e) { //对连接串赋值 if (this.Session["constr"] != null) { this.txt_con.Value = this.Session["constr"].ToString(); } if (!this.IsPostBack) { if (this.txt_con.Value != "") { strCon = this.txt_con.Value; } else { strCon = System.Configuration.ConfigurationManager.AppSettings["HYOA_SQL_PRO"].ToString(); } //连接数据库 DAO_sql db = new DAO_sql(strCon); //找到库名 string sqlSelect = " SELECT Name from Master..SysDatabases ORDER BY Name "; DataTable dt = new DataTable(); try { dt = db.GetDataTable(sqlSelect); } catch (Exception ex) { Response.Write("<script>alert('对不起,连接串输入有错误,sql生成器急将重置!');</script>"); this.txt_con.Value = ""; if (this.Session["constr"] != null) { this.Session["constr"] = null; } Response.Write("<script>window.location='" + this.Request.Url.ToString() + "';</script>"); } this.ddl_database.DataSource = dt; this.ddl_database.DataTextField = "name"; this.ddl_database.DataValueField = "name"; this.ddl_database.DataBind(); this.ddl_database.Items.Insert(0, new ListItem("--请选择--", "")); this.ddl_tableid.Items.Insert(0, new ListItem("--请选择--", "")); } }
//得到赋值语句 protected void btn_voluation_Click(object sender, EventArgs e) { if (this.ddl_tableid.SelectedValue == "") { Response.Write("<script>alert('对不起,请输入表名!!!');</script>"); this.lb_showsql.Text = " "; return; } if (this.txt_con.Value != "") { strCon = this.txt_con.Value; } else { strCon = "server=(local);database=" + this.ddl_database.SelectedValue + ";uid=sa;pwd=sa!@#$%^*(sa;"; } DAO_sql db = new DAO_sql(strCon); //得到字段名 string sqlSelect = " select a.name as [name],b.name as type from syscolumns a,systypes b where a.id=object_id('" + this.ddl_tableid.SelectedValue + "') and a.xtype=b.xtype"; if (this.txt_sql.Value != "") { string[] arrySql = txt_sql.Value.Split('#'); string sqlWhere = ""; for (int i = 0; i < arrySql.Length; i++) { if (i == arrySql.Length - 1) { sqlWhere += "'" + arrySql[i] + "'"; } else { sqlWhere += "'" + arrySql[i] + "',"; } } sqlSelect += " and a.name not in(" + sqlWhere + ") "; } DataTable dt = new DataTable(); try { dt = db.GetDataTable(sqlSelect); } catch (Exception ex) { Response.Write("<script>alert('对不起,连接串输入有错误,系统将重置!');</script>"); this.txt_con.Value = ""; if (this.Session["constr"] != null) { this.Session["constr"] = null; } Response.Write("<script>window.location='" + this.Request.Url.ToString() + "';</script>"); } string strInsert = " "; if (dt.Rows.Count > 0) { for (int i = 0; i < dt.Rows.Count; i++) { strInsert += this.ddl_tableid.SelectedValue + "." + dt.Rows[i]["name"].ToString() + "=\"\"; <br/>"; } } this.lb_showsql.Text = strInsert; }
protected void ddl_database_SelectedIndexChanged(object sender, EventArgs e) { if (ddl_database.SelectedValue != "") { if (this.txt_con.Value != "") { strCon = this.txt_con.Value; } else { strCon = System.Configuration.ConfigurationManager.AppSettings["HYOA_SQL_PRO"].ToString(); } DAO_sql db = new DAO_sql(strCon); string sqlSelect = " SELECT Name FROM " + ddl_database.SelectedValue + "..SysObjects Where XType='U' ORDER BY Name "; DataTable dt = new DataTable(); try { dt = db.GetDataTable(sqlSelect); } catch (Exception ex) { Response.Write("<script>alert('对不起,连接串输入有错误,系统将重置!');</script>"); this.txt_con.Value = ""; if (this.Session["constr"] != null) { this.Session["constr"] = null; } Response.Write("<script>window.location='" + this.Request.Url.ToString() + "';</script>"); } this.ddl_tableid.DataSource = dt; this.ddl_tableid.DataTextField = "name"; this.ddl_tableid.DataValueField = "name"; this.ddl_tableid.DataBind(); this.ddl_tableid.Items.Insert(0, new ListItem("--请选择--", "")); } this.lb_showsql.Text = " "; }
//得到update语句 protected void btn_update_Click(object sender, EventArgs e) { string sqlWhere = ""; if (this.ddl_tableid.SelectedValue == "") { Response.Write("<script>alert('对不起,请输入表名!!!');</script>"); this.lb_showsql.Text = " "; return; } if (this.txt_con.Value != "") { strCon = this.txt_con.Value; } else { strCon = "server=(local);database=" + this.ddl_database.SelectedValue + ";uid=sa;pwd=sa!@#$%^*(sa;"; } DAO_sql db = new DAO_sql(strCon); //得到字段名 string sqlSelect = " select name from syscolumns where id=object_id('" + this.ddl_tableid.SelectedValue + "') "; if (this.txt_sql.Value != "") { string[] arrySql = txt_sql.Value.Split('#'); for (int i = 0; i < arrySql.Length; i++) { if (i == arrySql.Length - 1) { sqlWhere += "'" + arrySql[i] + "'"; } else { sqlWhere += "'" + arrySql[i] + "',"; } } } if (this.txt_where.Value != "") { string[] arrySql = txt_where.Value.Split('#'); for (int i = 0; i < arrySql.Length; i++) { if (i == 0 && sqlWhere != "") { sqlWhere += ",'" + arrySql[i] + "',"; } else if (i == arrySql.Length - 1) { sqlWhere += "'" + arrySql[i] + "'"; } else { sqlWhere += "'" + arrySql[i] + "',"; } } } if (this.txt_sql.Value != "" || this.txt_where.Value != "") { sqlSelect += " and name not in(" + sqlWhere + ") "; } DataTable dt = new DataTable(); try { dt = db.GetDataTable(sqlSelect); } catch (Exception ex) { Response.Write("<script>alert('对不起,连接串输入有错误,系统将重置!');</script>"); this.txt_con.Value = ""; if (this.Session["constr"] != null) { this.Session["constr"] = null; } Response.Write("<script>window.location='" + this.Request.Url.ToString() + "';</script>"); } //开始构造update语句 string strUpdate = "sqlUpdate=\" update " + this.ddl_tableid.SelectedValue + " set "; if (dt.Rows.Count > 0) { int j = 1; for (int i = 0; i < dt.Rows.Count; i++) { if (i == 0) { strUpdate += dt.Rows[0]["name"].ToString() + "=@" + dt.Rows[0]["name"].ToString(); } else { strUpdate += "," + dt.Rows[i]["name"].ToString() + "=@" + dt.Rows[i]["name"].ToString(); } if (strUpdate.Length > (100 * j)) { strUpdate += "\";</br>sqlUpdate+=\""; j++; } } } if (this.txt_where.Value != "") { strUpdate += " where 1=1"; string[] arryWhere = this.txt_where.Value.Split('#'); for (int n = 0; n < arryWhere.Length; n++) { strUpdate += " and " + arryWhere[n] + "=@" + arryWhere[n] + ""; } } //前台显示出来 this.lb_showsql.Text = strUpdate + "\";"; }
//得到sql的执行速度 protected void btn_speed_Click(object sender, EventArgs e) { if (this.txt_sql.Value == "") { Response.Write("<script>alert('对不起,请输入sql语句!!!');</script>"); return; } return; string sqlActive = " Declare @d Datetime Set @d=GetDate() " + this.txt_sql.Value + " Select [datetime]=DateDiff(ms,@d,GetDate()) "; if (this.txt_con.Value != "") { strCon = this.txt_con.Value; } else { strCon = "server=(local);database=" + this.ddl_database.SelectedValue + ";uid=sa;pwd=sa!@#$%^*(sa;"; } DAO_sql db = new DAO_sql(strCon); DataTable dt = db.GetDataTable(sqlActive); if (dt.Rows.Count > 0) { this.lb_showsql.Text = "SQL的执行速度为:" + dt.Rows[0]["datetime"].ToString(); } }
protected void btn_insert2_Click(object sender, EventArgs e) { if (this.ddl_tableid.SelectedValue == "") { Response.Write("<script>alert('对不起,请输入表名!!!');</script>"); this.lb_showsql.Text = " "; return; } if (this.txt_con.Value != "") { strCon = this.txt_con.Value; } else { strCon = "server=(local);database=" + this.ddl_database.SelectedValue + ";uid=sa;pwd=sa!@#$%^*(sa;"; } DAO_sql db = new DAO_sql(strCon); //得到字段 string sqlSelect = " select name from syscolumns where id=object_id('" + this.ddl_tableid.SelectedValue + "') "; if (this.txt_sql.Value != "") { string[] arrySql = txt_sql.Value.Split('#'); string sqlWhere = ""; //构造where条件句 for (int i = 0; i < arrySql.Length; i++) { if (i == arrySql.Length - 1) { sqlWhere += "'" + arrySql[i] + "'"; } else { sqlWhere += "'" + arrySql[i] + "',"; } } sqlSelect += " and name not in(" + sqlWhere + ") "; } DataTable dt = new DataTable(); try { dt = db.GetDataTable(sqlSelect); } catch (Exception ex) { Response.Write("<script>alert('对不起,连接串输入有错误,系统将重置!');</script>"); this.txt_con.Value = ""; if (this.Session["constr"] != null) { this.Session["constr"] = null; } Response.Write("<script>window.location='" + this.Request.Url.ToString() + "';</script>"); } //开始构造insert string strInsert = " insert into " + this.ddl_tableid.SelectedValue + "( "; int j = 1; if (dt.Rows.Count > 0) { for (int i = 0; i < dt.Rows.Count; i++) { if (i == 0) { strInsert += dt.Rows[0]["name"].ToString(); } else { strInsert += "," + dt.Rows[i]["name"].ToString(); } //对sql进行格式化 if (strInsert.Length > (100 * j)) { strInsert += "</br>"; j++; } } strInsert += ") values ( "; string flowid = ""; for (int i = 0; i < dt.Rows.Count; i++) { flowid = dt.Rows[i]["name"].ToString(); if (flowid.IndexOf("hyc") != -1) { flowid = flowid.Substring(4); } if (flowid.IndexOf("hy_") != -1) { flowid = flowid.Substring(3); } if (i == 0) { strInsert += "'\"+this." + flowid + ".Value+\"'"; } else { strInsert += ",'\"+this." + flowid + ".Value+\"'"; } //对sql进行格式化 if (strInsert.Length > (100 * j)) { strInsert += "</br>"; j++; } } } strInsert += ")"; //显示出来 this.lb_showsql.Text = strInsert; }
private string getSqlparam(string strType) { if (this.ddl_tableid.SelectedValue == "") { Response.Write("<script>alert('对不起,请输入表名!!!');</script>"); this.lb_showsql.Text = " "; return ""; } if (this.txt_con.Value != "") { strCon = this.txt_con.Value; } else { strCon = "server=(local);database=" + this.ddl_database.SelectedValue + ";uid=sa;pwd=sa!@#$%^*(sa;"; } DAO_sql db = new DAO_sql(strCon); //得到字段和字段类型 string sqlSelect = " select a.name as [name],b.name as type from syscolumns a,systypes b where a.id=object_id('" + this.ddl_tableid.SelectedValue + "') and a.xtype=b.xtype"; if (this.txt_sql.Value != "") { //过滤条件 string[] arrySql = txt_sql.Value.Split('#'); string sqlWhere = ""; for (int i = 0; i < arrySql.Length; i++) { if (i == arrySql.Length - 1) { sqlWhere += "'" + arrySql[i] + "'"; } else { sqlWhere += "'" + arrySql[i] + "',"; } } sqlSelect += " and a.name not in(" + sqlWhere + ") "; } DataTable dt = new DataTable(); try { dt = db.GetDataTable(sqlSelect); } catch (Exception ex) { Response.Write("<script>alert('对不起,连接串输入有错误,系统将重置!');</script>"); this.txt_con.Value = ""; if (this.Session["constr"] != null) { this.Session["constr"] = null; } Response.Write("<script>window.location='" + this.Request.Url.ToString() + "';</script>"); } //开始构造 string strInsert = " SqlParameter[] sqlparam = {"; if (dt.Rows.Count > 0) { for (int i = 0; i < dt.Rows.Count; i++) { if (i == dt.Rows.Count - 1) { if (strType == "1") { strInsert += " <br/> db.MakeInParam(\"@" + dt.Rows[i]["name"].ToString() + "\",SqlDbType." + getDateType(dt.Rows[i]["type"].ToString()) + "," + dt.Rows[i]["name"].ToString() + ")"; } else { strInsert += "\r\n db.MakeInParam(\"@" + dt.Rows[i]["name"].ToString() + "\",SqlDbType." + getDateType(dt.Rows[i]["type"].ToString()) + "," + dt.Rows[i]["name"].ToString() + ")"; } } else { if (strType == "1") { strInsert += "db.MakeInParam(\"@" + dt.Rows[i]["name"].ToString() + "\",SqlDbType." + getDateType(dt.Rows[i]["type"].ToString()) + "," + dt.Rows[i]["name"].ToString() + "),<br/>"; } else { strInsert += "db.MakeInParam(\"@" + dt.Rows[i]["name"].ToString() + "\",SqlDbType." + getDateType(dt.Rows[i]["type"].ToString()) + "," + dt.Rows[i]["name"].ToString() + "),\r\n"; } } } strInsert += "};"; } return strInsert; }
private string getInsert(string strType) { if (this.ddl_tableid.SelectedValue == "") { Response.Write("<script>alert('对不起,请输入表名!!!');</script>"); this.lb_showsql.Text = " "; return ""; } if (this.txt_con.Value != "") { strCon = this.txt_con.Value; } else { strCon = "server=(local);database=" + this.ddl_database.SelectedValue + ";uid=sa;pwd=sa!@#$%^*(sa;"; } DAO_sql db = new DAO_sql(strCon); //得到字段 string sqlSelect = " select name from syscolumns where id=object_id('" + this.ddl_tableid.SelectedValue + "') "; if (this.txt_sql.Value != "") { string[] arrySql = txt_sql.Value.Split('#'); string sqlWhere = ""; //构造where条件句 for (int i = 0; i < arrySql.Length; i++) { if (i == arrySql.Length - 1) { sqlWhere += "'" + arrySql[i] + "'"; } else { sqlWhere += "'" + arrySql[i] + "',"; } } sqlSelect += " and name not in(" + sqlWhere + ") "; } DataTable dt = new DataTable(); try { dt = db.GetDataTable(sqlSelect); } catch (Exception ex) { Response.Write("<script>alert('对不起,连接串输入有错误,系统将重置!');</script>"); this.txt_con.Value = ""; if (this.Session["constr"] != null) { this.Session["constr"] = null; } Response.Write("<script>window.location='" + this.Request.Url.ToString() + "';</script>"); } //开始构造insert string strInsert = " sqlInsert =\" insert into " + this.ddl_tableid.SelectedValue + "( "; int j = 1; if (dt.Rows.Count > 0) { for (int i = 0; i < dt.Rows.Count; i++) { if (i == 0) { strInsert += dt.Rows[0]["name"].ToString(); } else { strInsert += "," + dt.Rows[i]["name"].ToString(); } //对sql进行格式化 if (strInsert.Length > (100 * j)) { if (strType == "1") { strInsert += "\";</br> sqlInsert +=\" "; } else { strInsert += "\";\r\nsqlInsert +=\" "; } j++; } } strInsert += ") values ( "; for (int i = 0; i < dt.Rows.Count; i++) { if (i == 0) { strInsert += "@" + dt.Rows[0]["name"].ToString(); } else { strInsert += ",@" + dt.Rows[i]["name"].ToString(); } //对sql进行格式化 if (strInsert.Length > (100 * j)) { if (strType == "1") { strInsert += "\";</br> sqlInsert +=\" "; } else { strInsert += "\";\r\nsqlInsert +=\" "; } j++; } } } strInsert += ") \"; "; return strInsert; }