public string RelatedParametersDataGet(List <string> idList, DataTable dataTable, string schema) { DataColumnCollection columns = dataTable.Columns; DataRowCollection rows = dataTable.Rows; var relatedIds = ""; var amount = rows.Count; if (amount > 0) { var random = new Random(); DataRow row = rows[random.Next(0, amount - 1)]; foreach (var parameter in idList) { var columnName = NameModifier.ColumnNameGet(parameter); var id = ""; if (columns.Contains(columnName)) { id = row[columnName].ToString(); } if (String.IsNullOrEmpty(id)) { id = IdParametersDataGet(parameter, schema); relatedIds += $" ,{parameter} = '{id}' "; } else { relatedIds += $" ,{parameter} = '{id}' "; } } } return(relatedIds); }
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); }
//Function for Get stored procs only public string NonIdParametersDataGet(Dictionary <string, string> nonIdDict, string schema, string tableName, string pkName, string pk) { var nonIdParams = ""; SqlConnection connection = new SqlConnection(connString); try { connection.Open(); var paramNames = NameModifier.ColumnNameGet(nonIdDict); var query = $"SELECT TOP(1) {string.Join(",", paramNames)} FROM [{schema}].{tableName} WHERE {pkName} = '{pk}' and IsDeleted = 0 ORDER BY DtLastUpdated DESC"; var cmd = new SqlCommand(query, connection); using SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { foreach (var par in paramNames) { if (reader[par].ToString() == "False") { nonIdParams += $" ,@filter_{par}_eq = 0"; } else if (reader[par].ToString() == "True") { nonIdParams += $" ,@filter_{par}_eq = 1"; } else { nonIdParams += $" ,@filter_{par}_eq = '{reader[par]}'"; } } } } finally { connection.Close(); } return(nonIdParams); }
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); }