public string GetParametersWithoutDataTypes(string tableName, string dbName)
{
var primaryKeyName = PrimaryKeyGetter.GetTablePrimaryKeyName(tableName, dbName);
string selectParameters = "@" + primaryKeyName;
return(selectParameters);
}
/// <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);
}
public string GetParameters(string tableName, string dbName)
{
var primaryKeyName = PrimaryKeyGetter.GetTablePrimaryKeyName(tableName, dbName);
string selectParameters = "@" + primaryKeyName + " int";
return(selectParameters);
}
/// <summary>
/// Read the update 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 ReadUpdateStoredProcedure(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();
var xdoc = XDocument.Load(xmlPath);
var updateSp = xdoc.Descendants("SpsTemplate").Descendants("UpdateStoredProcedure").FirstOrDefault(); //e => e.Name == "UpdateStoredProcedure");
if (updateSp != null)
{
var updateSpTemplate = updateSp.Value;
var updateParams = new UpdateParameters();
var updateParamsWithDataTypes = updateParams.GetParameters(tableName, dbName);
var primaryIdName = PrimaryKeyGetter.GetTablePrimaryKeyName(tableName, dbName);
var updateColumnsWithEqual = UpdateProcedureAssigner.GetUpdateProcedureParametersWithEqual(tableName, dbName);
updateSpTemplate = updateSpTemplate.Replace("@TableName", tableName);
updateSpTemplate = updateSpTemplate.Replace("@ScriptDateTime", DateTime.Now.ToString());
updateSpTemplate = updateSpTemplate.Replace("@UpdateParameters", updateParamsWithDataTypes);
updateSpTemplate = updateSpTemplate.Replace("@UpdateColumnsParameters", updateColumnsWithEqual);
updateSpTemplate = updateSpTemplate.Replace("@PrimaryIDName", primaryIdName);
updateSpTemplate = updateSpTemplate.Replace("@PrimaryIDParameter", "@" + primaryIdName);
return(updateSpTemplate);
}
return(string.Empty);
}
/// <summary>
/// Read the select all 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 ReadDeleteStoredProcedure(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();
var xdoc = XDocument.Load(xmlPath);
var selectSp = xdoc.Descendants("SpsTemplate").Descendants("DeleteStoredProcedure").FirstOrDefault();
if (selectSp != null)
{
var deleteSp = selectSp.Value;
var primaryKeyName = PrimaryKeyGetter.GetTablePrimaryKeyName(tableName, dbName);
deleteSp = deleteSp.Replace("@TableName", tableName);
deleteSp = deleteSp.Replace("@ScriptDateTime", DateTime.Now.ToString());
deleteSp = deleteSp.Replace("@DeleteParameters", "@" + primaryKeyName + " int");
deleteSp = deleteSp.Replace("@PrimaryIDName", primaryKeyName);
deleteSp = deleteSp.Replace("@PrimaryIDParameter", "@" + primaryKeyName);
return(deleteSp);
}
return(string.Empty);
}
public TableColumnsGetter(string conStr, string tableName, string dbName)
{
_tableName = tableName;
_databaseName = dbName;
_connectionString = conStr;
_primaryKeyName = PrimaryKeyGetter.GetTablePrimaryKeyName(_tableName, _databaseName);
}
public static string GetUpdateProcedureParametersWithEqual(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 connector = new SqlServerConnector();
connector.OpenConnection(new SqlServerConnection(connectionStr));
var primaryKeyName = PrimaryKeyGetter.GetTablePrimaryKeyName(tableName, dbName);
SqlCommand command = new SqlCommand("SELECT * FROM " + dbName + ".INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'" + tableName + "'",
connector.Connection.connection);
var reader = command.ExecuteReader(CommandBehavior.CloseConnection);
string updateParametersWithEqual = string.Empty;
while (reader.Read())
{
if (reader[3].ToString().ToLower() != primaryKeyName.ToLower())
{
updateParametersWithEqual += reader[3].ToString() + " = @" + reader[3].ToString() + ",";
}
//columns.Add(new TableColumn()
//{
// ColumnName = reader[3].ToString(),
// ColumnLimit = reader[8] != DBNull.Value ? reader[8].ToString() : "",
// ColumnType = GetColumnType(reader[7].ToString())
//});
}
updateParametersWithEqual = updateParametersWithEqual.Substring(0, updateParametersWithEqual.Length - 1);
connector.CloseConnection();
return(updateParametersWithEqual);
}
/// <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);
}