1 public abstract class DBHelper
2 {
3 public static string connectionString = @"DATA SOURCE=.\sqldata;UID=sa;PWD=bkin123;DATABASE=bkintest";//ConfigurationManager.ConnectionStrings["database"].ConnectionString;
4 public DBHelper()
5 {
6 //connectionString = @"DATA SOURCE=(local);UID=sa;PWD=sa;DATABASE=db";
7 }
8
9 #region 不带参数
10 //执行不带参数数据记录的增、删、改操作，并返回执行后的结果值
11 public static int ExecuteCommand(string safeSql)
12 {
13 using (SqlConnection connection = new SqlConnection(connectionString))
14 {
15 using (SqlCommand cmd = new SqlCommand(safeSql, connection))
16 {
17 try
18 {
19 connection.Open();
20 int result = cmd.ExecuteNonQuery();
21 return result;
22 }
23 catch (System.Data.SqlClient.SqlException e)
24 {
25 connection.Close();
26 throw e;
27 }
28 }
29 }
30 }
31
32 //返回执行不带参数SQL语句后中结果集的第一行第一列
33 public static object GetScalar(string safeSql)
34 {
35 using (SqlConnection connection = new SqlConnection(connectionString))
36 {
37 using (SqlCommand cmd = new SqlCommand(safeSql, connection))
38 {
39 try
40 {
41 connection.Open();
42 object result = cmd.ExecuteScalar();
43 return result;
44 }
45 catch (System.Data.SqlClient.SqlException e)
46 {
47 connection.Close();
48 throw e;
49 }
50 }
51 }
52 }
53
54 //返回执行不带参数SQL语句后的结果数据表
55 public static DataTable GetDataTable(string safeSql)
56 {
57 using (SqlConnection connection = new SqlConnection(connectionString))
58 {
59 using (SqlCommand cmd = new SqlCommand(safeSql, connection))
60 {
61 try
62 {
63 connection.Open();
64 DataSet ds = new DataSet();
65 SqlDataAdapter da = new SqlDataAdapter(cmd);
66 da.Fill(ds);
67 return ds.Tables[0];
68 }
69 catch (System.Data.SqlClient.SqlException e)
70 {
71 connection.Close();
72 throw e;
73 }
74 }
75 }
76 }
77 #endregion
78
79 #region 带参数
80 //执行带参数数据记录的增、删、改操作，返回执行后的结果值
81 public static int ExecuteCommand(string safeSql, params SqlParameter[] values)
82 {
83 using (SqlConnection connection = new SqlConnection(connectionString))
84 {
85 using (SqlCommand cmd = new SqlCommand(safeSql, connection))
86 {
87 try
88 {
89 connection.Open();
90 cmd.Parameters.AddRange(values);
91 return cmd.ExecuteNonQuery();
92 }
93 catch (System.Data.SqlClient.SqlException e)
94 {
95 connection.Close();
96 throw e;
97 }
98 }
99 }
100 }
101
102 //返回执行带参数SQL语句后中结果集的第一行第一列
103 public static object GetScalar(string safeSql, params SqlParameter[] values)
104 {
105 using (SqlConnection connection = new SqlConnection(connectionString))
106 {
107 using (SqlCommand cmd = new SqlCommand(safeSql, connection))
108 {
109 try
110 {
111 connection.Open();
112 cmd.Parameters.AddRange(values);
113 object result = cmd.ExecuteScalar();
114 return result;
115 }
116 catch (System.Data.SqlClient.SqlException e)
117 {
118 connection.Close();
119 throw e;
120 }
121 }
122 }
123 }
124
125 //返回执行带参数SQL语句后的结果数据表
126 public static DataTable GetDataTable(string safeSql, params SqlParameter[] values)
127 {
128 using (SqlConnection connection = new SqlConnection(connectionString))
129 {
130 using (SqlCommand cmd = new SqlCommand(safeSql, connection))
131 {
132 try
133 {
134 connection.Open();
135 DataSet ds = new DataSet();
136 cmd.Parameters.AddRange(values);
137 SqlDataAdapter da = new SqlDataAdapter(cmd);
138 da.Fill(ds);
139 return ds.Tables[0];
140 }
141 catch (System.Data.SqlClient.SqlException e)
142 {
143 connection.Close();
144 throw e;
145 }
146 }
147 }
148 }
149 #endregion
150 }
