DbHelper通用数据库类及增删改 使用示例

DbHelper db = new DbHelper();
    protected void Page_Load(object sender, EventArgs e)
    {
 
    }
 
    //新增数据
    protected void Button1_Click(object sender, EventArgs e)
    {
        List<FieldTypeValue> ftvlist = new List<FieldTypeValue>();
        ftvlist.Add(new FieldTypeValue("[D_Name]", "测试用户" + DateTime.Now.ToString("yyyyMMddhhmmss")));
        ftvlist.Add(new FieldTypeValue("[D_Password]", "测试密码" + DateTime.Now.ToString("yyyyMMddhhmmss")));
        ftvlist.Add(new FieldTypeValue("[D_Else]", "测试备注" + DateTime.Now.ToString("yyyyMMddhhmmss")));
        string sql = BuilderSql.createInsertSql("TestTable", ftvlist);
        int opFlag = db.ExecuteNonQuery(db.GetSqlStringCommand(sql));
        if (opFlag > 0) { JsHelper.Alert("新增成功!", Page); } else { JsHelper.Alert("新增失败!", Page); };
    }
 
    //修改数据
    protected void Button2_Click(object sender, EventArgs e)
    {
        List<FieldTypeValue> ftvlist = new List<FieldTypeValue>();
        ftvlist.Add(new FieldTypeValue("[D_Name]", "这是个错误dsadsadasd"));
        ftvlist.Add(new FieldTypeValue("[D_Password]", "aaa这是个错误，我还没有处理"));
        ftvlist.Add(new FieldTypeValue("[D_Else]", "aaa这是个错误，我还没有处理"));
        string sql = BuilderSql.createUpdateSql("TestTable", ftvlist, "D_ID", "1");
        int opFlag = db.ExecuteNonQuery(db.GetSqlStringCommand(sql));
        if (opFlag > 0) { JsHelper.Alert("修改成功!", Page); } else { JsHelper.Alert("修改失败!", Page); };
    }
 
    //删除数据
    protected void Button3_Click(object sender, EventArgs e)
    {
        string sql = BuilderSql.createDeleteSql("[TestTable]", "[D_Id]", "1");
        int opFlag = db.ExecuteNonQuery(db.GetSqlStringCommand(sql));
        if (opFlag > 0) { JsHelper.Alert("删除成功!", Page); } else { JsHelper.Alert("删除失败!", Page); };
    }
 
    //事务提交
    protected void Button4_Click(object sender, EventArgs e)
    {
        using (Trans t = new Trans())
        {
            try
            {
                db.ExecuteNonQuery(db.GetSqlStringCommand("insert into TestTable(D_Name,D_Password,D_Else) values('aaaaa1','bbbbbb1','cccccc1')"), t);
                db.ExecuteNonQuery(db.GetSqlStringCommand("insert into TestTable(D_Name,D_Password,D_Else) values('aaaaa2','bbbbbb2','cccccc2')"), t);
                db.ExecuteNonQuery(db.GetSqlStringCommand("insert into TestTable(D_Name,D_Password,D_Else) values('aaaaa3','bbbbbb3','cccccc3')"), t);
                db.ExecuteNonQuery(db.GetSqlStringCommand("insert into TestTable(D_Name,D_Password,D_Else) values('aaaaa4','bbbbbb4','cccccc4')"), t);
                db.ExecuteNonQuery(db.GetSqlStringCommand("insert into TestTable(D_Name,D_Password,D_Else) values('aaaaa5','bbbbbb5','cccccc5')"), t);
                t.Commit();
                JsHelper.Alert("事务提交成功!", Page);
            }
            catch
            {
                t.RollBack();
                JsHelper.Alert("事务提交失败!", Page);
            }
        }
    }
 
 
 
 
 
 
    //分页控件分页
 
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindGridViewData();
        }
 
    }
 
    private void BindGridViewData()
    {
        string strCondition = "";
        MSCL.PageHelper wp = new MSCL.PageHelper();
        wp.TableName = "TestTable";
        wp.KeyField = "D_Id";
        wp.SortName = "D_Id";
        wp.Condition = strCondition;
        wp.CurrentPageIndex = AspNetPager1.CurrentPageIndex;
        wp.PageSize = AspNetPager1.PageSize;//=PageSize;
 
        DataTable dt = wp.GetDataTableMyPage();
        GridView1.DataSource = dt;
        GridView1.DataBind();
 
        AspNetPager1.RecordCount = wp.RecordCount;
        AspNetPager1.CustomInfoHTML = " 共<b>" + wp.RecordCount.ToString() + "</b>条记录/";
        AspNetPager1.CustomInfoHTML += " <b>" + wp.PageCount.ToString() + "</b>页";
        AspNetPager1.CustomInfoHTML += " 当前第<font color=\"red\"><b>" + AspNetPager1.CurrentPageIndex.ToString() + "</b></font>页";
    }
 
    protected void AspNetPager1_PageChanged(object sender, EventArgs e)
    {
        BindGridViewData();
    }
 
 
 
 
 
 
    //字符串分页
 
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            bindData();
        }
    }
 
    protected void bindData()
    {
        int count = 0; //记录总数
        int pageSize = 10; //每页显示记录数
        int pageIndex = (Request["PageIndex"] == null) ? 1 : Convert.ToInt32(Request["PageIndex"]);
        DataSet ds = MSCL.PageHelper.PageList("TestTable", pageSize, pageIndex, "D_ID", true, "", out count);
        GridView1.DataSource = ds;
        GridView1.DataBind();
 
        ltPage.Text = MSCL.PageHelper.ToSplitPageHtml(count, pageSize, 5, 1, "", true);
    }
以上我们好像没有指定数据库连接字符串,大家如果看下DbHelper的代码,就知道要使用它必须在config中配置两个参数,如下:
    <appSettings>
        <add key="DbHelperProvider" value="System.Data.SqlClient"/>
        <add key="DbHelperConnectionString" value="Data Source=(local);Initial Catalog=DbHelperTest;Persist Security Info=True;User ID=sa;Password=sa"/>
    </appSettings>

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
 
namespace FairHR.Util
{
    #region 数据表字段类
    /// <summary>
    /// 数据表字段类
    /// </summary>
    public class FieldTypeValue
    {
        /// <summary>
        /// 字段容器
        /// </summary>
        /// <param name="fieldName">字段名</param>
        /// <param name="fieldValue">字段值</param>
        /// <param name="isNum"></param>
        public FieldTypeValue(string fieldName, string fieldValue, bool isNum)
        {
            this.fieldName = fieldName;
            this.fieldValue = fieldValue;
            this.isNum = isNum;
        }
 
        /// <summary>
        /// 字段容器
        /// </summary>
        /// <param name="fieldName">字段名</param>
        /// <param name="fieldValue">字段值</param>
        public FieldTypeValue(string fieldName, string fieldValue)
        {
            this.fieldName = fieldName;
            this.fieldValue = fieldValue;
        }
 
        private string fieldName;
        /// <summary>
        /// 字段名
        /// </summary>
        public string FieldName
        {
            get { return fieldName; }
            set { fieldName = value; }
        }
 
        private bool isNum = false;
        /// <summary>
        /// 是否数字
        /// </summary>
        public bool IsNum
        {
            get { return isNum; }
            set { isNum = value; }
        }
 
        private string fieldValue;
        /// <summary>
        /// 字段值
        /// </summary>
        public string FieldValue
        {
            get { return fieldValue; }
            set { fieldValue = value; }
        }
    }
    #endregion
 
    #region SQL语句的构造类
    /// <summary>
    /// SQL语句的构造类
    /// </summary>
    public class BuilderSql
    {
 
 
        /// <summary>
        /// 构造新增Insert语句
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="ftvlist">list</param>
        /// <returns></returns>
        public static string createInsertSql(string tableName, List<FieldTypeValue> ftvlist)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" insert into ");
            sb.Append(tableName);
            sb.Append("(");
            for (int i = 0; i < ftvlist.Count; i++)
            {
                FieldTypeValue ftv = (FieldTypeValue)ftvlist[i];
                if (i != ftvlist.Count - 1)
                {
                    sb.Append(ftv.FieldName + ",");
                }
                else
                {
                    sb.Append(ftv.FieldName);
                }
            }
            sb.Append(") values(");
            for (int i = 0; i < ftvlist.Count; i++)
            {
                FieldTypeValue ftv = (FieldTypeValue)ftvlist[i];
                if (ftv.IsNum)
                {
                    if (i != ftvlist.Count - 1)
                    {
                        sb.Append(ftv.FieldValue + ",");
                    }
                    else
                    {
                        sb.Append(ftv.FieldValue);
                    }
                }
                else
                {
                    if (i != ftvlist.Count - 1)
                    {
                        sb.Append("'" + ftv.FieldValue + "',");
                    }
                    else
                    {
                        sb.Append("'" + ftv.FieldValue + "'");
                    }
                }
            }
            sb.Append(")");
            return sb.ToString();
        }
 
        
        /// <summary>
        /// 构造更新Update语句
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="ftvlist">list</param>
        /// <param name="pkName">条件字段名</param>
        /// <param name="pkValue">条件字段值</param>
        /// <returns></returns>
        public static string createUpdateSql(string tableName, List<FieldTypeValue> ftvlist, string pkName, string pkValue)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" update ");
            sb.Append(tableName);
            sb.Append(" set");
            for (int i = 0; i < ftvlist.Count; i++)
            {
                FieldTypeValue ftv = (FieldTypeValue)ftvlist[i];
                if (i != ftvlist.Count - 1)
                {
                    if (ftv.IsNum)
                    {
                        sb.Append(" " + ftv.FieldName + "=" + ftv.FieldValue + ",");
                    }
                    else
                    {
                        sb.Append(" " + ftv.FieldName + "='" + ftv.FieldValue + "',");
                    }
                }
                else
                {
                    if (ftv.IsNum)
                    {
                        sb.Append(" " + ftv.FieldName + "=" + ftv.FieldValue + "");
                    }
                    else
                    {
                        sb.Append(" " + ftv.FieldName + "='" + ftv.FieldValue + "'");
                    }
                }
            }
            sb.Append(" where " + pkName + "='" + pkValue + "'");
            return sb.ToString();
        }
 
        /// <summary>
        /// 构造删除Delete语句
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="pkName">条件字段名</param>
        /// <param name="pkValue">条件字段值</param>
        /// <returns></returns>
        public static string createDeleteSql(string tableName, string pkName, string pkValue)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" delete from ");
            sb.Append(tableName);
            sb.Append(" where " + pkName + " = '" + pkValue + "'");
            return sb.ToString();
        }
    }
    #endregion
}