Recently I used Telerik Data Access for my Data Access Layer
in one of my project. I have to deliver project quickly so I decided that
instead of manually writing DAL just use some utility which can speed up the
development pace. The project used in this article can be downloaded from here.
Telerik Data Access is outstanding and probably solution to database independent applications like I use to create in PHP. It has saved my tons of development hours and there is no need to maintain data access layers with stored procedure calls and lots of SQL in database. I have tested the product with complex object models as well and found no issue in it. I think every developer should give it a try and focus on something challenging instead of writing boring and repetitive code of SQL and .Net DAL.
Here is the list of supported databases by Telerik Data Access:-
Telerik Data Access is outstanding and probably solution to database independent applications like I use to create in PHP. It has saved my tons of development hours and there is no need to maintain data access layers with stored procedure calls and lots of SQL in database. I have tested the product with complex object models as well and found no issue in it. I think every developer should give it a try and focus on something challenging instead of writing boring and repetitive code of SQL and .Net DAL.
Here is the list of supported databases by Telerik Data Access:-
- Microsoft SQL Azure
- Microsoft SQL Server 2000, 2005, 2008, 2012, 2014
- Microsoft SQL Server 2005, 2008, 2012, 2014 Express, LocalDB
- Microsoft SQL Server Compact 3.5, 4.x
- Oracle Server 10.x, 11.x
- Oracle Database 10g, 11g Express Edition
- MySQL 5.x
- Maria DB 10.0 Alpha
- SQLite
- PostgreSQL 9.1
- Firebird 2.x
- Advantage Database Server 9.1, 10.1
- Sybase SQL Anywhere 11.x, 12.x
- VistaDB
To download the complete package with UI you have to create
a free account on Telerik website i.e. http://www.telerik.com/.
Alternatively you can download the product as Nuget package as well but it won’t
include a Visual Studio Extension which is very handy. So, once you created
your Telerik account go to Product & Subscription à Free Products and then
download Data Access Free.
The installation is quite simple and you just have to click next,
next, next buttonsJ,
so I am not including those details in this article. After installation you
should be able to see Telerik Menu in your visual Studio.
I have created a table in Sql Server database. Table creation script is as following:-
USE [Gnp]
go
/****** Object: Table [dbo].[UserManagment] Script Date: 8/6/2015 3:50:43 PM ******/
SET ansi_nulls ON
go
SET quoted_identifier ON
go
SET ansi_padding ON
go
CREATE TABLE [dbo].[usermanagment]
(
[id] [INT] IDENTITY(1, 1) NOT NULL,
[username] [VARCHAR](255) NULL,
[password] [VARCHAR](512) NULL,
[active] [INT] NULL,
CONSTRAINT [PK_UserManagment] 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
SET ansi_padding OFF
go
go
/****** Object: Table [dbo].[UserManagment] Script Date: 8/6/2015 3:50:43 PM ******/
SET ansi_nulls ON
go
SET quoted_identifier ON
go
SET ansi_padding ON
go
CREATE TABLE [dbo].[usermanagment]
(
[id] [INT] IDENTITY(1, 1) NOT NULL,
[username] [VARCHAR](255) NULL,
[password] [VARCHAR](512) NULL,
[active] [INT] NULL,
CONSTRAINT [PK_UserManagment] 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
SET ansi_padding OFF
go
Now create a console application to create a user management data access layer. First create a new Console Application.
Then add a new model by clicking on TelerikàTelerik Data Access àAdd Domain Model ...
Select Populate from database and click next.
In next interface define your connection string, I have created a database in sql server named GnP and executed the script which I mentioned earlier to create UserManagment Table. Select the table from list.
And just click next and then Finish.
Now you can see the model created against UserManagment table.
Now generally I create DTO (Data Transfer objects) to communicate between layers. So add a class named UserManagmentDTO as mentioned below:-
1: public class UserManagmentDTO
2: {
3: public int Id { get; set; }
4:
5: public string UserName { get; set; }
6:
7: public string Password { get; set; }
8:
9: public bool Active { get; set; }
10:
11: public override string ToString()
12: {
13: return "{Id=" + Id + ", UserName=" + UserName + ", Password=" + Password + ", Active=" + Active + "}";
14: }
15: }
Finally I have created the DAL (Data Access Layer) for my UserManagment table as mentioned below:-
1: public class UserManagmentDAL
2: {
3: /// <summary>
4: /// Gets the user.
5: /// </summary>
6: /// <param name="userId">The user identifier.</param>
7: /// <returns>UserManagmentDTO.</returns>
8: public UserManagmentDTO GetUserById(int userId)
9: {
10: using (var context = new EntitiesModel())
11: {
12: var user = (from record in context.UserManagments
13: where record.Id == userId
14: select
15: new UserManagmentDTO
16: {
17: Active = record.Active.HasValue && (record.Active.Value != 0),
18: Id = record.Id,
19: Password = record.Password,
20: UserName = record.UserName
21: }).FirstOrDefault();
22: return user;
23: }
24: }
25:
26: public UserManagmentDTO GetUserByName(string userName)
27: {
28: using (var context = new EntitiesModel())
29: {
30: var user = (from record in context.UserManagments
31: where record.UserName == userName
32: select
33: new UserManagmentDTO
34: {
35: Active = record.Active.HasValue && (record.Active.Value != 0),
36: Id = record.Id,
37: Password = record.Password,
38: UserName = record.UserName
39: }).FirstOrDefault();
40: return user;
41: }
42: }
43:
44: /// <summary>
45: /// Updates the user.
46: /// </summary>
47: /// <param name="user">The user.</param>
48: /// <returns><c>true</c> if XXXX, <c>false</c> otherwise.</returns>
49: public bool UpdateUser(UserManagmentDTO user)
50: {
51: var result = false;
52: using (var context = new EntitiesModel())
53: {
54: var entity = context.UserManagments.FirstOrDefault(x => x.Id == user.Id);
55: if (entity != null)
56: {
57: entity.Active = user.Active ? 1 : 0;
58: entity.Password = user.Password;
59: entity.UserName = user.UserName;
60: context.SaveChanges();
61: result = true;
62: }
63: }
64: return result;
65: }
66:
67: /// <summary>
68: /// Deletes the user.
69: /// </summary>
70: /// <param name="user">The user.</param>
71: /// <returns><c>true</c> if XXXX, <c>false</c> otherwise.</returns>
72: public bool DeleteUser(UserManagmentDTO user)
73: {
74: var result = false;
75: using (var context = new EntitiesModel())
76: {
77: var entity = context.UserManagments.FirstOrDefault(x => x.Id == user.Id);
78: if (entity != null)
79: {
80: context.Delete(entity);
81: context.SaveChanges();
82: result = true;
83: }
84: }
85: return result;
86: }
87:
88:
89: public bool AddUser(UserManagmentDTO user)
90: {
91: using (var context = new EntitiesModel())
92: {
93: var entity = new UserManagment
94: {
95: Active = user.Active ? 1 : 0,
96: Password = user.Password,
97: UserName = user.UserName
98: };
99: context.Add(entity);
100: context.SaveChanges();
101: }
102: return true;
103: }
104:
105:
106: /// <summary>
107: /// Gets all users.
108: /// </summary>
109: /// <returns>List<UserManagmentDTO>.</returns>
110: public List<UserManagmentDTO> GetAllUsers()
111: {
112: using (var context = new EntitiesModel())
113: {
114: var user = (from record in context.UserManagments
115: select
116: new UserManagmentDTO
117: {
118: Active = record.Active.HasValue && (record.Active.Value != 0),
119: Id = record.Id,
120: Password = record.Password,
121: UserName = record.UserName
122: }).ToList();
123: return user;
124: }
125: }
126: }
This class contains simple LINQ queries against which Telerik Data Access automatically generates SQL query and returns the result.
To test things I have called these functions in my main function, calling logic is as mentioned below:-
1: static void Main(string[] args)
2: {
3:
4: var dal = new UserManagmentDAL();
5: //Insert Test
6: var insertUser = new UserManagmentDTO {Active = true, Password = "123", UserName = "zeeshan.umar"};
7: dal.AddUser(insertUser);
8: var insertUserDb = dal.GetUserByName("zeeshan.umar");
9: if (insertUserDb != null)
10: {
11: Console.WriteLine("Insert sucessfull ...");
12: Console.WriteLine("User To Insert = " + insertUser);
13: Console.WriteLine("User from DB= " + insertUserDb);
14: Console.WriteLine("\r\n\r\r\n");
15: }
16:
17: var updateUser= dal.GetUserByName("zeeshan.umar");
18: updateUser.Password = "123456";
19: dal.UpdateUser(updateUser);
20: var updateUserDb = dal.GetUserByName("zeeshan.umar");
21:
22: if (updateUserDb.Password == "123456")
23: {
24: Console.WriteLine("Update sucessfull ...");
25: Console.WriteLine("User To Update = " + updateUser);
26: Console.WriteLine("User from DB= " + updateUserDb);
27: Console.WriteLine("\r\n\r\r\n");
28: }
29:
30: var deleteUser = dal.GetUserByName("zeeshan.umar");
31: dal.DeleteUser(deleteUser);
32: var deleteUserDb = dal.GetUserByName("zeeshan.umar");
33: if (deleteUserDb == null)
34: {
35: Console.WriteLine("Delete Sucessfull ...");
36: Console.WriteLine("User To Delete = " + deleteUser);
37: Console.WriteLine("User from DB= " + deleteUserDb);
38: Console.WriteLine("\r\n\r\r\n");
39: }
40: Console.ReadLine();
41: }
And as you run the project your output will be like this:-
Pretty simple, isn't. Feel free to comment in case of any query. Happy Codding !!!
