How to export DataTable to SQL Server when the SQL table doesn't exist

[gradx7]

I've seen a few posts about this and I had to write a function to do it
so I thought I'd share. (It uses ODBC cause that was the requirement I
had for a small job):

public class TableExport
{
public TableExport()
{
//
// TODO: Add constructor logic here
//
}

#region CSV
public static void GenerateCSV(DataTable dt, System.IO.TextWriter
httpStream, bool WriteHeader)
{
if(WriteHeader)
{
string[] arr = new String[dt.Columns.Count];
for(int i = 0; i<dt.Columns.Count; i++)
{
arr = dt.Columns.ColumnName;
arr = GetWriteableValue(arr);
}

httpStream.WriteLine(string.Join(",", arr));
}

for(int j = 0; j<dt.Rows.Count; j++)
{
string[] dataArr = new String[dt.Columns.Count];
for(int i = 0; i<dt.Columns.Count; i++)
{
object o = dt.Rows[j];
dataArr = GetWriteableValue(o);
}
httpStream.WriteLine(string.Join(",", dataArr));
}
}

public static void GenerateCSV(DataTable dt, System.IO.StreamWriter
file, bool WriteHeader)
{
if(WriteHeader)
{
string[] arr = new String[dt.Columns.Count];
for(int i = 0; i<dt.Columns.Count; i++)
{
arr = dt.Columns.ColumnName;
arr = GetWriteableValue(arr);
}

file.WriteLine(string.Join(",", arr));
}

for(int j = 0; j<dt.Rows.Count; j++)
{
string[] dataArr = new String[dt.Columns.Count];
for(int i = 0; i<dt.Columns.Count; i++)
{
object o = dt.Rows[j];
dataArr = GetWriteableValue(o);
}
file.WriteLine(string.Join(",", dataArr));
}
}

public static string GetWriteableValue(object o)
{
if(o==null || o == Convert.DBNull)
return "";
else if(o.ToString().IndexOf(",")==-1)
return o.ToString();
else
return "\"" + o.ToString() + "\"";

}
#endregion

public static void CreateTable(string connectionString, string
tableNameToCreate, DataTable dataTable)
{
StringCollection _columnsToCreate = new StringCollection();
bool _textImage = false;
foreach(DataColumn _col in dataTable.Columns)
{
bool _needsText = false;
string _colVal = MapType(_col, dataTable, out _needsText);
if (_needsText == true)
_textImage = true;

_columnsToCreate.Add(string.Format("[{0}] {1} NULL,",
_col.ColumnName, _colVal));
}
_columnsToCreate[_columnsToCreate.Count - 1] =
_columnsToCreate[_columnsToCreate.Count - 1].Substring(0,
_columnsToCreate[_columnsToCreate.Count - 1].Length - 1);
GenerateCreateTableScript(connectionString, tableNameToCreate,
_columnsToCreate, _textImage);
AddRecords(connectionString, tableNameToCreate, dataTable);
}

public static void AddRecords(string connectionString, string
tableToAddTo, DataTable dataTable)
{
using(OdbcConnection _conn = new OdbcConnection(connectionString))
{
_conn.Open();
OdbcCommand _cmd = new OdbcCommand("select * from " + tableToAddTo,
_conn);
OdbcDataAdapter _adapter = new OdbcDataAdapter(_cmd);

DataTable _newTable = dataTable.Copy();
_newTable.Rows.Clear();

foreach(DataRow _row in dataTable.Rows)
{
_newTable.Rows.Add(_row.ItemArray);
}

OdbcCommandBuilder _cmdBuilder = new OdbcCommandBuilder(_adapter);
_adapter.Update(_newTable);
_newTable.AcceptChanges();
}
}

private static void GenerateCreateTableScript(string dsn, string
tableNameToCreate, StringCollection columnsToCreate, bool
needsTextImage)
{
//if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Andrew2]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
//drop table [dbo].[Andrew2]
//GO
// CREATE TABLE [dbo].[Andrew] (
// ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

StringBuilder _createTable = new StringBuilder();
_createTable.Append(string.Format("if exists (select * from
dbo.sysobjects where id = object_id(N'[dbo].[{0}]') and
OBJECTPROPERTY(id, N'IsUserTable') = 1)", tableNameToCreate) +
Environment.NewLine);
_createTable.Append(string.Format("drop table [dbo].[{0}]",
tableNameToCreate) + Environment.NewLine);
_createTable.Append(Environment.NewLine);
_createTable.Append(string.Format("CREATE TABLE [dbo].[{0}] (",
tableNameToCreate));

foreach(string _column in columnsToCreate)
{
_createTable.Append(_column + Environment.NewLine);
}
_createTable.Append(") ON [PRIMARY]");
if (needsTextImage)
_createTable.Append("TEXTIMAGE_ON [PRIMARY]");

using(OdbcConnection _conn = new OdbcConnection(dsn))
{
_conn.Open();
OdbcCommand _cmd = new OdbcCommand(_createTable.ToString(), _conn);
_cmd.ExecuteNonQuery();
}
}

private static string MapType(DataColumn column, DataTable dataTable,
out bool textImage)
{
textImage = false;
//Boolean Bit
//Byte Tinyint
//Byte[] Image
//DateTime DateTime
//Decimal Decimal
//Double Float
//Guid Uniqueidentifier
//Int16 SmallInt
//Int32 Int
//Int64 Bigint
//Object SqlVariant
//Single Real
//String Text



switch(column.DataType.FullName)
{
case "System.Boolean":
return "[bit]";
case "System.Byte":
return "[tinyint]";
case "System.Byte[]":
int _maxSizeBinary = 50;
foreach(DataRow _row in dataTable.Rows)
{
if (!(_row[column] is DBNull))
{
byte[] _val = (byte[])_row[column];
if (_val.Length > _maxSizeBinary)
_maxSizeBinary = _val.Length;
}
}
if (_maxSizeBinary > 7950) // Max size of varbinary is 8000,
padding 50 to be safe
{
textImage = true;
return "[image]";
}
else
return string.Format("[varbinary]({0})", _maxSizeBinary);
case "System.DateTime":
return "[datetime]";
case "System.Decimal":
return "[decimal]";
case "System.Double":
return "[float]";
case "System.Guid":
return "[uniqueidentifier]";
case "System.Int16":
return "[smallint]";
case "System.Int32":
return "[int]";
case "System.Int64":
return "[bigint]";
case "System.Object":
return "[sql_variant]";
case "System.Single":
return "[real]";
case "System.String":
int _maxSizeString = 50;
foreach(DataRow _row in dataTable.Rows)
{
if (!(_row[column] is DBNull))
{
string _val = (string)_row[column];
if (_val.Length > _maxSizeString)
_maxSizeString = _val.Length;
}
}
if (_maxSizeString > 3950) // Max size of nvarchar is 4000,
padding 50 to be safe
{
textImage = true;
return "[text] COLLATE SQL_Latin1_General_CP1_CI_AS";
}
else
return string.Format("[nvarchar]({0})", _maxSizeString);
default:
throw new Exception("unsupported variable type found");
}
return null;
}
}