private void Model2SQL <T>(IQuery query, Model2Sql sqlObject) { if (query == null) { } else { sqlObject.SqlType = Model2Db.Query; Dictionary <IQueryValue, QueryLink> dic = query.All(); int cur = 1; foreach (var item in dic) { if (cur != 1) { sqlObject.Sql = sqlObject.Sql + string.Format(" {0} ", item.Value.ToString()); } if (item.Key is QueryValue) { QueryValue2Model2Sql <T>(sqlObject, item); } else if (item.Key is Query) { Query2Model2Sql <T>(sqlObject, item); } else { throw new ArgumentException("未实现指定的IQuery类型"); } cur++; } } }
public MySqlCommand CreateCommand(MySqlConnection connnection, Model2Sql model) { MySqlCommand command = new MySqlCommand(model.Sql, connnection); foreach (var item in model.Param) { command.Parameters.AddWithValue(item.Key, item.Value); } return(command); }
public Model2Sql Model2SQL <T>(IOrder order) { if (order is Order) { Model2Sql model = GetOrderSQL <T>(order); model.Sql = string.Format("SELECT {0} FROM {1} ORDER BY {2}", CreateQuery <T>(), ReflectionHelper.GetTypeName <T>(), model.Sql); return(model); } else { throw new ArgumentException("不支持此类型" + order.GetType().FullName); } }
/// <summary> /// 将IQuery查询条件转化为sql语句 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="query"></param> /// <returns></returns> public Model2Sql Model2SQL <T>(IQuery query) { CheckKey <T>(); List <PropertyInfo> keys = ReflectionHelper.GetKeys <T>(); IOrder order = new Order(); foreach (var item in keys) { order.Add(item.Name, OrderDirection.Desc); } Model2Sql sqlObject = GetQuerySQL <T>(query); sqlObject.Sql = String.Format("SELECT {0} FROM {1} WHERE {2} ORDER BY {3}", CreateQuery <T>(), ReflectionHelper.GetTypeName <T>(), sqlObject.Sql, GetOrderSQL <T>(order).Sql); return(sqlObject); }
public Model2Sql Model2SQL <T>(IPager <T> pager, IQuery query, IOrder order) { if (pager == null) { throw new ArgumentException("pager不能为空"); } if (order == null) { throw new ArgumentException("order不能为空"); } CheckKey <T>(); Model2Sql model = GetQuerySQL <T>(query); model.Sql = string.Format("SELECT {0} FROM {1} WHERE {2} ORDER BY {3} LIMIT {4},{5}", CreateQuery <T>(), ReflectionHelper.GetTypeName <T>(), model.Sql, GetOrderSQL <T>(order).Sql, (pager.CurrentPage - 1) * pager.PageNum, pager.PageNum); return(model); }
public Model2Sql Model2SQL <T>(Model2Db sqlType, object model) { CheckKey <T>(); Model2Sql sqlObject = new Model2Sql(); sqlObject.SqlType = sqlType; string tableName = ReflectionHelper.GetTypeName <T>(); List <PropertyInfo> keys = ReflectionHelper.GetKeys <T>(); List <PropertyInfo> objectKeys = ReflectionHelper.GetObjectKeys(keys, model); if (!objectKeys.Any()) { throw new ArgumentException("匿名类中不包括表主键"); } switch (sqlType) { case Model2Db.Add: break; case Model2Db.Delete: sqlObject.Sql = string.Format("DELETE FROM {0} WHERE {1}", tableName, CreatWhere(keys)); PropertyAdd2Param(model, sqlObject, objectKeys); break; case Model2Db.Update: List <PropertyInfo> nonKeys = ReflectionHelper.GetPropertysExceptBy(ReflectionHelper.GetPropertys(model), keys); sqlObject.Sql = string.Format("UPDATE {0} {1} WHERE {2}", tableName, CreateUpdate(nonKeys), CreatWhere(keys)); PropertyAdd2Param(model, sqlObject, nonKeys); PropertyAdd2Param(model, sqlObject, objectKeys); break; case Model2Db.Query: sqlObject.Sql = string.Format("SELECT {0} FROM {1} WHERE {2}", CreateQuery <T>(), tableName, CreatWhere(objectKeys)); PropertyAdd2Param(model, sqlObject, objectKeys); break; default: break; } return(sqlObject); }
public Model2Sql Model2SQL <T>(IQuery query, IOrder order) { Model2Sql querySql = GetQuerySQL <T>(query); Model2Sql orderSql = GetOrderSQL <T>(order); Model2Sql model = new Model2Sql() { SqlType = Model2Db.Query }; foreach (var item in querySql.Param) { model.Param.Add(item.Key, item.Value); } model.Sql = string.Format("SELECT {0} FROM {1} WHERE {2} ORDER BY {3}", CreateQuery <T>(), ReflectionHelper.GetTypeName <T>(), querySql.Sql, orderSql.Sql); return(model); }
private Model2Sql GetQuerySQL <T>(IQuery query) { if (query == null) { throw new ArgumentException("query不能为空"); } Model2Sql sqlObject = new Model2Sql(); sqlObject.SqlType = Model2Db.Query; Dictionary <IQueryValue, QueryLink> dic = query.All(); if (dic.Count == 0) { throw new ArgumentException("query不包含查询字段"); } int cur = 1; foreach (var item in dic) { if (cur != 1) { sqlObject.Sql = sqlObject.Sql + string.Format(" {0} ", item.Value.ToString()); } if (item.Key is QueryValue) { QueryValue2Model2Sql <T>(sqlObject, item); } else if (item.Key is Query) { Query2Model2Sql <T>(sqlObject, item); } else { throw new ArgumentException("未实现指定的IQuery类型"); } cur++; } return(sqlObject); }
public Model2Sql Model2SQL <T>(IPager <T> pager, IOrder order) { if (pager == null) { throw new ArgumentException("pager不能为空"); } if (order == null) { throw new ArgumentException("order不能为空"); } CheckKey <T>(); Model2Sql model = new Model2Sql() { SqlType = Model2Db.Query }; model.Sql = string.Format("SELECT {0} FROM {1} ORDER BY {2} LIMIT {3},{4}", CreateQuery <T>(), ReflectionHelper.GetTypeName <T>(), GetOrderSQL <T>(order).Sql, (pager.CurrentPage - 1) * pager.PageNum, pager.PageNum); return(model); }
/// <summary> /// 将IQuery查询条件转化为sql语句 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="query"></param> /// <returns></returns> public Model2Sql Model2SQL <T>(Model2Db model, IQuery query) { if (model != Model2Db.Count) { throw new ArgumentException("不支持此类型"); } CheckKey <T>(); List <PropertyInfo> keys = ReflectionHelper.GetKeys <T>(); IOrder order = new Order(); foreach (var item in keys) { order.Add(item.Name, OrderDirection.Desc); } Model2Sql sqlObject = GetQuerySQL <T>(query); sqlObject.Sql = String.Format("SELECT COUNT(*) FROM {0} WHERE {1} ORDER BY {2}", ReflectionHelper.GetTypeName <T>(), sqlObject.Sql, GetOrderSQL <T>(order).Sql); return(sqlObject); }
public Model2Sql Model2SQL <T>(IPager <T> pager, IQuery query) { if (pager == null) { throw new ArgumentException("pager不能为空"); } CheckKey <T>(); List <PropertyInfo> keys = ReflectionHelper.GetKeys <T>(); IOrder order = new Order(); foreach (var item in keys) { order.Add(item.Name, OrderDirection.Desc); } Model2Sql model = GetQuerySQL <T>(query); model.Sql = string.Format("SELECT {0} FROM {1} WHERE {2} ORDER BY {3} LIMIT {4},{5}", CreateQuery <T>(), ReflectionHelper.GetTypeName <T>(), model.Sql, GetOrderSQL <T>(order).Sql, (pager.CurrentPage - 1) * pager.PageNum, pager.PageNum); return(model); }
private static Model2Sql GetOrderSQL <T>(IOrder order) { if (order == null) { throw new ArgumentException("order不能为空"); } if (order.All().Count <= 0) { throw new ArgumentException("order中没有可排序字段"); } int n = 1; string orderSql = string.Empty; foreach (var item in order.All()) { if (!ReflectionHelper.ExistField <T>(item.Key)) { throw new ArgumentException(string.Format("此字段{0}不存在类中", item.Key)); } if (n != 1) { orderSql = orderSql + " , "; } orderSql += " " + item.Key + " " + item.Value.ToString(); n++; } Model2Sql model = new Model2Sql() { Sql = orderSql, SqlType = Model2Db.Query }; return(model); }
private void Query2Model2Sql <T>(Model2Sql sqlObject, KeyValuePair <IQueryValue, QueryLink> item) { if (item.Key is Query) { sqlObject.Sql = sqlObject.Sql + " ("; Query query = item.Key as Query; Dictionary <IQueryValue, QueryLink> dic = query.All(); int cur = 1; foreach (var dicItem in dic) { if (cur != 1) { sqlObject.Sql = sqlObject.Sql + string.Format(" {0} ", item.Value.ToString()); } if (dicItem.Key is QueryValue) { QueryValue2Model2Sql <T>(sqlObject, dicItem); } else if (dicItem.Key is Query) { sqlObject.Sql = sqlObject.Sql + string.Format(" {0} ", item.Value.ToString()) + " ("; Model2SQL <T>(dicItem.Key as IQuery, sqlObject); sqlObject.Sql = sqlObject.Sql + " )"; } else { throw new ArgumentException("未实现指定的IQuery类型"); } cur++; } sqlObject.Sql = sqlObject.Sql + " )"; } else { throw new ArgumentException("此方法只用来解析Query"); } }
public Model2Sql Model2SQL <T>(Model2Db sqlType, T model) { switch (sqlType) { case Model2Db.Add: case Model2Db.Delete: case Model2Db.Update: if (model == null) { throw new ArgumentException("模型不能为null"); } break; case Model2Db.Query: case Model2Db.Count: break; default: break; } CheckKey <T>(); Model2Sql sqlObject = new Model2Sql(); sqlObject.SqlType = sqlType; List <PropertyInfo> keys = ReflectionHelper.GetKeys <T>(); List <PropertyInfo> nonKeys = ReflectionHelper.GetNonKey <T>(); string tableName = ReflectionHelper.GetTypeName <T>(); switch (sqlType) { case Model2Db.Add: sqlObject.Sql = string.Format("INSERT INTO {0} ({1}) VALUES (@{2})", tableName, string.Join(",", ReflectionHelper.GetPropertys <T>().Select(p => p.Name).ToArray()), string.Join(",@", ReflectionHelper.GetPropertys <T>().Select(p => p.Name).ToArray())); ReflectionHelper.GetPropertys <T>().ToList().ForEach((p) => { sqlObject.Param.Add("@" + p.Name, p.GetValue(model)); }); break; case Model2Db.Delete: sqlObject.Sql = string.Format("DELETE FROM {0} WHERE {1}", tableName, CreatWhere(keys)); PropertyAdd2Param <T>(model, sqlObject, keys); break; case Model2Db.Update: sqlObject.Sql = string.Format("UPDATE {0} {1} WHERE {2}", tableName, CreateUpdate(nonKeys), CreatWhere(keys)); PropertyAdd2Param <T>(model, sqlObject, nonKeys); PropertyAdd2Param <T>(model, sqlObject, keys); break; case Model2Db.Query: IOrder order = new Order(); foreach (var item in keys) { order.Add(item.Name, OrderDirection.Desc); } sqlObject.Sql = string.Format("SELECT {0} FROM {1} ORDER BY {2}", CreateQuery <T>(), tableName, GetOrderSQL <T>(order).Sql); break; case Model2Db.Count: sqlObject.Sql = string.Format("SELECT COUNT(*) FROM {0}", tableName); break; default: break; } return(sqlObject); }
public MySqlDataAdapter CreateAdapter(MySqlConnection connection, Model2Sql model) { MySqlDataAdapter adapter = new MySqlDataAdapter(CreateCommand(connection, model)); return(adapter); }
private static void PropertyAdd2Param(object model, Model2Sql sqlObject, List <PropertyInfo> keys) { keys.ToList().ForEach((p) => { sqlObject.Param.Add("@" + p.Name, p.GetValue(model)); }); }
private void QueryValue2Model2Sql <T>(Model2Sql sqlObject, KeyValuePair <IQueryValue, QueryLink> item) { if (item.Key is QueryValue) { QueryValue value = item.Key as QueryValue; if (value != null) { //检查字段是否存在于T中,如果不存在,提示异常 if (!ReflectionHelper.ExistField <T>(value.FieldName)) { throw new ArgumentException(string.Format("类{0}中不包括此字段{1}", typeof(T).FullName, value.FieldName)); } string fieldName = "@" + value.FieldName; //检查同名参数是否已添加到字典(sqlObject.Param)中 if (sqlObject.Param.ContainsKey(fieldName)) { KeyValuePair <string, object> param = sqlObject.Param.Last(p => p.Key.Contains(fieldName)); fieldName = param.Key + "1"; } switch (value.Type) { case QueryType.Equal: sqlObject.Sql = sqlObject.Sql + string.Format(" {0}={1} ", value.FieldName, fieldName); sqlObject.Param.Add(fieldName, value.FieldValue); break; case QueryType.NotEqual: sqlObject.Sql = sqlObject.Sql + string.Format(" {0}!={1} ", value.FieldName, fieldName); sqlObject.Param.Add(fieldName, value.FieldValue); break; case QueryType.GreaterThan: //日期格式要不要特殊处理 sqlObject.Sql = sqlObject.Sql + string.Format(" {0}>{1} ", value.FieldName, fieldName); sqlObject.Param.Add(fieldName, value.FieldValue); break; case QueryType.LessThan: sqlObject.Sql = sqlObject.Sql + string.Format(" {0}<{1} ", value.FieldName, fieldName); sqlObject.Param.Add(fieldName, value.FieldValue); break; case QueryType.GreaterThenEqual: sqlObject.Sql = sqlObject.Sql + string.Format(" {0}>={1} ", value.FieldName, fieldName); sqlObject.Param.Add(fieldName, value.FieldValue); break; case QueryType.LessThanEqual: sqlObject.Sql = sqlObject.Sql + string.Format(" {0}<={1} ", value.FieldName, fieldName); sqlObject.Param.Add(fieldName, value.FieldValue); break; case QueryType.In: //要不要根据数据类型做特殊处理 sqlObject.Sql = sqlObject.Sql + string.Format(" {0} in ( ", value.FieldName); if (value.FieldValue.GetType().IsArray) { string[] valueArray = value.FieldValue as string[]; foreach (var valueItem in valueArray) { if (sqlObject.Param.ContainsKey(fieldName)) { KeyValuePair <string, object> param = sqlObject.Param.Last(p => p.Key.Contains(fieldName)); if (param.Key.IndexOf(fieldName) > 0) { fieldName = fieldName + (Convert.ToInt32(param.Key.Substring(param.Key.IndexOf(fieldName))) + 1); } else { fieldName = fieldName + "1"; } } sqlObject.Sql += fieldName + ","; sqlObject.Param.Add(fieldName, valueItem); } sqlObject.Sql = sqlObject.Sql.Remove(sqlObject.Sql.Length - 1) + ") "; } else { throw new ArgumentException(string.Format(" {0}的数据格式不是数组", value.FieldName)); } break; case QueryType.NotIn: sqlObject.Sql = sqlObject.Sql + string.Format(" {0} not in ( ", value.FieldName); if (value.FieldValue.GetType().IsArray) { string[] valueArray = value.FieldValue as string[]; foreach (var valueItem in valueArray) { KeyValuePair <string, object> param = sqlObject.Param.Last(p => p.Key.Contains(fieldName)); if (param.Key.IndexOf(fieldName) > 0) { fieldName = fieldName + (Convert.ToInt32(param.Key.Substring(param.Key.IndexOf(fieldName))) + 1); } else { fieldName = fieldName + "1"; } sqlObject.Sql += fieldName + ","; sqlObject.Param.Add(fieldName, valueItem); } sqlObject.Sql = sqlObject.Sql.Remove(sqlObject.Sql.Length - 1) + ") "; } else { throw new ArgumentException(string.Format(" {0}的数据格式不是数组", value.FieldName)); } break; case QueryType.LikeLeft: sqlObject.Sql = sqlObject.Sql + string.Format(" {0} like {1} ", value.FieldName, fieldName); sqlObject.Param.Add(fieldName, "%" + value.FieldValue.ToString()); break; case QueryType.LikeRight: sqlObject.Sql = sqlObject.Sql + string.Format(" {0} like {1} ", value.FieldName, fieldName); sqlObject.Param.Add(fieldName, value.FieldValue.ToString() + "%"); break; case QueryType.LikeFull: sqlObject.Sql = sqlObject.Sql + string.Format(" {0} like {1} ", value.FieldName, fieldName); sqlObject.Param.Add(fieldName, "%" + value.FieldValue.ToString() + "%"); break; default: break; } } } else { throw new ArgumentException("此方法只用来解析QueryValue"); } }