public int GenerateByDbTable(DataTable dt, TextWriter writer)
{
string[] columns = dt.Columns.ToEnumerable <DataColumn, string>(col => col.ColumnName).ToArray();
object[] values = new object[columns.Length];
foreach (DataRow row in dt.Rows)
{
values = row.ItemArray;
var pairs = new ColumnPairCollection(columns, values);
GenerateRow(writer, pairs);
count++;
if (count % 5000 == 0)
{
writer.WriteLine(SqlScript.GO);
}
}
if (count != 0)
{
writer.WriteLine(SqlScript.GO);
}
return(count);
}
public string UPDATE(ColumnPairCollection pairs)
{
var L1 = pairs
.Where(column => !ik.Contains(column.ColumnName))
.Where(column => !pk.Contains(column.ColumnName))
.Where(column => !ck.Contains(column.ColumnName))
.Select(p => $"{p.ColumnName.ColumnName()} = {p.Value.ToScript()}");
string update = string.Join(",", L1);
return(template.Update(update, WHERE(pairs)));
}
public string INSERT(ColumnPairCollection pairs, bool InsertWithoutColumns = false)
{
var L1 = pairs
.Where(column => !ik.Contains(column.ColumnName))
.Where(column => !ck.Contains(column.ColumnName));
var x1 = L1.Select(p => p.ColumnName.ColumnName());
var x2 = L1.Select(p => p.Value.ToScript());
if (InsertWithoutColumns)
{
return(template.Insert(string.Join(",", x2)));
}
else
{
return(template.Insert(string.Join(",", x1), string.Join(",", x2)));
}
}
private int GenerateByDbReader(DbDataReader reader, TextWriter writer, IProgress <int> progress)
{
DataTable schema1 = reader.GetSchemaTable();
string[] columns = schema1.AsEnumerable().Select(row => row.Field <string>("ColumnName")).ToArray();
object[] values = new object[columns.Length];
int step = 0;
//this is signle table
//while (reader.HasRows)
{
while (reader.Read())
{
step++;
if (step % 19 == 0)
{
progress?.Report(step);
}
reader.GetValues(values);
var pairs = new ColumnPairCollection(columns, values);
GenerateRow(writer, pairs);
count++;
if (count % 5000 == 0)
{
writer.WriteLine(SqlScript.GO);
}
}
reader.NextResult();
}
if (count != 0)
{
writer.WriteLine(SqlScript.GO);
}
return(count);
}
private void GenerateRow(TextWriter writer, ColumnPairCollection pairs)
{
switch (type)
{
case SqlScriptType.INSERT:
if (Option.HasIfExists)
{
writer.WriteLine(script.IF_NOT_EXISTS_INSERT(pairs));
}
else
{
writer.WriteLine(script.INSERT(pairs, Option.InsertWithoutColumns));
}
break;
case SqlScriptType.UPDATE:
writer.WriteLine(script.UPDATE(pairs));
break;
case SqlScriptType.INSERT_OR_UPDATE:
writer.WriteLine(script.IF_NOT_EXISTS_INSERT_ELSE_UPDATE(pairs));
break;
}
}
public string IF_NOT_EXISTS_INSERT_ELSE_UPDATE(ColumnPairCollection pairs)
{
return(template.IfNotExistsInsertElseUpdate(WHERE(pairs), INSERT(pairs), UPDATE(pairs)));
}
public string IF_NOT_EXISTS_INSERT(ColumnPairCollection pairs)
{
return(template.IfNotExistsInsert(WHERE(pairs), INSERT(pairs)));
}
private string WHERE(ColumnPairCollection pairs)
{
var L1 = pairs.Where(p => pk.Contains(p.ColumnName)).ToArray();
return(string.Join <ColumnPair>(" AND ", L1));
}
