// return 1 if success, 0 if failed public int UpdateOne <U, TU>(Dictionary <string, U> key, TU item, bool isInTransaction, string raiseOrDecrease, string table) { int af = 0; // if the item isn't the same type with schema, return 0 if (typeof(TU) != item.GetType()) { Console.WriteLine("The input item and the model isn't the same type"); return(af); } List <string> listFields = FieldsHandle.FilterField(item); string updateString = SqlStringBuilder.GetUpdateString(table, key, listFields, raiseOrDecrease); bool conOpened = con.State == ConnectionState.Open; if (!conOpened) { conOpened = OpenCon(); } if (conOpened) { mySqlCommand.CommandText = updateString; mySqlCommand.Parameters.Clear(); if (isInTransaction) { mySqlCommand.Transaction = tran; } else { mySqlCommand.Transaction = null; } HqlCommand cmd = new HqlCommand(mySqlCommand); cmd.AddValue(listFields, item); string keyFields = new List <string>(key.Keys)[0]; cmd.AddValue(keyFields, key[keyFields]); af = cmd.ExecuteNonQuery(); if (!isInTransaction) { CloseCon(); } } return(af); }
public MySqlDataReader ExecuteReader <TKey>(string scmd, Dictionary <string, TKey> value, bool isInTransaction) { MySqlDataReader reader = null; bool conOpened = con.State == ConnectionState.Open; if (!conOpened) { conOpened = OpenCon(); } if (conOpened) { mySqlCommand.CommandText = scmd; mySqlCommand.Parameters.Clear(); HqlCommand cmd = new HqlCommand(mySqlCommand); if (value != null) { cmd.AddValue(value); } reader = cmd.ExecuteReader(); if (!isInTransaction) { CloseCon(); } } return(reader); }
public int ExecuteNonQuery <TKey>(string scmd, Dictionary <string, TKey> value, bool isInTransaction) { int af = 0; bool conOpened = con.State == ConnectionState.Open; if (!conOpened) { conOpened = OpenCon(); } if (conOpened) { mySqlCommand.CommandText = scmd; mySqlCommand.Parameters.Clear(); HqlCommand cmd = new HqlCommand(mySqlCommand); if (value != null) { cmd.AddValue(value); } af = cmd.ExecuteNonQuery(); if (!isInTransaction) { CloseCon(); } } return(af); }
public int Delete <TKey>(Dictionary <string, TKey> condition) { int af = 0; string deleteString = SqlStringBuilder.GetDeleteString(table, new List <string>(condition.Keys)); if (OpenCon()) { mySqlCommand.CommandText = deleteString; mySqlCommand.Parameters.Clear(); HqlCommand cmd = new HqlCommand(mySqlCommand); cmd.AddValue(condition); af = cmd.ExecuteNonQuery(); CloseCon(); } return(af); }
public int InsertOne <TU>(TU item, bool isInTransaction, string table) where TU : class, new() { int af = 0; // if item and schema isn't the same type, return 0 if (typeof(TU) != item.GetType()) { Console.WriteLine("The input item and the model isn't the same type"); return(af); } List <string> listField = FieldsHandle.FilterField(item); // gen the insert string string insertString = SqlStringBuilder.GetInsertString(table, listField); // check if the connection is opened bool conOpened = con.State == ConnectionState.Open; // if not, open it if (!conOpened) { conOpened = OpenCon(); } if (conOpened) { mySqlCommand.CommandText = insertString; mySqlCommand.Parameters.Clear(); if (isInTransaction) { mySqlCommand.Transaction = tran; } else { mySqlCommand.Transaction = null; } HqlCommand cmd = new HqlCommand(mySqlCommand); cmd.AddValue(listField, item); af = cmd.ExecuteNonQuery(); // if the param willCloseCon == true, close the connection if (!isInTransaction) { CloseCon(); } } return(af); }
public List <TU> Find <TU, TKey>(Dictionary <string, TKey> condition, bool andOrCondition, bool isInTransaction, string table) where TU : class, new() // true: and condition, false: or condition { List <TU> listItem = new List <TU>(); List <string> listField = new List <string>(); if (condition != null) { listField = new List <string>(condition.Keys); } string selectString = SqlStringBuilder.GetSelectString(table, listField, andOrCondition); bool conOpened = con.State == ConnectionState.Open; if (!conOpened) { conOpened = OpenCon(); } if (conOpened) { mySqlCommand.CommandText = selectString; mySqlCommand.Parameters.Clear(); if (isInTransaction) { mySqlCommand.Transaction = tran; } else { mySqlCommand.Transaction = null; } HqlCommand cmd = new HqlCommand(mySqlCommand); if (condition != null) { cmd.AddValue(condition); } MySqlDataReader reader = cmd.ExecuteReader(); TU obj; object[] param; while (reader.Read()) { obj = new TU(); for (int i = 0; i < pi.Length; i++) { try { pi[i].SetValue(obj, reader[i]); //obj.GetType().GetMethod("set_" + pi[i].Name)?.Invoke(obj, param.Length == 0 ? null : param); } catch (Exception e) { Console.WriteLine("Error when set value for" + pi[i].Name + ": " + reader[i].GetType()); } } listItem.Add(obj); } reader.Close(); if (!isInTransaction) { CloseCon(); } } return(listItem); }