using System;
2 using System.Collections.Generic;
3 using System.Configuration;
4 using System.Data.SqlClient;
5 using System.Linq;
6 using System.Text;
7 using System.Threading.Tasks;
8 using System.Data;
9
10 namespace Students.DAL
11 {
12 public class DBHelper
13 {
14 public static readonly string conn = ConfigurationManager.ConnectionStrings["ClassRoomConnectionString"].ToString();
15 public static SqlConnection connection = new SqlConnection(DBHelper.conn);
16
17 /// <summary>
18 /// 增删改数据
19 /// </summary>
20 /// <param name="sql"></param>
21 /// <returns></returns>
22 public static int ExecuteNonQuery(string sql, SqlParameter[] par)
23 {
24 try
25 {
26 connection.Open(); //打开数据库连接
27 SqlCommand comm = new SqlCommand(sql, connection);
28 //comm.CommandType = System.Data.CommandType.StoredProcedure;
29 comm.Parameters.AddRange(par);
30 return comm.ExecuteNonQuery();
31 }
32 catch
33 {
34 throw;
35 }
36 finally
37 {
38 connection.Close();
39 }
40 }
41 /// <summary>
42 /// 增删改数据
43 /// </summary>
44 /// <param name="sql"></param>
45 /// <returns></returns>
46 public static int ExecuteNonQuery(string sql, SqlParameter[] par, string sql2, SqlParameter[] par2)
47 {
48 connection.Open(); //打开数据库连接
49 SqlTransaction tra = connection.BeginTransaction();
50 try
51 {
52 SqlCommand comm = new SqlCommand(sql, connection);
53 SqlCommand comm2 = new SqlCommand(sql2, connection);
54 comm.Parameters.AddRange(par);
55 comm2.Parameters.AddRange(par2);
56 comm.Transaction = tra;
57 comm2.Transaction = tra;
58 int num1 = comm.ExecuteNonQuery();
59 int num2 = comm2.ExecuteNonQuery();
60 int num = comm.ExecuteNonQuery() + comm2.ExecuteNonQuery();
61 tra.Commit();
62 return num;
63 }
64 catch
65 {
66 tra.Rollback();
67 throw;
68 }
69 finally
70 {
71 connection.Close();
72 }
73 }
74 /// <summary>
75 /// 查询数据
76 /// </summary>
77 /// <param name="sql"></param>
78 /// <returns></returns>
79 public static SqlDataReader ExecuteReader(string sql, SqlParameter[] par)
80 {
81 try
82 {
83 connection.Open(); //打开数据库连接
84 SqlCommand comm = new SqlCommand(sql, connection);
85 //comm.CommandType = System.Data.CommandType.StoredProcedure;
86 comm.Parameters.AddRange(par);
87 return comm.ExecuteReader(CommandBehavior.CloseConnection);
88 }
89 catch
90 {
91 throw;
92 }
93 }
94 /// <summary>
95 /// 查询数据
96 /// </summary>
97 /// <param name="sql"></param>
98 /// <returns></returns>
99 public static SqlDataReader ExecuteReader(string sql)
100 {
101 try
102 {
103 connection.Open(); //打开数据库连接
104 SqlCommand comm = new SqlCommand(sql, connection);
105 return comm.ExecuteReader(CommandBehavior.CloseConnection);
106 }
107 catch
108 {
109 throw;
110 }
111 }
112 /// <summary>
113 /// 返回单个值
114 /// </summary>
115 /// <param name="sql"></param>
116 /// <returns></returns>
117 public static object ExecuteScalar(string sql, SqlParameter[] par)
118 {
119 try
120 {
121 connection.Open(); //打开数据库连接
122 SqlCommand comm = new SqlCommand(sql, connection);
123 //comm.CommandType = System.Data.CommandType.StoredProcedure;
124 comm.Parameters.AddRange(par);
125 return comm.ExecuteScalar();
126 }
127 catch
128 {
129 throw;
130 }
131 finally
132 {
133 connection.Close();
134 }
135 }
136 }
137 }
<?xml version="1.0" encoding="utf-8" ?>
2 <configuration>
3 <startup>
4 <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
5 </startup>
6 <connectionStrings>
7 <add name ="ClassRoomConnectionString"
8 connectionString="Data Source=.;Initial Catalog=StudentDB;User ID=sa;Password=sa"
9 providerName="System.Data.SqlClient"/>
10 </connectionStrings>
11 </configuration>
