/// <summary>
/// Join文の作成(Select文に利用)
/// </summary>
/// <param name="dic">データ</param>
/// <param name="databaseName">DB名称</param>
/// <param name="tableName">テーブル名</param>
/// <returns></returns>
public static string CreateJoinSQLByDictionary(Dictionary <string, object> baseDic, string databaseName, string baseTableName, string targetTableName, bool isLeft = false)
{
var values = isLeft ? $@" LEFT JOIN {databaseName}.{targetTableName} ON " : $@" INNER JOIN {databaseName}.{targetTableName} ON ";
if (baseDic.Count <= 0)
{
return("");
}
foreach (var item in baseDic)
{
//最初の時
if (item.Key == baseDic.First().Key&& item.Value == baseDic.First().Value)
{
values += $@" {databaseName}.{baseTableName}.{NeetCommonMethod.CamelToSnake(item.Key)} = {databaseName}.{targetTableName}.{NeetCommonMethod.CamelToSnake(item.Key)}";
}
else
{
values += $@" AND {databaseName}.{baseTableName}.{NeetCommonMethod.CamelToSnake(item.Key)} = {databaseName}.{targetTableName}.{NeetCommonMethod.CamelToSnake(item.Key)} ";
}
}
values += ";";
var result = values;
return(result);
}
public bool isExist <T>(T dest)
{
var databaseName = GetDBName(dest);
var isRemoveAutoIncrement = true;
var tableName = NeetCommonMethod.CamelToSnake(dest.GetType().Name);
var pkdic = this.ToDictionaryProperty(true, !isRemoveAutoIncrement);
//SQL生成
var where = SQLCreater.CreateWhereSQLByDictionary(pkdic, databaseName, tableName);
var select = SQLCreater.MasterAllGetSQL(databaseName, tableName);
var sb = new StringBuilder();
sb.AppendLine(select);
sb.AppendLine(where);
NameValueCollection Values = new NameValueCollection();
Values["sql"] = sb.ToString();
Handler.URL = Handler.SelectURL;
var jsonStr = Handler.DoPost(Values);
var res = Handler.ConvertDeserialize(jsonStr);
//存在する
return(res.Count > 0);
}
/// <summary>
/// DictionaryToClass
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="dict"></param>
/// <returns></returns>
public static T DictionaryToClass <T>(Dictionary <object, object> dict)
{
Type type = typeof(T);
var obj = Activator.CreateInstance(type);
foreach (var kv in dict)
{
var getPro = type.GetProperty(NeetCommonMethod.SnakeToLowerCamel(kv.Key.ToString()));
var typeName = getPro.PropertyType.FullName;
if (typeName.Contains("Int"))
{
getPro.SetValue(obj, kv.Value.ToString().ToIntValue());
}
else if (typeName.Contains("Bool"))
{
var b = (kv.Value.ToString() == "true" || kv.Value.ToString() == "True");
getPro.SetValue(obj, b);
}
else
{
getPro.SetValue(obj, kv.Value.ToString());
}
}
return((T)obj);
}
static public List <T> GetFindAll <T>(T dest)
{
var databaseName = GetDBName(dest);
var tableName = NeetCommonMethod.CamelToSnake(dest.GetType().Name);
var select = SQLCreater.MasterAllGetSQL(databaseName, tableName);
NameValueCollection Values = new NameValueCollection();
Values["sql"] = select;
Handler.URL = Handler.SelectURL;
var jsonStr = Handler.DoPost(Values);
var res = Handler.ConvertDeserialize(jsonStr);
var list = res.Select(x => DictionaryToClass <T>(x)).ToList();
var result = list;
return(result);
}
public Dictionary <string, object> ToDictionaryProperty(bool isPrimary = false, bool isRemoveAutoIncrement = false, bool isSnake = true, bool isUpper = false)
{
var type = this.GetType();
var dic = new Dictionary <string, object>();
//メンバを取得する
var members = type.GetProperties(BindingFlags.Public | BindingFlags.NonPublic |
BindingFlags.Instance | BindingFlags.Static |
BindingFlags.DeclaredOnly);
foreach (PropertyInfo item in members)
{
//属性探知(プライマリーキー探索)
var primaryAttributes = item.GetCustomAttributes(typeof(PrimaryPropertyAttribute)).ToList();
var autoIncremenAttributes = item.GetCustomAttributes(typeof(AutoIncrementAttribute)).ToList();
var isPrimaryPropertyInfo = primaryAttributes.Count > 0 || autoIncremenAttributes.Count > 0;
var isAutoIncrementPropertyInfo = primaryAttributes.Count > 0;
var itemName = isSnake ? NeetCommonMethod.CamelToSnake(item.Name, isUpper) : item.Name;
//プライマリーキー探索有効
if (!isPrimary)
{
dic.Add(itemName, item.GetValue(this));
}
else
{
//プライマリーキー抽出
if (isPrimaryPropertyInfo)
{
//オートインクリメント削除探索有効
if (!isRemoveAutoIncrement)
{
dic.Add(itemName, item.GetValue(this));
}
else
{
//オートインクリメント削除
if (!isAutoIncrementPropertyInfo)
{
dic.Add(itemName, item.GetValue(this));
}
}
}
}
}
return(dic);
}
/// <summary>
/// 汎用更新メソッド（追加・更新を自動判定）
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="dest"></param>
/// <param name="databaseName"></param>
public void Register <T>(T dest)
{
//存在チェック
var isInsert = !isExist(dest);
var databaseName = GetDBName(dest);
var tableName = NeetCommonMethod.CamelToSnake(dest.GetType().Name);
var isRemoveAutoIncrement = true;
var dic = this.ToDictionaryProperty(false, !isRemoveAutoIncrement);
var pkdic = this.ToDictionaryProperty(true, isRemoveAutoIncrement);
var where = SQLCreater.CreateWhereSQLByDictionary(pkdic, databaseName, tableName);
var sql = isInsert ? SQLCreater.CreateInsertSQLByDictionary(dic, databaseName, tableName)
: SQLCreater.CreateUpdateSQLByDictionary(dic, databaseName, tableName, where)
;
NameValueCollection Values = new NameValueCollection();
Values["sql"] = sql;
Handler.URL = Handler.InsertAndUpdateURL;
Handler.DoPost(Values);
}
/// <summary>
/// Where文の作成(Update文に利用)
/// </summary>
/// <param name="dic">データ</param>
/// <param name="databaseName">DB名称</param>
/// <param name="tableName">テーブル名</param>
/// <returns></returns>
public static string CreateWhereSQLByDictionary(Dictionary <string, object> dic, string databaseName, string tableName)
{
var values = " WHERE ";
if (dic.Count <= 0)
{
return("");
}
foreach (var item in dic)
{
//最初の時
if (item.Key == dic.First().Key&& item.Value == dic.First().Value)
{
values += $@" {NeetCommonMethod.CamelToSnake(item.Key)} = '{item.Value}' ";
}
else
{
values += $@" AND {NeetCommonMethod.CamelToSnake(item.Key)} = '{item.Value}'";
}
}
var result = values;
return(result);
}
static void Snake()
{
var result = NeetCommonMethod.CamelToSnake("id");
Console.WriteLine(result);
}