/// <summary>
/// access database
/// </summary>
/// <param name="prostorename">Stored procedure name</param>
/// <param name="param">array list for SqlParameter</param>
/// <returns>result</returns>
public static int ExcuteNoneQuery(string connectionString, string prostorename, SqlParameter[] param)
{
int result = -1;
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = connection;
connection.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = prostorename;
if (param.Count() > 0)
{
for (int i = 0; i < param.Count(); i++)
{
cmd.Parameters.Add(param[i]);
}
cmd.Parameters.Add("@num", "").Direction = ParameterDirection.ReturnValue;
}
cmd.ExecuteNonQuery();
result = (int)cmd.Parameters["@num"].Value;
}
return result;
}
public int BookCar(int carId, string carNumber, DateTime travelDate, bool isLocked, string passengerSSN, string updatedUser)
{
string sCommandText = "dbo.[BookCar]";
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("@carId", carId),
new SqlParameter("@carNumber", carNumber),
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 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();
}
}
}
// overload to allow parameter arrays
private string ScalarValue(string sCommandText, SqlParameter[] SqlParameter)
{
string s; // temporary return string container
try
{
// open Connection
mySqlConnection.Open();
// configure Command, add Parameter
mySqlCommand = new SqlCommand();
mySqlCommand.Connection = mySqlConnection;
mySqlCommand.CommandText = sCommandText;
mySqlCommand.CommandType = CommandType.StoredProcedure;
for (int a = 0; a < SqlParameter.Count(); a++)
{
mySqlCommand.Parameters.Add(SqlParameter[a]);
}
s = mySqlCommand.ExecuteScalar().ToString();
// close Connection
mySqlConnection.Close();
// return ExecuteScalar value
return s;
}
catch (ApplicationException aex)
{
throw aex;
}
catch (Exception ex)
{
throw ex;
}
finally
{
// force to close connection
if (mySqlConnection.State != ConnectionState.Closed)
{
mySqlConnection.Close();
}
}
}
/// </summary>
/// Overloaded FillTable method with Array Parameters
/// <param name="sCommandText">dbo.Object</param>
/// <param name="SqlParameter">SQL Parameter</param>
/// <returns>Query result as DataTable</returns>
private DataTable FillTable(string sCommandText, SqlParameter[] SqlParameter)
{
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;
for (int a = 0; a < SqlParameter.Count(); a++)
{
mySqlCommand.Parameters.Add(SqlParameter[a]);
}
// setup dataAdapater and fill datatTable
mySqlDataAdapter = new SqlDataAdapter(mySqlCommand);
mySqlDataAdapter.Fill(myDataTable);
// close connection
return myDataTable;
}
catch (Exception ex)
{
throw ex;
}
finally
{
mySqlConnection.Close();
// force close connection
if (mySqlConnection.State != ConnectionState.Closed)
{
mySqlConnection.Close();
}
}
}
// overload to allow parameter arrays
private string ScalarValue(string sCommandText, SqlParameter[] SqlParameter)
{
string s; // temporary return string container
try
{
// open Connection
mySqlConnection.Open();
// configure Command, add Parameter
mySqlCommand = new SqlCommand();
mySqlCommand.Connection = mySqlConnection;
mySqlCommand.CommandText = sCommandText;
mySqlCommand.CommandType = CommandType.StoredProcedure;
for (int a = 0; a < SqlParameter.Count(); a++)
{
mySqlCommand.Parameters.Add(SqlParameter[a]);
}
s = mySqlCommand.ExecuteScalar().ToString();
//// validate scalar value if not dbNull, then if not stringNull/Empty (short-circuited)
//if (mySqlCommand.ExecuteScalar() != null &&
// !String.IsNullOrEmpty(Convert.ToString(mySqlCommand.ExecuteScalar())))
//{
// s = mySqlCommand.ExecuteScalar().ToString();
//}
//else
//{
// // s = String.Empty;
// // throw an error
// throw new ApplicationException("NullScalarValue");
//}
// close Connection
mySqlConnection.Close();
// return ExecuteScalar value
return s;
}
catch (ApplicationException aex)
{
throw aex;
}
catch (Exception ex)
{
throw ex;
}
finally
{
// force to close connection
if (mySqlConnection.State != ConnectionState.Closed)
{
mySqlConnection.Close();
}
}
}
public int UpdateSlot(int bookId, int carId, 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("@carBookingId", bookId),
new SqlParameter("@carId", carId),
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 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("@carBookingId", 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();
}
}
}