Knowledge Walls
Gopal Rao
Mumbai, Maharashtra, India
Passcode:
How to insert values into table in sql server from asp.net using 3 tier architecture
6520 Views
Introduction 
In this example program i explain about how to insert the values into the database using 3 tier architecture.
Preview of page 

When user clicks the submit buttom the four values insert into the database.
And also i have done asp validation for the fields.
.aspx
<asp:Content ID="Content1" ContentPlaceHolderID="HeadContent" runat="server">
    <style type="text/css">
        .style2
        {
        width: 439px;
        height: 35px;
        text-align: right;
    }
        .style3
        {
            height: 35px;
        }
        .style4
        {
            width: 439px;
            color: #003399;
            text-align: center;
        }
        .style5
        {
            color: #003399;
        }
        .style6
        {
        width: 439px;
        color: #003399;
        text-align: right;
        height: 35px;
    }
        .style7
        {
            height: 40px;
        }
        .style8
        {
            color: #003399;
            text-align: center;
            height: 33px;
        }
        </style>
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">
    <table style="width: 100%;">
        <tr>
            <td colspan="2" style="color: #003399; text-align: center" class="style7">
                &nbsp;
                &nbsp;
                &nbsp;
            <strong>Add CDs </strong>
            </td>
        </tr>
        <tr>
            <td class="style2" style="color: #003399">
                &nbsp;
                CD Number</td>
            <td class="style3">
                &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                <asp:TextBox ID="cdNo" runat="server" MaxLength="8" ValidationGroup="gr_addcds"></asp:TextBox>
            &nbsp;&nbsp;
                <asp:RequiredFieldValidator ID="re_cdno" runat="server"
                    ControlToValidate="cdNo" Display="Dynamic" ErrorMessage="Enter the Cd Number"
                    ForeColor="Red" ValidationGroup="gr_addcds"></asp:RequiredFieldValidator>
                <asp:RegularExpressionValidator ID="com_num" runat="server"
                    ControlToValidate="cdNo" Display="Dynamic" ErrorMessage="Only Integers Allowed"
                    ForeColor="Red" ValidationExpression="^([0-9]*|\d*\d{1}?\d*)$"
                    ValidationGroup="gr_addcds"></asp:RegularExpressionValidator>
            </td>
        </tr>
        <tr>
            <td class="style2">
                &nbsp;<span class="style5"> Title</span></td>
            <td class="style3">
                &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                <asp:TextBox ID="cdTitle" runat="server" ValidationGroup="gr_addcds"></asp:TextBox>
            &nbsp;&nbsp;
                <asp:RequiredFieldValidator ID="re_Title" runat="server"
                    ControlToValidate="cdTitle" Display="Dynamic" ErrorMessage="Enter the Title"
                    ForeColor="Red" ValidationGroup="gr_addcds"></asp:RequiredFieldValidator>
            </td>
        </tr>
        <tr>
            <td class="style6">
                Quantity</td>
            <td class="style3">
                &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                <asp:TextBox ID="cdQuantity" runat="server" MaxLength="8"
                    ValidationGroup="gr_addcds"></asp:TextBox>
            &nbsp;
                <asp:RequiredFieldValidator ID="re_quantity" runat="server"
                    ControlToValidate="cdQuantity" Display="Dynamic"
                    ErrorMessage="Enter the Quantity" ForeColor="Red"
                    ValidationGroup="gr_addcds"></asp:RequiredFieldValidator>
                <asp:RegularExpressionValidator ID="re_com_quan" runat="server"
                    ControlToValidate="cdQuantity" Display="Dynamic"
                    ErrorMessage="Only Integers Allowed" ForeColor="Red"
                    ValidationExpression="^([0-9]*|\d*\d{1}?\d*)$" ValidationGroup="gr_addcds"></asp:RegularExpressionValidator>
            </td>
        </tr>
        <tr>
            <td class="style6">
                Type</td>
            <td class="style3">
                &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                <asp:TextBox ID="cdType" runat="server" MaxLength="20"
                    ValidationGroup="gr_addcds"></asp:TextBox>
            &nbsp;&nbsp;
                <asp:RequiredFieldValidator ID="re_cdtype" runat="server"
                    ControlToValidate="cdType" Display="Dynamic" ErrorMessage="Enter the Type"
                    ForeColor="Red" ValidationGroup="gr_addcds"></asp:RequiredFieldValidator>
            </td>
        </tr>
        <tr>
            <td class="style4">
                &nbsp;</td>
            <td>
                &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                <asp:Button ID="add_cd" class="button_example" runat="server" Text="Submit"
                    onclick="add_cd_Click" Height="36px" ValidationGroup="gr_addcds" />
            </td>
        </tr>
        <tr>
            <td class="style8" colspan="2">
                <asp:Label runat=server ID="lbl_add"></asp:Label></td>
        </tr>
    </table>
</asp:Content>
.aspx.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using BO;
using BAL;

namespace cdManage
{
    public partial class addCds : System.Web.UI.Page
    {
        boAddCds boaddcds = new boAddCds();
        balAddCds baladd = new balAddCds();
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void add_cd_Click(object sender, EventArgs e)
        {
            boaddcds.cdNo = cdNo.Text;
            boaddcds.title = cdTitle.Text;
            boaddcds.quantity = cdQuantity.Text;
            boaddcds.type = cdType.Text;
            int value = baladd.balAddCd(boaddcds);
            if (value == 1)
            {
                cdNo.Text = "";
                cdTitle.Text = "";
                cdQuantity.Text = "";
                cdType.Text = "";
                lbl_add.Text = "Added successfully";
            }
            else
            {
                cdNo.Text = "";
                lbl_add.Text = "Entered Cd number already available in cd list.search cd number in update cds";
            }
        }
    }
}
BusinessObjects.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace BO
{
    public class boAddCds
    {
        private string _cdNo;

        public string cdNo
        {
            get { return _cdNo; }
            set { _cdNo = value; }
        }

        private string _title;

        public string title
        {
            get { return _title; }
            set { _title = value; }
        }
        private string _quantity;

        public string quantity
        {
            get { return _quantity; }
            set { _quantity = value; }
        }

        private string _type;

        public string type
        {
            get { return _type; }
            set { _type = value; }
        }

        private DateTime _dt;

        public DateTime dt
        {
            get { return _dt; }
            set { _dt = value; }
        }
        
    }
}
Bal.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BO;
using DAL;
namespace BAL
{
    public class balAddCds
    {
        dalAddCds daladdcds = new dalAddCds();
        public int balAddCd(boAddCds boaddcds)
        {
            try
            {
                return daladdcds.dalAddCd(boaddcds);
            }
            catch(Exception ex)
            {
                ex.GetType();
            }
            return 0;
        }
    }
}
Dal.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using BO;

namespace DAL
{
    public class dalAddCds
    {
        SqlConnection conn = new SqlConnection(@"data source=LAKSHANYA\SQLEXPRESS;database=master;integrated security=true");
        public int dalAddCd(boAddCds boaddcds)
        {
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("insert into addcds values(" + boaddcds.cdNo + ",'" + boaddcds.title + "'," + boaddcds.quantity + ",'" + boaddcds.type + "')", conn);
                cmd.ExecuteNonQuery();
                return 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
    }
}
Create sql table
CREATE TABLE addcds (
cdNo int null,
title varchar(30) null,
quantity int null,
type varchar(30) null
);
Using this code inseted values in database 
Best Lessons of "Asp.net/Ado.net/C#.net(Dotnet)"
Top lessons which are viewed more times.
  Copyright © 2014 Knowledge walls, All rights reserved
KnowledgeWalls
keep your tutorials and learnings with KnowledgeWalls. Don't lose your learnings hereafter. Save and revise it whenever required.
Click here for more details