DBOP——数据库操作类库

/*
* Created by SharpDevelop.
* User: XiaoQ
* Date: 2009-8-7
* Time: 21:37
*
* To change this template use Tools | Options | Coding | Edit Standard Headers.
*/
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

/// <summary>
/// DBOp 的摘要说明
/// </summary>
public class DBOP
{
public DBOP()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
private static string _connectionString;

public static string ConnectionString
{
get
{
return _connectionString;
}
set
{
_connectionString = value;
}
}

public static SqlConnection GetConn()
{
ConnectionString=System.Configuration.ConfigurationManager.AppSettings["ConnectionString"];
// ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
SqlConnection SqlConn = new SqlConnection(ConnectionString);
return SqlConn;
}

public static SqlCommand GetCmd(string procName, SqlParameter[] prams)
{
SqlConnection SqlConn = GetConn();
SqlCommand SqlCmd = new SqlCommand();
SqlCmd.Connection = SqlConn;
SqlCmd.CommandType = CommandType.StoredProcedure;
SqlCmd.CommandText = procName;
if (prams != null)
{
foreach (SqlParameter parameter in prams)
{
if (parameter != null)
{
SqlCmd.Parameters.Add(parameter);
}
}
}
return SqlCmd;
}

public static SqlCommand GetCmd(string procName, SqlParameter[] prams,SqlConnection SqlConn)
{
SqlCommand SqlCmd = new SqlCommand();
SqlCmd.Connection = SqlConn;
SqlCmd.CommandType = CommandType.StoredProcedure;
SqlCmd.CommandText = procName;
if (prams != null)
{
foreach (SqlParameter parameter in prams)
{
if (parameter != null)
{
SqlCmd.Parameters.Add(parameter);
}
}
}
return SqlCmd;
}

public static SqlCommand GetCmd(string procName)
{
SqlConnection SqlConn = GetConn();
SqlCommand SqlCmd = new SqlCommand();
SqlCmd.Connection = SqlConn;
SqlCmd.CommandType = CommandType.StoredProcedure;
SqlCmd.CommandText = procName;
return SqlCmd;
}

public static SqlCommand GetCmd(string procName,SqlConnection SqlConn)
{
SqlCommand SqlCmd = new SqlCommand();
SqlCmd.Connection = SqlConn;
SqlCmd.CommandType = CommandType.StoredProcedure;
SqlCmd.CommandText = procName;
return SqlCmd;
}

public static SqlDataReader GetReader(string procName)
{
SqlConnection SqlConn = GetConn();
SqlCommand SqlCmd = GetCmd(procName,SqlConn);
SqlConn.Open();
SqlDataReader SqlDR = SqlCmd.ExecuteReader(CommandBehavior.CloseConnection);
return SqlDR;
}

public static SqlDataReader GetReader(string procName,SqlConnection SqlConn)
{
SqlCommand SqlCmd = GetCmd(procName, SqlConn);
SqlConn.Open();
SqlDataReader SqlDR = SqlCmd.ExecuteReader(CommandBehavior.CloseConnection);
return SqlDR;
}

public static SqlDataReader GetReader(string procName, SqlParameter[] prams)
{
SqlConnection SqlConn = GetConn();
SqlCommand SqlCmd = GetCmd(procName,prams,SqlConn);
SqlConn.Open();
SqlDataReader SqlDR = SqlCmd.ExecuteReader(CommandBehavior.CloseConnection);
return SqlDR;
}

public static SqlDataReader GetReader(string procName, SqlParameter[] prams,SqlConnection SqlConn)
{
SqlCommand SqlCmd = GetCmd(procName, prams, SqlConn);
SqlConn.Open();
SqlDataReader SqlDR = SqlCmd.ExecuteReader(CommandBehavior.CloseConnection);
return SqlDR;
}

public static DataTable GetTable(string procName, SqlParameter[] prams)
{
SqlConnection SqlConn = GetConn();
SqlCommand SqlCmd = GetCmd(procName, prams, SqlConn);
SqlDataAdapter SqlDA = new SqlDataAdapter();
DataSet DS = new DataSet();
SqlDA.SelectCommand = SqlCmd;
SqlDA.Fill(DS);
DataTable DT = DS.Tables[0];
SqlConn.Close();
DS.Dispose();
SqlDA.Dispose();
SqlCmd.Dispose();
SqlConn.Dispose();
return DT;
}

public static DataTable GetTable(string procName, SqlParameter[] prams,SqlConnection SqlConn)
{
SqlCommand SqlCmd = GetCmd(procName, prams, SqlConn);
SqlDataAdapter SqlDA = new SqlDataAdapter();
DataSet DS = new DataSet();
SqlDA.SelectCommand = SqlCmd;
SqlDA.Fill(DS);
DataTable DT = DS.Tables[0];
SqlConn.Close();
DS.Dispose();
SqlDA.Dispose();
SqlCmd.Dispose();
SqlConn.Dispose();
return DT;
}

public static int RunExecute(string procName)
{
SqlConnection SqlConn = GetConn();
SqlCommand SqlCmd = GetCmd(procName, SqlConn);
int intResult;
try
{
SqlConn.Open();
intResult = SqlCmd.ExecuteNonQuery();
}
catch (SqlException e)
{
return -1;
}
finally
{
SqlConn.Close();
SqlCmd.Dispose();
SqlConn.Dispose();
}
return intResult;
}

public static int RunExecute(string procName,SqlConnection SqlConn)
{
SqlCommand SqlCmd = GetCmd(procName, SqlConn);
int intResult;
try
{
SqlConn.Open();
intResult = SqlCmd.ExecuteNonQuery();
}
catch (SqlException e)
{
return -1;
}
finally
{
SqlConn.Close();
SqlCmd.Dispose();
SqlConn.Dispose();
}
return intResult;
}

public static int RunExecute(string procName,SqlParameter[] prams)
{
SqlConnection SqlConn = GetConn();
SqlCommand SqlCmd = GetCmd(procName,prams,SqlConn);
int intResult;
try
{
SqlConn.Open();
intResult = SqlCmd.ExecuteNonQuery();
}
catch (SqlException e)
{
return -1;
}
finally
{
SqlConn.Close();
SqlCmd.Dispose();
SqlConn.Dispose();
}
return intResult;
}

public static int RunExecute(string procName, SqlParameter[] prams,SqlConnection SqlConn)
{
SqlCommand SqlCmd = GetCmd(procName, prams, SqlConn);
int intResult;
try
{
SqlConn.Open();
intResult = SqlCmd.ExecuteNonQuery();
}
catch (SqlException e)
{
return -1;
}
finally
{
SqlConn.Close();
SqlCmd.Dispose();
SqlConn.Dispose();
}
return intResult;
}

public static int RunExecuteScalar(string procName)
{
SqlConnection SqlConn = GetConn();
SqlCommand SqlCmd = GetCmd(procName,SqlConn);
int intResult;
try
{
SqlConn.Open();
intResult = Convert.ToInt32(SqlCmd.ExecuteScalar());
}
catch (SqlException e)
{
return -1;
}
finally
{
SqlConn.Close();
SqlCmd.Dispose();
SqlConn.Dispose();
}
return intResult;
}

public static int RunExecuteScalar(string procName,SqlConnection SqlConn)
{
SqlCommand SqlCmd = GetCmd(procName,SqlConn);
int intResult;
try
{
SqlConn.Open();
intResult = Convert.ToInt32(SqlCmd.ExecuteScalar());
}
catch (SqlException e)
{
return -1;
}
finally
{
SqlConn.Close();
SqlCmd.Dispose();
SqlConn.Dispose();
}
return intResult;
}

public static int RunExecuteScalar(string procName,SqlParameter[] prams)
{
SqlConnection SqlConn = GetConn();
SqlCommand SqlCmd = GetCmd(procName, prams,SqlConn);
int intResult;
try
{
SqlConn.Open();
intResult = Convert.ToInt32(SqlCmd.ExecuteScalar());
}
catch (SqlException e)
{
return -1;
}
finally
{
SqlConn.Close();
SqlCmd.Dispose();
SqlConn.Dispose();
}
return intResult;
}

public static int RunExecuteScalar(string procName, SqlParameter[] prams,SqlConnection SqlConn)
{
SqlCommand SqlCmd = GetCmd(procName, prams,SqlConn);
int intResult;
try
{
SqlConn.Open();
intResult = Convert.ToInt32(SqlCmd.ExecuteScalar());
}
catch (SqlException e)
{
return -1;
}
finally
{
SqlConn.Close();
SqlCmd.Dispose();
SqlConn.Dispose();
}
return intResult;
}

/********************Sql操作，CommandType为Text(直接执行SqlCommand语句)**************************************/

/// <summary>
/// 获取指定查询语句的SqlCommand
/// </summary>
/// <param name="sqlStr">要执行的Sql查询语句</param>
/// <param name="SqlConn">数据库连接</param>
/// <returns>SqlCommand</returns>
public static SqlCommand ReturnCmd(string sqlStr,SqlConnection SqlConn)
{
SqlCommand SqlCmd = new SqlCommand(sqlStr,SqlConn);
return SqlCmd;
}
/// <summary>
/// 获取指定Sql操作的SqlCommand
/// </summary>
/// <param name="sqlStr">要执行的Sql查询语句</param>
/// <returns>SqlCommand</returns>
public static SqlCommand ReturnCmd(string sqlStr)
{
SqlConnection SqlConn = GetConn();
SqlCommand SqlCmd = new SqlCommand(sqlStr, SqlConn);
return SqlCmd;
}
/// <summary>
/// 获取指定查询语句的SqlDataReader
/// </summary>
/// <param name="sqlStr">要执行的Sql查询语句</param>
/// <param name="SqlConn">数据库连接</param>
/// <returns>SqlDataReader</returns>
public static SqlDataReader ReturnReader(string sqlStr,SqlConnection SqlConn)
{
SqlCommand SqlCmd = ReturnCmd(sqlStr,SqlConn);
SqlConn.Open();
SqlDataReader SqlDR = SqlCmd.ExecuteReader(CommandBehavior.CloseConnection);
return SqlDR;
}
/// <summary>
/// 获取指定查询语句的SqlDataReader
/// </summary>
/// <param name="sqlStr">要执行的Sql查询语句</param>
/// <returns>SqlDataReader</returns>
public static SqlDataReader ReturnReader(string sqlStr)
{
SqlConnection SqlConn = GetConn();
SqlCommand SqlCmd = ReturnCmd(sqlStr, SqlConn);
SqlConn.Open();
SqlDataReader SqlDR = SqlCmd.ExecuteReader(CommandBehavior.CloseConnection);
return SqlDR;
}

public static int ExeCmd(string sqlStr)
{
SqlConnection SqlConn = GetConn();
SqlCommand SqlCmd = new SqlCommand(sqlStr, SqlConn);
int intResult;
try
{
SqlConn.Open();
intResult= SqlCmd.ExecuteNonQuery();
}
catch (SqlException e)
{
return -1;
}
finally
{
SqlConn.Close();
SqlCmd.Dispose();
SqlConn.Dispose();
}
return intResult;

}

public static int ExeCmd(string sqlStr,SqlConnection SqlConn)
{
SqlCommand SqlCmd = new SqlCommand(sqlStr, SqlConn);
int intResult;
try
{
SqlConn.Open();
intResult = SqlCmd.ExecuteNonQuery();
}
catch (SqlException e)
{
return -1;
}
finally
{
SqlConn.Close();
SqlCmd.Dispose();
SqlConn.Dispose();
}
return intResult;

}

public static DataTable ReturnTable(string sqlStr)
{
SqlConnection SqlConn = GetConn();
SqlCommand SqlCmd = ReturnCmd(sqlStr, SqlConn);
SqlDataAdapter SqlDA= new SqlDataAdapter(SqlCmd);
DataSet DS = new DataSet();

try
{
SqlDA.Fill(DS);
}
catch (SqlException e)
{
}
finally
{
SqlConn.Close();

}
DataTable DT = DS.Tables[0];
DS.Dispose();
SqlDA.Dispose();
SqlCmd.Dispose();
SqlConn.Dispose();
return DT;
}
}

转载于:https://www.cnblogs.com/xiaoQcn/archive/2009/08/08/1652937.html