简单的增删改查--示例

增

/// <summary>
        /// 添加新的用户
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public static UserInfo AddUser(UserInfo user)
        {
            string sql = "insert UserInfo(Name,LoginId,LoginPwd,UserRoleId,UserStateId)" +
                "values(@name,@loginId,@loginPwd,@userRoleId,@userStateId)";
            sql += " ; select @@IDENTITY";

            SqlParameter[] para = new SqlParameter[]
            {
                new SqlParameter("@name",user.Name),
                new SqlParameter("@loginId",user.LoginId),
                new SqlParameter("@loginPwd",user.LoginPwd),
                new SqlParameter("@userRoleId",user.UserRoleId.Id),
                new SqlParameter("@userStateId",user.UserStateId.Id)
            };
            int newId = DBHelper.GetScalar(sql, para);
            return GetUserById(newId);

        }

删

 /// <summary>
        /// 根据id删除用户
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static bool DELETEUserById(int id)
        {
            string sql = "DELETE FROM UserInfo WHERE UserId=@id";

            SqlParameter[] para = new SqlParameter[]
            {
                new SqlParameter("@id",id)
            };

            int count = DBHelper.ExecuteCommand(sql, para);
            if (count > 0)
            {
                return true;
            }
            else {
                return false;
            }
        }

改

 /// <summary>
        /// 更新用户
        /// </summary>
        /// <param name="user"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public static bool ModifyUser(UserInfo user,int id)
        {
            string sql = "UPDATE UserInfo SET  "
             + "Name = @name,"
             + "LoginId = @login,"
             + "LoginPwd = @pwd "
             //+ "UserStateId = @state,"
             //+ "UserRoleId=@role "
             + "WHERE UserId =@userid";
            sql += " ; SELECT @@IDENTITY";
            try
            {
                SqlParameter[] para = new SqlParameter[]
            {
                 
                new SqlParameter("@name", user.Name),  
                new SqlParameter("@pwd", user.LoginPwd),
                new SqlParameter("@login", user.LoginId),
                //new SqlParameter("@role", user.UserRoleId.Id),
                //new SqlParameter("@state",user.UserStateId.Id),
                new SqlParameter("@UserId",id)
            };
                int count = DBHelper.ExecuteCommand(sql, para);
                if (count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                throw;
            }
        }

查询单条信息

/// <summary>
        /// 根据id查询用户
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static UserInfo GetUserById(int id)
        {
            string sql = "select * from UserInfo where UserId =@id";

            SqlParameter[] para = new SqlParameter[] 
            {
                 new  SqlParameter("@id",id)
            };
            int roleId;
            int stateId;
            using(SqlDataReader reader =DBHelper.GetReader(sql, para))
            {
                if (reader.Read())
                {
                    UserInfo user = new UserInfo();
                    user.UserId = (int)reader["UserId"];
                    user.LoginId = (string)reader["LoginId"];
                    user.LoginPwd = (string)reader["LoginPwd"];
                    user.Name = (string)reader["Name"];
                    roleId = (int)reader["UserRoleId"];
                    stateId = (int)reader["UserStateId"];
                    reader.Close();
                    user.UserRoleId = RoleInfoService.GetRoleById(roleId);
                    user.UserStateId = UserStateService.GetStateById(stateId);
                    
                    return user;
                }
                else
                {
                    reader.Close();
                    return null;
                }
            }
        }

查询多条信息

 /// <summary>
        /// 根据sql语句查询用户
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static IList<UserInfo> GetUserBysql(string sql)
        {
            List<UserInfo> list = new List<UserInfo>();
            using (DataTable dt = DBHelper.GetDataSet(sql))
            {
                foreach (DataRow row in dt.Rows)
                {
                    UserInfo user = new UserInfo();
                    user.UserId = (int)row["UserId"];
                    user.LoginId = (string)row["LoginId"];
                    user.LoginPwd = (string)row["LoginPwd"];
                    user.Name = (string)row["Name"];
                    user.UserRoleId = RoleInfoService.GetRoleById((int)row["UserRoleId"]);
                    user.UserStateId = UserStateService.GetStateById((int)row["UserStateId"]);

                    list.Add(user);
                }
                return list;
            }
        }

/// <summary>
        /// 查询所有的用户
        /// </summary>
        /// <returns></returns>
        public static IList<UserInfo> GetAllUser()
        {
            string sql = "select * from UserInfo";
            return GetUserBysql(sql);

        }