數據訪問抽象基礎類

/// <summary>

/// 數據訪問抽象基礎類

/// </summary>

public abstract class SQLHelper

{

//數據庫連接字符串(web.config來配置)，可以動態更改connectionString支持多數據庫.

public static string connectionString = ConfigurationManager.AppSettings["ConnectionString"];

/// <summary>

/// 準備執行一個命令

/// </summary>

/// <param name="cmd">sql命令</param>

/// <param name="conn">Sql連接</param>

/// <param name="trans">Sql事務</param>

/// <param name="cmdType">命令類型例如存儲過程或者文本</param>

/// <param name="cmdText">命令文本,例如：Select * from Products</param>

/// <param name="cmdParms">執行命令的參數</param>

private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)

{

if (conn.State != ConnectionState.Open)

conn.Open();

cmd.Connection = conn;

cmd.CommandText = cmdText;

if (trans != null)

cmd.Transaction = trans;

cmd.CommandType = cmdType;

if (cmdParms != null)

{

foreach (SqlParameter parm in cmdParms)

cmd.Parameters.Add(parm);

}

/// <summary>

/// 給定連接的數據庫用假設參數執行一個sql命令（不返回數據集）

/// </summary>

/// <param name="connectionString">一個有效的連接字符串</param>

/// <param name="commandType">命令類型(存儲過程, 文本, 等等)</param>

/// <param name="commandText">存儲過程名稱或者sql命令語句</param>

/// <param name="commandParameters">執行命令所用參數的集合</param>

/// <returns>執行命令所影響的行數</returns>

public static int ExecuteNonQuery(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)

{

SqlCommand cmd = new SqlCommand();

using (SqlConnection conn = new SqlConnection(connectionString))

{

PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);

int val = cmd.ExecuteNonQuery();

cmd.Parameters.Clear();

return val;

}

/// <summary>

/// 執行多條SQL語句，實現數據庫事務。

/// </summary>

/// <param name="SQLStringList">多條SQL語句</param>

public static int ExecuteNonQuerySqlTran(List<String> StringList)

{

using (SqlConnection conn = new SqlConnection(connectionString))

{

conn.Open();

SqlCommand cmd = new SqlCommand();

cmd.Connection = conn;

SqlTransaction trans = conn.BeginTransaction();

cmd.Transaction = trans;

try

{

int count = 0;

for (int i = 0; i < StringList.Count; i++)

{

string strsql = StringList[i];

if (strsql.Trim().Length > 1)

{

cmd.CommandText = strsql;

count += cmd.ExecuteNonQuery();

}

trans.Commit();

return count;

}

catch

{

trans.Rollback();

return 0;

}

finally

{

cmd.Dispose();

conn.Close();

}

/// <summary>

/// 用執行的數據庫連接執行一個返回數據集的sql命令

/// </summary>

/// <param name="connectionString">一個有效的連接字符串</param>

/// <param name="commandType">命令類型(存儲過程, 文本, 等等)</param>

/// <param name="commandText">存儲過程名稱或者sql命令語句</param>

/// <param name="commandParameters">執行命令所用參數的集合</param>

/// <returns>包含結果的讀取器</returns>

public static SqlDataReader ExecuteReader(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)

{

//創建一個SqlCommand對象

SqlCommand cmd = new SqlCommand();

//創建一個SqlConnection對象

SqlConnection conn = new SqlConnection(connectionString);

//在這里我們用一個try/catch結構執行sql文本命令/存儲過程，因為如果這個方法產生一個異常我們要關閉連接，因為沒有讀取器存在，

//因此commandBehaviour.CloseConnection 就不會執行

try

{

//調用 PrepareCommand 方法，對 SqlCommand 對象設置參數

PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);

//調用 SqlCommand 的 ExecuteReader 方法

SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

//清除參數

cmd.Parameters.Clear();

return reader;

}

catch

{

//關閉連接，拋出異常

conn.Close();

throw;

}

/// <summary>

/// 用指定的數據庫連接字符串執行一個命令并返回一個數據集的第一列

/// </summary>

///<param name="connectionString">一個有效的連接字符串</param>

/// <param name="cmdType">命令類型(存儲過程, 文本, 等等)</param>

/// <param name="cmdText">存儲過程名稱或者sql命令語句</param>

/// <param name="commandParameters">執行命令所用參數的集合</param>

/// <returns>用 Convert.To{Type}把類型轉換為想要的 </returns>

public static object ExecuteScalar(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)

{

SqlCommand cmd = new SqlCommand();

using (SqlConnection connection = new SqlConnection(connectionString))

{

PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);

object val = cmd.ExecuteScalar();

cmd.Parameters.Clear();

return val;

}

/// <summary>

/// 返回數據集DataSet

/// </summary>

/// <param name="cmdType">命令類型(存儲過程, 文本, 等等)</param>

/// <param name="cmdText">存儲過程名稱或者sql命令語句</param>

/// <param name="commandParameters">執行命令所用參數的集合</param>

/// <returns>返回DataSet</returns>

public static DataSet ExecuteDataset(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)

{

using (SqlConnection connection = new SqlConnection(connectionString))

{

SqlCommand cmd = new SqlCommand();

PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);

using (SqlDataAdapter da = new SqlDataAdapter(cmd))

{

DataSet ds = new DataSet();

try

{

da.Fill(ds, "ds");

cmd.Parameters.Clear();

}

catch (System.Data.SqlClient.SqlException ex)

{

throw new Exception(ex.Message);

}

finally

{

cmd.Dispose();

connection.Close();

}

return ds;

}

/// <summary>

/// 返回數據集DataSet

/// </summary>

/// <param name="int_PageSize"></param>

/// <param name="int_CurrentPageIndex"></param>

/// <param name="cmdType"></param>

/// <param name="cmdText"></param>

/// <param name="cmdParms"></param>

/// <returns></returns>

public static DataSet ExecuteDataset(int int_PageSize, int int_CurrentPageIndex, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)

{

using (SqlConnection connection = new SqlConnection(connectionString))

{

SqlCommand cmd = new SqlCommand();

PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);

using (SqlDataAdapter da = new SqlDataAdapter(cmd))

{

DataSet ds = new DataSet();

try

{

if (int_PageSize == 0 && int_CurrentPageIndex == 0)

{

da.Fill(ds, "ds");

}

else

{

int int_Page = int_PageSize * (int_CurrentPageIndex - 1);

if (int_Page < 0)

{

int_Page = 0;

}

da.Fill(ds, int_Page, int_PageSize, "ds");

}

cmd.Parameters.Clear();

}

catch (System.Data.SqlClient.SqlException ex)

{

throw new Exception(ex.Message);

}

finally

{

cmd.Dispose();

connection.Close();

}

return ds;

}

posted @ 2008-04-03 13:14 love .net FrameWork 閱讀(738) 評論(1) 收藏舉報

刷新頁面返回頂部

Love .NET Framework

我愛技術,這是我自信心的源泉。

數據訪問抽象基礎類

公告