1
using System;
2
using System.Data;
3
using System.Data.SqlClient;
4
using System.Configuration;
5
6
namespace DbBase
7

{
8
9
public abstract class Base
10

{
11
12
"Fields of base calss"#region "Fields of base calss"
13
14
protected static string strConn = ConfigurationSettings.AppSettings["strConnection"];
15
16
protected static string strSQL;
17
18
#endregion
19
20
21
"Properties of base class"#region "Properties of base class"
22
}
23
24
#endregion
25
26
27
"Functions of base class"#region "Functions of base class"
28
public Base()
29

{
30
//
31
// TODO: Add constructor logic here
32
//
33
}
34
35
/**//// <summary>
36
/// executing SQL commands
37
/// </summary>
38
/// <param name="strSQL">string</param>
39
/// <returns>return int</returns>
40
protected static int ExecuteSql(string strSQL)
41

{
42
SqlConnection myCn = new SqlConnection(strConn);
43
SqlCommand myCmd = new SqlCommand(strSQL,myCn);
44
try
45

{
46
myCn.Open();
47
myCmd.ExecuteNonQuery();
48
return 0;
49
}
50
catch(System.Data.SqlClient.SqlException e)
51

{
52
throw new Exception(e.Message);
53
}
54
finally
55

{
56
myCmd.Dispose();
57
myCn.Close();
58
}
59
}
60
61
62
/**//// <summary>
63
///executing SQL commands
64
/// </summary>
65
/// <param name="strSQL">要执行的SQL语句,为字符串类型string</param>
66
/// <returns>返回执行情况,整形int</returns>
67
protected static int ExecuteSqlEx(string strSQL)
68

{
69
SqlConnection myCn = new SqlConnection(strConn);
70
SqlCommand myCmd = new SqlCommand(strSQL,myCn);
71
72
try
73

{
74
myCn.Open();
75
SqlDataReader myReader = myCmd.ExecuteReader();
76
if(myReader.Read())
77

{
78
return 0;
79
}
80
else
81

{
82
throw new Exception("Value Unavailable!");
83
}
84
}
85
catch(System.Data.SqlClient.SqlException e)
86

{
87
throw new Exception(e.Message);
88
}
89
finally
90

{
91
myCmd.Dispose();
92
myCn.Close();
93
}
94
}
95
96
97
/**//// <summary>
98
/// get dataset
99
/// </summary>
100
/// <param name="strSQL">(string)</param>
101
/// <returns>(DataSet)</returns>
102
protected static DataSet ExecuteSql4Ds(string strSQL)
103

{
104
SqlConnection myCn = new SqlConnection(strConn);
105
try
106

{
107
myCn.Open();
108
SqlDataAdapter sda = new SqlDataAdapter(strSQL,myCn);
109
DataSet ds = new DataSet("ds");
110
sda.Fill(ds);
111
return ds;
112
}
113
catch(System.Data.SqlClient.SqlException e)
114

{
115
throw new Exception(e.Message);
116
}
117
finally
118

{
119
myCn.Close();
120
}
121
}
122
123
124
/**//// <summary>
125
/// get single value
126
/// </summary>
127
/// <param name="strSQL">(string)</param>
128
/// <returns>(int)</returns>
129
protected static int ExecuteSql4Value(string strSQL)
130

{
131
SqlConnection myCn = new SqlConnection(strConn);
132
SqlCommand myCmd = new SqlCommand(strSQL,myCn);
133
try
134

{
135
myCn.Open();
136
object r = myCmd.ExecuteScalar();
137
if(Object.Equals(r,null))
138

{
139
throw new Exception("value unavailable!");
140
}
141
else
142

{
143
return (int)r;
144
}
145
}
146
catch(System.Data.SqlClient.SqlException e)
147

{
148
throw new Exception(e.Message);
149
}
150
finally
151

{
152
myCmd.Dispose();
153
myCn.Close();
154
}
155
}
156
157
158
/**//// <summary>
159
/// get object
160
/// </summary>
161
/// <param name="strSQL">(string)</param>
162
/// <returns>(object)</returns>
163
protected static object ExecuteSql4ValueEx(string strSQL)
164

{
165
SqlConnection myCn = new SqlConnection(strConn);
166
SqlCommand myCmd = new SqlCommand(strSQL,myCn);
167
try
168

{
169
myCn.Open();
170
object r = myCmd.ExecuteScalar();
171
if(Object.Equals(r,null))
172

{
173
throw new Exception("object unavailable!");
174
}
175
else
176

{
177
return r;
178
}
179
}
180
catch(System.Data.SqlClient.SqlException e)
181

{
182
throw new Exception(e.Message);
183
}
184
finally
185

{
186
myCmd.Dispose();
187
myCn.Close();
188
}
189
}
190
191
192
/**//// <summary>
193
/// execute multipul SQL commands
194
/// </summary>
195
/// <param name="strSQLs">string</param>
196
/// <returns>int</returns>
197
protected static int ExecuteSqls(string[] strSQLs)
198

{
199
SqlConnection myCn = new SqlConnection(strConn);
200
SqlCommand myCmd = new SqlCommand();
201
int j=strSQLs.Length;
202
203
try
204

{
205
myCn.Open();
206
}
207
catch(System.Data.SqlClient.SqlException e)
208

{
209
throw new Exception(e.Message);
210
}
211
SqlTransaction myTrans = myCn.BeginTransaction();
212
213
try
214

{
215
myCmd.Connection = myCn;
216
myCmd.Transaction = myTrans;
217
218
foreach(string str in strSQLs)
219

{
220
myCmd.CommandText = str;
221
myCmd.ExecuteNonQuery();
222
}
223
myTrans.Commit();
224
return 0;
225
}
226
catch(System.Data.SqlClient.SqlException e)
227

{
228
myTrans.Rollback();
229
throw new Exception(e.Message);
230
}
231
finally
232

{
233
myCmd.Dispose();
234
myCn.Close();
235
}
236
}
237
238
#endregion
239
}
240
}
241
using System;2
using System.Data;3
using System.Data.SqlClient;4
using System.Configuration;5

6
namespace DbBase7


{8

9
public abstract class Base10


{11

12

"Fields of base calss"#region "Fields of base calss"13

14
protected static string strConn = ConfigurationSettings.AppSettings["strConnection"];15

16
protected static string strSQL;17

18
#endregion19

20

21

"Properties of base class"#region "Properties of base class"22
}23

24
#endregion25

26

27

"Functions of base class"#region "Functions of base class"28
public Base()29


{30
//31
// TODO: Add constructor logic here32
//33
}34

35

/**//// <summary>36
/// executing SQL commands37
/// </summary>38
/// <param name="strSQL">string</param>39
/// <returns>return int</returns>40
protected static int ExecuteSql(string strSQL)41


{42
SqlConnection myCn = new SqlConnection(strConn); 43
SqlCommand myCmd = new SqlCommand(strSQL,myCn);44
try45


{46
myCn.Open();47
myCmd.ExecuteNonQuery();48
return 0;49
}50
catch(System.Data.SqlClient.SqlException e)51


{ 52
throw new Exception(e.Message);53
}54
finally55


{56
myCmd.Dispose();57
myCn.Close();58
}59
}60

61

62

/**//// <summary>63
///executing SQL commands64
/// </summary>65
/// <param name="strSQL">要执行的SQL语句,为字符串类型string</param>66
/// <returns>返回执行情况,整形int</returns>67
protected static int ExecuteSqlEx(string strSQL)68


{69
SqlConnection myCn = new SqlConnection(strConn); 70
SqlCommand myCmd = new SqlCommand(strSQL,myCn);71

72
try73


{74
myCn.Open(); 75
SqlDataReader myReader = myCmd.ExecuteReader();76
if(myReader.Read())77


{78
return 0;79
} 80
else81


{82
throw new Exception("Value Unavailable!");83
}84
}85
catch(System.Data.SqlClient.SqlException e)86


{ 87
throw new Exception(e.Message);88
}89
finally90


{91
myCmd.Dispose();92
myCn.Close();93
}94
}95

96

97

/**//// <summary>98
/// get dataset99
/// </summary>100
/// <param name="strSQL">(string)</param>101
/// <returns>(DataSet)</returns>102
protected static DataSet ExecuteSql4Ds(string strSQL)103


{104
SqlConnection myCn = new SqlConnection(strConn); 105
try106


{107
myCn.Open();108
SqlDataAdapter sda = new SqlDataAdapter(strSQL,myCn);109
DataSet ds = new DataSet("ds");110
sda.Fill(ds);111
return ds;112
}113
catch(System.Data.SqlClient.SqlException e)114


{ 115
throw new Exception(e.Message);116
}117
finally118


{119
myCn.Close();120
}121
}122

123

124

/**//// <summary>125
/// get single value126
/// </summary>127
/// <param name="strSQL">(string)</param>128
/// <returns>(int)</returns>129
protected static int ExecuteSql4Value(string strSQL)130


{131
SqlConnection myCn = new SqlConnection(strConn); 132
SqlCommand myCmd = new SqlCommand(strSQL,myCn);133
try134


{135
myCn.Open();136
object r = myCmd.ExecuteScalar();137
if(Object.Equals(r,null))138


{139
throw new Exception("value unavailable!");140
}141
else142


{143
return (int)r;144
} 145
}146
catch(System.Data.SqlClient.SqlException e)147


{ 148
throw new Exception(e.Message);149
}150
finally151


{152
myCmd.Dispose();153
myCn.Close();154
}155
} 156

157

158

/**//// <summary>159
/// get object160
/// </summary>161
/// <param name="strSQL">(string)</param>162
/// <returns>(object)</returns>163
protected static object ExecuteSql4ValueEx(string strSQL)164


{165
SqlConnection myCn = new SqlConnection(strConn); 166
SqlCommand myCmd = new SqlCommand(strSQL,myCn);167
try168


{169
myCn.Open();170
object r = myCmd.ExecuteScalar();171
if(Object.Equals(r,null))172


{173
throw new Exception("object unavailable!");174
}175
else176


{177
return r;178
} 179
}180
catch(System.Data.SqlClient.SqlException e)181


{ 182
throw new Exception(e.Message);183
}184
finally185


{186
myCmd.Dispose();187
myCn.Close();188
}189
}190

191

192

/**//// <summary>193
/// execute multipul SQL commands 194
/// </summary>195
/// <param name="strSQLs">string</param>196
/// <returns>int</returns>197
protected static int ExecuteSqls(string[] strSQLs)198


{199
SqlConnection myCn = new SqlConnection(strConn); 200
SqlCommand myCmd = new SqlCommand(); 201
int j=strSQLs.Length;202

203
try204


{205
myCn.Open(); 206
}207
catch(System.Data.SqlClient.SqlException e)208


{209
throw new Exception(e.Message);210
}211
SqlTransaction myTrans = myCn.BeginTransaction();212

213
try214


{ 215
myCmd.Connection = myCn; 216
myCmd.Transaction = myTrans;217

218
foreach(string str in strSQLs)219


{220
myCmd.CommandText = str;221
myCmd.ExecuteNonQuery();222
}223
myTrans.Commit();224
return 0;225
}226
catch(System.Data.SqlClient.SqlException e)227


{ 228
myTrans.Rollback();229
throw new Exception(e.Message);230
}231
finally232


{233
myCmd.Dispose();234
myCn.Close();235
}236
}237

238
#endregion239
}240
}241

浙公网安备 33010602011771号