ASP.NET|构建数据访问组件

最新推荐文章于 2024-11-08 18:30:00 发布

weixin_30315435

最新推荐文章于 2024-11-08 18:30:00 发布

阅读量85

点赞数

文章标签：数据库 ui

原文链接：http://www.cnblogs.com/yexinwei/archive/2012/12/20/2827000.html

版权

数据访问组件是什么？

　　数据访问组件是专门用来对数据库进行操作的类。

　　数据访问组件通常至少两个类，一个封装单条记录的数据包类（数据类）和一个使用ADO.NET代码执行实际数据库操作的数据库应用类（数据访问类）。

优秀的数据访问组件具备以下特点：

　　创建的数据库组件封装良好，经过优化且可以在单独的进程中执行。如果需要，还可以用在多服务器负载平衡的环境中。

构建优秀的数据访问组件需要做到：

　　尽快打开和关闭连接

　　　　减少和数据库的连接时间，提高效率，节省资源。

　　实现错误处理

　　　　保证即使SQL命令产生异常，连接也要被关闭，站着茅坑不拉屎对谁都不好。

　　遵守无状态设计实践

　　　　数据访问类除了连接字符串，不应该存在其他用来存储状态的字段和属性，即使是连接字符串，也是从被初始化开始，之后一直不会发生变化的字段。

　　　　尽可能用一句SQL语句完成所有任务。也可以说是：尽可能只用一次连接完成所有任务。

　　不要让客户端使用广泛开发的查询

　　　　查询确实需要的列，不要总是Select * From Table。

　　　　在可能的情况下，用where子句限制返回的行数。

数据访问组件的工作原理：

　　简单而设计良好的数据库组件使用单独的类（数据类）来代表数据库中的表。

　　　　例如数据库中的一张学生信息表（包含列有：学生学号，学生姓名，学生年龄）对应一个学生信息类（包含字段和属性有：学生学号，学生姓名，学生年龄）。

　　通用的数据库访问方法（如插入、删除和修改记录）被封装在单独的无状态方法中。

　　最后，每个数据库访问使用专门的存储过程。

　　这些连起来的工作流程就是：

　　　　（举例）用户在网页中插入一条学生信息，输入了学生学号，姓名，年龄，提交给服务器。服务器接受到信息后构造一个学生信息类的实例，给实例中的属性赋值，然后将实例作为参数传给数据访问类中的方法，此方法通过使用数据库存储过程给数据库中添加学生信息。

构建数据访问组件实践：

建立数据库

CREATE TABLE Musics(
MusicID uniqueidentifier PRIMARY KEY,
MusicName nvarchar(50),
CategoryName nvarchar(50),
Singer nvarchar(50),
Lyric nvarchar(500),
UploaderID nvarchar(50),
Url nvarchar(50),
AddDate datetime,
Clicks int
)

构建数据类

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

/// <summary>
///MusicDetails 的摘要说明
/// </summary>
public class MusicDetails
{
    public MusicDetails(Guid musicID, string musicName, string categoryName, string singer, string lyric, string uploaderID, string url, DateTime addDate, int clicks)
    {
        //
        //TODO: 在此处添加构造函数逻辑
        //
        MusicID = musicID;
        MusicName = musicName;
        CategoryName = categoryName;
        Singer = singer;
        Lyric = lyric;
        UploaderID = uploaderID;
        Url = url;
        AddDate = addDate;
        Clicks = clicks;
    }
    private Guid musicID;

    public Guid MusicID
    {
        get { return musicID; }
        set { musicID = value; }
    }
    private string musicName;

    public string MusicName
    {
        get { return musicName; }
        set { musicName = value; }
    }
    private string categoryName;

    public string CategoryName
    {
        get { return categoryName; }
        set { categoryName = value; }
    }
    private string singer;

    public string Singer
    {
        get { return singer; }
        set { singer = value; }
    }
    private string lyric;

    public string Lyric
    {
        get { return lyric; }
        set { lyric = value; }
    }
    private string uploaderID;

    public string UploaderID
    {
        get { return uploaderID; }
        set { uploaderID = value; }
    }
    private string url;

    public string Url
    {
        get { return url; }
        set { url = value; }
    }
    private DateTime addDate;

    public DateTime AddDate
    {
        get { return addDate; }
        set { addDate = value; }
    }
    private int clicks;

    public int Clicks
    {
        get { return clicks; }
        set { clicks = value; }
    }
    

}

构建存储过程

CREATE PROCEDURE CountMusics
AS
SELECT COUNT(MusicID) FROM Musics
GO

CREATE PROCEDURE DeleteMusic
@MusicID nvarchar(50)
AS
DELETE FROM Musics WHERE MusicID=@MusicID
GO

CREATE PROCEDURE GetAllMusics
AS
SELECT MusicID, MusicName, CategoryName, Singer, Lyric, UploaderID, Url, AddDate,Clicks FROM Musics
GO

CREATE PROCEDURE GetMusic
@MusicID nvarchar(50)
AS
SELECT MusicID, MusicName, CategoryName, Singer, Lyric, UploaderID, Url, AddDate,Clicks FROM Musics
WHERE MusicID=@MusicID
GO

CREATE PROCEDURE InsertMusic
@MusicID nvarchar(50) OUTPUT,
@MusicName nvarchar(50),
@CategoryName nvarchar(50),
@Singer nvarchar(50),
@Lyric nvarchar(500),
@UploaderID nvarchar(50),
@Url nvarchar(50)
AS
SET @MusicID=newID()
INSERT INTO Musics
  (MusicID, MusicName, CategoryName, Singer, Lyric, UploaderID, Url, AddDate,Clicks)
  VALUES(@MusicID, @MusicName, @CategoryName, @Singer, @Lyric, @UploaderID, @Url, GETDATE(),0)
GO

CREATE PROCEDURE UpdateMusic
@MusicID nvarchar(50),
@MusicName nvarchar(50),
@CategoryName nvarchar(50),
@Singer nvarchar(50),
@Lyric nvarchar(500),
@UploaderID nvarchar(50),
@Url nvarchar(50),
@AddDate datetime,
@Clicks int
AS
UPDATE Musics
SET MusicName=@MusicName,
CategoryName=@CategoryName,
Singer=@Singer,
Lyric=@Lyric,
UploaderID=@UploaderID,
Url=@Url,
AddDate=@AddDate,
Clicks=@Clicks
WHERE MusicID=@MusicID
GO

构建数据访问类

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Configuration;
using System.Data.SqlClient;
using System.Data;

/// <summary>
///MusicDB 的摘要说明
/// </summary>
public class MusicDB
{
    private string connectionString;
    public MusicDB()
    {
        //
        //TODO: 在此处添加构造函数逻辑
        //
        connectionString = WebConfigurationManager.ConnectionStrings["MusicBar"].ConnectionString;
    }
    public MusicDB(string connectionString)
    {
        this.connectionString = connectionString;
    }
    public Guid InsertMusic(MusicDetails mus)
    {
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand("InsertMusic", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("@MusicName", SqlDbType.NVarChar, 50));
        cmd.Parameters["@MusicName"].Value = mus.MusicName;
        cmd.Parameters.Add(new SqlParameter("@CategoryName", SqlDbType.NVarChar, 50));
        cmd.Parameters["@CategoryName"].Value = mus.CategoryName;
        cmd.Parameters.Add(new SqlParameter("@Singer", SqlDbType.NVarChar, 50));
        cmd.Parameters["@Singer"].Value = mus.Singer;
        cmd.Parameters.Add(new SqlParameter("@Lyric", SqlDbType.NVarChar, 500));
        cmd.Parameters["@Lyric"].Value = mus.Lyric;
        cmd.Parameters.Add(new SqlParameter("@UploaderID", SqlDbType.NVarChar, 50));
        cmd.Parameters["@UploaderID"].Value = mus.UploaderID;
        cmd.Parameters.Add(new SqlParameter("@Url", SqlDbType.NVarChar, 50));
        cmd.Parameters["@Url"].Value = mus.Url;
        cmd.Parameters.Add(new SqlParameter("@MusicID", SqlDbType.UniqueIdentifier));
        cmd.Parameters["@MusicID"].Direction = ParameterDirection.Output;
        try
        {
            con.Open();
            cmd.ExecuteNonQuery();
            return (Guid)cmd.Parameters["@MusicID"].Value;
        }
        catch (SqlException err)
        {
            throw new ApplicationException("Data error.");
        }
        finally
        {
            con.Close();
        }
        
    }
    public void DeleteMusic(Guid musicID)
    {
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand("DeleteMusic", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("@MusicID", SqlDbType.UniqueIdentifier));
        cmd.Parameters["@MusicID"].Value = musicID;

        try
        {
            con.Open();
            cmd.ExecuteNonQuery();
        }
        catch (SqlException err)
        {
            throw new ApplicationException("Data error.");
        }
        finally
        {
            con.Close();
        }
    }
    public void UpdateMusic(MusicDetails mus)
    {
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand("UpdateMusic", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("@MusicID", SqlDbType.UniqueIdentifier));
        cmd.Parameters["@MusicID"].Value = mus.MusicID;
        cmd.Parameters.Add(new SqlParameter("@MusicName", SqlDbType.NVarChar, 50));
        cmd.Parameters["@MusicName"].Value = mus.MusicName;
        cmd.Parameters.Add(new SqlParameter("@CategoryName", SqlDbType.NVarChar, 50));
        cmd.Parameters["@CategoryName"].Value = mus.CategoryName;
        cmd.Parameters.Add(new SqlParameter("@Singer", SqlDbType.NVarChar, 50));
        cmd.Parameters["@Singer"].Value = mus.Singer;
        cmd.Parameters.Add(new SqlParameter("@Lyric", SqlDbType.NVarChar, 500));
        cmd.Parameters["@Lyric"].Value = mus.Lyric;
        cmd.Parameters.Add(new SqlParameter("@UploaderID", SqlDbType.NVarChar, 50));
        cmd.Parameters["@UploaderID"].Value = mus.UploaderID;
        cmd.Parameters.Add(new SqlParameter("@Url", SqlDbType.NVarChar, 50));
        cmd.Parameters["@Url"].Value = mus.Url;
        cmd.Parameters.Add(new SqlParameter("@AddDate", SqlDbType.DateTime));
        cmd.Parameters["@AddDate"].Value = mus.AddDate;
        cmd.Parameters.Add(new SqlParameter("@Clicks", SqlDbType.Int));
        cmd.Parameters["@Clicks"].Value = mus.Clicks;

        try
        {
            con.Open();
            cmd.ExecuteNonQuery();
        }
        catch (SqlException err)
        {
            throw new ApplicationException("Data error.");
        }
        finally
        {
            con.Close();
        }
        
    }
    public MusicDetails GetMusic(Guid musicID)
    {
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand("GetMusic", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("@MusicID", SqlDbType.UniqueIdentifier));
        cmd.Parameters["MusicID"].Value = musicID;

        try
        {
            con.Open();
            SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
            if (!reader.HasRows) return null;
            reader.Read();
            MusicDetails mus = new MusicDetails(
                (Guid)reader["MusicID"], reader["MusicName"].ToString(),
                reader["CategoryName"].ToString(), reader["Singer"].ToString(),
                reader["Lyric"].ToString(), reader["UploaderID"].ToString(),
                reader["Url"].ToString(), (DateTime)reader["AddDate"],
                (int)reader["Clicks"]);
            reader.Close();
            return mus;
        }
        catch (SqlException err)
        {
            throw new ApplicationException("Data error.");
        }
        finally
        {
            con.Close();
        }
    }
    public List<MusicDetails> GetMusics()
    {
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand("GetAllMusics", con);
        cmd.CommandType = CommandType.StoredProcedure;

        List<MusicDetails> musics = new List<MusicDetails>();

        try
        {
            con.Open();
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                MusicDetails mus = new MusicDetails(
                (Guid)reader["MusicID"], reader["MusicName"].ToString(),
                reader["CategoryName"].ToString(), reader["Singer"].ToString(),
                reader["Lyric"].ToString(), reader["UploaderID"].ToString(),
                reader["Url"].ToString(), (DateTime)reader["AddDate"],
                (int)reader["Clicks"]);
                musics.Add(mus);
            }
            reader.Close();
            return musics;
        }
        catch (SqlException err)
        {
            throw new ApplicationException("Data error.");
        }
        finally
        {
            con.Close();
        }
    }
    public int CountMusics()
    {
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand("CountMusics", con);
        cmd.CommandType = CommandType.StoredProcedure;

        try
        {
            con.Open();
            return (int)cmd.ExecuteScalar();
        }
        catch (SqlException err)
        {
            throw new ApplicationException("Data error.");
        }
        finally
        {
            con.Close();
        }

    }
}

测试数据库组件

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Text;

public partial class ComponentTest : System.Web.UI.Page
{
    private MusicDB db = new MusicDB();
    protected void Page_Load(object sender, EventArgs e)
    {
        //db.InsertMusic(new MusicDetails(new Guid(), "童话", "流行", "光良", "无歌词", "无上传", "无存储", DateTime.Now, 0));
        //db.InsertMusic(new MusicDetails(new Guid(), "半城烟沙", "流行", "许嵩", "无歌词", "无上传", "无存储", DateTime.Now, 0));
        //db.InsertMusic(new MusicDetails(new Guid(), "素颜", "流行", "许嵩", "无歌词", "无上传", "无存储", DateTime.Now, 0));
        //db.InsertMusic(new MusicDetails(new Guid(), "春天里", "流行", "汪峰", "无歌词", "无上传", "无存储", DateTime.Now, 0));
        //db.InsertMusic(new MusicDetails(new Guid(), "白天不懂夜的黑", "流行", "那英", "无歌词", "无上传", "无存储", DateTime.Now, 0));
        //db.UpdateMusic(new MusicDetails(new Guid("392409a5-dc28-43b3-aca8-ed27e4ab0839"), "半城烟沙(改)", "流行", "杨坤", "无歌词", "无上传", "无存储", DateTime.Now, 0));
        WriteMusicsList();

        HtmlContent.Text += "<br />Inserted 1 music.<br />";
        Guid musID = db.InsertMusic(new MusicDetails(new Guid(), "无所谓", "流行", "杨坤", "无歌词", "无上传", "无存储", DateTime.Now, 0));
        
        WriteMusicsList();

        db.DeleteMusic(musID);
        HtmlContent.Text += "<br />Deleted 1 music.<br />";

        WriteMusicsList();
    }
    private void WriteMusicsList()
    {
        StringBuilder htmlStr = new StringBuilder("");
        int numMusics = db.CountMusics();
        htmlStr.Append("<br />Total musics: <b>");
        htmlStr.Append(numMusics.ToString());
        htmlStr.Append("</b><br /><br />");

        List<MusicDetails> musics = db.GetMusics();
        foreach (MusicDetails mus in musics)
        {
            htmlStr.Append("<li>");
            htmlStr.Append(mus.MusicID);
            htmlStr.Append("|<b>");
            htmlStr.Append(mus.MusicName);
            htmlStr.Append("-");
            htmlStr.Append(mus.Singer);
            htmlStr.Append("</b>|");
            htmlStr.Append(mus.UploaderID);
            htmlStr.Append("</li>");
        }
        htmlStr.Append("<br />");
        HtmlContent.Text += htmlStr.ToString();
    }
    
}