≡ Menu

Simple Data Access Class for Access database

After little time i have to wrote a simple data access layer for MS Access database,

For Connection string I wrote a simple Util Class as

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Configuration; 

/// <summary>
/// Summary description for Util
/// </summary>
public static  class Util
{

    public static string GetConnString()
    {
        return WebConfigurationManager.ConnectionStrings["myConnStr"].ConnectionString;
    }
}

 

Then i Wrote another class where Insert , update ,delete and simple select method placed as

Insert method

 public void InsertReviews(string _Nick, string _Name, string _EmailAddress, string _RealStateTitle, string _Address, string _City, string _ZipCode, string _Country, string _Comments, string _FromIP, string _Status, string _Langi, string _Lit)
    {
        string ConnString = Util.GetConnString();
        string SqlString = "Insert Into ClientInfo (Nick,Name,EmailAddress,RealStateTitle,Address,City,ZipCode,Country,Comments,FromIP,Status,Langi,Lit) Values (?,?,?,?,?,?,?,?,?,?,?,?,?)";
        using (OleDbConnection conn = new OleDbConnection(ConnString))
        {
            using (OleDbCommand cmd = new OleDbCommand(SqlString, conn))
            {
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.AddWithValue("Nick", _Nick);
                cmd.Parameters.AddWithValue("Name", _Name);
                cmd.Parameters.AddWithValue("EmailAddress", _EmailAddress);
                cmd.Parameters.AddWithValue("RealStateTitle", _RealStateTitle);
                cmd.Parameters.AddWithValue("Address", _Address);
                cmd.Parameters.AddWithValue("City", _City);
                cmd.Parameters.AddWithValue("ZipCode", _ZipCode);
                cmd.Parameters.AddWithValue("Country", _Country);
                cmd.Parameters.AddWithValue("Comments", _Comments);
                cmd.Parameters.AddWithValue("FromIP", _FromIP);
                cmd.Parameters.AddWithValue("Status", _Status);
                cmd.Parameters.AddWithValue("Langi", _Langi);
                cmd.Parameters.AddWithValue("Lit", _Lit);
                conn.Open();
                cmd.ExecuteNonQuery();
            }
        }

    }

Update Method

  public void UpdateReviews(int _ID,string _Nick, string _Name, string _EmailAddress, string _RealStateTitle, string _Address, string _City, string _ZipCode, string _Country, string _Comments, string _FromIP, string _Status, string _Langi, string _Lit)
    {
        string ConnString = Util.GetConnString();
        string SqlString = "Update ClientInfo Set Nick=?,Name=?,EmailAddress=?,RealStateTitle=?,Address=?,City=?,ZipCode=?,Country=?,Comments=?,FromIP=?,Status=?,Langi=?,Lit=? where IDs=?";
        using (OleDbConnection conn = new OleDbConnection(ConnString))
        {
            using (OleDbCommand cmd = new OleDbCommand(SqlString, conn))
            {
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.AddWithValue("Nick", _Nick);
                cmd.Parameters.AddWithValue("Name", _Name);
                cmd.Parameters.AddWithValue("EmailAddress", _EmailAddress);
                cmd.Parameters.AddWithValue("RealStateTitle", _RealStateTitle);
                cmd.Parameters.AddWithValue("Address", _Address);
                cmd.Parameters.AddWithValue("City", _City);
                cmd.Parameters.AddWithValue("ZipCode", _ZipCode);
                cmd.Parameters.AddWithValue("Country", _Country);
                cmd.Parameters.AddWithValue("Comments", _Comments);
                cmd.Parameters.AddWithValue("FromIP", _FromIP);
                cmd.Parameters.AddWithValue("Status", _Status);
                cmd.Parameters.AddWithValue("Langi", _Langi);
                cmd.Parameters.AddWithValue("Lit", _Lit);
                cmd.Parameters.AddWithValue("IDs", _ID);
               conn.Open();
                cmd.ExecuteNonQuery();
            }
        }
    }

Delete Method

 public void DelteReviews(int _ID)
    {
        string ConnString = Util.GetConnString();
        string SqlString = "Delete * From ClientInfo where IDs=?";
        using (OleDbConnection conn = new OleDbConnection(ConnString))
        {
            using (OleDbCommand cmd = new OleDbCommand(SqlString, conn))
            {
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.AddWithValue("IDs", _ID);
                conn.Open();
                cmd.ExecuteNonQuery();
            }
        }

    }

Select Method as

 public DataSet SelectReviews(int _ID)
    {
        string ConnString = Util.GetConnString();
        string SqlString = "Select * From ClientInfo where IDs=?";
        DataSet _ds = new DataSet();
        using (OleDbConnection conn = new OleDbConnection(ConnString))
        {
            using (OleDbCommand cmd = new OleDbCommand(SqlString, conn))
            {
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.AddWithValue("IDs", _ID);
                OleDbDataAdapter _Adp = new OleDbDataAdapter();
                _Adp.SelectCommand = cmd;
                _Adp.Fill(_ds);
                _Adp.Dispose();
                conn.Open();
            }

        }
        return _ds;
    }

Chears 🙂

Comments on this entry are closed.