I have posted this on several forums including the asp.net one to no avail. I have a webservice, it either adds or updates room inventory based on whether SqlDataReader hasrows being true or false. (Code below).
The problem I am having with this code is that it will either do a add for the entire range of dates entered or an entire an update for that entire range otherwise it throws an error like for example:
If I am changing inventory for the period 18/8/2013 - 25/8/2013 and 18/8-23/8 has no inventory but 24/8-25/8 has; an error is thrown because of the availability in 24/8 saying inventory is available and insert is not possible due to primary key constraints, halting the entire process.
I want my code to either add or update across the date range depending on whether its there or not rather then doing just an add or just update of everything.
Is that possible? How do I change my code for that?
Would be grateful for any insight on how to do this.
Code:
[WebMethod(Description = "Add or Amend Availability & Rates")]
public bool Avail(string Username, string Password, DateTime Dte, DateTime Dtm, int ID, string RoomType, int Qty, double CurPrice)
{
GetCredentials(Username, Password);
int ID= Convert.ToInt16(GetCredentials(Username, Password).Tables[0].Rows[0]["strTypeID"]);
bool retVal = false;
GetCredentials(Username, Password);
using (SqlConnection mySQLconnection = new SqlConnection(connStr))
{
using (SqlCommand dbCommand = new SqlCommand("select * from Available where intID=@ID and dtm=@Dtm and strRoomType=@RoomType", mySQLconnection))
{
SqlParameter dt = new SqlParameter("@Dtm", Dtm);
SqlParameter RoomT = new SqlParameter("@RoomType", RoomType);
SqlParameter typeI = new SqlParameter("@ID", ID);
dbCommand.Parameters.Add(dt);
dbCommand.Parameters.Add(RoomT);
dbCommand.Parameters.Add(typeI);
mySQLconnection.Open();
using (SqlDataReader reader = dbCommand.ExecuteReader())
{
if (!reader.HasRows)
{
AddAvail(Username, Password, Dte, Dtm, RoomType, Qty, CurPrice);
retVal = false;
}
else
{
AmdAvail(Username, Password, Dte, Dtm, RoomType, Qty, CurPrice);
retVal = true;
}
mySQLconnection.Close();
dbCommand.Dispose();
mySQLconnection.Dispose();
return retVal;
}
}
}
}
/*----------------------------------------------------
* Webmethod AddAvail Adds multiple availability for speicifed date range
* ---------------------------------------------------*/
[WebMethod(Description = "Multiple Add of Availability", BufferResponse = true)]
public void AddAvail(string Username, string Password, DateTime Dte,DateTime Dtm, string RoomType, int Qty, double CurPrice)
{
GetAuthCredentials(Username, Password);
DateTime dat = Dtm;
int strTypeID = Convert.ToInt16(GetAuthCredentials(Username, Password).Tables[0].Rows[0]["strTypeID"]);
using (SqlConnection mySQLconnection = new SqlConnection(connStr))
{
mySQLconnection.Open();
for (DateTime date = Dte; date <= dat; date = date.AddDays(1.0))
{
string sqlInsertString = "INSERT INTO Available (dtm,intResortID,strRoomType,intQty,curPrice) VALUES (@dat,@strTypeID,@strRoomType,@intQty,@CurPrice)";
using (SqlCommand command = new SqlCommand())
{
command.Connection = mySQLconnection;
command.CommandText = sqlInsertString;
SqlParameter dt = new SqlParameter("@dat", date);
SqlParameter intRID = new SqlParameter("@strTypeID", strTypeID);
SqlParameter strRType = new SqlParameter("@strRoomType", RoomType);
SqlParameter intQuty = new SqlParameter("@intQty", Qty);
SqlParameter curpPrice = new SqlParameter("@curPrice", CurPrice);
command.Parameters.AddRange(new SqlParameter[] { dt, intRID, strRType, intQuty, curpPrice });
command.ExecuteNonQuery();
}
}
}
}
/*-----------------------------------------------------
* Webmethod AmdAvail Amends multiple Availability for specified date range
* -----------------------------------------------------*/
[WebMethod(Description = "Multilple Updates", BufferResponse = true)]
public DataSet AmdAvail(string Username, string Password, DateTime Dte, DateTime Dtm, string RoomType, int Qty, double CurPrice)
{
GetAuthCredentials(Username, Password);
int strTypeID = Convert.ToInt16(GetAuthCredentials(Username, Password).Tables[0].Rows[0]["strTypeID"]);
using (SqlConnection mySQLconnection = new SqlConnection(connStr))
{
mySQLconnection.Open();
using (SqlCommand dbCommand = new SqlCommand())
{
dbCommand.CommandText = "Update Available set intQty=@Qty,curprice=@CurPrice where dtm between @Dte and @Dtm and strRoomType=@Roomtype and intResortID=@strTypeID ";
dbCommand.Connection = mySQLconnection;
//Create new DataAdapter
using (SqlDataAdapter da = new SqlDataAdapter())
{
da.SelectCommand = dbCommand;
SqlParameter typeI = new SqlParameter("@strTypeID", strTypeID);
dbCommand.Parameters.Add(typeI);
dbCommand.Parameters.AddWithValue("@Dtm", Dtm);
dbCommand.Parameters.AddWithValue("@Dte", Dte);
dbCommand.Parameters.AddWithValue("@Qty", Qty);
dbCommand.Parameters.AddWithValue("@RoomType", RoomType);
dbCommand.Parameters.AddWithValue("@curprice", CurPrice);
dbCommand.Parameters.AddWithValue("@username", Username);
dbCommand.Parameters.AddWithValue("@password", Password);
DataSet ds = new DataSet();
da.Fill(ds);
return ds;
}
}
}
}
Web service to do insert/update based on SqlDataReader hasrows value
