最近在学ASP.NET MVC的时候,有的时候,要对进行增、删、改、查操作
一开始感觉用自己带的那个 ADO.NET Entity Data Model 来进行增、删,改、查挺方便的
但后来发一个问题,在MYSQL里不能同时在*.edmx打开多个Read操作,SQL里可以设置一个属性,不记得就可以了。
所以就不用它了,由于都是对单表进行操作,没有复杂的操作。
就写一个简单扩展方法
首先有一点就是,自己写的那个类的名称,必须和数据库里表的名称一至
要不能在进行增、删、改、查的时候,就会出问题了。
举个例子吧
//-->查询操作
var sp = new SortedParam[] { new SortedParam { FieldName = "id", Sorted = SortedType.Desc } };
user_info ui = new user_info();
var video = ui.ToList(sp, 30); //-->获取用户信息,按 id 降序取出表中前三十条记录。
//-->插入操作
1 string errorMessage = string.Empty;2 var ui = newuser_info();3 ui.Username = 'aaaa';4 ui.Password = 'bbbb';5 ui.Type = 1
6 var result=ui.Insert(null, referrorMessage);7 if(0
10 }11 else
12 {13 //...
14 }
//-->删除、修改基本上都差不多。
个人水平有限,如果更好的见记,还望各告诉。
具体的实例还是看看扩展方法。
扩展中使用到的两个类
1、排序用
///
/// 排序类型
///
public enum SortedType
{
///
/// 升序
///
Asc,
///
/// 降序
///
Desc
}
///
/// 排序参数
///
public class SortedParam
{
///
/// 字段名称
///
public string FieldName { get; set; }
///
/// 排序类型
///
public SortedType Sorted { get; set; }
}
1、条件用
///
/// 字段参数
///
public class FieldParam
{
///
/// 字段名称
///
public string FieldName { get; set; }
///
/// 字段值
///
public object FieldValue { get; set; }
///
/// 字段类型
///
public Type FieldType { get; set; }
}
///
/// 操作数据库单扩展
///
public static class AccessDBExtend
{
///
/// 获取 T 表中记录集合
///
/// 表的Model类
/// 类的变量
/// 返回IList集合
public static IList ToList(this T source) where T : new()
{
string table = typeof(T).Name;
string sqlSentence = string.Format("select * from {0} ", table);
return DataTableToList(sqlSentence);
}
///
/// 获取 T 表中记录集合
///
/// 表的Model类
/// 类的变量
/// 每页的大小
/// 当前页面索引
/// 当前表中记录总数
/// 返回IList集合
public static IList ToList(this T source, int pageSize, int pageIndex, out int total) where T : new()
{
return ToList(source, null, null, pageSize, pageIndex, out total);
}
///
/// 获取表的总数
///
/// 表的名称
/// 条件
/// 返回记录数
static int GetTotal(string tableName, FieldParam[] fields)
{
string whereCondition = GetCondition(fields);
string sqlSentece = string.Format(" select count(*) from {0} {1} ", tableName, whereCondition);
return string.Format("{0}", DBAccess.ExecuteScalar(sqlSentece)).ConvertTo();
}
///
/// 获取 T 表中记录集合
///
/// 表的Model类
/// 类的变量
/// 条件字段
/// 当前表中记录总数
/// 返回IList集合
public static IList ToList(this T source, FieldParam[] fields, out int total) where T : new()
{
return ToList(source, fields, null, out total);
}
///
/// 获取 T 表中记录集合
///
/// 表的Model类
/// 类的变量
/// 条件字段
/// 排序
/// 当前表中记录总数
/// 返回IList集合
public static IList ToList(this T source, FieldParam[] fields, SortedParam[] sorted, out int total) where T : new()
{
string table = typeof(T).Name;
string sqlSentence = string.Format("select * from {0} {1} {2}", table, GetCondition(fields), GetOrderBy(sorted));
total = GetTotal(table, fields);
return DataTableToList(sqlSentence);
}
///
/// 获取 T 表中记录集合
///
/// 表的Model类
/// 类的变量
/// 条件字段
/// 每页的大小
/// 当前页面索引
/// 当前表中记录总数
/// 返回IList集合
public static IList ToList(this T source, FieldParam[] fields, int pageSize, int pageIndex, out int total) where T : new()
{
return ToList(source, fields, null, pageSize, pageIndex, out total);
}
///
/// 获取 T 表中记录集合
///
/// 表的Model类
/// 类的变量
/// 条件字段
/// 排序
/// 每页的大小
/// 当前页面索引
/// 当前表中记录总数
/// 返回IList集合
public static IList ToList(this T source, FieldParam[] fields, SortedParam[] sorted, int pageSize, int pageIndex, out int total) where T : new()
{
string table = typeof(T).Name;
string sqlSentence = GetSqlSentence(table, fields, sorted, pageSize, pageIndex);
total = GetTotal(table, fields);
List item = new List();
item.Add(string.Format("set @count = 0"));
item.Add(string.Format("set @count = 0"));
DBAccess.ExecuteNonQuery(item);
return DataTableToList(sqlSentence);
}
///
/// 获取 T 表中记录集合
///
/// 表的Model类
/// 类的变量
/// 排序
/// 每页的大小
/// 当前页面索引
/// 当前表中记录总数
/// 返回IList集合
public static IList ToList(this T source, SortedParam[] sorted, int pageSize, int pageIndex, out int total) where T : new()
{
return ToList(source, null, sorted, pageSize, pageIndex, out total);
}
///
/// 获取 T 表中记录集合
///
/// 表的Model类
/// 类的变量
/// 排序
/// 获取表的前几条记录
/// 返回IList集合
public static IList ToList(this T source, SortedParam[] sorted, int topSize) where T : new()
{
string table = typeof(T).Name;
string orderBy = GetOrderBy(sorted);
string sqlSentence = string.Format("SELECT * from {0} {1} ", table, orderBy);
if (0 < topSize)
{
sqlSentence += string.Format(" LIMIT 0,{0} ", topSize);
}
return DataTableToList(sqlSentence);
}
///
/// 获取 T 表中记录集合
///
/// 表的Model类
/// 类的变量
/// 排序
/// 返回IList集合
public static IList ToList(this T source, SortedParam[] sorted) where T : new()
{
return ToList(source,sorted,0);
}
///
/// 将DataTable转成 IList
///
///
///
///
static IList DataTableToList(string sqlSentence) where T : new()
{
var dt = Database.DBAccess.GetDataTable(sqlSentence);
if (null == dt)
{
return null;
}
return dt.ToList();
}
static string GetSqlSentence(string tableName, FieldParam[] fields, SortedParam[] sorted, int pageSize, int pageIndex)
{
string _orderBy = GetOrderBy(sorted);
string sql = string.Format("select * from (");
sql += string.Format("SELECT (@count := @count +1) SerialNumber, t.* from {0} t {1} {2}", tableName, GetCondition(fields), _orderBy);
if (0 < pageSize)
{
sql += string.Format(" LIMIT 0,{0} ", pageSize * (pageIndex));
}
sql += string.Format(") v {0}", _orderBy);
if (0 < pageSize)
{
sql += string.Format(" LIMIT {0},{1}", pageSize * (pageIndex - 1), pageSize * (pageIndex));
}
return sql;
}
static string GetCondition(FieldParam[] fields)
{
if (null == fields || 0 == fields.Length)
{
return null;
}
string whereCondtion =
string.Format(" where {0}",
string.Join(" and ",
from p in fields
select string.Format("{0} = '{1}'", p.FieldName, p.FieldValue)));
return whereCondtion;
}
///
///
///
///
///
static string GetOrderBy(SortedParam[] fields)
{
if (null == fields || 0 == fields.Length)
{
return null;
}
string whereCondtion =
string.Format(" order by {0}",
string.Join(" and ",
from p in fields
select string.Format("{0} {1}", p.FieldName, p.Sorted)));
return whereCondtion;
}
///
/// 获取单条记录
///
/// 表的Model类
/// 类的变量
/// 条件字段
/// 返回model类
public static T ToModel(this T source, FieldParam[] fields) where T : new()
{
string table = typeof(T).Name;
string sqlSentence = string.Format("select * from {0} {1}", table, GetCondition(fields));
var dt = Database.DBAccess.GetDataTable(sqlSentence);
return dt.FirstModel();
}
///
/// 删除记录
///
/// 表的Model类
/// 类的变量
/// 条件字段
/// 是否成功 , >0成功;否则失败。
public static int Delete(this T Source, FieldParam[] fields, ref string errorMessage)
{
try
{
string tableName = typeof(T).Name;
string sqlSentence = string.Format("delete from {0} {1} ", tableName, GetCondition(fields));
var result = DBAccess.ExecuteNonQuery(sqlSentence);
errorMessage = DBAccess.ErrorMessage;
return result;
}
catch (Exception ee)
{
errorMessage = ee.Message;
return -1;
}
}
///
/// 向表中插入记录
///
///
///
/// 不用进行插入的字符集合
///
///
public static int Insert(this T source, FieldParam[] fields, ref string errorMessage)
{
try
{
var tt = typeof(T);
string sqlSentence = GetInsertSentence(source, fields);
var result = DBAccess.ExecuteNonQuery(sqlSentence);
errorMessage = DBAccess.ErrorMessage;
return result;
}
catch (Exception ee)
{
errorMessage = ee.Message;
return -1;
}
}
///
/// 编辑,先删除,然后再进行插入操作,在一个事务里处理的【 0 成功;其它失败 】
///
///
///
///
///
public static int Edit(this T Source, FieldParam[] fields, ref string errorMessage)
{
try
{
string tableName = typeof(T).Name;
List item = new List();
string sqlSentence = string.Format("delete from {0} {1} ", tableName, GetCondition(fields));
item.Add(sqlSentence);
sqlSentence = GetInsertSentence(Source, null);
item.Add(sqlSentence);
var result = DBAccess.ExecuteNonQuery(item);
errorMessage = DBAccess.ErrorMessage;
return result;
}
catch (Exception ee)
{
errorMessage = ee.Message;
return -1;
}
}
///
/// 获取插入的SQL语句
///
///
///
/// 不用插入的字符
///
public static string GetInsertSentence(this T source, FieldParam[] fields)
{
var tt = typeof(T);
List fieldNames = new List();
List fieldValues = new List();
var piItem = tt.GetProperties();
foreach (var v in piItem)
{
if (v.Name.ToLower().Equals("SerialNumber".ToLower()))
{
continue;
}
//-->判断是否存在fields
if (null != fields && 0 < fields.Length)
{
var fp = fields.Where(p => p.FieldName.ToLower().Equals(v.Name.ToLower())).GetFirst();
if (null != fp)
{
continue;
}
}
fieldNames.Add(v.Name);
fieldValues.Add(GetPropertyValue(v, source));
}
string sql = string.Format("insert into {0} ({1}) values ({2}) ",
tt.Name,
string.Join(",", fieldNames),
string.Join(",", fieldValues));
return sql;
}
///
/// 获取属性的值
///
///
///
///
static string GetPropertyValue(PropertyInfo pi, object source)
{
object value = pi.GetValue(source, null);
string result = string.Format(" '{0}' ", value);
switch (pi.PropertyType.FullName)
{
case "int":
case "int16":
case "int32":
case "int64":
case "System.UInt16":
case "System.UInt32":
case "System.UInt64":
case "System.Int16":
case "System.Int32":
case "System.Int64":
case "System.Single":
case "System.Double":
case "System.decimal":
result = string.Format(" {0} ", value);
break;
case "System.String":
value = Wrapper.StringReplace(string.Format("{0}", value));
result = string.Format(" '{0}' ", value);
break;
case "System.DateTime":
var dt = Convert.ToDateTime(value);
result = string.Format(" STR_TO_DATE('{0}','%Y-%m-%d %H:%i:%s') ", dt.ToString("yyyy-MM-dd HH:mm:ss"));
break;
}
return result;
}
}