private static int CommandCreate(string TableName, DataBaseIO output)
{
string sqlCommand_Create =
$"INSERT INTO {TableName} " +
$"({string.Join(",", output.Data.Keys)}, createdAt, updatedAt)" +
$" VALUES " +
$"('{string.Join("','", (from val in output.Data.Values select (PublicTools.EncodeString(val))).ToArray())}', '{DateTime.Now}', '{DateTime.Now}')";
SqlCommand command_Create = new SqlCommand(sqlCommand_Create, sqlConnection);
return(command_Create.ExecuteNonQuery());
}
private static int CommandUpdate(string TableName, string ObjectID, DataBaseIO output)
{
string sqlCommand_Update =
$"UPDATE {TableName} " +
$"SET {string.Join(",", (from q in output.Data select $"{q.Key} = '{PublicTools.EncodeString(q.Value)}' ").ToArray())}, updatedAt = '{DateTime.Now}' " +
$"WHERE objectId = '{ObjectID}'";
SqlCommand command_Update = new SqlCommand(sqlCommand_Update, sqlConnection);
return(command_Update.ExecuteNonQuery());
}
private static DataBaseIO[] GetFirstRecord(string tableName, string Column, object Value)
=> SQLQueryCommand($"SELECT TOP(1) * FROM {tableName} WHERE {Column} = '{PublicTools.EncodeString(Value)}' ");
private static string BuildQueryString(string TableName, DBQuery dbQuery)
{
string sqlCommand_Query = $"SELECT TOP({dbQuery._Limit}) * FROM {TableName}{((dbQuery.EqualTo.Count > 0 || dbQuery.Contains.Count > 0 || dbQuery.ContainedInArray.Count > 0) ? " WHERE " : "")}";
if (dbQuery.EqualTo.Count > 0)
{
string[] queriesStringCollection = (from q in dbQuery.EqualTo select $"{q.Key} = '{PublicTools.EncodeString(q.Value.ToString())}'").ToArray();
sqlCommand_Query += "(" + string.Join(" AND ", queriesStringCollection) + ")";
sqlCommand_Query += (dbQuery.ContainedInArray.Count > 0 || dbQuery.Contains.Count > 0) ? " AND " : string.Empty;
}
if (dbQuery.ContainedInArray.Count > 0)
{
List <string> containsSQLList = new List <string>();
foreach (var item in dbQuery.ContainedInArray)
{
containsSQLList.Add($"( {item.Key} IN ('{string.Join("', '", item.Value)}'))");
}
string finalQueryString = string.Join(" OR ", containsSQLList.ToArray());
sqlCommand_Query += "(" + finalQueryString + ")";
sqlCommand_Query += (dbQuery.Contains.Count > 0) ? " AND " : string.Empty;
}
if (dbQuery.Contains.Count > 0)
{
string[] queriesStringCollection = (from q in dbQuery.Contains select $"{q.Key} LIKE '%{PublicTools.EncodeString(q.Value)}%'").ToArray();
sqlCommand_Query += string.Join(" AND ", queriesStringCollection);
}
return(sqlCommand_Query);
}