DBHelper 博客分类： C# SQL

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
//数据库访问工具类
namespace userWeb.Util
{
    public static class DBHelper
    {

   //     private static SqlConnection connection;
        public static string connectionString = "Data Source=localhost;Initial Catalog=user_db;User ID=sa;password=abc";
        //public static SqlConnection Connection
        //{
        //    get
        //    {
        //        string connectionString = "Data Source=localhost;Initial Catalog=user_db;User ID=sa;password=abc";
        //        return new SqlConnection(connectionString);
        //        //if (connection == null)
        //        //{
        //        //    connection = new SqlConnection(connectionString);
        //        //    connection.Open();
        //        //}
        //        //else if (connection.State == System.Data.ConnectionState.Closed)
        //        //{
        //        //    connection.Open();
        //        //}
        //        //else if (connection.State == System.Data.ConnectionState.Broken)
        //        //{
        //        //    connection.Close();
        //        //    connection.Open();
        //        //}
        //        //return connection;
        //    }
        //}
        public static SqlConnection getCon()
        {
           
            SqlConnection con = new SqlConnection(connectionString);
            con.Open(); return con;
        }


        public static int ExecuteCommand(string safeSql)
        {
            SqlCommand cmd = new SqlCommand(safeSql, getCon());
            int result = cmd.ExecuteNonQuery();
            return result;
        }

        public static int ExecuteCommand(string sql, params SqlParameter[] values)
        {
            SqlCommand cmd = new SqlCommand(sql, getCon());
            cmd.Parameters.AddRange(values);
            return cmd.ExecuteNonQuery();
        }

        public static int GetScalar(string safeSql)
        {
            SqlCommand cmd = new SqlCommand(safeSql, getCon());
            int result = Convert.ToInt32(cmd.ExecuteScalar());
            return result;
        }

        public static int GetScalar(string sql, params SqlParameter[] values)
        {
            SqlCommand cmd = new SqlCommand(sql, getCon());
            cmd.Parameters.AddRange(values);
            int result = Convert.ToInt32(cmd.ExecuteScalar());
            return result;
        }

        public static SqlDataReader GetReader(string safeSql)
        {
            SqlCommand cmd = new SqlCommand(safeSql, getCon());
            SqlDataReader reader = cmd.ExecuteReader();
            return reader;
        }

        public static SqlDataReader GetReader(string sql, params SqlParameter[] values)
        {
            SqlCommand cmd = new SqlCommand(sql, getCon());
            cmd.Parameters.AddRange(values);
            SqlDataReader reader = cmd.ExecuteReader();
            return reader;
        }

        public static DataTable GetDataSet(string safeSql)
        {
            DataSet ds = new DataSet();
            SqlCommand cmd = new SqlCommand(safeSql, getCon());
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            return ds.Tables[0];
        }

        public static DataTable GetDataSet(string sql, params SqlParameter[] values)
        {
            DataSet ds = new DataSet();
            SqlCommand cmd = new SqlCommand(sql, getCon());
            cmd.Parameters.AddRange(values);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            return ds.Tables[0];
        }

    }
}