DBHelp连接数据库

来源：互联网发布：js文件怎么下载编辑：程序博客网时间：2024/05/03 00:18

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace MyBankDAL
{
    /// <summary>
    /// 连接数据库类
    /// </summary>
    public class DBHelp
    {
        private static SqlConnection connection;
        /// <summary>
        /// 获取数据库连接
        /// </summary>
        public static SqlConnection Connection
        {
            get
            {
                string 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;
            }
        }
        /// <summary>
        /// 执行增删改查命令，返回影响的行数
        /// </summary>
        /// <param Name="safeSql"></param>
        /// <returns></returns>
        public static int ExecuteCommand(string safeSql)
        {
            SqlCommand cmd = new SqlCommand(safeSql,Connection);
            int result = cmd.ExecuteNonQuery();
            return result;
        }
        /// <summary>
        /// 带参数的sql语句，执行增删改查命令，返回影响的行数
        /// </summary>
        /// <param Name="sql"></param>
        /// <param Name="values"></param>
        /// <returns></returns>
        public static int ExecuteCommand(string sql ,params SqlParameter[] values)
        {
            SqlCommand cmd = new SqlCommand(sql,Connection);
            cmd.Parameters.AddRange(values);
            return cmd.ExecuteNonQuery();
        }
        /// <summary>
        /// 返回单行值查询结果第一行第一列
        /// </summary>
        /// <param name="safeSql"></param>
        /// <returns></returns>
        public static int GetScaler(string safeSql)
        {
            SqlCommand cmd = new SqlCommand(safeSql,Connection);
            int result = Convert.ToInt32(cmd.ExecuteScalar());
            return result;
        }
        public static int GetScaler(params SqlParameter[] values)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = Connection;
            cmd.CommandText = ""; //存储过程
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddRange(values);
            int result = Convert.ToInt32(cmd.ExecuteScalar());
            return result;
        }
        public static int GetScaler(string sql,params SqlParameter[] values)
        {
            SqlCommand cmd = new SqlCommand(sql,Connection);
            cmd.Parameters.AddRange(values);
            int result = Convert.ToInt32(cmd.ExecuteScalar());
            return result;
        }
        /// <summary>
        /// 根据SQL语句返回SqlDataReader
        /// </summary>
        /// <param Name="safeSql"></param>
        /// <returns></returns>
        public static SqlDataReader GetReader(string safeSql)
        {
            SqlCommand cmd = new SqlCommand(safeSql,Connection);
            SqlDataReader reader = cmd.ExecuteReader();
            return reader;
        }
        /// <summary>
        /// 带参数，根据SQL语句返回SqlDataReader
        /// </summary>
        /// <param Name="sql"></param>
        /// <param Name="values"></param>
        /// <returns></returns>
        public static SqlDataReader GetReader(string sql, params SqlParameter[] values)
        {
            SqlCommand cmd = new SqlCommand(sql,Connection);
            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,Connection);
            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,Connection);
            cmd.Parameters.AddRange(values);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            return ds.Tables[0];
        }
    }
}