using System;
using System.Data;
using System.Data.OleDb;

public class CustomerDataAccessLayer
{
    private static string DATA_FILE = "U:/nnLab4/App_Data/Customers.mdb";

    // Returns a connection to the Access database
    public static OleDbConnection getConnection()
    {
        string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + DATA_FILE;
        return new OleDbConnection(connectionString);
    }

    public static DataSet GetCustomerById(int id)
    {
        // Create the database command
        System.Data.OleDb.OleDbCommand dbCommand = new OleDbCommand();
        dbCommand.CommandText = "SELECT * FROM Customer WHERE (CustomerId = @id)";
        dbCommand.Connection = getConnection();

        // Specify the ID parameter
        System.Data.OleDb.OleDbParameter dbParam_id = new OleDbParameter();
        dbParam_id.ParameterName = "@id";
        dbParam_id.Value = id;
        dbParam_id.DbType = System.Data.DbType.Int32;
        dbCommand.Parameters.Add(dbParam_id);

        // Create a dataset to hold the results of the query
        System.Data.DataSet dataSet = new DataSet();

        // Create the data adapter and use it to fill the dataset
        System.Data.OleDb.OleDbDataAdapter dataAdapter = new OleDbDataAdapter();
        dataAdapter.SelectCommand = dbCommand;
        dataAdapter.Fill(dataSet);

        return dataSet;
    }

    public static DataSet GetCustomers()
    {
        // Create the database command
        OleDbCommand dbCommand = new OleDbCommand();
        dbCommand.CommandText = "SELECT * FROM Customer";
        dbCommand.Connection = getConnection();

        // Create a dataset to hold the results of the query
        System.Data.DataSet dataSet = new DataSet();

        // Create the data adapter and use it to fill the dataset
        System.Data.OleDb.OleDbDataAdapter dataAdapter = new OleDbDataAdapter();
        dataAdapter.SelectCommand = dbCommand;
        dataAdapter.Fill(dataSet);

        return dataSet;
    }

    public static int UpdateCustomer(int id, string fname, string lname, decimal credit, string phone)
    {
        // Create the database command
        OleDbCommand dbCommand = new OleDbCommand();
        dbCommand.CommandText = "UPDATE Customer SET FirstName=@fname, LastName=@lname, CreditLimit=@credit, PhoneNumber=@phone WHERE (CustomerId = @id)";

        //set the parameter values
        dbCommand.Parameters.AddWithValue("@fname", fname);
        dbCommand.Parameters.AddWithValue("@lname", lname);
        dbCommand.Parameters.AddWithValue("@credit", credit);
        dbCommand.Parameters.AddWithValue("@phone", phone);
        dbCommand.Parameters.AddWithValue("@id", id);

        int rowsAffected = 0;

        dbCommand.Connection = getConnection();
        try
        {
            dbCommand.Connection.Open();
            rowsAffected = dbCommand.ExecuteNonQuery();
        }
        finally
        {
            dbCommand.Connection.Close();
        }
        return rowsAffected;
    }


    public static int InsertCustomer(int id, string fname, string lname, decimal credit, string phone)
    {
        // Create the database command
        OleDbCommand dbCommand = new OleDbCommand();

        dbCommand.CommandText = "INSERT INTO [Customer] (CustomerId, FirstName, LastName, CreditLimit, PhoneNumber) VALUES (@id, @fName, @lName, @credit, @phone)";

        //set the parameter values        
        dbCommand.Parameters.AddWithValue("@id", id);
        dbCommand.Parameters.AddWithValue("@fname", fname);
        dbCommand.Parameters.AddWithValue("@lname", lname);
        dbCommand.Parameters.AddWithValue("@credit", credit);
        dbCommand.Parameters.AddWithValue("@phone", phone);

        int rowsAffected = 0;

        dbCommand.Connection = getConnection();
        try
        {
            dbCommand.Connection.Open();
            rowsAffected = dbCommand.ExecuteNonQuery();
        }
        finally
        {
            dbCommand.Connection.Close();
        }
        return rowsAffected;
    }


    public static int DeleteCustomer(int id)
    {
        OleDbCommand dbCommand = new OleDbCommand();
        dbCommand.CommandText = "DELETE FROM Customer WHERE CustomerId = @id";

        dbCommand.Parameters.AddWithValue("@id", id);

        int rowsAffected = 0;
        dbCommand.Connection = getConnection();
        try
        {
            dbCommand.Connection.Open();
            rowsAffected = dbCommand.ExecuteNonQuery();
        }
        finally
        {
            dbCommand.Connection.Close();
        }
        return rowsAffected;
    }
}