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