Beispiel #1
0
        /// <summary>
        /// 数据填充方式查询数据
        /// </summary>
        /// <param name="manager">查询参数</param>
        /// <returns>返回DataTable数据集</returns>
        public DataTable ExecuteFill(MySqlManager manager)
        {
            MySqlConnection con = new MySqlConnection(ConnectionString);

            try
            {
                MySqlCommand com = new MySqlCommand(manager.Sql, con);
                if (manager.CommandTimeout != -1)
                {
                    com.CommandTimeout = manager.CommandTimeout;
                }
                com.CommandType = manager.Type;
                foreach (MySqlParameter item in manager)
                {
                    com.Parameters.Add(item);
                }
                MySqlDataAdapter da = new MySqlDataAdapter();
                da.SelectCommand = com;
                DataTable table = new DataTable();
                da.Fill(table);
                return(table);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con.State == ConnectionState.Open || con.State == ConnectionState.Broken)
                {
                    con.Close();
                }
            }
        }
Beispiel #2
0
        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);
        }
Beispiel #3
0
 public object ExecuteScalar(MySqlManager manager)
 {
     using (MySqlConnection con = new MySqlConnection(ConnectionString))
     {
         try
         {
             MySqlCommand com = new MySqlCommand(manager.Sql, con);
             com.CommandType = manager.Type;
             if (manager.CommandTimeout != -1)
             {
                 com.CommandTimeout = manager.CommandTimeout;
             }
             foreach (MySqlParameter item in manager)
             {
                 com.Parameters.Add(item);
             }
             con.Open();
             return(com.ExecuteScalar());
         }
         catch (Exception ex)
         {
             throw ex;
         }
         finally
         {
             if (con.State == ConnectionState.Open || con.State == ConnectionState.Broken)
             {
                 con.Close();
             }
         }
     }
 }
Beispiel #4
0
        /// <summary>
        /// 查询数据返回实体集合
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="tableExtend">数据表扩展名</param>
        /// <returns></returns>
        public virtual List <T> SelectAll <T>(string tableExtend) where T : ModelBase
        {
            Type         type    = typeof(T);
            string       sql     = string.Format("select * from {0}", type.Name + tableExtend);
            MySqlManager manager = new MySqlManager(sql);

            return(Select <T>(manager));
        }
Beispiel #5
0
        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);
        }
Beispiel #6
0
        /// <summary>
        /// SqlDataReader(类似游标) 查询数据
        /// </summary>
        /// <param name="manager">查询参数</param>
        /// <returns></returns>
        public MySqlDataReader ExecuteReader(MySqlManager manager)
        {
            MySqlConnection con = new MySqlConnection(ConnectionString);
            MySqlCommand    com = new MySqlCommand(manager.Sql, con);

            com.CommandType = manager.Type;
            if (manager.CommandTimeout != -1)
            {
                com.CommandTimeout = manager.CommandTimeout;
            }
            foreach (MySqlParameter item in manager)
            {
                com.Parameters.Add(item);
            }
            con.Open();
            return(com.ExecuteReader(CommandBehavior.CloseConnection));
        }
Beispiel #7
0
        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查询数据");
                }
            }
        }
Beispiel #8
0
        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);
        }
Beispiel #9
0
        protected internal virtual List <T> Select <T>(MySqlManager manager) where T : ModelBase
        {
            Type type = typeof(T);

            using (MySqlDataReader read = sqlHelp.ExecuteReader(manager))
            {
                List <T> list = new List <T>();
                while (read.Read())
                {
                    //根据类型来创建类型的对象
                    T t = Activator.CreateInstance(type) as T;
                    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(t, value, null);
                                }
                                break;
                            }
                        }
                    }

                    list.Add(t);
                }
                return(list);
            }
        }
Beispiel #10
0
        /// <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
        }