JLH/JLHHJSvr/Tools/TempTableHelper.cs

using System;
using System.Data;
using System.Data.SqlClient;
using System.Text.RegularExpressions;

namespace JLHHJSvr.Tools
{
    public static class TempTableHelper
    {
        private static readonly Regex TempTableNameRegex = new Regex(@"^#{1,2}[A-Za-z_][A-Za-z0-9_]*$", RegexOptions.Compiled);

        /// <summary>
        /// Creates a SQL Server temp table from DataTable columns and writes rows by SqlBulkCopy.
        /// </summary>
        public static void CreateAndBulkInsert(SqlCommand cmd, string tempTableName, DataTable dataTable, bool recreate = true, int batchSize = 1000, int bulkCopyTimeout = 60)
        {
            CheckArguments(cmd, tempTableName, dataTable);

            if (recreate)
            {
                DropIfExists(cmd, tempTableName);
            }

            CreateTempTable(cmd, tempTableName, dataTable);
            BulkInsert(cmd, tempTableName, dataTable, batchSize, bulkCopyTimeout);
        }

        /// <summary>
        /// Writes DataTable rows to an existing SQL Server temp table by SqlBulkCopy.
        /// </summary>
        public static void BulkInsert(SqlCommand cmd, string tempTableName, DataTable dataTable, int batchSize = 1000, int bulkCopyTimeout = 60)
        {
            CheckArguments(cmd, tempTableName, dataTable);

            using (var sqlBC = cmd.Transaction == null
                ? new SqlBulkCopy(cmd.Connection)
                : new SqlBulkCopy(cmd.Connection, SqlBulkCopyOptions.Default, cmd.Transaction))
            {
                sqlBC.BatchSize = batchSize;
                sqlBC.BulkCopyTimeout = bulkCopyTimeout;
                sqlBC.DestinationTableName = tempTableName;

                foreach (DataColumn column in dataTable.Columns)
                {
                    sqlBC.ColumnMappings.Add(column.ColumnName, column.ColumnName);
                }

                sqlBC.WriteToServer(dataTable);
            }
        }

        /// <summary>
        /// Drops a SQL Server temp table in the current connection when it exists.
        /// </summary>
        public static void DropIfExists(SqlCommand cmd, string tempTableName)
        {
            CheckCommand(cmd);
            CheckTempTableName(tempTableName);

            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Clear();
            cmd.CommandText = string.Format(
                "IF OBJECT_ID('tempdb..{0}') IS NOT NULL DROP TABLE {1}",
                tempTableName.Replace("'", "''"),
                QuoteName(tempTableName));
            cmd.ExecuteNonQuery();
        }

        private static void CreateTempTable(SqlCommand cmd, string tempTableName, DataTable dataTable)
        {
            if (dataTable.Columns.Count == 0)
            {
                throw new ArgumentException("DataTable must contain at least one column.", "dataTable");
            }

            string columnSql = string.Empty;
            foreach (DataColumn column in dataTable.Columns)
            {
                if (!string.IsNullOrEmpty(columnSql))
                {
                    columnSql += ",";
                }

                columnSql += string.Format("{0} {1} {2}",
                    QuoteName(column.ColumnName),
                    GetSqlType(column),
                    column.AllowDBNull ? "NULL" : "NOT NULL");
            }

            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Clear();
            cmd.CommandText = string.Format("CREATE TABLE {0} ({1})", QuoteName(tempTableName), columnSql);
            cmd.ExecuteNonQuery();
        }

        private static string GetSqlType(DataColumn column)
        {
            if (column.ExtendedProperties.ContainsKey("SqlType"))
            {
                var sqlType = Convert.ToString(column.ExtendedProperties["SqlType"]);
                if (!string.IsNullOrWhiteSpace(sqlType))
                {
                    return sqlType;
                }
            }

            var type = Nullable.GetUnderlyingType(column.DataType) ?? column.DataType;
            if (type == typeof(string))
            {
                return column.MaxLength > 0 && column.MaxLength <= 4000
                    ? string.Format("NVARCHAR({0})", column.MaxLength)
                    : "NVARCHAR(MAX)";
            }
            if (type == typeof(int)) return "INT";
            if (type == typeof(long)) return "BIGINT";
            if (type == typeof(short)) return "SMALLINT";
            if (type == typeof(byte)) return "TINYINT";
            if (type == typeof(bool)) return "BIT";
            if (type == typeof(decimal)) return "DECIMAL(38, 10)";
            if (type == typeof(double)) return "FLOAT";
            if (type == typeof(float)) return "REAL";
            if (type == typeof(DateTime)) return "DATETIME";
            if (type == typeof(Guid)) return "UNIQUEIDENTIFIER";
            if (type == typeof(byte[])) return "VARBINARY(MAX)";

            throw new NotSupportedException(string.Format("Unsupported DataColumn type: {0}, column: {1}", column.DataType.FullName, column.ColumnName));
        }

        private static void CheckArguments(SqlCommand cmd, string tempTableName, DataTable dataTable)
        {
            CheckCommand(cmd);
            CheckTempTableName(tempTableName);

            if (dataTable == null)
            {
                throw new ArgumentNullException("dataTable");
            }
        }

        private static void CheckCommand(SqlCommand cmd)
        {
            if (cmd == null)
            {
                throw new ArgumentNullException("cmd");
            }
            if (cmd.Connection == null)
            {
                throw new ArgumentException("SqlCommand must have a valid SqlConnection.", "cmd");
            }
        }

        private static void CheckTempTableName(string tempTableName)
        {
            if (string.IsNullOrWhiteSpace(tempTableName) || !TempTableNameRegex.IsMatch(tempTableName))
            {
                throw new ArgumentException("Temp table name must start with # or ## and contain only letters, numbers, and underscores.", "tempTableName");
            }
        }

        private static string QuoteName(string name)
        {
            return "[" + name.Replace("]", "]]") + "]";
        }
    }
}