Three-tier architecture implementation in Windows form application

Question

I am trying to insert data into a database using a three-tier architecture, but I am stuck and I cannot proceed further.

This is my code

First is UI part:

    public void assignField()
    {
        string maritalCondition = "";
        string sex = "";

        assignObj.Registered_Date = dateTimePicker1_Date.Value;
        assignObj.First_Name = txt_FirstName.Text;

        if (comboBox2_MaritalStatus.SelectedIndex == 0)
        {
            maritalCondition = "Single";
        }
        else
            maritalCondition = "Married";

        assignObj.Marital_Status = maritalCondition;

        if (RadioButton_Male.Checked == true)
            sex = "Male";
        else
            sex = "Female";
        assignObj.Gender = sex;

        this.txt_Age.Text = Convert.ToInt32(age).ToString();
    }

    private void btnRegister_Click(object sender, EventArgs e)
    {
        assignField();
    }

Next is the middle tier:

public class CustomerDataType
{
    private DateTime registered_Date;
    private string first_Name;
    private int age;
    private string marital_Status;
    private string gender;

    public DateTime Registered_Date
    {
        get { return registered_Date; }
        set { registered_Date = value; }
    }

    public string First_Name
    {
        get { return first_Name; }
        set { first_Name = value; }
    }

    public int Age
    {
        get { return age; }
        set { age = value; }
    }

    public string Marital_Status
    {
        get { return marital_Status; }
        set { marital_Status = value; }
    }

    public string Gender
    {
        get { return gender; }
        set { gender = value; }
    }

    public void insertInfo()
    {
        CustomerDataAccess insertObj = new CustomerDataAccess(Registered_Date, First_Name, Age, Marital_Status, Gender);

        insertObj.insertCustomerInfo();
    }
}

and last is the data access tier:

public class CustomerDataAccess
{
    public CustomerDataAccess(DateTime Registered_Date, string First_Name, int Age, string Marital_Status, string Gender)
    {
        this.registrationDate = Registered_Date;
        this.fName = First_Name;
        this.userAge = Age;
        this.marriageStatus = Marital_Status;
        this.userGender = Gender;
    }

    SqlConnection con;
    SqlCommand cmd;

    DateTime registrationDate;
    string fName = "";        
    int userAge;
    string marriageStatus;
    string userGender;

    public void insertCustomerInfo()
    {
         try
         {  
            con = new SqlConnection("Data Source=LAKHE-PC;Initial Catalog=Sahakari;Integrated Security=True");
            con.Open();
            cmd = con.CreateCommand();
            cmd.CommandText = "sp_registerCust";
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@Registered_Date", SqlDbType.DateTime);
            cmd.Parameters["@Registered_Date"].Value = registrationDate;
            cmd.Parameters.Add("@First_Name", SqlDbType.VarChar);
            cmd.Parameters["@First_Name"].Value = fName;
            cmd.Parameters.Add("@Age", SqlDbType.Int.ToString());
            cmd.Parameters["@Age"].Value = userAge;
            cmd.Parameters.Add("@Marital_Status", SqlDbType.VarChar);
            cmd.Parameters["@Marital_Status"].Value = marriageStatus;
            cmd.Parameters.Add("@Gender", SqlDbType.VarChar);
            cmd.Parameters["@Gender"].Value = userGender;

            cmd.ExecuteNonQuery();
            con.Close();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
}

Here with the stored procedure, there is no problem and and from SQL Server I can insert data into table easily. But from windows form, it does not insert data in table. Plz help me.

This bit doesn't look right: `cmd.Parameters.Add("@Age", SqlDbType.Int.ToString());` - surely you just want `SqlDbType.Int` — StuartLC, May 20 '13 at 06:50
Small tip: Auto-properties for brevity = less bugs (not the problem with your code, but everyone should use this): http://msdn.microsoft.com/en-us/library/bb384054.aspx — Gaute Løken, May 20 '13 at 06:59
Tip: if you already **have** a `CustomerDataType` - then use it! In the UI layer, create an instance of it and set its properties, and in the data access layer, use that type in your `insertCustomerInfo` method - not all separate parameters for each property..... — marc_s, May 20 '13 at 07:04
When logged into SQL Server are you using the same user your application uses? If not, make sure the user running the SP in your application has permission to execute. — Gibron, May 20 '13 at 07:07
This is an older article, but worth a read. Your "flavor" is "Passing Custom Business Entity Components As Inputs and Outputs" — granadaCoder, May 20 '13 at 13:10
Thank You for your help.I am able to store data in database. — shrawan_lakhe, May 21 '13 at 14:51

score 3 · Accepted Answer · answered May 20 '13 at 07:25

I'll do something like below

UI

CustomerHandler custHandler = new CustomerHandler();
// create Customer object and pass to insert method
if (custHandler.InsertCustomer(new Customer(){
    FirstName = txt_FirstName.Text, Registered_Date =dateTimePicker1_Date.Value,
    //decalare other parameters....
    ))
{
    // insert Success, show message or update label with succcess message
}

In my BL

public  class CustomerHandler
{
    // in BL you may have to call several DAL methods to perform one Task 
    // here i have added validation and insert 
    // in case of validation fail method return false
    public bool InsertCustomer(Customer customer)
    {
        if (CustomerDataAccess.Validate(customer))
        {
            CustomerDataAccess.insertCustomer(customer);
            return true;
        }
        return false;
    }
}

In MY DAL

// this is the class you going to use to transfer data across the layers 
public class Customer
{
    public DateTime Registered_Date { get; set; }
    public string FirstName { get; set; }
    //so on...

}

public class CustomerDataAccess
{
    public static void insertCustomer(Customer customer)
    {
        using (var con = new SqlConnection("Data Source=LAKHE-PC;Initial Catalog=Sahakari;Integrated Security=True"))
        using (var cmd = con.CreateCommand())
        {
            con.Open();
            cmd.CommandText = "sp_registerCust";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@Registered_Date", customer.Registered_Date);
            cmd.Parameters.AddWithValue("@FirstName", customer.FirstName);
            // so on...
            cmd.ExecuteNonQuery();

        }

    }

    internal static bool Validate(Customer customer)
    {
        // some validations before insert 
    }
}

score 1 · Answer 2 · edited May 23 '17 at 12:22

Your middle tier consists of classes holding the values you require in properties. Instead of writing the data access manually, try using the Entity Framework (EF) which does that for you. Here (at MSDN) you can find a quickstart example which shows you how you can use it.

Instead of mapping the fields manually and executing a query, the Entity Framework does that which means you just have to assign the values to the object's properties and call SaveChanges() - the SQL code is created and executed automatically by the EF.

For further reading, there is also a lot to find here (at Stackoverflow).

Three-tier architecture implementation in Windows form application

2 Answers2