数据表读写类
最今天做了一个修改数据库表的通用类，代码没什么技术含量，纯属是为了对付以后可能遇到的那些超小项目，对系统性能没有特别要求，而且随意性很大，说不准早上老板说要做，下午就改方案的情况出现，为此做了这么一个表处理的通用类，提供了对表的添加，修改，删除。
使用很简单
CR_Soft.Data.SqlTableHelper STH = new CR_Soft.Data.SqlTableHelper("Data Source=127.0.0.1;Initial Catalog=Data;Integrated Security=True", "Comment");
STH.ParSqlAdd("UserID", 6);//用来指定哪些列需要修改（添加）
STH.ParWhereAdd("ID", 1);//添加相应的条件
MessageBox.Show(STH.Insert().ToString() + " 行受影响");//添加
MessageBox.Show(STH.Update().ToString() + " 行受影响");//修改
MessageBox.Show(STH.Delete().ToString() + " 行受影响");//删除
STH.ParClear();//清楚参数
STH.Close();//释放资源
源代码
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
namespace CR_Soft.Data
{
/// <summary>
/// 操作符
/// </summary>
public enum Operators
{
/// <summary>
/// 等于
/// </summary>
Equal,
/// <summary>
/// 大于
/// </summary>
More,
/// <summary>
/// 大于等于
/// </summary>
MoreEqual,
/// <summary>
/// 小于
/// </summary>
Less,
/// <summary>
/// 小于等于
/// </summary>
LessEqual,
/// <summary>
/// 不等于
/// </summary>
NotEqual,
/// <summary>
/// 模糊
/// </summary>
Like
}
/// <summary>
/// 表操作助手
/// </summary>
public class SqlTableHelper
{
/// <summary>
/// 数据库访问组件
/// </summary>
private CR_Soft.Data.SQLServer _SqlServer;
/// <summary>
/// SqlServer对象是否是本类创建
/// </summary>
private bool _IntoCreater;
/// <summary>
/// 要添加或修改的列
/// </summary>
private List<string> _StrSqlList = new List<string>();
/// <summary>
/// 添加（修改）参数
/// </summary>
private List<SqlParameter> _ParSqlList = new List<SqlParameter>();
/// <summary>
/// 条件参数，用来构建条件语句
/// </summary>
private List<string> _StrWhereList = new List<string>();
/// <summary>
/// 条件参数，用来记录参数内容
/// </summary>
private List<SqlParameter> _ParWhereList = new List<SqlParameter>();
/// <summary>
/// 操作的表
/// </summary>
private string _Table;
/// <summary>
/// 构造函数
/// </summary>
public SqlTableHelper(SQLServer SqlServer, string Table)
{
_SqlServer = SqlServer;
_Table = Table;
_IntoCreater = false;
}
/// <summary>
/// 构造函数
/// </summary>
public SqlTableHelper(string spConStr, string Table)
{
_SqlServer = new SQLServer(spConStr);
_Table = Table;
_IntoCreater = true;
}
/// <summary>
/// 构造函数
/// </summary>
/// <param name="spConName"></param>
/// <param name="config"></param>
/// <param name="Table"></param>
public SqlTableHelper(string spConName, bool config, string Table)
{
_SqlServer = new SQLServer(spConName, config);
_Table = Table;
_IntoCreater = true;
}
/// <summary>
/// 析构函数
/// </summary>
~SqlTableHelper()
{
}
/// <summary>
/// 关闭
/// </summary>
public void Close()
{
if (_IntoCreater == true)
{
_SqlServer.Close();
}
else
{
_SqlServer.disConnection();
}
}
/// <summary>
/// 数据参数添加
/// </summary>
public void ParSqlAdd(string ColumnName, object Value)
{
//加S只是为了区分条件参数和数据参数
_ParSqlList.Add(new SqlParameter("S" + ColumnName, Value));
_StrSqlList.Add(ColumnName);
}
/// <summary>
/// 条件参数添加
/// </summary>
public void ParWhereAdd(string ColumnName, object Value)
{
ParWhereAdd(ColumnName, Value, Operators.Equal);
}
/// <summary>
/// 条件参数添加
/// </summary>
public void ParWhereAdd(string ColumnName, object Value, Operators Oper)
{
//加W只是为了区分条件参数和数据参数
_StrWhereList.Add(ColumnName + GetOperators(Oper) + "@W" + ColumnName + " ");
_ParWhereList.Add(new SqlParameter("W" + ColumnName, Value));
}
/// <summary>
/// 条件参数添加
/// </summary>
public void ParWhereAdd(string ColumnName, object Value, SqlDbType dbType, Operators Oper)
{
_StrWhereList.Add(ColumnName + GetOperators(Oper) + "@W" + ColumnName + " ");
SqlParameter SqlPar = new SqlParameter("W" + ColumnName, dbType);
SqlPar.Value = Value;
_ParWhereList.Add(SqlPar);
}
/// <summary>
/// 更新语句
/// </summary>
public int Update()
{
try
{
string SqlStr = "Update " + _Table + " Set ";
for (int i = 0; i < _ParSqlList.Count - 1; i++)
{
//加S只是为了区分条件参数和数据参数
SqlStr += _StrSqlList[i] + " = @" + _ParSqlList[i].ParameterName + ", ";
}
SqlStr += _StrSqlList[_ParSqlList.Count - 1] + " = @" + _ParSqlList[_ParSqlList.Count - 1].ParameterName + " ";
SqlStr += GetWhereSql();
return SqlExec(SqlStr);
}
catch (Exception)
{
return -1;
}
}
/// <summary>
/// 插入语句
/// </summary>
public int Insert()
{
try
{
string SqlStr = "INSERT INTO " + _Table + " ";
string SqlCol = "(";
string SqlPar = "VALUES (";
for (int i = 0; i < _ParSqlList.Count - 1; i++)
{
SqlCol += _StrSqlList[i] + ",";
SqlPar += "@" + _ParSqlList[i].ParameterName + ",";
}
SqlCol += _StrSqlList[_StrSqlList.Count - 1] + ")";
SqlPar += "@" + _ParSqlList[_StrSqlList.Count - 1].ParameterName + ")";
SqlStr += SqlCol + SqlPar;
return SqlExec(SqlStr);
}
catch (Exception)
{
return -1;
}
}
/// <summary>
/// 获得条件语句
/// </summary>
/// <returns></returns>
private string GetWhereSql()
{
string SqlStr = " where 1=1 ";
for (int i = 0; i < _StrWhereList.Count; i++)
{
SqlStr += " and " + _StrWhereList[i];
}
return SqlStr;
}
/// <summary>
/// 删除语句
/// </summary>
public int Delete()
{
try
{
string SqlStr = "DELETE FROM Comment ";
SqlStr += GetWhereSql();
return SqlExec(SqlStr);
}
catch (Exception)
{
return -1;
}
}
/// <summary>
/// 所有参数清空
/// </summary>
public void ParClear()
{
ParWhereClear();
ParSqlClear();
}
/// <summary>
/// 条件参数清空
/// </summary>
public void ParWhereClear()
{
_ParWhereList.Clear();
_StrWhereList.Clear();
}
/// <summary>
/// 数据参数清空
/// </summary>
public void ParSqlClear()
{
_ParSqlList.Clear();
_StrSqlList.Clear();
}
/// <summary>
/// 获得运算符
/// </summary>
/// <param name="Oper"></param>
/// <returns></returns>
private string GetOperators(Operators Oper)
{
switch (Oper)
{
case Operators.Equal: return " = ";
case Operators.Less: return " < ";
case Operators.LessEqual: return " <= ";
case Operators.Like: return " Like ";
case Operators.More: return " > ";
case Operators.MoreEqual: return " >= ";
case Operators.NotEqual: return " != ";
default: return " = ";
}
}
/// <summary>
/// SQL代码执行
/// </summary>
/// <param name="SqlStr"></param>
/// <returns></returns>
private int SqlExec(string SqlStr)
{
_SqlServer.CommandText = SqlStr;
for (int i = 0; i < _ParSqlList.Count; i++)
{
_SqlServer.Parameters.Add(_ParSqlList[i]);
}
for (int i = 0; i < _ParWhereList.Count; i++)
{
_SqlServer.Parameters.Add(_ParWhereList[i]);
}
return _SqlServer.Exec();
}
}
}
代码下载：https://files.cnblogs.com/CR-Soft/SqlTableHelper.rar
