SqlServerDatabase.cs

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

public class SqlServerDatabase
{
    #region 变量
    private static string _connectionString;
    private static SqlConnection _connection;
    private static SqlCommand _command;

    #endregion

    #region 实例化
    private static SqlServerDatabase _instance;
    public static SqlServerDatabase Instance => _instance ?? (_instance = new SqlServerDatabase());
    public SqlServerDatabase()
    {
        _instance = this;
    }
    public SqlServerDatabase(string connName)
    {
        _instance = this;
        Init(connName);
    }
    public SqlServerDatabase(string dataSource, string initialCatalog, string username, string password)
    {
        _instance = this;
        Init(dataSource, initialCatalog, username, password);
    }

    #endregion

    #region 公共调用方法
    //初始化
    public void Init(string connName)
    {
        _connectionString = ConfigurationManager.ConnectionStrings[connName].ToString();
    }

    //初始化
    public void Init(string dataSource, string initialCatalog, string username, string password)
    {
        _connectionString = $"Data Source={dataSource};Initial Catalog={initialCatalog};user id={username};pwd={password}";
    }

    //打开
    public void Open()
    {
        _connection = new SqlConnection(_connectionString);
        _connection.Open();
        _command = new SqlCommand { Connection = _connection };
    }

    //关闭
    public void Close() => _connection.Close();

    public DataTable GetTable(string sql)
    {
        var dt = new DataTable();
        try
        {
            _command.CommandText = sql;
            var dr = _command.ExecuteReader();
            dt.Load(dr);
            dr.Close();
        }
        catch
        {
            // ignored
        }
        return dt;
    }

    public int Perform(string sql)
    {
        try
        {
            _command.CommandText = sql;
            return _command.ExecuteNonQuery();
        }
        catch
        {
            return 0;
            // ignored
        }
    }

    public int Perform(string sql, SqlParameter[] parameters = null)
    {
        var result = -1;
        try
        {
            _command.CommandText = sql;
            if (parameters != null)
            {
                _command.Parameters.AddRange(parameters);
            }
            result = _command.ExecuteNonQuery();
        }
        catch
        {
            // ignored
        }
        return result;
    }

    public bool Perform(string sql, out string err)
    {
        var result = false;
        err = null;
        try
        {
            _command.CommandText = sql;
            _command.ExecuteNonQuery();
            result = true;
        }
        catch (Exception ex)
        {
            err = ex.Message;
        }
        return result;
    }

    public string Query(string sql)
    {
        string result = null;
        try
        {
            _command.CommandText = sql;
            result = _command.ExecuteScalar().ToString();
        }
        catch
        {
            // ignored
        }
        return result;
    }

    #endregion

}