自己写的一个DBHelper

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Text;
  4 using System.Data;
  5 using System.Data.SqlClient;
  6 
  7 namespace DAL
  8 {
  9     class DBHelper
 10     {
 11         /// <summary>
 12         /// 连接对象
 13         /// </summary>
 14         private static SqlConnection Conn
 15         {
 16             get
 17             {
 18                 SqlConnection conn = null;
 19                 string connString = "server=.\\sqlexpress;database=MyOffice;uid=sa;pwd=sa;";
 20                 //连接字符串
 21                 if (conn == null)
 22                 {
 23                     conn = new SqlConnection(connString);
 24                     conn.Open();
 25                 }
 26                 if (conn.State == ConnectionState.Closed)
 27                 {
 28                     conn.Open();
 29                 }
 30                 if (conn.State == ConnectionState.Broken)
 31                 {
 32                     conn.Close();
 33                     conn.Open();
 34                 }
 35                 return conn;
 36             }
 37         }
 38         /// <summary>
 39         /// 执行增删改操作
 40         /// </summary>
 41         /// <param name="sql"></param>
 42         /// <returns></returns>
 43         public static int ExecuteNonQuery(string sql)
 44         {
 45             SqlCommand comm = new SqlCommand(sql, Conn);
 46             return comm.ExecuteNonQuery();
 47         }
 48 
 49         /// <summary>
 50         /// 执行带参数的增删改操作
 51         /// </summary>
 52         /// <param name="sql"></param>
 53         /// <param name="para"></param>
 54         /// <returns></returns>
 55         public static int ExecuteNonQuery(string sql,SqlParameter[] para)
 56         {
 57             SqlCommand comm = new SqlCommand(sql, Conn);
 58             comm.Parameters.AddRange(para);
 59             return comm.ExecuteNonQuery();
 60         }
 61 
 62         /// <summary>
 63         /// 执行查询操作
 64         /// </summary>
 65         /// <param name="sql"></param>
 66         /// <returns></returns>
 67         public static DataTable ExecuteQuery(string sql)
 68         {
 69             SqlDataAdapter sda = new SqlDataAdapter(sql, Conn);
 70             DataSet ds = new DataSet();
 71             sda.Fill(ds);
 72             return ds.Tables[0];
 73         }
 74 
 75         /// <summary>
 76         /// 执行带参数的查询操作
 77         /// </summary>
 78         /// <param name="sql"></param>
 79         /// <param name="para"></param>
 80         /// <returns></returns>
 81         public static DataTable ExecuteQuery(string sql,SqlParameter[] para)
 82         {
 83             SqlCommand comm = new SqlCommand(sql, Conn);
 84             comm.Parameters.AddRange(para);
 85             SqlDataAdapter sda = new SqlDataAdapter(comm);
 86             DataSet ds = new DataSet();
 87             sda.Fill(ds);
 88             return ds.Tables[0];
 89         }
 90 
 91         /// <summary>
 92         /// 执行查询，返回首行首列数据
 93         /// </summary>
 94         /// <param name="sql"></param>
 95         /// <returns></returns>
 96         public static object ExecuteScalar(string sql)
 97         {
 98             SqlCommand comm = new SqlCommand(sql, Conn);
 99             return comm.ExecuteScalar();
100         }
101 
102         /// <summary>
103         /// 执行带参数的查询，返回首行首列数据
104         /// </summary>
105         /// <param name="sql"></param>
106         /// <param name="para"></param>
107         /// <returns></returns>
108         public static object ExecuteScalar(string sql,SqlParameter[] para)
109         {
110             SqlCommand comm = new SqlCommand(sql, Conn);
111             comm.Parameters.AddRange(para);
112             return comm.ExecuteScalar();
113         }
114     }
115 }
116