java mysql dbhelper_DbHelper通用数据库类及增删改 使用示例

DbHelper db = new DbHelper();

protected void Page_Load(object sender, EventArgs e)

{

}

//新增数据

protected void Button1_Click(object sender, EventArgs e)

{

List ftvlist = new List();

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 ftvlist = new List();

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 = " 共" + wp.RecordCount.ToString() + "条记录/";

AspNetPager1.CustomInfoHTML += " " + wp.PageCount.ToString() + "页";

AspNetPager1.CustomInfoHTML += " 当前第" + AspNetPager1.CurrentPageIndex.ToString() + "页";

}

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中配置两个参数,如下:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

namespace FairHR.Util

{

#region 数据表字段类

///

/// 数据表字段类

///

public class FieldTypeValue

{

///

/// 字段容器

///

/// 字段名

/// 字段值

///

public FieldTypeValue(string fieldName, string fieldValue, bool isNum)

{

this.fieldName = fieldName;

this.fieldValue = fieldValue;

this.isNum = isNum;

}

///

/// 字段容器

///

/// 字段名

/// 字段值

public FieldTypeValue(string fieldName, string fieldValue)

{

this.fieldName = fieldName;

this.fieldValue = fieldValue;

}

private string fieldName;

///

/// 字段名

///

public string FieldName

{

get { return fieldName; }

set { fieldName = value; }

}

private bool isNum = false;

///

/// 是否数字

///

public bool IsNum

{

get { return isNum; }

set { isNum = value; }

}

private string fieldValue;

///

/// 字段值

///

public string FieldValue

{

get { return fieldValue; }

set { fieldValue = value; }

}

}

#endregion

#region SQL语句的构造类

///

/// SQL语句的构造类

///

public class BuilderSql

{

///

/// 构造新增Insert语句

///

/// 表名

/// list

///

public static string createInsertSql(string tableName, List 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();

}

///

/// 构造更新Update语句

///

/// 表名

/// list

/// 条件字段名

/// 条件字段值

///

public static string createUpdateSql(string tableName, List 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();

}

///

/// 构造删除Delete语句

///

/// 表名

/// 条件字段名

/// 条件字段值

///

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

}