/// <summary>
/// Gets the insert parameters from columns names and data types.
/// </summary>
/// <param name="tableName"></param>
/// <param name="dbName"></param>
/// <returns></returns>
public string GetParameters(string tableName, string dbName)
{
var connectionStr = ConfigurationManager.ConnectionStrings["default"].ConnectionString;
if (string.IsNullOrEmpty(connectionStr))
{
connectionStr = "data source=(local); user Id=sa; password=P@ssw0rd;";
}
var columnsGetter = new TableColumnsGetter(connectionStr, tableName, dbName);
var tableColumnsList = columnsGetter.GetTableColumns().ToList();
// var tableColumns = columnsGetter.GetTableColumnsNames();
var primaryKeyName = PrimaryKeyGetter.GetTablePrimaryKeyName(tableName, dbName);
string insertParameters = string.Empty;
var columnsCount = tableColumnsList.Count();
for (int index = 0; index <= columnsCount - 1; index++)
{
if (tableColumnsList[index].ColumnName.ToLower() != primaryKeyName.ToLower())
{
insertParameters += "@" + tableColumnsList[index].ColumnName + " " + tableColumnsList[index].ColumnType.ToString();
var columnLimit = !string.IsNullOrEmpty(tableColumnsList[index].ColumnLimit) ? "(" + tableColumnsList[index].ColumnLimit + ")" : string.Empty;
insertParameters += " " + columnLimit;
if (index < columnsCount - 1)
{
insertParameters += " ,";
}
}
}
return(insertParameters);
}
/// <summary>
/// Get the parameters from columns names without the primary key.
/// </summary>
/// <param name="tableName"></param>
/// <param name="dbName"></param>
/// <returns></returns>
public string GetParametersWithoutDataTypes(string tableName, string dbName)
{
var connectionStr = ConfigurationManager.ConnectionStrings["default"].ConnectionString;
if (string.IsNullOrEmpty(connectionStr))
{
connectionStr = "data source=(local); user Id=sa; password=P@ssw0rd;";
}
var columnsGetter = new TableColumnsGetter(connectionStr, tableName, dbName);
var tableColumnsList = columnsGetter.GetTableColumns().ToList();
//var tableColumns = columnsGetter.GetTableColumnsNames();
string insertParameters = string.Empty;
var columnsCount = tableColumnsList.Count();
for (int index = 0; index <= columnsCount - 1; index++)
{
insertParameters += "@" + tableColumnsList[index].ColumnName;
if (index < columnsCount - 1)
{
insertParameters += ", ";
}
}
return(insertParameters);
}
/// <summary>
/// Read the insert stored procedure template from the xml file and replace the placeholder values.
/// </summary>
/// <param name="tableName"></param>
/// <param name="dbName"></param>
/// <returns></returns>
public string ReadInsertStoredProcedure(string tableName, string dbName)
{
var connectionStr = ConfigurationManager.ConnectionStrings["default"].ConnectionString;
if (string.IsNullOrEmpty(connectionStr))
{
connectionStr = "data source=(local); user Id=sa; password=P@ssw0rd;";
}
var xmlPath = GetXmlFilePath();
if (!string.IsNullOrEmpty(xmlPath))
{
var xdoc = XDocument.Load(xmlPath);
var insertSp = xdoc.Descendants("SpsTemplate").Descendants("InsertStoredProcedure").FirstOrDefault();
var columnsName = new TableColumnsGetter(connectionStr, tableName, dbName).GetTableColumnsNames();
var insertParams = new InsertParameters();
var insertParamsWithDataTypes = insertParams.GetParameters(tableName, dbName);
var insertParamsWithoutTypes = insertParams.GetParametersWithoutDataTypes(tableName, dbName);
if (insertSp != null)
{
var insertSpTemplate = insertSp.Value;
insertSpTemplate = insertSpTemplate.Replace("@TableName", tableName);
insertSpTemplate = insertSpTemplate.Replace("@ScriptDateTime", DateTime.Now.ToString());
insertSpTemplate = insertSpTemplate.Replace("@InsertParameters", insertParamsWithDataTypes);
insertSpTemplate = insertSpTemplate.Replace("@Columns", columnsName);
insertSpTemplate = insertSpTemplate.Replace("@SelectParameters", insertParamsWithoutTypes);
insertSpTemplate = insertSpTemplate.Replace("@PrimaryIDName", PrimaryKeyGetter.GetTablePrimaryKeyName(tableName, dbName));
return(insertSpTemplate);
}
}
return(string.Empty);
}
