1 public class DBHelper
2 {
3 public DBHelper(string conn)
4 {
5 ConnectionString = ConfigurationManager.ConnectionStrings[conn].ToString();
6 }
7 public string ConnectionString { get; set; }
8
9 public SqlDataReader GetReader(string sql, SqlParameter[] param = null, CommandType cmdType = CommandType.Text)
10 {
11 SqlDataReader reader = null;
12 SqlConnection conn = new SqlConnection(ConnectionString);
13 SqlCommand cmd = new SqlCommand(sql, conn);
14 cmd.CommandType = cmdType;
15 if (param != null)
16 {
17 cmd.Parameters.AddRange(param);
18 }
19 conn.Open();
20 //CommandBehavior.CloseConnection的作用是如果关联的DataReader对象关闭，则连接自动关闭
21 reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
22 return reader;
23 }
24 public DataSet GetDataSet(string sql, SqlParameter[] param = null, CommandType cmdType = CommandType.Text)
25 {
26 DataSet dt = new DataSet(); ;
27 using (SqlConnection conn = new SqlConnection(ConnectionString))
28 {
29 using (SqlDataAdapter adapt = new SqlDataAdapter(sql, conn))
30 {
31 adapt.SelectCommand.CommandType = cmdType;
32 if (param != null)
33 {
34 adapt.SelectCommand.Parameters.AddRange(param);
35 }
36 adapt.Fill(dt);
37 }
38 conn.Close();
39 }
40 return dt;
41 }
42 public DataTable GetTable(string sql, SqlParameter[] param = null, CommandType cmdType = CommandType.Text)
43 {
44 DataTable dt = new DataTable(); ;
45 using (SqlConnection conn = new SqlConnection(ConnectionString))
46 {
47 using (SqlDataAdapter adapt = new SqlDataAdapter(sql, conn))
48 {
49 adapt.SelectCommand.CommandType = cmdType;
50 if (param != null)
51 {
52 adapt.SelectCommand.Parameters.AddRange(param);
53 }
54 adapt.Fill(dt);
55 }
56 conn.Close();
57 }
58 return dt;
59 }
60 public object GetValue(string sql, SqlParameter[] param = null, CommandType cmdType = CommandType.Text)
61 {
62 object o = null;
63 using (SqlConnection conn = new SqlConnection(ConnectionString))
64 {
65 using (SqlCommand cmd = new SqlCommand(sql, conn))
66 {
67 cmd.CommandType = cmdType;
68 if (param != null)
69 {
70 cmd.Parameters.AddRange(param);
71 }
72 conn.Open();
73 o = cmd.ExecuteScalar();
74 }
75 conn.Close();
76 }
77 return o;
78 }
79 // 返回影响行数
80 public int ExcuteSQL(string sql, SqlParameter[] param = null, CommandType cmdType = CommandType.Text)
81 {
82 int i = 0;
83 using (SqlConnection conn = new SqlConnection(ConnectionString))
84 {
85 using (SqlCommand cmd = new SqlCommand(sql, conn))
86 {
87 cmd.CommandType = cmdType;
88 if (param != null)
89 {
90 cmd.Parameters.AddRange(param);
91 }
92 conn.Open();
93 i = cmd.ExecuteNonQuery();
94 }
95 conn.Close();
96 }
97 return i;
98 }
99 // 返回第一行第一列
100 public int ExcuteScalarSQL(string sql, SqlParameter[] param = null, CommandType cmdType = CommandType.Text)
101 {
102 int i = 0;
103 using (SqlConnection conn = new SqlConnection(ConnectionString))
104 {
105 using (SqlCommand cmd = new SqlCommand(sql, conn))
106 {
107 cmd.CommandType = cmdType;
108 if (param != null)
109 {
110 cmd.Parameters.AddRange(param);
111 }
112 conn.Open();
113 i = Convert.ToInt32(cmd.ExecuteScalar());
114 }
115 conn.Close();
116 }
117 return i;
118 }
119
120 // 批量更新数据
121 public void BulkToDB(DataTable source, string targetTable)
122 {
123 if (source != null && source.Rows.Count != 0)
124 {
125 using (SqlConnection conn = new SqlConnection(ConnectionString))
126 {
127 using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn))
128 {
129 bulkCopy.DestinationTableName = targetTable;
130 bulkCopy.BatchSize = source.Rows.Count;
131 for (int i = 0; i < source.Columns.Count; i++)
132 {
133 bulkCopy.ColumnMappings.Add(source.Columns[i].ColumnName, source.Columns[i].ColumnName);
134 }
135 conn.Open();
136 bulkCopy.WriteToServer(source);
137 bulkCopy.Close();
138 }
139 conn.Close();
140 }
141 }
142 }
143 }
