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);
}
private string AllParametersGet(string procedure, string schema, string procType, List <string> idList, Dictionary <string, string> nonIdDict, List <string> userIdList, string userSchema, Dictionary <string, ColumnTablePair> data)
{
var nonIdParams = "";
if (nonIdDict.Count > 0)
{
nonIdParams = ParametersDataGet(procedure, schema, procType, nonIdDict);
}
var idParams = "";
if (procType == "Get")
{
idParams = RelatedParametersDataGet(idList, CommonIdsTable, schema);
}
else
{
idParams = IdParametersDataGet(data);
}
var specialParams = NameModifier.SpecialParamaterNamesSet(ParametersDataTable);
var userIdParams = "";
if (userIdList.Count > 0)
{
userIdParams = UserIdsGet(userIdList, userSchema);
}
return(nonIdParams + idParams + specialParams + userIdParams);
}
public string UserIdsGet(List <string> userIdsList, string userSchema)
{
var userParams = "";
if (userIdsList.Count > 0)
{
var userId = "";
var userPk = NameModifier.UserPkNameGet(userIdsList[0]);
SqlConnection connection = new SqlConnection(connString);
try
{
connection.Open();
var query = $"SELECT TOP(1) {userPk} FROM [{userSchema}].[Users]";
var cmd = new SqlCommand(query, connection);
using SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
userId = reader["UserId"].ToString();
}
}
finally
{
connection.Close();
}
foreach (var name in userIdsList)
{
userParams += $" ,{name} = '{userId}' ";
}
}
return(userParams);
}
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);
}
//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 string DataGet(string procedure, string schema, string procType)
{
ParametersDataTable = ParametersTableGet(procedure, schema);
var idList = NameModifier.IdParamaterNamesSet(ParametersDataTable);
var userList = NameModifier.UserParamaterNamesSet(ParametersDataTable);
var nonIdDict = NameModifier.NonIdParamaterNamesSet(ParametersDataTable);
var userIdList = NameModifier.UserIdNamesSet(userList, procType);
var listIdDict = NameModifier.ListIdParamaterNamesSet(ParametersDataTable);
var userSchema = NameModifier.UsersSchemaNameSet(TableNames);
var data = NameModifier.TableAndColumnNamesSet(schema, procType, procedure, idList, TableNames);
var allParams = AllParametersGet(procedure, schema, procType, idList, nonIdDict, userIdList, userSchema, data);
var idListParams = IdListParametersGet(listIdDict, schema);
var query = QueryGet(schema, procedure, allParams, idListParams);
Clear();
return(query);
}
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);
}