• Download Geniuses Code Generting Tools - 125.1 KB
• Download GCFS.zip - 44.6 KB

Resources

• Geniuses Code Data Frame work with datareader Map ( video ).
• How to use Geniuses code Data framework Multiple data base ( video ) .
• Arabic Artical Part 1 ( Get start with Geniuses Code) . 
• Arabic Artical Part 2 ( Validation ) .
• Arabc Aritcal Part 3 ( Model and controller Generator ) .
• On Youtube.

Introduction

public List<Compnies> Get()
{
    List<Compnies> compnies = new List<Compnies>();
    using (var connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ToString()))
    {
        if(connection.State == System.Data.ConnectionState.Closed)
        {
            connection.Open();
        }

        using(var command = new SqlCommand("EOM_Companies_Get", connection))
        {
            command.CommandType = System.Data.CommandType.StoredProcedure;
            using (var reader = command.ExecuteReader())
            {
                while(reader.Read())
                {
                    compnies.Add(new Compnies()
                        {
                            ID = int.Parse(reader["ID"].ToString()),
                            NameEn = reader["NameEn"].ToString(),
                            Active = bool.Parse(reader["Active"].ToString())
                        });
                }
            }
        }
    }

    return compnies;
}

So what is the problem in this code at the top? It works well and implement all what you want to the fullest, the problem in this code it consumes a great effort of your time to do things far away entirely from the requirements of the application.

You must always have to deal with data base objects like Command, Connection and Data Reader in order to get the data and then begin processing that data. It is very difficult in maintenance, extremely long and repetitive.  In addition, reserved for a specific database, its SQL Server in that example at the top, in the future, if you want to operate your application on Oracle, you will need to change everything in order to do that.

Background

Now I believe that I reached what I want to talk about, my idea is very simple:  
I want to use regular ADO.net, but without working with all the above-mentioned steps.

I start work almost a year ago to build a DLL extension to do a lot with only one line of code; I will give you example, look at the following code:

        /// <summary>
        /// Get all Details by settlement
        /// </summary>
        /// <param name="settlement">settlement you want to get details for</param>
        /// <returns>a list of details</returns>
        public List<Details> Get(Settlement settlement)
        {
            return new Operations().ExecuteQueryToList<Details>("RI_Settlements_Details_Get_BySettlement", Operations.CommandType.StoredProcedure, new Parameter("Settlement", settlement.MojID));
        }

Yes, just one line doing the following steps: 

• Open a connection to the database (Oracle, MS SQL, Access, SQLite). 
• Create Command Object . 
• Passing parameter to the SP .
• Execute the query.
• Read the data Reader. 
• Unloaded the contents of the data Reader into a List or single object (thanks to Generic and reflection). 

Object is ready for action. 

Using the code

Frist install 

Nuget 

PM> Install-Packages GeniusesCode.Framework.Data 

Confugration 

There are there setting you need to added in the app setting : 

 

<configuration>
  <appSettings>
    <add key="AllowEncryption" value="true"/>
    <add key="EncryptionKey" value="mTXMwEuxoAgt1P69mTXMw6Agt1P69mTXMw6VcpAgt1PmTXMw69mTXMw6"/>    
    <add key="DbConnection" value="Data Source=.\SQLEXPRESS;Initial Catalog=RealestateIndicator;Integrated Security=True"/>
  </appSettings>

</configuration>

Once it install you can use it like that 

        /// <summary>
        /// Get all Details
        /// </summary>
        /// <returns>a list of all details</returns>
        public List<Details> Get()
        {
            return new Operations().ExecuteQueryToList<Details>("RI_Settlements_Details_Get");
        }

And you can pass parameters like that : 

        /// <summary>
        /// Get all Details by settlement
        /// </summary>
        /// <param name="settlement">settlement you want to get details for</param>
        /// <returns>a list of details</returns>
        public List<Details> Get(Settlement settlement)
        {
            return new Operations().ExecuteQueryToList<Details>("RI_Settlements_Details_Get_BySettlement", Operations.CommandType.StoredProcedure, new Parameter("Settlement", settlement.MojID));
        }

For the create and update and delete you don't need to map anything Operations class will Query   the stored Producer  for the parameters then it will map it to what object your giving, code : 

        /// <summary>
        /// Create details and get the new ID of it
        /// </summary>
        /// <param name="details">details you want to create</param>
        /// <param name="newDetailsID">the new details id</param>
        /// <returns>true if details get created</returns>
        public bool Create(Details details, out int newDetailsID)
        {
            newDetailsID = 0;
            return new Operations().ExecuteNoneQuery<Details>("RI_Settlements_Details_Create", details);
        }

And one more thing if you have primary key and Foreign Key  when you create child object you need the primary key of the parents, For the parents class look for the primary key property and set it as primary key code : 

        /// <summary>
        /// Gets or sets ID of the Details
        /// </summary>
        [PrimaryKey]
        public int ID { get; set; }

Than in child just set the parents property as foreign key Code: 

        /// <summary>
        /// Gets or sets Category of the Details
        /// </summary>
        [ForeignKey]
        public DetailsCategory Category { get; set; }

 

Quick start

So we have those class 

Details Class : 

/// <summary>
 /// this class Represents Detailstable
 /// </summary>
 public class Details
 {
     /// <summary>
     /// Initializes a new instance of the <see cref="Details"/> class.
     /// </summary>
     public Details()
     {
     }

     /// <summary>
     /// Initializes a new instance of the <see cref="Details"/> class.
     /// </summary>
     /// <param name="id">id of the Details</param>
     public Details(int id)
     {
     }

     /// <summary>
     /// Initializes a new instance of the <see cref="Details"/> class.
     /// </summary>
     /// <param name="id">id of the Details</param>
     /// <param name="total">total of the Details</param>
     /// <param name="area">area of the </param>
     /// <param name="dealsCount">deal count</param>
     /// <param name="createAt">create at</param>
     /// <param name="category">category of the details</param>
     /// <param name="settlement">settlement of the details</param>
     public Details(int id, string total, string area, int dealsCount, string createAt, DetailsCategory category, Settlement settlement)
     {
         this.ID = id;
         this.Total = total;
         this.Area = area;
         this.CreateAt = createAt;
         this.Category = category;
         this.DealsCount = dealsCount;
         this.Settlement = settlement;
     }

     /// <summary>
     /// Gets or sets ID of the Details
     /// </summary>
     [PrimaryKey]
     public int ID { get; set; }

     /// <summary>
     /// Gets or sets Details Deals Count
     /// </summary>
     public int DealsCount { get; set; }
     /// <summary>
     /// Gets or sets Details Total
     /// </summary>
     public string Total { get; set; }

     /// <summary>
     /// Gets or sets Area Category
     /// </summary>
     public string Area { get; set; }

     /// <summary>
     /// Gets or sets when the Details was created
     /// </summary>
     public string CreateAt { get; set; }

     /// <summary>
     /// Gets or sets Category of the Details
     /// </summary>
     [ForeignKey]
     public DetailsCategory Category { get; set; }
 }

Details Category Class :

namespace RealestateIndicator.Model
{
    using GeniusesCode.Framework.Data;
    using GeniusesCode.Helper;

    /// <summary>
    /// this class Detail Category Category table
    /// </summary>
    public class DetailsCategory 
    {
        /// <summary>
        /// Initializes a new instance of the <see cref="DetailsCategory"/> class.
        /// </summary>
        public DetailsCategory()             
        {
        }

        /// <summary>
        /// Initializes a new instance of the <see cref="DetailsCategory"/> class.
        /// </summary>
        /// <param name="id">id of the details category</param>
        public DetailsCategory(int id)
        {          
        }

        /// <summary>
        /// Initializes a new instance of the <see cref="Category"/> class.
        /// </summary>
        /// <param name="id">id of the settlement category</param>
        /// <param name="name">name of the settlement category</param>
        public DetailsCategory(int id, string name)
        {
            this.ID = id;
            this.Name = name;
        }

        /// <summary>
        /// Gets or sets Details Category ID
        /// </summary>
        public int ID { get; set; }

        /// <summary>
        /// Gets or sets Details Category Name
        /// </summary>
        public string Name { get; set; }
    }

 

Geniuses Code DLL 

Operations Class 

Operations class after you the following : 

public class Operations
{
    public Operations();
    public Operations(string connectionString, string encryptionKey = null, bool allowEncryption = false);

    public bool AllowEncryption { get; set; }
    public string ConnectionStirng { get; set; }
    public string EncryptionKey { get; set; }

    public string Decrypt(string data);
    public string Decrypt(string data, string key);
    public bool Delete(string tableName, string whereColumnName, object whereValue);
    public string Encrypt(string data);
    public string Encrypt(string data, string key);
    public bool ExecuteNoneQuery<T>(string command, T dealWithThisObject);
    public bool ExecuteNoneQuery(string command, Operations.CommandType commandType = Operations.CommandType.Text, params Parameter[] parameters);
    public bool ExecuteNoneQuery<T>(string command, T dealWithThisObject, out int paramter);
    public bool ExecuteNoneQuery<T>(string command, T dealWithThisObject, out List<Parameter> outParams);
    public bool ExecuteNoneQuery(string command, out List<Parameter> outPramas, Operations.CommandType commandType = Operations.CommandType.Text, params Parameter[] parameters);
    public DataTable ExecuteQuery(string command, Operations.CommandType commandType = Operations.CommandType.Text, params Parameter[] parameters);
    public DataTable ExecuteQuery(string command, out List<Parameter> outPramas, Operations.CommandType commandType = Operations.CommandType.Text, params Parameter[] parameters);
    public List<T> ExecuteQueryToList<T>(string command, Operations.CommandType commandType = Operations.CommandType.Text, params Parameter[] parameters);
    public List<T> ExecuteQueryToList<T>(string command, out List<Parameter> outPramas, Operations.CommandType commandType = Operations.CommandType.Text, params Parameter[] parameters);
    public T ExecuteQueryToObject<T>(string command, Operations.CommandType commandType = Operations.CommandType.Text, params Parameter[] parameters);
    public T ExecuteQueryToObject<T>(string command, out List<Parameter> outPramas, Operations.CommandType commandType = Operations.CommandType.Text, params Parameter[] parameters);
    public bool Insert(string tableName, params Parameter[] parameters);
    public bool Update(string tableName, string whereColumnName, object whereValue, params Parameter[] parameters);

    public enum CommandType
    {
        StoredProcedure = 0,
        Text = 1,
        TableDirect = 2,
    }
}

Encryption and Decryption Methods

You can Encrypt and Decrypt any string with key stored in config file or a key you pass it to Operations : 

string word = new Operations().Encrypt("ALGHABBAN");
 string word2 = new Operations().Encrypt("ALGHABBAN", "this my key");

string word3 = new Operations().Decrypt(word);
string word4 = new Operations().Decrypt(word2, "this my key");

If you allow Encryption by using the config file the Operations Class will automatically encrypt and dcrypt all string proprties for you. 

Execute None Query Methods with mapping 

You have 5 method to Execute None Query Command (Update, Delete, Insert) , 

Frist Execute None Query for object , for example if I want to insert new row in Details category table I will use this stored procedure : 

CREATE PROCEDURE RI_Settlements_Details_Categories_Create
    @ID int output, 
    @Name nvarchar(1500) 
AS
BEGIN
    insert into RI_Settlements_Details_Categories(Name)
    values (@Name)
    
    SET @ID = SCOPE_IDENTITY();
END

 

Execute None Query with no output parameters return . 

        /// <summary>
        /// Create new Details
        /// </summary>
        /// <param name="detailsCategory">details you want to create</param>
        /// <returns>true if details get created</returns>
        public bool Create(DetailsCategory detailsCategory)
        {            
            return new Operations().ExecuteNoneQuery<DetailsCategory>(
                "RI_Settlements_Details_Categories_Create", detailsCategory);
        }

As you can see there's no data mapping what so ever no parameter no command nothing, the Operations class will take the stored procedure name and get all the parameters for then the Operations class will walk throw the list of the parameters and create new command parameter which will take the value from the opposite property in details category . 

Execute None Query with one integer output parameters return . 

        /// <summary>
        /// Create new Details
        /// </summary>
        /// <param name="detailsCategory">details you want to create</param>
        /// <returns>true if details get created</returns>
        public bool Create(DetailsCategory detailsCategory)
        {
            int outNewDetailsID = 0;
            return new Operations().ExecuteNoneQuery<DetailsCategory>(
                "RI_Settlements_Details_Categories_Create", detailsCategory, out outNewDetailsID);
        }

Execute None Query with a List of output parameters return :

/// <summary>
/// Create details and get the new ID of it
/// </summary>
/// <param name="detailsCategory">details you want to create</param>
/// <param name="newDetailsID">the new details id</param>
/// <returns>true if details get created</returns>
public bool Create(DetailsCategory detailsCategory, out int newDetailsID)
{
    newDetailsID = 0;
    List<Parameter> newDetailsIDParamters = new List<Parameter>();
    bool createDetails = new Operations().ExecuteNoneQuery("RI_Settlements_Details_Categories_Create", detailsCategory, out newDetailsIDParamters );
    if (newDetailsIDParamters != null && newDetailsIDParamters.Count > 0)
    {
        newDetailsID = int.Parse(newDetailsIDParamters[0].Value.ToString());
    }

    return createDetails;
}

Execute None Query Methods without mapping 

Execute None Query with no output parameters return . 

/// <summary>
/// Update details Category
/// </summary>
/// <param name="detailsCategory">details Category you want to update</param>
/// <returns>true if details Category get updated</returns>
public bool Update(DetailsCategory detailsCategory)
{
    return new Operations().ExecuteNoneQuery(
        "RI_Settlements_Details_Categories_Update",
        Operations.CommandType.StoredProcedure,
        new Parameter("ID", detailsCategory.ID),
        new Parameter("Name", detailsCategory.Name));
}

Execute None Query with List of output parameters return .

/// <summary>
/// Create details and get the new ID of it
/// </summary>
/// <param name="detailsCategory">details you want to create</param>
/// <param name="newDetailsID">the new details id</param>
/// <returns>true if details get created</returns>
public bool Create(DetailsCategory detailsCategory, out int newDetailsID)
{
    newDetailsID = 0;
    List<Parameter> newDetailsIDParamters = new List<Parameter>();
    bool createDetails = new Operations().ExecuteNoneQuery(
        "RI_Settlements_Details_Categories_Create",
        out newDetailsIDParamters,
        Operations.CommandType.StoredProcedure,
        new Parameter("ID", detailsCategory.ID, Parameter.Direction.Output),
        new Parameter("Name", detailsCategory.Name));
    if (newDetailsIDParamters != null && newDetailsIDParamters.Count > 0)
    {
        newDetailsID = int.Parse(newDetailsIDParamters[0].Value.ToString());
    }

    return createDetails;
}

Execute Query Methods 

Execute Query with out data mapping  : 

DataTable detailsCastegory = new Operations().ExecuteQuery("RI_Settlements_Details_Categories_Get");

Execute Query with data mapping to List of objerct : 

return new Operations().ExecuteQueryToList<DetailsCategory>("RI_Settlements_Details_Categories_Get");

Execute Query with data mapping to objerct : 

DetailsCategory detailsCategory = new Operations().ExecuteQueryToObject<DetailsCategory>("RI_Settlements_Details_Categories_Get_ByID", Operations.CommandType.StoredProcedure, new Parameter("ID", id));

Your not limit to Stored procedure

You can aloes deal with Query in Code for Example you can do the following : 

DetailsCategory detailsCategory = new Operations().ExecuteQueryToObject<DetailsCategory>("select * from RI_Settlement_Details_Category where ID = @ID ", Operations.CommandType.Text, new Parameter("ID", id));

Or 

DetailsCategory detailsCategory = new Operations().ExecuteQueryToObject<DetailsCategory>("select * from RI_Settlement_Details_Category where ID = 15", Operations.CommandType.Text);

Primary Key and Foreign Key

In the Example above  we have Details and DetailsCategory Object, So each details will has a category , if you want to create a new details Object without auto mapping option will right this code : 

        /// <summary>
        /// Update Details
        /// </summary>
        /// <param name="details">details you want to update</param>
        /// <returns>true if details get updated</returns>
        public bool Update(Details details)
        {
            return new Operations().ExecuteNoneQuery(
                "RI_Settlements_Details_Update",
                Operations.CommandType.StoredProcedure,
                new Parameter("ID", details.ID),
                new Parameter("Settlement", details.Settlement.MojID),
                new Parameter("CreateAt", details.CreateAt),
                new Parameter("Total", details.Total),
                new Parameter("DealsCount", details.DealsCount),
                new Parameter("Area", details.Area),
                new Parameter("Category", details.Category.ID));
        }

Notes the last line of the code this one 

new Parameter("Category", details.Category.ID)

Your not passing the cateogry your passing the ID of the category that's  because the ID of the category is the priamry key DetealisCategory table. 

Now when it's came t auto mapping how you can tell that ID is primary key of the detalisCateogry Object, what I want to do is loop throw each properties until I come across foreign key then I need to  find the priamry key of that property and take the value of it . 

So on order to that you will need to add an attribute to ID of the Details Cateogry named Primary key see code : 

/// <summary>
/// Gets or sets ID of the Details
/// </summary>
[PrimaryKey]
public int ID { get; set; }

Then you will need to go the Foreign key in the details Object and add Foreign key attribute see code :

/// <summary>
/// Gets or sets Category of the Details
/// </summary>
[ForeignKey]
public DetailsCategory Category { get; set; }

Relationships

In the example above details has a property named cateogry I wanna be able to wirte something like that : 

Console.WriteLine(details.Category.Name) // printer

So let me walk you throw what happens when Operations Start Data Mapping in Execute Query case it loop thru each properties in given object if it find user User-Defined Type it look for constructor that take integer if there's no constructor take integer it will throw error . 

In that constructor all what you need to do the following : 

/// <summary>
/// Initializes a new instance of the <see cref="DetailsCategory"/> class.
/// </summary>
/// <param name="id">id of the details category</param>
public DetailsCategory(int id)
{
      DetailsCategory detailsCategory = new Operations().ExecuteQueryToObject<DetailsCategory>("RI_Settlement_Details_Category_GetByID", Operations.CommandType.StoredProcedure, new Parameter("ID", id));
    if (detailsCategory != null)
    {
        this.ID = detailsCategory.ID;
        this.Name = detailsCategory.Name;
    }
}

And that's how you can access the cateogry name from the details object . 

Validation

Valisation is also one part you you start dealing with DataBase, you need allows to check that get the right value from the user, there are 3 type of validation that keep repeted over and over in my code : 

• No null and if string not null or empty 
• Data Base Existence ( when you need to check that category if deatils object is in the data base ) 
• Expression  for validat something like email or password . 

And the end you will end up writing something like this : 

  public bool Create(User user)
  {
      if(user != null)
      {
          if(!string.IsNullOrWhiteSpace(user.Name))
          {
              if (!string.IsNullOrWhiteSpace(user.Password))
              {
                  if (this.ValidatPassword(user.Password))
                  {
                      if (!string.IsNullOrWhiteSpace(user.Email))
                      {
                         if(this.ValidatEmail(user.Email))
                         {
                             if (user.Role != null)
                             {
                                 if(new Role().Get(user.Role.ID) != null)
                                 {
                                     return new Operations().ExecuteNoneQuery<User>("UMS_users_Create", user);
                                 }
   
                                 throw new Exception("Role Can't be null, or not regested");
                             }
                         }
                      }
   
                      throw new Exception("Email can't be null");
                  }
              }
   
              throw new Exception("Password can't be null");
          }
   
          throw new Exception("User name can't be null or empty");
      }
   
      throw new Exception("User can't be null");
  }

So in order to fix this issues I create class named AutoValidation Derived from attributes : 

  namespace GeniusesCode.Framework.Data
  {
      using System;
  
      /// <summary>
      /// Auto Validation Class
      /// </summary>
      public class AutoValidation : Attribute
      {
          /// <summary>
          /// Gets or sets ValidationType
          /// </summary>
          public enum ValidationType
          {
              Number,
              Custom,
              DataBaseExistence,
          }        
   
          /// <summary>
          /// Gets or sets All Null
          /// </summary>
          public bool AllowNull { get; set; }
  
          /// <summary>
          /// Gets or sets Expression Object
          /// </summary>
          public string Expression { get; set; }
   
          /// <summary>
          /// Gets or set Message
          /// </summary>
          public string ErrorMessage { get; set; }
   
          /// <summary>
          /// 
          /// </summary>
          public AutoValidation.ValidationType Type { get; set; }
      }
  }

Now if I have property named name in class User and that name should not be null I can just do something like that : 

        /// <summary>
        /// Gets or sets User Name
        /// </summary>
        [AutoValidation(
            AllowNull = false,
            ErrorMessage = "User Name can't be null")]
        public string Name { get; set; }

And by set auto validation to true in the web.config like this : 

      <?xml version="1.0" encoding="utf-8" ?>
   <configuration>
     <appSettings>
       <add key="DbConnection" value="Smoething"/>
       <add key="AutoValidation" value="True"/>
     </appSettings>
       <startup> 
           <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
       </startup>
   </configuration>

Now when I try to create the user object I will get this : 

Now if you want to validate email 

        /// <summary>
        /// Gets or sets User Email
        /// </summary>
        [AutoValidation(
            AllowNull = false,
            ErrorMessage = "Wrong Email Address",
            Expression = @"^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+$",
            Type = AutoValidation.ValidationType.Custom)]
        public string Email { get; set; }

And the result will be

Any user will has a role and if you want to check that role is  Exist in the data base before adding the user you will need to set the ValidationType to DataBaseExistence and create a method that take int ( the ID of the Role) and return Role object  : 

 

        /// <summary>
        /// Gets or sets User Role
        /// </summary>        
        [AutoValidation(
            AllowNull = false,
            ErrorMessage = "Role Can't be null, or not regested",
            Type = AutoValidation.ValidationType.DataBaseExistence)]
        [ForeignKey]
        public Role Role { get; set; }

Set the Get method as DataBaseExistence  

        /// <summary>
        /// Get role by id
        /// </summary>
        /// <param name="id">id for the role you want to get</param>
        /// <returns>a role object</returns>
        [DataBaseExistence]
        public Role Get(int id)
        {
            return new Operations().ExecuteQueryToObject<Role>("GKPI_Roles_Get_ByID", Operations.CommandType.StoredProcedure, new Parameter("ID", id));            
        }

The result will be : 

Model and Controller Generating 

You can also Generate the model class for tables in the database using Geniuses Code Generator, it's simple tools straightforward 

To start just click then database icon in the Tables and Project     add the connection string and the click save you will see all the tables listed in the tables and project window . Now select a table from the table list and you can update the properties of that table in the properties window. 

Class name : the name of the class you for this table for example you can have the NorthWind_Categpries as table name but Category as class name.

GetByIDStoredProucderName : here you can set the name of the sorted producer that restores by the ID. 

Columns : this where you can edit and set Colum’s of the a table see the Image : 

If you want to enable the auto validation of a priorities just make AutoValidation to true, If the you set the column as Primary key of foreign key change the type if the to object type set an error message all or expression all the things you need to do it by writing you can just do it visually. 

Now to see the C# code you can double click the table name in the table list or right click and click to Generate code or just click the <? icon any of those option will show you something like that : 

 

Now your done from updating and customizing the table and the priorities of it and you ready to generate the code, First you need to edit the project setting by click in the project icon in the tables and project window    you can set the fowling properties : 

Name space : the name space of the model.

SaveTo : where you want the generated code. 

Company Name : the company name you want to show in copyright header of the file. 

Name : the name of the project. 

Once you done you can just click at the   and the Model class with cotnroller  as Visual studio project .

Data Base Sync 

Execute Non Query To multiple database at same time with just one line of code . 

add adding DbConnections in the app.Config or web.config and paasing all the data base connection string to it. 

Logger 

To enable logger just add the flowing setting in the setting part 

<appSetting>
  <add key="AllowLogging" value="True"/>
  <add key="WriteLogIn" value="WindowsEvent"/> 
  <add key=SoruceName" value="GeniusesCode.Framework.SQLServer.Test"/>
  <add key="LoggerPath" value="C:\Log.text"/>
  <add Key=GenericMessage" value="Something wrong happend" />
</appSetting>

new the  user will see what ever the Generic Message value has.  alsoe you can use windowsEvent or Text to write log in.