public static void CreateCSVFromDataTable(DataTable dt, string exportName, string exportDirectory)
{
string exportPath = exportDirectory + @"\" + exportName + ".csv";
if (File.Exists(exportPath))
{
File.Delete(exportPath);
}
StringBuilder sb = StringOperations.BuildCSVStringFromDataTable(dt);
File.WriteAllText(exportPath, sb.ToString());
}
public static void SqlWriteDataTable(string tableName, SqlConnection sqlConnection, DataTable dataTable, bool dropTable)
{
foreach (var column in dataTable.Columns.Cast <DataColumn>().ToArray())
{
if (dataTable.AsEnumerable().All(dr => dr.IsNull(column) || dr[column].ToString() == "NA"))
{
dataTable.Columns.Remove(column);
}
}
if (dataTable.Columns.Count > 1000)
{
TaskDialog.Show("Alert", "Column Count for " + tableName + " is greater than 1000");
return;
}
string sqldrop = "IF OBJECT_ID('[dbo].[" + tableName + "]', 'U') IS NOT NULL DROP TABLE [" + tableName + "]";
string sqlsc = "CREATE TABLE [" + tableName + "] (";
for (int i = 0; i < dataTable.Columns.Count; i++)
{
sqlsc += "\n [" + dataTable.Columns[i].ColumnName + "] ";
string columnType = dataTable.Columns[i].DataType.ToString();
switch (columnType)
{
case "System.Int32":
sqlsc += " int ";
break;
case "System.Int64":
sqlsc += " bigint ";
break;
case "System.Int16":
sqlsc += " smallint ";
break;
case "System.Byte":
sqlsc += " tinyint ";
break;
case "System.Decimal":
sqlsc += " decimal(15,6) ";
break;
case "System.Double":
sqlsc += " decimal(15,6) ";
break;
case "System.DateTime":
sqlsc += " datetime ";
break;
case "System.Guid":
sqlsc += " uniqueidentifier ";
break;
case "System.String":
sqlsc += " nvarchar(MAX) ";
break;
default:
sqlsc += string.Format(" nvarchar(MAX) ");
break;
}
if (dataTable.Columns[i].AutoIncrement)
{
sqlsc += " IDENTITY(" + dataTable.Columns[i].AutoIncrementSeed.ToString() + "," + dataTable.Columns[i].AutoIncrementStep.ToString() + ") ";
}
if (!dataTable.Columns[i].AllowDBNull)
{
sqlsc += " NOT NULL ";
}
sqlsc += ",";
}
string sqlstring = sqlsc.Substring(0, sqlsc.Length - 1) + "\n)";
using (SqlConnection sqlconn = sqlConnection)
{
if (dropTable)
{
SqlCommand sqlDrop = new SqlCommand(sqldrop, sqlconn);
sqlDrop.ExecuteNonQuery();
}
DataTable dt = sqlConnection.GetSchema("Tables");
List <string> existingTables = new List <string>();
foreach (DataRow row in dt.Rows)
{
string existingTableName = (string)row[2];
existingTables.Add(existingTableName);
}
SqlBulkCopyOptions options = SqlBulkCopyOptions.Default;
if (!existingTables.Contains(tableName))
{
SqlCommand sqlCreate = new SqlCommand(sqlstring, sqlconn);
sqlCreate.ExecuteNonQuery();
try
{
using (SqlBulkCopy s = new SqlBulkCopy(sqlconn, options, null))
{
s.DestinationTableName = "[" + tableName + "]";
foreach (var column in dataTable.Columns)
{
s.ColumnMappings.Add(column.ToString(), column.ToString());
}
s.WriteToServer(dataTable);
}
}
catch { MessageBox.Show("Table already exists"); }
}
else
{
DataTable columnsTable = sqlconn.GetSchema("Columns", new[] { sqlconn.DataSource, null, tableName });
List <string> sqlColumnNamesList = new List <string>();
List <string> dtColumnNamesList = new List <string>();
foreach (DataRow sqlColumnRow in columnsTable.Rows)
{
sqlColumnNamesList.Add(sqlColumnRow[0].ToString());
StringBuilder sb = StringOperations.BuildCSVStringFromDataTable(columnsTable);
MessageBox.Show(sb.ToString());
}
string sqltype;
foreach (DataColumn dtColumn in dataTable.Columns)
{
string columnType = dtColumn.DataType.ToString();
switch (columnType)
{
case "System.Int32":
sqltype = " int ";
break;
case "System.Int64":
sqltype = " bigint ";
break;
case "System.Int16":
sqltype = " smallint ";
break;
case "System.Byte":
sqltype = " tinyint";
break;
case "System.Decimal":
sqltype = " decimal(15,6) ";
break;
case "System.Double":
sqltype = " decimal(15,6)";
break;
case "System.DateTime":
sqltype = " datetime ";
break;
case "System.Guid":
sqltype = " uniqueidentifier ";
break;
case "System.String":
default:
sqltype = string.Format(" nvarchar(255) ");
break;
}
if (!sqlColumnNamesList.Contains(dtColumn.ColumnName))
{
SqlCommand sqlCommand = new SqlCommand("ALTER TABLE " + tableName + " ADD " + dtColumn.ColumnName + " " + sqltype, sqlconn);
try
{
sqlCommand.ExecuteNonQuery();
}
catch
{
continue;
}
}
}
try
{
using (SqlBulkCopy s = new SqlBulkCopy(sqlconn, options, null))
{
s.DestinationTableName = "[" + tableName + "]";
s.WriteToServer(dataTable);
}
}
catch (SqlException appendException) { MessageBox.Show(appendException.ToString()); }
}
SqlCloseConnection(sqlconn);
}
SqlLogWriter(tableName);
}
