C# SQLDBHelper

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

public class SQLDBHelper
{
    #region 数据库连接配置
    //数据库连接配置（Web.config）    //<add name="SQLConnection" connectionString="Data Source=127.0.0.1;Database=xxxx;UID=xxxx;PWD=xxxx;" providerName="System.Data.SqlClient" />
    //static readonly string webconnstr = System.Configuration.ConfigurationManager.ConnectionStrings["SQLConnection"].ConnectionString;

    //数据库连接配置（App.config）    //<add key="SQLConnection" value="Data Source=127.0.0.1;Database=xxxx;UID=xxxx;PWD=xxxx;" />
    //static readonly string appconnstr = System.Configuration.ConfigurationManager.AppSettings["SQLConnection"].ToString();
    #endregion

    /// <summary>
    /// 执行查询，返回DataTable对象
    /// </summary>
    /// <param name="sqlstr">sql语句</param>
    /// <param name="parms">参数</param>
    /// <param name="cmdtype">命令类型(StoredProcedure、TableDirect、Text)</param>
    /// <param name="connstr">数据库连接字符串</param>
    /// <returns></returns>
    public static DataTable GetDataTable(string sqlstr, SqlParameter[] parms, CommandType cmdtype, string connstr)
    {
        DataTable dt = new DataTable(); ;
        using (SqlConnection conn = new SqlConnection(connstr))
        {
            SqlDataAdapter da = new SqlDataAdapter(sqlstr, conn);
            da.SelectCommand.CommandType = cmdtype;
            if (parms != null)
            {
                da.SelectCommand.Parameters.AddRange(parms);
            }
            da.Fill(dt);
        }
        return dt;
    }

    /// <summary>
    /// 执行查询，返回DataSet对象
    /// </summary>
    /// <param name="sqlstr">sql语句</param>
    /// <param name="parms">参数</param>
    /// <param name="cmdtype">命令类型(StoredProcedure、TableDirect、Text)</param>
    /// <param name="connstr">数据库连接字符串</param>
    /// <returns></returns>
    public static DataSet GetDataSet(string sqlstr, SqlParameter[] parms, CommandType cmdtype, string connstr)
    {
        DataSet ds = new DataSet(); ;
        using (SqlConnection conn = new SqlConnection(connstr))
        {
            SqlDataAdapter da = new SqlDataAdapter(sqlstr, conn);
            da.SelectCommand.CommandType = cmdtype;
            if (parms != null)
            {
                da.SelectCommand.Parameters.AddRange(parms);
            }
            da.Fill(ds);
        }
        return ds;
    }

    /// <summary>
    /// 执行非查询存储过程和SQL语句
    /// </summary>
    /// <param name="sqlstr">sql语句</param>
    /// <param name="parms">参数</param>
    /// <param name="cmdtype">命令类型(StoredProcedure、TableDirect、Text)</param>
    /// <param name="connstr">数据库连接字符串</param>
    /// <returns></returns>
    public static int ExecuteNonQuery(string sqlstr, SqlParameter[] parms, CommandType cmdtype, string connstr)
    {
        int r = 0;
        using (SqlConnection conn = new SqlConnection(connstr))
        {
            SqlCommand cmd = new SqlCommand(sqlstr, conn);
            cmd.CommandType = cmdtype;
            if (parms != null)
            {
                cmd.Parameters.AddRange(parms);
            }
            conn.Open();
            r = cmd.ExecuteNonQuery();
            conn.Close();
        }
        return r;
    }

    /// <summary>
    /// 执行SQL语句，返回首行首列
    /// </summary>
    /// <param name="sqlstr">sql语句</param>
    /// <param name="parms">参数</param>
    /// <param name="cmdtype">命令类型(StoredProcedure、TableDirect、Text)</param>
    /// <param name="connstr">数据库连接字符串</param>
    /// <returns></returns>
    public static string ExecuteScalar(string sqlstr, SqlParameter[] parms, CommandType cmdtype, string connstr)
    {
        string result = "";
        using (SqlConnection conn = new SqlConnection(connstr))
        {
            SqlCommand cmd = new SqlCommand(sqlstr, conn);
            cmd.CommandType = cmdtype;
            if (parms != null)
            {
                cmd.Parameters.AddRange(parms);
            }
            conn.Open();
            result = cmd.ExecuteScalar().ToString();
            conn.Close();
        }
        return result;
    }

    /// <summary>
    /// 执行SQL语句，返回首行首列
    /// </summary>
    /// <param name="sqlstr">sql语句</param>
    /// <param name="parms">参数</param>
    /// <param name="cmdtype">命令类型(StoredProcedure、TableDirect、Text)</param>
    /// <param name="connstr">数据库连接字符串</param>
    /// <returns></returns>
    public static object GetObject(string sqlstr, SqlParameter[] parms, CommandType cmdtype, string connstr)
    {
        object obj = null;
        using (SqlConnection conn = new SqlConnection(connstr))
        {
            SqlCommand cmd = new SqlCommand(sqlstr, conn);
            cmd.CommandType = cmdtype;
            if (parms != null)
            {
                cmd.Parameters.AddRange(parms);
            }

            conn.Open();
            obj = cmd.ExecuteScalar();
            conn.Close();
        }
        return obj;
    }

    /// <summary>
    /// 批量插入数据
    /// </summary>
    /// <param name="sourceDt">DataTable 数据集</param>
    /// <param name="targetTable">目标表</param>
    /// <param name="connstr">数据库连接字符串</param>
    public static void SqlBulkCopy(DataTable sourceDt, string targetTable, string connstr)
    {
        SqlConnection conn = new SqlConnection(connstr);
        SqlBulkCopy bulkCopy = new SqlBulkCopy(conn); //用其他源的数据有效批量加载SQL Server表
        bulkCopy.DestinationTableName = targetTable; //服务器上目标表的名称
        bulkCopy.BatchSize = sourceDt.Rows.Count; //每一批次中的行数

        try
        {
            conn.Open();
            if (sourceDt != null && sourceDt.Rows.Count != 0)
            {
                bulkCopy.WriteToServer(sourceDt); //将提供的数据源中的所有行复制到目标表中
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            conn.Close();
            if (bulkCopy != null)
            {
                bulkCopy.Close();
            }
        }
    }
}