Skip to main content

Save DataTable to SQL Server

A couple of times now, I've needed to save in-memory ADO.NET DataTables straight to SQL Server as new tables. Its handy sometimes, if you're importing data from other sources. Nick Tompson had posted some code on the social.msdn forum to do this, and here I've stripped out the features I didn't need and adapted it to use StringBuilders:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;


    // Save a DataTable to a SQLServer Table
    // Based on Nick Tompsons code from 
    // http://social.msdn.microsoft.com/forums/en-US/adodotnetdataproviders/thread/4929a0a8-0137-45f6-86e8-d11e220048c3/
    // Removed 'make from Schema' options to simplify, and used StringBuilders.
    public class SqlTableCreator
    {
        
        private SqlConnection _connection;
        public SqlConnection Connection
        {
            get { return _connection; }
            set { _connection = value; }
        }

        private SqlTransaction _transaction;
        public SqlTransaction Transaction
        {
            get { return _transaction; }
            set { _transaction = value; }
        }

        private string _tableName;
        public string DestinationTableName
        {
            get { return _tableName; }
            set { _tableName = value; }
        }
        

        

        public SqlTableCreator(SqlConnection connection) : this(connection, null) { }
        public SqlTableCreator(SqlConnection connection, SqlTransaction transaction)
        {
            _connection = connection;
            _transaction = transaction;
        }
        


        public object CreateFromDataTable(DataTable table)
        {
            string sql = GetCreateFromDataTableSQL(_tableName, table);

            SqlCommand cmd;
            if (_transaction != null && _transaction.Connection != null)
                cmd = new SqlCommand(sql, _connection, _transaction);
            else
                cmd = new SqlCommand(sql, _connection);

            return cmd.ExecuteNonQuery();
        }


        public static string GetCreateFromDataTableSQL(string tableName, DataTable table)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("CREATE TABLE [{0}] (", tableName);
            
            // columns
            bool first = true;
            foreach (DataColumn column in table.Columns)
            {
                if (first)
                    first = false;
                else
                    sb.Append(",");

                sb.AppendFormat("\n[{0}] {1}", column.ColumnName, SQLGetType(column));
            }
            
            // primary keys
            if (table.PrimaryKey.Length > 0)
            {
                sb.AppendFormat(" CONSTRAINT [PK_{0}] PRIMARY KEY CLUSTERED (", tableName);
                bool first2 = true;
                foreach (DataColumn column in table.PrimaryKey)
                {
                    if (first2)
                        first2 = false;
                    else
                        sb.Append(",");

                    sb.AppendFormat("[{0}]", column.ColumnName);
                }
                sb.Append(")\n");
            }
            sb.Append(")\n");

            return sb.ToString();
        }


        // Return T-SQL data type definition, based on schema definition for a column
        public static string SQLGetType(object type, int columnSize, int numericPrecision, int numericScale)
        {
            switch (type.ToString())
            {
                case "System.String":
                    return String.Format("VARCHAR({0})", ((columnSize == -1) ? 255 : columnSize));

                case "System.Decimal":
                    if (numericScale > 0)
                        return "REAL";
                    else if (numericPrecision > 10)
                        return "BIGINT";
                    else
                        return "INT";

                case "System.Double":
                case "System.Single":
                    return "REAL";

                case "System.Int64":
                    return "BIGINT";

                case "System.Int16":
                case "System.Int32":
                    return "INT";

                case "System.DateTime":
                    return "DATETIME";

                default:
                    throw new ApplicationException(String.Format("{0} not implemented.",type.ToString()));
            }
        }

        // Overload based on DataColumn from DataTable type
        public static string SQLGetType(DataColumn column)
        {
            return SQLGetType(column.DataType, column.MaxLength, 10, 2);
        }

        

    }

Comments

Popular posts from this blog

Copying data to Salesforce Sandboxes using TalenD

A common problem with Salesforce Developer Sandboxes is that they are blank. Really you're going to want some data in there, so there are various strategies for copying data from your live instance to the Sandbox. There are some paid-for solutions - SFXOrgData , Salesforce Partial Data Sandboxes - but if you've got a decent ETL tool you can build your own. There are a bunch of free ETL tools for Salesforce: JitterBit Data Loader is good for quick ad-hoc tasks but the free version makes it difficult to manage specific ETL projects or share projects with other users Pentaho Community Edition - an open source edition of the enterprise version Apatar was a free open source Salesforce ETL which still works but development seems to have stopped since 2011 TalenD Open Studio is an open source ETL tool For the task of copying data from live to a Sandbox, either Pentaho or TalenD Open Studio could be used, depending on preference. Here's a good comparison of the dif

SSRS multi-value parameters with less fail

SSRS supports multi-value parameters, which is nice, but there are a few issues with them. This is how I deal with them. Two of the problems with SSRS multi-value parameters are: You have to jump through a few hoops to get them to work with stored procedures The (Select All) option, as shown above The reason the (Select All) option is a problem is that it is a really inelegant way of saying 'this parameter does not matter to me'. If you have a list with hundreds of values, passing all of them as a default option just seems wrong. Also, if your report shows the user which items they selected, printing the whole list when they choose (Select All) is excessive. So in this post I'm going to show my particular way of: Jumping through the hoops to get Multi-Value params in a stored procedure Adding a single '--All--' value that the report interprets as meaning all the options. Getting Multi-Value params to work with Stored Procedures This is

Remote Desktop on High DPI screens

Scott Hanselman wrote a nice blog post back in January about some of the issues you might face running Windows on a High DPI screen like that of a Surface Pro or Lenova Yoga. I'm kindof mystified that he didn't mention Remote Desktop though because thats been the number one problem for me on High DPI screens. That said, if you remote into very recent Server OS's like Windows Server 2012 R2, then apparently Remote Desktop will sort out the DPI scaling automatically. Perhaps Scott hadn't noticed the Remote Desktop issue because he only remotes into Server 2012 R2. Certainly if I was Scott, I wouldn't remote into anything less than that. But, in practice, I regularly have to remote into Server 2008 machines and yes even Server 2003. If you do that from a high DPI screen, the remote desktop is rendered at regular pixel size, which makes everything tiny. Its hard to show screenshots of High DPI screens that correctly convey the pixel size, so I've photoshoppe