示例#1
0
    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("--请选择--", ""));

        }
    }
示例#2
0
 //得到赋值语句
 protected void btn_voluation_Click(object sender, EventArgs e)
 {
     if (this.ddl_tableid.SelectedValue == "")
     {
         Response.Write("<script>alert('对不起,请输入表名!!!');</script>");
         this.lb_showsql.Text = "&nbsp;";
         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;
 }
示例#3
0
 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 = "&nbsp;";
 }
示例#4
0
 //得到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 = "&nbsp;";
         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 + "\";";
 }
示例#5
0
 //得到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();
     }
 }
示例#6
0
 protected void btn_insert2_Click(object sender, EventArgs e)
 {
     if (this.ddl_tableid.SelectedValue == "")
     {
         Response.Write("<script>alert('对不起,请输入表名!!!');</script>");
         this.lb_showsql.Text = "&nbsp;";
         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;
 }
示例#7
0
    private string getSqlparam(string strType)
    {
        if (this.ddl_tableid.SelectedValue == "")
        {
            Response.Write("<script>alert('对不起,请输入表名!!!');</script>");
            this.lb_showsql.Text = "&nbsp;";
            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;
    }
示例#8
0
    private string getInsert(string strType)
    {
        if (this.ddl_tableid.SelectedValue == "")
        {
            Response.Write("<script>alert('对不起,请输入表名!!!');</script>");
            this.lb_showsql.Text = "&nbsp;";
            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;
    }