在Web服务中插入来自SQLDataReader的更新错误
本文关键字：SQLDataReader 更新 错误 Web 服务 插入 | 更新日期: 2023-09-27 18:27:28
我有一个Web服务，可以根据是否可用来添加或更新房间库存。（以下代码）。我使用此代码遇到的问题是，它将对整个范围进行添加或更新，或者抛出错误，例如，如果我正在更改2013年8月18日至2013年8日25日期间的库存，并且2018年8月23日没有库存，但8月24日至25日有库存，则会在24小时8日抛出错误，表示库存可用，并且无法插入，从而停止整个过程。我希望我的代码能够添加或更新整个日期范围，而不仅仅是添加或修改所有内容。这可能吗？我需要为此更改代码吗？我该怎么做？如果您能深入了解我哪里出了问题，或者如何更改代码以做到这一点，我将不胜感激。该代码目前是实时的，因此目前正在影响我们的业务流程，因此我们将感谢任何事情。
[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;
}
}
}
}
因为我没有访问表模式的权限，所以我不确定，但似乎dtm是表中的主键。
根据您的示例，可用表中数据的当前状态如下所示：-
18/08-无数据
19/08-无数据
20/08-无数据
21/08-无数据
22/08-无数据
2008年23月-无数据
24/08-有数据
25/08-有数据
如果您尝试插入介于"18/08/2013"与"25/08/2013"之间的dtm范围的数据，它将失败，因为24已经获得了数据。
作为一个解决方案，你可以做的是：-
在代码中查找我的评论。搜索//AJAY:-
[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);
List<DataTime> existingDates = new List<DataTime>(); //AJAY:- This is to keep a track of what is already in the DataBase
using (SqlConnection mySQLconnection = new SqlConnection(connStr))
{
//AJAY:- Get the data for the whole Range from the DataBase
using (SqlCommand dbCommand = new SqlCommand("select * from Available where intID=@ID and dtm BETWEEN @Dte AND @Dtm and strRoomType=@RoomType", mySQLconnection))
{
SqlParameter dtStart = new SqlParameter("@Dte", Dte);
SqlParameter dtEnd = new SqlParameter("@Dtm", Dtm);
SqlParameter RoomT = new SqlParameter("@RoomType", RoomType);
SqlParameter typeI = new SqlParameter("@ID", ID);
dbCommand.Parameters.Add(dtStart);
dbCommand.Parameters.Add(dtEnd);
dbCommand.Parameters.Add(RoomT);
dbCommand.Parameters.Add(typeI);
mySQLconnection.Open();
using (SqlDataReader reader = dbCommand.ExecuteReader())
{
while(reader.read())
{
existingDates.Add(Convert.ToDateTime((string)reader["Dtm"])); //AJAY:- Change it as per your format of Dtm column in table schema
}
for (DateTime date = Dte; date <= dat; date = date.AddDays(1.0))
{
if(existingDates.Contains(date)) //AJAY:- record is already there just update it
{
AmdAvail(Username, Password, date, RoomType, Qty, CurPrice);
}
else //AJAY:- Record is not present ADD it
{
AddAvail(Username, Password, date, RoomType, Qty, CurPrice);
}
}
mySQLconnection.Close();
dbCommand.Dispose();
mySQLconnection.Dispose();
return retVal;
}
}
}
}
/*----------------------------------------------------
* //AJAY:- Webmethod AddAvail Adds single availability for a speicifed date
* ---------------------------------------------------*/
[WebMethod(Description = "Single Add of Availability", BufferResponse = true)]
public void AddAvail(string Username, string Password, DateTime date, 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();
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();
}
}
}
/*-----------------------------------------------------
* //AJAY:- Webmethod AmdAvail Amends single Availability for a specified date
* -----------------------------------------------------*/
[WebMethod(Description = "Single Update", BufferResponse = true)]
public DataSet AmdAvail(string Username, string Password, DateTime date, 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())
{
//AJAY:- Just pass a single date to update
dbCommand.CommandText = "Update Available set intQty=@Qty,curprice=@CurPrice where dtm = @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", date);
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;
}
}
}
}
我尚未编译代码，因此可能存在一些编译错误
我希望这能解决你的问题。