1002
數據庫訪問類（使用存儲過程的）
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; public class DataBase { //使用單件模式，隻能實例化一個對象，所有方法都是靜態方法； private static DataBase _instance; public DataBase() { } public static DataBase GetInstance() { if(_instance==null) { _instance = new DataBase(); } return _instance; } //得到並返回一個打開的數據庫鏈接。 public static SqlConnection ReturnConn() { string strConn = System.Configuration.ConfigurationManager.AppSettings["ConnectionString"]; SqlConnection Conn = new SqlConnection(strConn); if (Conn.State.Equals(ConnectionState.Closed)) { Conn.Open(); } return Conn; } //①將存儲過程轉換成SqlCommand；（存儲過程名，字段集合，數據庫連接） public static SqlCommand CreateCmd(string procName, SqlParameter[] prams, SqlConnection Conn) { SqlConnection SqlConn = Conn; if (SqlConn.State.Equals(ConnectionState.Closed)) { SqlConn.Open(); } SqlCommand Cmd = new SqlCommand(); Cmd.CommandType = CommandType.StoredProcedure; Cmd.Connection = SqlConn; Cmd.CommandText = procName; if (prams != null) { foreach (SqlParameter parameter in prams) { if (parameter != null) { Cmd.Parameters.Add(parameter); } } } return Cmd; } //②將存儲過程轉換成SqlCommand；（存儲過程名，字段集合） public static SqlCommand CreateCmd(string procName, SqlParameter[] prams) { SqlConnection Conn = ReturnConn(); SqlCommand Cmd = new SqlCommand(); Cmd.CommandType = CommandType.StoredProcedure; Cmd.Connection = Conn; Cmd.CommandText = procName; if (prams != null) { foreach (SqlParameter parameter in prams) { if (parameter != null) { Cmd.Parameters.Add(parameter); } } } return Cmd; } //③將存儲過程轉換成SqlCommand；（存儲過程名，數據庫連接） public static SqlCommand CreateCmd(string procName,SqlConnection Conn) { SqlConnection SqlConn = Conn; if (SqlConn.State.Equals(ConnectionState.Closed)) { SqlConn.Open(); } SqlCommand Cmd = new SqlCommand(); Cmd.CommandType = CommandType.StoredProcedure; Cmd.Connection = SqlConn; Cmd.CommandText = procName; return Cmd; } //④將存儲過程轉換成SqlCommand；（存儲過程名） public static SqlCommand CreateCmd(string procName) { SqlConnection Conn = ReturnConn(); SqlCommand Cmd = new SqlCommand(); Cmd.CommandType = CommandType.StoredProcedure; Cmd.Connection = Conn; Cmd.CommandText = procName; return Cmd; } //①執行存儲過程並返回一個SqlDataReader對象，（存儲過程名，字段集合） public static SqlDataReader RunProcGetReader(string procName, SqlParameter[] prams) { SqlCommand Cmd = CreateCmd(procName, prams); SqlDataReader Dr = Cmd.ExecuteReader(CommandBehavior.CloseConnection); return Dr; } //②執行存儲過程並返回一個SqlDataReader對象，（存儲過程名，字段集合，數據庫連接） public static SqlDataReader RunProcGetReader(string procName, SqlParameter[] prams, SqlConnection Conn) { SqlCommand Cmd = CreateCmd(procName, prams,Conn); SqlDataReader Dr = Cmd.ExecuteReader(CommandBehavior.CloseConnection); return Dr; } //③執行存儲過程並返回一個SqlDataReader對象，（存儲過程名，數據庫連接） public static SqlDataReader RunProcGetReader(string procName, SqlConnection Conn) { SqlCommand Cmd = CreateCmd(procName,Conn); SqlDataReader Dr = Cmd.ExecuteReader(CommandBehavior.CloseConnection); return Dr; } //④執行存儲過程並返回一個SqlDataReader對象，（存儲過程名） public static SqlDataReader RunProcGetReader(string procName) { SqlCommand Cmd = CreateCmd(procName); SqlDataReader Dr = Cmd.ExecuteReader(CommandBehavior.CloseConnection); return Dr; } //執行存儲過程並返回一個DataTable對象，（存儲過程名，字段集合，數據庫連接） public static DataTable RunProcGetTable(string procName, SqlParameter[] prams, SqlConnection Conn) { SqlCommand Cmd = CreateCmd(procName, prams, Conn); SqlDataAdapter Dtr = new SqlDataAdapter(); DataSet Ds = new DataSet(); Dtr.SelectCommand = Cmd; Dtr.Fill(Ds); DataTable Dt = Ds.Tables[0]; Conn.Close(); return Dt; } //①直接執行存儲過程，返回整形數據。（存儲過程名） public static int RunExecute(string procName) { SqlConnection Conn = ReturnConn(); SqlCommand Cmd = CreateCmd(procName,Conn); int intResult = Cmd.ExecuteNonQuery(); Conn.Close(); return intResult; } //②直接執行存儲過程，返回整形數據。（存儲過程名，字段集合） public static int RunExecute(string procName,SqlParameter[] prams) { SqlConnection Conn = ReturnConn(); SqlCommand Cmd = CreateCmd(procName,prams,Conn); int intResult = Cmd.ExecuteNonQuery(); Conn.Close(); return intResult; } public static int RunExecuteScalar(string procName) { SqlConnection Conn = ReturnConn(); SqlCommand Cmd = CreateCmd(procName, Conn); int intResult = Convert.ToInt32(Cmd.ExecuteScalar()); Conn.Close(); return intResult; } public static int RunExecuteScalar(string procName, SqlParameter[] prams) { SqlConnection Conn = ReturnConn(); SqlCommand Cmd = CreateCmd(procName, prams, Conn); int intResult =Convert.ToInt32(Cmd.ExecuteScalar()); Conn.Close(); return intResult; } }
最後更新：2017-04-02 04:00:23