Database(数据库访问类)方法概要
1、
ReturnConn()//返回一个连接SqlConnection
2、
a. CreateCmd(string procName, SqlParameter[] prams) //返回SqlCommand
b. CreateCmd(string procName, SqlParameter[] prams, SqlConnection conn)
c. CreateCmd(string procName, SqlConnection conn) //返回SqlCommand
d. CreateCmd(string procName) //返回SqlCommand
3、
a．RunProcGetReader(string procName, SqlParameter[] prams)//返回SqlDataReader
b. RunProcGetReader(string procName,SqlParameter[] prams,SqlConnection conn)//返回SqlDataReader
c. RunProcGetReader(string procName, SqlConnection conn) //返回SqlDataReader
d. RunProcGetReader(string procName) //返回SqlDataReader
4、
RunProcGetTable(string procName, SqlParameter[] prams, SqlConnection conn) //返回DataTable
5、
a．RunExecute(string procName);//返回int
b. RunExecute(string procName, SqlParameter[] prams)//返回int
6、
a. RunExecuteScalar(string procName)//返回首行首列
b. RunExecuteScalar(string procName,SqlParameter[] prams) //返回首行首列
完整代码：
Code
1
using System;
2using System.Data;
3using System.Configuration;
4using System.Web;
5using System.Web.Security;
6using System.Web.UI;
7using System.Web.UI.WebControls;
8using System.Web.UI.WebControls.WebParts;
9using System.Web.UI.HtmlControls;
10using System.Data.SqlClient;
11
12/**//// <summary>
13
/// DataBase 的摘要说明
14
/// </summary>
15public class DataBase
16
{
17
18 private static DataBase _instance;
19 protected DataBase()
20
{
21
}
22
23 //用单例模式返回对象的实例
24 public static DataBase getInstance()
25 {
26 if (null == _instance)
27 {
28 _instance = new DataBase();
29 }
30 return _instance;
31 }
32
33 //获得数据库连接
34 public static SqlConnection ReturnConn()
35 {
36 string strConn = System.Configuration.ConfigurationManager.AppSettings["ConnectionString"];
37 SqlConnection Conn = new SqlConnection(strConn);
38 if (Conn.State.Equals(ConnectionState.Closed))//如果数据库连接已经关闭则将其打开
39 {
40 Conn.Open();
41 }
42 return Conn;
43 }
44
45 //返回SqlCommand
46 public static SqlCommand CreateCmd(string procName, SqlParameter[] parms)
47 {
48 SqlConnection Conn = ReturnConn();
49 SqlCommand Cmd = new SqlCommand();
50 Cmd.CommandType = CommandType.StoredProcedure;
51 Cmd.Connection = Conn;
52 Cmd.CommandText = procName;
53 if (parms != null && parms.Length > 0)
54 {
55 foreach (SqlParameter parm in parms)
56 {
57 if (parm != null)
58 {
59 Cmd.Parameters.Add(parm);
60 }
61 }
62 }
63 return Cmd;
64 }
65
66 public static SqlCommand CreateCmd(string procName, SqlParameter[] parms, SqlConnection conn)
67 {
68 SqlConnection Conn = conn;
69 if (Conn.State.Equals(ConnectionState.Closed))
70 {
71 Conn.Open();
72 }
73 SqlCommand Cmd = new SqlCommand();
74 Cmd.CommandType = CommandType.StoredProcedure;
75 Cmd.Connection = Conn;
76 Cmd.CommandText = procName;
77 if (parms != null && parms.Length > 0)
78 {
79 foreach (SqlParameter parm in parms)
80 {
81 if (parm != null)
82 {
83 Cmd.Parameters.Add(parm);
84 }
85 }
86 }
87 return Cmd;
88 }
89
90 public static SqlCommand CreateCmd(string procName, SqlConnection conn)
91 {
92 SqlConnection Conn = conn;
93 if (Conn.State.Equals(ConnectionState.Closed))
94 {
95 Conn.Open();
96 }
97 SqlCommand Cmd = new SqlCommand();
98 Cmd.CommandType = CommandType.StoredProcedure;
99 Cmd.Connection = Conn;
100 Cmd.CommandText = procName;
101 return Cmd;
102 }
103
104 public static SqlCommand CreateCmd(string procName)
105 {
106 SqlConnection Conn = ReturnConn();
107 SqlCommand Cmd = new SqlCommand();
108 Cmd.CommandType = CommandType.StoredProcedure;
109 Cmd.Connection = Conn;
110 Cmd.CommandText = procName;
111 return Cmd;
112 }
113
114 //返回SqlDataReader
115 public static SqlDataReader RunProcGetReader(string procName, SqlParameter[] prams)
116 {
117 SqlCommand Cmd = CreateCmd(procName, prams);
118 SqlDataReader Dr = Cmd.ExecuteReader(CommandBehavior.CloseConnection);
119 return Dr;
120 }
121
122 public static SqlDataReader RunProcGetReader(string procName, SqlParameter[] prams, SqlConnection conn)
123 {
124 SqlCommand Cmd = CreateCmd(procName, prams, conn);
125 SqlDataReader Dr = Cmd.ExecuteReader(CommandBehavior.CloseConnection);
126 return Dr;
127 }
128
129 public static SqlDataReader RunProcGetReader(string procName, SqlConnection conn)
130 {
131 SqlCommand Cmd = CreateCmd(procName, conn);
132 SqlDataReader Dr = Cmd.ExecuteReader(CommandBehavior.CloseConnection);
133 return Dr;
134 }
135
136 public static SqlDataReader RunProcGetReader(string procName)
137 {
138 SqlCommand Cmd = CreateCmd(procName);
139 SqlDataReader Dr = Cmd.ExecuteReader(CommandBehavior.CloseConnection);
140 return Dr;
141 }
142
143 //返回DataTable
144 public static DataTable RunProcGetTable(string procName, SqlParameter[] prams, SqlConnection conn)
145 {
146 SqlCommand Cmd = CreateCmd(procName, prams, conn);
147 SqlDataAdapter Dtr = new SqlDataAdapter();
148 DataSet Ds = new DataSet();
149 Dtr.SelectCommand = Cmd;
150 Dtr.Fill(Ds);
151 DataTable Dt = Ds.Tables[0];
152 conn.Close();
153 return Dt;
154 }
155
156 //返回影响的行数
157 public static int RunExecute(string procName)
158 {
159 SqlConnection Conn = ReturnConn();
160 SqlCommand Cmd = CreateCmd(procName, Conn);
161 int intResult = Cmd.ExecuteNonQuery();
162 Conn.Close();
163 return intResult;
164 }
165
166 public static int RunExecute(string procName,SqlParameter[] prams)
167 {
168 SqlConnection Conn = ReturnConn();
169 SqlCommand Cmd = CreateCmd(procName,prams, Conn);
170 int intResult = Cmd.ExecuteNonQuery();
171 Conn.Close();
172 return intResult;
173 }
174
175 //返回结果集中的第一行第一列
176 public static object RunExecuteScalar(string procName)
177 {
178 SqlConnection Conn = ReturnConn();
179 SqlCommand Cmd = CreateCmd(procName, Conn);
180 object objResult = Cmd.ExecuteScalar();
181 Conn.Close();
182 return objResult;
183 }
184
185 public static object RunExecuteScalar(string procName, SqlParameter[] prams)
186 {
187 SqlConnection Conn = ReturnConn();
188 SqlCommand Cmd = CreateCmd(procName, prams, Conn);
189 object objResult = Cmd.ExecuteScalar();
190 Conn.Close();
191 return objResult;
192 }
193
194}
195
