SqlServer数据库连接帮助类

帮助类

/// <summary>
/// 配置获取
/// </summary>

public class DBConfig
{
    public static string getBasicConn()
    {
        return ConfigurationManager.ConnectionStrings["SqlServerDataSource"].ConnectionString;
    }

    public static string getLteConn()
    {
        return ConfigurationManager.ConnectionStrings["lte"].ConnectionString;
    }
}

/// <summary>
/// 数据库帮助类
/// </summary>
public class SqlDBHelper
{
    /// <summary>
    /// 链接字符串
    /// </summary>
    private string connectionString;

    public SqlDBHelper()
    {
        this.connectionString = DBConfig.getBasicConn();
    }

    public SqlDBHelper(string connectionString)
    {
        this.connectionString = connectionString;
    }

    /// <summary>
    /// 执行SQL语句
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public int ExecuteSqlWithRet(string sql)
    {
        try
        {
            using (SqlConnection connection = new SqlConnection(this.connectionString))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = sql;
                connection.Open();
                int result = Convert.ToInt32(command.ExecuteScalar());//执行查询，并返回查询所返回的结果集中第一行的第一列。 忽略其他列或行
                connection.Close();
                return result;
            }

        }
        catch (Exception)
        {
            throw;
        }
    }


    /// <summary>
    ///  执行SQL语句
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="pars"></param>
    /// <returns></returns>
    public int ExecuteSqlWithRet(string sql, params SqlParameter[] pars)
    {
        try
        {
            using (SqlConnection connection = new SqlConnection(this.connectionString))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = sql;
                command.Parameters.AddRange(pars);
                connection.Open();
                int result = Convert.ToInt32(command.ExecuteScalar());
                connection.Close();
                return result;
            }

        }
        catch (Exception)
        {
            throw;
        }
    }
    /// <summary>
    /// 执行SQL语句
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public int ExecuteSql(string sql)
    {
        try
        {
            using (SqlConnection connection = new SqlConnection(this.connectionString))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = sql;
                connection.Open();
                int result = command.ExecuteNonQuery();
                connection.Close();
                return result;
            }

        }
        catch (Exception)
        {
            throw;
        }
    }

    /// <summary>
    /// 执行SQL语句
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="pars"></param>
    /// <returns></returns>
    public int ExecuteSql(string sql, params SqlParameter[] pars)
    {
        try
        {
            using (SqlConnection connection = new SqlConnection(this.connectionString))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = sql;
                command.Parameters.AddRange(pars);
                connection.Open();
                int result = command.ExecuteNonQuery();
                connection.Close();
                return result;
            }

        }
        catch (Exception ex)
        {
            throw;
        }
    }

    /// <summary>
    /// 查询数据
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public DataTable GetDataTable(string sql)
    {
        try
        {
            using (SqlConnection connection = new SqlConnection(this.connectionString))
            {
                DataSet ds = new DataSet();
                SqlDataAdapter sda = new SqlDataAdapter(sql, connection);
                sda.Fill(ds);

                if (ds != null && ds.Tables.Count > 0)
                {
                    return ds.Tables[0];
                }
                else
                {
                    return null;
                }
            }
        }
        catch (Exception)
        {
            throw;
        }
    }

    /// <summary>
    /// 查询数据
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="pars"></param>
    /// <returns></returns>
    public DataTable GetDataTable(string sql, params SqlParameter[] pars)
    {
        try
        {
            using (SqlConnection connection = new SqlConnection(this.connectionString))
            {
                DataSet ds = new DataSet();
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandText = sql;
                command.Parameters.AddRange(pars);
                SqlDataAdapter sda = new SqlDataAdapter(command);

                sda.Fill(ds);

                if (ds != null && ds.Tables.Count > 0)
                {
                    return ds.Tables[0];
                }
                else
                {
                    return null;
                }
            }
        }
        catch (Exception)
        {
            throw;
        }
    }

    public DataSet GetDataTableByPage(int start, int pageSize, string table, string columns, string order, string filter)
    {
        try
        {
            string sql = "SELECT TOP " + pageSize + " " + columns + " FROM (SELECT ROW_NUMBER() OVER (ORDER BY " + order + " ) AS RowNumber," + columns + " FROM " + table + " {0}) A WHERE RowNumber> " + start + " order by RowNumber ";
            sql = string.Format(sql, (filter.Trim() == "" ? "" : (" WHERE " + filter.Trim())));

            //记录总数(非结果集记录总数)
            string sql_count = "SELECT COUNT(*) FROM " + table;
            sql_count += (filter.Trim() == "" ? "" : (" WHERE " + filter));


            using (SqlConnection connection = new SqlConnection(this.connectionString))
            {
                DataSet ds = new DataSet();
                ds.Tables.Add("result");
                ds.Tables.Add("count");

                SqlDataAdapter sda = new SqlDataAdapter();
                sda.SelectCommand = new SqlCommand();
                sda.SelectCommand.Connection = connection;

                sda.SelectCommand.CommandText = sql;
                sda.Fill(ds.Tables["result"]);

                sda.SelectCommand.CommandText = sql_count;
                sda.Fill(ds.Tables["count"]);

                return ds;
            }
        }
        catch (Exception)
        {

            throw;
        }
    }

    /// <summary>
    /// 分页查询数据
    /// </summary>
    public DataSet GetDataTableByPage(int start, int pageSize, string table, string columns, string order, string filter, params SqlParameter[] pars)
    {
        try
        {
            string sql = "SELECT TOP " + pageSize + " " + columns + " FROM (SELECT ROW_NUMBER() OVER (ORDER BY " + order + " ) AS RowNumber," + columns + " FROM " + table + " {0}) A WHERE RowNumber> " + start + " ORDER BY " + order;
            sql = string.Format(sql, (filter.Trim() == "" ? "" : (" WHERE " + filter.Trim())));

            //记录总数(非结果集记录总数)
            string sql_count = "SELECT COUNT(*) FROM " + table;
            sql_count += (filter.Trim() == "" ? "" : (" WHERE " + filter));


            using (SqlConnection connection = new SqlConnection(this.connectionString))
            {
                DataSet ds = new DataSet();
                ds.Tables.Add("result");
                ds.Tables.Add("count");

                SqlDataAdapter sda = new SqlDataAdapter();
                sda.SelectCommand = new SqlCommand();
                sda.SelectCommand.Connection = connection;
                sda.SelectCommand.Parameters.AddRange(pars);

                sda.SelectCommand.CommandText = sql;
                sda.Fill(ds.Tables["result"]);

                sda.SelectCommand.CommandText = sql_count;
                sda.Fill(ds.Tables["count"]);

                return ds;
            }
        }
        catch (Exception)
        {

            throw;
        }
    }
}

使用

  DBHelper helper = new DBHelper();

• 查询

 r= helper.ExecuteSqlWithRet(" SELECT * [dbo].[student] where ....... ",
                                               new SqlParameter("@a",""),
                                               new SqlParameter("@b", CreatedTime.ToDateTime()),
                                               new SqlParameter("@c", CreatedTime.ToDateTime())
                                              );

• 更新

  helper.ExecuteSql("DELETE  FROM [dbo].[student] where ....... ", new SqlParameter("@a", a),
                   new SqlParameter("@a", b),new SqlParameter("@c", c));

连接

<connectionStrings>
    <add name="SqlServerDataSource" connectionString="Server=127.0.0.1;Initial Catalog=StudentDB;User ID=sa;Password=123456" providerName="System.Data.SqlClient"/>
  </connectionStrings>