One Of The Methodologies To Write Clean, Maintainable & Extensible Software

One of the most important goals when writing a software is to make your code:

1. Clean
2. Maintainable: easy to be maintained, debugged and fixed when needed
3. Extensible: easy to be extended and added to when new requirements are introduced

So, the question now is how to achieve this in the most appropriate way without tending to increase code complexity and make it very hard to read and understand. Some may think that this is a quite easy task to be done and it doesn't need a hassle, if you are one of these, hang on for a while.

Don't get me wrong, I don't mean that it is an impossible goal to achieve or a so complex task to be carried out, all what I mean is that as far as you understand the main concept behind each aspect, as easy you will find it. So, don't just imitate, try to understand and get it well.

To imagine what we are talking about here, let's suppose the following:

1. You are required to write a software, let's say a windows forms application
2. With very simple user interface
3. The client should be able to use this application to carry out two tasks:
1. Get the details (ID, Name, Age) of an employee by searching for him by ID
2. Get the details (ID, Name, Age) of a list of employees by searching for them by Name
4. So, far it is so simple, right?
5. But, there is a main requirement, the system should be able to get these employees from SQL & Oracle databases one at each time
6. Also, the system user should be able to switch between databases at run-time through user interface
7. Also, it may be requested by client that the system supports an extra new database at any day and this feature should take as minimum cost (time and money) as possible

So, how can we achieve this by the easiest way keeping in mind the three main goals (clean, maintainable & extensible). This is what I am going to try to explain as simple as I can so please keep reading if you are interested.

To do so, we will try to develop the software described above but I will keep secondary things as simple as possible so that the main focus will be on the design concept and so that we are not confused by minor things.

I will break down the code into small blocks to make it easy to understand and spare our minds for the big bang parts :)

So, let's start ..........


Analysis:

1. We will write our application in a 3-tier architecture
2. The UI/Presentation layer will be called "MainProgram" as we will merge it with the driver application for simplicity
3. The Business Logic layer will be called "BLL" and it will be presented by a class called "BusinessLogicLayerManager" which is a manager class including all business related methods and implementations. This class is independent on the database type and it is oriented to the system business needs. It doesn't care if the main data storage back-end is SQL or Oracle database or even XML
4. The Data Access layer will be called "DAL". It should be presented by a manager class which includes all related methods and implementations for retrieving data from databases. This class is dependent on the main data storage back-end type as it is the one responsible for actual retrieving of data from this back-end. So, if the back-end is XML, the class should include some code to get employees from an XML file by parsing nodes and so on. On the other hand, if the back-end is SQL database, then this class should include some code to get employees from a SQL database by executing some SQL stored procedures, functions, routines and so on......
5. But, we know that according to the requirements our application should deal with more than one back-end type, as a start, it should deal with SQL and Oracle databases. Also, the application should be able to switch between them at run-time. How????!!!!! Let's wait till we reach this point
6. The system deals with only one entity which is "Employee" but we will add another one which is a collection of employees and it will be called "EmployeeCollection"
7. Also, we will need some helping classes to be shared between all layers, so to avoid circular references we will isolate these classes in a separate project called "CommonEntities"
8. So, let's start with the "CommonEntities" project which is a class library project including a class with this code inside
   

    using System;  
    using System.Collections.Generic;  
    using System.Linq;  
    using System.Text;  
    using System.Collections.ObjectModel;  
      
    namespace CommonEntities  
    {  
      public class Employee  
      {  
        int id;  
        public int Id  
        {  
          get { return id; }  
          set { id = value; }  
        }  
      
        string name;  
        public string Name  
        {  
          get { return name; }  
          set { name = value; }  
        }  
      
        int age;  
        public int Age  
        {  
          get { return age; }  
          set { age = value; }  
        }  
      }  
      
      public class EmployeeCollection : Collection<Employee>  
      {  
      }  
    }  
   

9.  As you see the "Employee" class is so simple and the "EmployeeCollection" class is just an inherited class from Collection<Employee>
10. Since we already know that the application should be able to switch between SQL and Oracle databases, then at some point we will need to choose from UI one of these two database types
11. So, we will need to retrieve the user input in an appropriate way and also encapsulate it in a strong typed way, so I thought we will need an "enum" with both choices. This enum is called "DALManagerType", don't get stuck at the name right now, we will get to it soon and you will understand why I named it so
12. So, we need to have another cs file including this enum, the code is as follows
    

     using System;  
     using System.Collections.Generic;  
     using System.Linq;  
     using System.Text;  
       
     namespace CommonEntities.Enums  
     {  
       public enum DALManagerType  
       {  
         SQL = 0,  
         Oracle = 1  
       }  
     }  
    

13. Now, according to the requirements, the application should be able to:
1. Get the details (ID, Name, Age) of an employee by searching for him by ID
2. Get the details (ID, Name, Age) of a list of employees by searching for them by Name
14. So, in order to achieve that, we need our business logic layer to include two methods:
1. A method which takes the id of an employee as an input parameter and returns this employee
2. A method which takes a name as an input parameter and returns all employees which have this name
15. The implementation of these two methods should be oriented to business and not related by any means to the back-end type. Actually, these two methods should be calling the corresponding ones in the data access layer and apply some few business manipulations on the results if needed, nothing less, nothing more
16. So, to achieve this, we will create a new class library project named "BLL" and add to it the methods as below
    

     using System;  
     using System.Collections.Generic;  
     using System.Linq;  
     using System.Text;  
     using DAL;  
     using CommonEntities.BaseClasses;  
     using CommonEntities.Enums;  
     using DAL.Factories;  
     using CommonEntities;  
       
     namespace BLL  
     {  
       public class BusinessLogicLayerManager  
       {  
         public Employee GetEmployeeById(int employeeId)  
         {  
             
         }  
         public EmployeeCollection GetEmployeesByName(string employeeName)  
         {  
             
         }  
       }  
     }
    

17.  Now, inside the two methods, we should call corresponding methods in the data access layer to actually get the employee(s) data. This leads us to the next point, the data access layer
18. The data access layer should also include two methods to do the same job as the ones in the "BLL" but this time the methods should actually retrieve data from the back-end according to its type (SQL, Oracle, XML, .....)
19. Here we have a problem, the system should support 2 types of back-ends (SQL & Oracle) at the same time with the ability to switch between them at run-time. We can do this by creating 2 classes for "DAL", one for SQL and the other for Oracle. Then, at run-time, when system user chooses from UI to use SQL, our "BLL" should use the SQL DAL class, and when he chooses to use Oracle, our "BLL" should use the Oracle DAL class
20. So, at this point, our "BLL" could be as follows
    

     using System;  
     using System.Collections.Generic;  
     using System.Linq;  
     using System.Text;  
     using DAL;  
     using CommonEntities.BaseClasses;  
     using CommonEntities.Enums;  
     using DAL.Factories;  
     using CommonEntities;  
       
     namespace BLL  
     {  
       public class BusinessLogicLayerManager  
       {  
         private DALManagerType dbType;  
       
         public BusinessLogicLayerManager(DALManagerType dALMngrType)  
         {  
           dbType = dALMngrType;  
         }  
         public Employee GetEmployeeById(int employeeId)  
         {  
           switch(dbType)  
           {  
             case DALManagerType.SQL  
               //use corresponding method in the SQL DAL class  
               break;  
             case DALManagerType.Oracle  
               //use corresponding method in the Oracle DAL class  
               break;  
           }  
         }  
         public EmployeeCollection GetEmployeesByName(string employeeName)  
         {  
           switch(dbType)  
           {  
             case DALManagerType.SQL  
               //use corresponding method in the SQL DAL class  
               break;  
             case DALManagerType.Oracle  
               //use corresponding method in the Oracle DAL class  
               break;  
           }  
         }  
       }  
     }  
    

21.  So now, each time the system user switches the DB type, a new BLL class is initialized using the constructor which takes the DB type enum as an input parameter. Then, each time you try to use the BLL methods, they will check the DB type and correspondingly decide which DAL class to use
22. This will work, but this is not the best way cause now the coupling between the BLL and DAL is very very strict. This is because:
1.  You have to check for the DB type everywhere inside the BLL which is not logical as it should be independent on the DB type
2. Any tiny change in the DAL classes structure will need a corresponding change in the BLL logic to match
3. Any added extra DB type will require changes in the BLL to support this new DB type
4. The code will be unstable due to duplicate logic all around the BLL (like the switch-case block)
23. For these reasons and more, we should somehow reduce the coupling between the BLL and DAL as far as we can
24. Also, the logic says that the BLL should not be dependent on the DB type and it should only be oriented to the business requirements. This doesn't mean that the BLL should deal with only one type of DAL, but the way it interacts with many DAL types should be the same
25. Does this ring any bells? Do you feel you already know or encountered this case when a module (class) only cares about what other module (class) offers not the way it offers it? If you knew that the answer is "Interfaces", you can go on, if not, I recommend you read Interfaces - The Concept first to be able to fully understand the coming part
26. So now I assume you already know the main concept behind interfaces but let's check why interfaces fits in our case:
1. The BLL class expects all DAL classes to provide 2 methods with the header definitions we already know
2. So, BLL wants to guarantee that all DAL classes will obey this requirement and accordingly, the BLL will be able to deal with all DAL classes blindly without any need to know their exact definitions (implementations)
3. This way, we will be preferring "abstraction" to "implementation"
4. So, the best way to do this is to define an interface which includes the headers of the 2 required methods and all DAL classes should implement this interface
5. Now, the BLL should only work with any class implementing this interface and the compiler will guarantee the rest, no room for errors or misunderstanding
27.  So, to implement this part using interfaces, we first need to create the interface, so in the "CommonEntities" class library project, we will add a cs file with this code inside
    

     using System;  
     using System.Collections.Generic;  
     using System.Linq;  
     using System.Text;  
       
     namespace CommonEntities.Interfaces  
     {  
       public interface IDALManager  
       {  
         Employee GetEmployeeById(int employeeId);  
         EmployeeCollection GetEmployeesByName(string employeeName);  
       }  
     }  
    

28. Now, we are ready to work on our DAL classes, but first, lets discuss something. We already said that each of our DAL classes should implement this interface and the BLL will deal with instances from this interface as a substitute for the real DAL classes instances. This is good but I think we can tune it up a bit
29. I think it will be more appropriate to introduce a layer in-between the interface and each DAL class. This layer will be an abstract class
30. Why will we do this? we will do this because the in-between abstract class will provide us with the ability to add common implementation between all DAL classes if we need to do so. May be in our case we don't need it cause the DAL classes are so simple with only 2 methods which can not by any way share common implementations, but I am just pointing out a note which could be useful in other cases
31. So, following the in-between layer approach:
1. We will have an abstract class
2. This abstract class (not the DAL classes) implements the interface
3. Each DAL class will inherit from this abstract class (instead of the interface)
4. The BLL class will operate using definition of the abstract class (not the interface)
32.  So, in the "CommonEntities" class library project, we will add a cs file with this code inside
    

     using System;  
     using System.Collections.Generic;  
     using System.Linq;  
     using System.Text;  
     using CommonEntities.Interfaces;  
       
     namespace CommonEntities.BaseClasses  
     {  
       public abstract class DALManagerBase : IDALManager  
       {  
         public abstract Employee GetEmployeeById(int employeeId);  
         public abstract EmployeeCollection GetEmployeesByName(string employeeName);  
       }  
     }  
    

33. Note that we marked the 2 methods in the abstract class as "abstract" cause we don't need to write any implementation right now cause there is no common implementation between child DAL classes for these two methods and each DAL class should decide its own implementations
34. So, now we have our abstract class which all DAL classes should inherit and the BLL class should use to reference the appropriate DAL class
35. Now we have to update the BLL class to use the new approach (abstract DAL class) rather than using the old one (concrete DAL classes). So, the code of the BLL will be as follows
    

     using System;  
     using System.Collections.Generic;  
     using System.Linq;  
     using System.Text;  
     using DAL;  
     using CommonEntities.BaseClasses;  
     using CommonEntities.Enums;  
     using DAL.Factories;  
     using CommonEntities;  
       
     namespace BLL  
     {  
       public class BusinessLogicLayerManager  
       {  
         private DALManagerBase dALMngr;  
       
         public BusinessLogicLayerManager(DALManagerType dALMngrType)  
         {  
           switch (dALMngrType)  
           {  
             case DALManagerType.SQL:  
               dALMngr = new SQLDataAccessLayerManager();  
               break;  
             case DALManagerType.Oracle:  
               dALMngr = new OracleDataAccessLayerManager();  
               break;  
           }  
         }  
         public Employee GetEmployeeById(int employeeId)  
         {  
           return dALMngr.GetEmployeeById(employeeId);  
         }  
         public EmployeeCollection GetEmployeesByName(string employeeName)  
         {  
           return dALMngr.GetEmployeesByName(employeeName);  
         }  
       }  
     }  
    

36. Now, the DAL implementation can be switched at run-time depending on the type of the DB used without too much coupling between the BLL and DAL except for the "switch-case" block. This is good, but believe  it or not, it could get better
37. We can also get rid of the switch-case block inside the BLL by moving it to a separate class which will be responsible for returning an appropriate DAL class corresponding to the DB type chosen at run-time
38. So, to do this, we will create a class in the DAL class library project which has only one method. This method takes the DB type enum and returns a corresponding DAL class to be passed to the BLL
39. So, add a new cs file to the "DAL" class library project with the code below
    

     using System;  
     using System.Collections.Generic;  
     using System.Linq;  
     using System.Text;  
     using CommonEntities.BaseClasses;  
     using CommonEntities.Enums;  
       
     namespace DAL.Factories  
     {  
       public static class DataAccessLayerManagerFactory  
       {  
         public static DALManagerBase GetDALManager(DALManagerType dALManagerType)  
         {  
           DALManagerBase result = null;  
       
           switch (dALManagerType)  
           {  
             case DALManagerType.SQL:  
               result = new SQLDataAccessLayerManager();  
               break;  
             case DALManagerType.Oracle:  
               result = new OracleDataAccessLayerManager();  
               break;  
           }  
       
           return result;  
         }  
       }  
     }  
    

40. This factory class is a static class with one static method cause we don't need to create an instance of it. Also, note that the return type of the method is the abstract DAL class not the interface or any of the concrete DAL classes. This is logic cause this method will be called from BLL which expects an instance of the abstract DAL class, not an interface or any concrete class
41. So, now, our BLL will be modified as follows
    

     using System;  
     using System.Collections.Generic;  
     using System.Linq;  
     using System.Text;  
     using DAL;  
     using CommonEntities.BaseClasses;  
     using CommonEntities.Enums;  
     using DAL.Factories;  
     using CommonEntities;  
       
     namespace BLL  
     {  
       public class BusinessLogicLayerManager  
       {  
         private DALManagerBase dALMngr;  
       
         public BusinessLogicLayerManager(DALManagerType dALMngrType)  
         {  
           dALMngr = DataAccessLayerManagerFactory.GetDALManager(dALMngrType);  
         }  
         public Employee GetEmployeeById(int employeeId)  
         {  
           return dALMngr.GetEmployeeById(employeeId);  
         }  
         public EmployeeCollection GetEmployeesByName(string employeeName)  
         {  
           return dALMngr.GetEmployeesByName(employeeName);  
         }  
       }  
     }  
    

42. As you can see, our BLL is now loosely coupled with DAL and doesn't depend on its implementation by any way. This enables us to apply any updates on DAL classes without the need to apply any updates on the BLL, but note that this statement is valid only when preserving the inheritance between the DAL classes and the abstract DAL class
43. I have one note to add here, did you ask yourself why we needed to add the factory class in the DAL class library project? We did so cause we need the BLL class to reference this class (because we used it to call the method to get the appropriate  DAL class instance). Also, the factory class itself needed to reference the DAL classes (to be able to return the appropriate instance between these DAL classes). So, the best place to define this factory class with avoiding circular reference is in the DAL namespace itself
44. Now, finally we are going to work on the DAL classes. We will create a class library project with the name "DAL"
45. Then create a cs file with the code below
    

     using System;  
     using System.Collections.Generic;  
     using System.Linq;  
     using System.Text;  
     using CommonEntities.BaseClasses;  
     using CommonEntities;  
       
     namespace DAL  
     {  
       public class SQLDataAccessLayerManager : DALManagerBase  
       {  
         public override Employee GetEmployeeById(int employeeId)  
         {  
           //should write here the actual implementation which  
           //gets an employee by id from SQL database  
       
           Employee dummyEmployee = new Employee();  
           dummyEmployee.Id = 1;  
           dummyEmployee.Name = "Ahmed from SQL";  
           dummyEmployee.Age = 27;  
       
           return dummyEmployee;  
         }  
         public override EmployeeCollection GetEmployeesByName(string employeeName)  
         {  
           //should write here the actual implementation which  
           //gets a collection of employees by name from SQL database  
       
           EmployeeCollection dummyEmployeeCollection = new EmployeeCollection();  
           dummyEmployeeCollection.Add(new Employee() { Id = 1, Name = "Ahmed 1 from SQL", Age = 27 });  
           dummyEmployeeCollection.Add(new Employee() { Id = 2, Name = "Ahmed 2 from SQL", Age = 27 });  
           dummyEmployeeCollection.Add(new Employee() { Id = 3, Name = "Ahmed 3 from SQL", Age = 27 });  
       
           return dummyEmployeeCollection;  
         }  
       }  
     }  
    

46. Then create another cs file with the code below
    

     using System;  
     using System.Collections.Generic;  
     using System.Linq;  
     using System.Text;  
     using CommonEntities.BaseClasses;  
     using CommonEntities;  
       
     namespace DAL  
     {  
       public class OracleDataAccessLayerManager : DALManagerBase  
       {  
         public override Employee GetEmployeeById(int employeeId)  
         {  
           //should write here the actual implementation which  
           //gets an employee by id from Oracle database  
       
           Employee dummyEmployee = new Employee();  
           dummyEmployee.Id = 1;  
           dummyEmployee.Name = "Ahmed from Oracle";  
           dummyEmployee.Age = 27;  
       
           return dummyEmployee;  
         }  
         public override EmployeeCollection GetEmployeesByName(string employeeName)  
         {  
           //should write here the actual implementation which  
           //gets a collection of employees by name from Oracle database  
       
           EmployeeCollection dummyEmployeeCollection = new EmployeeCollection();  
           dummyEmployeeCollection.Add(new Employee() { Id = 1, Name = "Ahmed 1 from Oracle", Age = 27 });  
           dummyEmployeeCollection.Add(new Employee() { Id = 2, Name = "Ahmed 2 from Oracle", Age = 27 });  
           dummyEmployeeCollection.Add(new Employee() { Id = 3, Name = "Ahmed 3 from Oracle", Age = 27 });  
       
           return dummyEmployeeCollection;  
         }  
       }  
     }  
    

47. Now, we have created two DAL classes, one for SQL DB and the other for Oracle DB. You will find that the implementation of the 2 methods in the 2 classes is dummy code which is not actually retrieving data from any databases, just returning some hard-coded employee and employee collection with the words SQL and Oracle in the details to distinguish between the source of data
48. I meant to make the implementation stupid simple so that the full focus is on the concept and methodology rather than complex business implementation
49. Now, we are going to build our driver application which will use our BLL to retrieve data and populate some UI controls
50. Create a windows forms application project with the name "MainProgram"
51. The code for the designer will be as follows
    

     namespace MainProgram  
     {  
       partial class Form1  
       {  
         /// <summary>  
         /// Required designer variable.  
         /// </summary>  
         private System.ComponentModel.IContainer components = null;  
       
         /// <summary>  
         /// Clean up any resources being used.  
         /// </summary>  
         /// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>  
         protected override void Dispose(bool disposing)  
         {  
           if (disposing && (components != null))  
           {  
             components.Dispose();  
           }  
           base.Dispose(disposing);  
         }  
       
         #region Windows Form Designer generated code  
       
         /// <summary>  
         /// Required method for Designer support - do not modify  
         /// the contents of this method with the code editor.  
         /// </summary>  
         private void InitializeComponent()  
         {  
           this.cmbDbType = new System.Windows.Forms.ComboBox();  
           this.btnGetEmployee = new System.Windows.Forms.Button();  
           this.label1 = new System.Windows.Forms.Label();  
           this.lblID = new System.Windows.Forms.Label();  
           this.lblName = new System.Windows.Forms.Label();  
           this.lblAge = new System.Windows.Forms.Label();  
           this.btnGetEmployees = new System.Windows.Forms.Button();  
           this.grdEmployees = new System.Windows.Forms.DataGridView();  
           ((System.ComponentModel.ISupportInitialize)(this.grdEmployees)).BeginInit();  
           this.SuspendLayout();  
           //   
           // cmbDbType  
           //   
           this.cmbDbType.DropDownStyle = System.Windows.Forms.ComboBoxStyle.DropDownList;  
           this.cmbDbType.FormattingEnabled = true;  
           this.cmbDbType.Items.AddRange(new object[] {  
           "SQL",  
           "Oracle"});  
           this.cmbDbType.Location = new System.Drawing.Point(64, 20);  
           this.cmbDbType.Name = "cmbDbType";  
           this.cmbDbType.Size = new System.Drawing.Size(121, 21);  
           this.cmbDbType.TabIndex = 0;  
           this.cmbDbType.SelectedIndexChanged += new System.EventHandler(this.cmbDbType_SelectedIndexChanged);  
           //   
           // btnGetEmployee  
           //   
           this.btnGetEmployee.Location = new System.Drawing.Point(12, 62);  
           this.btnGetEmployee.Name = "btnGetEmployee";  
           this.btnGetEmployee.Size = new System.Drawing.Size(184, 23);  
           this.btnGetEmployee.TabIndex = 1;  
           this.btnGetEmployee.Text = "Get Employee By ID";  
           this.btnGetEmployee.UseVisualStyleBackColor = true;  
           this.btnGetEmployee.Click += new System.EventHandler(this.btnGetEmployee_Click);  
           //   
           // label1  
           //   
           this.label1.AutoSize = true;  
           this.label1.Location = new System.Drawing.Point(12, 23);  
           this.label1.Name = "label1";  
           this.label1.Size = new System.Drawing.Size(47, 13);  
           this.label1.TabIndex = 2;  
           this.label1.Text = "DB Type";  
           //   
           // lblID  
           //   
           this.lblID.AutoSize = true;  
           this.lblID.Location = new System.Drawing.Point(12, 101);  
           this.lblID.Name = "lblID";  
           this.lblID.Size = new System.Drawing.Size(22, 13);  
           this.lblID.TabIndex = 3;  
           this.lblID.Text = "ID:";  
           //   
           // lblName  
           //   
           this.lblName.AutoSize = true;  
           this.lblName.Location = new System.Drawing.Point(133, 101);  
           this.lblName.Name = "lblName";  
           this.lblName.Size = new System.Drawing.Size(38, 13);  
           this.lblName.TabIndex = 4;  
           this.lblName.Text = "Name:";  
           //   
           // lblAge  
           //   
           this.lblAge.AutoSize = true;  
           this.lblAge.Location = new System.Drawing.Point(281, 101);  
           this.lblAge.Name = "lblAge";  
           this.lblAge.Size = new System.Drawing.Size(30, 13);  
           this.lblAge.TabIndex = 5;  
           this.lblAge.Text = "Age:";  
           //   
           // btnGetEmployees  
           //   
           this.btnGetEmployees.Location = new System.Drawing.Point(15, 138);  
           this.btnGetEmployees.Name = "btnGetEmployees";  
           this.btnGetEmployees.Size = new System.Drawing.Size(184, 23);  
           this.btnGetEmployees.TabIndex = 6;  
           this.btnGetEmployees.Text = "Get Employees By Name";  
           this.btnGetEmployees.UseVisualStyleBackColor = true;  
           this.btnGetEmployees.Click += new System.EventHandler(this.btnGetEmployees_Click);  
           //   
           // grdEmployees  
           //   
           this.grdEmployees.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize;  
           this.grdEmployees.Location = new System.Drawing.Point(15, 168);  
           this.grdEmployees.Name = "grdEmployees";  
           this.grdEmployees.Size = new System.Drawing.Size(354, 170);  
           this.grdEmployees.TabIndex = 7;  
           //   
           // Form1  
           //   
           this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);  
           this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;  
           this.ClientSize = new System.Drawing.Size(385, 350);  
           this.Controls.Add(this.grdEmployees);  
           this.Controls.Add(this.btnGetEmployees);  
           this.Controls.Add(this.lblAge);  
           this.Controls.Add(this.lblName);  
           this.Controls.Add(this.lblID);  
           this.Controls.Add(this.label1);  
           this.Controls.Add(this.btnGetEmployee);  
           this.Controls.Add(this.cmbDbType);  
           this.Name = "Form1";  
           this.Text = "Form1";  
           this.Load += new System.EventHandler(this.Form1_Load);  
           ((System.ComponentModel.ISupportInitialize)(this.grdEmployees)).EndInit();  
           this.ResumeLayout(false);  
           this.PerformLayout();  
       
         }  
       
         #endregion  
       
         private System.Windows.Forms.ComboBox cmbDbType;  
         private System.Windows.Forms.Button btnGetEmployee;  
         private System.Windows.Forms.Label label1;  
         private System.Windows.Forms.Label lblID;  
         private System.Windows.Forms.Label lblName;  
         private System.Windows.Forms.Label lblAge;  
         private System.Windows.Forms.Button btnGetEmployees;  
         private System.Windows.Forms.DataGridView grdEmployees;  
       }  
     }  
    

52. The form code behind is as follows
    

     using System;  
     using System.Collections.Generic;  
     using System.ComponentModel;  
     using System.Data;  
     using System.Drawing;  
     using System.Linq;  
     using System.Text;  
     using System.Windows.Forms;  
     using BLL;  
     using CommonEntities;  
       
     namespace MainProgram  
     {  
       public partial class Form1 : Form  
       {  
         public Form1()  
         {  
           InitializeComponent();  
         }  
       
         private BusinessLogicLayerManager bLLMngr;  
       
         private void Form1_Load(object sender, EventArgs e)  
         {  
           cmbDbType.SelectedIndex = 0;  
           bLLMngr = new BusinessLogicLayerManager(CommonEntities.Enums.DALManagerType.SQL);  
         }  
       
         private void btnGetEmployee_Click(object sender, EventArgs e)  
         {  
           Employee emp = bLLMngr.GetEmployeeById(264);  
           lblID.Text = emp.Id.ToString();  
           lblName.Text = emp.Name;  
           lblAge.Text = emp.Age.ToString();  
         }  
       
         private void btnGetEmployees_Click(object sender, EventArgs e)  
         {  
           EmployeeCollection empColl = bLLMngr.GetEmployeesByName("dummy string");  
           grdEmployees.DataSource = empColl;  
         }  
       
         private void cmbDbType_SelectedIndexChanged(object sender, EventArgs e)  
         {  
           switch (cmbDbType.SelectedIndex)  
           {  
             case 0:  
               bLLMngr = new BusinessLogicLayerManager(CommonEntities.Enums.DALManagerType.SQL);  
               break;  
             case 1:  
               bLLMngr = new BusinessLogicLayerManager(CommonEntities.Enums.DALManagerType.Oracle);  
               break;  
           }  
         }  
       }  
     }  
    

53. You will find the code so stupid simple. It just uses the BLL class passing to it the DB type which the system user chose from UI and you know the rest .........
54. So, running the program you will get this

 

Notes:

1. The design which we implemented in the form of (Interface <= Abstract Class <= Concrete Class) is actually a design pattern called "Bridge", so now smile :)
2. The design of moving the logic responsible for returning DAL instances based on the DB type to a factory class is actually a design pattern called "Factory", so, smile again :)
3. Using this technique is not only for DALs, it is used for many other cases where you need to have different implementations but reserving the main business line

Recommendations:

1. Read more about "Bridge" and "Factory" design patterns
2. Practice using this technique when needed and always avoid over design. It harms when you just apply what you know but not what you need
3. Always try to keep it stupid simple as far as you can

The code for this demo can be found here


Finally, wish you find this post useful :)

Read More

SQL Stored Procedures With Table-Valued Parameters(TVP) & Calling From C#

Have you ever wished that you can call a stored procedure passing a collection of data for batch processing to avoid multiple round trips? If yes, then I encourage you to read the rest of this post.

Table-Valued Parameters is a type which you can create in SQL. This type is defined in order to allow SQL to understand the structure of the collection you wish to pass to a stored procedure. It is a "strong-typed" approach.

So, lets jump into the details and see the magic in action.

Note:
The source code for this demo can be found on this link


Creating the DB structure required for the demo:
1. Run this script to create the required tables

• We have only one table called Employees
• Each record in this table has:
• ID ==> int ==> Identity
• Name ==> nvarchar(max)
• Age ==> int

 USE [TvpTestDB]  
 GO  
   
 SET ANSI_NULLS ON  
 GO  
   
 SET QUOTED_IDENTIFIER ON  
 GO  
   
 --Droping the employees table if the table already exists in the database  
 IF EXISTS  
 (  
  SELECT * FROM dbo.sysobjects  
  WHERE id = object_id(N'[dbo].[Employees]')  
      AND OBJECTPROPERTY(id, N'IsUserTable') = 1  
 )  
 DROP TABLE [dbo].[Employees]  
 GO  
   
 --Creating the employees table  
 CREATE TABLE [dbo].[Employees](  
     [Employee_ID] [int] IDENTITY(1,1) NOT NULL,  
     [Name] [nvarchar](max) NOT NULL,  
     [Age] [int] NOT NULL,  
  CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED   
 (  
     [Employee_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  

2. Run this script to create the TVPs

• TVP = Table-Valued Parameter
• TVP is a SQL type which you can create providing the suitable structure
• In our case, the TVP will take a table shape with the same structure as the Employee table
• But, we will not include the ID column because it will not be sent as a parameter because it is already an Identity column which will gets its value automatically
• In other cases, if the ID column is not an Identity column, you should include it in the TVP definition

 USE [TvpTestDB]  
 GO  
   
 --Creating Employees TVP  
 CREATE TYPE EmployeesTVP AS TABLE  
 (  
     [Name] [nvarchar](max) NOT NULL,  
     [Age] [int] NOT NULL  
 )  
 GO  

3. Run this script to create the SPs

• We will create only one stored procedure called "AddEmployees" which will be responsible for batch adding employees
• It takes a TVP as an input parameter but with the same structure of the Employees TVP we created in the previous step
• Since we created the TVP above as a type, SQL already knows all required info about this TVP and it is now a "strong-typed" entity which SQL can deal with
• So, the input parameter of the SP will be "EmployeesTVP"
• Also, all TVPs should be marked as READONLY and it will not work otherwise
• This also makes us notice that the TVP data couldn't be manipulated by anyway inside the SP
• The stored procedure only batch inserts into the Employees table the records existing into the EmployeeTVP passed to it as an input parameter

 USE [TvpTestDB]  
 GO  
   
 --Dropping the stored procedure "AddEmployees" if already exists in a database  
 IF EXISTS  
 (  
  SELECT * FROM dbo.sysobjects  
  WHERE id = object_id(N'[dbo].[AddEmployees]')  
      AND OBJECTPROPERTY(id, N'IsProcedure') = 1  
 )  
 DROP PROCEDURE [dbo].[AddEmployees]  
 GO  
   
 CREATE PROCEDURE AddEmployees  
 (  
     @BatchEmployees AS EmployeesTVP READONLY  
 )  
 AS  
 BEGIN  
     INSERT INTO Employees(Name, Age)  
     SELECT Name, Age FROM @BatchEmployees  
 END  

Calling the SP passing the TVP instance as an input parameter through SQL:
Run this script to:

• Declare a variable of type EmployeesTVP
• SQL already knows the structure of this TVP (table)
• Insert some employee data into this variable to use for batch inserting
• Call the stored procedure passing the EmployeeTVP variable

 USE [TvpTestDB]
 GO
 
 --Declaring a variable of TVP type "EmployeesTVP" to be used to hold the input batch employee data
 DECLARE @BatchEmployees AS EmployeesTVP
 
 --Inserting batch employee data in the @BatchEmployees variable
 INSERT INTO @BatchEmployees(Name, Age)
 VALUES ('Ahmed', 27)
 , ('Tarek', 12)
 , ('Hasan', 13)
 , ('Saleh', 14)
 , ('Selim', 15)
 
 --Clearing tables before inserting new records
 DELETE FROM Employees
 
 --Calling the stored procedure "AddEmployees" passing to it the batch data variable "BatchEmployees"
 EXEC AddEmployees @BatchEmployees
 GO

Now, when you check the "Employees" table, you will find that the records are inserted and that is a good thing :)

But, can we do the same using C# like calling a stored procedure and passing to it a collection of certain class or entity? Yes, you can.


Calling the stored procedure from C# passing a TVP as an input parameter:
To do so, you have 2 ways:

1. Creating a DataTable with matching structure to the one of the SQL stored procedure TVP and populating it with data
2. Preparing your own custom collection class to be sent as a TVP by implementing the "IEnumerable<SqlDataRecord>" interface

You can check the code below to understand how to achieve this using both ways. You will find the code simple and well documented.

 using System;  
 using System.Collections.Generic;  
 using System.Linq;  
 using System.Text;  
 using System.Data;  
 using System.Data.SqlClient;  
 using Microsoft.SqlServer.Server;  
   
 namespace TestTVP  
 {  
   class Program  
   {  
     static void Main(string[] args)  
     {  
       //Creating a datatable with the same structure of the TVP   
       DataTable BatchEmployee = new DataTable();  
       BatchEmployee.Columns.Add("Name", typeof(string));  
       BatchEmployee.Columns.Add("Age", typeof(int));  
   
       //Adding records into the datatable  
       BatchEmployee.Rows.Add(new object[] { "Ahmed1", 27 });  
       BatchEmployee.Rows.Add(new object[] { "Ahmed2", 28 });  
       BatchEmployee.Rows.Add(new object[] { "Ahmed3", 29 });  
       BatchEmployee.Rows.Add(new object[] { "Ahmed4", 30 });  
       BatchEmployee.Rows.Add(new object[] { "Ahmed5", 31 });  
   
       //Calling the stored procedure passing the datatable as a TVP  
       //Modify the connection string to work with your environment before running the code  
       using (var conn = new SqlConnection("Data Source=DEVMACHINE2;Initial Catalog=TvpTestDB;Integrated Security=True"))  
       {  
         conn.Open();  
         using (var cmd = new SqlCommand("AddEmployees", conn))  
         {  
           cmd.CommandType = CommandType.StoredProcedure;  
           var BatchEmployeeParameter = cmd.Parameters.AddWithValue("@BatchEmployees", BatchEmployee);  
           //This is a very important step  
           //You should set the SqlDbType property of the SqlParameter to "Structured"  
           BatchEmployeeParameter.SqlDbType = SqlDbType.Structured;  
           cmd.ExecuteNonQuery();  
         }  
         conn.Close();  
       }  
   
       //Creating instance of the EmployeeCollection and populate some employee data  
       EmployeeCollection employees = new EmployeeCollection();  
       employees.Add(new Employee() { Name = "Mohamed1", Age = 20 });  
       employees.Add(new Employee() { Name = "Mohamed2", Age = 21 });  
       employees.Add(new Employee() { Name = "Mohamed3", Age = 22 });  
       employees.Add(new Employee() { Name = "Mohamed4", Age = 23 });  
       employees.Add(new Employee() { Name = "Mohamed5", Age = 24 });  
       employees.Add(new Employee() { Name = "Mohamed6", Age = 25 });  
   
       //Calling the stored procedure passing the EmployeeCollection as a TVP  
       //To do so, you should first make sure that the EmployeeCollection implements the "IEnumerable<SqlDataRecord>" interface  
       //Check the EmployeeCollection class definition below  
       //Modify the connection string to work with your environment before running the code  
       using (var conn1 = new SqlConnection("Data Source=DEVMACHINE2;Initial Catalog=TvpTestDB;Integrated Security=True"))  
       {  
         conn1.Open();  
         using (var cmd = new SqlCommand("AddEmployees", conn1))  
         {  
           cmd.CommandType = CommandType.StoredProcedure;  
           var BatchEmployeeParameter = cmd.Parameters.AddWithValue("@BatchEmployees", employees);  
           //This is a very important step  
           //You should set the SqlDbType property of the SqlParameter to "Structured"  
           BatchEmployeeParameter.SqlDbType = SqlDbType.Structured;  
           cmd.ExecuteNonQuery();  
         }  
         conn1.Close();  
       }  
     }  
   }  
   
   //Employee class  
   public class Employee  
   {  
     int id;  
     public int Id  
     {  
       get { return id; }  
     }  
   
     string name;  
     public string Name  
     {  
       get { return name; }  
       set { name = value; }  
     }  
   
     int age;  
     public int Age  
     {  
       get { return age; }  
       set { age = value; }  
     }  
   }  
   
   //EmployeeCollection class  
   //For EmployeeCollection to be used as a TVP, it should implement the "IEnumerable<SqlDataRecord>" interface  
   public class EmployeeCollection : List<Employee>, IEnumerable<SqlDataRecord>  
   {  
     IEnumerator<SqlDataRecord> IEnumerable<SqlDataRecord>.GetEnumerator()  
     {  
       SqlDataRecord record = new SqlDataRecord  
         (  
           new SqlMetaData("Name", SqlDbType.Text),  
           new SqlMetaData("Age", SqlDbType.Int)  
         );  
   
       foreach(Employee emp in this)  
       {  
         record.SetSqlString(0, emp.Name);  
         record.SetSqlInt32(1, emp.Age);  
         yield return record;  
       }  
     }  
   }  
 }  
   

That's it, you can do magic with this technique :)

Read More

Dynamic Filtering Using Linq Query Self-Wrote Library

What you will find in this post:

1. The source code for the library
2. How to use the library
3. Main concept implemented through the library
4. Comparison operators currently supported by the library

What you will NOT find in this post:

1. Explanation of the source code of the library

Limitations on the library:

1. Needs system user input module to accept conditions in a certain form (discussed below in the main concept implemented)
2. Works only with data collections which implement the "IQueryable" interface

To jump to the point, imagine that you are working on a windows application or an ASP.Net application and you have a data set which you want your system user to filter dynamically. In other words, you want the system user to choose the columns he wish to filter by at run-time.

This means that at the moment of writing the code, you don't know the exact condition to filter your data set by. Some will think to implement every single probability so that they have to implement the functionality of filtering by first column, another one for filtering by second column and finally another one for filtering by both columns.

Ok, although I don't recommend, but, this may work for simple entities with so few columns (properties) but not for complex ones. Imagine if your data set has 10 columns and you wish all these 10 columns are valid to be used for filtering. To implement the same solution above you will have so much pain and for sure the probability of having mistakes and issues is so great. this is beside your code being so hard to be maintained and extended someday ... imagine a new requirement to add 2 more columns :)

So, now you have to think in a more innovative way to make your code generic, clean, easy to manage and extensible. That is what I tried to do here and I think I achieved something good :)


Main concept implemented:

• All conditions (and/or) can be transformed to a flat form however complex these conditions are
• Assume we have conditions A, B, C, D, E & F
• Also, assume that the anding is represented by "&" and the oring by "|"
• Then (A & B) ==> (A & B)
• (A | B) ==> (A) | (B)
• (A | (B & C)) ==> (A) | (B & C)
• (A & (B | C)) ==> (A & B) | (A & C)
• (A & (B | C)) & (D & (E | F))
• ==> [(A & B) | (A & C)] & [(D & E) | (D & F)]
• ==> [(A & B) & (D & E)] | [(A & B) & (D & F)] | [(A & C) & (D & E)] | [(A & C) & (D & F)]
• ==> (A & B & D & E) | (A & B & D & F) | (A & C & D & E) | (A & C & D & F)

So, from the examples above, you will understand that any condition however complex it is can be transformed into a flat form which is (groups, each group consists of anded conditions, these groups are ored together). This concept is pure logic and mathematically derived.

This concept is already used in SQL management studio. If you write a view with some complex conditions, then you right-click on the view and choose to script it as create, you will find that the view in the generated script is implemented in a different way cause management studio has flattened the conditions of the view to be optimized for performance.

Anyway, this is the main concept used in this library and this will require the input of the library to be in a similar form.


How the library is implemented:

1. The library is implemented in the form of extension methods for the "IQueryable" interface. These extension methods will provide:
1. An extra overload for the "Where" method which takes an "OringGroup" instance as input
2. The new "WhereNot" method which takes an "OringGroup" instance as input
2. The library provides the definition of the "OringGroup" class which is a collection of the "AndingGroup" class
3. The library provides the definition of the "AndingGroup" class which is a collection of the "AndingItem" class
4. The library provides the definition of the "AndingItem" class which holds the main aspects of a condition
5. All items (AndingItem) in the "AndingGroup" class instance will be anded with each other
6. All items (AndingGroup) in the "OringGroup" class instance will be ored with each other

Comparison operators currently supported by the library:

1. Equal
2. NotEqual
3. GreaterThan
4. GreaterThanOrEqual
5. LessThan
6. LessThanOrEqual
7. RegexMatch
8. Contains

Can we add more comparison operators to the library?
Yes


How to use the library:

1. Add reference to the library project/DLL
2. Add the "using DynamicLinqExtensionMethodsNS;" to the using section
3. Follow the example below:
4. This is a dummy entity to use in a collection
   

    public class Order  
      {  
        string text;  
        public string Text  
        {  
          get { return text; }  
          set { text = value; }  
        }  
      
        int index;  
        public int Index  
        {  
          get { return index; }  
          set { index = value; }  
        }  
      
        public Order()  
        {  
      
        }  
      
        public Order(string _text, int _index)  
        {  
          Text = _text;  
          Index = _index;  
        }  
      }  
   

5. Creating a list of this dummy entity to use as the main data set to be filtered at run-time
   

    List<Order> lst = new List<Order>();  
                lst.Add(new Order("a",1));  
                lst.Add(new Order("b",4));  
                lst.Add(new Order("a",5));  
                lst.Add(new Order("a",5));  
                lst.Add(new Order("c",7));  
                lst.Add(new Order("d",9));  
                lst.Add(new Order("a",14));  
                lst.Add(new Order("a",2));  
                lst.Add(new Order("a",5));  
                lst.Add(new Order("ahmed tarek hasan", 0));  
                lst.Add(new Order("mohamed emad eldin", -1));
   

6. Creating "OringGroup" instance and populating it with the conditions to be used for filtering
   

    OringGroup org = new OringGroup();  
      
    AndingGroup ang1 = new AndingGroup();  
    ang1.Add(new AndingItem(){ PropertyName = "Text", Value = "a", ComparisonOperator = OperatorType.Equal});  
    ang1.Add(new AndingItem(){ PropertyName = "Index", Value = 5, ComparisonOperator = OperatorType.Equal});  
      
    AndingGroup ang2 = new AndingGroup();  
    ang2.Add(new AndingItem() { PropertyName = "Text", Value = "b", ComparisonOperator = OperatorType.Equal });  
    ang2.Add(new AndingItem() { PropertyName = "Index", Value = 4, ComparisonOperator = OperatorType.Equal });  
      
    AndingGroup ang3 = new AndingGroup();  
    ang3.Add(new AndingItem() { PropertyName = "Index", Value = 7, ComparisonOperator = OperatorType.GreaterThanOrEqual });  
      
    AndingGroup ang4 = new AndingGroup();  
    ang4.Add(new AndingItem() { PropertyName = "Text", Value = "tarek", ComparisonOperator = OperatorType.RegexMatch });  
      
    AndingGroup ang5 = new AndingGroup();  
    ang5.Add(new AndingItem() { PropertyName = "Text", Value = "emad", ComparisonOperator = OperatorType.Contains });  
      
    org.Add(ang1);  
    org.Add(ang2);  
    org.Add(ang3);  
    org.Add(ang4);  
    org.Add(ang5);  
   

7. Using the library in filtering
   

    var t = lst.AsQueryable().Where(org).ToList();  
    //var t = lst.AsQueryable().WhereNot(org).ToList();  
    dataGridView1.DataSource = t;  
   

8. Watch the results and say "Yesssssssssssssssss" :)

How will this work in real system:

1. Build your system UI to collect conditions (columns to filter by, comparison operators & comparison values)
2. Create the "AndingItems" by setting:
1. PropertyName ==> the name of the "property" in the entity you are filtering on
2. Value ==> the value to be compared to
3. ComparisonOperator ==> the comparison operator to be used for the filtering
3. Create an "AndingGroup" for each set of "AndingItems" which will be anded together at run-time condition evaluation
4. Create an "OringGroup" and add to it all "AndingGroup" instances created in the previous step
5. Now, if your collection implements the "IQueryable" interface, you will find the extra overload of the "Where" which takes "OringGroup" as input. Also, you will find the new "WhereNot" method which also takes "OringGroup" as input
6. When the "Where" or "WhereNot" method is executed, the library constructs the full Linq condition then the filtering is triggered at once, not at round trips for each condition

Can I redistribute this library:
Sure you can but please try to mention the main source (this blog).


Where is the download link for the library source code:
You can download the library source code from here



Finally, I hope you find this library helpful and for sure all your comments are welcomed :)

Read More

How To Get List Of SQL SPs/Views/Functions/Triggers Containing Certain Word/Pattern

Sometimes you find yourself in a situation where you need to know where a certain word (which may be a name for a column or just a string message) exists in your SQL database code items like stored procedures or views or functions or triggers.

In such situation, you will need a search feature which can aid you to find the word you are looking for.

I know that you are not supposed to face such case because there should be a proper sort of documentation for the database design. But, believe me in some cases this type of documentation is not found and you are asked to work on the database to fix some bugs or whatever.

So, to do your job, you may need to find a certain word in the database. Why? because this word may be a key word for your whole issue.

To imagine what I am talking about here, let's have a look on these cases:

• Want to know where a certain column in a table is updated
• Want to know where a certain stored procedure is called
• Want to know where data is inserted into a certain table
• Want to know where a certain table is used to retrieve data from
• Want to know where a certain view is used to retrieve data from

These are just some examples and there are many others. So, to carry out these tasks, you need to search the whole database items for a word or a pattern to find the database items which include this word or pattern into its code definition. Then, you can work on the analysis of the results you got.


So, I wrote a SQL script which carries out this search functionality. The script is as below:

 use [DbName] --Database name
GO

DECLARE @SearchText varchar(max)
SET @SearchText='KeyWord' --The (word)/(like pattern) to search for

SELECT DISTINCT
'[' + s.[name] + '].[' + obj.name + ']' AS [ObjectName]
, obj.type_desc AS [Object Type]
, module.definition AS [Code]
, obj.create_date AS [Date Created]
, obj.modify_date AS [Date Modified]

FROM
sys.sql_modules module

INNER JOIN sys.objects obj
ON module.object_id = obj.object_id

INNER JOIN sys.schemas s
ON s.schema_id = obj.schema_id

WHERE module.definition Like '%' + @SearchText + '%'
   
GROUP BY obj.type_desc
, '[' + s.[name] + '].[' + obj.name + ']'
, module.definition
, obj.create_date
, obj.modify_date

ORDER BY obj.type_desc
, '[' + s.[name] + '].[' + obj.name + ']'
, module.definition
, obj.create_date
, obj.modify_date

To use the script, you just need to update the database name in the "use" statement (first line) and the word or the like pattern you wish to search for (fifth line).


Wish this helps you someday :)

Read More

How To Call C# Methods In SQL!!!!

Have you ever needed to use regular expressions in a SQL SELECT statement?

Me myself, I have. One day I needed to write a SQL script to fix a data issue on the production environment DB. Fixing this issue required to write a complex script which will carry out complex logic including comparing some columns data values with other using some patterns. It was a nightmare for me and some of my team members to write such script especially that SQL doesn't heavily support such operations.

So, I dreamed that I can use regular expressions in my SQL UPDATE statement, this will spare my life. But, how?? SQL doesn't know regular expressions.

So, i decided to search for any alternatives and I found it. You can write C# code and fire it from SQL!!!

I couldn't believe my eyes, this means that the problem is now fixed and now I can do magic. I freed my mind a bit and tried to think of what I can do with such thing. Here are some of the ideas i got in mind then, i can write C# methods which can:

1. Carry out complex string manipulations
2. Sending mails
3. Dealing with NTFS (Note: requires some permissions and policies)
4. Calling web services (WOW!!!)

And many other wild ideas. So, now let's see how we can do this using a simple example with two functions, one to just return "Hello " followed by a name which is passed to the method, and the other replaces a string with another using regular expressions.

So, to achieve this, we need to follow these steps:

1. Open visual studio
2. Create a class library
3. Change the name of the class to "CLRFunctions"
4. Change the namespace to "SQLServerClrNS" (Note: make sure the name is updated in the project properties not just in the cs file)
5. Add "using System.Text.RegularExpressions;" to the using block
6. Now you should write the two methods so that the code will look like that
7. 
   

    using System;  
    using System.Collections.Generic;  
    using System.Linq;  
    using System.Text;  
    using System.Text.RegularExpressions;  
      
    namespace SQLServerClrNS  
    {
      public class CLRFunctions  
      {  
        public static string HelloWorld(string name)  
        {  
          return "Hello " + name;  
        }  
      
        public static string ReplaceWithRegExp(string SourceStr, string RegExp, string ReplacementStr)  
        {  
          return Regex.Replace(SourceStr, RegExp, ReplacementStr);  
        }  
      }  
    }  
   

8. Build the solution and locate the generated DLL file
9. The DLL file name should be "SQLServerCLR.dll"
10. Copy "SQLServerCLR.dll" to a folder on the SQL server, for the sake of this tutorial, copy it to "C:\" so that the file "C:\SQLServerCLR.dll" is found on the SQL server
11. Open "Microsoft SQL Server Management Studio" on the SQL server
12. Run the script below after updating the database name on the first line
13. 
    

     USE [TestDB]  
     GO  
       
     --Turning on CLR functionality  
     --By default, CLR is disabled in SQL Server so to turn it on  
     --we need to run this command against our database  
     EXEC sp_configure 'clr enabled', 1  
     GO  
     RECONFIGURE  
     GO  
       
     --Checking if the SQL assesmbly we want to create already exists  
     --If exists, drop it to recreate  
     IF EXISTS  
     (  
             SELECT *  
             FROM sys.assemblies  
             WHERE name = 'SQLServerCLRAssembly'  
     )  
     BEGIN  
             -- To drop the SQL assembly, we must first drop all functions  
             -- which reference this SQL assembly  
             IF EXISTS  
             (  
         SELECT *  
         FROM INFORMATION_SCHEMA.ROUTINES  
         WHERE ROUTINE_NAME = 'HelloWorld'  
         AND ROUTINE_SCHEMA = 'dbo'  
         AND ROUTINE_TYPE = 'FUNCTION'  
             )  
             DROP FUNCTION [dbo].HelloWorld  
       
             IF EXISTS  
             (  
         SELECT *  
         FROM INFORMATION_SCHEMA.ROUTINES  
         WHERE ROUTINE_NAME = 'ReplaceWithRegExp'  
         AND ROUTINE_SCHEMA = 'dbo'  
         AND ROUTINE_TYPE = 'FUNCTION'  
             )  
             DROP FUNCTION [dbo].ReplaceWithRegExp  
       
             -- Dropping the SQL assmbly  
             DROP ASSEMBLY SQLServerCLRAssembly  
     END  
       
     -- Creating the SQL assembly and linking it to the C# library DLL we created  
     CREATE ASSEMBLY SQLServerCLRAssembly FROM 'C:\SQLServerCLR.dll'  
     GO  
       
     -- Creating SQL wraping functions to use the C# methods we created in the C# library  
     CREATE FUNCTION dbo.HelloWorld  
     (  
             @name as nvarchar(MAX)  
     )  
     RETURNS nvarchar(MAX)  
     AS EXTERNAL NAME SQLServerCLRAssembly.[SQLServerClrNS.CLRFunctions].HelloWorld  
     --SQLAssemblyName.[C#NameSpace".C#ClassName].C#MethodName  
     GO  
       
     CREATE FUNCTION dbo.ReplaceWithRegExp  
     (  
             @SourceStr as nvarchar(MAX)  
             , @RegExp as nvarchar(MAX)  
             , @ReplacementStr as nvarchar(MAX)  
     )  
     RETURNS nvarchar(MAX)  
     AS EXTERNAL NAME SQLServerCLRAssembly.[SQLServerClrNS.CLRFunctions].ReplaceWithRegExp  
     --SQLAssemblyName.[C#NameSpace".C#ClassName].C#MethodName  
     GO
    

14. Now, you are ready to run a test, so run the 2 lines below

     SELECT dbo.HelloWorld('Ahmed Tarek')  
     SELECT dbo.ReplaceWithRegExp('Ahmed Tarek', 'Tarek', 'Tarek Hasan')  
    

15. When you run the 2 lines above, you will get the results

     Hello Ahmed Tarek  
     Ahmed Tarek Hasan  
    

16.  This means that you have just ran your first C# method on SQL :)

Notes:

1. You should read the SQL script and you will find it well documented
2. You first create/define a SQL assembly which refers to the C# DLL you created
3. Then create SQL function to wrap a C# function in the DLL through the SQL assembly
4. Now, you are ready to call this SQL function wherever you need and SQL supports calling functions
5. I wrote the SQL script in a way that you don't need to make big changes in it when just updating the C# DLL file. In other words, I wrote some code to drop the SQL assembly and its depending functions if they already exist so that you can run the whole script more than once without worrying about SQL errors due to creating an already existing SQL assembly and functions
6. You can delete the DLL file from the path on the SQL server NTFS (in our example, "C:\SQLServerCLR.dll") after running the SQL script. It is not needed anymore

You can download the code from this link


I hope this approach will help you someday to carry out complex operations :)

Read More

Parent VS. Child Classes - Think Like A Compiler

Some developers get confused when they find that the method they are willing to call takes a class as an input parameter which is the parent/child of the class they already have in hand. They don't know what to do or even when they know, they don't know why it failed/succeeded.

I believe that the best way to understand similar topics is to try to think like the compiler. We all know that C# is a strong typed language. This means that the compiler will not allow any code unless it is sure that this code will not cause type mismatch or such things which cause run time errors. So, lets now try to think like a compiler.


Let's assume that:

• We have class "A"
• We have class "B" which is child of "A"
• "A" has a method with the header "public void f1()"
• Then "B" also has the same method with the header "public void f1()"
• Also, "B" has another method with the header "public void f2()"

So, now we have 4 cases to study:

1. Calling a method which takes "A" as an input parameter passing "A" to it
2. Calling a method which takes "A" as an input parameter passing "B" to it
3. Calling a method which takes "B" as an input parameter passing "A" to it
4. Calling a method which takes "B" as an input parameter passing "B" to it

As we can see, both cases 1 and 4 are straight forward and will not cause any problems so we don't really need to study them. But, we need to study the other two cases 2 and 3.


Calling a method which takes "A" as an input parameter passing "B" to it

• Let's assume we have a method with header "public void Validate(A input)"
• Inside the method implementation, the compiler expects the developer to write some code which can access the members of "input" which is an instance of class "A", not "B"
• This means that the compiler will allow the developer to write "input.f1()" but not "input.f2()" because class "A" doesn't have "f2()"
• So, when you call "Validate" sending it an instance of class "B", the compiler already knows that "B" is a child of "A"
• This means that the compiler is sure that whatever members of "A" the method "Validate" uses, it will also be available with "B". This acts as a contract which the compiler fully trusts
• So, the compiler will allow such behavior

Calling a method which takes "B" as an input parameter passing "A" to it

• Let's assume we have a method with header "public void Validate(B input)"
• Inside the method implementation, the compiler expects the developer to write some code which can access the members of "input" which is an instance of class "B" which is a child of "A"
• This means that the compiler will allow the developer to write "input.f1()" and "input.f2()" because class "B" have both of them
• So, when you call "Validate" sending it an instance of class "A", the compiler already knows that "B" is a child of "A" which means that "B" may have some members which are not found inside "A" and this could cause a problem
• This means that the compiler is not sure that whatever members of "B" the method "Validate" uses, it will also be available with "A"
• So, does the compiler need to wait to see if the method "Validate" uses any of the members of "B" which are not found in "A"????? ......... Does the compiler need to check if class "B" has extended members of "A" or they are just duplicate classes?????............ No, the compiler takes a restrictive decision here that the whole thing doesn't depend on the method implementation or the classes relative structure rather than applying a more concrete rule; strong typed rule
• So, the compiler will not allow such behavior

So, whenever you feel that you don't know if a certain approach can succeed compiler-wise, try to think as a compiler which always wants to make sure that the code will be safe and cause no run time errors.


Hope this approach helps you understand some basic rules.

Read More