Resources
Introduction
Once you have finished the design of the database for your project, you start directly writing C # code in order to deal with it, if you are a great developer you will design your application using N-Tire structure you know, Model, which represents tables, and Controller, which represents the functionality of that table.
Will then usual you have to work with the database stuff Connection, Command , Reader , Adapter , Data Set , Data Table etc., you will end up with something like that:
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:
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
public List<Details> Get()
{
return new Operations().ExecuteQueryToList<Details>("RI_Settlements_Details_Get");
}
And you can pass parameters like that :
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 :
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 :
[PrimaryKey]
public int ID { get; set; }
Than in child just set the parents property as foreign key Code:
[ForeignKey]
public DetailsCategory Category { get; set; }
Quick start
So we have those class
Details Class :
public class Details
{
public Details()
{
}
public Details(int id)
{
}
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;
}
[PrimaryKey]
public int ID { get; set; }
public int DealsCount { get; set; }
public string Total { get; set; }
public string Area { get; set; }
public string CreateAt { get; set; }
[ForeignKey]
public DetailsCategory Category { get; set; }
}
Details Category Class :
namespace RealestateIndicator.Model
{
using GeniusesCode.Framework.Data;
using GeniusesCode.Helper;
public class DetailsCategory
{
public DetailsCategory()
{
}
public DetailsCategory(int id)
{
}
public DetailsCategory(int id, string name)
{
this.ID = id;
this.Name = name;
}
public int ID { get; set; }
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 .
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 .
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 :
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 .
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 .
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 :
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 :
[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 :
[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)
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 :
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;
public class AutoValidation : Attribute
{
public enum ValidationType
{
Number,
Custom,
DataBaseExistence,
}
public bool AllowNull { get; set; }
public string Expression { get; set; }
public string ErrorMessage { get; set; }
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 :
[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 :
="1.0" ="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
[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 :
[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
[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.
