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

Format Log File

In almost all projects we have to implement some kind of logging mechanism which facilitates us in finding the root cause of the issue and application health. However generally I believe that log files are not properly formatted due to which we need to spend more time in understanding our log files.

So, finally I have written a small class to format complex object into well formatted log file. Also it uses word wrap on columns of log to ensure readable format. Here is how log files look before:-


And here is how log look after properly formatted:-

Here is code which I used to format my log:-

   1:      public class LogFormatter 
   2:      {
   3:          private static string DefaultNameSpace = ConfigurationManager.AppSettings["Log.DefaultNameSpace"];
   4:          /// <summary>
   5:          /// Formats the object as string.
   6:          /// </summary>
   7:          /// <param name="obj">The obj.</param>
   8:          /// <returns></returns>
   9:          private static string FormatTypeAsString(object obj)
  10:          {
  11:              StringBuilder sb = new StringBuilder(1024);
  12:   
  13:              if (obj is string || obj is Exception || obj is DbCommand || obj is DateTime || obj is TimeSpan || obj == null)
  14:              {
  15:   
  16:              }
  17:              else
  18:              {
  19:                  sb.Append("[Type:" + obj.GetType().ToString() + "]");
  20:              }
  21:   
  22:              if (obj == null)
  23:              {
  24:                  sb.Append("Value: Null");
  25:              }
  26:              else if (obj is string)
  27:              {
  28:                  sb.Append((string)obj);
  29:              }
  30:              else if (obj is DateTime)
  31:              {
  32:                  sb.Append(((DateTime)obj).ToString("dd-MMM-yyyy hh:mm:ss tt"));
  33:              }
  34:              else if (obj is TimeSpan)
  35:              {
  36:                  sb.Append(((TimeSpan)obj).FormatAsString());
  37:              }
  38:              else if (obj is DbCommand)
  39:              {
  40:                  DbCommand cmd = (DbCommand)obj;
  41:                  string commandDetail = string.Empty;
  42:                  commandDetail = cmd.CommandType.ToString();
  43:   
  44:                  if (cmd.Connection != null)
  45:                  {
  46:                      commandDetail += "(" + cmd.Connection.DataSource + ")";
  47:                  }
  48:   
  49:                  sb.Append(" CommandText=[" + cmd.CommandText + "] CommandDetail=[" + commandDetail + "] Parameters= [");
  50:   
  51:                  foreach (DbParameter param in cmd.Parameters)
  52:                  {
  53:                      string paramValue = "null";
  54:                      if (param.Value != null)
  55:                      {
  56:                          paramValue = param.Value.ToString();
  57:                      }
  58:   
  59:                      sb.Append("@" + param.ParameterName + "=" + paramValue + " ");
  60:                  }
  61:                  sb.Append("]");
  62:              }
  63:              else if (obj is Exception)
  64:              {
  65:                  sb.Append(" Exception=[" + obj.ToString().Replace("\r", "").Replace("\n", "\t") + "] Details=[" +
  66:                  ((Exception)obj).StackTrace.Replace("\r", "").Replace("\n", "\t") + "]");
  67:              }
  68:              else
  69:              {
  70:                  var type = obj.GetType();
  71:   
  72:                  foreach (var prop in type.GetProperties())
  73:                  {
  74:                      var val = prop.GetValue(obj, new object[] { });
  75:   
  76:                      var valStr = string.Empty;
  77:                      if (val != null)
  78:                      {
  79:                          if (prop.PropertyType == typeof(DateTime))
  80:                          {
  81:                              valStr = ((DateTime)val).ToString("dd-MMM-yyyy hh:mm:ss tt");
  82:                          }
  83:                          else if (prop.PropertyType == typeof(TimeSpan))
  84:                          {
  85:                              valStr = ((TimeSpan)val).FormatAsString();
  86:                          }
  87:                          else
  88:                          {
  89:                              valStr = val.ToString();
  90:                          }
  91:                          sb.Append("[" + prop.Name + ":" + valStr + "]");
  92:                      }
  93:                  }
  94:              }
  95:              return sb.ToString();
  96:          }
  97:   
  98:          /// <summary>
  99:          /// Formats the log.
 100:          /// </summary>
 101:          /// <param name="eventName">Name of the event.</param>
 102:          /// <param name="details">The details.</param>
 103:          /// <returns></returns>
 104:          public string FormatLog(string eventName, object details, DateTime logDate)
 105:          {
 106:              int eventLength = 30;
 107:              int methodLength = 60;
 108:              int typeDetailLength = 80;
 109:              List<string> eventList = WordWrap(eventName, eventLength);
 110:              List<string> methodList = WordWrap(GetMethodNameFromStackTrace(), methodLength);
 111:              List<string> typeDetailList = WordWrap(FormatTypeAsString(details), typeDetailLength);
 112:   
 113:              StringBuilder sb = new StringBuilder();
 114:   
 115:              int maxCount = eventList.Count;
 116:              if (methodList.Count > maxCount)
 117:              {
 118:                  maxCount = methodList.Count;
 119:              }
 120:              else if (typeDetailList.Count > maxCount)
 121:              {
 122:                  maxCount = typeDetailList.Count;
 123:              }
 124:   
 125:              for (int i = 0; i < maxCount; i++)
 126:              {
 127:                  sb.Append("\r\n");
 128:   
 129:                  string line = string.Format("{0,-23}|{1,-" + eventLength + "}|{2,-" + methodLength + "}|{3,-" + typeDetailLength + "}|",
 130:                      (i == 0) ? logDate.ToString("dd-MMM-yyy hh:mm:ss tt") : string.Empty,
 131:                      GetValueFromList(eventLength, eventList, i),
 132:                      GetValueFromList(methodLength, methodList, i),
 133:                      GetValueFromList(typeDetailLength, typeDetailList, i));
 134:   
 135:                  sb.Append(line);
 136:              }
 137:   
 138:              return sb.ToString();
 139:          }
 140:   
 141:          /// <summary>
 142:          /// Gets the value from list.
 143:          /// </summary>
 144:          /// <param name="maxLength">Length of the max.</param>
 145:          /// <param name="list">The list.</param>
 146:          /// <param name="index">The index.</param>
 147:          /// <returns></returns>
 148:          private string GetValueFromList(int maxLength, List<string> list, int index)
 149:          {
 150:              string result = string.Empty;
 151:              if (index < list.Count)
 152:              {
 153:                  result = list[index];
 154:              }
 155:              else
 156:              {
 157:                  result = new string(' ', maxLength);
 158:              }
 159:              return result;
 160:          }
 161:   
 162:          /// <summary>
 163:          /// Words the wrap.
 164:          /// </summary>
 165:          /// <param name="text">The text.</param>
 166:          /// <param name="width">The width.</param>
 167:          /// <returns></returns>
 168:          private List<string> WordWrap(string text, int width)
 169:          {
 170:              List<string> list = new List<string>();
 171:              StringBuilder sb = new StringBuilder();
 172:   
 173:              if (text.Length < width)
 174:              {
 175:                  list.Add(text);
 176:              }
 177:              else
 178:              {
 179:   
 180:                  for (int i = 0; i < text.Length; i++)
 181:                  {
 182:                      if (i % width == 0 && i != 0)
 183:                      {
 184:                          list.Add(sb.ToString());
 185:                          sb = new StringBuilder();
 186:                      }
 187:   
 188:                      sb.Append(text[i]);
 189:                  }
 190:                  list.Add(sb.ToString());
 191:              }
 192:              return list;
 193:          }
 194:   
 195:          /// <summary>
 196:          /// Gets the name of the method from StackTrace.
 197:          /// </summary>
 198:          /// <returns></returns>
 199:          private string GetMethodNameFromStackTrace()
 200:          {
 201:              try
 202:              {
 203:                  var method = new StackFrame(4, true).GetMethod();
 204:                  string methodName = method.DeclaringType.ToString() + "." + method.Name + "()";
 205:                  methodName = methodName.Replace(DefaultNameSpace, "");
 206:                  return methodName;
 207:              }
 208:              catch //(Exception ex)
 209:              {
 210:                  //Suppress Exception
 211:              }
 212:              return string.Empty;
 213:          }
 214:      }

Happy Coding !!!

Download Image from URL with c#

Recently I came across a situation where I need to download Images from a web site. Here is a tiny little code through which I have done that. I am sharing it for your reference.
/// 
/// Downloads the image from URL.
/// 
/// "imageUrl">The image URL.
/// "imagePath">The image path.
public void  DownloadImageFromUrl(string imageUrl,string imagePath)
{
    try
    {
        System.Net.HttpWebRequest httpWebRequest = (System.Net.HttpWebRequest)System.Net.HttpWebRequest.Create(imageUrl);
        httpWebRequest.AllowWriteStreamBuffering = true;
        httpWebRequest.Timeout = 30000;
 
        using (System.Net.WebResponse webResponse = httpWebRequest.GetResponse())
        {
            System.IO.Stream stream = webResponse.GetResponseStream();
            System.Drawing.Image image = System.Drawing.Image.FromStream(stream);
            image.Save(imagePath);
        }
    }
    catch (Exception ex)
    {
        //Do some Exception Handling There
    } 
}
Here is how you can call above function
string downloadPath = "d:\\test.jpg";
DownloadImageFromUrl("http://test.com/test/test.ashx?ImageID=" + x + ".jpg", downloadPath); 
Happy Coding !!!

Random Password Generator with optional combination of Lower, Upper, Special and Numeric characters

This is been quite some time since I have shared some useful stuff with community. As you all know priorities always keep changing. So here is another simple utility class to generate random password.

You can define the minimum numbers of characters in your password string as well. This can be very useful if you have strict requirements for password security. As many password policy restrict user to set a password with sufficient complexity so that no one can brute force it easily.

This class uses double randomization algorithm to ensure that passwords don't get repeated. First it randomly picks the characters into a buffer. Once done, it does random swapping of all elements on buffer to ensure randomness of password.
Here is how you can utilize this code:-
/*
    * Author: Zeeshan Muar
    * Version: 1.0
    * This program is free software: you can redistribute it and/or modify
    * it under the terms of the GNU General Public License as published by
    * the Free Software Foundation, either version 3 of the License, or
    * (at your option) any later version.
    * 
    * This program is distributed in the hope that it will be useful,
    * but WITHOUT ANY WARRANTY; without even the implied warranty of
    * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    * GNU General Public License for more details.
    * 
    * You should have received a copy of the GNU General Public License
    * along with this program.  If not, see .
*/
public sealed class PasswordEngine
{
    private static PasswordEngine engine = new PasswordEngine();
 
    public static PasswordEngine Default
    {
        get
        {
            return engine;
        }
    }
        
    private readonly Random rnd = new Random();
    private string[] allowedCharacters = new string[]
    {
        "abcdefghijklmnopqrstuvwxyz",
        "ABCDEFGHIJKLMNOPQRSTUVWXYZ",
        "1234567890",
        "!@#$%^&*()_"
    };
 
    public string Generate(int size)
    {
        return Generate(size, 0, 0, 0);
    }
 
    public string Generate(int size,  int minCapitalLetters, int minNumbers, int minSpecial)
    {
        //Parameter validation
        if (minCapitalLetters + minNumbers + minSpecial > size)
        {
            throw new Exception("Parameter size should be less than or equal to minCapitalLetters + minSmallLetters + minNumbers + minSpecial");
        }
 
 
        //Buffer for 
        char[] buffer = new char[size];
 
        int currentIndex = 0;
        //Fill Capital Letters
        currentIndex = FillBuffer(currentIndex, minCapitalLetters, 1, buffer);
        //Fill Numbers Characters
        currentIndex = FillBuffer(currentIndex, minNumbers, 2, buffer);
        //Fill Special Characters
        currentIndex = FillBuffer(currentIndex, minSpecial, 3, buffer);
 
        //Fill remaining buffer with small characters
        int minSmallLetters= size - (minCapitalLetters  + minNumbers + minSpecial);
        currentIndex = FillBuffer(currentIndex, minSmallLetters, 0, buffer);
 
        RandomizeBuffer(size, buffer);
 
        return new string(buffer);
    }
 
    private void RandomizeBuffer(int size, char[] buffer)
    {
        for (int i = 0; i < size; i++)
        {
            char source = buffer[i];
            int swapIndex = rnd.Next(size);
            buffer[i] = buffer[swapIndex];
            buffer[swapIndex] = source;
        }
    }
 
    private int FillBuffer(int startIndex, int count, int row, char[] buffer)
    {
        for (int i = 0; i < count; i++)
        {
            rnd.Next(3);
            int col = rnd.Next(allowedCharacters[row].Length);
 
            buffer[i+startIndex] = allowedCharacters[row][col];
        }
 
        return startIndex + count;
    }
}

Here is how you can call this class to generate random passwords:-
//This will generate a simple password
string password1 = PasswordEngine.Default.Generate(10);
 
//This will generate a password with 2 numbers, 2 special and 2 capital letters
string password2 = PasswordEngine.Default.Generate(10, 2, 2, 2);

Feel free to discuss, Happy Coding !!!

Add hyper link in Excel using EPPlus

Many People has asked me that how they can add hyper link to another sheet through EPPlus. This is bit tricky because ws.Cells[1,1].Hyperlink takes Uri object which is generally used for external references only (file or website).

So after doing bit RnD I have developed a function through which you can easily add hyperlinks within your sheets. Here is the function:-

/// 
/// Adds the hyper link.
/// 
/// "ws">The ws.
/// "source">The source.
/// "destination">The destination.
/// "displayText">The display text.
private static void AddHyperLink(ExcelWorksheet ws, ExcelRange source, ExcelRange destination, string displayText)
{
	source.Formula = "HYPERLINK(\"[\"&MID(CELL(\"filename\"),SEARCH(\"[\",CELL(\"filename\"))+1, SEARCH(\"]\",CELL(\"filename\"))-SEARCH(\"[\",CELL(\"filename\"))-1)&\"]" + destination.FullAddress + "\",\"" + displayText + "\")";
}
Here is how you can call above function to add hyperlinks in your cell:-

ExcelWorksheet ws = CreateSheet(p,"Sample Sheet");
ExcelWorksheet sheet2 = CreateSheet(p, "Sample Sheet 2");
sheet2.Cells[1, 1].Value = "Hyperlink Target";
AddHyperLink(ws, ws.Cells[13, 1], sheet2.Cells[1,1], "Sample Hyperlink");
Hopefully this tip will be useful for you, Happy Coding !!!