public string GenerateScript(string filePath, ScriptModel script)
{
if (string.IsNullOrEmpty(filePath) || !File.Exists(filePath))
{
throw new FileNotFoundException();
}
StringBuilder sb = new StringBuilder();
if (!string.IsNullOrEmpty(script.DBDetails.InitialCatalog))
{
sb.AppendLine($"USE {script.DBDetails.InitialCatalog} \nGO");
}
if (string.IsNullOrEmpty(script.DBDetails.TableName))
{
script.DBDetails.TableName = "PlaceHolderTable";
}
sb.AppendLine($"IF NOT EXISTS(SELECT 1 FROM SYS.OBJECTS WHERE NAME = '{script.DBDetails.TableName}')");
sb.AppendLine($"CREATE TABLE {script.DBDetails.TableName} (");
foreach (Row col in script.Rows)
{
sb.AppendLine($"[{col.ColumnName}] { (col.DataType.ToLower() == "string" ? "VARCHAR(MAX)" : col.DataType) }");
}
sb.AppendLine(")");
sb.AppendLine("GO");
using (var fs = File.OpenRead(filePath))
using (DataSet fileDS = ExcelReaderFactory.CreateReader(fs).AsDataSet())
{
int rowIndex = 0;
foreach (DataRow row in fileDS.Tables[script.SheetName].Rows)
{
StringBuilder tableValues = new StringBuilder();
if (rowIndex == 0)
{
sb.AppendLine(PrepareInsertStatement(script));
}
rowIndex++;
foreach (var col in script.Rows)
{
if (col.DataType.ToLower() == "datetime")
{
tableValues.Append($"'{ DateTime.ParseExact(Convert.ToString(row[col.ColumnName]), col.ExtraInfo, CultureInfo.InvariantCulture)}',");
}
else
{
string cellValue = Convert.ToString(row[col.ColumnName]);
col.ExtraInfo = col.ExtraInfo ?? "";
foreach (var ch in col.ExtraInfo.ToCharArray())
{
cellValue = cellValue.Replace(ch.ToString(), "\\" + ch);
}
tableValues.Append($"'{cellValue }',");
}
}
sb.AppendLine("(");
sb.AppendLine(tableValues.ToString().TrimEnd(','));
sb.Append("),");
if (rowIndex == 500)
{
rowIndex = 0;
}
}
}
var targetFilePath = Path.Combine(Directory.GetParent(filePath).FullName, Path.GetFileNameWithoutExtension(filePath) + "_Output.sql");
File.WriteAllText(targetFilePath, sb.ToString().TrimEnd(','));
return(Path.GetFileName(targetFilePath));
}
