C#下一个SQL SERVER数据库操作类
主要参考了M$的SqlHelper类,在参考了SqlHelper的基础进行修改而成,对一些基本的数据库操作进行了封装.
推荐使用
using (DBHelper db = new DBHelper(Config.ConnStr))
{
}代码如下:
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
namespace Liydotnet.Data.Core
{
public class DBHelper : IDisposable
{
#region private
private bool m_AlreadyDispose = false;
private int m_CommandTimeout = 30;
private string m_ConnStr;
private SqlConnection m_Connection;
private SqlCommand m_Command;
#endregion
#region 属性
///
/// 数据库连接字符串
///
public string ConnStr
{
set { m_ConnStr = value; }
get { return m_ConnStr; }
}
///
/// 执行时间
///
public int CommandTimeout
{
set { m_CommandTimeout = value; }
get { return m_CommandTimeout; }
}
#endregion
#region DBHelper
///
/// 构造函数
///
/// 数据库连接字符串
public DBHelper(string connStr)
{
m_ConnStr = connStr;
Initialization();
}
///
/// 构造函数
///
/// 数据库连接字符串
/// 执行时间
public DBHelper(string connStr, int commandTimeout)
{
m_ConnStr = connStr;
m_CommandTimeout = commandTimeout;
Initialization();
}
///
/// 初始化函数
///
protected void Initialization()
{
try
{
m_Connection = new SqlConnection(m_ConnStr);
if (m_Connection.State == ConnectionState.Closed)
m_Connection.Open();
m_Command = new SqlCommand();
}
catch (Exception ex)
{
throw new Exception(ex.Message.ToString());
}
}
#endregion
#region Dispose
///
/// 析构函数
///
~DBHelper()
{
Dispose(false);
}
///
///释放资源
///
/// 标志
protected virtual void Dispose(bool isDisposing)
{
if (m_AlreadyDispose) return;
if (isDisposing)
{
if (m_Command != null)
{
m_Command.Cancel();
m_Command.Dispose();
}
if (m_Connection != null)
{
try
{
if (m_Connection.State != ConnectionState.Closed)
m_Connection.Close();
m_Connection.Dispose();
}
catch (Exception ex)
{
throw new Exception(ex.ToString());
}
finally
{
m_Connection = null;
}
}
}
m_AlreadyDispose = true;//已经进行的处理
}
///
/// 释放资源
///
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}
#endregion
#region
#endregion
#region ExecuteNonQuery
public int ExecuteNonQuery(string cmdText)
{
try
{
m_Command = new SqlCommand(cmdText, m_Connection);
m_Command.CommandTimeout = m_CommandTimeout;
int iRet = m_Command.ExecuteNonQuery();
return iRet;
}
catch (Exception ex)
{
//Loger.Debug(ex.ToString(),@"C:\sql.txt");
throw new Exception(ex.ToString());
}
finally
{
if (m_Command != null)
m_Command.Dispose();
}
}
public int ExecuteNonQuery(string cmdText, SqlParameter[] para)
{
if (para == null)
{
return ExecuteNonQuery(cmdText);
}
try
{
m_Command = new SqlCommand(cmdText, m_Connection);
m_Command.CommandTimeout = m_CommandTimeout;
for (int i = 0; i < para.Length; i++)
m_Command.Parameters.Add(para);
int iRet = m_Command.ExecuteNonQuery();
return iRet;
}
catch (Exception ex)
{
//Loger.Debug(ex.ToString(), @"C:\sql.txt");
throw new Exception(ex.ToString());
}
finally
{
if (m_Command != null)
m_Command.Dispose();
m_Command.Parameters.Clear();
}
}
public int ExecuteNonQuery(string cmdText, SqlParameter[] para, bool isStoreProdure)
{
if (!isStoreProdure)
{
return ExecuteNonQuery(cmdText, para);
}
try
{
m_Command = new SqlCommand(cmdText, m_Connection);
m_Command.CommandTimeout = m_CommandTimeout;
m_Command.CommandType = CommandType.StoredProcedure;
if (para != null)
{
for (int i = 0; i < para.Length; i++)
m_Command.Parameters.Add(para);
}
int iRet = m_Command.ExecuteNonQuery();
return iRet;
}
catch (Exception ex)
{
//Loger.Debug(ex.ToString(), @"C:\sql.txt");
throw new Exception(ex.ToString());
}
finally
{
if (m_Command != null)
m_Command.Dispose();
m_Command.Parameters.Clear();
}
}
#endregion
#region ExecuteTransaction
public bool ExecuteTransaction(string[] cmdText)
{
SqlTransaction trans = m_Connection.BeginTransaction();
try
{
m_Command = new SqlCommand();
m_Command.Connection = m_Connection;
m_Command.CommandTimeout = m_CommandTimeout;
m_Command.Transaction = trans;
for (int i = 0; i < cmdText.Length; i++)
{
if (cmdText != null && cmdText != string.Empty)
{
m_Command.CommandText = cmdText;
m_Command.ExecuteNonQuery();
}
}
trans.Commit();
return true;
}
catch (Exception ex)
{
trans.Rollback();
throw new Exception(ex.ToString());
}
finally
{
if (m_Command != null)
m_Command.Dispose();
trans.Dispose();
}
}
public bool ExecuteTransaction(string[] cmdText, SqlParameter[] para)
{
if (para == null)
return ExecuteTransaction(cmdText);
SqlTransaction trans = m_Connection.BeginTransaction();
try
{
m_Command = new SqlCommand();
m_Command.Connection = m_Connection;
m_Command.CommandTimeout = m_CommandTimeout;
m_Command.Transaction = trans;
for (int i = 0; i < para.Length; i++)
m_Command.Parameters.Add(para);
for (int i = 0; i < cmdText.Length; i++)
{
if (cmdText != null && cmdText != string.Empty)
{
m_Command.CommandText = cmdText;
m_Command.ExecuteNonQuery();
}
}
trans.Commit();
return true;
}
catch (Exception ex)
{
trans.Rollback();
throw new Exception(ex.ToString());
}
finally
{
if (m_Command != null)
m_Command.Dispose();
trans.Dispose();
}
}
#endregion
#region ExecuteScalar
public object ExecuteScalar(string cmdText)
{
try
{
m_Command = new SqlCommand(cmdText, m_Connection);
m_Command.CommandTimeout = m_CommandTimeout;
object obj = m_Command.ExecuteScalar();
if (object.Equals(obj, null) || object.Equals(obj, DBNull.Value))
{
obj = null;
}
return obj;
}
catch (Exception ex)
{
throw new Exception(ex.ToString());
}
finally
{
if (m_Command != null)
m_Command.Dispose();
}
}
public object ExecuteScalar(string cmdText, SqlParameter[] para)
{
if (para == null)
return ExecuteScalar(cmdText);
try
{
m_Command = new SqlCommand(cmdText, m_Connection);
m_Command.CommandTimeout = m_CommandTimeout;
for (int i = 0; i < para.Length; i++)
m_Command.Parameters.Add(para);
object obj = m_Command.ExecuteScalar();
if (object.Equals(obj, null) || object.Equals(obj, DBNull.Value))
obj = null;
return obj;
}
catch (Exception ex)
{
throw new Exception(ex.ToString());
}
finally
{
if (m_Command != null)
m_Command.Dispose();
m_Command.Parameters.Clear();
}
}
public object ExecuteScalar(string cmdText, SqlParameter[] para, bool isStoreProdure)
{
if (!isStoreProdure)
return ExecuteScalar(cmdText, para);
try
{
m_Command = new SqlCommand(cmdText, m_Connection);
m_Command.CommandTimeout = m_CommandTimeout;
m_Command.CommandType = CommandType.StoredProcedure;
if (para != null)
for (int i = 0; i < para.Length; i++)
m_Command.Parameters.Add(para);
object obj = m_Command.ExecuteScalar();
if (object.Equals(obj, null) || object.Equals(obj, DBNull.Value))
obj = null;
return obj;
}
catch (Exception ex)
{
throw new Exception(ex.ToString());
}
finally
{
if (m_Command != null)
{
m_Command.Dispose();
}
m_Command.Parameters.Clear();
}
}
#endregion
#region ExecuteDataTable
public DataTable ExecuteDataTable(string tableName, string cmdText)
{
try
{
DataTable myTable = new DataTable(tableName);
m_Command = new SqlCommand(cmdText, m_Connection);
m_Command.CommandTimeout = m_CommandTimeout;
SqlDataAdapter da = new SqlDataAdapter(m_Command);
da.Fill(myTable);
da.Dispose();
return myTable;
}
catch (Exception ex)
{
throw new Exception(ex.ToString());
}
finally
{
if (m_Command != null)
m_Command.Dispose();
}
}
public DataTable ExecuteDataTable(string tableName, string cmdText, SqlParameter[] para)
{
if (para == null)
return ExecuteDataTable(tableName, cmdText);
try
{
DataTable myTable = new DataTable(tableName);
m_Command = new SqlCommand(cmdText, m_Connection);
m_Command.CommandTimeout = m_CommandTimeout;
for (int i = 0; i < para.Length; i++)
m_Command.Parameters.Add(para);
SqlDataAdapter da = new SqlDataAdapter(m_Command);
da.Fill(myTable);
da.Dispose();
return myTable;
}
catch (Exception ex)
{
//Loger.Debug(ex.ToString(), @"C:\sql.txt");
throw new Exception(ex.ToString());
}
finally
{
if (m_Command != null)
m_Command.Dispose();
m_Command.Parameters.Clear();
}
}
public DataTable ExecuteDataTable(string tableName, string cmdText, SqlParameter[] para, bool isStoreProdure)
{
if (!isStoreProdure)
{
return ExecuteDataTable(tableName, cmdText, para);
}
try
{
DataTable myTable = new DataTable(tableName);
m_Command = new SqlCommand(cmdText, m_Connection);
m_Command.CommandTimeout = m_CommandTimeout;
m_Command.CommandType = CommandType.StoredProcedure;
if (para != null)
for (int i = 0; i < para.Length; i++)
m_Command.Parameters.Add(para);
SqlDataAdapter da = new SqlDataAdapter(m_Command);
da.Fill(myTable);
da.Dispose();
return myTable;
}
catch (Exception ex)
{
throw new Exception(ex.ToString());
}
finally
{
if (m_Command != null)
m_Command.Dispose();
m_Command.Parameters.Clear();
}
}
#endregion
#region ExecuteDataReader
public SqlDataReader ExecuteDataReader(string cmdText)
{
try
{
m_Command = new SqlCommand(cmdText, m_Connection);
m_Command.CommandTimeout = m_CommandTimeout;
SqlDataReader reader = m_Command.ExecuteReader();
return reader;
}
catch (Exception ex)
{
throw new Exception(ex.ToString());
}
finally
{
if (m_Command != null)
m_Command.Dispose();
}
}
public SqlDataReader ExecuteDataReader(string cmdText, SqlParameter[] para)
{
if (para == null)
{
return ExecuteDataReader(cmdText);
}
try
{
m_Command = new SqlCommand(cmdText, m_Connection);
m_Command.CommandTimeout = m_CommandTimeout;
for (int i = 0; i < para.Length; i++)
m_Command.Parameters.Add(para);
SqlDataReader reader = m_Command.ExecuteReader();
return reader;
}
catch (Exception ex)
{
throw new Exception(ex.ToString());
}
finally
{
if (m_Command != null)
m_Command.Dispose();
m_Command.Parameters.Clear();
}
}
public SqlDataReader ExecuteDataReader(string cmdText, SqlParameter[] para, bool isStoreProdure)
{
if (!isStoreProdure)
{
return ExecuteDataReader(cmdText, para);
}
try
{
m_Command = new SqlCommand(cmdText, m_Connection);
m_Command.CommandTimeout = m_CommandTimeout;
m_Command.CommandType = CommandType.StoredProcedure;
if (para != null)
for (int i = 0; i < para.Length; i++)
m_Command.Parameters.Add(para);
SqlDataReader reader = m_Command.ExecuteReader();
return reader;
}
catch (Exception ex)
{
throw new Exception(ex.ToString());
}
finally
{
if (m_Command != null)
m_Command.Dispose();
m_Command.Parameters.Clear();
}
}
#endregion
#regionStatic
public static SqlParameter MakeInParam(string paraName, SqlDbType paraType, object value)
{
SqlParameter para = new SqlParameter(paraName, paraType);
if (Object.Equals(value, null) || Object.Equals(value, DBNull.Value) || value.ToString().Trim() == string.Empty)
para.Value = DBNull.Value;
else
para.Value = value;
return para;
}
public static SqlParameter MakeInParam(string paraName, SqlDbType paraType, int len, object value)
{
SqlParameter para = new SqlParameter(paraName, paraType, len);
if (Object.Equals(value, null) || Object.Equals(value, DBNull.Value) || value.ToString().Trim() == string.Empty)
para.Value = DBNull.Value;
else
para.Value = value;
return para;
}
#endregion
}
}
页:
[1]