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