Zoe
2017-7-27 DbHelper通用数据库类及增删改 使用示例（转）
  1. DbHelper db = new DbHelper();  
  2.     protected void Page_Load(object sender, EventArgs e)  
  3.     {  
  4.   
  5.     }  
  6.   
  7.     //新增数据  
  8.     protected void Button1_Click(object sender, EventArgs e)  
  9.     {  
  10.         List<FieldTypeValue> ftvlist = new List<FieldTypeValue>();  
  11.         ftvlist.Add(new FieldTypeValue("[D_Name]", "测试用户" + DateTime.Now.ToString("yyyyMMddhhmmss")));  
  12.         ftvlist.Add(new FieldTypeValue("[D_Password]", "测试密码" + DateTime.Now.ToString("yyyyMMddhhmmss")));  
  13.         ftvlist.Add(new FieldTypeValue("[D_Else]", "测试备注" + DateTime.Now.ToString("yyyyMMddhhmmss")));  
  14.         string sql = BuilderSql.createInsertSql("TestTable", ftvlist);  
  15.         int opFlag = db.ExecuteNonQuery(db.GetSqlStringCommand(sql));  
  16.         if (opFlag > 0) { JsHelper.Alert("新增成功!", Page); } else { JsHelper.Alert("新增失败!", Page); };  
  17.     }  
  18.   
  19.     //修改数据  
  20.     protected void Button2_Click(object sender, EventArgs e)  
  21.     {  
  22.         List<FieldTypeValue> ftvlist = new List<FieldTypeValue>();  
  23.         ftvlist.Add(new FieldTypeValue("[D_Name]", "这是个错误dsadsadasd"));  
  24.         ftvlist.Add(new FieldTypeValue("[D_Password]", "aaa这是个错误，我还没有处理"));  
  25.         ftvlist.Add(new FieldTypeValue("[D_Else]", "aaa这是个错误，我还没有处理"));  
  26.         string sql = BuilderSql.createUpdateSql("TestTable", ftvlist, "D_ID", "1");  
  27.         int opFlag = db.ExecuteNonQuery(db.GetSqlStringCommand(sql));  
  28.         if (opFlag > 0) { JsHelper.Alert("修改成功!", Page); } else { JsHelper.Alert("修改失败!", Page); };  
  29.     }  
  30.   
  31.     //删除数据  
  32.     protected void Button3_Click(object sender, EventArgs e)  
  33.     {  
  34.         string sql = BuilderSql.createDeleteSql("[TestTable]", "[D_Id]", "1");  
  35.         int opFlag = db.ExecuteNonQuery(db.GetSqlStringCommand(sql));  
  36.         if (opFlag > 0) { JsHelper.Alert("删除成功!", Page); } else { JsHelper.Alert("删除失败!", Page); };  
  37.     }  
  38.   
  39.     //事务提交  
  40.     protected void Button4_Click(object sender, EventArgs e)  
  41.     {  
  42.         using (Trans t = new Trans())  
  43.         {  
  44.             try  
  45.             {  
  46.                 db.ExecuteNonQuery(db.GetSqlStringCommand("insert into TestTable(D_Name,D_Password,D_Else) values('aaaaa1','bbbbbb1','cccccc1')"), t);  
  47.                 db.ExecuteNonQuery(db.GetSqlStringCommand("insert into TestTable(D_Name,D_Password,D_Else) values('aaaaa2','bbbbbb2','cccccc2')"), t);  
  48.                 db.ExecuteNonQuery(db.GetSqlStringCommand("insert into TestTable(D_Name,D_Password,D_Else) values('aaaaa3','bbbbbb3','cccccc3')"), t);  
  49.                 db.ExecuteNonQuery(db.GetSqlStringCommand("insert into TestTable(D_Name,D_Password,D_Else) values('aaaaa4','bbbbbb4','cccccc4')"), t);  
  50.                 db.ExecuteNonQuery(db.GetSqlStringCommand("insert into TestTable(D_Name,D_Password,D_Else) values('aaaaa5','bbbbbb5','cccccc5')"), t);  
  51.                 t.Commit();  
  52.                 JsHelper.Alert("事务提交成功!", Page);  
  53.             }  
  54.             catch  
  55.             {  
  56.                 t.RollBack();  
  57.                 JsHelper.Alert("事务提交失败!", Page);  
  58.             }  
  59.         }  
  60.     }  
  61.   
  62.   
  63.   
  64.   
  65.   
  66.   
  67.     //分页控件分页  
  68.   
  69.     protected void Page_Load(object sender, EventArgs e)  
  70.     {  
  71.         if (!IsPostBack)  
  72.         {  
  73.             BindGridViewData();  
  74.         }  
  75.   
  76.     }  
  77.   
  78.     private void BindGridViewData()  
  79.     {  
  80.         string strCondition = "";  
  81.         MSCL.PageHelper wp = new MSCL.PageHelper();  
  82.         wp.TableName = "TestTable";  
  83.         wp.KeyField = "D_Id";  
  84.         wp.SortName = "D_Id";  
  85.         wp.Condition = strCondition;  
  86.         wp.CurrentPageIndex = AspNetPager1.CurrentPageIndex;  
  87.         wp.PageSize = AspNetPager1.PageSize;//=PageSize;  
  88.   
  89.         DataTable dt = wp.GetDataTableMyPage();  
  90.         GridView1.DataSource = dt;  
  91.         GridView1.DataBind();  
  92.   
  93.         AspNetPager1.RecordCount = wp.RecordCount;  
  94.         AspNetPager1.CustomInfoHTML = " 共<b>" + wp.RecordCount.ToString() + "</b>条记录/";  
  95.         AspNetPager1.CustomInfoHTML += " <b>" + wp.PageCount.ToString() + "</b>页";  
  96.         AspNetPager1.CustomInfoHTML += " 当前第<font color=\"red\"><b>" + AspNetPager1.CurrentPageIndex.ToString() + "</b></font>页";  
  97.     }  
  98.   
  99.     protected void AspNetPager1_PageChanged(object sender, EventArgs e)  
  100.     {  
  101.         BindGridViewData();  
  102.     }  
  103.   
  104.   
  105.   
  106.   
  107.   
  108.   
  109.     //字符串分页  
  110.   
  111.     protected void Page_Load(object sender, EventArgs e)  
  112.     {  
  113.         if (!IsPostBack)  
  114.         {  
  115.             bindData();  
  116.         }  
  117.     }  
  118.   
  119.     protected void bindData()  
  120.     {  
  121.         int count = 0; //记录总数  
  122.         int pageSize = 10; //每页显示记录数  
  123.         int pageIndex = (Request["PageIndex"] == null) ? 1 : Convert.ToInt32(Request["PageIndex"]);  
  124.         DataSet ds = MSCL.PageHelper.PageList("TestTable", pageSize, pageIndex, "D_ID", true, "", out count);  
  125.         GridView1.DataSource = ds;  
  126.         GridView1.DataBind();  
  127.   
  128.         ltPage.Text = MSCL.PageHelper.ToSplitPageHtml(count, pageSize, 5, 1, "", true);  
  129.     }  
  130. 以上我们好像没有指定数据库连接字符串,大家如果看下DbHelper的代码,就知道要使用它必须在config中配置两个参数,如下:  
  131.     <appSettings>  
  132.         <add key="DbHelperProvider" value="System.Data.SqlClient"/>  
  133.         <add key="DbHelperConnectionString" value="Data Source=(local);Initial Catalog=DbHelperTest;Persist Security Info=True;User ID=sa;Password=sa"/>  
  134.     </appSettings>   


 

[csharp] view plain copy
 
 print?
    1. using System;  
    2. using System.Collections.Generic;  
    3. using System.Linq;  
    4. using System.Text;  
    5.   
    6. namespace FairHR.Util  
    7. {  
    8.     #region 数据表字段类  
    9.     /// <summary>  
    10.     /// 数据表字段类  
    11.     /// </summary>  
    12.     public class FieldTypeValue  
    13.     {  
    14.         /// <summary>  
    15.         /// 字段容器  
    16.         /// </summary>  
    17.         /// <param name="fieldName">字段名</param>  
    18.         /// <param name="fieldValue">字段值</param>  
    19.         /// <param name="isNum"></param>  
    20.         public FieldTypeValue(string fieldName, string fieldValue, bool isNum)  
    21.         {  
    22.             this.fieldName = fieldName;  
    23.             this.fieldValue = fieldValue;  
    24.             this.isNum = isNum;  
    25.         }  
    26.   
    27.         /// <summary>  
    28.         /// 字段容器  
    29.         /// </summary>  
    30.         /// <param name="fieldName">字段名</param>  
    31.         /// <param name="fieldValue">字段值</param>  
    32.         public FieldTypeValue(string fieldName, string fieldValue)  
    33.         {  
    34.             this.fieldName = fieldName;  
    35.             this.fieldValue = fieldValue;  
    36.         }  
    37.   
    38.         private string fieldName;  
    39.         /// <summary>  
    40.         /// 字段名  
    41.         /// </summary>  
    42.         public string FieldName  
    43.         {  
    44.             get { return fieldName; }  
    45.             set { fieldName = value; }  
    46.         }  
    47.   
    48.         private bool isNum = false;  
    49.         /// <summary>  
    50.         /// 是否数字  
    51.         /// </summary>  
    52.         public bool IsNum  
    53.         {  
    54.             get { return isNum; }  
    55.             set { isNum = value; }  
    56.         }  
    57.   
    58.         private string fieldValue;  
    59.         /// <summary>  
    60.         /// 字段值  
    61.         /// </summary>  
    62.         public string FieldValue  
    63.         {  
    64.             get { return fieldValue; }  
    65.             set { fieldValue = value; }  
    66.         }  
    67.     }  
    68.     #endregion  
    69.  
    70.     #region SQL语句的构造类  
    71.     /// <summary>  
    72.     /// SQL语句的构造类  
    73.     /// </summary>  
    74.     public class BuilderSql  
    75.     {  
    76.   
    77.   
    78.         /// <summary>  
    79.         /// 构造新增Insert语句  
    80.         /// </summary>  
    81.         /// <param name="tableName">表名</param>  
    82.         /// <param name="ftvlist">list</param>  
    83.         /// <returns></returns>  
    84.         public static string createInsertSql(string tableName, List<FieldTypeValue> ftvlist)  
    85.         {  
    86.             StringBuilder sb = new StringBuilder();  
    87.             sb.Append(" insert into ");  
    88.             sb.Append(tableName);  
    89.             sb.Append("(");  
    90.             for (int i = 0; i < ftvlist.Count; i++)  
    91.             {  
    92.                 FieldTypeValue ftv = (FieldTypeValue)ftvlist[i];  
    93.                 if (i != ftvlist.Count - 1)  
    94.                 {  
    95.                     sb.Append(ftv.FieldName + ",");  
    96.                 }  
    97.                 else  
    98.                 {  
    99.                     sb.Append(ftv.FieldName);  
    100.                 }  
    101.             }  
    102.             sb.Append(") values(");  
    103.             for (int i = 0; i < ftvlist.Count; i++)  
    104.             {  
    105.                 FieldTypeValue ftv = (FieldTypeValue)ftvlist[i];  
    106.                 if (ftv.IsNum)  
    107.                 {  
    108.                     if (i != ftvlist.Count - 1)  
    109.                     {  
    110.                         sb.Append(ftv.FieldValue + ",");  
    111.                     }  
    112.                     else  
    113.                     {  
    114.                         sb.Append(ftv.FieldValue);  
    115.                     }  
    116.                 }  
    117.                 else  
    118.                 {  
    119.                     if (i != ftvlist.Count - 1)  
    120.                     {  
    121.                         sb.Append("'" + ftv.FieldValue + "',");  
    122.                     }  
    123.                     else  
    124.                     {  
    125.                         sb.Append("'" + ftv.FieldValue + "'");  
    126.                     }  
    127.                 }  
    128.             }  
    129.             sb.Append(")");  
    130.             return sb.ToString();  
    131.         }  
    132.   
    133.           
    134.         /// <summary>  
    135.         /// 构造更新Update语句  
    136.         /// </summary>  
    137.         /// <param name="tableName">表名</param>  
    138.         /// <param name="ftvlist">list</param>  
    139.         /// <param name="pkName">条件字段名</param>  
    140.         /// <param name="pkValue">条件字段值</param>  
    141.         /// <returns></returns>  
    142.         public static string createUpdateSql(string tableName, List<FieldTypeValue> ftvlist, string pkName, string pkValue)  
    143.         {  
    144.             StringBuilder sb = new StringBuilder();  
    145.             sb.Append(" update ");  
    146.             sb.Append(tableName);  
    147.             sb.Append(" set");  
    148.             for (int i = 0; i < ftvlist.Count; i++)  
    149.             {  
    150.                 FieldTypeValue ftv = (FieldTypeValue)ftvlist[i];  
    151.                 if (i != ftvlist.Count - 1)  
    152.                 {  
    153.                     if (ftv.IsNum)  
    154.                     {  
    155.                         sb.Append(" " + ftv.FieldName + "=" + ftv.FieldValue + ",");  
    156.                     }  
    157.                     else  
    158.                     {  
    159.                         sb.Append(" " + ftv.FieldName + "='" + ftv.FieldValue + "',");  
    160.                     }  
    161.                 }  
    162.                 else  
    163.                 {  
    164.                     if (ftv.IsNum)  
    165.                     {  
    166.                         sb.Append(" " + ftv.FieldName + "=" + ftv.FieldValue + "");  
    167.                     }  
    168.                     else  
    169.                     {  
    170.                         sb.Append(" " + ftv.FieldName + "='" + ftv.FieldValue + "'");  
    171.                     }  
    172.                 }  
    173.             }  
    174.             sb.Append(" where " + pkName + "='" + pkValue + "'");  
    175.             return sb.ToString();  
    176.         }  
    177.   
    178.         /// <summary>  
    179.         /// 构造删除Delete语句  
    180.         /// </summary>  
    181.         /// <param name="tableName">表名</param>  
    182.         /// <param name="pkName">条件字段名</param>  
    183.         /// <param name="pkValue">条件字段值</param>  
    184.         /// <returns></returns>  
    185.         public static string createDeleteSql(string tableName, string pkName, string pkValue)  
    186.         {  
    187.             StringBuilder sb = new StringBuilder();  
    188.             sb.Append(" delete from ");  
    189.             sb.Append(tableName);  
    190.             sb.Append(" where " + pkName + " = '" + pkValue + "'");  
    191.             return sb.ToString();  
    192.         }  
    193.     }  
    194.     #endregion  
    195. }  
posted on 2017-07-27 11:21  口袋里的SKY  阅读(190)  评论(0)  编辑  收藏  举报

Copyright © 2024 口袋里的SKY
Powered by .NET 9.0 on Kubernetes Powered By博客园