毕设之数据库操作

      为了简化操作，使得所有的增删改查都统一处理，不用区别对待那个表。通俗来讲也就是不用对哪个表进行插入操作时都需要进行一系列的Insert的了。为此设计了几个方法进行控制。这样一来可以更高的提高开发的效率，但是也损失了系统的性能。

一、Insert

设计思路：通过反射来获取实体类的字段类型和字段，再根据实际情况进行处理。

方法名：public int Insert(object obj){}其中的参数obj是指需要插入的实体类。

  #region Insert
        public int Insert(object obj)
        {
            int count = -2;
            string filedStr = string.Empty;
            string ConditionStr = string.Empty;
            Type type = obj.GetType();
            PropertyInfo[] infos = type.GetProperties();
            if (infos.Length > 0)
            {
                foreach (PropertyInfo info in infos)
                {

                    if (info.PropertyType.Name.ToLower() == "datetime")
                    {
                        filedStr += info.Name + ",";
                        DateTime dt = (DateTime)info.GetValue(obj, null);
                        string time = dt.ToString("yyyy-MM-dd HH:mm:ss");
                        if (time.Contains("0001-01-01"))
                        {
                            ConditionStr += "'" + DBNull.Value + "'" + ",";
                        }
                        else
                        {
                            ConditionStr += "'" + time + "'" + ",";
                        }

                    }
                    if (info.PropertyType.Name.ToLower() == "string")
                    {
                        filedStr += info.Name + ",";
                        ConditionStr += "'" + Convert.ToString(info.GetValue(obj, null)) + "'" + ",";
                    }
                    if (info.PropertyType.Name.ToLower() == "boolean")
                    {
                        filedStr += info.Name + ",";
                        char flag = '0';
                        if (Convert.ToBoolean(info.GetValue(obj, null)) == true)
                        {
                            flag = '1';
                        }
                        else
                        {
                            flag = '0';
                        }

                        ConditionStr += flag + ",";
                    }
                    if (info.PropertyType.Name.ToLower() == "int32")
                    {
                        filedStr += info.Name + ",";
                        ConditionStr += Convert.ToInt32(info.GetValue(obj, null)) + ",";
                    }
                    if (info.PropertyType.Name.ToLower() == "single")
                    {
                        filedStr += info.Name + ",";
                        ConditionStr += Convert.ToDouble(info.GetValue(obj, null)) + ",";
                    }
                    if (info.PropertyType.Name.ToLower() == "decimal")
                    {
                        filedStr += info.Name + ",";
                        ConditionStr += Convert.ToDecimal(info.GetValue(obj, null)) + ",";
                    }
                }
            }
            string fieldString = filedStr.Remove(filedStr.Length - 1);
            string conditionString = ConditionStr.Remove(ConditionStr.Length - 1);
            string sql = string.Format("insert into {0}({1}) values({2})", type.Name, fieldString, conditionString);
            count=this.SaveOrUpdate(sql);//封装好对数据库执行操作
            return count;

        }
        #endregion

View Code

二、Edit

描述：对字段进行修改。

设计思路：传入修改之前和修改之后的俩个实体类，对这俩个实体类行进比较，对于俩者之间的不同。记录下来，然后进行数据库的Update操作。

方法名： public int Edit(object obj,object newObj,string DJID,string DJIDName)

参数：obj：修改之前的实体类。

         newObj：修改之后的实体类。

         DJIDName：主键ID, 用来匹配Where条件。

         DJID：主键ID值，因为ID的唯一性

 #region Edit
        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="obj">之前的数据</param>
        /// <param name="newObj">修改之后的数据</param>
        /// <param name="DJID">主键</param>
        /// <returns>结果</returns>
        public int Edit(object obj,object newObj,string DJID,string DJIDName)
        {
            
            Type objType = obj.GetType();
            string tableName = objType.Name;
            
            Type newObjType = newObj.GetType();
            PropertyInfo[] objInfos = objType.GetProperties();
            PropertyInfo[] newObjInfos = newObjType.GetProperties();
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("update {0} set", tableName);
            foreach (PropertyInfo objInfo in objInfos)
            {
                foreach (PropertyInfo newObjInfo in newObjInfos)
                {
                    if (objInfo.Name == newObjInfo.Name)
                    {
                        object value = objInfo.GetValue(obj, null);
                        object newValue = newObjInfo.GetValue(newObj, null);
                        if (!value.Equals(newValue)&& newValue != null)
                        {
                            if (objInfo.PropertyType.Name.ToLower() == "datetime")
                            {

                                DateTime dt = (DateTime)newValue;
                                string time = dt.ToString("yyyy-MM-dd HH:mm:ss");
                                if (time.Contains("0001-01-01"))
                                {
                                    sb.AppendFormat(" {0}='{1}',", newObjInfo.Name, DBNull.Value);
                                }
                                else
                                {
                                    sb.AppendFormat(" {0}='{1}',", newObjInfo.Name, time);
                                }

                            }

                            if (objInfo.PropertyType.Name.ToLower() == "string")
                            {
                                sb.AppendFormat(" {0}='{1}',", newObjInfo.Name, Convert.ToString(newValue));
                            }
                            if (objInfo.PropertyType.Name.ToLower() == "boolean")
                            {
                                ;
                                char flag = '0';
                                if (Convert.ToBoolean(newValue) == true)
                                {
                                    flag = '1';
                                }
                                else
                                {
                                    flag = '0';
                                }

                                sb.AppendFormat(" {0}={1},", newObjInfo.Name, flag);
                            }
                            if (objInfo.PropertyType.Name.ToLower() == "int32")
                            {
                                sb.AppendFormat(" {0}={1},", newObjInfo.Name, Convert.ToInt32(newValue));
                            }
                            if (objInfo.PropertyType.Name.ToLower() == "single")
                            {
                                sb.AppendFormat(" {0}={1},", newObjInfo.Name, Convert.ToDouble(newValue));
                            }
                            if (objInfo.PropertyType.Name.ToLower() == "decimal")
                            {
                                sb.AppendFormat(" {0}={1},", newObjInfo.Name, Convert.ToDecimal(newValue));
                            }
                            
                        }
                        break; 
                    }
                    
                }
            }
            string sqlHA = sb.ToString();
            string SQL=sqlHA.Remove(sqlHA.Length - 1);
            SQL += " where " + DJIDName + "=" + "'" + DJID + "'";
            int count=SaveOrUpdate(SQL);
            return count;
        }
        #endregion

View Code

三、Select

描述：查询数据库中表的所有信息，并将表中的记录保存到实体类中，实体类放到ArrayList中。

设计思路：使用泛型和反射。通过利用泛型来获取实体类的类型，利用反射来获取实体类字段，并进行赋值。

方法名:   public ArrayList Select<T>(string tableName, string sql = "") where T : new().。声明了一个泛型方法。

参数：tableName：数据库中想要查询的数据表。

         sql=“”；查询的SQL

#region Select
        public ArrayList Select<T>(string tableName, string sql = "") where T : new()
        {
            ArrayList list = new ArrayList();
            DataTable dt = baseFormDal.Select(tableName, sql);
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    T t = new T();

                    Type type = t.GetType();
                    PropertyInfo[] infos = type.GetProperties();
                    if (infos.Length > 0)
                    {
                        foreach (PropertyInfo info in infos)
                        {
                            foreach (DataColumn dc in dt.Columns)
                            {
                                if (info.Name == dc.ColumnName)
                                {
                                    if (info.PropertyType.Name.ToLower() == "string")
                                    {
                                        info.SetValue(t, dr[dc.ColumnName].ToString(), null);
                                    }
                                    else if (info.PropertyType.Name.ToLower() == "datetime")
                                    {
                                        if (string.IsNullOrWhiteSpace(dr[dc.ColumnName].ToString()))
                                        {
                                            //info.SetValue(t, new DateTime(), null);
                                        }
                                        else
                                        {
                                            DateTime date = DateTime.Parse(dr[dc.ColumnName].ToString());
                                            info.SetValue(t, date, null);
                                        }
                                    }
                                    else if (info.PropertyType.Name.ToLower() == "boolean")
                                    {
                                        if (dr[dc.ColumnName].ToString() == "1")
                                        {
                                            info.SetValue(t, true, null);
                                        }
                                        else
                                        {
                                            info.SetValue(t, false, null);
                                        }
                                    }
                                    else if (info.PropertyType.Name.ToLower() == "double" ||
                                        info.PropertyType.Name.ToLower() == "float")
                                    {
                                        info.SetValue(t, Convert.ToDouble(dr[dc.ColumnName]), null);
                                    }
                                    else if (info.PropertyType.Name.ToLower() == "decimal")
                                    {
                                        info.SetValue(t, Convert.ToDecimal(dr[dc.ColumnName]), null);
                                    }
                                }
                            }
                        }
                    }
                    list.Add(t);

                }
            }
            return list;

        }

View Code

 

转载于:https://www.cnblogs.com/ningfd/p/4585524.html