SQL Definition: Structured Query language (SQL) pronounced as “S-Q-L” or sometimes as “See-Quel”is actually the standard language for dealing with Relational Databases.

SQL programming can be effectively used to insert, search, update, delete database records.

That doesn’t mean SQL cannot do things beyond that.

In fact it can do lot of things including, but not limited to ,optimizing and maintenance of databases.

Database

Database is big logical unit .  A database is a systematic collection of data. Databases support storage and  manipulation of data. Databases make data management easy.

first go and Create a database.

Step 1

a1

2.Step Database name

a2

Table->

Create a new table which will have customer name, phone number, product name, bill amount and also create a customerid and this customer idmake a primary key.

Database-> customerDB-> table-> new table

a3

Fill the Values in table and Save.

a4

What is a Layer?

A layer is a reusable portion of code that performs a specific function.

In the .NET environment, a layer is usually set up as a project that represents this specific function. This specific layer is in charge of working with other layers to perform some specific goal.

Let’s briefly look at the latter situation first.

Presentation Layer

The Presentation Layer contains pages like .aspx or Windows Forms forms where data is presented to the user or input is taken from the user. The ASP.NET web site or Windows Forms application (the UI for the project) is called the Presentation Layer. The Presentation Layer is the most important layer simply because it’s the one that everyone sees and uses. Even with a well structured business and data layer, if the Presentation Layer is designed poorly, this gives the users a poor view of the system.

Advantages of a Three-Tier Architecture

The main characteristic of a Host Architecture is that the application and databases reside on the same host computer and the user interacts with the host using an unfriendly dumb terminal. This architecture does not support distributed computing (the host applications are unable to connect to a database of a strategically allied partner). Some managers find that developing a host application takes too long and it is expensive. These disadvantages consequently led to a Client-Server architecture.

Both reference to UI layer this is Business layer as will as Data layer.

a5

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using BusinessLogis;
using DALCustomer;
namespace WindowsFormsApplicationDemo
{
    public partial class CustomerUI : Form
    {
        public CustomerUI()
        {
            InitializeComponent();
       }
        private void btnSave_Click(object sender, EventArgs e)
        {
            try
            {
                Customer obj = new Customer();
                obj.CustomerName = txtCustomerName.Text;
                obj.MobileNumber = txtMobileNumber.Text;
                obj.ProductName = txtProductName.Text;
                obj.BillAmount = Convert.ToDecimal(txtBillAmount.Text);
               if (obj.validate())
                {
                    CustomerDAL dal = new CustomerDAL();
                    dal.add(obj);
                }
            }

            catch (Exception ex)

            {
                MessageBox.Show(ex.Message);
            }
        }
    }
}

Business Layer

A BAL contains business logic, validations or calculations related to the data.

Though a web site could talk to the data access layer directly, it usually goes through another layer called the Business Layer. The Business Layer is vital in that it validates the input conditions before calling a method from the data layer. This ensures the data input is correct before proceeding, and can often ensure that the outputs are correct as well. This validation of input is called business rules, meaning the rules that the Business Layer uses to make “judgments” about the data.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace BusinessLogis
{
    public class Customer
    {
       // public int CustomerID { get;}
       public string CustomerName { get; set; }
        public string MobileNumber { get; set; }
        public string ProductName { get; set; }
        public decimal BillAmount { get; set; }
        public Customer()
        {
           CustomerName = "";
            MobileNumber = "";
            ProductName = "";
            BillAmount = 0;
        }
         public bool validate()
        {
            if (CustomerName.Length == 0)
            {
                throw new Exception("customer Name is required");
            }
            if (MobileNumber.Length == 0)
            {
                throw new Exception("Phone Number is required");
            }
            if (ProductName.Length == 0)
            {
                throw new Exception("Product Name is required");
            }
            if (BillAmount == 0)
            {
                throw new Exception("Bill Amount is required");
            }
            return true;
        }
    }
}

Data Layer

A DAL contains methods that helps the Business Layer to connect the data and perform required actions, whether to return data or to manipulate data (insert, update, delete and so on).

Create DAL

a6

Name of DAL.

a7

Add the reference in DAL

a8

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using BusinessLogis;
namespace DALCustomer
{
    public class CustomerDAL
    {
        public bool add(Customer obj)
        {
            try
            {
 string constr  = @"Data Source=VANSH;InitialCatalog=CustomerDB;Integrated Security=True";                    
            // open connection
            SqlConnection con = new SqlConnection(constr);
            con.Open();
 SqlCommand cmd= new SqlCommand();
 cmd.Connection= con;
 cmd.CommandText = "insert into tbcustomer" + "(CustomerName,PhoneNumber,ProductName,BillAmount )" + "VALUES('" + obj.CustomerName + "', '" + obj.MobileNumber + "', '" + obj.ProductName + "', " + obj.BillAmount.ToString() + ")";
            cmd.ExecuteNonQuery();
            con.Close();
            return true;
            }
            catch (Exception exc)
            {
                MessageBox.Show(exc.Message);
            }
        }
    }
}

 

 

Advertisements