SqlServerSlowXDataBaseHelper SqlServer 数据库操作相关方法
2015-09-04 13:21:12 访问(1643) 赞(0) 踩(0)
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
namespace SlowX.DataBaseHelper
{
/// <summary>
/// SqlServer 数据库操作相关方法
/// </summary>
public class SqlServerSlowXDataBaseHelper
{
/// <summary>
/// 数据库链接串
/// </summary>
public readonly static string dataBaseConnectionString
=
System.Configuration.ConfigurationManager.AppSettings["SlowX.DataBaseHelper.dataBaseConnectionString"];
/// <summary>
/// 查询的表格名
/// </summary>
public const string srcTableName = "SelectDataTable";
#region 获得数据集 ExecuteDataSet
/// <summary>
/// 获得数据集
/// </summary>
/// <param name="_SQL"></param>
/// <returns></returns>
public static DataSet ExecuteDataSet
(
string _SQL
)
{
SqlConnection cn = null;
SqlCommand cmd = null;
SqlDataAdapter da = null;
DataSet theResult = null;
try
{
cn = new SqlConnection(dataBaseConnectionString);
cn.Open();
cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
da = new SqlDataAdapter(cmd);
theResult = new DataSet();
da.Fill(theResult, srcTableName);
}
catch (Exception err)
{
throw err;
}
finally
{
if (da != null)
{
da.Dispose();
da = null;
}
if (cmd != null)
{
cmd.Parameters.Clear();
cmd.Dispose();
cmd = null;
}
if (cn != null)
{
if (cn.State != ConnectionState.Closed)
cn.Close();
cn.Dispose();
cn = null;
}
}
return theResult;
}
/// <summary>
/// 获得数据集
/// </summary>
/// <param name="_SQL"></param>
/// <param name="theList"></param>
/// <returns></returns>
public static DataSet ExecuteDataSet
(
string _SQL,
List<SqlParameter> theList
)
{
SqlConnection cn = null;
SqlCommand cmd = null;
SqlDataAdapter da = null;
DataSet theResult = null;
try
{
cn = new SqlConnection(dataBaseConnectionString);
cn.Open();
cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
if (theList != null)
{
foreach (SqlParameter p in theList)
{
if (p == null)
continue;
cmd.Parameters.Add(p);
}
}
da = new SqlDataAdapter(cmd);
theResult = new DataSet();
da.Fill(theResult, srcTableName);
}
catch (Exception err)
{
throw err;
}
finally
{
if (da != null)
{
da.Dispose();
da = null;
}
if (cmd != null)
{
cmd.Parameters.Clear();
cmd.Dispose();
cmd = null;
}
if (cn != null)
{
if (cn.State != ConnectionState.Closed)
cn.Close();
cn.Dispose();
cn = null;
}
}
return theResult;
}
/// <summary>
/// 获得数据集
/// </summary>
/// <param name="_dbConnectionString"></param>
/// <param name="_SQL"></param>
/// <returns></returns>
public static DataSet ExecuteDataSet
(
string _dbConnectionString,
string _SQL
)
{
SqlConnection cn = null;
SqlCommand cmd = null;
SqlDataAdapter da = null;
DataSet theResult = null;
try
{
cn = new SqlConnection(_dbConnectionString);
cn.Open();
cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
da = new SqlDataAdapter(cmd);
theResult = new DataSet();
da.Fill(theResult, srcTableName);
}
catch (Exception err)
{
throw err;
}
finally
{
if (da != null)
{
da.Dispose();
da = null;
}
if (cmd != null)
{
cmd.Parameters.Clear();
cmd.Dispose();
cmd = null;
}
if (cn != null)
{
if (cn.State != ConnectionState.Closed)
cn.Close();
cn.Dispose();
cn = null;
}
}
return theResult;
}
/// <summary>
/// 获得数据集
/// </summary>
/// <param name="_dbConnectionString"></param>
/// <param name="_SQL"></param>
/// <param name="theList"></param>
/// <returns></returns>
public static DataSet ExecuteDataSet
(
string _dbConnectionString,
string _SQL,
List<SqlParameter> theList
)
{
SqlConnection cn = null;
SqlCommand cmd = null;
SqlDataAdapter da = null;
DataSet theResult = null;
try
{
cn = new SqlConnection(_dbConnectionString);
cn.Open();
cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
if (theList != null)
{
foreach (SqlParameter p in theList)
{
if (p == null)
continue;
cmd.Parameters.Add(p);
}
}
da = new SqlDataAdapter(cmd);
theResult = new DataSet();
da.Fill(theResult, srcTableName);
}
catch (Exception err)
{
throw err;
}
finally
{
if (da != null)
{
da.Dispose();
da = null;
}
if (cmd != null)
{
cmd.Parameters.Clear();
cmd.Dispose();
cmd = null;
}
if (cn != null)
{
if (cn.State != ConnectionState.Closed)
cn.Close();
cn.Dispose();
cn = null;
}
}
return theResult;
}
/// <summary>
/// 获得数据集
/// </summary>
/// <param name="cn"></param>
/// <param name="_SQL"></param>
/// <returns></returns>
public static DataSet ExecuteDataSet
(
SqlConnection cn,
string _SQL
)
{
if (cn == null)
throw new Exception("SqlConnection cn为null。");
bool needOpen = false;
SqlCommand cmd = null;
SqlDataAdapter da = null;
DataSet theResult = null;
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
needOpen = true;
}
cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
da = new SqlDataAdapter(cmd);
theResult = new DataSet();
da.Fill(theResult, srcTableName);
}
catch (Exception err)
{
throw err;
}
finally
{
if (da != null)
{
da.Dispose();
da = null;
}
if (cmd != null)
{
cmd.Parameters.Clear();
cmd.Dispose();
cmd = null;
}
if (needOpen)
{
if (cn != null)
{
if (cn.State != ConnectionState.Closed)
cn.Close();
}
}
}
return theResult;
}
/// <summary>
/// 获得数据集
/// </summary>
/// <param name="cn"></param>
/// <param name="_SQL"></param>
/// <param name="theList"></param>
/// <returns></returns>
public static DataSet ExecuteDataSet
(
SqlConnection cn,
string _SQL,
List<SqlParameter> theList
)
{
if (cn == null)
throw new Exception("SqlConnection cn为null。");
bool needOpen = false;
SqlCommand cmd = null;
SqlDataAdapter da = null;
DataSet theResult = null;
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
needOpen = true;
}
cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
if (theList != null)
{
foreach (SqlParameter p in theList)
{
if (p == null)
continue;
cmd.Parameters.Add(p);
}
}
da = new SqlDataAdapter(cmd);
theResult = new DataSet();
da.Fill(theResult, srcTableName);
}
catch (Exception err)
{
throw err;
}
finally
{
if (da != null)
{
da.Dispose();
da = null;
}
if (cmd != null)
{
cmd.Parameters.Clear();
cmd.Dispose();
cmd = null;
}
if (needOpen)
{
if (cn != null)
{
if (cn.State != ConnectionState.Closed)
cn.Close();
}
}
}
return theResult;
}
/// <summary>
/// 获得数据集
/// </summary>
/// <param name="tran"></param>
/// <param name="_SQL"></param>
/// <returns></returns>
public static DataSet ExecuteDataSet
(
SqlTransaction tran,
string _SQL
)
{
if (tran == null)
throw new Exception("SqlTransaction tran为null。");
SqlConnection cn = tran.Connection;
if (cn == null)
throw new Exception("SqlConnection cn为null。");
bool needOpen = false;
SqlCommand cmd = null;
SqlDataAdapter da = null;
DataSet theResult = null;
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
needOpen = true;
}
cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
da = new SqlDataAdapter(cmd);
theResult = new DataSet();
da.Fill(theResult, srcTableName);
}
catch (Exception err)
{
throw err;
}
finally
{
if (da != null)
{
da.Dispose();
da = null;
}
if (cmd != null)
{
cmd.Parameters.Clear();
cmd.Dispose();
cmd = null;
}
if (needOpen)
{
if (cn != null)
{
if (cn.State != ConnectionState.Closed)
cn.Close();
}
}
}
return theResult;
}
/// <summary>
/// 获得数据集
/// </summary>
/// <param name="tran"></param>
/// <param name="_SQL"></param>
/// <param name="theList"></param>
/// <returns></returns>
public static DataSet ExecuteDataSet
(
SqlTransaction tran,
string _SQL,
List<SqlParameter> theList
)
{
if (tran == null)
throw new Exception("SqlTransaction tran为null。");
SqlConnection cn = tran.Connection;
if (cn == null)
throw new Exception("SqlConnection cn为null。");
bool needOpen = false;
SqlCommand cmd = null;
SqlDataAdapter da = null;
DataSet theResult = null;
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
needOpen = true;
}
cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
if (theList != null)
{
foreach (SqlParameter p in theList)
{
if (p == null)
continue;
cmd.Parameters.Add(p);
}
}
da = new SqlDataAdapter(cmd);
theResult = new DataSet();
da.Fill(theResult, srcTableName);
}
catch (Exception err)
{
throw err;
}
finally
{
if (da != null)
{
da.Dispose();
da = null;
}
if (cmd != null)
{
cmd.Parameters.Clear();
cmd.Dispose();
cmd = null;
}
if (needOpen)
{
if (cn != null)
{
if (cn.State != ConnectionState.Closed)
cn.Close();
}
}
}
return theResult;
}
#endregion 获得数据集 ExecuteDataSet
#region 执行SQL语句，返回影响记录数
/// <summary>
/// 执行SQL语句，返回影响记录数
/// </summary>
/// <param name="_SQL"></param>
/// <returns></returns>
public static int ExecuteNonQuery
(
string _SQL
)
{
SqlConnection cn = null;
SqlCommand cmd = null;
int theResult = -1;
try
{
cn = new SqlConnection(dataBaseConnectionString);
cn.Open();
cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
theResult = cmd.ExecuteNonQuery();
}
catch (Exception err)
{
throw err;
}
finally
{
if (cmd != null)
{
cmd.Parameters.Clear();
cmd.Dispose();
cmd = null;
}
if (cn != null)
{
if (cn.State != ConnectionState.Closed)
cn.Close();
cn.Dispose();
cn = null;
}
}
return theResult;
}
/// <summary>
/// 执行SQL语句，返回影响记录数
/// </summary>
/// <param name="_SQL"></param>
/// <param name="theList"></param>
/// <returns></returns>
public static int ExecuteNonQuery
(
string _SQL,
List<SqlParameter> theList
)
{
SqlConnection cn = null;
SqlCommand cmd = null;
int theResult = -1;
try
{
cn = new SqlConnection(dataBaseConnectionString);
cn.Open();
cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
if (theList != null)
{
foreach (SqlParameter p in theList)
{
if (p == null)
continue;
cmd.Parameters.Add(p);
}
}
theResult = cmd.ExecuteNonQuery();
}
catch (Exception err)
{
throw err;
}
finally
{
if (cmd != null)
{
cmd.Parameters.Clear();
cmd.Dispose();
cmd = null;
}
if (cn != null)
{
if (cn.State != ConnectionState.Closed)
cn.Close();
cn.Dispose();
cn = null;
}
}
return theResult;
}
/// <summary>
/// 执行SQL语句，返回影响记录数
/// </summary>
/// <param name="_dbConnectionString"></param>
/// <param name="_SQL"></param>
/// <returns></returns>
public static int ExecuteNonQuery
(
string _dbConnectionString,
string _SQL
)
{
SqlConnection cn = null;
SqlCommand cmd = null;
int theResult = -1;
try
{
cn = new SqlConnection(_dbConnectionString);
cn.Open();
cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
theResult = cmd.ExecuteNonQuery();
}
catch (Exception err)
{
throw err;
}
finally
{
if (cmd != null)
{
cmd.Parameters.Clear();
cmd.Dispose();
cmd = null;
}
if (cn != null)
{
if (cn.State != ConnectionState.Closed)
cn.Close();
cn.Dispose();
cn = null;
}
}
return theResult;
}
/// <summary>
/// 执行SQL语句，返回影响记录数
/// </summary>
/// <param name="_dbConnectionString"></param>
/// <param name="_SQL"></param>
/// <param name="theList"></param>
/// <returns></returns>
public static int ExecuteNonQuery
(
string _dbConnectionString,
string _SQL,
List<SqlParameter> theList
)
{
SqlConnection cn = null;
SqlCommand cmd = null;
int theResult = -1;
try
{
cn = new SqlConnection(_dbConnectionString);
cn.Open();
cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
if (theList != null)
{
foreach (SqlParameter p in theList)
{
if (p == null)
continue;
cmd.Parameters.Add(p);
}
}
theResult = cmd.ExecuteNonQuery();
}
catch (Exception err)
{
throw err;
}
finally
{
if (cmd != null)
{
cmd.Parameters.Clear();
cmd.Dispose();
cmd = null;
}
if (cn != null)
{
if (cn.State != ConnectionState.Closed)
cn.Close();
cn.Dispose();
cn = null;
}
}
return theResult;
}
/// <summary>
/// 执行SQL语句，返回影响记录数
/// </summary>
/// <param name="cn"></param>
/// <param name="_SQL"></param>
/// <returns></returns>
public static int ExecuteNonQuery
(
SqlConnection cn,
string _SQL
)
{
if (cn == null)
throw new Exception("SqlConnection cn为null。");
bool needOpen = false;
SqlCommand cmd = null;
int theResult = -1;
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
needOpen = true;
}
cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
theResult = cmd.ExecuteNonQuery();
}
catch (Exception err)
{
throw err;
}
finally
{
if (cmd != null)
{
cmd.Parameters.Clear();
cmd.Dispose();
cmd = null;
}
if (needOpen)
{
if (cn != null)
{
if (cn.State != ConnectionState.Closed)
cn.Close();
}
}
}
return theResult;
}
/// <summary>
/// 执行SQL语句，返回影响记录数
/// </summary>
/// <param name="cn"></param>
/// <param name="_SQL"></param>
/// <param name="theList"></param>
/// <returns></returns>
public static int ExecuteNonQuery
(
SqlConnection cn,
string _SQL,
List<SqlParameter> theList
)
{
if (cn == null)
throw new Exception("SqlConnection cn为null。");
bool needOpen = false;
SqlCommand cmd = null;
int theResult = -1;
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
needOpen = true;
}
cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
if (theList != null)
{
foreach (SqlParameter p in theList)
{
if (p == null)
continue;
cmd.Parameters.Add(p);
}
}
theResult = cmd.ExecuteNonQuery();
}
catch (Exception err)
{
throw err;
}
finally
{
if (cmd != null)
{
cmd.Parameters.Clear();
cmd.Dispose();
cmd = null;
}
if (needOpen)
{
if (cn != null)
{
if (cn.State != ConnectionState.Closed)
cn.Close();
}
}
}
return theResult;
}
/// <summary>
/// 执行SQL语句，返回影响记录数
/// </summary>
/// <param name="tran"></param>
/// <param name="_SQL"></param>
/// <returns></returns>
public static int ExecuteNonQuery
(
SqlTransaction tran,
string _SQL
)
{
if (tran == null)
throw new Exception("SqlTransaction tran为null。");
SqlConnection cn = tran.Connection;
if (cn == null)
throw new Exception("SqlConnection cn为null。");
bool needOpen = false;
SqlCommand cmd = null;
int theResult = -1;
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
needOpen = true;
}
cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
theResult = cmd.ExecuteNonQuery();
}
catch (Exception err)
{
throw err;
}
finally
{
if (cmd != null)
{
cmd.Parameters.Clear();
cmd.Dispose();
cmd = null;
}
if (needOpen)
{
if (cn != null)
{
if (cn.State != ConnectionState.Closed)
cn.Close();
}
}
}
return theResult;
}
/// <summary>
/// 执行SQL语句，返回影响记录数
/// </summary>
/// <param name="tran"></param>
/// <param name="_SQL"></param>
/// <param name="theList"></param>
/// <returns></returns>
public static int ExecuteNonQuery
(
SqlTransaction tran,
string _SQL,
List<SqlParameter> theList
)
{
if (tran == null)
throw new Exception("SqlTransaction tran为null。");
SqlConnection cn = tran.Connection;
if (cn == null)
throw new Exception("SqlConnection cn为null。");
bool needOpen = false;
SqlCommand cmd = null;
int theResult = -1;
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
needOpen = true;
}
cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
if (theList != null)
{
foreach (SqlParameter p in theList)
{
if (p == null)
continue;
cmd.Parameters.Add(p);
}
}
theResult = cmd.ExecuteNonQuery();
}
catch (Exception err)
{
throw err;
}
finally
{
if (cmd != null)
{
cmd.Parameters.Clear();
cmd.Dispose();
cmd = null;
}
if (needOpen)
{
if (cn != null)
{
if (cn.State != ConnectionState.Closed)
cn.Close();
}
}
}
return theResult;
}
#endregion 执行SQL语句，返回影响记录数
#region 执行SQL语句，获得一个记录
/// <summary>
/// 执行SQL语句，获得一个记录
/// </summary>
/// <param name="_SQL"></param>
/// <returns></returns>
public static object ExecuteScalar
(
string _SQL
)
{
SqlConnection cn = null;
SqlCommand cmd = null;
object theResult = null;
try
{
cn = new SqlConnection(dataBaseConnectionString);
cn.Open();
cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
theResult = cmd.ExecuteScalar();
}
catch (Exception err)
{
throw err;
}
finally
{
if (cmd != null)
{
cmd.Parameters.Clear();
cmd.Dispose();
cmd = null;
}
if (cn != null)
{
if (cn.State != ConnectionState.Closed)
cn.Close();
cn.Dispose();
cn = null;
}
}
return theResult;
}
/// <summary>
/// 执行SQL语句，获得一个记录
/// </summary>
/// <param name="_SQL"></param>
/// <param name="theList"></param>
/// <returns></returns>
public static object ExecuteScalar
(
string _SQL,
List<SqlParameter> theList
)
{
SqlConnection cn = null;
SqlCommand cmd = null;
object theResult = null;
try
{
cn = new SqlConnection(dataBaseConnectionString);
cn.Open();
cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
if (theList != null)
{
foreach (SqlParameter p in theList)
{
if (p == null)
continue;
cmd.Parameters.Add(p);
}
}
theResult = cmd.ExecuteScalar();
}
catch (Exception err)
{
throw err;
}
finally
{
if (cmd != null)
{
cmd.Parameters.Clear();
cmd.Dispose();
cmd = null;
}
if (cn != null)
{
if (cn.State != ConnectionState.Closed)
cn.Close();
cn.Dispose();
cn = null;
}
}
return theResult;
}
/// <summary>
/// 执行SQL语句，获得一个记录
/// </summary>
/// <param name="_dbConnectionString"></param>
/// <param name="_SQL"></param>
/// <returns></returns>
public static object ExecuteScalar
(
string _dbConnectionString,
string _SQL
)
{
SqlConnection cn = null;
SqlCommand cmd = null;
object theResult = null;
try
{
cn = new SqlConnection(_dbConnectionString);
cn.Open();
cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
theResult = cmd.ExecuteScalar();
}
catch (Exception err)
{
throw err;
}
finally
{
if (cmd != null)
{
cmd.Parameters.Clear();
cmd.Dispose();
cmd = null;
}
if (cn != null)
{
if (cn.State != ConnectionState.Closed)
cn.Close();
cn.Dispose();
cn = null;
}
}
return theResult;
}
/// <summary>
/// 执行SQL语句，获得一个记录
/// </summary>
/// <param name="_dbConnectionString"></param>
/// <param name="_SQL"></param>
/// <param name="theList"></param>
/// <returns></returns>
public static object ExecuteScalar
(
string _dbConnectionString,
string _SQL,
List<SqlParameter> theList
)
{
SqlConnection cn = null;
SqlCommand cmd = null;
object theResult = null;
try
{
cn = new SqlConnection(_dbConnectionString);
cn.Open();
cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
if (theList != null)
{
foreach (SqlParameter p in theList)
{
if (p == null)
continue;
cmd.Parameters.Add(p);
}
}
theResult = cmd.ExecuteScalar();
}
catch (Exception err)
{
throw err;
}
finally
{
if (cmd != null)
{
cmd.Parameters.Clear();
cmd.Dispose();
cmd = null;
}
if (cn != null)
{
if (cn.State != ConnectionState.Closed)
cn.Close();
cn.Dispose();
cn = null;
}
}
return theResult;
}
/// <summary>
/// 执行SQL语句，获得一个记录
/// </summary>
/// <param name="cn"></param>
/// <param name="_SQL"></param>
/// <returns></returns>
public static object ExecuteScalar
(
SqlConnection cn,
string _SQL
)
{
if (cn == null)
throw new Exception("SqlConnection cn为null。");
bool needOpen = false;
SqlCommand cmd = null;
object theResult = null;
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
needOpen = true;
}
cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
theResult = cmd.ExecuteScalar();
}
catch (Exception err)
{
throw err;
}
finally
{
if (cmd != null)
{
cmd.Parameters.Clear();
cmd.Dispose();
cmd = null;
}
if (needOpen)
{
if (cn != null)
{
if (cn.State != ConnectionState.Closed)
cn.Close();
}
}
}
return theResult;
}
/// <summary>
/// 执行SQL语句，获得一个记录
/// </summary>
/// <param name="cn"></param>
/// <param name="_SQL"></param>
/// <param name="theList"></param>
/// <returns></returns>
public static object ExecuteScalar
(
SqlConnection cn,
string _SQL,
List<SqlParameter> theList
)
{
if (cn == null)
throw new Exception("SqlConnection cn为null。");
bool needOpen = false;
SqlCommand cmd = null;
object theResult = null;
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
needOpen = true;
}
cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
if (theList != null)
{
foreach (SqlParameter p in theList)
{
if (p == null)
continue;
cmd.Parameters.Add(p);
}
}
theResult = cmd.ExecuteScalar();
}
catch (Exception err)
{
throw err;
}
finally
{
if (cmd != null)
{
cmd.Parameters.Clear();
cmd.Dispose();
cmd = null;
}
if (needOpen)
{
if (cn != null)
{
if (cn.State != ConnectionState.Closed)
cn.Close();
}
}
}
return theResult;
}
/// <summary>
/// 执行SQL语句，获得一个记录
/// </summary>
/// <param name="tran"></param>
/// <param name="_SQL"></param>
/// <returns></returns>
public static object ExecuteScalar
(
SqlTransaction tran,
string _SQL
)
{
if (tran == null)
throw new Exception("SqlTransaction tran为null。");
SqlConnection cn = tran.Connection;
if (cn == null)
throw new Exception("SqlConnection cn为null。");
bool needOpen = false;
SqlCommand cmd = null;
object theResult = null;
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
needOpen = true;
}
cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
theResult = cmd.ExecuteScalar();
}
catch (Exception err)
{
throw err;
}
finally
{
if (cmd != null)
{
cmd.Parameters.Clear();
cmd.Dispose();
cmd = null;
}
if (needOpen)
{
if (cn != null)
{
if (cn.State != ConnectionState.Closed)
cn.Close();
}
}
}
return theResult;
}
/// <summary>
/// 执行SQL语句，获得一个记录
/// </summary>
/// <param name="tran"></param>
/// <param name="_SQL"></param>
/// <param name="theList"></param>
/// <returns></returns>
public static object ExecuteScalar
(
SqlTransaction tran,
string _SQL,
List<SqlParameter> theList
)
{
if (tran == null)
throw new Exception("SqlTransaction tran为null。");
SqlConnection cn = tran.Connection;
if (cn == null)
throw new Exception("SqlConnection cn为null。");
bool needOpen = false;
SqlCommand cmd = null;
object theResult = null;
try
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
needOpen = true;
}
cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = _SQL;
cmd.CommandType = CommandType.Text;
if (theList != null)
{
foreach (SqlParameter p in theList)
{
if (p == null)
continue;
cmd.Parameters.Add(p);
}
}
theResult = cmd.ExecuteScalar();
}
catch (Exception err)
{
throw err;
}
finally
{
if (cmd != null)
{
cmd.Parameters.Clear();
cmd.Dispose();
cmd = null;
}
if (needOpen)
{
if (cn != null)
{
if (cn.State != ConnectionState.Closed)
cn.Close();
}
}
}
return theResult;
}
#endregion 执行SQL语句，获得一个记录
#region 列出表所有记录
/// <summary>
///
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
public static DataSet GetDataSetByTableName(string tableName)
{
SqlConnection cn = null;
SqlCommand cmd = null;
SqlDataAdapter da = null;
DataSet theResult = null;
try
{
cn = new SqlConnection(dataBaseConnectionString);
cn.Open();
cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = "select * from " + tableName;
cmd.CommandType = CommandType.Text;
da = new SqlDataAdapter(cmd);
theResult = new DataSet();
da.Fill(theResult, srcTableName);
}
catch (Exception err)
{
throw err;
}
finally
{
if (da != null)
{
da.Dispose();
da = null;
}
if (cmd != null)
{
cmd.Parameters.Clear();
cmd.Dispose();
cmd = null;
}
if (cn != null)
{
if (cn.State != ConnectionState.Closed)
cn.Close();
cn.Dispose();
cn = null;
}
}
return theResult;
}
/// <summary>
///
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
public static int CountByTableName(string tableName)
{
SqlConnection cn = null;
SqlCommand cmd = null;
SqlDataAdapter da = null;
int theResult = 0;
try
{
cn = new SqlConnection(dataBaseConnectionString);
cn.Open();
cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = "select count(1) from " + tableName;
cmd.CommandType = CommandType.Text;
object obj = cmd.ExecuteScalar();
if (obj == null)
theResult = 0;
else
theResult = int.Parse(obj.ToString());
}
catch (Exception err)
{
throw err;
}
finally
{
if (da != null)
{
da.Dispose();
da = null;
}
if (cmd != null)
{
cmd.Parameters.Clear();
cmd.Dispose();
cmd = null;
}
if (cn != null)
{
if (cn.State != ConnectionState.Closed)
cn.Close();
cn.Dispose();
cn = null;
}
}
return theResult;
}
#endregion 列出表所有记录
#region 创建参数
/// <summary>
/// 创建SqlParameter[]
/// </summary>
/// <param name="iLen"></param>
/// <returns></returns>
public static SqlParameter[] SqlParameterArrayCreate(int iLen)
{
return new SqlParameter[iLen];
}
/// <summary>
/// 创建IDataParameter[]
/// </summary>
/// <param name="iLen"></param>
/// <returns></returns>
public static IDataParameter[] IDataParameterArrayCreate(int iLen)
{
return new SqlParameter[iLen];
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public static SqlParameter SqlParameterCreate()
{
return new SqlParameter();
}
/// <summary>
///
/// </summary>
/// <param name="iLen"></param>
/// <returns></returns>
public static IDataParameter IDataParameterCreate()
{
return new SqlParameter();
}
#endregion 创建参数
}
}
标签：
SqlServerSlowXDataBaseHelper SqlServer 数据库操作相关方法 
上一条：
下一条：
相关评论
发表评论