using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.EntityClient;
using System.Data.Objects;
using System.Collections;
using System.Data.SqlClient;
namespace GY.DA.Common
{
//为了处理所传哈希表key大小写不一致的情况，在建立哈希表时，采用如下方式建立：
// private Hashtable data = new Hashtable(StringComparer.OrdinalIgnoreCase);
/// <summary>
/// 实现对数据的SQL操作（增删改）
/// </summary>
public class SqlHandler
{
#region 参数定义
private DbConnection conn;
/// <summary>
/// 链接字符串
/// </summary>
private string _tableName = "";
private List<string> _tableKeys = new List<string>();
private List<DbParameter> plist = new List<DbParameter>();
private List<DbCommand> operList = new List<DbCommand>();
private Hashtable _FieldsTypes = new Hashtable(StringComparer.OrdinalIgnoreCase);
public Hashtable FieldsTypes
{
get
{
return this._FieldsTypes;
}
}
#endregion
/// <summary>
/// 构造函数
/// </summary>
/// <param name="ASysName">连接字符串名称</param>
/// <param name="tableName"></param>
public SqlHandler(DbConnection connection, string tableName)
{
useTran = true;
conn = connection;
_tableName = tableName;
Prepair();
}
/// <summary>
/// 构造函数
/// </summary>
/// <param name="AConnName">连接字符串名称</param>
public SqlHandler(DbConnection connection)
{
conn = connection;
}
/// <summary>
/// 支持多个表更新
/// </summary>
public string tableName
{
get { return _tableName; }
set { SetTableName(value); }
}
/// <summary>
/// 是否使用事务
/// </summary>
public bool useTran { get; set; }
/// <summary>
/// 设置表的主键字段，数据处理时按照主键进行更新、删除，
/// 插入数据时，如果主键有值，则插入主键值，否则不插入主键值
/// </summary>
/// <param name="keys"></param>
public void SetTableKeys(string[] keys)
{
_tableKeys.Clear();
_tableKeys.AddRange(keys);
}
private void SetTableName(string value)
{
if (value.Trim().ToLower() == _tableName.ToLower())
{
return;
}
_tableName = value;
Prepair();
}
private DbType TypeToDbType(Type t)
{
DbType dbt;
try
{
dbt = (DbType)Enum.Parse(typeof(DbType), t.Name);
}
catch
{
dbt = DbType.Object;
}
return dbt;
}
//获取数据表结构及字段类型
public void Prepair()
{
if (conn.State != ConnectionState.Open) { conn.Open(); }
_FieldsTypes.Clear();
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = "select * from " + _tableName + " where 0!=0";
using (DbDataReader reader = cmd.ExecuteReader())
{
for (int i = 0; i < reader.FieldCount; i++)
{
//if (reader.GetName(i) == "PicStream" && _tableName.ToLower() == "XianCCY_PicS_Stream".ToLower())
//{
//}
_FieldsTypes[reader.GetName(i)] = TypeToDbType(reader.GetFieldType(i));
}
}
}
//如果不是字符类型的数据库类型，paramValue为带客格的字符串时去掉空格
private object getParamValue(bool valueEmpytToDBNull, object paramValue, DbType paramType)
{
object result = paramValue;
if (paramValue == null) { return result; }
if (valueEmpytToDBNull && (paramValue.GetType().Name == "String"))
{
if ((paramType != DbType.AnsiString) || (paramType != DbType.String) || (paramType != DbType.StringFixedLength))
{
result = paramValue.ToString().Trim();
}
}
return result;
}
private bool CreateSqlParams(string paramName, object paramValue, DbType paramType, DbCommand cmd, bool valueEmpytToDBNull = true)
{
bool result = false;
if ((cmd == null) || (string.IsNullOrEmpty(paramName)))
{
return result;
}
paramValue = getParamValue(valueEmpytToDBNull, paramValue, paramType);
DbParameter param = cmd.CreateParameter();
param.ParameterName = paramName;
param.DbType = paramType;
if ((paramValue == null) || (valueEmpytToDBNull) && (string.IsNullOrEmpty(paramValue.ToString())))
{
param.Value = DBNull.Value;
}
else
{
if (paramType == DbType.Boolean)
{
string v = paramValue.GetType().ToString().ToLower();
bool value = true; //数字类型，0代表false,字符串：true代表true，bool型直接用，其它只要不空，就为真
if (v.Contains("int"))
{
value = (int)paramValue != 0;
}
else if (v.Contains("string"))
{
string pv= paramValue.ToString().ToLower();
value = pv == "true" || pv == "on" || pv == "1" || pv == "checked" || pv=="是";
}
else if (v.Contains("bool"))
{
value = (bool)paramValue;
}
param.Value = value;
}
else
{
if (paramType.ToString().ToLower().Contains("date") && (paramValue == null || paramValue.ToString() == ""))
{
param.Value = DBNull.Value;
}
else
{
param.Value = paramValue;
}
}
}
cmd.Parameters.Add(param);
return result;
}
public void InsertRow(Hashtable rowData)
{
DbCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
string sql = "insert into " + _tableName + "(";
string sqlv = " values(";
foreach (string key in _FieldsTypes.Keys)
{
if (rowData.ContainsKey(key))
{
sql += "[" + key + "],";
sqlv += "@" + key + ",";
}
}
sql = sql.Substring(0, sql.Length - 1);
sqlv = sqlv.Substring(0, sqlv.Length - 1);
cmd.CommandText = sql + ")" + sqlv + ")";
foreach (string key in _FieldsTypes.Keys)
{
if (rowData.ContainsKey(key))
{
CreateSqlParams(key, rowData[key], (DbType)_FieldsTypes[key.ToLower()], cmd);
}
}
operList.Add(cmd);
}
//利用key=value进行数据更新
public void UpdateRow(Hashtable rowData, string key, string Value)
{
Hashtable wData = new Hashtable(StringComparer.OrdinalIgnoreCase);
wData[key] = Value;
UpdateRow(rowData, wData);
}
/// <summary>
/// 利用表的键值进行更新，键取自Tablekeys,值从rowData中读取
/// </summary>
/// <param name="rowData"></param>
public void UpdateRow(Hashtable rowData)
{
Hashtable wData = new Hashtable(StringComparer.OrdinalIgnoreCase);
foreach (string key in _tableKeys)
{
if (!rowData.ContainsKey(key))
{
throw new Exception("数据中缺少更新的主键！");
}
else
{
wData[key] = rowData[key];
}
}
UpdateRow(rowData, wData);
}
/// <summary>
/// 根据whereData中的值对进行更新，数据采用=判断
/// </summary>
/// <param name="rowData"></param>
/// <param name="whereData"></param>
public void UpdateRow(Hashtable rowData, Hashtable whereData)
{
DbCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
string sql = "update " + _tableName + " set ";
foreach (string key in _FieldsTypes.Keys)
{
if (rowData.ContainsKey(key)) //不更新主键字段
{
sql += "[" + key + "] = @" + key + ",";
}
}
sql = sql.Substring(0, sql.Length - 1);
string wSQL = "";
foreach (string key in whereData.Keys)
{
wSQL += "[" + key + "]" + "= @where__" + key + " and ";
}
if (wSQL != "")
{
sql += " where " + wSQL.Substring(0, wSQL.Length - 4);
}
//生成要更新的数据参数表
foreach (string key in _FieldsTypes.Keys)
{
if (rowData.ContainsKey(key))
{
CreateSqlParams(key, rowData[key], (DbType)_FieldsTypes[key.ToLower()], cmd);
}
}
//生成Where条件参数表
foreach (string key in whereData.Keys)
{
CreateSqlParams("where__" + key, whereData[key], (DbType)_FieldsTypes[key], cmd, false);
}
cmd.CommandText = sql;
operList.Add(cmd);
}
/// <summary>
/// 根据whereSQL和whereParams中的值对进行数据更新，可以更新主键值
/// </summary>
/// <param name="rowData"></param>
/// <param name="whereSQL"></param>
/// <param name="whereParams"></param>
public void UpdateRow(Hashtable rowData, string whereSQL, Hashtable whereParams)
{
DbCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
string sql = "update " + _tableName + " set ";
foreach (string key in _FieldsTypes.Keys)
{
if (rowData.ContainsKey(key))
{
sql += "[" + key + "] = @" + key + ",";
}
}
sql = sql.Substring(0, sql.Length - 1);
//生成要更新的数据参数表
foreach (string key in whereParams.Keys)
{
if (rowData.ContainsKey(key))
{
CreateSqlParams(key, rowData[key], (DbType)_FieldsTypes[key], cmd);
}
}
//生成Where条件参数表
foreach (string key in whereParams.Keys)
{
CreateSqlParams(key, whereParams[key], (DbType)_FieldsTypes[key], cmd, false);
}
cmd.CommandText = sql + " where " + whereSQL;
operList.Add(cmd);
}
public void DeleteRow(Hashtable whereData)
{
if (whereData.Count == 0) { return; }
DbCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
string sql = "delete from " + _tableName + " where";
foreach (string key in whereData.Keys)
{
sql += " [" + key + "]" + "=@where__" + key + " and ";
CreateSqlParams("where__" + key, whereData[key], (DbType)_FieldsTypes[key], cmd, false);
}
cmd.CommandText = sql.Substring(0, sql.Length - 4);
operList.Add(cmd);
}
public void DeleteRow(string key, string value)
{
Hashtable wData = new Hashtable(StringComparer.OrdinalIgnoreCase);
wData[key] = value;
DeleteRow(wData);
}
public void DeleteRow(string delSql, Hashtable whereParams)
{
DbCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = delSql;
//生成Where条件参数表
foreach (string key in whereParams.Keys)
{
CreateSqlParams(key, whereParams[key], (DbType)_FieldsTypes[key], cmd, false);
}
operList.Add(cmd);
}
public void DeleteAll()
{
DbCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
string sql = "delete from " + _tableName;
cmd.CommandText = sql;
operList.Add(cmd);
}
/// <summary>
/// 执行SQL语句添加
/// </summary>
/// <param name="ASqlCommond"></param>
public void ExecuteSql(string ASqlCommond)
{
DbCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = ASqlCommond;
operList.Add(cmd);
}
public bool CopyCommand(DbCommand fromCom, ref DbCommand toCom)
{
bool result = false;
try
{
toCom.Parameters.Clear();
toCom.CommandText = fromCom.CommandText;
toCom.CommandType = fromCom.CommandType;
foreach (DbParameter param in fromCom.Parameters)
{
DbParameter tmpParam = toCom.CreateParameter();
tmpParam.DbType = param.DbType;
tmpParam.ParameterName = param.ParameterName;
tmpParam.Value = param.Value;
toCom.Parameters.Add(tmpParam);
}
}
catch
{
return result;
}
result = true;
return result;
}
public int ExecuteScalar()
{
int m_Ret = 0;
try
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
foreach (DbCommand cmd in operList)
{
m_Ret = Int32.Parse( cmd.ExecuteScalar().ToString());
}
}
catch (Exception e)
{
m_Ret = 0;
throw e;
}
finally
{
operList.Clear();
conn.Close();
}
return m_Ret;
}
public string ErrorMessage {private set; get; }
public int SqlSave()
{
int m_Ret = 0;
ErrorMessage = "";
DbTransaction dbTran = null;
try
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
if (useTran)
{
dbTran = conn.BeginTransaction();
}
for (int i = 0; i < operList.Count; i++)
{
if (dbTran != null)
{
operList[i].Transaction = dbTran;
}
operList[i].ExecuteNonQuery();
}
if (dbTran != null) dbTran.Commit();
m_Ret = 1;
}
catch (Exception ex)
{
if (dbTran != null) dbTran.Rollback();
ErrorMessage = ex.Message;
m_Ret = 0;
}
finally{
operList.Clear();
conn.Close();
}
return m_Ret;
}
// 扩展提交数据脚本方法，使用带参数的 insert update 或 delete sql 语句，不使用事务。
/// <summary>
/// 执行 insert update 或 delete 脚本并返回受影响的行数，使用 <paramref name="parameters"/> 指定的参数。
/// </summary>
/// <param name="sql">insert update 或 delete 脚本</param>
/// <param name="parameters">数据参数，允许为空或空数组</param>
/// <returns></returns>
public int ExecuteSql(string sql, params System.Data.SqlClient.SqlParameter[] parameters)
{
if (conn.State != ConnectionState.Open) conn.Open();
DbCommand cmd = conn.CreateCommand();
if (parameters != null && parameters.Length > 0)
{
foreach (var p in parameters)
{
cmd.Parameters.Add(p);
}
}
cmd.CommandText = sql;
int result = cmd.ExecuteNonQuery();
conn.Close();
return result;
}
public int ExecuteSqlProc(string sql, params System.Data.SqlClient.SqlParameter[] parameters)
{
if (conn.State != ConnectionState.Open) conn.Open();
DbCommand cmd = conn.CreateCommand();
if (parameters != null && parameters.Length > 0)
{
foreach (var p in parameters)
{
cmd.Parameters.Add(p);
}
}
cmd.CommandText = sql;
cmd.CommandType = CommandType.StoredProcedure;
int result = cmd.ExecuteNonQuery();
conn.Close();
return result;
}
/// <summary>
/// 自增长id 插入 加获取插入id的条件
/// </summary>
/// <param name="rowData"></param>
/// <param name="id_ZiZ">1</param>
public void InsertRow(Hashtable rowData ,string id_ZiZ)
{
DbCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
string sql = "insert into " + _tableName + "(";
string sqlv = " values(";
foreach (string key in _FieldsTypes.Keys)
{
if (rowData.ContainsKey(key))
{
sql += "[" + key + "],";
sqlv += "@" + key + ",";
}
}
sql = sql.Substring(0, sql.Length - 1);
sqlv = sqlv.Substring(0, sqlv.Length - 1);
cmd.CommandText = sql + ")" + sqlv + ")";
if (!string.IsNullOrEmpty(id_ZiZ)&&id_ZiZ=="1")
{
cmd.CommandText += " SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY] ";
}
foreach (string key in _FieldsTypes.Keys)
{
if (rowData.ContainsKey(key))
{
CreateSqlParams(key, rowData[key], (DbType)_FieldsTypes[key.ToLower()], cmd);
}
}
operList.Add(cmd);
}
}
}
