1. how to connect a database in ms sql server first create
TRANSCRIPT
1. How to connect a database in MS SQL Server
First create a database using SQL Management Studio.
In Visual Studio after creating a new website project click on View -- > Server Exploler.
After doing that as you can see in the picture below, Right click on Data Connection and then choose
Add Connection option.
After clicking on Add connection, the following window should open.
If you are using MS SQL SERVER then click on CHANGE button and set it to Microsoft SQL server.
Then Server name drop Down list will show the Server Name. If it doesn't show up, use ./Sqlexpress as a
server name.
Then in the last drop down list it should display all the database names which you have created in SQL
Management Studio. Select a database you want to connect to and click Ok. Before doing that, you
might want to press Test Connection to see if everything is fine.
After that, you should be able to see the database in the Server Exploler tab. Right click the on Database
Name and click on Properties and then on right side of the 3rd property is Connection String which you
need to copy.
Highlighted part below is the one you have copied which has to be pasted in web.config file as shown in
the next step.
<connectionStrings>
<add name="RS" connectionString="Data Source=SUNNY\SQLEXPRESS;Initial
Catalog=RoomManagementDB;Integrated Security=True" providerName="System.Data.SqlClient"/> </connectionStrings>
Now open web.config file using solution explorer and then paste the connection string in web.config file
as follows:
<connectionStrings> <add name="RS" connectionString="Data Source=SUNNY\SQLEXPRESS;Initial
Catalog=RoomManagementDB;Integrated Security=True" providerName="System.Data.SqlClient"/>
</connectionStrings>
Example codes:
Connect to the database using following code.
public class ConnectionManager {
public static SqlConnection GetRoomReservationConnection()
{ // Get the connection string from the configuration file
string connectionString =
ConfigurationManager.ConnectionStrings["RS"].ConnectionString;
//make sure instead of RS you must use appropriate name from the web.config file
// Create a new connection object SqlConnection connection = new SqlConnection(connectionString);
// Open the connection, and return it connection.Open();
return connection;
}
}
Executing Stored procedure and displaying data on web page
The yellow highlighted part is the name of Stored Procedure and the @Month and @ Year are
parameters in the Stored Procedure.
public static DataTable GetReservationsByMonth(int month,int year)
{
DataTable dt = new DataTable();
using (SqlConnection connection =
ConnectionManager.GetRoomReservationConnection()) {
SqlCommand command = new SqlCommand("GetEventsByMonth", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@Month", SqlDbType.Int).Value = month;
command.Parameters.Add("@Year", SqlDbType.Int).Value = year; SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
dataAdapter.Fill(dt);
}
return dt;
}
in page load event
DataTable dt = Reservation.GetReservationsByMonth(month,year); GridView1.DataSource = dt;
GridView1.DataBind();
2. Example Codes:
Room class codes to insert , update and delete from the database.
using System;
using System.Collections.Generic;
using System.Linq; using System.Web;
using System.Data.Sql;
using System.Data; using System.Data.SqlClient;
/// <summary>
/// Summary description for Room/// </summary> public class Room
{
private int locationID; private int roomTypeID;
private string roomName;
private int roomNumber;
private int floor_;
public Room()
{
}
// Properties
public int LocationID
{ get
{
return locationID; }
set
{ locationID = value;
}
}
public int RoomNumber {
get
{ return roomNumber;
}
set {
roomNumber = value;
}
} public int Floor
{
get
{ return floor_ ;
}
set {
floor_ = value;
}
}
public int RoomTypeID
{ get
{
return roomTypeID; }
set
{
roomTypeID = value; }
}
public string RoomName
{
get
{
return roomName; }
set
{
roomName = value; }
}
//Methods
public int InsertNewRoom()
{
int affectedRows = 0;
using (SqlConnection con = ConnectionManager.GetRoomReservationConnection()) {
SqlCommand command = new SqlCommand("insert_room_tbl", con);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@locationID", SqlDbType.Int).Value = LocationID; command.Parameters.Add("@roomNumber", SqlDbType.Int).Value = RoomNumber; ;
command.Parameters.Add("@_floor", SqlDbType.Int).Value = Floor;
command.Parameters.Add("@roomDescID", SqlDbType.Int).Value = RoomTypeID;
command.Parameters.Add("@roomName", SqlDbType.NVarChar, 50).Value = RoomName;
affectedRows = command.ExecuteNonQuery();
}
return affectedRows;
} public int UpdateRoomInfo(int RoomID)
{
int affectedRows = 0; try
{
using (SqlConnection con = ConnectionManager.GetRoomReservationConnection())
{ SqlCommand command = new SqlCommand("Update_room", con);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@roomID", SqlDbType.Int).Value = RoomID;
command.Parameters.Add("@locationID", SqlDbType.Int).Value = LocationID;
command.Parameters.Add("@roomNumber", SqlDbType.Int).Value = RoomNumber; command.Parameters.Add("@_floor", SqlDbType.Int).Value = Floor;
command.Parameters.Add("@roomDescID", SqlDbType.Int).Value = RoomTypeID;
command.Parameters.Add("@roomName", SqlDbType.NVarChar, 50).Value =
RoomName;
affectedRows = command.ExecuteNonQuery();
} }
catch (SqlException e)
{ if (e.Number == 2627)
{
affectedRows = -2627; }
}
return affectedRows;
}
public int DeleteRoom(int RoomID) {
int affectedRows = 0;
try {
using (SqlConnection con = ConnectionManager.GetRoomReservationConnection())
{
SqlCommand command = new SqlCommand("Delete_room", con); command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@roomID", SqlDbType.Int).Value = RoomID;
affectedRows = command.ExecuteNonQuery(); }
}
catch (SqlException r) {
}
return affectedRows;
}
public bool CheckRoom()
{ DataTable dt = new DataTable();
using (SqlConnection connection =
ConnectionManager.GetRoomReservationConnection()) {
SqlCommand command = new SqlCommand("Check_Room", connection);
command.Parameters.Add("@roomDescID", SqlDbType.Int).Value = RoomTypeID;
command.Parameters.Add("@locationID", SqlDbType.Int).Value = LocationID;
command.Parameters.Add("@roomNumber", SqlDbType.Int).Value = RoomNumber;
command.CommandType = CommandType.StoredProcedure;
SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
dataAdapter.Fill(dt);
if (dt.Rows.Count >= 1)
{
return false ;
} else
{
return true; }
}
}
public DataTable LoadRooms(int LocationID) {
DataTable dt = new DataTable();
using (SqlConnection connection = ConnectionManager.GetRoomReservationConnection())
{
SqlCommand command = new SqlCommand("Load_Rooms", connection); command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@locationId", SqlDbType.Int).Value = LocationID;
SqlDataAdapter dataAdapter = new SqlDataAdapter(command); dataAdapter.Fill(dt);
}
return dt;
}
public DataTable LoadRoomNumbers(int LocationID,int RoomDescID)
{
DataTable dt = new DataTable();
using (SqlConnection connection =
ConnectionManager.GetRoomReservationConnection())
{ SqlCommand command = new SqlCommand("Load_RoomNumbers", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@locationID", SqlDbType.Int).Value = LocationID;
command.Parameters.Add("@roomDescID", SqlDbType.Int).Value = RoomDescID;
SqlDataAdapter dataAdapter = new SqlDataAdapter(command); dataAdapter.Fill(dt);
}
return dt;
}
public DataTable returnRoomDetails()
{
DataTable dt = new DataTable();
using (SqlConnection connection =
ConnectionManager.GetRoomReservationConnection()) {
SqlCommand command = new SqlCommand("RoomDetails", connection);
command.CommandType = CommandType.StoredProcedure;
SqlDataAdapter dataAdapter = new SqlDataAdapter(command); dataAdapter.Fill(dt);
}
return dt; }
}