Add a new class RoomReservationData.cs and write two methods say ReserveRoom and GeReservations
namespace
Practice.WCF
{
internal class RoomReservationData
{
private string connectionString = ConfigurationManager .ConnectionStrings["con" ].ConnectionString;
internal bool ReserveRoom(RoomReservationRequest roomReservationReq)
{
SqlConnection connection = GetConnection();
string sqlCommand = "INSERT INTO RoomReservationRequest(NoOfRooms, TypeOfRoom, FromDate, ToDate, ContactPersonName, " +
"ContactPersonMail, ContactPersonMob, Comments, Status) VALUES (" +
"@NoOfRooms, @TypeOfRoom, @FromDate, @ToDate, @ContactPersonName, " +
"@ContactPersonMail, @ContactPersonMob, @Comments, @Status)" ;
SqlCommand command = connection.CreateCommand();
command.CommandText = sqlCommand;
command.Parameters.Add("@NoOfRooms" , System.Data.SqlDbType .Int);
command.Parameters.Add("@TypeOfRoom" , System.Data.SqlDbType .NVarChar, 20);
command.Parameters.Add("@FromDate" , System.Data.SqlDbType .DateTime );
command.Parameters.Add("@ToDate" , System.Data.SqlDbType .DateTime);
command.Parameters.Add("@ContactPersonName" , System.Data.SqlDbType .NVarChar, 50);
command.Parameters.Add("@ContactPersonMail" , System.Data.SqlDbType .NVarChar, 50);
command.Parameters.Add("@ContactPersonMob" , System.Data.SqlDbType .NVarChar, 20);
command.Parameters.Add("@Comments" , System.Data.SqlDbType .NVarChar, 200);
command.Parameters.Add("@Status" , System.Data.SqlDbType .NVarChar, 200);
command.Parameters["@NoOfRooms" ].Value = roomReservationReq.NoOfRooms;
command.Parameters["@TypeOfRoom" ].Value = roomReservationReq.TypeOfRoom;
command.Parameters["@FromDate" ].Value = roomReservationReq.FromDate;
command.Parameters["@ToDate" ].Value = roomReservationReq.ToDate;
command.Parameters["@ContactPersonName" ].Value = roomReservationReq.ContactPersonName;
command.Parameters["@ContactPersonMail" ].Value = roomReservationReq.ContactPersonMail;
command.Parameters["@ContactPersonMob" ].Value = roomReservationReq.ContactPersonMob;
command.Parameters["@Comments" ].Value = roomReservationReq.Comments;
command.Parameters["@Status" ].Value = roomReservationReq.Status;
int rowsEffected =0;
try
{
rowsEffected = command.ExecuteNonQuery();
}
finally
{
if (connection != null )
{
connection.Close();
connection.Dispose();
}
}
return rowsEffected > 0;
}
internal RoomReservationRequest [] GetReservations(DateTime fromDate, DateTime toDate)
{
List <RoomReservationRequest > reservedRooms = new List <RoomReservationRequest >();
SqlConnection connection = GetConnection();
SqlCommand command = connection.CreateCommand();
command.CommandText = "SELECT ReservationId, NoOfRooms, TypeOfRoom, FromDate" +
",ToDate, ContactPersonName, ContactPersonMail, ContactPersonMob, Comments, Status " +
"FROM RoomReservationRequest " +
"WHERE FromDate > @FromDate AND ToDate<@ToDate" ;
command.Parameters.Add("@FromDate" , System.Data.SqlDbType .DateTime);
command.Parameters.Add("@ToDate" , System.Data.SqlDbType .DateTime);
command.Parameters["@FromDate" ].Value = fromDate;
command.Parameters["@ToDate" ].Value = toDate;
SqlDataReader reader = null ;
try
{
reader = command.ExecuteReader(CommandBehavior .CloseConnection);
while (reader.Read())
{
RoomReservationRequest roomReservationRequest = new RoomReservationRequest ();
roomReservationRequest.ReservationId = Convert .ToInt16(reader[0]);
roomReservationRequest.NoOfRooms = Convert .ToInt16(reader[1]);
roomReservationRequest.TypeOfRoom = reader[2].ToString();
roomReservationRequest.FromDate = Convert .ToDateTime(reader[3]);
roomReservationRequest.ToDate = Convert .ToDateTime(reader[4]);
roomReservationRequest.ContactPersonName = reader[5].ToString();
roomReservationRequest.ContactPersonMail = reader[6].ToString();
roomReservationRequest.ContactPersonMob = reader[7].ToString();
roomReservationRequest.Comments = reader[8].ToString();
roomReservationRequest.Status = reader[9].ToString();
reservedRooms.Add(roomReservationRequest);
}
}
finally
{
if (reader != null )
{
reader.Close();
reader.Dispose();
}
if (connection != null )
{
connection.Close();
connection.Dispose();
}
}
return reservedRooms.ToArray();
private SqlConnection GetConnection()
{
SqlConnection connection = new SqlConnection (connectionString);
try
{
connection.Open();
}
finally
{
}
return connection;
}
}
}
Implement the Interface ISErvice1 in Service1.svc.cs class. Create instance of class RoomReservationData which we implemented in Step-12 and use the same into the implemented methods.
namespace Practice.WCF
{
public class Service1 : IService1
{
#region IService1 Members
private RoomReservationData roomReservationData = new RoomReservationData ();
public bool ReserveRoom(RoomReservationRequest reservationRequest)
{
return roomReservationData.ReserveRoom(reservationRequest);
}
public RoomReservationRequest [] GetReservations(DateTime fromDate, DateTime toDate)
{
return roomReservationData.GetReservations(fromDate, toDate);
}
#endregion
}
}