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)); }