public static int DeleteByID(object id,Type type)
{
if (mySqlclient == null)
mySqlclient = MySqlClient.GetMySqlClient();
DataAttr dataAttr = (DataAttr)type.GetCustomAttribute(typeof(DataAttr), false);
StringBuilder sb = new StringBuilder();
string key = "";
Type keyType = null;
sb.Append("DELETE FROM ");
sb.Append(dataAttr.TableName);
sb.Append(" WHERE ");
PropertyInfo[] propertyInfos = type.GetProperties();
foreach (PropertyInfo info in propertyInfos)
{
DataAttr infoAttr = (DataAttr)info.GetCustomAttribute(typeof(DataAttr), false);
if (infoAttr == null)
continue;
if (infoAttr.Key)
{
key = info.Name;
keyType = info.PropertyType;
sb.Append(key + " =@"+ key);
break;
}
}
sb.Append(ANDCOMPANYID);
List<MySqlParameter> parameters = new List<MySqlParameter>();
MySqlParameter parameter = new MySqlParameter("@"+key, mySqlclient.ConvertDBType(keyType));
parameter.Value = id;
parameters.Add(parameter);
return mySqlclient.ExecuteNonQuery(sb.ToString(), parameters, CommandType.Text);
}
/// <summary>
/// 根据Type获取DataTable
/// </summary>
/// <param name="type"></param>
/// <returns></returns>
public static DataTable SelectData(Type type)
{
if (mySqlclient == null)
{
mySqlclient = MySqlClient.GetMySqlClient();
}
DataAttr dataAttr = (DataAttr)type.GetCustomAttribute(typeof(DataAttr), false);
StringBuilder sb = new StringBuilder();
sb.Append("SELECT ");
PropertyInfo[] propertyInfos = type.GetProperties();
foreach (PropertyInfo info in propertyInfos)
{
DataAttr infoAttr = (DataAttr)info.GetCustomAttribute(typeof(DataAttr), false);
if (infoAttr == null)
{
continue;
}
sb.Append(info.Name + ",");
}
sb.Remove(sb.Length - 1, 1);//移除 多余的 ","
sb.Append(" FROM " + dataAttr.TableName + " WHERE CompanyId= ");
sb.Append(SystemConst.companyId);
DataSet ds = mySqlclient.GetDataSet(sb.ToString(), CommandType.Text);
return(ds.Tables[0]);
}
/// <summary>
/// 将DataRow 转成T
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="dr"></param>
/// <param name="colcount"></param>
/// <returns></returns>
private static T ConvertDataRowToT <T>(DataRow dr, int colcount)
{
Type type = typeof(T);
PropertyInfo[] propertyInfos = type.GetProperties();
T t = (T)Activator.CreateInstance(type);
object objPacked = t;
for (int j = 0; j < colcount && j < propertyInfos.Length; ++j)
{
PropertyInfo info = propertyInfos[j];
DataAttr infoAttr = (DataAttr)info.GetCustomAttribute(typeof(DataAttr), false);
if (infoAttr == null)
{
continue;
}
if (dr[j] == null)
{
info.SetValue(t, "");
}
else if (dr[j] == System.DBNull.Value)
{
info.SetValue(t, null);
}
else
{
info.SetValue(t, dr[j]);
}
}
t = (T)objPacked;
return(t);
}
public string GenerateInsertSql(object data)
{
Type type = data.GetType();
StringBuilder strb = new StringBuilder();
DataAttr dataAttr = (DataAttr)type.GetCustomAttribute(typeof(DataAttr), false);
if (dataAttr == null)
{
return(null);
}
strb.Append("insert into " + dataAttr.TableName + " (");
PropertyInfo[] pinfos = type.GetProperties();
foreach (PropertyInfo pinfo in pinfos)
{
DataAttr pdataAttr = (DataAttr)pinfo.GetCustomAttribute(typeof(DataAttr), false);
if (dataAttr == null)
{
break;
}
if (pdataAttr.Bquery)
{
strb.Append(pinfo.Name + ",");
}
}
strb.Remove(strb.Length - 1, 1);
strb.Append(") values (");
//值
foreach (PropertyInfo pinfo in pinfos)
{
DataAttr pdataAttr = (DataAttr)pinfo.GetCustomAttribute(typeof(DataAttr), false);
if (dataAttr == null)
{
break;
}
if (pdataAttr.Bquery)
{
object t = pinfo.GetValue(data);
if (t == null)
{
strb.Append("null,");
}
else
{
if (pinfo.PropertyType.Name.Equals("String") || pinfo.PropertyType.FullName.Contains("DateTime"))
{
strb.Append("'" + t + "',");
}
else
{
strb.Append(t + ",");
}
}
}
}
strb.Remove(strb.Length - 1, 1);
strb.Append(")");
return(strb.ToString());
}
public static List <T> GetTempOrderByRoomID <T>(int roomID)
{
List <T> tList = new List <T>();
if (mySqlclient == null)
{
mySqlclient = MySqlClient.GetMySqlClient();
}
Type type = typeof(T);
DataAttr dataAttr = (DataAttr)type.GetCustomAttribute(typeof(DataAttr), false);
StringBuilder sb = new StringBuilder();
string sql = @"SELECT * FROM TempOrder WHERE RoomID = @RoomID" + ANDCOMPANYID;
List <MySqlParameter> parameters = new List <MySqlParameter>()
{
new MySqlParameter("@RoomID", MySqlDbType.Int32)
};
parameters[0].Value = roomID;
DataSet ds = mySqlclient.GetDataSet(sql, parameters, CommandType.Text);
DataTable dt = ds.Tables[0];
PropertyInfo[] propertyInfos = type.GetProperties();
for (int i = 0; i < dt.Rows.Count; ++i)
{
T t = (T)Activator.CreateInstance(type);
//object objPacked = t;
for (int j = 0; j < ds.Tables[0].Columns.Count && j < propertyInfos.Length; ++j)
{
PropertyInfo info = propertyInfos[j];
DataAttr infoAttr = (DataAttr)info.GetCustomAttribute(typeof(DataAttr), false);
object value = dt.Rows[i][j];
if (infoAttr == null)
{
continue;
}
if (info.PropertyType.Name.Equals("String") && value == DBNull.Value)
{
info.SetValue(t, "");
}
else
{
if (value == DBNull.Value)
{
info.SetValue(t, null);
}
else
{
info.SetValue(t, value);
}
}
}
//t = (T)objPacked;
tList.Add(t);
}
return(tList);
}
public static void GetSkillCommisionByName <T>(string skillName, ref List <T> tList)
{
if (mySqlclient == null)
{
mySqlclient = MySqlClient.GetMySqlClient();
}
Type type = typeof(T);
DataAttr dataAttr = (DataAttr)type.GetCustomAttribute(typeof(DataAttr), false);
string sql = @"SELECT Id,SkillName,StaffLevel,PriceA,PriceB,PriceC,Remark
FROM SkillCommision WHERE SkillName = @SkillName" + ANDCOMPANYID;
List <MySqlParameter> parameters = new List <MySqlParameter>()
{
new MySqlParameter("@SkillName", MySqlDbType.String)
};
parameters[0].Value = skillName;
DataSet ds = mySqlclient.GetDataSet(sql, parameters, CommandType.Text);
DataTable dt = ds.Tables[0];
PropertyInfo[] propertyInfos = type.GetProperties();
for (int i = 0; i < dt.Rows.Count; ++i)
{
T t = (T)Activator.CreateInstance(type);
object objPacked = t;
for (int j = 0; j < ds.Tables[0].Columns.Count && j < propertyInfos.Length; ++j)
{
PropertyInfo info = propertyInfos[j];
DataAttr infoAttr = (DataAttr)info.GetCustomAttribute(typeof(DataAttr), false);
if (infoAttr == null)
{
continue;
}
if (dt.Rows[i][j] != null)
{
info.SetValue(t, dt.Rows[i][j]);
}
else
{
info.SetValue(t, "");
}
}
t = (T)objPacked;
tList.Add(t);
}
}
public static List <T> SelectData <T>(string conditionStr)
{
List <T> tList = new List <T>();
if (mySqlclient == null)
{
mySqlclient = MySqlClient.GetMySqlClient();
}
Type type = typeof(T);
DataAttr dataAttr = (DataAttr)type.GetCustomAttribute(typeof(DataAttr), false);
StringBuilder sb = new StringBuilder();
sb.Append("SELECT ");
PropertyInfo[] propertyInfos = type.GetProperties();
foreach (PropertyInfo info in propertyInfos)
{
DataAttr infoAttr = (DataAttr)info.GetCustomAttribute(typeof(DataAttr), false);
if (infoAttr == null)
{
continue;
}
sb.Append(info.Name + ",");
}
sb.Remove(sb.Length - 1, 1);//移除 多余的 ","
sb.Append(" FROM " + dataAttr.TableName + " WHERE CompanyId= ");
sb.Append(SystemConst.companyId);
sb.Append(" AND " + conditionStr);
List <MySqlParameter> parameters = new List <MySqlParameter>();
DataSet ds = mySqlclient.GetDataSet(sb.ToString(), parameters, CommandType.Text);
foreach (DataRow dr in ds.Tables[0].Rows)
{
T t = ConvertDataRowToT <T>(dr, ds.Tables[0].Columns.Count);
tList.Add(t);
}
return(tList);
}
public static List <T> SelectDataByID <T>(object id)
{
List <T> tList = new List <T>();
if (mySqlclient == null)
{
mySqlclient = MySqlClient.GetMySqlClient();
}
Type type = typeof(T);
DataAttr dataAttr = (DataAttr)type.GetCustomAttribute(typeof(DataAttr), false);
StringBuilder sb = new StringBuilder();
PropertyInfo[] propertyInfos = type.GetProperties();
string strkey = "";
Type keyType = null;
foreach (PropertyInfo info in propertyInfos)
{
DataAttr infoAttr = (DataAttr)info.GetCustomAttribute(typeof(DataAttr), false);
if (infoAttr.Key)
{
strkey = info.Name;
keyType = info.PropertyType;
break;
}
}
sb.Append(" SELECT * FROM " + dataAttr.TableName + " WHERE " + strkey + "=@" + strkey);
sb.Append(ANDCOMPANYID);
List <MySqlParameter> parameters = new List <MySqlParameter>();
MySqlParameter parameter = new MySqlParameter("@" + strkey, mySqlclient.ConvertDBType(keyType));
parameter.Value = id;
parameters.Add(parameter);
DataSet ds = mySqlclient.GetDataSet(sb.ToString(), parameters, CommandType.Text);
DataTable dt = ds.Tables[0];
for (int i = 0; i < dt.Rows.Count; ++i)
{
T t = (T)Activator.CreateInstance(type);
object objPacked = t;
for (int j = 0; j < ds.Tables[0].Columns.Count && j < propertyInfos.Length; ++j)
{
PropertyInfo info = propertyInfos[j];
DataAttr infoAttr = (DataAttr)info.GetCustomAttribute(typeof(DataAttr), false);
if (infoAttr == null)
{
continue;
}
if (dt.Rows[i][j] == null)
{
info.SetValue(t, "");
}
else if (dt.Rows[i][j] == System.DBNull.Value)
{
info.SetValue(t, null);
}
else
{
info.SetValue(t, dt.Rows[i][j]);
}
}
t = (T)objPacked;
tList.Add(t);
}
return(tList);
}
public static int InsertData(object data, Type type)
{
if (mySqlclient == null)
{
mySqlclient = MySqlClient.GetMySqlClient();
}
DataAttr dataAttr = (DataAttr)type.GetCustomAttribute(typeof(DataAttr), false);
StringBuilder sb = new StringBuilder();
sb.Append("INSERT INTO ");
sb.Append(dataAttr.TableName + "(");
PropertyInfo[] propertyInfos = type.GetProperties();
foreach (PropertyInfo info in propertyInfos)
{
DataAttr infoAttr = (DataAttr)info.GetCustomAttribute(typeof(DataAttr), false);
if (infoAttr == null)
{
continue;
}
if (infoAttr.Bquery)
{
sb.Append(info.Name + ",");
}
}
sb.Remove(sb.Length - 1, 1);//移除 多余的 ","
sb.Append(")");
sb.Append("VALUES(");
foreach (PropertyInfo info in propertyInfos)
{
DataAttr infoAttr = (DataAttr)info.GetCustomAttribute(typeof(DataAttr), false);
if (infoAttr == null)
{
continue;
}
if (infoAttr.Bquery)
{
sb.Append("@" + info.Name + ",");
}
}
sb.Remove(sb.Length - 1, 1);//移除 多余的 ","
sb.Append(")");
List <MySqlParameter> parameters = new List <MySqlParameter>();
for (int i = 0; i < propertyInfos.Length; ++i)
{
PropertyInfo info = propertyInfos[i];
DataAttr infoAttr = (DataAttr)info.GetCustomAttribute(typeof(DataAttr), false);
if (infoAttr == null)
{
continue;
}
if (infoAttr.Bquery)
{
string strPara = "@" + info.Name;
MySqlParameter parameter = new MySqlParameter(strPara, ConvertDBType(info.PropertyType));
parameter.Value = info.GetValue(data);
parameters.Add(parameter);
}
}
return(mySqlclient.ExecuteNonQuery(sb.ToString(), parameters, CommandType.Text));
}
public string GenerateUpdateSql(object data)
{
Type type = data.GetType();
DataAttr dataAttr = (DataAttr)type.GetCustomAttribute(typeof(DataAttr), false);
StringBuilder sb = new StringBuilder();
string key = "";
object keyValue = null;
Type keyType = null;
sb.Append("UPDATE ");
sb.Append(dataAttr.TableName);
sb.Append(" Set ");
PropertyInfo[] propertyInfos = type.GetProperties();
foreach (PropertyInfo info in propertyInfos)
{
DataAttr infoAttr = (DataAttr)info.GetCustomAttribute(typeof(DataAttr), false);
if (infoAttr == null)
{
continue;
}
if (infoAttr.Key)
{
key = info.Name;
keyValue = info.GetValue(data);
keyType = info.PropertyType;
}
else
{
object t = info.GetValue(data);
if (t == DBNull.Value || t == null)
{
if (info.PropertyType.Name.Equals("String"))
{
sb.Append(info.Name + " ='" + t + "',");
}
else
{
sb.Append(info.Name + " = null,");
}
}
else
{
if (info.PropertyType.Name.Equals("String") || info.PropertyType.FullName.Contains("DateTime"))
{
sb.Append(info.Name + " ='" + t + "',");
}
else
{
sb.Append(info.Name + " =" + t + ",");
}
}
}
}
sb.Remove(sb.Length - 1, 1);//移除 多余的 ","
if (keyType.Name.Equals("String") || keyType.Name.Equals("DateTime"))
{
sb.Append(" Where " + key + "='" + keyValue + "'");
}
else
{
sb.Append(" Where " + key + "=" + keyValue);
}
return(sb.ToString());
}
public TransactionParameter GenerateUpdateSqlWithParam <T>(T data)
{
Type type = data.GetType();
DataAttr dataAttr = (DataAttr)type.GetCustomAttribute(typeof(DataAttr), false);
StringBuilder sb = new StringBuilder();
string key = "";
object keyValue = null;
Type keyType = null;
sb.Append("UPDATE ");
sb.Append(dataAttr.TableName);
sb.Append(" Set ");
PropertyInfo[] propertyInfos = type.GetProperties();
foreach (PropertyInfo info in propertyInfos)
{
DataAttr infoAttr = (DataAttr)info.GetCustomAttribute(typeof(DataAttr), false);
if (infoAttr == null)
{
continue;
}
if (infoAttr.Key)
{
key = info.Name;
keyValue = info.GetValue(data);
keyType = info.PropertyType;
}
else
{
sb.Append(info.Name + " =@" + info.Name + " ,");
}
}
sb.Remove(sb.Length - 1, 1);//移除 多余的 ","
sb.Append(" Where " + key + " =@" + key);
List <MySqlParameter> parameters = new List <MySqlParameter>();
for (int i = 0; i < propertyInfos.Length; ++i)
{
PropertyInfo info = propertyInfos[i];
DataAttr infoAttr = (DataAttr)info.GetCustomAttribute(typeof(DataAttr), false);
if (infoAttr == null)
{
continue;
}
if (!infoAttr.Key)
{
string strPara = "@" + info.Name;
MySqlParameter parameter = new MySqlParameter(strPara, ConvertDBType(info.PropertyType));
parameter.Value = info.GetValue(data);
parameters.Add(parameter);
}
}
MySqlParameter keyParameter = new MySqlParameter("@" + key, ConvertDBType(keyType));
keyParameter.Value = keyValue;
parameters.Add(keyParameter);
TransactionParameter transPara = new TransactionParameter();
transPara.SqlString = sb.ToString();
transPara.SqlParameters = parameters;
return(transPara);
}
public TransactionParameter GenerateInsertSqlWithParam <T>(T data)
{
Type type = typeof(T);
DataAttr dataAttr = (DataAttr)type.GetCustomAttribute(typeof(DataAttr), false);
StringBuilder sb = new StringBuilder();
sb.Append("INSERT INTO ");
sb.Append(dataAttr.TableName + "(");
PropertyInfo[] propertyInfos = type.GetProperties();
foreach (PropertyInfo info in propertyInfos)
{
DataAttr infoAttr = (DataAttr)info.GetCustomAttribute(typeof(DataAttr), false);
if (infoAttr == null)
{
continue;
}
if (infoAttr.Bquery)
{
sb.Append(info.Name + ",");
}
}
sb.Remove(sb.Length - 1, 1);//移除 多余的 ","
sb.Append(")");
sb.Append("VALUES(");
foreach (PropertyInfo info in propertyInfos)
{
DataAttr infoAttr = (DataAttr)info.GetCustomAttribute(typeof(DataAttr), false);
if (infoAttr == null)
{
continue;
}
if (infoAttr.Bquery)
{
sb.Append("@" + info.Name + ",");
}
}
sb.Remove(sb.Length - 1, 1);//移除 多余的 ","
sb.Append(")");
List <MySqlParameter> parameters = new List <MySqlParameter>();
for (int i = 0; i < propertyInfos.Length; ++i)
{
PropertyInfo info = propertyInfos[i];
DataAttr infoAttr = (DataAttr)info.GetCustomAttribute(typeof(DataAttr), false);
if (infoAttr == null)
{
continue;
}
if (infoAttr.Bquery)
{
string strPara = "@" + info.Name;
MySqlParameter parameter = new MySqlParameter(strPara, ConvertDBType(info.PropertyType));
parameter.Value = info.GetValue(data);
parameters.Add(parameter);
}
}
TransactionParameter transPara = new TransactionParameter();
transPara.SqlString = sb.ToString();
transPara.SqlParameters = parameters;
return(transPara);
}
public static int UpdateByID(object data)
{
if (mySqlclient == null)
{
mySqlclient = MySqlClient.GetMySqlClient();
}
Type type = data.GetType();
DataAttr dataAttr = (DataAttr)type.GetCustomAttribute(typeof(DataAttr), false);
StringBuilder sb = new StringBuilder();
string key = "";
object keyValue = null;
Type keyType = null;
sb.Append("UPDATE ");
sb.Append(dataAttr.TableName);
sb.Append(" Set ");
PropertyInfo[] propertyInfos = type.GetProperties();
foreach (PropertyInfo info in propertyInfos)
{
DataAttr infoAttr = (DataAttr)info.GetCustomAttribute(typeof(DataAttr), false);
if (infoAttr == null)
{
continue;
}
if (infoAttr.Key)
{
key = info.Name;
keyValue = info.GetValue(data);
keyType = info.PropertyType;
}
else
{
sb.Append(info.Name + " =@" + info.Name + " ,");
}
}
sb.Remove(sb.Length - 1, 1);//移除 多余的 ","
sb.Append("Where " + key + " =@" + key);
sb.Append(ANDCOMPANYID);
List <MySqlParameter> parameters = new List <MySqlParameter>();
for (int i = 0; i < propertyInfos.Length; ++i)
{
PropertyInfo info = propertyInfos[i];
DataAttr infoAttr = (DataAttr)info.GetCustomAttribute(typeof(DataAttr), false);
if (infoAttr == null)
{
continue;
}
if (!infoAttr.Key)
{
string strPara = "@" + info.Name;
MySqlParameter parameter = new MySqlParameter(strPara, mySqlclient.ConvertDBType(info.PropertyType));
parameter.Value = info.GetValue(data);
parameters.Add(parameter);
}
}
MySqlParameter keyParameter = new MySqlParameter("@" + key, mySqlclient.ConvertDBType(keyType));
keyParameter.Value = keyValue;
parameters.Add(keyParameter);
return(mySqlclient.ExecuteNonQuery(sb.ToString(), parameters, CommandType.Text));
}
