代码段——SqlHelperForSqlServer

连接字符串

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

SqlHelper

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

public static class SqlHelper
{
    private static readonly string _connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;

    public static SqlConnection GetConnection()
    {
        SqlConnection sqlConnection = new SqlConnection(_connStr);
        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 DataSet GetDataSet(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;
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet);
                return dataSet;
            }
        }
    }

    /// <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>
    /// 返回 XML 数据
    /// </summary>
    public static XmlDocument GetXML(string sql, CommandType type = CommandType.Text, params SqlParameter[] param)
    {
        XmlDocument xmlDoc = new XmlDocument();

        using (SqlConnection conn = GetConnection())
        {
            using (SqlCommand cmd = new SqlCommand(sql, conn))
            {
                cmd.CommandType = type;

                if (param != null)
                {
                    cmd.Parameters.AddRange(param);
                }

                using (XmlReader reader = cmd.ExecuteXmlReader())
                {
                    if (reader.Read())
                    {
                        xmlDoc.Load(reader);
                    }
                }
            }
        }

        return xmlDoc;
    }

    /// <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();//关闭连接
        }
    }
}

测试查询返回XML

//XmlDocument xmlData =  SqlHelper.GetXML("SELECT TOP 1 [id], [name], [job_service], [job_class] FROM [dbo].[core_job] FOR XML PATH;");
XmlDocument xmlData = SqlHelper.GetXML("DECLARE @return_xml XML;\r\nSET @return_xml=(SELECT 'OK' result, NULL error FOR XML PATH('data'));\r\nSELECT @return_xml;");
string result=xmlData.OuterXml;