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
<>SQL SERVER CREATE PROCEDURE WITH OUTPUT PARAMETERUse 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; }
