northwinduserinfo.cs

来自「asp。net 2.0宝典一书源码 全书源码给大家共享」· CS 代码 · 共 218 行

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.ComponentModel;

namespace DataSourceDemo
{
    /// <summary>
    /// 操作Northwind数据库的UserInfo表
    /// </summary>
    public class NorthwindUserInfo
    {
        public NorthwindUserInfo()
        {
           
        }

        /// <summary>
        /// 获取数据库连接字符串
        /// </summary>
        /// <returns></returns>
        private static string GetConnnectionString()
        {
            //读取配置文件
            System.Configuration.ConnectionStringSettings connString = System.Web.Configuration.WebConfigurationManager.
                ConnectionStrings["NorthwindConnectionString"];
            if (null != connString)
            {
                return connString.ConnectionString;
            }
            else
                return "";
        }

        /// <summary>
        /// 获取所有用户信息列表
        /// </summary>
        /// <returns></returns>
        [DataObjectMethod(DataObjectMethodType.Select)]
        public static DataTable GetAllUser()
        {
            SqlConnection conn;
            try
            {
                //读取用户信息的SQL语句
                string sqlCommand = "SELECT * from UserInfo ";
                //建立数据库连接对象
                conn = new SqlConnection(GetConnnectionString());
                SqlDataAdapter da = new SqlDataAdapter(sqlCommand, conn);
                DataSet ds = new DataSet();
                conn.Open();
                //填充用户信息表
                da.Fill(ds, "UserInfo");
                return ds.Tables["UserInfo"];
            }
            catch
            {
                return null;
            }
        }

        /// <summary>
        /// 根据用户ID获取用户信息
        /// </summary>
        /// <param name="userID"></param>
        /// <returns></returns>
        [DataObjectMethod(DataObjectMethodType.Select)]
        public static DataTable GetUserByID(int userID)
        {
            SqlConnection conn;
            try
            {
                //读取用户信息的SQL语句
                string sqlCommand = "SELECT * from UserInfo where UserID=@UserID";
                conn = new SqlConnection(GetConnnectionString());
                SqlDataAdapter da = new SqlDataAdapter(sqlCommand, conn);
                da.SelectCommand.Parameters.Add("UserID", SqlDbType.Int).Value = userID;
                DataSet ds = new DataSet();
                conn.Open();
                //填充符合用户ID的信息记录
                da.Fill(ds, "UserInfo");
                return ds.Tables["UserInfo"];
            }
            catch
            {
                return null;
            }
        }

        /// <summary>
        /// 新增加一个用户
        /// </summary>
        /// <param name="UserID">返回新建的用户ID</param>
        /// <param name="FirstName">用户名</param>
        /// <param name="LastName">用户姓</param>
        /// <returns>是否添加成功</returns>
        [DataObjectMethod(DataObjectMethodType.Insert)]
        public static bool InsertUser(out int UserID, string FirstName, string LastName)
        {
            UserID=0;
            SqlConnection conn = new SqlConnection(GetConnnectionString());
            //增加用户信息的SQL语句
            SqlCommand cmd = new SqlCommand("INSERT INTO UserInfo " +
                                                "  (FirstName, LastName) " +
                                                "  Values(@FirstName, @LastName); " +
                                                "SELECT @UserID = SCOPE_IDENTITY()", conn);

            //构建用户信息的参数
            cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 10).Value = FirstName;
            cmd.Parameters.Add("@LastName", SqlDbType.VarChar, 10).Value = LastName;
            SqlParameter p = cmd.Parameters.Add("@UserID", SqlDbType.Int);
            p.Direction = ParameterDirection.Output;

            try
            {
                conn.Open();

                //执行增加的SQL语句
                cmd.ExecuteNonQuery();
                //返回新增加的用户ID
                UserID = (int)p.Value;
            }
            catch 
            {
                
            }
            finally
            {
                conn.Close();
            }

            return true;
        }

        /// <summary>
        /// 更新输入用户ID的用户信息
        /// </summary>
        /// <param name="UserID">用户ID</param>
        /// <param name="FirstName">用户名</param>
        /// <param name="LastName">用户姓</param>
        /// <returns>更新成功或者失败</returns>
        [DataObjectMethod(DataObjectMethodType.Update)]
        public static bool UpdateUser(int UserID, string FirstName, string LastName)
        {
            SqlConnection conn = new SqlConnection(GetConnnectionString());
            //修改用户信息的SQL语句
            SqlCommand cmd = new SqlCommand("UPDATE UserInfo " +
                                                "  SET FirstName=@FirstName, LastName=@LastName " +
                                                "  WHERE UserID=@UserID", conn);
            //构建用户信息的参数
            cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 10).Value = FirstName;
            cmd.Parameters.Add("@LastName", SqlDbType.VarChar, 10).Value = LastName;
            cmd.Parameters.Add("@UserID", SqlDbType.Int).Value = UserID;
            try
            {
                conn.Open();
                //执行修改的SQL语句
                if (cmd.ExecuteNonQuery() == 0)
                    return false;
            }
            catch
            {
                
            }
            finally
            {
                conn.Close();
            }
            return true;
        }

        /// <summary>
        /// 删除指定用户ID的用户信息
        /// </summary>
        /// <param name="UserID">用户ID</param>
        /// <returns>操作成功或者失败</returns>
        [DataObjectMethod(DataObjectMethodType.Delete)]
        public static bool DeleteUser(int UserID)
        {
            SqlConnection conn = new SqlConnection(GetConnnectionString());
            //删除用户信息的SQL语句
            SqlCommand cmd = new SqlCommand("delete UserInfo " +
                                                "  WHERE UserID=@UserID", conn);
            //构建用户ID信息的参数
            cmd.Parameters.Add("@UserID", SqlDbType.Int).Value = UserID;
            try
            {
                conn.Open();

                //执行删除的SQL语句
                if (cmd.ExecuteNonQuery() == 0)
                    return false;
            }
            catch
            {

            }
            finally
            {
                conn.Close();
            }
            return true;
        }





    }
}