Created
May 23, 2021 09:36
-
-
Save goforgold/367f7eb0230c23c9ae900112b55cddfb to your computer and use it in GitHub Desktop.
Generate Insert and Update Query Using Entity (snake_case)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
private static List<(string name, string paramName, object value)> GenerateNameValuePair(object obj, params string[] ignoredProperties) | |
{ | |
List<(string name, string paramName, object value)> pairs = new List<(string name, string paramName, object value)>(); | |
var props = obj.GetType().GetProperties(); | |
foreach (var prop in props) | |
{ | |
if (!IsPrimitiveType(prop.PropertyType) || ignoredProperties.Contains(prop.Name)) continue; | |
pairs.Add((prop.Name.ToSnakeCase(), $"@{prop.Name.ToSnakeCase()}", prop.GetValue(obj))); | |
} | |
return pairs; | |
} | |
private static bool IsPrimitiveType(Type type) | |
{ | |
return | |
type.IsValueType || | |
type == typeof(string); | |
} | |
private static string GetTableName<T>() where T : class => typeof(T).Name.Substring(2).Pluralize().ToSnakeCase(); | |
private static NpgsqlCommand GenerateInsertQuery<T>(T obj, params string[] ignoredProperties) where T : class | |
{ | |
NpgsqlCommand query = new NpgsqlCommand(); | |
var pairs = GenerateNameValuePair(obj, ignoredProperties); | |
var allFields = string.Join(", ", pairs.Select(m => $@"""{m.name}""")); | |
var parametersName = string.Join(", ", pairs.Select(m => m.paramName)); | |
var parameters = pairs.Select(m => | |
{ | |
var param = new NpgsqlParameter(m.paramName, m.value ?? DBNull.Value); | |
if (m.value != null && m.value.GetType().IsEnum) | |
{ | |
param.Value = (byte)m.value; | |
} | |
return param; | |
}).ToArray(); | |
query.CommandText = $"INSERT INTO {GetTableName<T>()} ({allFields}) VALUES({parametersName})"; | |
query.Parameters.AddRange(parameters); | |
return query; | |
} | |
private static NpgsqlCommand GenerateUpdateQuery<T>(T obj, Expression<Func<T, object>> keyExpression, params string[] ignoredProperties) where T : class | |
{ | |
NpgsqlCommand query = new NpgsqlCommand(); | |
var pairs = GenerateNameValuePair(obj, ignoredProperties); | |
string allFields = string.Join(", ", pairs.Select(m => $@"""{m.name}"" = {m.paramName}")); | |
NpgsqlParameter[] parameters = pairs.Select(m => | |
{ | |
var param = new NpgsqlParameter(m.paramName, m.value ?? DBNull.Value); | |
if (m.value != null && m.value.GetType().IsEnum) | |
{ | |
param.Value = (byte)m.value; | |
} | |
return param; | |
}).ToArray(); | |
string primaryKeyFieldName = ((keyExpression.Body as UnaryExpression).Operand as MemberExpression).Member.Name.ToSnakeCase(); | |
object primaryKeyParamName = pairs.First(m => m.name == primaryKeyFieldName).paramName; | |
query.CommandText = $"UPDATE {GetTableName<T>()} SET {allFields} WHERE {primaryKeyFieldName} = {primaryKeyParamName}"; | |
query.Parameters.AddRange(parameters); | |
return query; | |
} | |
private static NpgsqlCommand GenerateUpdateQuery<T>(T obj, string[] primaryKeyProps, params string[] ignoredProperties) where T : class | |
{ | |
NpgsqlCommand query = new NpgsqlCommand(); | |
List<(string name, string paramName, object value)> pairs = GenerateNameValuePair(obj, ignoredProperties); | |
string allFields = string.Join(", ", pairs.Select(m => $@"""{m.name}"" = {m.paramName}")); | |
NpgsqlParameter[] parameters = pairs.Select(m => | |
{ | |
var param = new NpgsqlParameter(m.paramName, m.value ?? DBNull.Value); | |
if (m.value != null && m.value.GetType().IsEnum) | |
{ | |
param.Value = (byte)m.value; | |
} | |
return param; | |
}).ToArray(); | |
List<string> primaryKeyConditions = new List<string>(); | |
foreach (var primaryKeyProp in primaryKeyProps) | |
{ | |
string primaryKeyFieldName = primaryKeyProp.ToSnakeCase(); | |
string primaryKeyParamName = pairs.First(m => m.name == primaryKeyFieldName).paramName; | |
primaryKeyConditions.Add($"{primaryKeyFieldName} = {primaryKeyParamName}"); | |
} | |
string primaryKeyConditionStr = string.Join(" AND ", primaryKeyConditions); | |
query.CommandText = $"UPDATE {GetTableName<T>()} SET {allFields} WHERE {primaryKeyConditionStr}"; | |
query.Parameters.AddRange(parameters); | |
return query; | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment