insert into datbase using stored procedure and .net code by standard form control

In this blog we are going to show that how you will store the data inside a data base from a simple form using the store procedure

Database coding

Use the below code to use a particular database

// USE name_of_the_database

USE DB02TEST01  

Create a table to insert data using the precedure

 CREATE TABLE EtaxiSystem_1937935(
 BookingReferenceNumber INT PRIMARY KEY IDENTITY(100000,1),
 PassengerName VARCHAR(50),
 FromLocation VARCHAR(50),
 ToLocation VARCHAR(50),
 DepartureTime DATETIME,
 DistanceinKm INT,
 EstimatedAmount INT,
 CabType VARCHAR(50)
 )  

Use the below code to see the table data It is present

 select * from EtaxiSystem_1937935

Execute the below code to create a procedure in sql server

Below is the code for stored procedure

CREATE PROCEDURE ETaxiProcedure
 ( 
 @PassengerName VARCHAR(50),
 @FromLocation VARCHAR(50),
 @ToLocation VARCHAR(50),
 @DepartureTime DATETIME,
 @DistanceinKm INT,
 @EstimatedAmount INT,
 @CabType VARCHAR(50),
 @BookingReferenceNumber INT OUT
 )
 AS
 BEGIN

 INSERT INTO EtaxiSystem_1937935 VALUES 
 (
 @PassengerName,
 @FromLocation,
 @ToLocation,
 @DepartureTime,
 @DistanceinKm,
 @EstimatedAmount,
 @CabType)
 SET @BookingReferenceNumber = @@IDENTITY
 END

Execute the code to see your procedure is working or not

You can use the below code for checking your stored procedure

DECLARE @BookingReferenceNumber1 INT
EXEC ETaxiProcedure 'Rumman', 'Kolkata', 'Kerala', '12.02.1996', 1, 10, 'indigo', @BookingReferenceNumber1 OUT
PRINT @BookingReferenceNumber1 

Now see the table details using the below code again

SELECT * FROM EtaxiSystem_1937935

.NET Coding

File Name: TravelDetailsInsert.aspx

 <form runat="server">

<div class="container">

    <div class="row">
  <div class="col-sm-3"> </div>
  <div class="col-sm-6" style="background-color:lavenderblush;">



    <table class="table table-hover">
        <tr>
            <td>
                <asp:Label ID="Label1" runat="server" Text="Passenger Name"></asp:Label> </td>
            <td> <asp:TextBox ID="PassengerName" runat="server"></asp:TextBox>
                <asp:RegularExpressionValidator ID="RegularExpressionValidator1" runat="server" ErrorMessage="* Please Enter Characters and space only" ControlToValidate="PassengerName" ValidationExpression="[a-zA-Z ]*$"></asp:RegularExpressionValidator>
                <asp:RequiredFieldValidator ID="RequiredFieldValidator5" runat="server" ControlToValidate="PassengerName" ErrorMessage="Enter passenger name"></asp:RequiredFieldValidator>
            </td>
        </tr>
        <tr>
            <td>
                <asp:Label ID="Label2" runat="server" Text="From Location"></asp:Label> </td>
            <td>
                <asp:DropDownList ID="FromLocation" runat="server" AutoPostBack="true">
                    <asp:ListItem>Please Select</asp:ListItem>
                    <asp:ListItem>Thiruvananthapuram</asp:ListItem>
                    <asp:ListItem>Kochi</asp:ListItem>
                    <asp:ListItem>Kozhikode</asp:ListItem>
                    <asp:ListItem>Kollam</asp:ListItem>
                    <asp:ListItem>Thrissur</asp:ListItem>
                    <asp:ListItem>Kannur</asp:ListItem>
                    <asp:ListItem>Alappuzha</asp:ListItem>
                </asp:DropDownList> 
            <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ErrorMessage="Select the Base location" ControlToValidate="FromLocation"></asp:RequiredFieldValidator></td>
            
        </tr>
         <tr>
            <td> <asp:Label ID="Label3" runat="server" Text="To Location"></asp:Label> </td>
            <td> <asp:DropDownList ID="ToLocation" runat="server"  AutoPostBack="true"  OnSelectedIndexChanged="ToLocation_SelectedIndexChanged1">
                <asp:ListItem>Thiruvananthapuram</asp:ListItem>
                    <asp:ListItem>Kochi</asp:ListItem>
                    <asp:ListItem>Kozhikode</asp:ListItem>
                    <asp:ListItem>Kollam</asp:ListItem>
                    <asp:ListItem>Thrissur</asp:ListItem>
                    <asp:ListItem>Kannur</asp:ListItem>
                    <asp:ListItem>Alappuzha</asp:ListItem>
                </asp:DropDownList>
                <asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ErrorMessage="Select Your Destination Location" ControlToValidate="ToLocation"></asp:RequiredFieldValidator>
            <asp:CustomValidator ID="CustomValidator1" runat="server" ErrorMessage="TO and FROM is same" ControlToValidate="ToLocation"></asp:CustomValidator>
                <asp:Label ID="Label6" runat="server" Text="Label" ForeColor="#FF3399"></asp:Label>
            </td>
        </tr>
         <tr>
            <td>  <asp:Label ID="Label4" runat="server" Text="DepartureTime"></asp:Label> </td>
            <td> <asp:TextBox ID="DepartureTime" runat="server" Type="date"></asp:TextBox>
                <asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" ErrorMessage="Select a Date" ControlToValidate="DepartureTime"></asp:RequiredFieldValidator>
            </td>
        </tr>
         <tr>
            <td> <asp:Label ID="Label5" runat="server" Text="Distance in Km "></asp:Label>  </td>
            <td> <asp:TextBox ID="DistanceinKm" runat="server" onTextChanged="DistanceinKm_TextChanged" AutoPostBack="true"  ></asp:TextBox>
                <%-- <asp:Button ID="Button2" runat="server" Text="See Car Rent" OnClick="TextBox2_TextChanged"  />--%>
                <asp:RequiredFieldValidator ID="RequiredFieldValidator4" runat="server" ErrorMessage="Select the distance" ControlToValidate="DistanceinKm"></asp:RequiredFieldValidator>
            </td>
        </tr>
         <tr>
            <td> <asp:Label ID="EstimatedAmountText" runat="server" Text="EstimatedAmount"></asp:Label> </td>
            <td>  <asp:TextBox ID="EstimatedAmount" runat="server"></asp:TextBox> </td>
        </tr>
         <tr>
            <td> <asp:Label ID="Label7" runat="server" Text="CabType"></asp:Label> </td>
            <td> <asp:RadioButtonList ID="CabType" runat="server">
                <asp:ListItem>Indigo</asp:ListItem>
                <asp:ListItem>Innova </asp:ListItem>
                </asp:RadioButtonList></td>
        </tr>
        <tr>
         <td colspan="1"> </td>
            <td>
                <asp:Button ID="Button1" runat="server" Text="Button" CssClass="button button-info" OnClick="Button1_Click" /> </td>
        </tr>

        </table>
    </form>
     </div>
      <div class="col-sm-3"> 



      </div> 
  </div>

TravelDetailsInsert.aspx.cs

Button event

        protected void Button1_Click(object sender, EventArgs e)
        {

            // int BookingReferenceNumber = 100000;
            string PassengerName1 = PassengerName.Text;
            string FromLocation1 = FromLocation.Text;
            string ToLocation1 = ToLocation.Text;
            string DepartureTime1 = DepartureTime.Text;
            string DistanceinKm1 = DistanceinKm.Text;
            string EstimatedAmount1 = EstimatedAmount.Text;
            string CabType1 = CabType.Text;


            BookCabClass carBookDetailsobj = new BookCabClass(PassengerName1, FromLocation1, ToLocation1, DepartureTime1, DistanceinKm1, EstimatedAmount1, CabType1);
            BookCabDBOperation dbObj = new BookCabDBOperation();
            int returnValue = dbObj.InsertBooksCarData(carBookDetailsobj);
            string message = "Data Saved Successfully with ID:" + returnValue;
            if (Convert.ToInt32(returnValue) > 0)
            {
                Response.Write(String.Format("<script>alert('{0}') </script>",message));
            }
        }

File name: BookCabClass.cs

    public class BookCabClass
    {

        int BookingReferenceNumber;
        string PassengerName;
        string FromLocation;
        string ToLocation;
        string DepartureTime;
        string DistanceinKm;
        string EstimatedAmount;
        string CabType;

        public BookCabClass(string passengerName, string fromLocation, string toLocation, string departureTime, string distanceinKm, string estimatedAmount, string cabType)
        {
          // BookingReferenceNumber1 = BookingReferenceNumber;
            PassengerName = passengerName;
            FromLocation = fromLocation;
            ToLocation = toLocation;
            DepartureTime = departureTime;
            DistanceinKm = distanceinKm;
            EstimatedAmount = estimatedAmount;
            CabType = cabType;
        }

        public int BookingReferenceNumber1 { get => BookingReferenceNumber; set => BookingReferenceNumber = value; }
        public string PassengerName1 { get => PassengerName; set => PassengerName = value; }
        public string FromLocation1 { get => FromLocation; set => FromLocation = value; }
        public string ToLocation1 { get => ToLocation; set => ToLocation = value; }
        public string DepartureTime1 { get => DepartureTime; set => DepartureTime = value; }
        public string DistanceinKm1 { get => DistanceinKm; set => DistanceinKm = value; }
        public string EstimatedAmount1 { get => EstimatedAmount; set => EstimatedAmount = value; }
        public string CabType1 { get => CabType; set => CabType = value; }
    }

File name: BookCabDBOperation.cs

To work with date base we created a new class

public class BookCabDBOperation
    {
        string conString = ConfigurationManager.ConnectionStrings["constring"].ConnectionString;
        public int InsertBooksCarData(BookCabClass carBookObj)
        {
            SqlConnection con = new SqlConnection(conString);
            con.Open();
            SqlCommand cmd = new SqlCommand("ETaxiProcedure", con); 
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@PassengerName", carBookObj.PassengerName1);
            cmd.Parameters.AddWithValue("@FromLocation", carBookObj.FromLocation1);
            cmd.Parameters.AddWithValue("@ToLocation", carBookObj.ToLocation1);
            cmd.Parameters.AddWithValue("@DepartureTime", carBookObj.DepartureTime1);
            cmd.Parameters.AddWithValue("@DistanceinKm", carBookObj.DistanceinKm1);
            cmd.Parameters.AddWithValue("@EstimatedAmount", carBookObj.EstimatedAmount1);
            cmd.Parameters.AddWithValue("@CabType", carBookObj.CabType1);
            cmd.Parameters.Add("@BookingReferenceNumber",SqlDbType.Int);
            cmd.Parameters["@BookingReferenceNumber"].Direction = ParameterDirection.Output;
            
            int rowsAffected = cmd.ExecuteNonQuery();
            if(rowsAffected >0)
            {
                carBookObj.BookingReferenceNumber1 = Convert.ToInt32(cmd.Parameters["@BookingReferenceNumber"].Value);
            }

            return carBookObj.BookingReferenceNumber1; 

        }