// 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);
}
