65.9K
CodeProject 正在变化。 阅读更多。
Home

数据访问方法比较 - 第 3 部分

李松博士
starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.67/5 (13投票s)

2013年10月9日

CPOL

8分钟阅读

viewsIcon

36703

downloadIcon

930

.NET环境中数据访问方法比较的第三部分。

介绍 

.NET环境中数据访问方法的第三部分。在本部分，我将整合所有内容。我将向您展示一个中等复杂的示例，该示例可以在一次数据库往返中执行插入、更新和删除操作。我将比较 ADO.NET 与 NHibernate。 总的来说，NHibernate 在访问数据库时可能会很慢，尤其是在处理大量数据集时。在本示例中，我将向您展示与直接使用 ADO.NET 相比，它有多慢。 虽然比较是在 .NET 环境和 SQL Server 数据库中进行的，但概念和结果应适用于 Java 和 Oracle 等其他环境。

背景     

在本文的 第一部分 和 第二部分 中，我进行了一些关于读取操作和插入操作性能的比较。如果您还不是 NHibernate 专家，我强烈建议您阅读 第一部分 和 第二部分 ，特别是 第一部分。在 第一部分 中，我概述了不同数据访问方法的结构，并详细讨论了如何在 .Net 应用程序中设置 NHibernate 环境。在本文的这一部分，我将直接开始使用 ADO.NET 和 NHibernate 进行数据库操作，并比较数据访问性能。

示例数据库 

本文中的示例使用 Visual Studio 2010 和 SQL Server 2008 R2。如果您想自己重复这些示例，您将需要一个 SQL Server，并且需要拥有对它的管理员权限。如果您在数据库服务器上运行以下脚本，您将获得示例数据库以及示例的所有表和存储过程。 

SET NOCOUNT ON
	
USE [master]
GO
	
-- Create a login user
IF EXISTS
	(SELECT * FROM sys.server_principals
		WHERE name = N'TestUser')
DROP LOGIN [TestUser]
GO
	
EXEC sp_addlogin @loginame = 'TestUser', @passwd  = 'Password123';
GO
	
-- Create the database
IF EXISTS 
	(SELECT name FROM sys.databases 
		WHERE name = N'SQLPerformanceInsertUpdateDelete')
BEGIN
 ALTER DATABASE [SQLPerformanceInsertUpdateDelete] SET SINGLE_USER
	WITH ROLLBACK IMMEDIATE
 DROP DATABASE [SQLPerformanceInsertUpdateDelete]
END
GO
	
CREATE DATABASE SQLPerformanceInsertUpdateDelete
GO
	
USE [SQLPerformanceInsertUpdateDelete]
GO
	
-- Grant the required database access to the login 
sp_grantdbaccess 'TestUser'
GO
sp_addrolemember 'db_datareader', 'TestUser'
GO
sp_addrolemember 'db_datawriter', 'TestUser'
GO
	
CREATE TABLE [dbo].[TStudent](
	[ID] [int] NOT NULL,
	[LastName] [varchar](50) NOT NULL,
	[FirstName] [varchar](50) NOT NULL,
	[EnrollmentTime] [datetime] NOT NULL,
 CONSTRAINT [PK_TStudent] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
	IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
	
CREATE TABLE [dbo].[TCourse](
	[ID] [int] NOT NULL,
	[Name] [varchar](50) NOT NULL,
	[CreditHours] [int] NOT NULL,
 CONSTRAINT [PK_TCourse] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
	IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
	
CREATE UNIQUE NONCLUSTERED INDEX [IX_Name] ON [dbo].[TCourse] 
(
	[Name] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,
	IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,
	ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
	
CREATE TABLE [dbo].[TStudentScore](
	[StudentID] [int] NOT NULL,
	[CourseID] [int] NOT NULL,
	[Score] [int] NOT NULL,
	[LastUpdated] [datetime] NOT NULL,
 CONSTRAINT [PK_TStudentScore] PRIMARY KEY CLUSTERED 
(
	[StudentID] ASC,
	[CourseID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,
	ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
	
ALTER TABLE [dbo].[TStudentScore]  WITH CHECK ADD
	CONSTRAINT [FK_TStudentScore_TScore] FOREIGN KEY([CourseID])
REFERENCES [dbo].[TCourse] ([ID])
	
ALTER TABLE [dbo].[TStudentScore]  WITH CHECK ADD
	CONSTRAINT [FK_TStudentScore_TStudent] FOREIGN KEY([StudentID])
REFERENCES [dbo].[TStudent] ([ID])
GO
	
CREATE TYPE [dbo].[TStudentTableType] AS TABLE(
	[ID] [int] NOT NULL,
	[LastName] [varchar](50) NOT NULL,
	[FirstName] [varchar](50) NOT NULL,
	[EnrollmentTime] DATETIME NOT NULL,
	PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
GO
	
CREATE TYPE [dbo].[TStudentScoreTableType] AS TABLE 
(
	StudentID INT NOT NULL,
	CourseName VARCHAR(50) NOT NULL, 
	Score INT NOT NULL,
	LastUpdated DATETIME NOT NULL
)
GO
	
-- Add the courses
INSERT INTO TCourse VALUES(1, 'English', 3)
INSERT INTO TCourse VALUES(2, 'Math', 5)
INSERT INTO TCourse VALUES(3, 'Biology', 4)
INSERT INTO TCourse VALUES(4, 'Music', 3)
INSERT INTO TCourse VALUES(5, 'Basketball', 2)
GO
	
CREATE PROCEDURE DBO.GetStudents
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT S.ID StudentId, s.LastName, s.FirstName,
		AVG(SS.Score) AverageScore,
		SUM(CASE WHEN SS.Score >= 60 THEN C.CreditHours ELSE 0 END) Credits
	FROM TStudent S
		LEFT JOIN TStudentScore SS ON S.ID = SS.StudentID
		LEFT JOIN TCourse C ON SS.CourseID = C.ID
	GROUP BY S.ID, s.LastName, s.FirstName
	ORDER BY S.ID
END
GO
	
CREATE PROCEDURE [dbo].[InsertUpdateDeleteStudents]
	@Students TStudentTableType READONLY,
	@StudentScores TStudentScoreTableType READONLY
AS
BEGIN
	SET NOCOUNT ON;
	
	CREATE TABLE #TempStudentScore
	(
		StudentID INT NOT NULL,
		CourseID INT NOT NULL,
		Score INT NOT NULL,
		LastUpdated DATETIME NOT NULL
	)
	CREATE INDEX #IX_StudentID ON #TempStudentScore (StudentID)
	CREATE INDEX #IX_CourseID ON #TempStudentScore (CourseID)
	
	INSERT INTO #TempStudentScore
	SELECT SS.StudentID, C.ID, SS.Score, SS.LastUpdated
	FROM @StudentScores SS INNER JOIN TCourse C ON SS.CourseName = C.Name
	
	BEGIN TRANSACTION
	BEGIN TRY
		-- DELETE
		DELETE SS
		FROM TStudentScore SS LEFT JOIN #TempStudentScore temp
			ON SS.StudentID = temp.StudentID AND SS.CourseID = temp.CourseID
		WHERE temp.StudentID IS NULL
		
		DELETE TS
		FROM TStudent TS LEFT JOIN @Students S
			ON TS.ID = S.ID
		WHERE S.ID IS NULL
		
		-- UPDATE
		UPDATE TS SET LastName = S.LastName, FirstName = S.FirstName,
			EnrollmentTime = S.EnrollmentTime
		FROM TStudent TS INNER JOIN @Students S ON TS.ID = S.ID
		
		UPDATE TSS SET CourseID = temp.CourseID, LastUpdated = temp.LastUpdated
		FROM TStudentScore TSS INNER JOIN #TempStudentScore temp
			ON TSS.StudentID = temp.StudentID AND TSS.CourseID = temp.CourseID
		
		-- INSERT
		INSERT INTO TStudent
		SELECT S.ID, S.LastName, S.FirstName, S.EnrollmentTime
		FROM @Students S LEFT JOIN TStudent TS ON S.ID = TS.ID
		WHERE TS.ID IS NULL
		
		INSERT INTO TStudentScore
		SELECT Temp.StudentID, Temp.CourseID, Temp.Score, Temp.LastUpdated
		FROM #TempStudentScore Temp LEFT JOIN TStudentScore SS
			ON Temp.StudentID = SS.StudentID AND Temp.CourseID = SS.CourseID
		WHERE SS.StudentID IS NULL
	
	END TRY
	BEGIN CATCH
		IF @@TRANCOUNT > 0
			ROLLBACK TRANSACTION
	
		RAISERROR ('Unable to save students', 16, 1)
		RETURN
	END CATCH
	
	IF @@TRANCOUNT > 0
		COMMIT TRANSACTION
END
GO
	
GRANT EXECUTE ON TYPE::[dbo].[TStudentTableType] TO TestUser
GRANT EXECUTE ON TYPE::[dbo].[TStudentScoreTableType] TO TestUser
GRANT EXECUTE ON OBJECT::[dbo].[GetStudents] TO TestUser
GRANT EXECUTE ON OBJECT::[dbo].[InsertUpdateDeleteStudents] TO TestUser
GO
	
-- Bring the database on-line
ALTER DATABASE [SQLPerformanceInsertUpdateDelete] SET MULTI_USER
GO
	
PRINT 'SQLPerformanceInsertUpdateDelete database is created'

成功运行脚本后，我们将在数据库服务器中有一个名为 [SQLPerformanceInsertUpdateDelete] 的数据库。 该脚本还创建了表之间的适当索引和引用关系。  

在 [SQLPerformanceInsertUpdateDelete] 数据库中，我们有 3 个表和 2 个存储过程。数据库 [SQLPerformanceInsertUpdateDelete] 的数据库图如下所示： 

 

创建数据库时，[TCourse] 表将填充以下数据： 



存储过程 [GetStudents] 创建如下。示例应用程序使用它从数据库检索信息以供示例应用程序显示。 

CREATE PROCEDURE [dbo].[GetStudents]
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT S.ID StudentId, s.LastName, s.FirstName,
		AVG(SS.Score) AverageScore,
		SUM(CASE WHEN SS.Score >= 60 THEN C.CreditHours ELSE 0 END) Credits
	FROM TStudent S
		LEFT JOIN TStudentScore SS ON S.ID = SS.StudentID
		LEFT JOIN TCourse C ON SS.CourseID = C.ID
	GROUP BY S.ID, s.LastName, s.FirstName
	ORDER BY S.ID
END

存储过程 [InsertUpdateDeleteStudents] 用于应用程序将信息上传到数据库。 

CREATE PROCEDURE [dbo].[InsertUpdateDeleteStudents]
	@Students TStudentTableType READONLY,
	@StudentScores TStudentScoreTableType READONLY
AS
BEGIN
	SET NOCOUNT ON;
	
	CREATE TABLE #TempStudentScore
	(
		StudentID INT NOT NULL,
		CourseID INT NOT NULL,
		Score INT NOT NULL,
		LastUpdated DATETIME NOT NULL
	)
	CREATE INDEX #IX_StudentID ON #TempStudentScore (StudentID)
	CREATE INDEX #IX_CourseID ON #TempStudentScore (CourseID)
	
	INSERT INTO #TempStudentScore
	SELECT SS.StudentID, C.ID, SS.Score, SS.LastUpdated
	FROM @StudentScores SS INNER JOIN TCourse C ON SS.CourseName = C.Name
	
	BEGIN TRANSACTION
	BEGIN TRY
		-- DELETE
		DELETE SS
		FROM TStudentScore SS LEFT JOIN #TempStudentScore temp
			ON SS.StudentID = temp.StudentID AND SS.CourseID = temp.CourseID
		WHERE temp.StudentID IS NULL
		
		DELETE TS
		FROM TStudent TS LEFT JOIN @Students S
			ON TS.ID = S.ID
		WHERE S.ID IS NULL
		
		-- UPDATE
		UPDATE TS SET LastName = S.LastName, FirstName = S.FirstName,
			EnrollmentTime = S.EnrollmentTime
		FROM TStudent TS INNER JOIN @Students S ON TS.ID = S.ID
		
		UPDATE TSS SET CourseID = temp.CourseID, LastUpdated = temp.LastUpdated
		FROM TStudentScore TSS INNER JOIN #TempStudentScore temp
			ON TSS.StudentID = temp.StudentID AND TSS.CourseID = temp.CourseID
		
		-- INSERT
		INSERT INTO TStudent
		SELECT S.ID, S.LastName, S.FirstName, S.EnrollmentTime
		FROM @Students S LEFT JOIN TStudent TS ON S.ID = TS.ID
		WHERE TS.ID IS NULL
		
		INSERT INTO TStudentScore
		SELECT Temp.StudentID, Temp.CourseID, Temp.Score, Temp.LastUpdated
		FROM #TempStudentScore Temp LEFT JOIN TStudentScore SS
			ON Temp.StudentID = SS.StudentID AND Temp.CourseID = SS.CourseID
		WHERE SS.StudentID IS NULL
	
	END TRY
	BEGIN CATCH
		IF @@TRANCOUNT > 0
			ROLLBACK TRANSACTION
	
		RAISERROR ('Unable to save students', 16, 1)
		RETURN
	END CATCH
	
	IF @@TRANCOUNT > 0
		COMMIT TRANSACTION
END

这两个 用户定义表类型 由存储过程 [InsertUpdateDeleteStudents] 用于接收应用程序的信息。 

CREATE TYPE [dbo].[TStudentTableType] AS TABLE(
	[ID] [int] NOT NULL,
	[LastName] [varchar](50) NOT NULL,
	[FirstName] [varchar](50) NOT NULL,
	[EnrollmentTime] [datetime] NOT NULL,
	PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF)
) 
CREATE TYPE [dbo].[TStudentScoreTableType] AS TABLE(
	[StudentID] [int] NOT NULL,
	[CourseName] [varchar](50) NOT NULL,
	[Score] [int] NOT NULL,
	[LastUpdated] [datetime] NOT NULL
)

要上传的示例数据  

为了比较数据访问方法的性能，我在本文中包含了 2 个示例数据文件。 

 

 

本文的目的是创建一个 .Net Web 应用程序，该应用程序将 XML 文件上传到数据库。成功上传后，数据库中的数据将与 XML 文件中的信息匹配，这意味着： 

  • 如果数据库中存在学生及其分数，但 XML 文件中不存在，则将其删除；
  • 如果数据库中不存在学生及其分数，但 XML 文件中存在，则将其插入；
  • 如果数据库和 XML 文件中都存在学生及其分数，但信息不同，则将其更新为与 XML 文件中的信息匹配。
示例 Web 应用程序将使用 ADO.Net 和 NHibernate 来访问数据库并比较性能。 

MVC Web 应用程序 

与本文的 第一部分 和 第二部分一样，示例应用程序是在 Visual Studio 2010 和 MVC 2 中开发的，因此大多数人都可以下载并运行它，而不会遇到任何环境问题。 

 

  • Controllers\HomeController.cs 是应用程序的控制器。 
  • ViewModels\IndexVM.cs 实现了 MVC 视图模型。
  • "DataAccessLayer" 中的文件实现了使用每种方法访问数据库的代码。  

"DataAccessUtilities" 文件夹中的文件实现了一些非常基本的功能，以支持每种数据访问方法的数据库操作。这些文件与本文 第一部分 中讨论的几乎相同。我强烈建议您查看 第一部分，其中我详细讨论了不同数据访问方法的架构结构以及如何在应用程序中设置环境来使用 ADO.Net、NHibernate 和 Dapper。我将使用 Dapper 从数据库加载数据以在此应用程序中显示。"DataAccessMethod.cs" 文件实现了一个 枚举，该枚举代表用于将学生上传到数据库的数据访问方法。  

namespace SQLPerformanceInsertUpdateDelete.DataAccessUtilities
{
    public enum DataAccessMethod { AdoNet, NHibernate };
}

在本文中，我将比较 ADO.Net 和 NHibernate 之间的插入、更新和删除数据库操作的性能。  数据库连接字符串保留在 "Web.config" 文件中。  

<appSettings>
    <add key="ConnectionString"
         value="Data Source=localhost;Initial Catalog=SQLPerformanceInsertUpdateDelete;
		User Id=TestUser;Password=Password123;"/>
</appSettings>

视图模型 

应用程序的视图模型实现在 ViewModels\IndexVM.cs文件。 

using System;
using System.Collections.Generic;
using SQLPerformanceInsertUpdateDelete.DataAccessUtilities;
	
namespace SQLPerformanceInsertUpdateDelete.ViewModels
{
    public class Student
    {
        public int StudentId { get; set; }
        public string LastName { get; set; }
        public string FirstName { get; set; }
        public int AverageScore { get; set; }
        public int Credits { get; set; }
    }
	
    public class IndexVm
    {
        public DataAccessMethod AccessMethod { get; set; }
        public TimeSpan? TimeSpent { get; set; }
        public List<Student> UploadedStudents { get; set; }
    }
}

类 Student 代表上传到 Web 应用程序的学生，而类 IndexVm 将由 UI 使用，以显示上传的学生以及通过每种数据访问方法对数据库执行插入、更新和删除操作所花费的时间。   

数据访问层 

 

ADO.NET 数据访问方法   

文件 ADONET\AdoNetDbAccess.cs 实现了使用 ADO.NET 将学生插入、更新和删除到数据库的代码。 

using System;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Xml.Linq;
using SQLPerformanceInsertUpdateDelete.DataAccessUtilities.ADONET;
using SQLPerformanceInsertUpdateDelete.ViewModels;
	
namespace SQLPerformanceInsertUpdateDelete.DataAccessLayer.ADONET
{
    public static class AdoNetDbAccess
    {
        private static DataSet GetStudentList(HttpPostedFileBase fileStudents)
        {
            var studentTable = new DataTable("studentTable");
            studentTable.Columns.Add("ID",
                System.Type.GetType("System.Int32"));
            studentTable.Columns.Add("LastName",
                System.Type.GetType("System.String"));
            studentTable.Columns.Add("FirstName",
                System.Type.GetType("System.String"));
            studentTable.Columns.Add("EnrollmentTime",
                System.Type.GetType("System.DateTime"));
	
            var studentCourseTable = new DataTable("studentCourseTable");
            studentCourseTable.Columns.Add("StudentID",
                System.Type.GetType("System.Int32"));
            studentCourseTable.Columns.Add("CourseName",
                System.Type.GetType("System.String"));
            studentCourseTable.Columns.Add("Score",
                System.Type.GetType("System.Int32"));
            studentCourseTable.Columns.Add("LastUpdated",
                System.Type.GetType("System.DateTime"));
	
            var streamStudents = fileStudents.InputStream;
            streamStudents.Position = 0;
            var doc = XDocument.Load(streamStudents);
            var students = doc.Descendants("student");
	
            foreach (var student in students)
            {
                // Get the student
                var studentrow = studentTable.NewRow();
                var studentId = Convert.ToInt32(student.Attribute("Id").Value);
                studentrow["Id"] = studentId;
                studentrow["LastName"] = student.Descendants("LastName")
                    .First().Value;
                studentrow["FirstName"] = student.Descendants("FirstName")
                    .First().Value;
                studentrow["EnrollmentTime"] = student
                    .Descendants("EnrollmentTime").First().Value;
                studentTable.Rows.Add(studentrow);
	
                // Get the courses
                var scores = student.Descendants("score");
                foreach (var score in scores)
                {
                    var studentscorerow = studentCourseTable.NewRow();
                    studentscorerow["StudentID"] = studentId;
                    studentscorerow["CourseName"] = score.Attribute("Class").Value;
                    studentscorerow["Score"] = score.Descendants("Score").First().Value;
                    studentscorerow["LastUpdated"] = score
                        .Descendants("LastUpdated").First().Value;
	
                    studentCourseTable.Rows.Add(studentscorerow);      
                }
            }
	
            var set = new DataSet();
            set.Tables.Add(studentTable);
            set.Tables.Add(studentCourseTable);
	
            return set;
        }
	
        public static IndexVm UploadFile(HttpPostedFileBase fileStudents)
        {
            var vm = new IndexVm();
	
            var startTime = DateTime.Now;
	
            var cmd = new SqlCommand
            {
                CommandType = CommandType.StoredProcedure,
                CommandText = "InsertUpdateDeleteStudents"
            };
            var set = GetStudentList(fileStudents);
            cmd.Parameters.AddWithValue("@Students",
                set.Tables["studentTable"]);
            cmd.Parameters.AddWithValue("@StudentScores",
                set.Tables["studentCourseTable"]);
            AdoNetUtility.ExecuteCommand(cmd);
	
            vm.TimeSpent = DateTime.Now.Subtract(startTime);
            return vm;
        }
    }
}

方法 UploadFile 在单个批次中调用存储过程 [InsertUpdateDeleteStudents]。它会更改 [TStudent] 和 [TStudentScore] 表中的数据。事务在存储过程内部控制，因此如果成功，则所有学生和分数都会在数据库中更改。如果事务失败，则没有任何更改。 

NHibernate 数据访问方法 

要使用 NHibernate 数据访问方法，我们首先需要为我们将要处理的每个数据库表创建数据模型类。 

namespace SQLPerformanceInsertUpdateDelete.DataAccessLayer.NHibernate.DataModel
{
    public class TCourse
    {
        public virtual int Id { get; set; }
        public virtual string Name { get; set; }
        public virtual int CreditHours { get; set; }
    }
} 
using System;
using System.Collections.Generic;
    
namespace SQLPerformanceInsertUpdateDelete.DataAccessLayer.NHibernate.DataModel
{
    public class TStudent
    {
        public virtual int Id { get; set; }
        public virtual string LastName { get; set; }
        public virtual string FirstName { get; set; }
        public virtual DateTime EnrollmentTime { get; set; }
        public virtual IList<TStudentScore> StudentScores { get; set; }
    }
} 
using System;
	
namespace SQLPerformanceInsertUpdateDelete.DataAccessLayer.NHibernate.DataModel
{
    public class TStudentScore
    {
        public virtual int StudentId { get; set; }
        public virtual int CourseId { get; set; }
        public virtual int Score { get; set; }
        public virtual DateTime LastUpdated { get; set; }
	
        public virtual TCourse Course { get; set; }
	
        public override bool Equals(object obj)
        {
            var other = obj as TStudentScore;
	
            if (ReferenceEquals(null, other)) return false;
            if (ReferenceEquals(this, other)) return true;
	
            return StudentId == other.StudentId &&
                CourseId == other.CourseId;
        }
	
        public override int GetHashCode()
        {
            unchecked
            {
                int hash = GetType().GetHashCode();
                hash = (hash * 31) ^ StudentId.GetHashCode();
                hash = (hash * 31) ^ CourseId.GetHashCode();
	
                return hash;
            }
        }
    }
}

我们还需要创建 NHibernate 映射类，将每个数据模型类与每个数据库表连接起来。 

using FluentNHibernate.Mapping;
using SQLPerformanceInsertUpdateDelete.DataAccessLayer.NHibernate.DataModel;
	
namespace SQLPerformanceInsertUpdateDelete.DataAccessLayer.NHibernate.TableMapping
{
    public class TCourseMap : ClassMap<TCourse>
    {
        public TCourseMap()
        {
            Table("TCourse");
	
            Id(x => x.Id).Column("ID");
            Map(x => x.Name).Column("Name").CustomSqlType("varchar").Length(50);
            Map(x => x.CreditHours).Column("CreditHours");
	
            ReadOnly();
        }
    }
} 
using FluentNHibernate.Mapping;
using SQLPerformanceInsertUpdateDelete.DataAccessLayer.NHibernate.DataModel;
	
namespace SQLPerformanceInsertUpdateDelete.DataAccessLayer.NHibernate.TableMapping
{
    public class TStudentMap : ClassMap<TStudent>
    {
        public TStudentMap()
        {
            Table("TStudent");
	
            Id(x => x.Id).Column("ID").GeneratedBy.Assigned();
	
            Map(x => x.LastName).Column("LastName").CustomSqlType("varchar").Length(50);
            Map(x => x.FirstName).Column("FirstName").CustomSqlType("varchar");
            Map(x => x.EnrollmentTime).Column("EnrollmentTime");
	
            HasMany(x => x.StudentScores).KeyColumn("StudentId").Inverse().ReadOnly();
        }
    }
} 
using FluentNHibernate.Mapping;
using SQLPerformanceInsertUpdateDelete.DataAccessLayer.NHibernate.DataModel;
	
namespace SQLPerformanceInsertUpdateDelete.DataAccessLayer.NHibernate.TableMapping
{
    public class TStudentScoreMap : ClassMap<TStudentScore>
    {
        public TStudentScoreMap()
        {
            Table("TStudentScore");
	
            CompositeId().KeyProperty(x => x.StudentId, "StudentId")
                .KeyProperty(x => x.CourseId, "CourseId");
	
            Map(x => x.Score).Column("Score");
            Map(x => x.LastUpdated).Column("LastUpdated");
	
            References(x => x.Course).ForeignKey("CourseId").Column("CourseId").ReadOnly();
        }
    }
}

基于数据模型类和映射类，"NHibernateDbAccess.cs" 文件实现了使用 NHibernate 在数据库中插入、更新和删除学生及分数的数据库操作。 

using System;
using System.Collections.Generic;
using System.Web;
using System.Xml.Linq;
using SQLPerformanceInsertUpdateDelete.DataAccessLayer.NHibernate.DataModel;
using SQLPerformanceInsertUpdateDelete.DataAccessUtilities.NHibernate;
using SQLPerformanceInsertUpdateDelete.ViewModels;
using System.Linq;
using NHibernate.Linq;
	
namespace SQLPerformanceInsertUpdateDelete.DataAccessLayer.NHibernate
{
    public class NHibernateDbAccess
    {
        public static List<TStudent> GetUploadedStudentList(HttpPostedFileBase fileStudents)
        {
            var students = new List<TStudent>();
	
            // Load course from the database, so we can have
            // the course Id for each student course
            // The xml file only has the course name
            List<TCourse> courses;
            using (var session = NHibernateUtility.OpenSession())
            {
                courses = session.Query<TCourse>().ToList();
            }
	
            var streamStudents = fileStudents.InputStream;
            streamStudents.Position = 0;
            var doc = XDocument.Load(streamStudents);
            var xmlstudents = doc.Descendants("student");
	
            foreach (var xmlstudent in xmlstudents)
            {
                var studentId = Convert.ToInt32(xmlstudent.Attribute("Id").Value);
                var student = new TStudent()
                {
                    Id = studentId,
                    LastName = xmlstudent.Descendants("LastName").First().Value,
                    FirstName = xmlstudent.Descendants("FirstName").First().Value,
                    EnrollmentTime =
                        Convert.ToDateTime(xmlstudent
                            .Descendants("EnrollmentTime").First().Value)
                };
	
                var studentScores = new List<TStudentScore>();
                student.StudentScores = studentScores;
                students.Add(student);
                	
                var xmlscores = xmlstudent.Descendants("score");
                foreach (var xmlscore in xmlscores)
                {
                    var course = (from c in courses
                                  where c.Name == xmlscore.Attribute("Class").Value
                                  select c).Single();
                    var studentScore = new TStudentScore()
                    {
                        StudentId = studentId,
                        CourseId = course.Id,
                        Course = course,
                        Score = Convert.ToInt32(xmlscore.Descendants("Score")
                            .First().Value),
                        LastUpdated =
                            Convert.ToDateTime(xmlscore.Descendants("LastUpdated")
                            .First().Value)
                    };
	
                    studentScores.Add(studentScore);
                }
            }
	
            return students;
        }
	
        private static List<TStudent> GetDbStudentList()
        {
            List<TStudent> students;
            using (var session = NHibernateUtility.OpenSession())
            {
                // Make sure to eager load the student scores for
                // better performance
                students = session.Query<TStudent>()
                    .FetchMany(s => s.StudentScores).ToList();
            }
	
            return students;
        }
	
        public static IndexVm UploadFile(HttpPostedFileBase fileStudents)
        {
            var vm = new IndexVm();
            var startTime = DateTime.Now;
	
            var xmlStudents = GetUploadedStudentList(fileStudents);
            var dbStudents = GetDbStudentList();
	
            using (var session = NHibernateUtility.OpenSession())
            {
                using (var transaction = session.BeginTransaction())
                {
                    //Delete students & scores not in XML
                    foreach (var dbStudent in dbStudents)
                    {
                        // Check if the student is in the XML
                        var studentId = dbStudent.Id;
                        var xmlStudent = (from s in xmlStudents
                                          where s.Id == studentId
                                          select s).SingleOrDefault();
	
                        // student does not exist in xml, delete everything
                        if (xmlStudent == null)
                        {
                            foreach (var dbscore in dbStudent.StudentScores)
                            {
                                session.Delete(dbscore);
                            }
	
                            session.Delete(dbStudent);
                            continue;
                        }
	
                        // student exist in the xml, check if any scores
                        // need to delete
                        var xmlScores = xmlStudent.StudentScores;
                        foreach (var dbscore in dbStudent.StudentScores)
                        {
                            var courseId = dbscore.CourseId;
                            var xmlScore = (from s in xmlScores
                                            where s.StudentId == studentId
                                                && s.CourseId == courseId
                                            select s).SingleOrDefault();
	
                            if (xmlScore == null)
                            {
                                session.Delete(dbscore);
                            }
                        }
                    }
	
                    // Save or update the studens and the scores
                    foreach (var xmlStudent in xmlStudents)
                    {
                        session.SaveOrUpdate(xmlStudent);
	
                        foreach (var xmlScore in xmlStudent.StudentScores)
                        {
                            session.SaveOrUpdate(xmlScore);
                        }
                    }
	
                    transaction.Commit();
                }
            }
	
            vm.TimeSpent = DateTime.Now.Subtract(startTime);
            return vm;
        }
    }
}

Dapper 数据访问方法 

在本文中，我仅使用 Dapper 读取数据库。"DapperDbAccess.cs" 实现的代码用于将学生信息从数据库加载到 Web 应用程序中显示。 

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using SQLPerformanceInsertUpdateDelete.DataAccessUtilities.ADONET;
using SQLPerformanceInsertUpdateDelete.DataAccessUtilities.Dapper;
using SQLPerformanceInsertUpdateDelete.ViewModels;
	
namespace SQLPerformanceInsertUpdateDelete.DataAccessLayer.Dapper
{
    public static class DapperDbAccess
    {
        public static List<Student> LoadStudentSummaries()
        {
            List<Student> students;
	
            using (var cn = AdoNetUtility.GetConnection())
            {
                cn.Open();
                students
                    = cn.Query<Student>("GetStudents",
                        commandType: CommandType.StoredProcedure).ToList();
            }
	
            return students;
        }
    }
}

Dapper 非常轻量级且高效。如果您有兴趣了解 Dapper 的性能，可以参考本文的 第一部分。  

控制器   

此 MVC 应用程序的控制器实现在 Controllers\HomeController.cs 文件中。 

 using System.Web;
using System.Web.Mvc;
using SQLPerformanceInsertUpdateDelete.ActionFilters;
using SQLPerformanceInsertUpdateDelete.DataAccessLayer.ADONET;
using SQLPerformanceInsertUpdateDelete.DataAccessLayer.Dapper;
using SQLPerformanceInsertUpdateDelete.DataAccessLayer.NHibernate;
using SQLPerformanceInsertUpdateDelete.DataAccessUtilities;
using SQLPerformanceInsertUpdateDelete.ViewModels;
	
namespace SQLPerformanceInsertUpdateDelete.Controllers
{
    [HandleError]
    [NoCache]
    public class HomeController : Controller
    {
        public ActionResult Index()
        {
            var vm = new IndexVm
            {
                AccessMethod = DataAccessMethod.AdoNet,
                TimeSpent = null,
                UploadedStudents = null
            };
	
            return View(vm);
        }
	
        [HttpPost]
        public ActionResult UploadStudents(DataAccessMethod radAccessMethod,
            HttpPostedFileBase fileStudents)
        {
            var vm = (radAccessMethod == DataAccessMethod.AdoNet)
                         ? AdoNetDbAccess.UploadFile(fileStudents)
                         : NHibernateDbAccess.UploadFile(fileStudents);
            vm.UploadedStudents = DapperDbAccess.LoadStudentSummaries();
            vm.AccessMethod = radAccessMethod;
	
            return View("Index", vm);
        }
    }
}
操作方法 Index 仅加载应用程序的主页。当文件被上传时， UploadStudents 方法使用所选的数据访问方法执行数据库操作。它还将数据库中的学生信息以及插入、更新和删除学生所花费的时间传递给视图页面进行显示。 

视图   

示例应用程序是一个单视图 MVC 应用程序。视图实现在 "Views\Home\Index.aspx" 文件中。   

<%@ Page Language="C#" Inherits="System.Web.Mvc.ViewPage<dynamic>" %>
<%@ Import Namespace="SQLPerformanceInsertUpdateDelete.DataAccessUtilities" %>
	
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
	
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>SQL Access Performance - Insert Update Delete ALL in One!</title>
    <link href="<%=Url.Content("~/Content/Site.css") %>"
        rel="stylesheet" type="text/css" />
    <script src="<%=Url.Content("~/Scripts/jquery-1.8.2.min.js") %>"
        type="text/javascript"></script>
</head>
	
<body>
    <div id="container">
        <div id="header">
            <h1>SQL Access Performance - Insert Update Delete ALL in One!</h1>
        </div>
        <div id="content">
            <form id="frmMain" method="post" enctype="multipart/form-data">
                <div>
                    <label class="textLabel">Data access method</label>
                    <span style="margin-right: 10px">
                        <input type="radio" name="radAccessMethod"
                                value="<%=DataAccessMethod.AdoNet.ToString()%>"
                                <%if (Model.AccessMethod == DataAccessMethod.AdoNet)
                                    { %> checked="checked" <% } %> />
                        <label>ADO.Net</label>
                        <input type="radio" name="radAccessMethod"
                                value="<%=DataAccessMethod.NHibernate.ToString()%>"
                                <%if (Model.AccessMethod == DataAccessMethod.NHibernate)
                                    { %> checked="checked" <% } %> />
                        <label>NHibernate</label>
                    </span>
                    <label class="textLabel">File to upload</label>
                    <span>
                        <input type="file" id="fileStudents" name="fileStudents"/>
                        <input type="button" id="btnSubmitFile" value="Submit file" />
                    </span>
                </div>
            </form>
            <div id="divResult">
                <%if (Model.TimeSpent != null)
                    {%>
                    <br/>
                    <label>Database Access Time: <span style="color: red">
                                                        <%=Model.TimeSpent%>
                                                    </span></label>
                <% }%>
	
                <%if (Model.UploadedStudents != null)
                    {%>
                    <div id="divStudentSummary" class="divTable">
                    <div class="tableHeader">
                        <span>StudentId</span>
                        <span>Last Name</span>
                        <span>First Name</span>
                        <span>Average Score</span>
                        <span>Credits</span>
                    </div>
	
                    <% foreach (var student in Model.UploadedStudents)
                        {%>
                        <div>
                            <span><%=student.StudentId%></span>
                            <span><%=student.LastName%></span>
                            <span><%=student.FirstName%></span>
                            <span><%=student.AverageScore%></span>
                            <span><%=student.Credits%></span>
                        </div>
                    <% }%>
                </div>
                <% }%>
            </div>
        </div>
        <div id="footer">Copyright © SL. 2012</div>
    </div>
</body>
</html>

此视图使用的 JavaScript 代码如下。 

<script type="text/javascript">
        $(document).ready(function () {
            var clearResult = function () {
                $('#divResult').html('');
            };
            
            $('#btnSubmitFile').click(function () {
                if ($.trim($('#fileStudents').val()) == '') {
                    alert('Please browse a file to upload ...');
                    return;
                }
	
                clearResult();
                $('#frmMain').attr('action', fileUploadUrl).submit();
            });
	
            $('input[name=radAccessMethod]').change(function () {
                clearResult();
            });
        });
    </script>

运行应用程序  

 确保您的 SQL Server 正在运行且可访问，并确保 [SQLPerformanceInsertUpdateDelete] 数据库已成功创建。然后我们可以运行应用程序。 

 

我们可以选择一种数据访问方法，然后上传文件到服务器。图片显示了使用 ADO.Net 方法的结果。 

比较结果  

平均 10 次运行的时间，我在我的电脑上得到了以上结果，这表明 ADO.Net 方法比 NHibernate 方法快约 150 倍。如果您有时间，可以重复我的实验。您也可以尝试创建更大的 XML 文件来上传更多学生，看看比较结果如何。  

关注点        

  • 我终于完成了关于 .Net 环境中数据访问方法的比较的第三部分。如我之前提到的，尽管示例是在 .Net 和 SQL Server 中进行的，但这些概念绝对可以应用于其他环境，例如 Java 和 Oracle。
  • 如果您花时间阅读本文的 第一部分 和 第二部分 ，您会发现阅读这一部分变得非常容易。 
  • 在本文的 第一部分 中，我比较了不同方法在读取操作上的性能。比较表明，Dapper 在读取操作方面是一个非常高效的 ORM 工具。 
  • 在本文的 第二部分 中，我比较了 ADO.Net 和 NHibernate 在插入操作上的性能。我还向您展示了 NHibernate 的 Hilo 以及它给我们带来的问题。
  • 在本文的这一部分，我将插入、更新和删除操作放在一起，比较了 ADO.Net 和 NHibernate 之间的性能。 
  • 在所有比较中，ADO.Net 的性能都远远优于 NHibernate。 
  • 要使用数据访问方法，您不需要对 ADO.Net 进行任何特殊配置。您也可以轻松使用 Dapper。但如果您想使用 NHibernate，则需要包含许多额外的 DLL。有时，获取这些 DLL 可能并不容易。
  • 希望您喜欢我的帖子，希望本文能以任何方式帮助到您。  

历史

首次修订 - 2013/10/9

.NET环境中数据访问方法比较 - 第3部分 - CodeProject - 代码之家
© . All rights reserved.