/// <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(); } } }