Introduction
If you are a new to SQL server and ASP.NET you always curious to know how to create a table in SQL server and connect databae form ASP.NET. This article will help you step by step from creating table, stored procedure and connect it form Asp.Net using C#. First we will create a database then we will insert data into from Asp.net page and retrieve data back from database and display in GridView.
In this article we will create a table to keep vehicle records where you can see one to many relationship betwen two tables, then we will create a stored proceure where you can see the join between two tables. We will use these stored procedures to retrieve data from database and insert data into database.
You can see the sample code written in Asp.net using C# to retrieve data from database and insert data into database. We will discuss how to connect Sql Server from Asp.net below in this article. We will talk why we use the Stored procedure instead of in-line sql statment in the code behind of aspx page.
I have used SQL Server 2008R2 and VS 2010 to build this sample. If you are using SQL Express, not the server than this connection string might not work for you.
A sample database "UsedCarManagement" is created. It has two tables Vehicle and VehicleMaintenanceRecord. Below diagram describe the relationship between these two tables.
Fig1
There is one to many relationship between table Vehicle and VehicleMaintenanceRecords. This database is just a sample database to store the vehicle information for Used Car Seller. Vehicle table keeps the general information about a Vehicle and VehicleMaintenanceRecord table will have the log of vehicle repaired and other maintainace.
Here is Data Dictionary of Database
Fig2
Each table has auto increment column as primary key, called ID. There is a VINumber column in vehicle table its a Unique key column so that we can not key duplicate value under this clolumn. Vehicle_ID is the Foreign key of Vehicle table. There is a referential integrity is set up using primary key of Vehicle table and Foreign key of child table. It will prevent us leaving orphan reocrd in child table that means neither we can delete parent record without deleting child records nor we can insert child records without having parent record.
Here is a script to create a database and table
CREATE DATABASE UsedCarManagement;
--// Create Table Vehicle
USE [UsedCarManagement]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Vehicle](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Year] [nchar](4) NOT NULL,
[Make] [nvarchar](100) NOT NULL,
[Model] [nvarchar](100) NOT NULL,
[Color] [nvarchar](50) NULL,
[Trim] [nvarchar](50) NULL,
[Condition] [nvarchar](50) NULL,
[Mileage] [int] NOT NULL,
[VINumber] [nvarchar](16) NOT NULL,
[IsHold] [bit] NULL,
[Note] [nvarchar](max) NULL,
[ModifiedDate] [datetime] NOT NULL,
CONSTRAINT [PK_Vehicle] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [Uq_VINumber_Vehicle] UNIQUE NONCLUSTERED
(
[VINumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Create Table VehicleMaintenanceRecord
USE [UsedCarManagement]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[VehicleMaintenanceRecord](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Vehicle_ID] [int] NOT NULL,
[ServiceBy] [nvarchar](100) NOT NULL,
[ServiceDate] [date] NULL,
[ConditionDescr] [nvarchar](max) NULL,
[ModifiedDate] [datetime] NOT NULL,
CONSTRAINT [PK_VehicleMaintenanceRecord] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[VehicleMaintenanceRecord] WITH CHECK ADD CONSTRAINT [FK_VehicleMaintenanceRecord_Vehicle] FOREIGN KEY([Vehicle_ID])
REFERENCES [dbo].[Vehicle] ([ID])
GO
ALTER TABLE [dbo].[VehicleMaintenanceRecord] CHECK CONSTRAINT [FK_VehicleMaintenanceRecord_Vehicle]
GO
Below is the data for Vehicle and VehicleMaintenanceRecord. First insert data into Vehicle table and then into VehicleMaintenanceRecord table otherwise it will thorw referential integrity error. Here I have used only 6 char long VIN Number bceause it is just a sample Database.
USE [UsedCarManagement]
GO
SET IDENTITY_INSERT [dbo].[Vehicle] ON
INSERT [dbo].[Vehicle] ([ID], [Year], [Make], [Model], [Color], [Trim], [Condition], [Mileage], [VINumber], [IsHold], [Note], [ModifiedDate]) VALUES (1, N'2010', N'Toyota', N'Camary', N'White', N'LE', N'Good', 25000, N'TOY234', NULL, NULL, CAST(0x0000A387014F1414 AS DateTime))
INSERT [dbo].[Vehicle] ([ID], [Year], [Make], [Model], [Color], [Trim], [Condition], [Mileage], [VINumber], [IsHold], [Note], [ModifiedDate]) VALUES (2, N'2009', N'Toyota', N'Corolla', N'Black', N'S', N'Good', 40000, N'TOY456', NULL, NULL, CAST(0x0000A387014F2CB0 AS DateTime))
INSERT [dbo].[Vehicle] ([ID], [Year], [Make], [Model], [Color], [Trim], [Condition], [Mileage], [VINumber], [IsHold], [Note], [ModifiedDate]) VALUES (3, N'2011', N'Toyota', N'Prius C', N'Red', NULL, N'Very Good', 27500, N'TOY243', NULL, NULL, CAST(0x0000A387014F3160 AS DateTime))
INSERT [dbo].[Vehicle] ([ID], [Year], [Make], [Model], [Color], [Trim], [Condition], [Mileage], [VINumber], [IsHold], [Note], [ModifiedDate]) VALUES (4, N'2009', N'Toyota', N'Rav4', N'White', N'XLE', N'Good', 50000, N'TOY012', NULL, NULL, CAST(0x0000A387014F4EAC AS DateTime))
INSERT [dbo].[Vehicle] ([ID], [Year], [Make], [Model], [Color], [Trim], [Condition], [Mileage], [VINumber], [IsHold], [Note], [ModifiedDate]) VALUES (5, N'2009', N'Nissan', N'Altima', N'White', N'SV', N'Good', 45000, N'NIS564', NULL, NULL, CAST(0x0000A3870151EBD0 AS DateTime))
INSERT [dbo].[Vehicle] ([ID], [Year], [Make], [Model], [Color], [Trim], [Condition], [Mileage], [VINumber], [IsHold], [Note], [ModifiedDate]) VALUES (6, N'2010', N'Nissan', N'Altima', N'Black', N'SL', N'Good', 38000, N'NIS435', NULL, NULL, CAST(0x0000A38701521600 AS DateTime))
INSERT [dbo].[Vehicle] ([ID], [Year], [Make], [Model], [Color], [Trim], [Condition], [Mileage], [VINumber], [IsHold], [Note], [ModifiedDate]) VALUES (7, N'2012', N'Nissian', N'Rouge', N'Silver', N'SV', N'Very Good', 20000, N'NIS987', NULL, NULL, CAST(0x0000A3870152541C AS DateTime))
INSERT [dbo].[Vehicle] ([ID], [Year], [Make], [Model], [Color], [Trim], [Condition], [Mileage], [VINumber], [IsHold], [Note], [ModifiedDate]) VALUES (8, N'2009', N'Honda', N'Civic', N'Red', N'LX', N'Good', 37000, N'HON243', NULL, NULL, CAST(0x0000A387016D5884 AS DateTime))
INSERT [dbo].[Vehicle] ([ID], [Year], [Make], [Model], [Color], [Trim], [Condition], [Mileage], [VINumber], [IsHold], [Note], [ModifiedDate]) VALUES (9, N'2010', N'Honda', N'Accord', N'White', N'LX', N'Very Good', 28000, N'HON543', NULL, NULL, CAST(0x0000A3870172E3A8 AS DateTime))
INSERT [dbo].[Vehicle] ([ID], [Year], [Make], [Model], [Color], [Trim], [Condition], [Mileage], [VINumber], [IsHold], [Note], [ModifiedDate]) VALUES (10, N'2010', N'GMC', N'Terrian', N'White', NULL, N'Good', 22000, N'GMC986', NULL, NULL, CAST(0x0000A38701734AC8 AS DateTime))
INSERT [dbo].[Vehicle] ([ID], [Year], [Make], [Model], [Color], [Trim], [Condition], [Mileage], [VINumber], [IsHold], [Note], [ModifiedDate]) VALUES (11, N'2014', N'GMC', N'Acadia', N'Black', NULL, N'Excellent', 4000, N'GMC356', NULL, NULL, CAST(0x0000A38701759710 AS DateTime))
INSERT [dbo].[Vehicle] ([ID], [Year], [Make], [Model], [Color], [Trim], [Condition], [Mileage], [VINumber], [IsHold], [Note], [ModifiedDate]) VALUES (12, N'2010', N'Toyota', N'Camary', NULL, N'XLE', NULL, 30000, N'TOY230', NULL, NULL, CAST(0x0000A3870175AC28 AS DateTime))
INSERT [dbo].[Vehicle] ([ID], [Year], [Make], [Model], [Color], [Trim], [Condition], [Mileage], [VINumber], [IsHold], [Note], [ModifiedDate]) VALUES (13, N'2001', N'Honda', N'Civic', NULL, NULL, NULL, 50000, N'HON111', 0, NULL, CAST(0x0000A38701765038 AS DateTime))
INSERT [dbo].[Vehicle] ([ID], [Year], [Make], [Model], [Color], [Trim], [Condition], [Mileage], [VINumber], [IsHold], [Note], [ModifiedDate]) VALUES (15, N'2003', N'Nissan', N'Sentra', N'Black', NULL, N'Good', 80000, N'NIS211', 1, N'It is on hold till next week because of customer''s request.', CAST(0x0000A387017667A8 AS DateTime))
INSERT [dbo].[Vehicle] ([ID], [Year], [Make], [Model], [Color], [Trim], [Condition], [Mileage], [VINumber], [IsHold], [Note], [ModifiedDate]) VALUES (19, N'2000', N'Nissan', N'Sentra', N'White', NULL, NULL, 120000, N'NIS091', 0, NULL, CAST(0x0000A38701768044 AS DateTime))
SET IDENTITY_INSERT [dbo].[Vehicle] OFF
USE [UsedCarManagement]
GO
SET IDENTITY_INSERT [dbo].[VehicleMaintenanceRecord] ON
INSERT [dbo].[VehicleMaintenanceRecord] ([ID], [Vehicle_ID], [ServiceBy], [ServiceDate], [ConditionDescr], [ModifiedDate]) VALUES (1, 1, N'ABC Auto Shop', CAST(0x9F340B00 AS Date), N'Tire, Break Pad Change. Condiotn is good', CAST(0x0000A387017DD218 AS DateTime))
INSERT [dbo].[VehicleMaintenanceRecord] ([ID], [Vehicle_ID], [ServiceBy], [ServiceDate], [ConditionDescr], [ModifiedDate]) VALUES (2, 1, N'Holiday Auto Shop', CAST(0x9F360B00 AS Date), N'Timing Belt Chage', CAST(0x0000A387017DEAB4 AS DateTime))
INSERT [dbo].[VehicleMaintenanceRecord] ([ID], [Vehicle_ID], [ServiceBy], [ServiceDate], [ConditionDescr], [ModifiedDate]) VALUES (3, 5, N'Repair Auto Shop', CAST(0xC8360B00 AS Date), N'Rear Bumper Change', CAST(0x0000A3870180A77C AS DateTime))
INSERT [dbo].[VehicleMaintenanceRecord] ([ID], [Vehicle_ID], [ServiceBy], [ServiceDate], [ConditionDescr], [ModifiedDate]) VALUES (4, 8, N'ABC Auto Shop', CAST(0x59320B00 AS Date), N'Oil and Filter Change', CAST(0x0000A38701811DD8 AS DateTime))
INSERT [dbo].[VehicleMaintenanceRecord] ([ID], [Vehicle_ID], [ServiceBy], [ServiceDate], [ConditionDescr], [ModifiedDate]) VALUES (5, 8, N'ABC Auto Shop', CAST(0xC4330B00 AS Date), N'All four Tires replaced', CAST(0x0000A38701813674 AS DateTime))
INSERT [dbo].[VehicleMaintenanceRecord] ([ID], [Vehicle_ID], [ServiceBy], [ServiceDate], [ConditionDescr], [ModifiedDate]) VALUES (6, 8, N'Mobile Auto Shop', CAST(0x51380B00 AS Date), N'Engine Changed', CAST(0x0000A38701817A6C AS DateTime))
SET IDENTITY_INSERT [dbo].[VehicleMaintenanceRecord] OFF
We don't want to write the sql statements behind User Interface layer we will use the Stored Procedure. Here is some benefit of using Stored Procedure.
- It helps to maintain the code.
- We can test the result of stored procedure on database which helps for testing.
- It does not give direct access to the table.
- It increase the performance because it stays in the catch of server.
Lets create the stored procedure. There are three stored procedures
- GetAllVehicles
- GetVehicleByVIN
- AddVehicle
GetAllVehicle stored procedure retrieve list of vehicles form Database. There is Left Join between two table to pull all the information about vehicle.
GetVehicleByVIN stored procedure takes one input parameter which is VINumber and return the related vehicle detail information.
AddVehicle stored procedure is used to add new vehicle into database.
Below is the script for creating above Stored procedure
USE [UsedCarManagement]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[AddVehicle]
@Year char(4),
@make varchar(100),
@model varchar(100),
@color varchar(50),
@trim varchar(50),
@condition varchar(50),
@mileage int,
@VINumber varchar(16),
@isHold bit,
@note varchar(max),
@modifiedDate datetime
AS
BEGIN
INSERT INTO dbo.Vehicle( Year,Make,Model,Color,Trim,Condition,Mileage,VINumber,IsHold,Note,ModifiedDate)
VALUES(@Year,@make,@model,@color,@trim,@condition,@mileage,@VINumber,@isHold,@note,@modifiedDate)
END
GO
USE [UsedCarManagement]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetAllVehicles]
AS
BEGIN
SELECT vh.ID, vh.Year,vh.Make,vh.Model,vh.Color,vh.Trim,vh.Condition,vh.Mileage,vh.VINumber,vh.ModifiedDate,
vh.IsHold, vh.Note,vhm.ID as VehMaintenanceID, vhm.ServiceBy, vhm.ServiceDate, vhm.ConditionDescr,vhm.ModifiedDate
FROM dbo.Vehicle vh
LEFT JOIN dbo.VehicleMaintenanceRecord vhm on vhm.Vehicle_ID = vh.id
END
GO
USE [UsedCarManagement]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetVehicleByVIN]
(
@VINumber varchar(16)
)
AS
BEGIN
SELECT vh.ID, vh.Year,vh.Make,vh.Model,vh.Color,vh.Trim,vh.Condition,vh.Mileage,vh.VINumber,vh.Ishold,Vh.Note,
vh.ModifiedDate, vhm.ID as VehMaintenanceID, vhm.ServiceBy, vhm.ServiceDate, vhm.ConditionDescr,vhm.ModifiedDate
FROM dbo.Vehicle vh
LEFT JOIN dbo.VehicleMaintenanceRecord vhm on vhm.Vehicle_ID = vh.id
WHERE vh.VINumber = @VINumber
END
GO
Lets Create a Project in Asp.Net. We will connect this database from Asp.net and use stored procedure to insert and retrieve data.
Step1. Click on VS2010, then Click on File, then New, then Project.
Step2. Template window will pop up. Expand Visual C# node and Select Web.
Step3. Then select Asp.Net Web Application and give it name "ConnectSqlServer" project.
If you have followed step 1 to step 3 then you will get something like Fig3. You will not have all the aspx page that you can see in Fig 3. But you wil have Default.aspx and Account.aspx page. I have deleted those two aspx pages and created some other pages which we will use later.
Fig3
Right Click on ConnecSqlServer project and select "Add" and then select "New Item". Then new window will pop up and select Web Form and give it name as AddNewVehicle your screen will look like as Fig4 and hit Add button.
Fig4.
Similarly create Vechicles.aspx and VehicleDetail.aspx page. If you have created all the aspx page then your project solution will look like as Fig3 above.
Connect Database from Asp.Net project (ConnectSqlServer project)
Open Web.Config file and add the below string inside <connectionStrings> node
<connectionStrings>
<add name="vehicleConString" connectionString="server=(local);database=UsedCarManagement;uid=sa;password=Test123; " />
</connectionStrings>
Change uid and password as your database user id and password. We will use this connection string from aspx.cs page when we interact with database. Name of this connectionstring is "vehicleConString" if there is more than one connecting string make it unique. We will use this name to get the connection string.
Lets create a form within AddNewVehilce page to add new vehicle into database. I have not forced any validation except RequiredValidation on some of the filed. We will take about validation in another article.
Here is Aspx page html mark up.
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="AddNewVehicle.aspx.cs" Inherits="ConnectSqlServer.AddNewVehicle" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<style type="text/css">
table {
border-collapse: collapse;
}
td
{
padding:0.5em 0.7em 0.5em 0;
}
.AlignRight
{
text-align:center;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div>
<p>Add New Vehicle</p>
<div id="InputBlockDiv">
<table>
<tr>
<td>
<asp:Label ID="lblYear" runat="server" Text="Year:*"></asp:Label>
</td>
<td>
<asp:TextBox ID="txtYear" runat="server"></asp:TextBox>
<asp:RequiredFieldValidator ID="reqYear" runat="server" ControlToValidate="txtYear" Display="Dynamic"
Text="*" ForeColor="Red" ToolTip="Enter 4 digit year" ></asp:RequiredFieldValidator>
</td>
<td>
<asp:Label ID="lblMake" runat="server" Text="Make:*"></asp:Label></td>
<td>
<asp:TextBox ID="txtMake" runat="server"></asp:TextBox>
<asp:RequiredFieldValidator ID="reqMake" runat="server" ControlToValidate="txtMake" Text="*"
ForeColor="Red" Display="Dynamic"></asp:RequiredFieldValidator>
</td>
<td>
<asp:Label ID="lblModel" runat="server" Text="Model:*"></asp:Label></td>
<td>
<asp:TextBox ID="txtModel" runat="server"></asp:TextBox>
<asp:RequiredFieldValidator ID="reqModel" runat="server" ControlToValidate="txtModel" Text="*"
ForeColor="Red" Display="Dynamic" >*</asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td>
<asp:Label ID="lblColor" runat="server" Text="Color:"></asp:Label>
</td>
<td>
<asp:TextBox ID="txtColor" runat="server"></asp:TextBox>
</td>
<td>
<asp:Label ID="lblTrim" runat="server" Text="Trim:"></asp:Label>
</td>
<td>
<asp:TextBox ID="txtTrim" runat="server"></asp:TextBox>
</td>
<td>
<asp:Label ID="lblCondition" runat="server" Text="Condition:"></asp:Label>
</td>
<td>
<asp:DropDownList ID="drpCondition" runat="server" Width="150px">
<asp:ListItem Selected="True" Text="--Select--" Value="0"></asp:ListItem>
<asp:ListItem Text="Excellent" Value="Excellent"></asp:ListItem>
<asp:ListItem Text="Very Good" Value="Very Good"></asp:ListItem>
<asp:ListItem Text="Good" Value="Good"></asp:ListItem>
<asp:ListItem Text="Fair" Value="Fair"></asp:ListItem>
<asp:ListItem Text="Poor" Value="Poor"></asp:ListItem>
</asp:DropDownList>
</td>
</tr>
<tr>
<td><asp:Label ID="lblMileage" runat="server" Text="Mileage:*"></asp:Label></td>
<td>
<asp:TextBox ID="txtMileage" runat="server"></asp:TextBox>
<asp:RequiredFieldValidator ID="reqMileage" runat="server" Text="*" ForeColor="Red" ControlToValidate="txtMileage"
Display="Dynamic"></asp:RequiredFieldValidator>
</td>
<td>
<asp:Label ID="lblVinNumber" runat="server" Text="VIN Number:*"></asp:Label>
</td>
<td>
<asp:TextBox ID="txtVinNumber" runat="server"></asp:TextBox>
<asp:RequiredFieldValidator ID="reqVinnumber" runat="server" Text="*" ControlToValidate="txtVinNumber"
Display="Dynamic" ForeColor="Red"></asp:RequiredFieldValidator>
</td>
<td>
<asp:Label ID="lblOnHold" runat="server" Text="On Hold:"></asp:Label>
</td>
<td> <asp:RadioButton ID="rdOnHold" GroupName="IsItOnHold" runat="server" Text="Yes" />
<asp:RadioButton ID="rdNotOnHold" GroupName="IsItOnHold" runat="server" Text="No" /> </td>
</tr>
<tr>
<td colspan="1">
<asp:Label ID="lblNote" runat="server" Text="Note:"></asp:Label>
</td>
<td colspan="2">
<asp:TextBox ID="txtNote" runat="server" TextMode="MultiLine" Rows="3" Columns="40"></asp:TextBox>
</td>
<td colspan="3"></td>
</tr>
<tr>
<td colspan="6" class="AlignRight">
<asp:Button ID="btnSubmit" runat="server" Text="Submit" onclick="btnSubmit_Click" />
</td>
</tr>
<tr>
<td colspan="6" >
<asp:Label ID="lblMsg" runat="server" Text=""></asp:Label>
</td>
</tr>
</table>
</div>
</div>
</form>
</body>
</html>
Page will look like as below Fig 5.
Fig 5
Here is a code for adding new vehicle to database
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace ConnectSqlServer
{
public partial class AddNewVehicle : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
if (Page.IsValid)
{
addNewVehicle();
}
}
private void addNewVehicle()
{
string connStr = ConfigurationManager.ConnectionStrings["vehicleConString"].ConnectionString;
SqlConnection objSqlConn = new SqlConnection(connStr);
try
{
objSqlConn.Open();
SqlCommand command = new SqlCommand("AddVehicle", objSqlConn);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@Year", SqlDbType.VarChar).Value = txtYear.Text.Trim();
command.Parameters.Add("@make", SqlDbType.VarChar).Value = txtMake.Text.Trim();
command.Parameters.Add("@model", SqlDbType.VarChar).Value = txtModel.Text.Trim();
command.Parameters.Add("@color", SqlDbType.VarChar).Value = txtColor.Text.Trim() == "" ? Convert.DBNull : txtColor.Text.Trim();
command.Parameters.Add("@trim", SqlDbType.VarChar).Value = txtTrim.Text.Trim() == "" ? Convert.DBNull : txtTrim.Text.Trim();
command.Parameters.Add("@condition", SqlDbType.VarChar).Value = drpCondition.SelectedValue.ToString() == "0"
? Convert.DBNull : drpCondition.SelectedValue.ToString();
command.Parameters.Add("@mileage", SqlDbType.Int).Value = Convert.ToInt32(txtMileage.Text.Trim());
command.Parameters.Add("@VINumber", SqlDbType.VarChar).Value = txtVinNumber.Text.Trim();
command.Parameters.Add("@isHold", SqlDbType.Bit).Value = rdOnHold.Checked == true ? true : false;
command.Parameters.Add("@note", SqlDbType.VarChar).Value = txtNote.Text.Trim() == "" ? Convert.DBNull : txtNote.Text.Trim();
command.Parameters.Add("@modifiedDate", SqlDbType.DateTimeOffset).Value = DateTimeOffset.Now.LocalDateTime;
SqlDataAdapter adapter = new SqlDataAdapter(command);
int rows = command.ExecuteNonQuery();
if (rows == 1)
{
lblMsg.Text = "Data successfully inserted.";
ResetInputControls();
}
else
{
lblMsg.Text = "Data could not insert.";
}
}
catch (Exception ex)
{
Response.Redirect("Error.aspx?error=" + ex.Message.ToString());
}
finally
{
objSqlConn.Close();
}
}
private void ResetInputControls()
{
txtYear.Text = string.Empty;
txtMake.Text = string.Empty;
txtModel.Text = string.Empty;
txtColor.Text = string.Empty;
drpCondition.SelectedIndex = 0;
txtColor.Text = string.Empty;
txtTrim.Text = string.Empty;
txtMileage.Text = string.Empty;
txtVinNumber.Text = string.Empty;
rdOnHold.Checked = false;
rdNotOnHold.Checked = false;
txtNote.Text = string.Empty;
}
}
}
Lets talk what I did in code behind
We need to add System.Data.SqlClient namespave if we want to use the Ado.Net proerties for sql database, and System.Configuration for getting database connectionString object form Web.Config file.
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
If page is valid then I have called the method
addNewVehicle();
First we have got the connection string from WebConfig file by using
string connStr = ConfigurationManager.ConnectionStrings["vehicleConString"].ConnectionString;
then instantiate the object of Sqlconnection and pass connection string as a paramter
SqlConnection objSqlConn = new SqlConnection(connStr);
then we have open the connection. Once connection is open then we have instantiate the SqlCommand object as below
SqlCommand command = new SqlCommand("AddVehicle", objSqlConn);
command.CommandType = CommandType.StoredProcedure;
We have passed two parameter to SqlComand object first one is the Stored Procedure and second one is SqlConnection object.
To pass parameter to the StoredProcedure we have used below property of SqlCommand
command.Parameters.Add();
We need to instantiate the SqlDataAdapter object and pass the command object to the SqlDataAdapter object.
Then we have called the ExecuteNonQuery() method of command object. If data is succefully inserted into DB then it will return 1.
command.ExecuteNonQuery();
If ExecuteNonQuery() method return 1 then we display the messge into a "lblDisplay" label and reset all input controls by calling ResetInputControls() method otherwise we redirect the user to Error.aspx page.
we have used try and catch block here if there is any error with database connection or inserting data then we are redirecting the user to Error.aspx page and closing the connection object from final block.
Lets get the list of Vehilce. we want to see all the vechile which we have saved into database. We have created the Vehilce.aspx page where we have a gridview which we will use to dispaly the list of vechiles. Here we will call the "GetAllVehilces" stored procedure. This stored procedure does not take any input parameter but return all the vechiles from database.
Here is aspx page Html tag
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Vehicles.aspx.cs" Inherits="ConnectSqlServer.Vehicles" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="gvDisplay" runat="server">
</asp:GridView>
</div>
</form>
</body>
</html>
Here is Vehilces.aspx.cs code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace ConnectSqlServer
{
public partial class Vehicles : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
GetAllVehicles();
}
private void GetAllVehicles()
{
string connStr = ConfigurationManager.ConnectionStrings["vehicleConString"].ConnectionString;
SqlConnection objSqlConn = new SqlConnection(connStr);
try
{
objSqlConn.Open();
SqlCommand command = new SqlCommand("GetAllVehicles", objSqlConn);
command.CommandType = CommandType.StoredProcedure;
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataSet ds = new DataSet();
adapter.Fill(ds, "tb1");
gvDisplay.DataSource = ds;
gvDisplay.DataBind();
objSqlConn.Close();
}
catch (Exception ex)
{
Response.Redirect("Error.aspx?error=" + ex.Message.ToString());
}
finally
{
objSqlConn.Close();
}
}
}
}
Lets get the Vehicle detail information by passing VIN number. We have created the VehicleDetail.aspx page. It takes VIN Number as input paramter and return the detail information of vehilce. We will use th "GetVehicleByVIN" stored procedure to get information of vechile.
Here is the HTML mark up for VehicleDetail.aspx page.
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="VehicleDetail.aspx.cs" Inherits="ConnectSqlServer.VehicleDetail" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Label ID="lblVinNum" runat="server" Text="Enter Vin Number:" > </asp:Label>
<asp:TextBox ID="txtVinNumber" runat="server"></asp:TextBox>
<asp:RequiredFieldValidator ID="reqVINumber" runat="server"
ControlToValidate="txtVinNumber" Display="Dynamic"
Text="*" ForeColor="Red" EnableClientScript="False" ></asp:RequiredFieldValidator>
<br /><br />
<asp:Button ID="btnSubmit" runat="server" Text="Submit"
onclick="btnSubmit_Click" /><br /><br />
<asp:GridView ID="gvDisplay" runat="server">
</asp:GridView><br /><br />
<asp:Label ID="lblDisplay" runat="server" Text=""></asp:Label>
</div>
</form>
</body>
</html>
Page will look like as Fig 6 below
Fig6
Here is Vehicle.aspx.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace ConnectSqlServer
{
public partial class VehicleDetail : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
if (Page.IsValid)
{
gvDisplay.DataSource = null;
GetVehicleDetailByVIN();
}
else
{
gvDisplay.DataSource = null;
gvDisplay.DataBind();
lblDisplay.Text = string.Empty;
}
}
private void GetVehicleDetailByVIN()
{
string connStr = ConfigurationManager.ConnectionStrings["vehicleConString"].ConnectionString;
SqlConnection objSqlConn = new SqlConnection(connStr);
try
{
objSqlConn.Open();
SqlCommand command = new SqlCommand("GetVehicleByVIN", objSqlConn);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@VINumber", SqlDbType.VarChar).Value = txtVinNumber.Text.Trim();
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataSet ds = new DataSet();
adapter.Fill(ds, "tb1");
gvDisplay.DataSource = ds;
gvDisplay.DataBind();
lblDisplay.Text = ds.Tables[0].Rows.Count == 0 ? "There is no vehicle belong to this VIN Number." : string.Empty;
}
catch (Exception ex)
{
Response.Redirect("Error.aspx?error=" + ex.Message.ToString());
}
finally
{
objSqlConn.Close();
}
}
}
}
If there is no vehicle belong to user's entered VIN number then we are displaying the message to user as below.
lblDisplay.Text = ds.Tables[0].Rows.Count == 0 ? "There is no vehicle belong to this VIN Number." : string.Empty;
History
Keep a running update of any changes or improvements you've made here.
