数据访问层，完整的增，删，改，查

 

添加信息

 /// <summary>
        /// 添加新UseType
        /// </summary>
        /// <param name="useType">需要添加的内容</param>
        /// <returns>返回受影响多少行</returns>
        public int InsertUseType(UseType useType)
        {
            string cmdText = string.Format("insert into dbo.UseType (userTypeTo) values(@userTypeTo)");
            SqlCommand command = new SqlCommand(cmdText);
            command.CommandType = CommandType.Text;
            SqlParameter[] sqlParameters = new SqlParameter[]
            {
               new SqlParameter("@userTypeTo",SqlDbType.NVarChar,30)
            };

            if (useType.UserTypeTo == null)
                sqlParameters[0].Value = DBNull.Value;
            else
                sqlParameters[0].Value = useType.UserTypeTo;

            command.Parameters.AddRange(sqlParameters);

            return SQLDBHelper.ExecuteNonQuery(SQLDBHelper.ConnectionString, command);
        }

 

更新信息

 /// <summary>
        /// 更新UseType信息
        /// </summary>
        /// <param name="useType">需要更新的UseType对象</param>
        /// <returns>返回更新的行数</returns>
        public int UpdateUseTypeById(UseType useType)
        {
            string cmdText = string.Format("update dbo.UseType set [userTypeTo] = @userTypeTo where [userTypeId] = @userTypeId");
            SqlCommand command = new SqlCommand(cmdText);
            SqlParameter[] sqlParameters = new SqlParameter[]
            {
               new SqlParameter("@userTypeTo",SqlDbType.NVarChar,30),
               new SqlParameter("@userTypeId",SqlDbType.Int,4)
               
            };

            if (useType.UserTypeTo == null)
                sqlParameters[0].Value = DBNull.Value;
            else
                sqlParameters[0].Value = useType.UserTypeTo;

            sqlParameters[1].Value = useType.UserTypeId;

            command.Parameters.AddRange(sqlParameters);
            return SQLDBHelper.ExecuteNonQuery(SQLDBHelper.ConnectionString, command);
        }

 

查询全部信息

 /// <summary>
        /// 获取所有UseType信息
        /// </summary>
        /// <returns>返回所有UseType信息列表</returns>
        public List<UseType> GetUseTypeAll()
        {
            List<UseType> useTypeList = new List<UseType>();

            string cmdText = "select * from dbo.UseType";
            SqlCommand command = new SqlCommand(cmdText);
            using (SqlDataReader dr = SQLDBHelper.ExecuteReader(SQLDBHelper.ConnectionString, command))
            {
                while (dr.Read())
                {
                    UseType useType = new UseType();
                    useType.UserTypeId = dr.GetInt32(0);
                    if (dr[1] is System.DBNull)
                        useType.UserTypeTo = String.Empty;
                    else
                        useType.UserTypeTo = dr.GetString(1);

                    useTypeList.Add(useType);
                }
            }

            return useTypeList;
        }

 

根据Id获取信息

 /// <summary>
        /// 根据useTypeId获取useTypeId信息
        /// </summary>
        /// <param name="useTypeId">编号</param>
        /// <returns>useTypeId信息</returns>
        public UseType GetUseTypeByID(int useTypeId)
        {
            UseType useType = new UseType();
            string cmdText = string.Format("select * from dbo.UseType where userTypeId=@userTypeId");
            SqlCommand command = new SqlCommand();
            command.CommandText = cmdText;
            SqlParameter[] sqlParameters = new SqlParameter[]
            {
    new SqlParameter("@userTypeId",SqlDbType.Int,4) 
   };
            sqlParameters[0].Value = useTypeId;
            command.Parameters.AddRange(sqlParameters);

            using (SqlDataReader dr = SQLDBHelper.ExecuteReader(SQLDBHelper.ConnectionString, command))
            {
                if (dr.Read())
                {
                    useType.UserTypeId = dr.GetInt32(0);
                    if (dr[1] is System.DBNull)
                        useType.UserTypeTo = String.Empty;
                    else
                        useType.UserTypeTo = dr.GetString(1);
                }
            }
            return useType;
        }

 

/// <summary>
        /// 根据useTypeId删除信息
        /// </summary>
        /// <param name="useTypeId">编号</param>
        /// <returns>返回删除行数</returns>
        public int DeleteUseType(int useTypeId)
        {
            string cmdText = "Delete dbo.UseType where userTypeId=@userTypeId";
            SqlCommand command = new SqlCommand(cmdText);

            SqlParameter[] sqlParameters = new SqlParameter[]
            {
    new SqlParameter("@userTypeId",SqlDbType.Int,4) 
   };

            sqlParameters[0].Value = useTypeId;
            command.Parameters.AddRange(sqlParameters);

            return SQLDBHelper.ExecuteNonQuery(SQLDBHelper.ConnectionString, command);
        }