/// <summary>
/// 批量存储数据
/// </summary>
/// <param name="tabName"></param>
/// <param name="dataTable"></param>
/// <param name="rowsCounts"> </param>
public void SQLBulkCopy(string tabName, DataTable dataTable, int rowsCounts)
{
if (string.IsNullOrEmpty(tabName))
{
throw new Exception("表明不能为空！");
}
if (dataTable != null && dataTable.Rows.Count > 0)
{
lock (this)
{
try
{
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(MSqlConn))
{
MSqlConn.Open();
bulkCopy.DestinationTableName = tabName;
bulkCopy.NotifyAfter = rowsCounts;
foreach (DataColumn tempColumn in dataTable.Columns)
{
bulkCopy.ColumnMappings.Add(tempColumn.ColumnName, tempColumn.ColumnName);
}
bulkCopy.WriteToServer(dataTable);
bulkCopy.Close();
}
}
finally
{
if (MSqlConn.State == ConnectionState.Open)
{
MSqlConn.Close();
}
}
}
}
}
/// <summary>
/// 单字段的查询结果
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public object ExtScalarSql(String sql)
{
if (String.IsNullOrEmpty(sql))
{
throw new Exception("Sql结构化查询语句不能为空！");
}
lock (this)
{
try
{
MSqlConn.Open();
_mCommand.CommandType = CommandType.Text;
_mCommand.CommandText = sql;
object retValue = _mCommand.ExecuteScalar();
return(retValue);
}
finally
{
if (MSqlConn.State == ConnectionState.Open)
{
MSqlConn.Close();
}
}
}
}
/// <summary>
/// 单字段的查询结果带加密参数
/// </summary>
/// <param name="sql"></param>
/// <param name="parameter"></param>
/// <returns></returns>
public object ExtScalarSql(String sql, object[] parameter)
{
SqlParameter[] sqlParameter = parameter as SqlParameter[];
lock (this)
{
try
{
MSqlConn.Open();
_mCommand.CommandType = CommandType.Text;
_mCommand.CommandText = sql;
_mCommand.Parameters.Clear();
if (sqlParameter != null)
{
foreach (SqlParameter temp in sqlParameter)
{
_mCommand.Parameters.Add(temp);
}
}
object retValue = _mCommand.ExecuteScalar();
return(retValue);
}
finally
{
if (MSqlConn.State == ConnectionState.Open)
{
MSqlConn.Close();
}
}
}
}
/// <summary>
/// 执行查询语句返回受影响的行数
/// </summary>
/// <param name="sql"></param>
/// <param name="parameter"></param>
/// <returns></returns>
public int IntExtSql(String sql, object[] parameter)
{
SqlParameter[] sqlParameter = parameter as SqlParameter[];
if (String.IsNullOrEmpty(sql))
{
throw new Exception("Sql结构化查询语句不能为空！");
}
lock (this)
{
try
{
MSqlConn.Open();
_mCommand.CommandType = CommandType.Text;
_mCommand.CommandText = sql;
_mCommand.Parameters.Clear();
if (sqlParameter != null)
{
foreach (SqlParameter temp in sqlParameter)
{
_mCommand.Parameters.Add(temp);
}
}
int retValue = _mCommand.ExecuteNonQuery();
return(retValue);
}
finally
{
if (MSqlConn.State == ConnectionState.Open)
{
MSqlConn.Close();
}
}
}
}
/// <summary>
/// 执行存储过程，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
/// </summary>
/// <param name="procName">存储过程名</param>
/// <param name="parameter">存储过程参数</param>
/// <returns>SqlDataReader</returns>
public SqlDataReader RunProcedure(string procName, IDataParameter[] parameter)
{
SqlParameter[] sqlParameter = parameter as SqlParameter[];
if (string.IsNullOrEmpty(procName))
{
throw new Exception("存储过程名称不能为空！");
}
lock (this)
{
try
{
_mCommand.CommandType = CommandType.StoredProcedure;
_mCommand.CommandText = procName;
_mCommand.Parameters.Clear();
if (sqlParameter != null)
{
foreach (SqlParameter temp in sqlParameter)
{
_mCommand.Parameters.Add(temp);
}
}
MSqlConn.Open();
return(_mCommand.ExecuteReader(CommandBehavior.CloseConnection));
}
finally
{
if (sqlParameter != null)
{
Array.Clear(sqlParameter, 0, sqlParameter.Length);
}
}
}
}
/// <summary>
/// 还原对象，对操作后的对象进行必要清理
/// </summary>
private void Restor()
{
if (MSqlConn.State == ConnectionState.Open)
{
MSqlConn.Close();
}
if (_mCommand != null && _mCommand.Parameters.Count > 0)
{
_mCommand.Parameters.Clear();
}
}
/// <summary>
/// 执行存储过程返回键值对对象
/// </summary>
/// <param name="procName">存储过程名</param>
/// <param name="parameter">调用存储的参数</param>
/// <param name="retFiled">要返回字段名</param>
/// <returns></returns>
public Dictionary <string, object> ExtProc(string procName, object[] parameter, string[] retFiled)
{
SqlParameter[] sqlParameter = parameter as SqlParameter[];
Dictionary <string, object> retDictionary = null;
if (string.IsNullOrEmpty(procName))
{
throw new Exception("存储名字不能为空！");
}
lock (this)
{
try
{
MSqlConn.Open();
_mCommand.CommandType = CommandType.StoredProcedure;
_mCommand.CommandText = procName;
_mCommand.Parameters.Clear();
if (sqlParameter != null)
{
foreach (SqlParameter temp in sqlParameter)
{
_mCommand.Parameters.Add(temp);
}
}
_mCommand.ExecuteNonQuery();
MSqlConn.Close();
if (retFiled != null)
{
retDictionary = new Dictionary <string, object>();
foreach (string key in retFiled)
{
retDictionary.Add(key, _mCommand.Parameters[key].Value);
}
}
return(retDictionary);
}
finally
{
Restor();
if (sqlParameter != null)
{
Array.Clear(sqlParameter, 0, sqlParameter.Length);
}
}
}
}
/// <summary>
/// 执行存储过程,取得返回结果
/// </summary>
/// <param name="ProName">存储过程名称</param>
/// <param name="parameter">存储过程参数</param>
/// <param name="ResultName">返回值保存的参数名称,不需要带@</param>
/// <returns></returns>
public int ResultValueExtProc(string ProName, object[] parameter, string ResultName)
{
SqlParameter[] sqlParameter = parameter as SqlParameter[];
if (string.IsNullOrEmpty(ProName))
{
throw new Exception("存储过程名称不能为空！");
}
lock (this)
{
try
{
MSqlConn.Open();
_mCommand.CommandType = CommandType.StoredProcedure;
_mCommand.CommandText = ProName;
_mCommand.Parameters.Clear();
if (sqlParameter != null)
{
foreach (SqlParameter temp in sqlParameter)
{
_mCommand.Parameters.Add(temp);
}
}
_mCommand.ExecuteNonQuery();
int retvalue = Convert.ToInt32(_mCommand.Parameters["@" + ResultName].Value);
MSqlConn.Close();
return(retvalue);
}
finally
{
Restor();
if (sqlParameter != null)
{
Array.Clear(sqlParameter, 0, sqlParameter.Length);
}
}
}
}