- 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;
- }
- }
数据库访问类(使用存储过程的)
最新推荐文章于 2021-11-21 10:38:04 发布