Generating Data Access Layer Using HXFUtil
In this tutorial we are going to use HXFUtil application to generate data access layer to SQL Server database
HXFUtil 1.2 can be downloaded here.
Create a new SQL Server database and name it something like "HXF_TEST", create a new table in the database using the SQL below.
CREATE TABLE STUDENT (
[ID] INT PRIMARY KEY IDENTITY(1,1),
[INTERNAL_ID] INT NOT NULL UNIQUE,
[NAME] NVARCHAR(60) NOT NULL,
[BIRTH_DATE] DATETIME2 NOT NULL,
[COLLEGE] INT NOT NULL,
[EMAIL] NVARCHAR(60) NOT NULL UNIQUE,
[TOTAL_RATE] FLOAT NOT NULL,
[NOTES] NVARCHAR(MAX) NULL
)HXF (more accurately HXF-P) is not an ORM tool and it is not trying to be one, the idea is to interact with the RDBMS through stored procedures only. The HXF-P generator will map each stored procedure to a C# method, and each table in the database will be represented as a simple class that serves as a data container only. Furthormore, it is possible to generate common (CRUD) procedures in the database automatically which in return will be mapped as C# methods.
To generate the database CRUD stored procedures (and data access layer later) we are going to use "HXFUtil" which is a GUI tool that allows the developer to manage the generation and update of the generated source code. The image below shows the main interface of HXFUtil.
Start HXFUtil, in the Data Access group set the connection string for your database, you can do that manually or by openning the connection dialog. Next, save your configuration by clicking the save icon in the main toolbar.
To generate CRUD stored procedures in your database go to "Operations" menu and then click on "Update Database". HXF-P will analyze your database tables and generate some stored procedures according to them. After the operation is done, goto your database and have a look at the generated stored procedures.
As you may notice, the CRUD generator generates a set of stored procedures that would cover most of the normal operations you do on a table. However, you are not obligated to use any of these generated procedures, you can even skip the step of generating CRUD procedures totally. You can change, delete and add stored procedures the way you like, the important thing to know is that all the procedures in your database whether auto-generated or written manually by you will be mapped to C# methods when you generate the data access layer source code in the next step. The image below shows the generated stored procedures.
In this section we are going to add a new stored procedure to the database, the new stored procedure will return three result sets, the first one will contain all the students in a certain college, the second one will contain studens with rate more or equal to 50, and the third one will contain the students with rate less than 50. Furthormore, the procedure will return the total count of students as the stored procedure result, it will also return the average rate of all students as an output paramater. The moral of creating this stored procedure is to demonstrate the ability to map all the features of stored procedures to C# methods.
The SQL of the stored procedure is shown below.
CREATE PROCEDURE GET_STUDENTS_BY_COLLEGE(
@COLLEGE INT,
@AVERAGE INT OUTPUT
)
AS
BEGIN
DECLARE @TOTAL_COUNT INT;
SELECT * FROM STUDENT WHERE [COLLEGE] = @COLLEGE;
SELECT * FROM STUDENT WHERE [COLLEGE] = @COLLEGE AND [TOTAL_RATE] >= 50;
SELECT * FROM STUDENT WHERE [COLLEGE] = @COLLEGE AND [TOTAL_RATE] < 50;
SELECT @TOTAL_COUNT = COUNT(ID) FROM STUDENT WHERE [COLLEGE] = @COLLEGE;
SELECT @AVERAGE = AVG([TOTAL_RATE]) FROM STUDENT WHERE [COLLEGE] = @COLLEGE;
RETURN @TOTAL_COUNT;
ENDIn this section we are going to generate the data access layer for our database. To do this, start HXFUtil, set the location for "DAL Location" where you want to save your data access layer files, then, go to "Operations" menu and click on "Update DAL". HXF will generate a set of C# files that contain the source code of your data access layer. we will see how to use the generated data access layer in the next section.
In this section we are going to use the generated data access layer source files.
Create a new project using the "Console Application" template. Add the generated data access files to your project, you also need to add the "HXF.Persistence.dll" assembly to the project. Add the below code to your Main method.
string connectionString = "Data Source=<server>;initial catalog=HXF_TEST;User=sa;Password=<password>;";
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
IDataStore dataStore = new DataStore(conn);
int? avg = 0;
StoredProcResult result = dataStore.GetStudentsByCollege(1, ref avg, typeof(Student), typeof(Student), typeof(Student));
List<Student> allStudents = result.FirstResultSet.OfType<Student>().ToList<Student>();
List<Student> successStudents = result.ResultSets[1].OfType<Student>().ToList<Student>();
List<Student> failedStudents = result.ResultSets[2].OfType<Student>().ToList<Student>();
int totalCount = (int)result.ReturnedValue;
Console.WriteLine("Results: Total: {0}, Aaverage: {1}", totalCount, avg);
}The above code shows how it is possible to get the data returned from the stored procedure as generic lists directly, it shows also how the output parameter was mapped to a ref parameter in C#.
Try to call other procedures from the database, you are encouraged also to have a look at the generated data access layer files, the generated source code is designed to be read and altered when necessary by developers. However, please note that these files are auto-generated so becareful not to lose the changes you applied manually.
This tutorial showed the main features of HXF-P which is to generate data access layer code in no time. Getting used to this model of work where all data operations are done using stored procedure might be strange at first, but I think this model is much cleaner and efficient than using ORM tools.