You must Sign In to post a response.
  • Category: .NET

    Three tier application data access layer

    I am developing 3 tier application in windows based c# application.

    I want to create a common method in Dal for performing dml operations like insert, update, delete using ado.net and not using factory classes approach i.e. generic Dal.

    Following is sample structure of my method:

    // DTO is data transfer object or entity layer for passing values to DAL

    public string AddUpdDel(DTO objdto)
    {

    }

    How would I achieve it ?
  • #764913
    Hi,

    Please check below code. Here you will be just passing the SQL string which you want to execute.


    public class ClsConnection
    {
    SqlConnection SQLConn = new SqlConnection(ConfigurationManager.ConnectionStrings["conn"].ToString());
    SqlDataAdapter SQLDAdapter = new SqlDataAdapter();
    DataSet ds = new DataSet();
    SqlDataReader dr;
    SqlCommand SQLcmd = new SqlCommand();

    public ClsConnection()
    {
    }
    public DataSet selectDS(string sql)
    {
    if (SQLConn.State == ConnectionState.Open)
    {
    SQLConn.Close();
    }
    SQLConn.Open();
    SQLcmd.CommandText = sql;
    SQLcmd.Connection = SQLConn;
    SQLDAdapter.SelectCommand = SQLcmd;
    SQLDAdapter.Fill(ds);
    SQLConn.Close();
    return ds;

    }
    public void ExecuteSQLQuery(string strSql)
    {
    if (SQLConn.State == ConnectionState.Open)
    {
    SQLConn.Close();
    }
    SQLConn.Open();
    SQLcmd.CommandText = strSql;
    SQLcmd.Connection = SQLConn;
    SQLcmd.ExecuteNonQuery();
    SQLConn.Close();
    }
    public string ExecuteSQLSelectQuery(string strSql)
    {
    if (SQLConn.State == ConnectionState.Open)
    {
    SQLConn.Close();
    }
    SQLConn.Open();
    SQLcmd.CommandText = strSql;
    SQLcmd.Connection = SQLConn;
    string val = Convert.ToString(SQLcmd.ExecuteScalar());
    SQLConn.Close();
    return val;
    }
    public SqlDataReader ExeSelectReader(String sql)
    {
    if (SQLConn.State == ConnectionState.Open)
    {
    SQLConn.Close();
    }
    SQLConn.Open();
    SQLcmd.CommandText = sql;
    SQLcmd.Connection = SQLConn;
    SqlDataReader dr = SQLcmd.ExecuteReader();
    return dr;
    SQLConn.Close();
    }
    }


    Regards,
    Asheej T K
    Microsoft MVP[ASP.NET/IIS]
    DotNetSpider MVM

  • #764916
    Hi Jayesh,

    Perform CRUD operations in single method, is not a good practice. Because now you are performing small operations in future you may perform big tasks that time it's very difficult to manage it. So, my suggestion is create separate separate methods for Adding, Updating, Deleting. If you want to perform all at one method then pass the Action type as attribute and based on attribute type perform the action in that method.

    EX:

    public string AddUpdDel(DTO objdto)
    {
    if(objdto.ToString()=="Add")
    {
    //perform adding
    }
    else if(objdto.ToString()=="Update")
    {
    //perform Updating
    }
    }


    Hope this will helpful to you...

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #764950
    As asheej state, you can simple write class file with a INSERT, UPDATE and DELETE operation and can call it from any where from the project
    DAL stands for Data Access Layer,in this layer,we add all methods that has to do with opening connection to DB,insert,update,delete,fetching data with all queries and stuff like that.
    BLL however stands for Bussiness Logic Layer,basically this layers has a CALLING methods to DAL.
    i.e DAL has a method to insert Employee data into DB,now BLL has a method to call that DAL function (yes,that's mean the DAL has access directly to database,but BLL isn't)
    so DAL is the different from business logic layer

    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #764958

    Hai Jayesh,
    As per my understanding, DTO are simple model classes which can be passed through using a single entity.
    Like if you want to do the CRUD operations for an entity like Employee, you can first create the DTO for it as below:

    public class EmployeeDTO
    {
    public int EmployeeId { get; set;}
    public string Name { get; set;}
    public int OperationName {get; set;}
    }

    Here Operation name is the name of operation(Insert/Update/Delete etc).
    Take an enum to do so:

    enum OperationType
    {
    Insert:1,
    Update: 2,
    Delete:3
    }

    Now you can use this DTO object in your code as below:

    public string AddUpdDelEmployee(EmployeeDTO objdto)
    {
    int operationType = objdto.OperationName;
    int empId= objdto.EmployeeId;
    string name = objdto.Name;
    switch(opeartionType)
    {
    case OperationType.Insert:
    // code to insert records
    break;
    case OperationType.Update:
    // code to update records
    break;
    case OperationType.Delete:
    // code to delete records
    break;
    }
    }

    Hope it will be helpful to you.


    Regards,
    Pawan Awasthi(DNS MVM)
    +91 8123489140 (whatsApp), +60 14365 1476(Malaysia)
    pawansoftit@gmail.com

  • #764974
    1. Send the Object and the Operation based on the operation based on the operation handle that insert/delete/update.

    public string AddUpdDel(DTO objdto, string/Enum Operation)
    {
    if Operation is Add do the insert
    ...
    ...
    ...
    }

    By Nathan
    Direction is important than speed

  • #764981
    class Dto
    {
    enum OperationType
    {
    Insert:1,
    Update: 2,
    Delete:3
    }
    }

    class Dal
    {
    public string AddUpdDelEmployee(EmployeeDTO objdto)
    {
    1. How to access enum OperationType value from Dto class ?

    2. How to pass user's selected option for insert or update or delete from
    presentation layer to Dal via enum in Dto class ?
    }
    }

    Please suggest proper code.


Sign In to post your comments