在开发过程中有大量涉及到与数据库相关的操作,为了便于开发和维护,把这些操作封装成类,实现代码复用原则。
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
namespace CommonClass
{
public class DBExecute
{
private static string strConn = System.Configuration.ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
/// <summary>
/// 无返回值的数据库操作,如插入、删除等;
/// </summary>
/// <param name="strSQL"></param>
/// <returns></returns>
public static int ExecuteSql(string strSQL)
{
SqlConnection conn = new SqlConnection(strConn);
SqlCommand cmd = new SqlCommand(strSQL, conn);
try
{
conn.Open(); //打开数据库
cmd.ExecuteNonQuery(); //执行无返回值的数据库操作
return 0;
}
catch (SqlException e)
{
throw new Exception(e.Message); //抛出异常
}
finally
{
cmd.Dispose(); //释放该组件占用的资源
conn.Close(); //每次操作完毕都要关闭连接
}
}
/// <summary>
/// 返回值为DataReader类型的操作
/// </summary>
/// <param name="strSQL"></param>
/// <returns></returns>
public static int ExecuteSqlReader(string strSQL)
{
SqlConnection conn = new SqlConnection(strConn);
SqlCommand cmd = new SqlCommand(strSQL, conn);
try
{
conn.Open();
SqlDataReader sdr = cmd.ExecuteReader();
if (sdr.Read())
{
return 0; //执行成功,返回 0
}
else
{
throw new Exception("Value Unavailable!");
}
}
catch (SqlException e)
{
throw new Exception(e.Message);
}
finally
{
cmd.Dispose();
conn.Close();
}
}
/// <summary>
/// 返回值为DataSet类型的操作
/// </summary>
/// <param name="strSQL"></param>
/// <returns></returns>
public static DataSet ExecuteSqlDS(string strSQL)
{
SqlConnection conn = new SqlConnection(strConn);
try
{
conn.Open();
SqlDataAdapter sda = new SqlDataAdapter(strSQL, conn);
DataSet ds = new DataSet("ds");
sda.Fill(ds); //调用SqlDataAdapter的Fill方法,为DataSet填充数据
return ds; //返回得到的DataSet对象,保存了从数据库查询到的数据
}
catch (SqlException e)
{
throw new Exception(e.Message);
}
finally
{
conn.Close();
}
}
/// <summary>
/// 返回值为int类型的操作
/// </summary>
/// <param name="strSQL"></param>
/// <returns></returns>
public static int ExecuteSqlValue(string strSQL)
{
SqlConnection conn = new SqlConnection(strConn);
SqlCommand cmd = new SqlCommand(strSQL, conn);
try
{
conn.Open();
object o = cmd.ExecuteScalar();
if (Object.Equals(o, null)) //判断对象是否为空
{
throw new Exception("Value Unavailable!");
}
else
{
return (int)o;
}
}
catch (SqlException e)
{
throw new Exception(e.Message);
}
finally
{
cmd.Dispose();
conn.Close();
}
}
/// <summary>
/// 直接返回object
/// </summary>
/// <param name="strSQL"></param>
/// <returns></returns>
protected static object ExecuteSqlObj(string strSQL)
{
SqlConnection conn = new SqlConnection(strConn);
SqlCommand cmd = new SqlCommand(strSQL, conn);
try
{
conn.Open();
object o = cmd.ExecuteScalar();
if (Object.Equals(o, null))
{
throw new Exception("Object Unavailable!");
}
else
{
return o;
}
}
catch (SqlException e)
{
throw new Exception(e.Message);
}
finally
{
cmd.Dispose();
conn.Close();
}
}
/// <summary>
/// 执行多条无返回值的数据库操作
/// </summary>
/// <param name="strSQLs"></param>
/// <returns></returns>
public static int ExecuteSqls(string[] strSQLArr)
{
SqlConnection conn = new SqlConnection(strConn);
SqlCommand cmd = new SqlCommand();
int i = strSQLArr.Length; //获取字符串数组中字符串的个数
try
{
conn.Open();
}
catch (SqlException e)
{
throw new Exception(e.Message);
}
SqlTransaction tran = conn.BeginTransaction();
try
{
cmd.Connection = conn;
cmd.Transaction = tran;
foreach (string str in strSQLArr)
{
cmd.CommandText = str;
cmd.ExecuteNonQuery();
}
tran.Commit(); //提交事务
return 0;
}
catch (SqlException e)
{
tran.Rollback();
throw new Exception(e.Message);
}
finally
{
cmd.Dispose();
conn.Close();
}
}
/// <summary>
/// 执行存储过程的,返回值为object类型
/// </summary>
/// <param name="ProcName">存储过程名称</param>
/// <param name="parm">参数</param>
/// <returns>object</returns>
public static object ExecuteProcGetObject(string ProcName, SqlParameter[] parm)
{
SqlConnection conn = new SqlConnection(strConn);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
if (parm != null)
{
foreach (SqlParameter p in parm)
{
if (p != null)
{
if ((p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Input) &&
(p.Value == null))
{
p.Value = DBNull.Value;
}
cmd.Parameters.Add(p);
}
}
}
cmd.CommandText = ProcName;
cmd.Connection = conn;
cmd.CommandTimeout = 0;
object obj = null;
try
{
conn.Open();
obj = cmd.ExecuteScalar();
}
catch (SqlException e)
{
throw new Exception(e.Message);
}
finally
{
cmd.Dispose();
conn.Close();
}
return obj;
}
/// <summary>
/// 执行存储过程,返回值为DataSet类型
/// </summary>
/// <param name="ProcName"></param>
/// <param name="parm"></param>
/// <returns></returns>
public static DataSet ExecuteProcGetDataSet(string ProcName, SqlParameter[] parm)
{
DataSet ds = new DataSet();
SqlConnection conn = new SqlConnection(strConn);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
if (parm != null)
{
foreach (SqlParameter p in parm)
{
if (p != null)
{
if ((p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Input) &&
(p.Value == null))
{
p.Value = DBNull.Value;
}
cmd.Parameters.Add(p);
}
}
}
cmd.CommandText = ProcName;
cmd.Connection = conn;
cmd.CommandTimeout = 0;
SqlDataAdapter sda = new SqlDataAdapter(cmd);
try
{
conn.Open();
sda.Fill(ds);
}
catch (SqlException e)
{
throw new Exception(e.Message);
}
finally
{
cmd.Dispose();
conn.Close();
}
return ds;
}
/// <summary>
/// 执行存储过程,返回值为DataTable类型
/// </summary>
/// <param name="ProcName"></param>
/// <param name="parm"></param>
/// <returns></returns>
public static DataTable ExecuteProcGetDataTable(string ProcName, SqlParameter[] parm)
{
DataSet ds = ExecuteProcGetDataSet(ProcName, parm);
if (ds == null) return null;
if (ds.Tables.Count >= 1)
{
return ds.Tables[0];
}
else
{
return null;
}
}
}
}