public virtual int InsertToID(ModelBase model) { if (model.Count() == 0) { throw new Exception("无任何数据进行添加,无法执行添加操作"); } Type type = model.GetType(); StringBuilder sql1 = new StringBuilder(); StringBuilder sql2 = new StringBuilder(); MySqlManager manager = new MySqlManager(); for (int i = 0; i < model.Count(); i++) { //取出已赋值的属性的名称 string name = model.GetValue(i); sql1.Append(name + ","); sql2.Append("@" + name + ","); //根据属性名获取属性对象 PropertyInfo pinfo = type.GetProperty(name); object value = pinfo.GetValue(model, null); manager.Add("@" + name, value); } sql1.Remove(sql1.Length - 1, 1); sql2.Remove(sql2.Length - 1, 1); string sql = string.Format("insert into {0}({1}) values({2});select @@identity", type.Name + model.Extend, sql1, sql2); manager.Sql = sql; int id = int.Parse(sqlHelp.ExecuteScalar(manager).ToString()); return(id); }
public virtual bool Update(ModelBase model) { //主键验证 string keyName = ValidateKey(model); //除主键外至少还有一个属性赋值 if (model.Count() < 2) { throw new Exception("没有给主键以外的其他列赋值,无法执行修改操作"); } Type type = model.GetType(); StringBuilder sql1 = new StringBuilder(); MySqlManager manager = new MySqlManager(); for (int i = 0; i < model.Count(); i++) { //取出已赋值的属性名称 string name = model.GetValue(i); if (name != keyName)//除主键外其他的属性应该拼凑到Sql中 { sql1.Append(name + "=@" + name + ","); //根据属性名获取属性对象,再取出属性值 PropertyInfo pinfo = type.GetProperty(name); object value = pinfo.GetValue(model, null); manager.Add("@" + name, value); } } sql1.Remove(sql1.Length - 1, 1); string sql = string.Format("update {0} set {1} where {2}=@{2}", type.Name + model.Extend, sql1, keyName); PropertyInfo keyPInfo = type.GetProperty(keyName); object keyValue = keyPInfo.GetValue(model, null); manager.Add("@" + keyName, keyValue); manager.Sql = sql; int count = sqlHelp.ExecuteNonQuery(manager); return(count == 1); }
public virtual bool Delete(ModelBase model) { //验证是否有主键(实体类上是否表示了主键特征) Type type = model.GetType(); string keyName = ValidateKey(model); string sql = string.Format("delete {0} where {1}=@{1}", type.Name + model.Extend, keyName); MySqlManager manager = new MySqlManager(sql); PropertyInfo pinfo = type.GetProperty(keyName); object value = pinfo.GetValue(model, null); manager.Add("@" + keyName, value); int count = sqlHelp.ExecuteNonQuery(manager); return(count == 1); }
public virtual void SelectForID(ModelBase model) { string keyName = ValidateKey(model); Type type = model.GetType(); string sql = string.Format("select * from {0} where {1}=@{1}", type.Name + model.Extend, keyName); MySqlManager manager = new MySqlManager(sql); PropertyInfo pinfo = type.GetProperty(keyName); object keyValue = pinfo.GetValue(model, null); manager.Add("@" + keyName, keyValue); using (MySqlDataReader read = sqlHelp.ExecuteReader(manager)) { if (read.Read()) { //取出实体类中所有的属性名 PropertyInfo[] pinfos = type.GetProperties(); //根据属性名取出查询结果中的值 foreach (PropertyInfo item in pinfos) { string name = item.Name; //验证该属性名在查询结果中 //read.fieldCount表示查询结果中的列数 for (int i = 0; i < read.FieldCount; i++) { //根据列的索引取出列的名称 string colName = read.GetName(i); if (name.ToLower() == colName.ToLower())//属性名与查询结果的列名匹配 { object value = read[name]; if (value != DBNull.Value) { item.SetValue(model, value, null); } break; } } } } else { throw new Exception("无法根据ID查询数据"); } } }
/// <summary> /// 通用分页(适用于MySql数据库) /// </summary> /// <typeparam name="T"></typeparam> /// <returns></returns> public List <T> GetPageDataForMySql <T>() where T : ModelBase { string sql = "ProcPage"; MySqlManager manager = new MySqlManager(sql, System.Data.CommandType.StoredProcedure); manager.CommandTimeout = this.TimeOut; manager.Add("tableName", this.TableName); manager.Add("showField", this.ShowField); manager.Add("whereText", this.WhereText.Replace("-", "")); manager.Add("orderText", this.OrderText); manager.Add("pageSize", this.PageSize); manager.Add("pageIndex", this.PageIndex); manager.AddOutput("dataCount", MySqlDbType.Int32); MySqlDALBase dal = new MySqlDALBase(); dal.sqlHelp.ConnectionString = this.ConnectionString; List <T> list = dal.Select <T>(manager); this.DataCount = int.Parse(manager[manager.Count - 1].Value.ToString()); this.PageCount = (DataCount - 1) / PageSize + 1; return(list); #region MySql分页存储过程 /* * CREATE PROCEDURE ProcPage( * in tableName varchar(20),#表名 * in showField varchar(100),#要显示的列名 * in whereText varchar(500),#where条件(只需要写where后面的语句) * in orderText varchar(500),#排序条件(只需要写order by后面的语句) * in pageSize int,#每一页显示的记录数 * in pageIndex int,#当前页 * out dataCount int#总记录数 * ) * BEGIN * * if (pageSize<1)then * set pageSize=20; * end if; * * if (pageIndex < 1)then * set pageIndex = 1; * end if; * * if(LENGTH(whereText)>0)then * set whereText=CONCAT(' where 1=1 ',whereText); * end if; * * if(LENGTH(orderText)>0)then * set orderText = CONCAT(' ORDER BY ',orderText); * end if; * * set @strsql = CONCAT('select ',showField,' from ',tableName,' ',whereText,' ',orderText,' limit ',pageIndex*pageSize-pageSize,',',pageSize); * * prepare stmtsql from @strsql; * execute stmtsql; * deallocate prepare stmtsql; * * set @strsqlcount=concat('select count(1) as count into @datacount from ',tableName,'',whereText); * prepare stmtsqlcount from @strsqlcount; * execute stmtsqlcount; * deallocate prepare stmtsqlcount; * set datacount=@datacount; * END;*/ #endregion }