代码段——SqlHelperForSqlServer

连接字符串

<connectionStrings>
	<add connectionString="server=;database=;uid=;pwd=" name="connStr" />
</connectionStrings>

SqlHelper

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

public class SqlHelper
{
    private static readonly string _connStr4WMSBak = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
    public static SqlConnection GetConnection()
    {
        SqlConnection sqlConnection = new SqlConnection(_connStr4WMSBak);
        sqlConnection.Open();
        return sqlConnection;
    }
    /// <summary>
    /// 返回查询结果的的表
    /// </summary>
    public static DataTable GetDataTable(string sql, CommandType type = CommandType.Text, params SqlParameter[] param)
    {
        using (SqlConnection conn = GetConnection())
        {
            using (SqlDataAdapter adapter = new SqlDataAdapter(sql, conn))
            {
                if (param != null)
                {
                    adapter.SelectCommand.Parameters.AddRange(param);
                }
                adapter.SelectCommand.CommandType = type;
                DataTable dt = new DataTable();
                adapter.Fill(dt);
                return dt;
            }
        }
    }
    /// <summary>
    /// 返回影响行数
    /// </summary>
    public static int ExecuteNonquery(string sql, CommandType type = CommandType.Text, params SqlParameter[] param)
    {
        using (SqlConnection conn = GetConnection())
        {
            using (SqlCommand cmd = new SqlCommand(sql, conn))
            {
                if (param != null)
                {
                    cmd.Parameters.AddRange(param);
                }
                cmd.CommandType = type;
                return cmd.ExecuteNonQuery();
            }
        }
    }
    /// <summary>
    /// 返回查询结果的第一行第一个单元格的数据
    /// </summary>
    public static object ExecuteScalar(string sql, CommandType type = CommandType.Text, params SqlParameter[] param)
    {
        using (SqlConnection conn = GetConnection())
        {
            using (SqlCommand cmd = new SqlCommand(sql, conn))
            {
                if (param != null)
                {
                    cmd.Parameters.AddRange(param);
                }
                cmd.CommandType = type;
                return cmd.ExecuteScalar();
            }
        }
    }
    /// <summary>
    /// 使用SqlBulkCopy将DataTable中的数据批量插入数据库中
    /// </summary>
    /// <param name="dbTableName">数据库中对应的表名</param>
    /// <param name="dtData">数据集</param>
    public static void SqlBulkCopyInsert(string dbTableName, DataTable dataTable)
    {
        using (SqlBulkCopy sqlRevdBulkCopy = new SqlBulkCopy(GetConnection()))//引用SqlBulkCopy
        {
            sqlRevdBulkCopy.DestinationTableName = dbTableName;//数据库中对应的表名
            sqlRevdBulkCopy.NotifyAfter = dataTable.Rows.Count;//有几行数据
            sqlRevdBulkCopy.WriteToServer(dataTable);//数据导入数据库
            sqlRevdBulkCopy.Close();//关闭连接
        }
    }
}