DBHerpler操作类

1. using System;
2. using System.Collections;
3. using System.Collections.Generic;
4. using System.Text;
5. using System.Data;
6. using System.Data.SqlClient;
7. using System.Configuration;
10. namespace Aptech.DAL
11. {
12.     public static class DBHelper
13.     {
14.         private static SqlConnection connection;//字段
15.         public static SqlConnection Connection//属性
16.         {
17.             get 
18.             {
19.                 //string connectionString = ConfigurationManager.ConnectionStrings["MyOfficeConnectionString"].ConnectionString;
20.                 string connectionString = "Data Source=.;Initial Catalog=stu;Integrated Security=True";
21.                 connection = new SqlConnection(connectionString);
22.                 if (connection == null)
23.                 {                    
24.                     connection.Open();
25.                 }
26.                 else if (connection.State == System.Data.ConnectionState.Closed)
27.                 {
28.                     connection.Open();
29.                 }
30.                 else if (connection.State == System.Data.ConnectionState.Broken)
31.                 {
32.                     connection.Close();
33.                     connection.Open();
34.                 }
35.                 return connection;
36.             }
37.         }
38.         //执行SQL语句，返回所影响的行数
39.         public static int ExecuteCommand(string safeSql)
40.         {
41.             SqlCommand cmd = new SqlCommand(safeSql, Connection);
42.             int result = cmd.ExecuteNonQuery();
43.             return result;
44.         }
45.         //执行带参的SQL语句，返回所影响的行数
46.         public static int ExecuteCommand(string sql, params SqlParameter[] values)
47.         {
48.             SqlCommand cmd = new SqlCommand(sql, Connection);
49.             cmd.Parameters.AddRange(values);
50.             return cmd.ExecuteNonQuery();
51.         }
52.         //执行SQL ,返回第一行第一列的字符串形式
53.         public static string ReturnStringScalar(string safeSql)
54.         {
55.             SqlCommand cmd = new SqlCommand(safeSql, Connection);
56.             try
57.             {
58.                 string result = cmd.ExecuteScalar().ToString();
59.                 return result;
60.             }
61.             catch (Exception e)
62.             {
63.                 return "0";
64.             }
65.             connection.Close();
66.         }
67.         //执行SQL ,返回第一行第一列的整数形式
68.         public static int GetScalar(string safeSql)
69.         {
70.             SqlCommand cmd = new SqlCommand(safeSql, Connection);
71.             try
72.             {
73.                 int result = Convert.ToInt32(cmd.ExecuteScalar());
74.                 return result;
75.             }
76.             catch (Exception e)
77.             { 
78.                 return 0;
79.             }
80.             connection.Close();
81.         }
82.         //执行带参的SQL,返回影响的第一行第一列
83.         public static int GetScalar(string sql, params SqlParameter[] values)
84.         {
85.             SqlCommand cmd = new SqlCommand(sql, Connection);
86.             cmd.Parameters.AddRange(values);
87.             int result = Convert.ToInt32(cmd.ExecuteScalar());
88.             return result;
89.             connection.Close();
90.         }
91.         //执行SQL,返回数据流
92.         public static SqlDataReader GetReader(string safeSql)
93.         {
94.             SqlCommand cmd = new SqlCommand(safeSql, Connection);
95.             SqlDataReader reader = cmd.ExecuteReader();
96.             return reader;
97.             reader.Close();
98.             reader.Dispose();
99.         }
100.         //执行带参SQL,返回数据流
101.         public static SqlDataReader GetReader(string sql, params SqlParameter[] values)
102.         {
103.             SqlCommand cmd = new SqlCommand(sql, Connection);
104.             cmd.Parameters.AddRange(values);
105.             SqlDataReader reader = cmd.ExecuteReader();
106.             return reader;
107.             reader.Close();
108.             reader.Dispose();
109.         }
110.         //执行SQL,返回数据集
111.         public static DataTable GetDataSet(string safeSql)
112.         {
113.             DataSet ds = new DataSet();
114.             SqlCommand cmd = new SqlCommand(safeSql, Connection);
115.             SqlDataAdapter da = new SqlDataAdapter(cmd);
116.             da.Fill(ds);
117.             connection.Close();
118.             connection.Dispose();
119.             return ds.Tables[0];
120.         }
121.         //执行带参SQL,返回数据集
122.         public static DataTable GetDataSet(string sql, params SqlParameter[] values)
123.         {
124.             DataSet ds = new DataSet();
125.             SqlCommand cmd = new SqlCommand(sql, Connection);
126.             cmd.Parameters.AddRange(values);
127.             SqlDataAdapter da = new SqlDataAdapter(cmd);
128.             da.Fill(ds);
129.             connection.Close();
130.             connection.Dispose();
131.             return ds.Tables[0];           
132.         }
133.     }
134. }