public static int BatchInsert <T>(List <T> list, string con, string table, string id_col, bool is_auto_inc) where T : class
{
var type = list[0].GetType();
var pis = type.GetProperties().Where(x => x.PropertyType.IsSealed && (x.Name != id_col || !is_auto_inc)).ToList();
int len = pis.Count;
MySqlTable dt = new MySqlTable {
cols = new string[len], data = new object[list.Count, len]
};
for (int i = 0; i < pis.Count; i++)
{
if (pis[i].Name == id_col && is_auto_inc)
{
i--;
continue;
}
dt.cols[i] = pis[i].Name;
for (int j = 0; j < list.Count; j++)
{
dt.data[j, i] = pis[i].GetValue(list[j]);
}
}
return(BatchInsert(con, table, dt));
}
public static int BatchInsert(string con, string table_name, MySqlTable table)
{
var paras = new List <MySqlParameter>();
var sb = new StringBuilder();
try
{
string sql = "insert " + table_name + "(" + string.Join(",", table.cols) + ") values ";
sb.Append(sql);
int rows = table.data.GetLength(0), cols = table.data.GetLength(1);
for (int r = 0; r < rows; r++)
{
sb.Append("(");
for (int c = 0; c < cols; c++)
{
var para = "@" + table.cols[c] + "para" + r;
sb.Append(para);
if (c < cols - 1)
{
sb.Append(",");
}
paras.Add(new MySqlParameter(para, table.data[r, c]));
}
sb.Append(")");
if (r < rows - 1)
{
sb.Append(",");
}
}
return(MySqlDbHelper.ExecuteSql(con, sb.ToString(), paras));
}
catch (Exception ex)
{
//LogerHelper.WriteErrorLog("{0}\r\n{1}".FormatStr(ex.Message, ex.StackTrace));
ExceptionHelper.LogExceptionErr(ex);
try
{
return(MySqlDbHelper.ExecuteSql(con, sb.ToString(), paras));
}
catch (Exception ex1)
{
//LogerHelper.WriteErrorLog("{0}\r\n{1}".FormatStr(ex.Message, ex.StackTrace));
ExceptionHelper.LogExceptionErr(ex1);
try
{
return(MySqlDbHelper.ExecuteSql(con, sb.ToString(), paras));
}
catch (Exception ex2)
{
//LogerHelper.WriteErrorLog("{0}\r\n{1}".FormatStr(ex.Message, ex.StackTrace));
ExceptionHelper.LogExceptionErr(ex2);
}
}
return(0);
}
}
public static int Upsert <T>(T t, string con, string table, string id_col, bool is_auto_inc) where T : class
{
var type = t.GetType();
var pis = type.GetProperties().Where(x => x.PropertyType.IsSealed && (x.Name != id_col || !is_auto_inc)).ToList();
var id_pi = type.GetProperty(id_col);
object id = id_pi.GetValue(t);
string sql = "select * from {0} where {1}='{2}'".FormatStr(table, id_col, id);
var o = GetEntities <T>(con, sql).FirstOrDefault();
if (o == null)
{
int len = pis.Count;
MySqlTable dt = new MySqlTable {
cols = new string[len], data = new object[1, len]
};
for (int i = 0; i < pis.Count; i++)
{
dt.cols[i] = pis[i].Name;
dt.data[0, i] = pis[i].GetValue(t);
}
return(BatchInsert(con, table, dt));
}
else
{
StringBuilder sb = new StringBuilder();
sb.Append("update " + table + " set ");
List <MySqlParameter> paras = new List <MySqlParameter>();
foreach (var pi in pis)
{
string new_val = "{0}".FormatStr(pi.GetValue(t));
string org_val = "{0}".FormatStr(pi.GetValue(o));
if (string.IsNullOrEmpty(new_val) || new_val == org_val)
{
continue;
}
var para = "@" + pi.Name;
paras.Add(new MySqlParameter(para, pi.GetValue(t)));
sb.Append(" {0}.{1}={2},".FormatStr(table, pi.Name, para));
}
sb.Remove(sb.Length - 1, 1);
string id_para = "@para_" + id_col;
paras.Add(new MySqlParameter(id_para, id));
sb.Append(" where {0}={1}".FormatStr(id_col, id_para));
sql = sb.ToString();
if (string.IsNullOrEmpty(sql.SubAfter("set").SubBefore(" where ").GetTrimed()))
{
return(0);
}
return(ExecuteSql(con, sql, paras));
}
}
