using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Xml.Linq;
using System.Data.SqlClient;
namespace swwPADSystem
{
public class DataClass_MsSQL
{
public DataClass_MsSQL() { }
/// <summary>
/// 得到连接字符串,从配置文件中获取
/// </summary>
public static string ConnectionStringSQL
{
get
{
return Convert.ToString(ConfigurationManager.AppSettings["SqlDBconnect"]);
}
}
public static System.Data.SqlClient.SqlConnection GetConnection()
{
return new SqlConnection(ConnectionStringSQL);
}
public static System.Data.SqlClient.SqlDataReader ExecuteReader(string strSQL)
{
SqlConnection conn = null;
SqlCommand cmd = null;
SqlDataReader reader = null;
try
{
conn = GetConnection();
conn.Open();
cmd = new SqlCommand(strSQL, conn);
reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return reader;
}
catch(System.Exception e)
{
if ( conn != null)
{
conn.Dispose();
}
if ( cmd != null)
{
cmd.Dispose();
}
throw(e);
}
}
public static object ExecuteScalar( string strSQL)
{
SqlConnection conn = null;
SqlCommand cmd = null;
object oRet = null;
try
{
conn = GetConnection();
conn.Open();
cmd = new SqlCommand(strSQL, conn);
oRet = cmd.ExecuteScalar();
}
catch (System.Exception e)
{
throw (e);
}
finally
{
if (conn != null)
conn.Dispose();
if (cmd != null)
cmd.Dispose();
}
return oRet;
}
/// <summary>
/// 传入SQL语句,返回数据集
/// </summary>
/// <param name="strSQL"></param>
/// <param name="strConnectionString"></param>
/// <returns></returns>
public static DataSet ExecuteDataSet(string strSQL , string strConnectionString)
{
SqlConnection conn = null;
SqlDataAdapter dapt = null;
DataSet ds = new DataSet();
try
{
conn = new SqlConnection(strConnectionString);
conn.Open();
dapt = new SqlDataAdapter(strSQL, conn);
dapt.Fill(ds, "table1");
}
catch (System.Exception e)
{
throw (e);
}
finally
{
if (conn != null)
conn.Dispose();
if (dapt != null)
dapt.Dispose();
}
return ds;
}
/// <summary>
/// 执行查询语句,返回数据集
/// </summary>
/// <param name="strSQL"></param>
/// <returns></returns>
public static DataSet ExecuteDataSet( string strSQL)
{
return ExecuteDataSet(strSQL, ConnectionStringSQL);
}
/// <summary>
/// 执行SQL语句,并且返回数据集
/// </summary>
/// <param name="strSQL"></param>
/// <returns></returns>
public static DataSet ExecuteDataSetWithTrans(string strSQL)
{
SqlConnection conn = null;
SqlCommand cmd = null;
SqlDataAdapter dapt = null;
SqlTransaction tran = null;
DataSet ds = new DataSet();
try
{
conn = GetConnection();
conn.Open();
tran = conn.BeginTransaction();
cmd = new SqlCommand(strSQL, conn, tran);
dapt = new SqlDataAdapter(cmd);
dapt.Fill(ds, "table1");
tran.Commit();
}
catch (System.Data.SqlClient.SqlException e)
{
tran.Rollback();
ds.Tables.Clear();
ds.Tables.Add("Err");
DataTable dt = ds.Tables[0];
dt.Columns.Add("ErrNumber", typeof(System.Int32));
dt.Columns.Add("ErrMsg", typeof(string));
DataRow row = dt.NewRow();
row["ErrNumber"] = e.ErrorCode;
row["ErrMsg"] = e.Message;
dt.Rows.Add(row);
}
finally
{
if (conn != null)
conn.Dispose();
if (cmd != null)
cmd.Dispose();
if (dapt != null)
dapt.Dispose();
if (tran != null)
tran.Dispose();
}
return ds;
}
/// <summary>
/// 执行查询语句,传入连接串
/// </summary>
/// <param name="strSQL"></param>
/// <param name="conn"></param>
/// <returns></returns>
public static DataTable ExecuteDataTable( string strSQL , ref SqlConnection conn)
{
SqlDataAdapter dapt = null;
DataTable dt = new DataTable();
try
{
dapt = new SqlDataAdapter(strSQL, conn);
dapt.Fill(dt);
}
catch (System.Exception e)
{
throw (e);
}
finally
{
if (dapt != null)
dapt.Dispose();
}
return dt;
}
/// <summary>
/// 执行查询语句,返回datatable
/// </summary>
/// <param name="strSQL"></param>
/// <returns></returns>
public static DataTable ExecuteDataTable(string strSQL)
{
SqlConnection conn = null;
DataTable dt = null;
try
{
conn = GetConnection();
conn.Open();
dt = ExecuteDataTable(strSQL, ref conn);
}
catch (System.Exception e)
{
throw (e);
}
finally
{
if (conn != null)
{
conn.Dispose();
}
}
return dt;
}
public static int ExecuteNoQuery(string strSQL)
{
string ErrMsg = string.Empty;
return EexcuteNoQuery(strSQL , ref ErrMsg);
}
/// <summary>
/// 执行SQL语句。
/// </summary>
/// <param name="strSQL"></param>
/// <param name="Msg"></param>
/// <returns></returns>
public static int EexcuteNoQuery(string strSQL , ref string Msg)
{
SqlConnection conn = null;
SqlCommand cmd = null;
int i = 0;
try
{
conn = GetConnection();
conn.Open();
cmd = new SqlCommand(strSQL, conn);
i = cmd.ExecuteNonQuery();
}
catch (System.Data.SqlClient.SqlException e)
{
i = Math.Abs(e.ErrorCode) * -1;
Msg = e.Message;
}
finally
{
if (conn != null)
conn.Dispose();
if (cmd != null)
cmd.Dispose();
}
return i;
}
/// <summary>
/// 执行指定的查询表达式,基于事务处理
/// </summary>
/// <param name="strSQL"></param>
/// <returns></returns>
public static int ExecuteNoQueryWithTrans(string strSQL)
{
SqlConnection conn = null;
SqlCommand cmd = null;
SqlTransaction tran = null;
int i = 0;
try
{
conn = GetConnection();
conn.Open();
tran = conn.BeginTransaction();
cmd = new SqlCommand(strSQL, conn, tran);
i = cmd.ExecuteNonQuery();
tran.Commit();
}
catch (System.Data.SqlClient.SqlException e)
{
i = Math.Abs(e.ErrorCode) * -1;
tran.Rollback();
}
finally
{
if (conn != null)
conn.Dispose();
if (cmd != null)
cmd.Dispose();
if (tran != null)
tran.Dispose();
}
return i;
}
/// <summary>
/// 分页方法,返回数据集
/// </summary>
/// <param name="strSQL">要查询的SQL语句</param>
/// <param name="PrimaryKey">主键</param>
/// <param name="PageNo"><当前页号/param>
/// <param name="PageSize">第页记录数</param>
/// <param name="SortExpression">排序表达式</param>
/// <param name="RecordCount"></param>
/// <returns></returns>
public static DataSet GetSqlResult(string strSQL, string PrimaryKey, int PageNo, int PageSize, string SortExpression, ref int RecordCount)
{
SqlConnection conn = null;
SqlCommand cmd = null;
SqlDataAdapter dapt = null;
try
{
conn = new SqlConnection(DataClass_MsSQL.ConnectionStringSQL);
cmd = new SqlCommand("GetPageResult", conn);
cmd.CommandTimeout = 60000;
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter pSql = cmd.Parameters.Add("@sql", SqlDbType.NVarChar, 4000);
pSql.Value = strSQL;
SqlParameter pPKey = cmd.Parameters.Add("@PKey", SqlDbType.VarChar, 50);
pPKey.Value = PrimaryKey;
SqlParameter pPageNo = cmd.Parameters.Add("@PageNo", SqlDbType.Int, 4);
pPageNo.Value = PageNo;
SqlParameter pPageSize = cmd.Parameters.Add("@PageSize", SqlDbType.Int, 4);
pPageSize.Value = PageSize;
SqlParameter pSort = cmd.Parameters.Add("@sort", SqlDbType.VarChar, 50);
pSort.Value = SortExpression;
SqlParameter pRecordCount = cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4);
//pRecordCount.Value = SortExpression;
pRecordCount.Direction = ParameterDirection.Output;
dapt = new SqlDataAdapter(cmd);
conn.Open();
DataSet ds = new DataSet();
dapt.Fill(ds, "Table1");
RecordCount = (int)pRecordCount.Value;
return ds;
}
catch (Exception e)
{
throw (e);
//return null;
}
finally
{
if (conn != null)
conn.Dispose();
if (cmd != null)
cmd.Dispose();
if (dapt != null)
dapt.Dispose();
}
}
}
}
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Xml.Linq;
using System.Data.SqlClient;
namespace swwPADSystem
{
public class DataClass_MsSQL
{
public DataClass_MsSQL() { }
/// <summary>
/// 得到连接字符串,从配置文件中获取
/// </summary>
public static string ConnectionStringSQL
{
get
{
return Convert.ToString(ConfigurationManager.AppSettings["SqlDBconnect"]);
}
}
public static System.Data.SqlClient.SqlConnection GetConnection()
{
return new SqlConnection(ConnectionStringSQL);
}
public static System.Data.SqlClient.SqlDataReader ExecuteReader(string strSQL)
{
SqlConnection conn = null;
SqlCommand cmd = null;
SqlDataReader reader = null;
try
{
conn = GetConnection();
conn.Open();
cmd = new SqlCommand(strSQL, conn);
reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return reader;
}
catch(System.Exception e)
{
if ( conn != null)
{
conn.Dispose();
}
if ( cmd != null)
{
cmd.Dispose();
}
throw(e);
}
}
public static object ExecuteScalar( string strSQL)
{
SqlConnection conn = null;
SqlCommand cmd = null;
object oRet = null;
try
{
conn = GetConnection();
conn.Open();
cmd = new SqlCommand(strSQL, conn);
oRet = cmd.ExecuteScalar();
}
catch (System.Exception e)
{
throw (e);
}
finally
{
if (conn != null)
conn.Dispose();
if (cmd != null)
cmd.Dispose();
}
return oRet;
}
/// <summary>
/// 传入SQL语句,返回数据集
/// </summary>
/// <param name="strSQL"></param>
/// <param name="strConnectionString"></param>
/// <returns></returns>
public static DataSet ExecuteDataSet(string strSQL , string strConnectionString)
{
SqlConnection conn = null;
SqlDataAdapter dapt = null;
DataSet ds = new DataSet();
try
{
conn = new SqlConnection(strConnectionString);
conn.Open();
dapt = new SqlDataAdapter(strSQL, conn);
dapt.Fill(ds, "table1");
}
catch (System.Exception e)
{
throw (e);
}
finally
{
if (conn != null)
conn.Dispose();
if (dapt != null)
dapt.Dispose();
}
return ds;
}
/// <summary>
/// 执行查询语句,返回数据集
/// </summary>
/// <param name="strSQL"></param>
/// <returns></returns>
public static DataSet ExecuteDataSet( string strSQL)
{
return ExecuteDataSet(strSQL, ConnectionStringSQL);
}
/// <summary>
/// 执行SQL语句,并且返回数据集
/// </summary>
/// <param name="strSQL"></param>
/// <returns></returns>
public static DataSet ExecuteDataSetWithTrans(string strSQL)
{
SqlConnection conn = null;
SqlCommand cmd = null;
SqlDataAdapter dapt = null;
SqlTransaction tran = null;
DataSet ds = new DataSet();
try
{
conn = GetConnection();
conn.Open();
tran = conn.BeginTransaction();
cmd = new SqlCommand(strSQL, conn, tran);
dapt = new SqlDataAdapter(cmd);
dapt.Fill(ds, "table1");
tran.Commit();
}
catch (System.Data.SqlClient.SqlException e)
{
tran.Rollback();
ds.Tables.Clear();
ds.Tables.Add("Err");
DataTable dt = ds.Tables[0];
dt.Columns.Add("ErrNumber", typeof(System.Int32));
dt.Columns.Add("ErrMsg", typeof(string));
DataRow row = dt.NewRow();
row["ErrNumber"] = e.ErrorCode;
row["ErrMsg"] = e.Message;
dt.Rows.Add(row);
}
finally
{
if (conn != null)
conn.Dispose();
if (cmd != null)
cmd.Dispose();
if (dapt != null)
dapt.Dispose();
if (tran != null)
tran.Dispose();
}
return ds;
}
/// <summary>
/// 执行查询语句,传入连接串
/// </summary>
/// <param name="strSQL"></param>
/// <param name="conn"></param>
/// <returns></returns>
public static DataTable ExecuteDataTable( string strSQL , ref SqlConnection conn)
{
SqlDataAdapter dapt = null;
DataTable dt = new DataTable();
try
{
dapt = new SqlDataAdapter(strSQL, conn);
dapt.Fill(dt);
}
catch (System.Exception e)
{
throw (e);
}
finally
{
if (dapt != null)
dapt.Dispose();
}
return dt;
}
/// <summary>
/// 执行查询语句,返回datatable
/// </summary>
/// <param name="strSQL"></param>
/// <returns></returns>
public static DataTable ExecuteDataTable(string strSQL)
{
SqlConnection conn = null;
DataTable dt = null;
try
{
conn = GetConnection();
conn.Open();
dt = ExecuteDataTable(strSQL, ref conn);
}
catch (System.Exception e)
{
throw (e);
}
finally
{
if (conn != null)
{
conn.Dispose();
}
}
return dt;
}
public static int ExecuteNoQuery(string strSQL)
{
string ErrMsg = string.Empty;
return EexcuteNoQuery(strSQL , ref ErrMsg);
}
/// <summary>
/// 执行SQL语句。
/// </summary>
/// <param name="strSQL"></param>
/// <param name="Msg"></param>
/// <returns></returns>
public static int EexcuteNoQuery(string strSQL , ref string Msg)
{
SqlConnection conn = null;
SqlCommand cmd = null;
int i = 0;
try
{
conn = GetConnection();
conn.Open();
cmd = new SqlCommand(strSQL, conn);
i = cmd.ExecuteNonQuery();
}
catch (System.Data.SqlClient.SqlException e)
{
i = Math.Abs(e.ErrorCode) * -1;
Msg = e.Message;
}
finally
{
if (conn != null)
conn.Dispose();
if (cmd != null)
cmd.Dispose();
}
return i;
}
/// <summary>
/// 执行指定的查询表达式,基于事务处理
/// </summary>
/// <param name="strSQL"></param>
/// <returns></returns>
public static int ExecuteNoQueryWithTrans(string strSQL)
{
SqlConnection conn = null;
SqlCommand cmd = null;
SqlTransaction tran = null;
int i = 0;
try
{
conn = GetConnection();
conn.Open();
tran = conn.BeginTransaction();
cmd = new SqlCommand(strSQL, conn, tran);
i = cmd.ExecuteNonQuery();
tran.Commit();
}
catch (System.Data.SqlClient.SqlException e)
{
i = Math.Abs(e.ErrorCode) * -1;
tran.Rollback();
}
finally
{
if (conn != null)
conn.Dispose();
if (cmd != null)
cmd.Dispose();
if (tran != null)
tran.Dispose();
}
return i;
}
/// <summary>
/// 分页方法,返回数据集
/// </summary>
/// <param name="strSQL">要查询的SQL语句</param>
/// <param name="PrimaryKey">主键</param>
/// <param name="PageNo"><当前页号/param>
/// <param name="PageSize">第页记录数</param>
/// <param name="SortExpression">排序表达式</param>
/// <param name="RecordCount"></param>
/// <returns></returns>
public static DataSet GetSqlResult(string strSQL, string PrimaryKey, int PageNo, int PageSize, string SortExpression, ref int RecordCount)
{
SqlConnection conn = null;
SqlCommand cmd = null;
SqlDataAdapter dapt = null;
try
{
conn = new SqlConnection(DataClass_MsSQL.ConnectionStringSQL);
cmd = new SqlCommand("GetPageResult", conn);
cmd.CommandTimeout = 60000;
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter pSql = cmd.Parameters.Add("@sql", SqlDbType.NVarChar, 4000);
pSql.Value = strSQL;
SqlParameter pPKey = cmd.Parameters.Add("@PKey", SqlDbType.VarChar, 50);
pPKey.Value = PrimaryKey;
SqlParameter pPageNo = cmd.Parameters.Add("@PageNo", SqlDbType.Int, 4);
pPageNo.Value = PageNo;
SqlParameter pPageSize = cmd.Parameters.Add("@PageSize", SqlDbType.Int, 4);
pPageSize.Value = PageSize;
SqlParameter pSort = cmd.Parameters.Add("@sort", SqlDbType.VarChar, 50);
pSort.Value = SortExpression;
SqlParameter pRecordCount = cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4);
//pRecordCount.Value = SortExpression;
pRecordCount.Direction = ParameterDirection.Output;
dapt = new SqlDataAdapter(cmd);
conn.Open();
DataSet ds = new DataSet();
dapt.Fill(ds, "Table1");
RecordCount = (int)pRecordCount.Value;
return ds;
}
catch (Exception e)
{
throw (e);
//return null;
}
finally
{
if (conn != null)
conn.Dispose();
if (cmd != null)
cmd.Dispose();
if (dapt != null)
dapt.Dispose();
}
}
}
}