/// <summary>
/// Deletes the specifed test
/// </summary>
/// <param name="adminId"></param>
public static void Delete(Guid testId)
{
Query query = new Query(SqlQueryType.SqlStoredProc, "TestDelete");
query.AddParameter("@TestId", testId);
DBManager.Execute(query);
}
/// <summary>
/// Adds page access to the specified account type
/// </summary>
/// <param name="pageId"></param>
/// <param name="accountTypeId"></param>
public static void AddAccountTypeAccess(int pageId, int accountTypeId)
{
Query query = new Query(SqlQueryType.SqlStoredProc, "PageAddAccess");
query.AddParameter("@PageId", pageId);
query.AddParameter("@AccountTypeId", accountTypeId);
DBManager.Execute(query);
}
/// <summary>
/// Deletes an item for the vendor
/// </summary>
/// <param name="vendorId"></param>
/// <param name="itemName"></param>
public static void Delete(int vendorId, string itemName)
{
Query query = new Query(SqlQueryType.SqlStoredProc, "ItemDelete");
query.AddParameter("@VendorID", vendorId);
query.AddParameter("@ItemName", itemName);
DBManager.Execute(query);
}
/// <summary>
/// Creates an account
/// </summary>
/// <param name="accountId"></param>
/// <param name="accountTypeId"></param>
/// <param name="accountUsername"></param>
/// <param name="accountPassword"></param>
/// <returns></returns>
public static void Create(int accountId, int accountTypeId, string accountUsername, string accountPassword)
{
Query query = new Query(SqlQueryType.SqlStoredProc, "AccountSave");
query.AddParameter("@AccountId", accountId);
query.AddParameter("@AccountTypeId", accountTypeId);
query.AddParameter("@AccountUsername", accountUsername);
query.AddParameter("@AccountPassword", accountPassword);
DBManager.Execute(query);
}
/// <summary>
/// Creates an item for the vendor
/// </summary>
/// <param name="vendorId"></param>
/// <param name="itemName"></param>
/// <param name="itemDesc"></param>
/// <param name="itemPrice"></param>
/// <param name="itemProcessingTime"></param>
public static void Create(int vendorId, string itemName, string itemDesc, decimal itemPrice, int itemProcessingTime)
{
Query query = new Query(SqlQueryType.SqlStoredProc, "ItemSave");
query.AddParameter("@VendorId", vendorId);
query.AddParameter("@ItemName", itemName);
query.AddParameter("@ItemDesc", itemDesc);
query.AddParameter("@ItemPrice", itemPrice);
query.AddParameter("@ItemProcessingTime", itemProcessingTime);
DBManager.Execute(query);
}
public static int Create(string eventTypeName, string eventTypeDesc)
{
int? eventTypeId = null;
Query query = new Query(SqlQueryType.SqlStoredProc, "EventTypeSave");
query.AddParameter("@EventTypeName", eventTypeName);
query.AddParameter("@EventTypeDesc", eventTypeDesc);
DBManager.ExecuteScalar(query, ref eventTypeId);
return eventTypeId.Value;
}
/// <summary>
/// Creates a new page
/// </summary>
/// <param name="pageName"></param>
/// <param name="pageUrl"></param>
/// <returns></returns>
public static bool CheckAccess(int pageId, int accountTypeId)
{
int? newPageId = null;
Query query = new Query(SqlQueryType.SqlStoredProc, "PageCheckAccess");
query.AddParameter("@PageId", pageId);
query.AddParameter("@AccountTypeId", accountTypeId);
DBManager.ExecuteScalar(query, ref newPageId);
return newPageId.HasValue;
}
/// <summary>
/// Creates a new page
/// </summary>
/// <param name="pageName"></param>
/// <param name="pageUrl"></param>
/// <returns></returns>
public static int Create(string pageName, string pageUrl)
{
int? pageId = null;
Query query = new Query(SqlQueryType.SqlStoredProc, "PageSave");
query.AddParameter("@PageName", pageName);
query.AddParameter("@PageUrl", pageUrl);
DBManager.ExecuteScalar(query, ref pageId);
return pageId.Value;
}
/// <summary>
/// Saves the specified test into the database
/// </summary>
/// <returns></returns>
public static Guid Save(Guid? testId)
{
Guid returnId = Guid.Empty;
Query query = new Query(SqlQueryType.SqlStoredProc, "TestSave");
query.AddParameter("@TestId", testId);
DBManager.ExecuteScalar(query, ref returnId);
return returnId;
}
/// <summary>
/// Executes a query object
/// </summary>
/// <param name="queryObject"></param>
public void Execute(Query queryObject)
{
SqlCommand command = new SqlCommand();
try
{
// Check the connection state
CheckConnection();
// Open the connection
mConnection.Open();
// Set the connection for the command object
command.Connection = mConnection;
// Set the timeout for the command object
command.CommandTimeout = queryObject.QueryTimeOut;
// Set the command text
command.CommandText = queryObject.QueryString;
// Set the Command Type, if type is store procedure
if (queryObject.QueryType.Equals(SqlQueryType.SqlStoredProc))
{
command.CommandType = CommandType.StoredProcedure;
}
// Set query parameters
SetParameters(queryObject, ref command);
// Execute the SQL
command.ExecuteNonQuery();
}
catch (SqlException ex)
{
if (ex.State == 17)
{
throw new Exception(ex.Message);
}
else
{
throw ex;
}
}
finally
{
// Close DB Connection
mConnection.Close();
command = null;
}
}
/// <summary>
/// Creates an Account Type Object
/// </summary>
/// <param name="accountTypeName"></param>
/// <param name="accountTypeDesc"></param>
/// <param name="landingUrl"></param>
/// <returns></returns>
public static int Create(string accountTypeName, string accountTypeDesc, string landingUrl)
{
int? accountTypeId = null;
Query query = new Query(SqlQueryType.SqlStoredProc, "AccountTypeSave");
query.AddParameter("@AccountTypeName", accountTypeName);
query.AddParameter("@AccountTypeDesc", accountTypeDesc);
query.AddParameter("@AccountTypeLandingUrl", landingUrl);
DBManager.ExecuteScalar(query, ref accountTypeId);
return accountTypeId.Value;
}
/// <summary>
/// Creates a company
/// </summary>
/// <param name="name"></param>
/// <param name="address"></param>
/// <param name="city"></param>
/// <param name="state"></param>
/// <param name="zip"></param>
/// <param name="phone"></param>
/// <param name="email"></param>
/// <param name="contactName"></param>
/// <returns></returns>
public static int Create(string name, string address, string city, string state, string zip, string phone, string email, string contactName)
{
int? companyId = null;
Query query = new Query(SqlQueryType.SqlStoredProc, "CompanySave");
query.AddParameter("@CompanyName", name);
query.AddParameter("@CompanyAddress", address);
query.AddParameter("@CompanyCity", city);
query.AddParameter("@CompanyState", state);
query.AddParameter("@CompanyZip", zip);
query.AddParameter("@CompanyPhone", phone);
query.AddParameter("@CompanyEmail", email);
query.AddParameter("@companyContactName", contactName);
DBManager.ExecuteScalar(query, ref companyId);
return companyId.Value;
}
/// <summary>
/// Creates a vendor
/// </summary>
/// <param name="name"></param>
/// <param name="address"></param>
/// <param name="city"></param>
/// <param name="state"></param>
/// <param name="zip"></param>
/// <param name="phone"></param>
/// <param name="email"></param>
/// <param name="contactName"></param>
/// <returns></returns>
public static int Create(string name, string address, string city, string state, string zip, string phone, string email, string contactName)
{
int? vendorId = null;
Query query = new Query(SqlQueryType.SqlStoredProc, "VendorSave");
query.AddParameter("@VendorName", name);
query.AddParameter("@VendorAddress", address);
query.AddParameter("@VendorCity", city);
query.AddParameter("@VendorState", state);
query.AddParameter("@VendorZip", zip);
query.AddParameter("@VendorPhone", phone);
query.AddParameter("@VendorEmail", email);
query.AddParameter("@VendorContactName", contactName);
DBManager.ExecuteScalar(query, ref vendorId);
return vendorId .Value;
}
/// <summary>
/// Create an employee
/// </summary>
/// <param name="companyId"></param>
/// <param name="fName"></param>
/// <param name="lName"></param>
/// <param name="DOB"></param>
/// <param name="StartDate"></param>
/// <param name="shipAddress"></param>
/// <param name="shipCity"></param>
/// <param name="shipState"></param>
/// <param name="shipZip"></param>
/// <returns></returns>
public static int Create(int companyId, string fName, string lName, DateTime DOB, DateTime StartDate, string shipAddress, string shipCity, string shipState, string shipZip)
{
int? newCompanyId = null;
Query query = new Query(SqlQueryType.SqlStoredProc, "EmployeeSave");
query.AddParameter("@CompanyId", companyId);
query.AddParameter("@EmployeeFName", fName);
query.AddParameter("@EmployeeLName", lName);
query.AddParameter("@EmployeeDOB", DOB.Date);
query.AddParameter("@EmployeeStartDate", StartDate.Date);
query.AddParameter("@EmployeeShipAddress", shipAddress);
query.AddParameter("@EmployeeShipCity", shipCity);
query.AddParameter("@EmployeeShipState", shipState);
query.AddParameter("@EmployeeShipZip", shipZip);
DBManager.ExecuteScalar(query, ref newCompanyId);
return newCompanyId.Value;
}
/// <summary>
/// Returns a loginObject if successful login
/// </summary>
/// <param name="username"></param>
/// <param name="password"></param>
/// <returns></returns>
public static LoginObject CheckLogin(string username, string password)
{
LoginObject loginObject = null;
DataTable dt = null;
Query query = new Query(SqlQueryType.SqlStoredProc,"AccountCheckLogin");
query.AddParameter("@Username", username);
query.AddParameter("@Password", password);
DBManager.Execute(query, ref dt);
if (dt.Rows.Count > 0)
{
DataRow row = dt.Rows[0];
int accountId = (int)row["AccountID"];
int accountTypeId = (int)row["AccountTypeID"];
string landingUrl = row["AccountTypeLandingUrl"] as string;
loginObject = new LoginObject { AccountId = accountId, AccountTypeId = accountTypeId, LandingUrl = landingUrl };
}
return loginObject;
}
/// <summary>
/// Executes a query object returning the results in a data set
/// </summary>
/// <param name="queryObejct"></param>
/// <returns></returns>
public DataSet ExecuteAsDataSet(Query queryObejct)
{
SqlCommand command = new SqlCommand();
SqlDataAdapter dataAdapter;
DataSet returnDS = new DataSet();
try
{
// Check the connection state
CheckConnection();
// Open the connection
mConnection.Open();
// Set the connection for the command object
command.Connection = mConnection;
// Set the timeout for the command object
command.CommandTimeout = queryObejct.QueryTimeOut;
// Set the Command Type, if type is store procedure
if (queryObejct.QueryType.Equals(SqlQueryType.SqlStoredProc))
{
command.CommandType = CommandType.StoredProcedure;
}
// Set the command text
command.CommandText = queryObejct.QueryString.ToString();
// Set query parameters
SetParameters(queryObejct, ref command);
// Execute the SQL
dataAdapter = new SqlDataAdapter();
dataAdapter.SelectCommand = command;
dataAdapter.Fill(returnDS, queryObejct.TableName.ToString());
// Return the dataset
return returnDS;
}
catch (SqlException ex)
{
if (ex.State == 17)
{
throw new Exception(ex.Message);
}
else
{
throw ex;
}
}
finally
{
// Close DB Connection
mConnection.Close();
command = null;
dataAdapter = null;
returnDS = null;
}
}
/// <summary>
/// Sets the parameters for a command object
/// </summary>
/// <param name="queryObject"></param>
/// <param name="command"></param>
private void SetParameters(Query queryObject, ref SqlCommand command)
{
foreach (Parameter currentParameter in queryObject.ParameterCollection.ParameterArray)
{
SqlParameter parameter = new SqlParameter();
parameter.ParameterName = currentParameter.Name.ToString();
parameter.Value = currentParameter.Value;
command.Parameters.Add(parameter);
parameter = null;
}
}
/// <summary>
/// Executes the query
/// </summary>
/// <param name="query"></param>
public static void Execute(Query query)
{
VerifyQueryObject(query);
ISqlQuery sqlClass = new SQLServer(GetConnectionString());
sqlClass.Execute(query);
}
/// <summary>
/// Executes a query object returning the results in an int?
/// </summary>
/// <param name="queryObject"></param>
/// <returns></returns>
public int? ExecuteAsInt(Query queryObject)
{
SqlCommand command = null;
object returnValue = null;
try
{
command = new SqlCommand();
// Check the connection state
CheckConnection();
// Open the connection
mConnection.Open();
// Set the connection for the command object
command.Connection = mConnection;
// Set the timeout for the command object
command.CommandTimeout = queryObject.QueryTimeOut;
// Set the Command Type, if type is store procedure
if (queryObject.QueryType.Equals(SqlQueryType.SqlStoredProc))
{
command.CommandType = CommandType.StoredProcedure;
}
// Set the command text
command.CommandText = queryObject.QueryString;
// Set query parameters
SetParameters(queryObject, ref command);
// Execute the SQL
returnValue = command.ExecuteScalar();
// This is a workaround for returning @@IDENTITY as an int
if (returnValue != null && returnValue.GetType() == typeof(decimal))
{
returnValue = Convert.ToInt32(returnValue);
}
// Return the value
return returnValue as int?;
}
finally
{
if (mConnection != null && mConnection.State != ConnectionState.Closed)
{
mConnection.Close();
}
if (command != null)
{
command.Dispose();
}
}
}
/// <summary>
/// Executes the query as a data table
/// </summary>
/// <param name="query"></param>
/// <param name="dataTableObject"></param>
public static void Execute(Query query, ref DataTable dataTableObject)
{
VerifyQueryObject(query);
ISqlQuery sqlClass = new SQLServer(GetConnectionString());
dataTableObject = sqlClass.ExecuteAsDataTable(query);
}
/// <summary>
/// Executes the query as a scalar
/// </summary>
/// <param name="query"></param>
///<param name="returnId></param>
public static void ExecuteScalar(Query query, ref Guid returnId)
{
VerifyQueryObject(query);
ISqlQuery sqlClass = new SQLServer(GetConnectionString());
returnId = (Guid)sqlClass.ExecuteAsScalar(query);
}
/// <summary>
/// Executes the query as a scalar
/// </summary>
/// <param name="query"></param>
/// <param name="returnInt"></param>
public static void ExecuteScalar(Query query, ref int? returnInt)
{
VerifyQueryObject(query);
ISqlQuery sqlClass = new SQLServer(GetConnectionString());
returnInt = sqlClass.ExecuteAsInt(query);
}
