Telerik Data Access a Simple Way to create Data Access Layers for your application

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:-

  • 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 

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&lt;UserManagmentDTO&gt;.</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 !!!