public ShelfLocationInformation getShelfLocation(string decodedRFID)
{
//string sqlCommandString = "select * from " + shelfDecodedRFIDToLocationTable + " where code=" + "'" + decodedRFID + "'";
string sqlCommandString = "select * from " + shelfDecodedRFIDToLocationTable + " where code=@decodedRFID";
try
{
ShelfLocationInformation oneShelf = new ShelfLocationInformation();
connectionDatabase();
SqlCommand readCommand = new SqlCommand(sqlCommandString, sqlConnection);
readCommand.Parameters.Add(new SqlParameter("decodedRFID",decodedRFID));
SqlDataReader sqlReader = readCommand.ExecuteReader();
sqlReader.Read();
oneShelf.floor = Int32.Parse(sqlReader["floor"].ToString().Trim());
oneShelf.selection = sqlReader["selection"].ToString().Trim();
oneShelf.row = Int32.Parse(sqlReader["row"].ToString().Trim());
oneShelf.side = sqlReader["side"].ToString().Trim();
oneShelf.col = Int32.Parse(sqlReader["col"].ToString().Trim());
oneShelf.shelf = Int32.Parse(sqlReader["shelf"].ToString().Trim());
oneShelf.code = sqlReader["code"].ToString().Trim();
return oneShelf;
}
catch (System.InvalidOperationException) //数据库查询失败
{
throw new DataBaseQueryException();
}
catch(System.Data.SqlClient.SqlException ) //数据库连接失败
{
throw new DataBaseConnectException();
}
catch(Exception e)
{
throw e;
}
finally
{
disconnectionDatabase();
}
}
public void saveBookLocation(string decodedBookRFIDCode,ShelfLocationInformation shelfLocation)
{
string sqlCommandStringForDelete= "delete from "+bookDecodedRFIDToLocationTable+" where bookDecodedRFID=@bookDecodedRFID";
string sqlCommandStringForInsert= "insert into "+bookDecodedRFIDToLocationTable+" (floor,selection,row,side,col,shelf,code,bookDecodedRFID) VALUES (@floor,@selection,@row,@side,@col,@shelf,@code,@bookDecodedRFID)";
/***
ShelfLocationInformation shelfLocation = new ShelfLocationInformation();
shelfLocation.floor = 1;
shelfLocation.selection = "W";
shelfLocation.row = 2;
shelfLocation.side = "A";
shelfLocation.col = 10;
shelfLocation.shelf = 3;
shelfLocation.code = "203343434";
***/
try
{
connectionDatabase();
//首先删除指定条目，参数为图书的rfid码
SqlCommand deleteCommand = new SqlCommand(sqlCommandStringForDelete, sqlConnection);
deleteCommand.Parameters.Add(new SqlParameter("bookDecodedRFID", decodedBookRFIDCode));
deleteCommand.ExecuteNonQuery();
//再添加这个条目
SqlCommand insertCommand = new SqlCommand(sqlCommandStringForInsert, sqlConnection);
//insertCommand.Parameters.Add(new SqlParameter("table", bookDecodedRFIDToLocationTable));
insertCommand.Parameters.Add(new SqlParameter("floor", shelfLocation.floor));
insertCommand.Parameters.Add(new SqlParameter("selection", shelfLocation.selection));
insertCommand.Parameters.Add(new SqlParameter("row", shelfLocation.row));
insertCommand.Parameters.Add(new SqlParameter("side", shelfLocation.side));
insertCommand.Parameters.Add(new SqlParameter("col", shelfLocation.col));
insertCommand.Parameters.Add(new SqlParameter("shelf", shelfLocation.shelf));
insertCommand.Parameters.Add(new SqlParameter("code", shelfLocation.code));
insertCommand.Parameters.Add(new SqlParameter("bookDecodedRFID", decodedBookRFIDCode));
insertCommand.ExecuteNonQuery();
}
catch (System.InvalidOperationException) //数据库查询失败
{
throw new DataBaseQueryException();
}
catch (System.Data.SqlClient.SqlException) //数据库连接失败
{
throw new DataBaseConnectException();
}
catch (Exception e)
{
throw e;
}
finally
{
disconnectionDatabase();
}
}
public string ShelfLocationInformationToString(ShelfLocationInformation oneShelf)
{
string desc="图书馆第";
desc = desc + oneShelf.floor +"层 ";
if(oneShelf.selection.Equals("W"))
{
desc = desc + "西区 ";
}
else if (oneShelf.selection.Equals("E"))
{
desc = desc + "东区 ";
}
desc = desc +"第"+oneShelf.row + "行 ";
desc = desc +"第"+ oneShelf.col + "列 ";
desc = desc + oneShelf.side + "面 ";
desc = desc + "书架第" + oneShelf.shelf + "层";
return desc;
}