I have created a simple N tier Application using c#.I have provided the screen shots for better understanding purpose. Here is my Project View:-
Now I have to Explain The Step By Step Architecture of my application.
My Database with both Table And StoreProcedure Parameter Views:--
I have taken one Sql server database inside App Data Folder. Created one Table and One Store Procedure which is shown above figure.
For your better information i have to show one clear screen shot of my database with folder:-
References of Business Access Layer is Shown in Below Screen Shot:-
References of Both Cache Layer and Data Access Layer are Shown in Below Screen Shot:-
References of Both Framework and Intigration Layer are Shown in Below Screen Shot:-
References which is needed in our application has shown in Below Screen Shot:-
The Entire Project View is Shown in Below Screen Shot with Data Access Layer Code:-
The Entire Project View is Shown in Below Screen Shot with Cache Layer Code:-
The Entire Project View is Shown in Below Screen Shot with Business Access Layer Code:-
Again I Have Shown Data Access Layer Code in Below Screen Shot :-
The Entire Project View is Shown in Below Screen Shot with Integration Layer Code:-
Briefly Description is given here.
Now take one class library(goto solution-->right click and add new project and select class library) and name it as Framework.cs
Now it will display like this:-
Now within that library create two folder Like Log And SendMail Now inside Log Folder Add one class as Log.cs and inside SendMail Add another class as SendMail.cs
Now the Code Inside Log.cs is:-
using System;
using System.Collections;
using System.Collections.Generic;
using System.Reflection;
using System.Net.Mail;
using System.Globalization;
using System.Threading;
using System.Text.RegularExpressions;
using System.Web;
using System.Configuration;
namespace Framework
{
public static class Log
{
#region Declaration Constante
const string NOTICKET = "no ticket";
const string NA = "---";
const string SEVERITY_HIGH = "HIGH";
const string SEVERITY_ERROR = "ERROR";
const string SEVERITY_WARNING = "WARNING";
const string SEVERITY_INFO = "INFORMATION";
#endregion
#region Declaration Properties & Class for Module Mail
private static Dictionary<string, InfosLog> LogList
{
get
{
if (HttpContext.Current.Application["Logger"] == null)
{
HttpContext.Current.Application.Add("Logger", new Dictionary<string, InfosLog>());
}
return (Dictionary<string, InfosLog>)HttpContext.Current.Application["Logger"];
}
}
private class InfosLog
{
public int CounterGroup;
public int CounterTotalGroup;
public DateTime LastDateTime;
public string Severity;
public bool IsMailSend;
public bool IsHighLevel;
public bool IsDisplayPachetInfo;
public InfosLog(string p_Severity)
{
CounterGroup = 1;
CounterTotalGroup = 1;
LastDateTime = DateTime.Now;
IsMailSend = true;
IsHighLevel = false;
IsDisplayPachetInfo = false;
Severity = p_Severity;
}
}
#endregion Declaration Class Log
#region Error
public static void Error(Exception exception)
{
Log.Logger(exception, false, false, SEVERITY_ERROR);
}
public static void Error(Exception exception, bool rethrowError)
{
Log.Logger(exception, rethrowError, false, SEVERITY_ERROR);
}
public static void Error(string Message)
{
Exception exception = new Exception(Message);
Log.Logger(exception, false, false, SEVERITY_ERROR);
}
public static void Error(string Message, bool rethrowError)
{
Exception exception = new Exception(Message);
Log.Logger(exception, rethrowError, false, SEVERITY_ERROR);
}
public static void Error(Exception exception, bool rethrowError, bool Ticket)
{
Log.Logger(exception, rethrowError, Ticket, SEVERITY_ERROR);
}
#endregion
#region Warning
public static void Warning(string Message)
{
Exception exception = new Exception(Message);
Log.Logger(exception, false, false, SEVERITY_WARNING);
}
public static void Warning(string PrefixMessage, Exception exception)
{
Log.Logger(exception, false, false, SEVERITY_WARNING, PrefixMessage);
}
public static void Warning(Exception exception)
{
Log.Logger(exception, false, false, SEVERITY_WARNING);
}
#endregion
#region Info
public static void Info(string Message)
{
Exception exception = new Exception(Message);
Log.Logger(exception, false, false, SEVERITY_INFO);
}
public static void Info(Exception exception)
{
Log.Logger(exception, false, false, SEVERITY_INFO);
}
#endregion
#region Private Method
private static void Logger(Exception exception, bool rethrowError, bool Ticket, string Severity)
{
Log.Logger(exception, rethrowError, Ticket, Severity, string.Empty);
}
/// <summary>
/// Core Logger
/// </summary>
private static void Logger(Exception exception, bool rethrowError, bool Ticket, string p_Severity, string PrefixMessage)
{
// Create Number ExceptionID
string ExceptionID = NOTICKET;
if (Ticket == true)
{
#region Create Number Incident
ExceptionID = DateTime.Now.ToString("yyMMddHHmmss") + DateTime.Now.Millisecond.ToString();
string key = string.Empty;
string Dico = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
Random randow = new Random();
for (int i = 0; i < 3; i++)
{
int k = randow.Next(1, 26);
key += Dico[k];
}
ExceptionID += key;
#endregion
#region Create cookie for page error 500
HttpCookie SwashWebsiteError = new HttpCookie("ErrorMessage");
SwashWebsiteError["ExceptionID"] = ExceptionID;
SwashWebsiteError["Page"] = HttpContext.Current.Request.Url.OriginalString;
SwashWebsiteError["Message"] = exception.Message;
SwashWebsiteError["Target"] = exception.TargetSite.ToString();
SwashWebsiteError["Time"] = DateTime.Now.ToString("dd/MM/yyyy - HH:mm:ss");
SwashWebsiteError.Expires = DateTime.Now.AddHours(1);
HttpContext.Current.Response.Cookies.Add(SwashWebsiteError);
#endregion
}
// Format on Module Format Message
string v_Message = Log.FormatMessage(ExceptionID, p_Severity, exception, PrefixMessage);
// Switch for Log on Module Mail
if ((p_Severity != SEVERITY_WARNING) && (p_Severity != SEVERITY_INFO))
{
Log.LoggerMail(v_Message, exception.Message, p_Severity);
}
// Switch for Log on Module Application Block
Log.LoggerApplicationBlock(v_Message, exception, rethrowError, p_Severity);
}
#region Module Mail
private static void LoggerMail(string MessageLog, string KeyMessageException, string p_Severity)
{
#region Initialization
DateTime NewDateTimeNow = DateTime.Now;
// Get Configuration in web.config
int LapTimeCritical = Convert.ToInt32(ConfigurationManager.AppSettings["Log_LapTimeCritical"]);
int CriticalQuantityMail = Convert.ToInt32(ConfigurationManager.AppSettings["Log_CriticalQuantityMail"]);
int QuantityMailInformBeforeCritical = Convert.ToInt32(ConfigurationManager.AppSettings["Log_QuantityMailInformBeforeCritical"]);
int FrequenceMailToSend = Convert.ToInt32(ConfigurationManager.AppSettings["Log_CriticalFrequenceMail"]);
#endregion
#region Purge Logs stock in Application
ArrayList v_ArrayListCodeDelete = new ArrayList();
foreach (string CodeLog in Log.LogList.Keys)
{
DateTime v_DateTimeLog = Log.LogList[CodeLog].LastDateTime;
// If date of Log is out of date
if (new TimeSpan(NewDateTimeNow.Ticks - v_DateTimeLog.Ticks).TotalSeconds > LapTimeCritical)
{
// If Log not send by mail (Purge)
if (Log.LogList[CodeLog].IsMailSend == false)
{
string MessagePurge = MessageLog;
// Send mail
Log.SendMailLog(MessagePurge, Log.LogList[CodeLog]);
}
// Stock Log to delete
v_ArrayListCodeDelete.Add(CodeLog);
}
}
// Delete all Log out of date
foreach (string CodeLog in v_ArrayListCodeDelete)
{
Log.LogList.Remove(CodeLog);
}
#endregion
#region Log Error / Warning / Infos Mail
InfosLog CurrentInfoLog;
// if MessageLog exist in Application Object 'LogList'
if (Log.LogList.ContainsKey(KeyMessageException))
{
CurrentInfoLog = Log.LogList[KeyMessageException];
// Update CurrentLog
CurrentInfoLog.CounterTotalGroup += 1;
DateTime v_LastDateTime = CurrentInfoLog.LastDateTime;
// if Date Log is NOT out-of-date
if (new TimeSpan(NewDateTimeNow.Ticks - v_LastDateTime.Ticks).TotalSeconds < LapTimeCritical)
{
// if Quantity Mail send is equal Critical Quantity Mail -> Start High Level
if ((CurrentInfoLog.CounterTotalGroup > (CriticalQuantityMail - QuantityMailInformBeforeCritical)) && (CurrentInfoLog.CounterTotalGroup <= CriticalQuantityMail))
{
CurrentInfoLog.IsHighLevel = true;
}
// if Quantity Mail send is higher to Critical Quantity
if (CurrentInfoLog.CounterTotalGroup > CriticalQuantityMail)
{
CurrentInfoLog.CounterGroup += 1;
CurrentInfoLog.IsHighLevel = true;
// if Frequency Mail is not reached, don't send
if (((CurrentInfoLog.CounterTotalGroup - CriticalQuantityMail) % FrequenceMailToSend) != 0)
{
CurrentInfoLog.IsMailSend = false;
CurrentInfoLog.IsDisplayPachetInfo = true;
}
else
{
CurrentInfoLog.IsMailSend = true;
}
}
CurrentInfoLog.LastDateTime = NewDateTimeNow;
}
}
else // Add new Log
{
CurrentInfoLog = new InfosLog(p_Severity);
Log.LogList.Add(KeyMessageException, CurrentInfoLog);
}
if (CurrentInfoLog.IsMailSend == true)
{
Log.SendMailLog(MessageLog, CurrentInfoLog);
// Reinit CounterGroup Log
CurrentInfoLog.CounterGroup = 0;
}
#endregion
}
private static void SendMailLog(string Body, InfosLog p_InfosLog)
{
string Subject = string.Empty;
MailPriority v_MailPriority = MailPriority.Normal;
switch (p_InfosLog.Severity)
{
case SEVERITY_ERROR:
Subject = "[ERROR]";
v_MailPriority = MailPriority.Normal;
break;
case SEVERITY_WARNING:
Subject = "[Warning]";
v_MailPriority = MailPriority.Low;
break;
case SEVERITY_INFO:
Subject = "[Info]";
v_MailPriority = MailPriority.Low;
break;
}
if (p_InfosLog.IsHighLevel == true)
{
v_MailPriority = MailPriority.High;
}
string InfosSubject = string.Empty;
if (HttpContext.Current.Handler != null)
{
TextInfo textInfo = Thread.CurrentThread.CurrentCulture.TextInfo;
InfosSubject = textInfo.ToTitleCase(HttpContext.Current.Handler.ToString().Replace("ASP.", string.Empty).Replace("_aspx", string.Empty).Replace("_", ">"));
}
if (p_InfosLog.IsDisplayPachetInfo == true)
{
Subject += " [x" + p_InfosLog.CounterGroup + "] (" + p_InfosLog.CounterTotalGroup + ")";
}
if (InfosSubject != string.Empty)
{
Subject += " Alert in " + InfosSubject;
}
else
{
Subject += " Alert in Website";
}
#region Send Log Mails
// Get Config Mail
string MailFrom = (string)ConfigurationManager.AppSettings["Log_MailFrom"];
string MailFromDisplayName = (string)ConfigurationManager.AppSettings["Log_MailFromDisplayName"];
string MailTo = (string)ConfigurationManager.AppSettings["Log_MailTo"];
string MailBc = (string)ConfigurationManager.AppSettings["Log_MailCc"];
if (!string.IsNullOrEmpty(MailFrom) && !string.IsNullOrEmpty(MailTo))
{
// Get Subject & Body
string MailSubject = Subject;
string MailBody = Body;
// Send Mail
MailMessage MyMessage = new MailMessage();
MailAddress MyAddressFrom = new MailAddress(MailFrom, MailFromDisplayName + " [" + (string)ConfigurationManager.AppSettings["ServerName"] + "]");
MyMessage.From = MyAddressFrom;
Regex v_Regex = new Regex("[;]");
string[] v_TabEmail = v_Regex.Split(MailTo);
foreach (string v_Email in v_TabEmail)
{
MailAddress MyAddressTo = new MailAddress(v_Email);
MyMessage.To.Add(MyAddressTo);
}
if (!string.IsNullOrEmpty(MailBc))
{
string[] v_TabEmailBc = v_Regex.Split(MailBc);
foreach (string v_EmailBc in v_TabEmailBc)
{
MailAddress MyAddressCc = new MailAddress(v_EmailBc);
MyMessage.CC.Add(MyAddressCc);
}
}
MyMessage.Priority = v_MailPriority;
MyMessage.Subject = MailSubject;
MyMessage.Body = MailBody;
SendMail Sender = new SendMail();
Sender.Send(MyMessage);
}
#endregion
}
#endregion
#region Module Application Block
private static void LoggerApplicationBlock(string Message, Exception exception, bool rethrowError, string p_Severity)
{
Exception ExceptionFormat = new Exception(Message);
// Throw Error
if (rethrowError)
{
throw (exception);
}
}
#endregion
#region Module Format Message
private static string FormatMessage(string ExceptionID, string Severity, Exception exception, string PrefixMessage)
{
string MessageException = PrefixMessage;
string MessageInnerException = string.Empty;
string TargetSite = string.Empty;
string StackTrace = string.Empty;
if (exception != null)
{
if (exception.Message != null)
{
MessageException += " " + exception.Message;
}
if (exception.InnerException != null)
{
MessageInnerException = "InnerException: " + exception.InnerException.Message + Environment.NewLine + "-----------------------------------------------------------------" + Environment.NewLine;
}
if (exception.TargetSite != null)
{
TargetSite = exception.TargetSite.ToString();
}
if (exception.StackTrace != null)
{
StackTrace = exception.StackTrace;
}
}
#region Get cookie in context
string Cookies = string.Empty;
if (HttpContext.Current.Request.Cookies["SwashWebsiteUserId"] != null)
{
Cookies += HttpContext.Current.Request.Cookies["SwashWebsiteUserId"].Value + " | ";
}
else
{
Cookies += " - | ";
}
if (HttpContext.Current.Request.Cookies["SwashWebsiteLanguage"] != null)
{
Cookies += HttpContext.Current.Request.Cookies["SwashWebsiteLanguage"].Value;
}
#endregion
#region Get infos
string PageName = NA;
string ClassName = NA;
string Browser = NA;
if (HttpContext.Current != null)
{
if (HttpContext.Current.Request != null)
{
Browser = HttpContext.Current.Request.UserAgent;
if (HttpContext.Current.Request.Url != null)
{
PageName = HttpContext.Current.Request.Url.AbsoluteUri;
}
}
if (HttpContext.Current.Handler != null)
{
ClassName = HttpContext.Current.Handler.ToString();
}
}
#endregion
#region eServices UserID
// [DEVELOPMENT SPECIFIC] - Begin
string UserID = "Not connected";
if (HttpContext.Current.Session != null)
{
for (int i = 0; i < HttpContext.Current.Session.Count; i++)
{
object obj = HttpContext.Current.Session[i];
if (obj != null)
{
Type type = obj.GetType();
if (obj.GetType().ToString() == "Website.BusinessMappingObject.Client")
{
PropertyInfo[] TabProp = type.GetProperties();
foreach (PropertyInfo prop in TabProp)
{
if (prop.ToString() == "System.String Login")
{
UserID = (string)prop.GetValue(obj, null);
break;
}
}
}
}
}
}
// [DEVELOPMENT SPECIFIC] - End
#endregion
#region Format Message
string Message = Environment.NewLine + "-----------------------------------------------------------------" + Environment.NewLine +
"DateTime: " + DateTime.Now.ToString("dddd dd/MM/yyyy - HH:mm:ss.fff") + Environment.NewLine + "-----------------------------------------------------------------" + Environment.NewLine +
"Number: " + ExceptionID + Environment.NewLine + "-----------------------------------------------------------------" + Environment.NewLine +
"Severity: " + Severity + Environment.NewLine + "-----------------------------------------------------------------" + Environment.NewLine +
"Page: " + PageName + Environment.NewLine + "-----------------------------------------------------------------" + Environment.NewLine +
"Message: " + MessageException + Environment.NewLine + "-----------------------------------------------------------------" + Environment.NewLine +
MessageInnerException +
"Class: " + ClassName + Environment.NewLine + "-----------------------------------------------------------------" + Environment.NewLine +
"TargetSite: " + TargetSite + Environment.NewLine + "-----------------------------------------------------------------" + Environment.NewLine +
"Stack: " + Environment.NewLine + Environment.NewLine + StackTrace + Environment.NewLine + Environment.NewLine + "-----------------------------------------------------------------" + Environment.NewLine +
"Browser: " + Browser + Environment.NewLine + "-----------------------------------------------------------------" + Environment.NewLine +
"Cookies: " + Cookies + Environment.NewLine + "-----------------------------------------------------------------" + Environment.NewLine +
"User ID: " + UserID + Environment.NewLine + "-----------------------------------------------------------------" + Environment.NewLine;
#endregion
return (Message);
}
#endregion
#endregion
}
}
Now the Code Inside SendMail.cs is:-
using System;
using System.Net.Mail;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Configuration;
namespace Framework
{
public class SendMail
{
#region Methods and Implementation
/// <summary>
/// Constructor
/// </summary>
/// <param name="strConnection">ConnectionString to use to save the mail in the database</param>
public SendMail()
{
}
public delegate void SendDelegate(MailMessage eMail);
/// <summary>
/// Send the MailMessage
/// </summary>
/// <param name="eMail">MailMessage to send</param>
public void Send(MailMessage eMail)
{
SendDelegate sd = new SendDelegate(SendEmail);
AsyncCallback cb = new AsyncCallback(finishSend);
IAsyncResult ar = sd.BeginInvoke(eMail, cb, sd);
}
/// <summary>
/// Send the mail message
/// </summary>
/// <param name="eMail">MailMessage to send</param>
public void SendEmail(MailMessage eMail)
{
SmtpClient smtpClient = new SmtpClient();
string MailServer = ConfigurationManager.AppSettings["MailServer"] as string;
smtpClient.Host = MailServer;
try
{
// Kishor : uncomment when actually required to send mail
// smtpClient.Send(eMail);
}
catch (Exception ex)
{
throw (ex);
// Recording the mail to databaes
//SendMailData sMailData = new SendMailData();
//sMailData.storeEmail(eMail);
}
}
private void finishSend(IAsyncResult ar) { }
#endregion Methods and Implementation
}
/// <summary>
/// <p>Class SendMailData</p>
/// </summary>
public class SendMailData
{
#region Variables
/// <summary>
/// <p>internal object which contains the OleDb connection</p>
/// </summary>
private OleDbConnection _OleDbConnection;
#endregion Variables
#region Methods and Implementation
/// <summary>
/// <p>Constructor</p>
/// </summary>
public SendMailData()
{
_OleDbConnection = new OleDbConnection();
_OleDbConnection.ConnectionString = ConfigurationManager.ConnectionStrings["BusinessConnectionString"].ConnectionString;
}
/// <summary>
/// <p>Open a connection to the database</p>
/// </summary>
public void openConnection()
{
if (_OleDbConnection.State == ConnectionState.Closed)
_OleDbConnection.Open();
}
/// <summary>
/// <p>close the connection to the database</p>
/// </summary>
public void closeConnection()
{
if (_OleDbConnection.State == ConnectionState.Open)
_OleDbConnection.Close();
}
/// <summary>
/// Store the mail message in the database
/// </summary>
/// <param name="eMail">Mail message to store</param>
public void storeEmail(MailMessage eMail)
{
OleDbCommand cmdInsertEmail = new OleDbCommand();
// Ouverture de la connexion
openConnection();
cmdInsertEmail.Connection = _OleDbConnection;
cmdInsertEmail.CommandType = CommandType.StoredProcedure;
cmdInsertEmail.CommandText = "DT_EMAIL_INSERT";
cmdInsertEmail.Parameters.Add("PBCC", OleDbType.VarChar, 4000).Value = (eMail.Bcc == null) ? "" : eMail.Bcc.ToString().Replace("'", "''");
cmdInsertEmail.Parameters.Add("PBODY", OleDbType.LongVarChar).Value = (eMail.Body == null) ? "" : eMail.Body.ToString().Replace("'", "''");
cmdInsertEmail.Parameters.Add("PBODYENCODING", OleDbType.VarChar, 7).Value = (string)getEncoding(eMail.BodyEncoding);
cmdInsertEmail.Parameters.Add("PCC", OleDbType.VarChar, 4000).Value = (eMail.CC == null) ? "" : eMail.CC.ToString().Replace("'", "''");
cmdInsertEmail.Parameters.Add("PFROM", OleDbType.VarChar, 4000).Value = (eMail.From == null) ? "" : eMail.From.ToString().Replace("'", "''");
cmdInsertEmail.Parameters.Add("PPRIORITY", OleDbType.Integer).Value = (int)getPriority(eMail.Priority);
cmdInsertEmail.Parameters.Add("PSUBJECT", OleDbType.VarChar, 4000).Value = (eMail.Subject == null) ? "" : eMail.Subject.ToString().Replace("'", "''");
cmdInsertEmail.Parameters.Add("PTO", OleDbType.VarChar, 4000).Value = (eMail.To == null) ? "" : eMail.To.ToString().Replace("'", "''");
// Exécution de la requête
cmdInsertEmail.ExecuteNonQuery();
// Fermeture de la connexion
closeConnection();
}
/// <summary>
/// Get the mail message in the database
/// </summary>
/// <param name="intEmail">id of the mail to retry to send</param>
/// <returns>Mail message</returns>
public MailMessage readEmail(int intEmail)
{
OleDbCommand cmdSelectEmail = new OleDbCommand();
MailMessage returnEmail = new MailMessage();
// Ouverture de la connexion
openConnection();
cmdSelectEmail.Connection = _OleDbConnection;
cmdSelectEmail.CommandType = CommandType.StoredProcedure;
cmdSelectEmail.CommandText = "DT_EMAIL_SELECT";
cmdSelectEmail.Parameters.Add("PID", OleDbType.Integer).Value = intEmail;
cmdSelectEmail.Parameters.Add(new OleDbParameter("PBCC", OleDbType.VarChar, 4000)).Direction = ParameterDirection.Output;
cmdSelectEmail.Parameters.Add(new OleDbParameter("PBODY", OleDbType.LongVarChar)).Direction = ParameterDirection.Output;
cmdSelectEmail.Parameters.Add(new OleDbParameter("PBODYENCODING", OleDbType.VarChar, 7)).Direction = ParameterDirection.Output;
cmdSelectEmail.Parameters.Add(new OleDbParameter("PCC", OleDbType.VarChar, 4000)).Direction = ParameterDirection.Output;
cmdSelectEmail.Parameters.Add(new OleDbParameter("PFROM", OleDbType.VarChar, 4000)).Direction = ParameterDirection.Output;
cmdSelectEmail.Parameters.Add(new OleDbParameter("PPRIORITY", OleDbType.Integer)).Direction = ParameterDirection.Output;
cmdSelectEmail.Parameters.Add(new OleDbParameter("PSUBJECT", OleDbType.VarChar, 4000)).Direction = ParameterDirection.Output;
cmdSelectEmail.Parameters.Add(new OleDbParameter("PTO", OleDbType.VarChar, 4000)).Direction = ParameterDirection.Output;
// Exécution de la requête
cmdSelectEmail.ExecuteNonQuery();
returnEmail.Bcc.Add(new MailAddress(cmdSelectEmail.Parameters["PBCC"].Value.ToString()));
//returnEmail.Body = ((LongVarChar)cmdSelectEmail.Parameters["PBODY"].Value).Value.ToString();
returnEmail.BodyEncoding = getEncoding(cmdSelectEmail.Parameters["PBODYENCODING"].Value.ToString());
returnEmail.CC.Add(new MailAddress(cmdSelectEmail.Parameters["PCC"].Value.ToString()));
returnEmail.From = new MailAddress(cmdSelectEmail.Parameters["PFROM"].Value.ToString());
returnEmail.Priority = getPriority(int.Parse(cmdSelectEmail.Parameters["PPRIORITY"].Value.ToString()));
returnEmail.Subject = cmdSelectEmail.Parameters["PSUBJECT"].Value.ToString();
returnEmail.To.Add(new MailAddress(cmdSelectEmail.Parameters["PTO"].Value.ToString()));
// Fermeture de la connexion
closeConnection();
return returnEmail;
}
/// <summary>
/// Delete the mail with the id "Email"
/// </summary>
/// <param name="Email">int corresponding to the mail to delete</param>
public void deleteEmail(int Email)
{
OleDbCommand cmdDeleteEmail = new OleDbCommand();
// Ouverture de la connexion
openConnection();
cmdDeleteEmail.Connection = _OleDbConnection;
cmdDeleteEmail.CommandType = CommandType.StoredProcedure;
cmdDeleteEmail.CommandText = "DT_EMAIL_DELETE";
cmdDeleteEmail.Parameters.Add("PID", OleDbType.Integer).Value = Email;
// Exécution de la requête
cmdDeleteEmail.ExecuteNonQuery();
// Fermeture de la connexion
closeConnection();
}
/// <summary>
/// Return a dataset containing the list of email not sended
/// </summary>
/// <param name="strSchema"></param>
/// <returns>a dataset</returns>
public DataSet getListEmail(string strSchema)
{
// Variables
DataSet dsResult = new DataSet();
OleDbCommand oCommandListEmail = new OleDbCommand();
OleDbDataAdapter oAdapterListEmail = new OleDbDataAdapter();
string strSQL;
// Initialisation variables
dsResult.ReadXmlSchema(strSchema);
// Ouverture de la connexion
openConnection();
strSQL = "SELECT EMAIL_ID FROM TEMAIL";
// Execution de la requête
oCommandListEmail.Connection = _OleDbConnection;
oCommandListEmail.CommandText = strSQL;
oAdapterListEmail.SelectCommand = oCommandListEmail;
oAdapterListEmail.Fill(dsResult, "LISTEMAIL");
// Fermeture de la connexion
closeConnection();
return dsResult;
}
/// <summary>
/// Return the string type of the encoding value
/// </summary>
/// <param name="bEncoding">Encoding Value</param>
/// <returns>string corresponding to the encoding value</returns>
private string getEncoding(Encoding bEncoding)
{
switch (bEncoding.EncodingName)
{
case "US-ASCII":
return "ASCII";
case "Unicode (UTF-7)":
return "UTF-7";
case "Unicode (UTF-8)":
return "UTF-8";
case "Unicode":
return "Unicode";
default:
return "Default";
}
}
/// <summary>
/// Return the encoding value corresponding to the string encoding value
/// </summary>
/// <param name="strEncoding">string encoding value</param>
/// <returns>Encoding value</returns>
private Encoding getEncoding(string strEncoding)
{
switch (strEncoding)
{
case "US-ASCII":
return Encoding.ASCII;
case "Unicode (UTF-7)":
return Encoding.UTF7;
case "Unicode (UTF-8)":
return Encoding.UTF8;
case "Unicode":
return Encoding.Unicode;
default:
return Encoding.Default;
}
}
/// <summary>
/// Return the mail priority
/// </summary>
/// <param name="mPriority">MailPriority</param>
/// <returns>int</returns>
private int getPriority(MailPriority mPriority)
{
switch (mPriority.ToString())
{
case "High":
return 3;
case "Low":
return 1;
case "Normal":
return 2;
default:
return 2;
}
}
/// <summary>
/// Return the MailPriority corresponding to the int value
/// </summary>
/// <param name="intPriority">int value</param>
/// <returns>MailPriority value</returns>
private MailPriority getPriority(int intPriority)
{
switch (intPriority)
{
case 3:
return MailPriority.High;
case 1:
return MailPriority.Low;
case 2:
return MailPriority.Normal;
default:
return MailPriority.Normal;
}
}
#endregion Methods and Implementation
}
}
Now take another class library(goto solution-->right click and add new project and select class library) and name it as BusinessObject
Now it will display like this:-
Now within that library create one class and name it as NtierTestTableObject.cs
and the code looks as:-
Now the Code Inside NtierTestTableObject.cs is:-
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace BusinessObject
{
[Serializable]
public class NtierTestTableObject
{
private string _Operation;
public string Operation
{
get { return _Operation; }
set { _Operation = value; }
}
private int _NID;
public int NID
{
get { return _NID; }
set { _NID = value; }
}
private string _NName;
public string NName
{
get { return _NName; }
set { _NName = value; }
}
private string _NPhone;
public string NPhone
{
get { return _NPhone; }
set { _NPhone = value; }
}
private string _NGender;
public string NGender
{
get { return _NGender; }
set { _NGender = value; }
}
}
}
I have created the property regarding my tables in sqlserver database. I have declared the Operation variable regarding to my store procedure and other variable declared according to my columns in my Table.
Now take another class library(goto solution-->right click and add new project and select class library) and name it as DataAccessLayer
Now it will display like this:-
Now within that library create two folder Like Abstract And ADMIN And Create one class as NtierTestTableDAL.cs. Now inside Abstract Folder Add one class as AbstractData.cs and inside ADMIN Add another class as _ERPDataAccess_.cs
Now the Code Inside AbstractData.cs is:-
using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Text;
using Framework;
namespace DataAccessLayer
{
public abstract class AbstractData_SqlClient
{
#region Methods and Implementations
/// <summary>
/// Key of the connection string in web.config
/// </summary>
private string _ConnectionKey;
/// <summary>
/// Key of the connection string in web.config
/// </summary>
public string ConnectionKey
{
get
{
return _ConnectionKey;
}
set
{
_ConnectionKey = value;
}
}
/// <summary>
/// Context from MultiCompaniesShipCompCode in web.config defines in BusinessDataAccess Layer
/// </summary>
private string _Context;
/// <summary>
/// Context from MultiCompaniesShipCompCode in web.config defines in BusinessDataAccess Layer
/// </summary>
public string Context
{
get { return _Context; }
set { _Context = value; }
}
/// <summary>
/// No argument constructor for Abstract Data
/// </summary>
protected AbstractData_SqlClient()
{
}
/// <summary>
/// Exception Policy to sue to handle the exception
/// </summary>
private const string ExceptionPolicyValue = "Data Policy";
/// <summary>
/// Replace single quote with its ASCII equivalent.
/// </summary>
/// <param name="input">String contains single quote</param>
/// <returns>Parsed string</returns>
private string parseString(string input)
{
return input.Replace("'", "'");
}
/// <summary>
/// Execute the SQL command and return single value (Used for return count from SQL)
/// </summary>
/// <param name="strSQL">SqlClient command</param>
/// <returns>Return single query result</returns>
protected object ExecuteScalar(string strSQL)
{
// Variables
object objResult = null;
SqlCommand oCommand = new SqlCommand();
try
{
oCommand.Connection = ConnectionFactory.GetInstance.GetConnection(_ConnectionKey);
oCommand.Connection.Open();
oCommand.CommandText = strSQL;
objResult = oCommand.ExecuteScalar();
}
catch (Exception e)
{
if (strSQL != null)
{
Exception ex = new Exception(strSQL, e);
Log.Error(ex, true);
}
else
{
Log.Error(e, true);
}
}
finally
{
ConnectionFactory.GetInstance.CloseConnection(oCommand.Connection);
}
// Returning object
return objResult;
}
/// <summary>
/// Execute the SQL command and return single value (Used for return count from SQL)
/// </summary>
/// <param name="oCommand">Sql command</param>
/// <returns>Return single query result</returns>
protected object ExecuteScalar(SqlCommand oCommand)
{
// Variables
object objResult = null;
try
{
oCommand.Connection = ConnectionFactory.GetInstance.GetConnection(_ConnectionKey);
oCommand.Connection.Open();
oCommand.CommandType = CommandType.StoredProcedure;
objResult = oCommand.ExecuteScalar();
}
catch (Exception e)
{
bool rethrow = true;
if (oCommand != null)
{
Exception ex = new Exception(oCommand.CommandText, e);
Log.Error(ex, true);
}
else
{
Log.Error(e, true);
}
if (rethrow)
{
throw;
}
}
finally
{
ConnectionFactory.GetInstance.CloseConnection(oCommand.Connection);
}
// Returning object
return objResult;
}
/// <summary>
/// Execute the SQL command and return a datarow containing data.
/// </summary>
/// <param name="oCommand">Sql command</param>
/// <returns>Datarow containing data</returns>
protected DataRow ExecuteGetDataRow(SqlCommand oCommand)
{
// Variables
DataSet DsResult = new DataSet();
DataRow drowObject = null;
SqlDataAdapter oAdapter = new SqlDataAdapter();
try
{
oCommand.Connection = ConnectionFactory.GetInstance.GetConnection(_ConnectionKey);
oCommand.Connection.Open();
oCommand.CommandType = CommandType.StoredProcedure;
oAdapter.SelectCommand = oCommand;
oAdapter.Fill(DsResult);
if (DsResult.Tables.Count != 0 && DsResult.Tables[0].Rows.Count != 0)
{
drowObject = DsResult.Tables[0].Rows[0];
}
// Genetating an exception if more than one record has been found.
if (DsResult.Tables[0].Rows.Count > 1)
{
throw new Exception("More than 1 record found for stored procedure " + oCommand.CommandText + ".");
}
}
catch (Exception e)
{
if (oCommand != null)
{
Exception ex = new Exception(oCommand.CommandText, e);
Log.Error(ex, true);
}
else
{
Log.Error(e, true);
}
}
finally
{
ConnectionFactory.GetInstance.CloseConnection(oCommand.Connection);
}
return drowObject;
}
/// <summary>
/// Execute the SQL command and return a datable containing data.
/// </summary>
/// <param name="oCommand">Oledb command</param>
/// <returns>Datarow containing data</returns>
protected DataTable ExecuteGetDataTable(SqlCommand oCommand)
{
// Variables
DataSet DsResult = new DataSet();
DataTable dtableObject = new DataTable();
SqlDataAdapter oAdapter = new SqlDataAdapter();
try
{
oCommand.Connection = ConnectionFactory.GetInstance.GetConnection(_ConnectionKey);
oCommand.Connection.Open();
oCommand.CommandType = CommandType.StoredProcedure;
oAdapter.SelectCommand = oCommand;
oAdapter.Fill(DsResult);
dtableObject = DsResult.Tables[0];
}
catch (Exception e)
{
if (oCommand != null)
{
Exception ex = new Exception(oCommand.CommandText, e);
Log.Error(ex, true);
}
else
{
Log.Error(e, true);
}
}
finally
{
ConnectionFactory.GetInstance.CloseConnection(oCommand.Connection);
}
return dtableObject;
}
/// <summary>
/// Execute the SQL command and return a datable containing data.
/// </summary>
/// <param name="oCommand">Oledb command</param>
/// <returns>Datarow containing data</returns>
protected DataTable ExecuteGetDualRequest(SqlCommand oCommandSP, string strSQL)
{
// Variables
DataSet DsResult = new DataSet();
DataTable dtableObject = new DataTable();
SqlDataAdapter oAdapter = new SqlDataAdapter();
SqlCommand oCommand = new SqlCommand();
try
{
oCommand.Connection = ConnectionFactory.GetInstance.GetConnection(_ConnectionKey);
oCommand.Connection.Open();
oCommand.CommandType = CommandType.Text;
oCommand.CommandText = strSQL;
oAdapter.SelectCommand = oCommand;
oAdapter.SelectCommand.ExecuteScalar();
oCommand.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter OP in oCommandSP.Parameters)
{
oCommand.Parameters.Add(GetParameter(OP.ParameterName, OP.SqlDbType, OP.Value)).Direction = OP.Direction;
}
oCommand.CommandText = oCommandSP.CommandText;
oAdapter.SelectCommand = oCommand;
oAdapter.Fill(DsResult);
dtableObject = DsResult.Tables[0];
}
catch (Exception e)
{
if (oCommand != null)
{
Exception ex = new Exception(oCommand.CommandText, e);
Log.Error(ex, true);
}
else
{
Log.Error(e, true);
}
}
finally
{
ConnectionFactory.GetInstance.CloseConnection(oCommand.Connection);
}
return dtableObject;
}
/// <summary>
/// Execute the SQL query and return a datable containing data.
/// </summary>
/// <param name="SQL"></param>
/// <returns>Datarow containing data</returns>
protected DataTable ExecuteGetDataTable(string strSQL)
{
// Variables
DataTable dtableObject = new DataTable();
SqlDataAdapter oAdapter = new SqlDataAdapter();
SqlCommand oCommand = new SqlCommand();
try
{
oCommand.Connection = ConnectionFactory.GetInstance.GetConnection(_ConnectionKey);
oCommand.Connection.Open();
oCommand.CommandText = strSQL;
oAdapter.SelectCommand = oCommand;
oAdapter.Fill(dtableObject);
}
catch (Exception e)
{
if (oCommand != null)
{
Exception ex = new Exception(oCommand.CommandText, e);
Log.Error(ex, true);
}
else
{
Log.Error(e, true);
}
}
finally
{
ConnectionFactory.GetInstance.CloseConnection(oCommand.Connection);
}
return dtableObject;
}
///// <summary>
///// Execute the stored procedure stored in oCommand
///// </summary>
///// <param name="oCommand">the Command to execute (stored procedure)</param>
///// <param name="oBlobParameters">the Oledb blob parameters to store the contents in</param>
///// <param name="oContents">the binary contents (in same order than oBlobParameters)</param>
//protected void ExecuteStoredProcedureWithBlob(SqlCommand oCommand, SqlParameter[] oBlobParameters, Byte[][] oContents)
//{
// // OledbParameter currentParameter;
// SqlTransaction oTransaction = null;
// OledbLob[] oOledbLobs = null;
// OledbConnection oConnection = ConnectionFactory.GetInstance.GetConnection(_ConnectionKey);
// try
// {
// OledbCommand blobCommands = new OledbCommand();
// oConnection.Open();
//
// // Create the transaction within the blob inserts will be executed
// oTransaction = oConnection.BeginTransaction();
// blobCommands.Connection = oConnection;
// blobCommands.Transaction = oTransaction;
// int i = 0;
// oOledbLobs = new OledbLob[oBlobParameters.Length];
// foreach (OledbParameter currentParameter in oBlobParameters)
// {
// // Create temporary blob Oledb parameters
// blobCommands.CommandText = "declare xx blob; begin dbms_lob.createtemporary(xx, false, 0); :tempblob" + i + ":= xx; end;";
// blobCommands.Parameters.Add(new OledbParameter("tempblob" + i, OledbType.Blob)).Direction = ParameterDirection.Output;
// blobCommands.Transaction = oTransaction;
// blobCommands.CommandType = CommandType.Text;
// blobCommands.ExecuteNonQuery();
// // We get the the previously created parameter from the command
// // and fill in the content with oContents argument
// oOledbLobs[i] = (OledbLob)blobCommands.Parameters[0].Value;
// oOledbLobs[i].BeginBatch(OledbLobOpenMode.ReadWrite);
// oOledbLobs[i].Write(oContents[i], 0, oContents[i].Length);
// // This operation is made under the same transaction than
// // the insert one (see oTransaction)
// oOledbLobs[i].EndBatch();
// oBlobParameters[i].Value = oOledbLobs[i];
// i++;
// }
// // Then execute the main command
// oCommand.Connection = oConnection;
// oCommand.Transaction = oTransaction;
// oCommand.CommandType = CommandType.StoredProcedure;
// oCommand.ExecuteNonQuery();
// oCommand.Transaction.Commit();
// }
// catch (Exception e)
// {
// if (oConnection.State == ConnectionState.Open)
// {
// if (oTransaction != null)
// {
// oTransaction.Rollback();
// }
// }
// if (oCommand != null)
// {
// Exception ex = new Exception(oCommand.CommandText, e);
// Log.Error(ex, true);
// }
// else
// {
// Log.Error(e, true);
// }
// }
// finally
// {
// ConnectionFactory.GetInstance.CloseConnection(oCommand.Connection);
// }
//}
/// <summary>
/// Execute the SQL query and return a datable containing data.
/// </summary>
/// <param name="SQL"></param>
/// <returns>Datarow containing data</returns>
protected DataRow ExecuteGetDataRow(string strSQL)
{
// Variables
DataSet DsResult = new DataSet();
DataRow drowObject = null;
SqlDataAdapter oAdapter = new SqlDataAdapter();
SqlCommand oCommand = new SqlCommand();
try
{
oCommand.Connection = ConnectionFactory.GetInstance.GetConnection(_ConnectionKey);
oCommand.Connection.Open();
oCommand.CommandText = strSQL;
oAdapter.SelectCommand = oCommand;
oAdapter.Fill(DsResult);
if (DsResult.Tables.Count != 0 && DsResult.Tables[0].Rows.Count != 0)
{
drowObject = DsResult.Tables[0].Rows[0];
}
// Genetating an exception if more than one record has been found.
if (DsResult.Tables[0].Rows.Count > 1)
{
throw new Exception("More than 1 record found for stored procedure " + oCommand.CommandText + ".");
}
}
catch (Exception e)
{
bool rethrow = true;
if (oCommand != null)
{
Exception ex = new Exception(oCommand.CommandText, e);
Log.Error(ex, true);
}
else
{
Log.Error(e, true);
}
if (rethrow)
{
throw;
}
}
finally
{
ConnectionFactory.GetInstance.CloseConnection(oCommand.Connection);
}
return drowObject;
}
/// <summary>
/// Execute the SQL command and return a dataset containing data.
/// </summary>
/// <param name="oCommand">Oledb command</param>
/// <param name="strTableName">Name of the table to fill</param>
/// <param name="dsetObject">DataSet containing data</param>
protected void ExecuteGetDataSet(SqlCommand oCommand, string strTableName, DataSet dsetObject)
{
SqlDataAdapter oAdapter = new SqlDataAdapter();
try
{
oCommand.Connection = ConnectionFactory.GetInstance.GetConnection(_ConnectionKey);
oCommand.Connection.Open();
oCommand.CommandType = CommandType.StoredProcedure;
oAdapter.SelectCommand = oCommand;
oAdapter.Fill(dsetObject, strTableName);
}
catch (Exception e)
{
if (oCommand != null)
{
Exception ex = new Exception(oCommand.CommandText, e);
Log.Error(ex, true);
}
else
{
Log.Error(e, true);
}
}
finally
{
ConnectionFactory.GetInstance.CloseConnection(oCommand.Connection);
}
}
/// <summary>
/// Execute the SQL command and return a dataset containing multiple table.
/// </summary>
/// <param name="oCommand">Oledb command</param>
/// <param name="dsetObject">DataSet containing data</param>
protected void ExecuteGetDataSet(SqlCommand oCommand, DataSet dsetObject)
{
SqlDataAdapter oAdapter = new SqlDataAdapter();
try
{
oCommand.Connection = ConnectionFactory.GetInstance.GetConnection(_ConnectionKey);
oCommand.Connection.Open();
oCommand.CommandType = CommandType.StoredProcedure;
oAdapter.SelectCommand = oCommand;
oAdapter.Fill(dsetObject);
}
catch (Exception e)
{
if (oCommand != null)
{
Exception ex = new Exception(oCommand.CommandText, e);
Log.Error(ex, true);
}
else
{
Log.Error(e, true);
}
}
finally
{
ConnectionFactory.GetInstance.CloseConnection(oCommand.Connection);
}
}
protected void ExecuteSqlStatement(SqlCommand oCommand)
{
try
{
oCommand.Connection = ConnectionFactory.GetInstance.GetConnection(_ConnectionKey);
oCommand.Connection.Open();
oCommand.CommandType = CommandType.Text;
oCommand.ExecuteNonQuery();
}
catch (Exception e)
{
if (oCommand != null)
{
Exception ex = new Exception(oCommand.CommandText, e);
Log.Error(ex, true);
}
else
{
Log.Error(e, true);
}
}
finally
{
ConnectionFactory.GetInstance.CloseConnection(oCommand.Connection);
}
}
/// <summary>
/// Execute the stored procedure
/// </summary>
/// <param name="strSPName">Name of procedure to be executed</param>
/// <param name="parametersList">List of input parameter for stored procedure</param>
protected void ExecuteStoredProcedure(SqlCommand oCommand)
{
try
{
oCommand.Connection = ConnectionFactory.GetInstance.GetConnection(_ConnectionKey);
oCommand.Connection.Open();
oCommand.CommandType = CommandType.StoredProcedure;
oCommand.ExecuteNonQuery();
}
catch (Exception e)
{
if (oCommand != null)
{
Exception ex = new Exception(oCommand.CommandText, e);
Log.Error(ex, true);
}
else
{
Log.Error(e, true);
}
}
finally
{
ConnectionFactory.GetInstance.CloseConnection(oCommand.Connection);
}
}
/// <summary>
/// Execute the stored procedure
/// </summary>
/// <param name="strSPName">Name of procedure to be executed</param>
/// <param name="parametersList">List of input parameter for stored procedure</param>
protected int ExecuteStoredProcedureGetID(SqlCommand oCommand)
{
int RetValue = 0;
try
{
oCommand.Connection = ConnectionFactory.GetInstance.GetConnection(_ConnectionKey);
oCommand.Connection.Open();
oCommand.CommandType = CommandType.StoredProcedure;
oCommand.ExecuteNonQuery();
}
catch (Exception e)
{
if (oCommand != null)
{
Exception ex = new Exception(oCommand.CommandText, e);
Log.Error(ex, true);
}
else
{
Log.Error(e, true);
}
}
finally
{
ConnectionFactory.GetInstance.CloseConnection(oCommand.Connection);
}
return RetValue;
}
/// <summary>
/// Execute the stored procedure list under same transaction.
/// </summary>
/// <param name="strSPName">Name of procedure to be executed</param>
/// <param name="commandList">List of Command object to be executed under same transaction</param>
protected int ExecuteStoredProcedure(SqlCommand[] commandList)
{
SqlTransaction oTransaction = null;
string currentProcedure = "";
int count = 0;
SqlConnection oConn = ConnectionFactory.GetInstance.GetConnection(_ConnectionKey);
try
{
oConn.Open();
////InitializeContext(oConn);
oTransaction = oConn.BeginTransaction();
foreach (SqlCommand command in commandList)
{
command.Transaction = oTransaction;
command.Connection = oConn;
command.CommandType = CommandType.StoredProcedure;
currentProcedure = command.CommandText;
count += command.ExecuteNonQuery();
}
oTransaction.Commit();
}
catch (Exception e)
{
if (oConn.State == ConnectionState.Open)
{
if (oTransaction != null)
{
oTransaction.Rollback();
}
}
Log.Error(e, true);
}
finally
{
ConnectionFactory.GetInstance.CloseConnection(oConn);
}
return count;
}
/// <summary>
/// Get the Next seed value of auto generated column.
/// </summary>
/// <param name="strSequenceName">Name of sequence to be get</param>
/// <returns>Next seed value</returns>
protected int GetNextSequence(string strSequenceName)
{
// Variables
int intReturn = -1;
SqlCommand oCommand = new SqlCommand();
SqlDataReader oReader;
try
{
oCommand.Connection = ConnectionFactory.GetInstance.GetConnection(_ConnectionKey);
oCommand.Connection.Open();
oCommand.CommandText = "SELECT " + strSequenceName + ".NEXTVAL SEQVALUE FROM DUAL";
oReader = oCommand.ExecuteReader();
// Generating string
if (oReader.Read())
{
intReturn = int.Parse(oReader["SEQVALUE"].ToString());
}
}
catch (Exception e)
{
if (strSequenceName != null)
{
Exception ex = new Exception(strSequenceName, e);
Log.Error(ex, true);
}
else
{
Log.Error(e, true);
}
}
finally
{
ConnectionFactory.GetInstance.CloseConnection(oCommand.Connection);
}
// Returning string.
return intReturn;
}
/// <summary>
/// Return Oledb parameter object.
/// </summary>
/// <param name="paramName">Oledb parameter name</param>
/// <param name="paramType">Oledb parameter type</param>
/// <param name="paramValue">Oledb parameter value</param>
/// <returns>Oledb parameter</returns>
protected SqlParameter GetParameter(string paramName, SqlDbType paramType, object paramValue)
{
return GetParameter(paramName, paramType, paramValue, 0);
}
/// <summary>
/// Return Oledb parameter object.
/// </summary>
/// <param name="paramName">Oledb parameter name</param>
/// <param name="paramType">Oledb parameter type</param>
/// <param name="paramValue">Oledb parameter value</param>
/// <param name="paramSize">Oledb parameter size</param>
/// <returns>Oledb parameter</returns>
protected SqlParameter GetParameter(string paramName, SqlDbType paramType, object paramValue, int paramSize)
{
SqlParameter oParameter = new SqlParameter();
oParameter.ParameterName = paramName;
oParameter.SqlDbType = paramType;
if (paramType == SqlDbType.VarChar || paramType == SqlDbType.VarBinary)
{
oParameter.Value = (paramValue == null) ? "" : (string)paramValue;
}
else
{
oParameter.Value = paramValue;
}
if (paramType == SqlDbType.VarChar || paramType == SqlDbType.Int)
{
if (paramSize != 0)
{
oParameter.Size = paramSize;
}
}
oParameter.Direction = ParameterDirection.Input;
return oParameter;
}
#endregion Methods and Implementations
#region InnerClass ConnectionFactory
/// <summary>
/// It give connection objects. It is a singleton class.
/// </summary>
private sealed class ConnectionFactory
{
#region Variables
/// <summary>
/// Static memeber of connection factory class.
/// </summary>
private static ConnectionFactory _instance = new ConnectionFactory();
#endregion Variables
#region Properties
/// <summary>
/// Static property, it returns the static private member of connection factory. Its
/// for implementing singleton.
/// </summary>
public static ConnectionFactory GetInstance
{
get
{
return _instance;
}
}
#endregion Properties
#region Methods & Implementation
/// <summary>
/// Construtor of connection factory.
/// </summary>
private ConnectionFactory()
{
}
/// <summary>
/// Returns Oledb connection object.
/// </summary>
/// <returns>Oledb connection object.</returns>
public SqlConnection GetConnection(string ConnectionKey)
{
//string myConnString = ConfigurationManager.ConnectionStrings[ConnectionKey].ToString();
// To use encrypted password
string myConnString = ConfigurationManager.ConnectionStrings[ConnectionKey].ToString();
//string[] pwd = myConnString.Split(';');
//string pass = pwd[pwd.Length - 1];
//string passWord = pass.Substring(4); //Swash.Frameworks.Password.DecryptBase64(pass.Substring(4));
//myConnString = pwd[0] + ";" + pwd[1] + ";" + pwd[2] + ";pwd=" + passWord;
SqlConnection oConn = new SqlConnection(myConnString);
return oConn;
}
/// <summary>
/// Close the connection state.
/// </summary>
/// <param name="oConn">Connection object to be closed.</param>
public void CloseConnection(SqlConnection oConn)
{
if (oConn != null)
{
if (oConn.State == ConnectionState.Open)
{
oConn.Close();
}
oConn.Dispose();
}
}
private string EncryptBase64(string thePassword)
{
try
{
byte[] encData_byte = new byte[thePassword.Length];
encData_byte = System.Text.Encoding.UTF8.GetBytes(thePassword);
string encodedData = Convert.ToBase64String(encData_byte);
return encodedData;
}
catch (Exception ex)
{
throw new Exception("Error in EncryptBase64" + ex.Message);
}
}
private string DecryptBase64(string thePassword)
{
try
{
System.Text.UTF8Encoding encoder = new System.Text.UTF8Encoding();
System.Text.Decoder utf8Decode = encoder.GetDecoder();
byte[] todecode_byte = Convert.FromBase64String(thePassword);
int charCount = utf8Decode.GetCharCount(todecode_byte, 0, todecode_byte.Length);
char[] decoded_char = new char[charCount];
utf8Decode.GetChars(todecode_byte, 0, todecode_byte.Length, decoded_char, 0);
string result = new String(decoded_char);
return result;
}
catch (Exception ex)
{
throw new Exception("Error in DecryptBase64" + ex.Message);
}
}
#endregion Methods & Implementation
}
#endregion InnerClass ConnectionFactory
}
}
Now the Code Inside_ERPDataAccess_.cs is:-
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace DataAccessLayer
{
public partial class NtierTestTableDAL : AbstractData_SqlClient
{
#region Code for Singleton Instance of the Data Layer
#region Variables
/// <summary>
/// Private static member to implement singleton
/// </summary>
private static NtierTestTableDAL instance = new NtierTestTableDAL();
#endregion Variables
#region Properties
/// <summary>
/// Static property of class which will give singleton instance of it.
/// </summary>
/// <returns>return singleton instance of ClientData class</returns>
public static NtierTestTableDAL GetInstance
{
get
{
return instance;
}
}
#endregion Properties
#endregion
#region Specify Database Connection String
private NtierTestTableDAL()
{
this.ConnectionKey = "LinqdemoPracticeConnectionString";
}
#endregion
public System.Data.DataTable GetParentCompany()
{
throw new NotImplementedException();
}
public System.Data.DataTable SelectNtierTestTable(List<BusinessObject.NtierTestTableObject> objBOList)
{
throw new NotImplementedException();
}
}
}
Now the Code Inside NtierTestTableDAL.cs is:-
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BusinessObject;
using System.Data;
using System.Data.SqlClient;
namespace DataAccessLayer
{
public partial class NtierTestTableDAL
{
public void InsertNtierTestTable(NtierTestTableObject objBO)
{
SqlCommand InsertNtierCMD = new SqlCommand("TestNtier");
InsertNtierCMD.CommandType = CommandType.StoredProcedure;
InsertNtierCMD.Parameters.AddWithValue("@Operation", objBO.Operation);
InsertNtierCMD.Parameters.AddWithValue("@NID",objBO.NID);
InsertNtierCMD.Parameters.AddWithValue("@NName", objBO.NName);
InsertNtierCMD.Parameters.AddWithValue("@NPhone", objBO.NPhone);
InsertNtierCMD.Parameters.AddWithValue("@NGender", objBO.NGender);
ExecuteStoredProcedure(InsertNtierCMD);
}
public DataTable SelectNtierTestTable()
{
DataTable dt = new DataTable();
SqlCommand SelectNtierCMD = new SqlCommand("TestNtier");
SelectNtierCMD.CommandType = CommandType.StoredProcedure;
SelectNtierCMD.Parameters.AddWithValue("@Operation", "Select");
dt=ExecuteGetDataTable(SelectNtierCMD);
return dt;
}
}
}
Now take another class library(goto solution-->right click and add new project and select class library) and name it as IntegrationLayer
Now it will display like this:-
Now within that library create one class as NtierTestTableIL.cs.
Now the Code Inside NtierTestTableIL.cs is:-
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using BusinessObject;
using DataAccessLayer;
namespace IntegrationLayer
{
public partial class NtierTestTableIL
{
public static void InsertNtierTestTable(NtierTestTableObject objBO)
{
NtierTestTableDAL.GetInstance.InsertNtierTestTable(objBO);
}
public static List<NtierTestTableObject> SelectNtierTestTable()
{
List<NtierTestTableObject> objBOList = new List<NtierTestTableObject>();
DataTable dtIntigration = new DataTable();
dtIntigration = NtierTestTableDAL.GetInstance.SelectNtierTestTable();
foreach (DataRow dtRow in dtIntigration.Rows)
{
NtierTestTableObject objBOO = new NtierTestTableObject();
objBOO.NID =int.Parse( dtRow["NID"].ToString());
objBOO.NName = dtRow["NName"].ToString();
objBOO.NPhone = dtRow["NPhone"].ToString();
objBOO.NGender = dtRow["NGender"].ToString();
objBOList.Add(objBOO);
}
return objBOList;
}
}
}
Now take another class library(goto solution-->right click and add new project and select class library) and name it as CacheLayer
Now it will display like this:-
Now within that library create one class and name it as NtierTestTableCL.cs
and the code looks Like as:-
Now the Code Inside NtierTestTableCL.cs is:-
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using IntegrationLayer;
using BusinessObject;
namespace CacheLayer
{
public partial class NtierTestTableCL
{
public static void InsertNtierTestTable(NtierTestTableObject objBO)
{
NtierTestTableIL.InsertNtierTestTable(objBO);
}
public static List<NtierTestTableObject> SelectNtierTestTable()
{
return NtierTestTableIL.SelectNtierTestTable();
}
}
}
Now take another class library(goto solution-->right click and add new project and select class library) and name it as BusinessAccessLayer
Now it will display like this:-
Now within that library create one class and name it as NtierTestTableBAL.cs
Now the Code Inside NtierTestTableBAL.cs is:-
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BusinessObject;
using CacheLayer;
namespace BusinessAccessLayer
{
public partial class NtierTestTableBAL
{
public static void InsertNtierTestTable(NtierTestTableObject objBO)
{
try
{
NtierTestTableCL.InsertNtierTestTable(objBO);
}
catch (Exception ex)
{
throw (new Exception("New Ntier.InsertNtierTestTable()", ex));
}
}
public static List<NtierTestTableObject> SelectNtierTestTable()
{
try
{
return NtierTestTableCL.SelectNtierTestTable();
}
catch (Exception ex)
{
throw (new Exception("New Ntier.SelectNtierTestTable()", ex));
}
}
}
}
Now coming to presentation layer
Add one ASPX page inside the project and name it as NtierTest.aspx
Now it will display like this:-
Now the Source Code Of My NtierTest.aspx is:-
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="NtierTest.aspx.cs" Inherits="NtierTest" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<style type="text/css">
.style1
{
width: 48%;
border-style: solid; background-position: center center; padding: 10px 20px 10px 20px;
height: 246px;
margin-left: 0px;
border-radius:50px;
}
.style5
{
width: 48%;
border-style: solid; background-position: center center; padding: 10px 20px 10px 20px;
height: 433px;
margin-left: 0px;
border-radius:50px;
}
.style2
{
width: 189px;
}
.style3
{
width: 56px;
}
.style4
{
width: 104px;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div>
<table class="style1" align="center" bgcolor="#999966" frame="box"
style="border-style: solid; border-width: 50px; border-color: #666633 #336600 #0000FF #800000;" >
<tr>
<td class="style3">
ID:-</td>
<td class="style2">
<asp:TextBox ID="TxtID" runat="server"></asp:TextBox>
</td>
<td>
</td>
<td class="style4">
Name:-</td>
<td>
<asp:TextBox ID="TxtName" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td class="style3">
</td>
<td class="style2">
</td>
<td>
</td>
<td class="style4">
</td>
<td>
</td>
</tr>
<tr>
<td class="style3">
Phone:-</td>
<td class="style2">
<asp:TextBox ID="TxtPhone" runat="server"></asp:TextBox>
</td>
<td>
</td>
<td class="style4">
Gender:-</td>
<td>
<asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td class="style3">
</td>
<td class="style2">
</td>
<td>
<asp:Button ID="BtnInsert" runat="server" onclick="BtnInsert_Click"
Text="insert" />
<asp:Button ID="BtnSelect" runat="server" Text="Select"
onclick="BtnSelect_Click" />
</td>
<td class="style4">
</td>
<td>
</td>
</tr>
</table>
</div>
<asp:ScriptManager ID="ScriptManager1" runat="server">
</asp:ScriptManager>
<diV align="center">
<div align="center">
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false">
<Columns>
<asp:BoundField HeaderText="ID" DataField="NID" />
<asp:BoundField HeaderText="Name" DataField="NName" />
<asp:BoundField HeaderText="Phone No" DataField="NPhone" />
<asp:BoundField HeaderText="Gender" DataField="NGender" />
</Columns>
</asp:GridView>
</div>
<br />
<br />
<br />
<br />
<br />
<table class="style5" align="center" bgcolor="#999966" frame="box"
style="border-style: solid; border-width: 50px; border-color: #666633 #336600 #0000FF #800000; background-color: #FFFFFF;" >
<tr>
<td class="style3" align="left" valign="top">
<asp:Button ID="BtnTime0"
style=" border-radius:90px; -moz-border-radius-topleft: 50px 30px; -moz-border-radius-bottomright: 500px; background-color:Black; margin-left:-23px;margin-top:-15px; background-color:Black; border-color:Maroon; color:Black;" runat="server"
Height="85px"
Width="80px" />
</td>
<td class="style2">
</td>
<td>
</td>
<td class="style4">
</td>
<td align="right" valign="top">
<asp:Button ID="BtnTime1"
style=" border-radius:90px; -moz-border-radius-topright: 50px 30px; -moz-border-radius-bottomleft: 500px; background-color:Black; margin-right:-23px;margin-top:-15px; border-color:Maroon; color:Black;" runat="server"
Height="85px" Text="Button"
Width="80px" />
</td>
</tr>
<tr>
<td class="style3">
</td>
<td class="style2">
</td>
<td>
</td>
<td class="style4">
</td>
<td>
</td>
</tr>
<tr>
<td class="style3">
</td>
<td class="style2">
</td>
<td>
<asp:UpdatePanel ID="UpdatePanel1" runat="server">
<ContentTemplate>
<asp:Timer ID="Timer1" runat="server" Enabled="true" Interval="1000" ontick="Timer1_Tick">
</asp:Timer>
<asp:Button ID="BtnTime"
style="background-image: -moz-linear-gradient(bottom, rgb(14,41,34) 38%, rgb(168,25,168) 73%, rgb(97,153,47) 100%); border-radius:90px; border-top-radius:1000px; background-color:Green; border-color:Maroon; color:Red;" runat="server"
Height="85px" Text="Button"
Width="80px" onclick="BtnTime_Click" />
</ContentTemplate>
</asp:UpdatePanel>
</td>
<td class="style4">
</td>
<td>
</td>
</tr>
<tr>
<td class="style3" align="left" valign="bottom">
<asp:Button ID="BtnTime3"
style=" border-radius:90px; -moz-border-radius-bottomleft: 50px 30px; -moz-border-radius-topright: 500px; background-color:Black; margin-left:-25px;margin-bottom:-15px; background-color:Black; border-color:Maroon; color:Black;" runat="server"
Height="85px" Text="Button"
Width="80px" />
</td>
<td class="style2">
</td>
<td>
</td>
<td class="style4">
</td>
<td align="right" valign="bottom">
<asp:Button ID="BtnTime2"
style=" border-radius:90px; margin-right:-23px;margin-bottom:-15px; -moz-border-radius-bottomright: 50px 30px; -moz-border-radius-topleft: 500px; background-color:Black; border-color:Maroon; color:Black;" runat="server"
Height="85px" Text="Button"
Width="80px" />
</td>
</tr>
</table>
</diV>
</form>
</body>
</html>
Now the Design View Of NtierTest.aspx is:-
Now the Code Behind Of NtierTest.aspx.cs is:-
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using BusinessObject;
using BusinessAccessLayer;
using Framework;
public partial class NtierTest : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void BtnInsert_Click(object sender, EventArgs e)
{
NtierTestTableObject objBO = new NtierTestTableObject();
objBO.Operation = "Insert";
objBO.NID = int.Parse(TxtID.Text);
objBO.NName = TxtName.Text;
objBO.NPhone = TextBox3.Text;
objBO.NGender = TxtPhone.Text;
NtierTestTableBAL.InsertNtierTestTable(objBO);
Page.RegisterStartupScript("ScriptDescription", "<script type=\"text/javascript\">alert('Data Inserted Successfully');</script>");
}
protected void Timer1_Tick(object sender, EventArgs e)
{
BtnTime.Text = System.DateTime.Now.ToShortTimeString();
if (System.DateTime.Now.ToShortTimeString() == "6:10 PM")
{
Response.Write("Hey friend this is the time to check");
Response.Write(System.DateTime.Now.ToShortTimeString());
}
}
protected void BtnTime_Click(object sender, EventArgs e)
{
BtnSelect.Text = "select";
Page.RegisterStartupScript("ScriptDescription", "<script type=\"text/javascript\">alert('The Time Is " + BtnTime.Text + " ');</script>");
}
protected void BtnSelect_Click(object sender, EventArgs e)
{
List<NtierTestTableObject> objBO = new List<NtierTestTableObject>();
objBO = NtierTestTableBAL.SelectNtierTestTable();
var ShowData = from n in objBO select n;
GridView1.DataSource = ShowData;
GridView1.DataBind();
}
}
Now the Code Of Web.config is:-
<?xml version="1.0"?>
<!--
For more information on how to configure your ASP.NET application, please visit
http://go.microsoft.com/fwlink/?LinkId=169433
-->
<configuration>
<connectionStrings>
<add name="LinqdemoPracticeConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\LinqdemoPractice.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>
</connectionStrings>
<system.web>
<compilation debug="true" targetFramework="4.0">
<assemblies>
<add assembly="System.Data.Linq, Version=4.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/>
</assemblies>
</compilation>
</system.web>
</configuration>
Here is my Store ProceDure TestNtier
Create PROCEDURE TestNtier
(@Operation varchar(50)=null,
@NID int=0,
@NName varchar(50)=null,
@NPhone varchar(50)=null,
@NGender varchar(50)=null
)
AS
Begin
if @Operation='Insert'
begin
insert into NtierTestTable values(@NID,@NName,@NPhone,@NGender)
end
else if @Operation='Update'
begin
update NtierTestTable set NID=@NID,NName=@NName,NPhone=@NPhone,NGender=@NGender
end
else if @Operation='Select'
begin
SELECT NID, NName, NPhone, NGender
FROM NtierTestTable
end
End
Here is my DataTypes of My Table NtierTestTable
After runing the page NtierTest.aspx it will display as below:-
After Click on the Select Button It will show the data in gridview as:-
Shibashish Mohanty
This is awesome!! really helpful for me. Thanks for sharing with us. Following links also helped me to complete my task.
ReplyDeletehttp://www.codeproject.com/Articles/439688/Creating-ASP-NET-application-with-n-tier-architect
http://www.mindstick.com/Articles/d36ceb0f-018c-4979-b2f5-a4a1e616cb5b/default.aspx?N+Tier+Architecture+in+ASP+NET