public string ParametersDataGet(string procedure, string schema, string procType, Dictionary <string, string> nonIdDict) { string nonIdParams = ""; var tableName = NameModifier.TableNameGet(procedure); if (IsTableExists(tableName, schema) && !tableName.Contains("Status")) { var pkName = NameModifier.PkNameGet(procedure); var pk = PrimaryKeyIdGet(schema, tableName, pkName); nonIdParams = NonIdParametersDataGet(schema, procType, tableName, pkName, pk, nonIdDict); } return(nonIdParams); }
public List <string> IdListParametersGet(Dictionary <string, string> listIdDict, string schema) { var idListParams = new List <string>(); if (listIdDict.Count > 0) { var declareString = ""; var listId = ""; var i = 0; foreach (var key in listIdDict.Keys) { var tableName = NameModifier.TableNameGet(key); if (IsTableExists(tableName, schema)) { declareString += $" declare @p{i} dbo.{listIdDict[key]} "; listId += $" ,{key} = @p{i} "; var columnName = NameModifier.ColumnNameGet(key); var query = $"SELECT TOP(3) {columnName} from [{schema}].{tableName}"; SqlConnection connection = new SqlConnection(connString); try { connection.Open(); var cmd = new SqlCommand(query, connection); using SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { declareString += $" ,insert into @p{i} values ('{reader[columnName]}') "; } } finally { connection.Close(); } } i++; } idListParams.Add(declareString); idListParams.Add(listId); } return(idListParams); }
public static Dictionary <string, ColumnTablePair> TableAndColumnNamesSet(string schema, string procType, string procedure, List <string> IdList, DataTable TableNames) { var paramColumnTable = new Dictionary <string, ColumnTablePair>(); //string usersSchema = UsersSchemaNameGet(TableNames); foreach (var name in IdList) { if (!name.Contains("ByUserId")) { var tableName = NameModifier.TableNameGet(name); var columnName = NameModifier.ColumnNameGet(name); if (IsTableExists(tableName, schema, TableNames) && !(procType == "Add" && tableName == NameModifier.TableNameGet(procedure))) { paramColumnTable.Add(name, new ColumnTablePair(columnName, $"[{schema}].[{tableName}]")); } } } return(paramColumnTable); }
public string IdParametersDataGet(string paramName, string schema) { var paramId = ""; var tableName = NameModifier.TableNameGet(paramName); if (IsTableExists(tableName, schema)) { SqlConnection connection = new SqlConnection(connString); //finds first occurency of suitable data for a parameter try { connection.Open(); var columnName = NameModifier.ColumnNameGet(paramName); var query = $"SELECT TOP(1) {columnName} FROM {schema}.{tableName} WHERE IsDeleted = 0 ORDER BY NEWID();"; var cmd = new SqlCommand(query, connection); using SqlDataReader reader = cmd.ExecuteReader(); if (reader.Read()) { paramId = reader[columnName].ToString(); } else { paramId = "NULL"; } } finally { connection.Close(); } } return(paramId); }