public int BookRoom(int roomId, string roomNumber, int hotelId, DateTime travelDate, bool isLocked, string passengerSSN, string updatedUser)
{
string sCommandText = "dbo.[BookRoom]";
try
{
DataTable myDataTable = new DataTable();
// open connection
mySqlConnection.Open();
// setup command options and parameteres
mySqlCommand = new SqlCommand();
mySqlCommand.Connection = mySqlConnection;
mySqlCommand.CommandText = sCommandText;
mySqlCommand.CommandType = CommandType.StoredProcedure;
// add parameter
SqlParameter[] mySQLParam = new SqlParameter[] {
new SqlParameter("@roomId", roomId),
new SqlParameter("@roomNumber", roomNumber),
new SqlParameter("@hotelId", hotelId),
new SqlParameter("@travelDate", travelDate),
new SqlParameter("@isLocked", isLocked),
new SqlParameter("@passengerSSN", passengerSSN),
new SqlParameter("@updatedUser", updatedUser)
};
for (int a = 0; a < mySQLParam.Count(); a++)
{
mySqlCommand.Parameters.Add(mySQLParam[a]);
}
int id = Convert.ToInt32(mySqlCommand.ExecuteScalar());
// close connection
mySqlConnection.Close();
return(id);
}
catch (Exception ex)
{
throw ex;
}
finally
{
// force close connection
if (mySqlConnection.State != ConnectionState.Closed)
{
mySqlConnection.Close();
}
}
}
public int ConfirmSlot(int bId, string userName, decimal paidAmount)
{
string sCommandText = "dbo.[ConfirmSlot]";
try
{
DataTable myDataTable = new DataTable();
// open connection
mySqlConnection.Open();
// setup command options and parameteres
mySqlCommand = new SqlCommand();
mySqlCommand.Connection = mySqlConnection;
mySqlCommand.CommandText = sCommandText;
mySqlCommand.CommandType = CommandType.StoredProcedure;
// add parameter
SqlParameter[] mySQLParam = new SqlParameter[] {
new SqlParameter("@id", bId),
new SqlParameter("@userName", userName),
new SqlParameter("@paidAmount", paidAmount)
};
for (int a = 0; a < mySQLParam.Count(); a++)
{
mySqlCommand.Parameters.Add(mySQLParam[a]);
}
int id = Convert.ToInt32(mySqlCommand.ExecuteScalar());
// close connection
mySqlConnection.Close();
return(id);
}
catch (Exception ex)
{
throw ex;
}
finally
{
// force close connection
if (mySqlConnection.State != ConnectionState.Closed)
{
mySqlConnection.Close();
}
}
}
public int UpdateSlot(int bookId, int slotNumber, DateTime travelDate)
{
string sCommandText = "dbo.[UpdateSlot]";
try
{
DataTable myDataTable = new DataTable();
// open connection
mySqlConnection.Open();
// setup command options and parameteres
mySqlCommand = new SqlCommand();
mySqlCommand.Connection = mySqlConnection;
mySqlCommand.CommandText = sCommandText;
mySqlCommand.CommandType = CommandType.StoredProcedure;
// add parameter
SqlParameter[] mySQLParam = new SqlParameter[] {
new SqlParameter("@id", bookId),
new SqlParameter("@slotNumber", slotNumber),
new SqlParameter("@travelDate", travelDate)
};
for (int a = 0; a < mySQLParam.Count(); a++)
{
mySqlCommand.Parameters.Add(mySQLParam[a]);
}
int id = Convert.ToInt32(mySqlCommand.ExecuteScalar());
// close connection
mySqlConnection.Close();
return(id);
}
catch (Exception ex)
{
throw ex;
}
finally
{
// force close connection
if (mySqlConnection.State != ConnectionState.Closed)
{
mySqlConnection.Close();
}
}
}
public void EnsureProceListInitialized()
{
XDocument doc = XDocument.Load(_filePath);
XElement root = doc.Root;
this._name = root.Element("database").Attribute("name").Value.Trim();
this._connectionString = root.Element("database").Attribute("connectionString").Value.Trim();
#region 主从库分离
if (this._masterSlave == MasterSlave.Slave)
{
//必须要有从库节点
XElement slave = root.Element("database").Element("slave");
if (slave != null)
{
this._name = slave.Attribute("name").Value.Trim();
this._connectionString = slave.Attribute("connectionString").Value.Trim();
}
}
#endregion
//存储过程所有节点
IEnumerable <XElement> suitableElements = root.Element("database").Elements("procedures");
//是否需要缓存优化？
foreach (XElement proceItem in suitableElements)
{
IEnumerable <Parameter> sqlParameters = from item in proceItem.Elements()
select new Parameter(item.Attribute("name").Value, item.Attribute("type").Value, Convert.ToInt32(item.Attribute("size").Value), Convert.ToInt32(item.Attribute("direction").Value));
SqlParameter[] parameters = new SqlParameter[sqlParameters.Count()];
List <Parameter> sqlParametersList = sqlParameters.ToList();
for (int i = 0; i < parameters.Count(); i++)
{
Parameter param = sqlParametersList[i];
SqlParameter sqlParam = new SqlParameter(param.Name, param.Type, param.Size);
sqlParam.Direction = param.Direction;
parameters[i] = sqlParam;
}
StoredProcedure procedure = new StoredProcedure {
SqlParameters = parameters, Name = proceItem.Attribute("name").Value
};
AddProcedure(procedure);
}
}
public void SaveProfile(string pRequestorId,
string pSalesTeam,
string pSegmentCode,
string pSegmentAreaCode,
string pRole,
string pCulture,
string pCultureTimeZone,
string pProgType,
bool pHPSW,
string pPartnerName,
string pCurrencyCode //14.06 June Release
)
{
string sCommandText = "dbo.[User_Profile_Default_Save]";
try
{
DataTable myDataTable = new DataTable();
// open connection
mySqlConnection.Open();
// setup command options and parameteres
mySqlCommand = new SqlCommand();
mySqlCommand.Connection = mySqlConnection;
mySqlCommand.CommandText = sCommandText;
mySqlCommand.CommandType = CommandType.StoredProcedure;
// add parameter
SqlParameter[] mySQLParam = new SqlParameter[] {
new SqlParameter("@sRequestorId", pRequestorId),
new SqlParameter("@sSalesTeam", pSalesTeam),
new SqlParameter("@sSegmentCode", pSegmentCode),
new SqlParameter("@sSegmentAreaCode", pSegmentAreaCode),
new SqlParameter("@sRole", pRole),
new SqlParameter("@sCulture", pCulture),
new SqlParameter("@sCultureTimeZone", pCultureTimeZone),
new SqlParameter("@sProgType", pProgType),
new SqlParameter("@bHPSW", pHPSW),
new SqlParameter("@sPartnerName", pPartnerName),
new SqlParameter("@sCurrencyCode", pCurrencyCode)
};
for (int a = 0; a < mySQLParam.Count(); a++)
{
mySqlCommand.Parameters.Add(mySQLParam[a]);
}
mySqlCommand.ExecuteNonQuery();
// close connection
mySqlConnection.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
// force close connection
if (mySqlConnection.State != ConnectionState.Closed)
{
mySqlConnection.Close();
}
}
}