Ejemplo n.º 1
0
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(Model.manager model)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder str1   = new StringBuilder();

            //利用反射获得属性的所有公共属性
            PropertyInfo[]      pros  = model.GetType().GetProperties();
            List <SqlParameter> paras = new List <SqlParameter>();

            strSql.Append("update " + databaseprefix + "manager set ");
            foreach (PropertyInfo pi in pros)
            {
                //如果不是主键则追加sql字符串
                if (!pi.Name.Equals("id"))
                {
                    //判断属性值是否为空
                    if (pi.GetValue(model, null) != null)
                    {
                        str1.Append(pi.Name + "=@" + pi.Name + ",");                          //声明参数
                        paras.Add(new SqlParameter("@" + pi.Name, pi.GetValue(model, null))); //对参数赋值
                    }
                }
            }
            strSql.Append(str1.ToString().Trim(','));
            strSql.Append(" where id=@id ");
            paras.Add(new SqlParameter("@id", model.id));
            return(DbHelperSQL.ExecuteSql(strSql.ToString(), paras.ToArray()) > 0);
        }
Ejemplo n.º 2
0
        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Model.manager GetModel(int id)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder str1   = new StringBuilder();

            Model.manager model = new Model.manager();
            //利用反射获得属性的所有公共属性
            PropertyInfo[] pros = model.GetType().GetProperties();
            foreach (PropertyInfo p in pros)
            {
                str1.Append(p.Name + ",");//拼接字段
            }
            strSql.Append("select top 1 " + str1.ToString().Trim(','));
            strSql.Append(" from " + databaseprefix + "manager");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters =
            {
                new SqlParameter("@id", SqlDbType.Int, 4)
            };
            parameters[0].Value = id;
            DataTable dt = DbHelperSQL.Query(strSql.ToString(), parameters).Tables[0];

            if (dt.Rows.Count > 0)
            {
                return(DataRowToModel(dt.Rows[0]));
            }
            else
            {
                return(null);
            }
        }
Ejemplo n.º 3
0
        /// <summary>
        /// 根据username得到一个对象实体
        /// </summary>
        public Model.manager GetModel(string user_name)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder str1   = new StringBuilder();

            Model.manager model = new Model.manager();
            //利用反射获得属性的所有公共属性
            PropertyInfo[] pros = model.GetType().GetProperties();
            foreach (PropertyInfo p in pros)
            {
                str1.Append(p.Name + ",");//拼接字段
            }
            strSql.Append("select top 1 " + str1.ToString().Trim(','));
            strSql.Append(" from vue_manager");
            strSql.Append(" where user_name=@user_name");
            SqlParameter[] parameters =
            {
                new SqlParameter("@user_name", SqlDbType.NVarChar, 50)
            };
            parameters[0].Value = user_name;
            DataTable dt = DbHelperSQL.Query(strSql.ToString(), parameters).Tables[0];

            if (dt.Rows.Count > 0)
            {
                return(DataRowToModel(dt.Rows[0]));
            }
            else
            {
                return(null);
            }
        }
Ejemplo n.º 4
0
 /// <summary>
 /// 将对象转换实体
 /// </summary>
 public Model.manager DataRowToModel(DataRow row)
 {
     Model.manager model = new Model.manager();
     if (row != null)
     {
         //利用反射获得属性的所有公共属性
         Type modelType = model.GetType();
         for (int i = 0; i < row.Table.Columns.Count; i++)
         {
             //查找实体是否存在列表相同的公共属性
             PropertyInfo proInfo = modelType.GetProperty(row.Table.Columns[i].ColumnName);
             if (proInfo != null && row[i] != DBNull.Value)
             {
                 proInfo.SetValue(model, row[i], null);//用索引值设置属性值
             }
         }
     }
     return(model);
 }
Ejemplo n.º 5
0
        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(Model.manager model)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder str1   = new StringBuilder(); //数据字段
            StringBuilder str2   = new StringBuilder(); //数据参数

            //利用反射获得属性的所有公共属性
            PropertyInfo[]      pros  = model.GetType().GetProperties();
            List <SqlParameter> paras = new List <SqlParameter>();

            strSql.Append("insert into " + databaseprefix + "manager(");
            foreach (PropertyInfo pi in pros)
            {
                //如果不是主键则追加sql字符串
                if (!pi.Name.Equals("id"))
                {
                    //判断属性值是否为空
                    if (pi.GetValue(model, null) != null)
                    {
                        str1.Append(pi.Name + ",");                                           //拼接字段
                        str2.Append("@" + pi.Name + ",");                                     //声明参数
                        paras.Add(new SqlParameter("@" + pi.Name, pi.GetValue(model, null))); //对参数赋值
                    }
                }
            }
            strSql.Append(str1.ToString().Trim(','));
            strSql.Append(") values (");
            strSql.Append(str2.ToString().Trim(','));
            strSql.Append(") ");
            strSql.Append(";select @@IDENTITY;");
            object obj = DbHelperSQL.GetSingle(strSql.ToString(), paras.ToArray());

            if (obj == null)
            {
                return(0);
            }
            else
            {
                return(Convert.ToInt32(obj));
            }
        }
Ejemplo n.º 6
0
        /// <summary>
        /// 将对象转换实体
        /// </summary>
        public Model.manager DataRowToModel(DataRow row)
        {
            Model.manager model = new Model.manager();
            if (row != null)
            {
                //利用反射获得属性的所有公共属性
                Type modelType = model.GetType();
                for (int i = 0; i < row.Table.Columns.Count; i++)
                {
                    //查找实体是否存在列表相同的公共属性
                    PropertyInfo proInfo = modelType.GetProperty(row.Table.Columns[i].ColumnName);
                    if (proInfo != null && row[i] != DBNull.Value)
                    {
                        proInfo.SetValue(model, row[i], null);//用索引值设置属性值
                    }
                }

                #region 员工权限======================
                //StringBuilder strSql2 = new StringBuilder();
                //strSql2.Append("select * from MS_userRolePemission");
                //strSql2.Append(" where urp_username=@name");
                //SqlParameter[] parameters2 = {
                //        new SqlParameter("@name", SqlDbType.VarChar,20)};
                //parameters2[0].Value = model.user_name;

                //DataTable dt2 = DbHelperSQL.Query(strSql2.ToString(), parameters2).Tables[0];
                //if (dt2.Rows.Count > 0)
                //{
                //    int rowsCount = dt2.Rows.Count;
                //    List<Model.userRolePemission> models = new List<Model.userRolePemission>();
                //    Model.userRolePemission modelt;
                //    for (int n = 0; n < rowsCount; n++)
                //    {
                //        modelt = new Model.userRolePemission();
                //        Type modeltType = modelt.GetType();
                //        for (int i = 0; i < dt2.Rows[n].Table.Columns.Count; i++)
                //        {
                //            PropertyInfo proInfo = modeltType.GetProperty(dt2.Rows[n].Table.Columns[i].ColumnName);
                //            if (proInfo != null && dt2.Rows[n][i] != DBNull.Value)
                //            {
                //                proInfo.SetValue(modelt, dt2.Rows[n][i], null);
                //            }
                //        }
                //        models.Add(modelt);
                //    }
                //    model.UserPemissionList = models;
                //}
                #endregion

                #region 员工的角色权限======================
                StringBuilder strSql3 = new StringBuilder();
                strSql3.Append("select * from MS_userRolePemission");
                strSql3.Append(" where urp_roleid=@name");
                SqlParameter[] parameters3 =
                {
                    new SqlParameter("@name", SqlDbType.VarChar, 20)
                };
                parameters3[0].Value = model.role_id;

                DataTable dt3 = DbHelperSQL.Query(strSql3.ToString(), parameters3).Tables[0];
                if (dt3.Rows.Count > 0)
                {
                    int rowsCount = dt3.Rows.Count;
                    List <Model.userRolePemission> models = new List <Model.userRolePemission>();
                    Model.userRolePemission        modelt;
                    for (int n = 0; n < rowsCount; n++)
                    {
                        modelt = new Model.userRolePemission();
                        Type modeltType = modelt.GetType();
                        for (int i = 0; i < dt3.Rows[n].Table.Columns.Count; i++)
                        {
                            PropertyInfo proInfo = modeltType.GetProperty(dt3.Rows[n].Table.Columns[i].ColumnName);
                            if (proInfo != null && dt3.Rows[n][i] != DBNull.Value)
                            {
                                proInfo.SetValue(modelt, dt3.Rows[n][i], null);
                            }
                        }
                        models.Add(modelt);
                    }
                    model.RolePemissionList = models;
                }
                #endregion
            }
            return(model);
        }
Ejemplo n.º 7
0
        /// <summary>
        /// 增加一条数据
        /// </summary>
        public string Add(Model.manager model, out int mid)
        {
            mid = 0;
            string result = string.Empty;

            using (SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString))
            {
                conn.Open();//打开数据连接
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        #region 主表信息==========================
                        StringBuilder strSql = new StringBuilder();
                        StringBuilder str1   = new StringBuilder(); //数据字段
                        StringBuilder str2   = new StringBuilder(); //数据参数
                                                                    //利用反射获得属性的所有公共属性
                        PropertyInfo[]      pros  = model.GetType().GetProperties();
                        List <SqlParameter> paras = new List <SqlParameter>();
                        strSql.Append("insert into " + databaseprefix + "manager(");
                        foreach (PropertyInfo pi in pros)
                        {
                            //拼接字段,忽略List<T>
                            if (!typeof(System.Collections.IList).IsAssignableFrom(pi.PropertyType))
                            {
                                //如果不是主键则追加sql字符串
                                if (!pi.Name.Equals("id"))
                                {
                                    //判断属性值是否为空
                                    if (pi.GetValue(model, null) != null && !pi.GetValue(model, null).ToString().Equals(""))
                                    {
                                        str1.Append(pi.Name + ",");                                           //拼接字段
                                        str2.Append("@" + pi.Name + ",");                                     //声明参数
                                        paras.Add(new SqlParameter("@" + pi.Name, pi.GetValue(model, null))); //对参数赋值
                                    }
                                }
                            }
                        }
                        strSql.Append(str1.ToString().Trim(','));
                        strSql.Append(") values (");
                        strSql.Append(str2.ToString().Trim(','));
                        strSql.Append(") ");
                        strSql.Append(";select @@IDENTITY;");
                        object obj = DbHelperSQL.GetSingle(conn, trans, strSql.ToString(), paras.ToArray());//带事务
                        model.id = Convert.ToInt32(obj);
                        mid      = model.id;
                        #endregion

                        #region 权限表信息
                        //if (model.UserPemissionList != null)
                        //{
                        //    string sql = "";
                        //    foreach (Model.userRolePemission item in model.UserPemissionList)
                        //    {
                        //        sql = "insert into MS_userRolePemission(urp_type,urp_roleid,urp_username,urp_code) values(@type,0,@name,@code)";
                        //        SqlParameter[] sp = new SqlParameter[] {
                        //            new SqlParameter("@type" , item.urp_type),
                        //            new SqlParameter("@name", item.urp_username),
                        //            new SqlParameter("@code", item.urp_code)
                        //        };
                        //        DbHelperSQL.ExecuteSql(conn, trans, sql, sp);
                        //    }
                        //}
                        #endregion

                        #region 生成内部客户
                        result = AddInnerCustomer(model, conn, trans);
                        if (!string.IsNullOrEmpty(result))
                        {
                            trans.Rollback();
                            return(result);
                        }
                        trans.Commit(); //提交事务
                        #endregion
                    }
                    catch (Exception ex)
                    {
                        trans.Rollback(); //回滚事务
                        result = ex.Message;
                    }
                }
            }
            return(result);
        }
Ejemplo n.º 8
0
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public string Update(Model.manager model, bool updateName = false, bool updateContact = false)
        {
            string result = string.Empty;

            using (SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString))
            {
                conn.Open();//打开数据连接
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        #region 主表信息==========================
                        StringBuilder strSql = new StringBuilder();
                        StringBuilder str1   = new StringBuilder();
                        //利用反射获得属性的所有公共属性
                        PropertyInfo[]      pros  = model.GetType().GetProperties();
                        List <SqlParameter> paras = new List <SqlParameter>();
                        strSql.Append("update " + databaseprefix + "manager set ");
                        foreach (PropertyInfo pi in pros)
                        {
                            //拼接字段,忽略List<T>
                            if (!typeof(System.Collections.IList).IsAssignableFrom(pi.PropertyType))
                            {
                                //如果不是主键则追加sql字符串
                                if (!pi.Name.Equals("id"))
                                {
                                    //判断属性值是否为空
                                    if (pi.GetValue(model, null) != null && !pi.GetValue(model, null).ToString().Equals(""))
                                    {
                                        str1.Append(pi.Name + "=@" + pi.Name + ",");                          //声明参数
                                        paras.Add(new SqlParameter("@" + pi.Name, pi.GetValue(model, null))); //对参数赋值
                                    }
                                }
                            }
                        }
                        strSql.Append(str1.ToString().Trim(','));
                        strSql.Append(" where id=@id ");
                        paras.Add(new SqlParameter("@id", model.id));
                        int obj = DbHelperSQL.ExecuteSql(conn, trans, strSql.ToString(), paras.ToArray());
                        #endregion

                        #region 权限表信息
                        //if (obj > 0)
                        //{
                        //    string sql = "delete from MS_userRolePemission where urp_username=@name";
                        //    SqlParameter[] sp1 = new SqlParameter[] {
                        //            new SqlParameter("@name" , model.user_name)
                        //        };
                        //    DbHelperSQL.ExecuteSql(conn, trans, sql, sp1);
                        //    if (model.UserPemissionList != null)
                        //    {
                        //        sql = "";
                        //        foreach (Model.userRolePemission item in model.UserPemissionList)
                        //        {
                        //            sql = "insert into MS_userRolePemission(urp_type,urp_roleid,urp_username,urp_code) values(@type,0,@name,@code)";
                        //            SqlParameter[] sp = new SqlParameter[] {
                        //                new SqlParameter("@type" , item.urp_type),
                        //                new SqlParameter("@name", item.urp_username),
                        //                new SqlParameter("@code", item.urp_code)
                        //            };
                        //            DbHelperSQL.ExecuteSql(conn, trans, sql, sp);
                        //        }
                        //    }
                        //}
                        #endregion
                        if (obj > 0)
                        {
                            if (updateName)
                            {
                                #region 生成新的内部客户
                                result = AddInnerCustomer(model, conn, trans);
                                if (!string.IsNullOrEmpty(result))
                                {
                                    trans.Rollback();
                                    return(result);
                                }
                                #endregion
                            }
                            else
                            {
                                string sql = "";
                                if (updateContact)
                                {
                                    #region 更新原来的内部客户
                                    sql = "update a set a.co_number='" + model.telephone + "' from MS_Contacts a left join MS_Customer on co_cid = c_id and co_flag=1 where c_name ='员工-" + model.real_name + "(" + model.user_name + ")'";
                                    DbHelperSQL.Query(conn, trans, sql);
                                    #endregion
                                }
                            }
                        }

                        #region 更新用户钉钉授权绑定状态
                        if (model.is_lock == 0)
                        {
                            new DAL.manager_oauth(databaseprefix).UpdateField(conn, trans, model.id, "is_lock=0");
                        }
                        #endregion

                        trans.Commit(); //提交事务
                    }
                    catch (Exception ex)
                    {
                        trans.Rollback(); //回滚事务
                        return(ex.Message);
                    }
                }
            }
            return(result);
        }