C# SQLHelper.ExecSql方法代码示例

 protected void btnRun_Click(object sender, EventArgs e)
 {
     string sql = tbxSql.Text.Trim().ToUpper();
     SQLHelper db = new SQLHelper();
     db.sql = sql;
     if (sql.Contains("SELECT "))
     {
         DataTable dt = db.Get_DataTable();
         gv.DataSource = dt;
         gv.DataBind();
         lblMsg.Text = "执行完成，查询结果如下：";
     }
     else
     {
         string result = db.ExecSql();
         if (result == "1")
         {
             lblMsg.Text = "执行完成";
         }
         else
         {
             lblMsg.Text = "执行失败，" + result;
         }
     }
 }

 public static void del(string id)
 {
     SQLHelper.DeleteEntity<DXLb>(id);
     SQLHelper db = new SQLHelper();
     db.sql = "UPDATE lb SET isDeleted=1 WHERE id="+id;
     db.ExecSql();
 }

 /// <summary>
 /// 添加栏目
 /// </summary>
 /// <param name="lbname">栏目名称</param>
 /// <param name="parentid">父栏目ID</param>
 /// <returns>int,1=成功，0=已存在</returns>
 public static string AddLb(string lbname, string parentid)
 {
     string maxlbid = MaxLbid();
     string sql = "INSERT INTO " + com.tablePrefix + "lb (lbid,lbname,parentid,OrderId) VALUES(" + maxlbid + ",'" + lbname + "'," + parentid + "," + maxlbid + ")";
     SQLHelper db = new SQLHelper();
     db.sql = sql;
     return db.ExecSql();
 }

        protected void btnAdd_Click(object sender, EventArgs e)
        {
            string lbid = hLbid.Value;
            string id = hID.Value;
            string title = pg.GetSafeString(txtTitle.Text);
            string cityid = ddlCity.SelectedValue;
            string newsBody = tbxBody.Text;
            string tel = tbxDesc.Text;
            //if (tel.Length < 8)
            //{
            //    alert.ShowAndBack(Page, "联系方式不能小于8位数字");
            //    return;
            //}
            string sql = "";
            if (cityid == "0" || cityid.Length==0)
            {
                alert.ShowAndBack(Page, "请选择省市");
                return;
            }
            if (id.Length > 0)
            {

                //修改
                sql = "UPDATE News SET Title='" + title + "',cityid='" + cityid + "',Description='" + tel + "',NewsBody='" + newsBody + "',EditTime=getdate() WHERE NewsID=" + id;
                SQLHelper db = new SQLHelper();
                db.sql = sql;
                if (db.ExecSql() == "1")
                {
                    alert.showAndGo("修改成功", "NetworkManage.aspx");
                }
                else
                {
                    //alert.ShowAndBack(Page, "修改失败");
                    Response.Write(sql);
                    Response.End();
                }
            }
            else
            {
                //添加
                string newsid = clsNews.MaxNewsid();
                lbid = "24";
                sql = "INSERT INTO News(NewsID,lbid,Title,cityid,Description,NewsBody,Creator) VALUES(" + newsid + "," + lbid + ",'" + title + "','" + cityid + "','" + tel + "','" + newsBody + "','" + Session["AdminName"].ToString() + "')";
                SQLHelper db = new SQLHelper();
                db.sql = sql;
                string result = db.ExecSql();
                if (result == "1")
                {
                    alert.showAndGo("添加成功", "NetworkManage.aspx");
                }
                else
                {
                    Response.Write(result+",sql="+sql);
                    Response.End();
                    //alert.ShowAndBack(Page, "添加失败" + result);
                }
            }
        }

 public static bool AdminDel(string id)
 {
     string sql = "DELETE Admin WHERE id=" + id;
     try
     {
         SQLHelper db = new SQLHelper();
         db.sql = sql;
         db.ExecSql();
         return true;
     }
     catch
     {
         return false;
     }
 }

        protected void Button1_Click(object sender, EventArgs e)
        {
            string pwd = txtPwd.Text;
            string newPwd = txtNewPwd.Text;
            string newPwd2 = txtNewPwd2.Text;
            if (pwd.Length == 0)
            {
                alert.Show(Page, "请填写原密码");
                return;
            }
            if (newPwd.Length == 0)
            {
                alert.Show(Page, "请填写新密码");
                return;
            }
            if (newPwd != newPwd2)
            {
                alert.Show(Page, "新密码与确认新密码不一致");
                return;
            }

            string memberName = Session["AdminName"].ToString();
            SQLHelper db = new SQLHelper();
            db.sql = "SELECT pwd FROM " + com.tablePrefix + "admin WHERE username='" + memberName + "'";
            DataTable dt = db.Get_DataTable();

            pwd = com.MD5(pwd, 1);
            newPwd = com.MD5(newPwd, 1);

            if (dt.Rows.Count > 0)
            {
                if (pwd != dt.Rows[0]["pwd"].ToString())
                {
                    alert.Show(Page, "原密码错误");
                    return;
                }
                else
                {
                    db.sql = "UPDATE admin SET Pwd='" + newPwd + "' WHERE username='" + memberName + "'";
                    db.ExecSql();
                    alert.Show(Page,"修改成功");
                }
            }
            else
            {
                Response.Redirect("Login.aspx");
            }
        }

 protected void Page_Load(object sender, EventArgs e)
 {
     com.adminLogin();
     if (!IsPostBack)
     {
         string act = pg.request("act");
         if (act == "del")
         {
             string id = pg.request("id");
             SQLHelper db = new SQLHelper();
             db.sql = "DELETE FROM " + com.tablePrefix + "jiameng WHERE id=" + id;
             db.ExecSql();
             alert.showAndGo("删除成功", "jiameng.aspx");
             return;
         }
         bindGv();
     }
 }

 public static string adminEdit(string id, string pwd, string role)
 {
     string sql = "";
     if (pwd.Length > 0)
         sql = "UPDATE Admin SET pwd='" + pwd + "',role='" + role + "' WHERE id=" + id;
     else
         sql = "UPDATE Admin SET role='" + role + "' WHERE id=" + id;
     try
     {
         SQLHelper db = new SQLHelper();
         db.sql = sql;
         db.ExecSql();
         return "1";
     }
     catch (Exception ex)
     {
         return ex.Message;// +"SQL:" + sql;
     }
 }

        protected void Page_Load(object sender, EventArgs e)
        {
            com.adminLogin();

            if (!IsPostBack)
            {
                bindProvince();
                string id = pg.request("id");
                string ProvinceId = pg.request("ProvinceId");
                ddlProvince.SelectedValue = ProvinceId;
                string lbid = "24";
                string act = pg.request("act");
                if (act == "del")
                {
                    SQLHelper db = new SQLHelper();
                    db.sql = "DELETE FROM " + com.tablePrefix + "News WHERE NewsID=" + id;
                    db.ExecSql();
                    alert.showAndGo("删除成功", "NetworkManage.aspx?ProvinceId=" + ProvinceId);

                    return;
                }
                bindGv();
            }
        }

 protected void GV_RowDeleting(object sender, GridViewDeleteEventArgs e)
 {
     string lbid = GV.DataKeys[e.RowIndex].Value.ToString();
     SQLHelper db = new SQLHelper();
     db.sql = "DELETE Province WHERE id=" + lbid;
     db.ExecSql();
     bindGv();
     alert.Show(Page, "删除成功");
     return;
 }

        protected void GV_RowCommand(object sender, GridViewCommandEventArgs e)
        {
            System.Web.UI.WebControls.Button btn = e.CommandSource as System.Web.UI.WebControls.Button;
            if (btn == null) return;
            int index = ((System.Web.UI.WebControls.GridViewRow)btn.Parent.Parent).RowIndex;

            DataKey key = this.GV.DataKeys[index];
            string lbid = key.Value.ToString();

            GridViewRow gvr = GV.Rows[index];
            if (e.CommandName == "Save")
            {
                //查找 title控件
                TextBox txtTitle = null;
                try
                {
                    txtTitle = this.GV.Rows[index].Cells[1].Controls[0] as TextBox;
                }
                catch
                {
                    alert.Show(Page, "未找到省份名称文本框控件");
                    return;
                }

                TextBox tbxJianCheng = null;//JianCheng
                try
                {
                    tbxJianCheng = this.GV.Rows[index].Cells[2].Controls[0] as TextBox;
                }
                catch
                {
                    alert.Show(Page, "未找到排序文本框控件");
                    return;
                }

                TextBox tbxOrderId = null;
                try
                {
                    tbxOrderId = this.GV.Rows[index].Cells[3].Controls[0] as TextBox;
                }
                catch
                {
                    alert.Show(Page, "未找到排序文本框控件");
                    return;
                }

                string title = pg.GetSafeString(txtTitle.Text.Trim());
                string orderid = pg.GetSafeString(tbxOrderId.Text.Trim());
                string jianCheng = tbxJianCheng.Text.Trim();
                if (title == "")
                {
                    alert.ShowAndBack(Page, "请填写省份名称");
                    return;
                }
                if (jianCheng == "")
                {
                    alert.ShowAndBack(Page, "请填写简称");
                    return;
                }
                try
                {
                    int o = Int32.Parse(orderid);
                }
                catch (Exception)
                {
                    orderid = "0";
                }
                SQLHelper db = new SQLHelper();
                if (lbid.Length > 0)
                {
                    //更新
                    string sql = "UPDATE Province SET name='" + title + "',jianCheng='" + jianCheng + "',OrderId=" + orderid;
                    sql += " WHERE id=" + lbid;
                    db.sql = sql;
                    if (db.ExecSql() != "1")
                    {
                        alert.Show(Page, "保存失败");
                    }
                    else
                    {
                        GV.EditIndex = -1;
                        bindGv();
                    }
                }
                else
                {
                    //添加
                    db.sql = "INSERT INTO Province(name,jianCheng,OrderId) VALUES('" + title + "','" + jianCheng + "'," + orderid + ")";
                    if (db.ExecSql() != "1")
                    {
                        alert.Show(Page, "添加失败");
                    }
                    else
                    {
                        GV.EditIndex = -1;
                        bindGv();
                    }
                }
            }
        }

        protected void GV_RowCommand(object sender, GridViewCommandEventArgs e)
        {
            lbid = hlbid.Value;
            System.Web.UI.WebControls.Button btn = e.CommandSource as System.Web.UI.WebControls.Button;
            if (btn == null) return;
            int index = ((System.Web.UI.WebControls.GridViewRow)btn.Parent.Parent).RowIndex;

            DataKey key = this.GV.DataKeys[index];
            string NewsID = key.Value.ToString();

            GridViewRow gvr = GV.Rows[index];
            if (e.CommandName == "upFile")
            {
                //寻找上传控件
                FileUpload fu = (FileUpload)gvr.FindControl("FileUpload1");//原图
                string pic = "";
                //上传图片
                pic = UpFile(fu);
                if (pic != "")
                {
                    SQLHelper db = new SQLHelper();
                    //删除原图
                    db.sql = "SELECT pic FROM " + com.tablePrefix + "News WHERE NewsID=" + NewsID;
                    string pic0 = db.Get_DataTable().Rows[0][0].ToString();
                    if (pic0.Length > 0)
                        FileSys.delFile(pic0);

                    //更新数据库
                    db.sql = "UPDATE News SET pic='" + pic + "' WHERE NewsID=" + NewsID;
                    db.ExecSql();

                    bindGv();
                    alert.Show(Page, "图片更新成功");

                }

            }
            if (e.CommandName == "Save")
            {
                //查找 title控件
                TextBox txtTitle = null;
                try
                {
                    txtTitle = this.GV.Rows[index].Cells[1].Controls[0] as TextBox;
                }
                catch
                {
                    alert.Show(Page, "未找到图标名称控件");
                    return;
                }

                TextBox txtDesc = null;
                try
                {
                    txtDesc = this.GV.Rows[index].Cells[2].Controls[0] as TextBox;
                }
                catch
                {
                    alert.Show(Page, "未找到图标描述控件");
                    return;
                }

                //FileUpload fu_small = gvr.Cells[4].FindControl("FileUpload2") as FileUpload;//缩略图
                FileUpload fu = gvr.Cells[3].FindControl("FileUpload1") as FileUpload;
                string title = pg.GetSafeString(txtTitle.Text.Trim());
                string desc = pg.GetSafeString(txtDesc.Text.Trim());
                //if (title == "")
                //{
                //    alert.ShowAndBack(Page, "请填写图标名称");
                //    return;
                //}
                if (desc == "")
                {
                    alert.ShowAndBack(Page, "请填写图标描述");
                    return;
                }

                string pic = UpFile(fu);

                SQLHelper db = new SQLHelper();
                if (NewsID.Length > 0)
                {
                    //取出旧图
                    string oldPic = "";
                    db.sql = "SELECT pic FROM " + com.tablePrefix + "News WHERE NewsID=" + NewsID;
                    DataTable dt = db.Get_DataTable();
                    if (dt.Rows.Count > 0)
                    {
                        oldPic = dt.Rows[0]["pic"].ToString();
                    }
                    //更新
                    string sql = "UPDATE News SET title='" + title + "',Description='" + desc + "'";
                    if (pic.Length > 0)
                    {
                        sql += " ,pic='" + pic + "'";
                        FileSys.delFile(oldPic);
                    }

                    sql += " WHERE NewsID=" + NewsID;
                    db.sql = sql;
//.........这里部分代码省略.........

 protected void GV_RowDeleting(object sender, GridViewDeleteEventArgs e)
 {
     string newsID = GV.DataKeys[e.RowIndex].Value.ToString();
     SQLHelper db = new SQLHelper();
     //删除图片
     db.sql = "SELECT pic FROM " + com.tablePrefix + "News WHERE NewsID=" + newsID;
     DataTable dt = db.Get_DataTable();
     if (dt.Rows.Count > 0)
     {
         string pic = dt.Rows[0]["pic"].ToString();
         FileSys.delFile(pic);
     }
     db.sql = "DELETE News WHERE NewsID=" + newsID;
     db.ExecSql();
     bindGv();
     alert.Show(Page, "删除成功");
     return;
 }

 protected void Page_Load(object sender, EventArgs e)
 {
     string newsid = pg.request("newsid"), sizeid="";
     string guid = pg.request("guid");
     string type = pg.request("type");
     string fileurl = null;
     string folder = "/UpFile/wallpaper/";
     string uploadsFolder = Server.MapPath(folder);
     HttpPostedFile httpfile = Request.Files["Filedata"];
     string filename = DateTime.Now.ToString("yyyyMMddHHmmssfff");
     if (httpfile != null)
     {
         //得到尺寸
         System.Drawing.Image original_image = null;//原图
         // Retrieve the uploaded image
         original_image = System.Drawing.Image.FromStream(httpfile.InputStream);
         // Calculate the new width and height
         int width = original_image.Width;
         int height = original_image.Height;
         bool flag = false;
         //验证分辨率
         SQLHelper db = new SQLHelper();
         db.sql = "SELECT id,size FROM " + com.tablePrefix + "wallpaperSize";
         DataTable dt = db.Get_DataTable();
         foreach (DataRow dr in dt.Rows)
         {
             if (!flag)
             {
                 string[] wh = dr["Size"].ToString().Split('×');
                 if (wh.Length > 0)
                 {
                     if (width.ToString() == wh[0] && height.ToString() == wh[1])
                     {
                         flag = true;
                         sizeid = dr["id"].ToString();
                     }
                 }
             }
         }
         if (!flag)
         {
             Response.Write("分辨率不对");
         }
         else
         {
             //保存到磁盘
             httpfile.SaveAs(string.Format("{0}\\{1}.jpg", uploadsFolder, filename));
             fileurl = folder + filename + ".jpg";
             //将图片名保存到数据库 wallpaper.pic
             if (newsid == "")
             {
                 db.sql = "INSERT INTO wallpaper(sizeid,pic,NewsGUID) VALUES(" + sizeid + ",'" + fileurl + "','"+guid+"')";
             }
             else
             {
                 db.sql = "INSERT INTO wallpaper(sizeid,pic,NewsGUID,NewsID) VALUES(" + sizeid + ",'" + fileurl + "','" + guid + "'," + newsid + ")";
             }
             db.ExecSql();
             Response.Write(fileurl);
         }
     }
     Response.End();
 }

        protected void GV_RowCommand(object sender, GridViewCommandEventArgs e)
        {
            System.Web.UI.WebControls.Button btn = e.CommandSource as System.Web.UI.WebControls.Button;
            if (btn == null) return;
            int index = ((System.Web.UI.WebControls.GridViewRow)btn.Parent.Parent).RowIndex;

            DataKey key = this.GV.DataKeys[index];
            string id = key.Value.ToString();

            GridViewRow gvr = GV.Rows[index];
            if (e.CommandName == "Save")
            {
                //查找 title控件
                TextBox txtTitle = null;
                try
                {
                    txtTitle = this.GV.Rows[index].Cells[1].Controls[0] as TextBox;
                }
                catch
                {
                    alert.Show(Page, "未找到颜色名称文本框");
                    return;
                }

                TextBox txtColorValue = null;
                try
                {
                    txtColorValue = this.GV.Rows[index].Cells[2].Controls[0] as TextBox;
                }
                catch
                {
                    alert.Show(Page, "未找到色值文本框");
                    return;
                }
                //排序

                TextBox txtOrderId = null;
                try
                {
                    txtOrderId = this.GV.Rows[index].Cells[3].Controls[0] as TextBox;
                }
                catch
                {
                    alert.Show(Page, "未找到排序文本框");
                    return;
                }

                string ColorName = pg.GetSafeString(txtTitle.Text.Trim());
                string colorValue = pg.GetSafeString(txtColorValue.Text.Trim());
                string orderid = pg.GetSafeString(txtOrderId.Text.Trim());
                if (ColorName == "")
                {
                    alert.ShowAndBack(Page, "请填写类别名称");
                    return;
                }

                SQLHelper db = new SQLHelper();
                if (id.Length > 0)
                {

                    //更新
                    string sql = "UPDATE Color SET ColorName='" + ColorName + "',ColorValue='" + colorValue + "',OrderId="+orderid;

                    sql += " WHERE id=" + id;
                    db.sql = sql;
                    if (db.ExecSql() != "1")
                    {
                        alert.Show(Page, "保存失败");
                    }
                    else
                    {
                        GV.EditIndex = -1;
                        bindGv();
                    }
                }
                else
                {
                    //添加
                    db.sql = "INSERT INTO Color(ColorName,ColorValue,OrderId) VALUES('" + ColorName + "','" + colorValue + "'," + orderid + ")";
                    if (db.ExecSql() != "1")
                    {
                        alert.Show(Page, "添加失败");
                    }
                    else
                    {
                        GV.EditIndex = -1;
                        bindGv();
                    }
                }
            }
        }