public string GenerateTableWithInserts(SqlTable sqlTable, string tableName, int maxBulk, SqlInsertStringFormat insertStringFormart)
{
var builderFinal = new StringBuilder();
if (sqlTable != null)
{
var builderIntermediate = new StringBuilder();
var builderList = new List <StringBuilder>();
var lastRow = sqlTable.Rows.LastOrDefault();
var countBulk = 0;
builderIntermediate = new StringBuilder();
builderList.Add(builderIntermediate);
foreach (var sqlRow in sqlTable.Rows)
{
if (countBulk == maxBulk)
{
builderIntermediate = new StringBuilder();
builderList.Add(builderIntermediate);
countBulk = 0;
}
var strCols = new StringBuilder();
foreach (var sqlColumn in sqlRow.Columns)
{
if (strCols.Length > 0)
{
if (insertStringFormart != SqlInsertStringFormat.None)
{
strCols.Append("\r\n");
}
strCols.Append(",");
}
strCols.Append(ObjectToSqlValue(sqlColumn.Value, sqlColumn.ColumnTypeDefinition.Type));
if (insertStringFormart == SqlInsertStringFormat.BreakLineAndShowColumnNameForEachColumn)
{
strCols.Append(" -- ");
strCols.Append(sqlColumn.ColumnTypeDefinition.Name);
strCols.Append(" ");
strCols.Append(sqlColumn.ColumnTypeDefinition.TypeFormatted);
}
}
builderIntermediate.Append("(");
if (insertStringFormart != SqlInsertStringFormat.None)
{
builderIntermediate.Append("\r\n");
}
builderIntermediate.Append(strCols.ToString());
if (insertStringFormart != SqlInsertStringFormat.None)
{
builderIntermediate.Append("\r\n");
}
builderIntermediate.Append(")");
if (sqlRow != lastRow && countBulk + 1 < maxBulk)
{
builderIntermediate.AppendLine(", ");
}
countBulk++;
}
builderFinal.Append("CREATE TABLE ");
builderFinal.Append(tableName);
builderFinal.Append(" ");
builderFinal.AppendLine("(");
var last = sqlTable.Headers.LastOrDefault();
foreach (var header in sqlTable.Headers)
{
builderFinal.AppendLine("[" + header.Name + "] " + header.TypeFormatted);
if (last != header)
{
builderFinal.Append(", ");
}
}
builderFinal.AppendLine(");");
foreach (var bulkInsert in builderList)
{
builderFinal.AppendLine();
builderFinal.Append("INSERT INTO ");
builderFinal.Append(tableName);
builderFinal.Append(" ");
builderFinal.AppendLine("VALUES");
builderFinal.AppendLine(bulkInsert.ToString());
}
}
return(builderFinal.ToString());
}
public string GenerateTableWithInserts(SqlTable sqlTable, string tableName, int maxBulk, SqlInsertStringFormat insertStringFormart)
{
var builderFinal = new StringBuilder();
if (sqlTable != null)
{
var builderIntermediate = new StringBuilder();
var builderList = new List<StringBuilder>();
var lastRow = sqlTable.Rows.LastOrDefault();
var countBulk = 0;
builderIntermediate = new StringBuilder();
builderList.Add(builderIntermediate);
foreach (var sqlRow in sqlTable.Rows)
{
if (countBulk == maxBulk)
{
builderIntermediate = new StringBuilder();
builderList.Add(builderIntermediate);
countBulk = 0;
}
var strCols = new StringBuilder();
foreach (var sqlColumn in sqlRow.Columns)
{
if (strCols.Length > 0)
{
if (insertStringFormart != SqlInsertStringFormat.None)
strCols.Append("\r\n");
strCols.Append(",");
}
strCols.Append(ObjectToSqlValue(sqlColumn.Value, sqlColumn.ColumnTypeDefinition.Type));
if (insertStringFormart == SqlInsertStringFormat.BreakLineAndShowColumnNameForEachColumn)
{
strCols.Append(" -- ");
strCols.Append(sqlColumn.ColumnTypeDefinition.Name);
strCols.Append(" ");
strCols.Append(sqlColumn.ColumnTypeDefinition.TypeFormatted);
}
}
builderIntermediate.Append("(");
if (insertStringFormart != SqlInsertStringFormat.None)
builderIntermediate.Append("\r\n");
builderIntermediate.Append(strCols.ToString());
if (insertStringFormart != SqlInsertStringFormat.None)
builderIntermediate.Append("\r\n");
builderIntermediate.Append(")");
if (sqlRow != lastRow && countBulk + 1 < maxBulk)
builderIntermediate.AppendLine(", ");
countBulk++;
}
builderFinal.Append("CREATE TABLE ");
builderFinal.Append(tableName);
builderFinal.Append(" ");
builderFinal.AppendLine("(");
var last = sqlTable.Headers.LastOrDefault();
foreach (var header in sqlTable.Headers)
{
builderFinal.AppendLine("[" + header.Name + "] " + header.TypeFormatted);
if (last != header)
builderFinal.Append(", ");
}
builderFinal.AppendLine(");");
foreach (var bulkInsert in builderList)
{
builderFinal.AppendLine();
builderFinal.Append("INSERT INTO ");
builderFinal.Append(tableName);
builderFinal.Append(" ");
builderFinal.AppendLine("VALUES");
builderFinal.AppendLine(bulkInsert.ToString());
}
}
return builderFinal.ToString();
}
