using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data;using System.Data.SqlClient;using System.Configuration;using System.Xml;using UtilityConsole;namespace DBAccess{ public enum DBOperation { ADD, UPDATE }; public class BaseOperation { //连接字符串 private string _connStr = string.Empty; public string ErrorInfo = string.Empty; protected string strTmp; protected StringBuilder sbMutilProcessSqlString; protected StringBuilder sb; protected int i; protected ReadDataFromDataReader mMyReadDataFromDataReader; protected DataTable dtTable; protected List<DBOperation> ActionList; public string ConnStr { get { return _connStr; } set { _connStr = value; } } protected BaseOperation() { _connStr = ConfigurationManager.AppSettings["connection"]; DBHelper.Instance.ConnectionStr = _connStr; sb = new StringBuilder(); sbMutilProcessSqlString = new StringBuilder(); mMyReadDataFromDataReader = new ReadDataFromDataReader(BaseReadDataFromDataReader); ActionList = new List<DBOperation>(); } /// <summary> /// 开始多任务操作 /// </summary> public void StartMultiOperation() { ActionList.Clear(); sbMutilProcessSqlString.Remove(0, sbMutilProcessSqlString.Length); } /// <summary> /// 添加动作 /// </summary> /// <param name="dbo">具体动作</param> protected void AddAction(List<string> paramValues, string tableName, string[] _nameString, string[] _nameStringType, DBOperation dbo) { if (dbo == DBOperation.ADD) { strTmp = BaseCreateInsertSqlString(paramValues, tableName, _nameString, _nameStringType); if (strTmp != null && strTmp.Trim().Length > 0) { sbMutilProcessSqlString.Append(strTmp); sbMutilProcessSqlString.Append("\r\n"); } } else if (dbo == DBOperation.UPDATE) { strTmp = BaseCreateUpdateSqlString(paramValues, tableName, _nameString, _nameStringType); if (strTmp != null && strTmp.Trim().Length > 0) { sbMutilProcessSqlString.Append(strTmp); sbMutilProcessSqlString.Append("\r\n"); } } } /// <summary> /// 执行多步操作 /// </summary> /// <returns>影响的行数，失败返回-1</returns> public int ExecuteMultiOperation() { strTmp = sbMutilProcessSqlString.ToString().Trim(); if (strTmp != null && strTmp.Length > 0) { return DBHelper.Instance.ExecuteSqlStatementTrans(sbMutilProcessSqlString.ToString()); } else { return -1; } } /// <summary> /// SqlCommand方法执行更新、删除 /// </summary> /// <param name="sqlStr">sqlStr执行语句</param> /// <returns></returns> protected int SqlExecutive(string sqlStr) { return DBHelper.Instance.ExecuteSqlStatement(sqlStr); } /// <summary> /// 是否存在 /// </summary> /// <param name="strSql">sql语句</param> /// <returns>判断是否存在</returns> protected bool BaseExists(string strSql) { return DBHelper.Instance.Exists(strSql); } /// <summary> /// 生成插入语句 /// </summary> /// <param name="paramValues">参数值</param> /// <param name="tableName">数据表名</param> /// <param name="_nameString">字段名</param> /// <param name="_nameStringType">字段类型</param> /// <returns>生成的插入语句</returns> protected string BaseCreateInsertSqlString(List<string> paramValues, string tableName, string[] _nameString, string[] _nameStringType) { sb.Remove(0, sb.Length); if (paramValues.Count != _nameString.Length) { return null; } sb.Append(string.Format("INSERT INTO [{0}] ", tableName)); //ok for (i = 1; i < _nameString.Length; i++) { if (i == 1) { sb.Append(string.Format("({0}", _nameString[i])); } else { sb.Append(string.Format(",{0}", _nameString[i])); } } sb.Append(") VALUES ("); //not ok for (i = 1; i < _nameString.Length; i++) { if (_nameStringType[i] == "int" || _nameStringType[i] == "float") { if (paramValues[i].Trim() == "") { paramValues[i] = "0"; } sb.Append(string.Format("{0},", paramValues[i])); } else { sb.Append(string.Format("'{0}',", paramValues[i])); } } if (i != 1) { sb.Remove(sb.Length - 1, 1); } sb.Append(")"); return sb.ToString(); } //基本的插入 protected int BaseInsertItem(List<string> paramValues, string tableName, string[] _nameString, string[] _nameStringType) { return SqlExecutive(BaseCreateInsertSqlString(paramValues, tableName, _nameString, _nameStringType)); } /// <summary> /// 删除操作 /// </summary> /// <param name="tableName">表名</param> /// <param name="strWhere">where子句</param> /// <returns>返回的结果</returns> protected int BaseDelete(string tableName,string strWhere) { sb.Remove(0, sb.Length); sb.Append(string.Format("delete from {0}", tableName)); if (strWhere != null && strWhere.Length > 0) { sb.Append(string.Format(" where {0}", strWhere)); } return SqlExecutive(sb.ToString()); } /// <summary> /// 添加更新语句 /// </summary> /// <param name="paramValues">值</param> /// <param name="tableName">数据库表</param> /// <param name="_nameString">字段</param> /// <param name="_nameStringType">字段类型</param> /// <returns></returns> protected string BaseCreateUpdateSqlString(List<string> paramValues, string tableName, string[] _nameString, string[] _nameStringType) { sb.Remove(0, sb.Length); if (paramValues.Count != _nameString.Length) { return null; } sb.Append(string.Format("update [{0}] set ", tableName)); for (i = 1; i <= _nameString.Length - 1; i++) { if (_nameStringType[i] == "int" || _nameStringType[i] == "float") { if (paramValues[i].Trim() == "") { paramValues[i] = "0"; } sb.Append(string.Format("{0} = {1},", _nameString[i], paramValues[i])); } else { sb.Append(string.Format("{0} = '{1}',", _nameString[i], paramValues[i])); } } if (i != 1) { sb.Remove(sb.Length - 1, 1); } sb.Append(string.Format(" where {0} = {1}", _nameString[0], paramValues[0])); return sb.ToString(); } /// <summary> /// 更新函数 /// </summary> protected int BaseUpdateItem(List<string> paramValues, string tableName, string[] _nameString, string[] _nameStringType) { return this.SqlExecutive(BaseCreateUpdateSqlString(paramValues, tableName, _nameString, _nameStringType)); } /// <summary> /// 创造内存表结构 /// </summary> /// <param name="colNames">类名</param> /// <returns>带结构的表</returns> protected DataTable GetDataTableSchema(string[] colNames) { DataTable dtTmp; DataColumn dcColumn; dtTmp = null; if (colNames != null && colNames.Length > 0) { dtTmp = new DataTable(); for (i = 0; i < colNames.Length; ++i) { dcColumn = new DataColumn(colNames[i]); dtTmp.Columns.Add(dcColumn); } } return dtTmp; } /// <summary> /// 读取数据 /// </summary> /// <param name="reader">数据集</param> protected void BaseReadDataFromDataReader(SqlDataReader reader) { if (reader.HasRows) { if (dtTable != null && dtTable.Columns.Count > 0) { DataRow NewRow; string strColName; int ColCount; while (reader.Read()) { NewRow = dtTable.NewRow(); ColCount = dtTable.Columns.Count; for (i = 0; i < dtTable.Columns.Count; ++i) { strColName = dtTable.Columns[i].ColumnName; NewRow[strColName] = reader[strColName]; } dtTable.Rows.Add(NewRow); } } } } /// <summary> /// 查询语句 /// </summary> /// <param name="tableName">表的名字</param> /// <param name="_nameString">列名</param> /// <param name="strWhere">子查询</param> /// <returns>返回的数据列</returns> protected DataTable BaseSelectItem(string tableName, string[] _nameString, string strWhere) { sb.Remove(0, sb.Length); sb.Append(string.Format("select * from [{0}]", tableName)); if (strWhere != null && strWhere.Length > 0) { sb.Append(string.Format(" where {0}", strWhere)); } dtTable = GetDataTableSchema(_nameString); //创造带结构的表 if (DBHelper.Instance.ExecuteSqlStatement(sb.ToString(), mMyReadDataFromDataReader)) { return dtTable; } return null; } protected int BaseGetMaxId(string strSql) { return int.Parse(DBHelper.Instance.SelectDB(strSql).Rows[0][0].ToString()); } }}该类主要将底层的类进行封装，实现了增删改和批量的事务操作
