public void ExecuteNonQuery(string query, SqlServerParameter parameter, ExecuteTypeEnum type)
{
using (_con = new SqlConnection(ConnectionString))
{
_con.Open();
_cmd = new SqlCommand
{
Connection = _con,
CommandType = type == ExecuteTypeEnum.StoredProcedure
? CommandType.StoredProcedure
: CommandType.Text,
CommandText = query,
CommandTimeout = ConnectDbTimeOut
};
if (parameter != null)
{
_cmd.Parameters.AddRange(parameter.ToArray());
}
_cmd.ExecuteNonQuery();
_con.Dispose();
if (_con.State == ConnectionState.Open)
{
_con.Close();
}
}
}
public DataTable ExecuteToTable(string query, SqlServerParameter parameter, ExecuteTypeEnum type)
{
using (_con = new SqlConnection(ConnectionString))
{
_con.Open();
_cmd = new SqlCommand
{
Connection = _con
};
_cmd.Parameters.Clear();
_cmd.CommandType = type == ExecuteTypeEnum.StoredProcedure ? CommandType.StoredProcedure : CommandType.Text;
_cmd.CommandText = query;
_cmd.CommandTimeout = ConnectDbTimeOut;
if (parameter != null)
{
_cmd.Parameters.AddRange(parameter.ToArray());
}
_adapter = new SqlDataAdapter(_cmd);
DataTable tbl = new DataTable();
_adapter.Fill(tbl);
_adapter.Dispose();
if (_con.State == ConnectionState.Open)
{
_con.Close();
}
return(tbl);
}
}
public static int Save(FitnessClass fitnessClassToSave)
{
int result = 0;
ExecuteTypeEnum queryId = ExecuteTypeEnum.InsertItem;
if (fitnessClassToSave.FitnessClassId > 0)
{
queryId = ExecuteTypeEnum.UpdateItem;
}
using (SqlConnection myConnection = new SqlConnection(AppConfiguration.ConnectionString))
{
using (SqlCommand myCommand = new SqlCommand("usp_ExecuteFitnessClass", myConnection))
{
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.Parameters.AddWithValue("@QueryId", queryId);
myCommand.Parameters.AddWithValue("@FitnessClassId", fitnessClassToSave.FitnessClassId);
myCommand.Parameters.AddWithValue("@FitnessClassName", fitnessClassToSave.FitnessClassName);
myCommand.Parameters.Add(HelperDAL.GetReturnParameterInt("ReturnValue"));
myConnection.Open();
myCommand.ExecuteNonQuery();
result = (int)myCommand.Parameters["@ReturnValue"].Value;
}
myConnection.Close();
}
return(result);
}
public T ExecuteScalar <T>(string query, SqlServerParameter parameter, ExecuteTypeEnum type)
{
using (_con = new SqlConnection(ConnectionString))
{
_con.Open();
_cmd = new SqlCommand
{
Connection = _con,
CommandType = type == ExecuteTypeEnum.StoredProcedure
? CommandType.StoredProcedure
: CommandType.Text,
CommandText = query,
CommandTimeout = ConnectDbTimeOut
};
if (parameter != null)
{
_cmd.Parameters.AddRange(parameter.ToArray());
}
object obj = _cmd.ExecuteScalar();
_con.Close();
return((T)obj);
}
}
public bool Equals(ExecuteTypeEnum obj)
{
if ((object)obj == null)
{
return(false);
}
return(StringComparer.OrdinalIgnoreCase.Equals(this.Value, obj.Value));
}
//public static Instructor GetItem(int instructorID)
//{
// Instructor tempItem = null;
// using (SqlConnection myConnection = new SqlConnection(AppConfiguration.ConnectionString))
// {
// using (SqlCommand myCommand = new SqlCommand("usp_GetInstructor", myConnection))
// {
// myCommand.CommandType = CommandType.StoredProcedure;
// myCommand.Parameters.AddWithValue("@QueryID", SelectTypeEnum.GetItem);
// myCommand.Parameters.AddWithValue("@InstructorId", instructorID);
// myConnection.Open();
// using (SqlDataReader myReader = myCommand.ExecuteReader())
// {
// if (myReader.Read())
// {
// tempItem = FillDataRecord(myReader);
// }
// myReader.Close();
// }
// }
// }
// return tempItem;
//}
#region INSERT and UPDATE
/// <summary>
/// Saves the Instructor to the Databese. Determines to INSERT or UPDATE based on
/// valid instructorId
/// </summary>
/// <param name="instructorToSave"></param>
/// <returns></returns>
public static int Save(Instructor instructorToSave)
{
int result = 0;
ExecuteTypeEnum queryId = ExecuteTypeEnum.InsertItem;
if (instructorToSave.InstructorId > 0)
{
queryId = ExecuteTypeEnum.UpdateItem;
}
using (SqlConnection myConnection = new SqlConnection(AppConfiguration.ConnectionString))
{
using (SqlCommand myCommand = new SqlCommand("usp_ExecuteInstructor", myConnection))
{
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.Parameters.AddWithValue("@QueryId", queryId);
myCommand.Parameters.AddWithValue("@InstructorId", instructorToSave.InstructorId);
myCommand.Parameters.AddWithValue("@PersonId", instructorToSave.PersonId);
if (instructorToSave.HireDate != DateTime.MinValue)
{
myCommand.Parameters.AddWithValue("@HireDate", instructorToSave.HireDate.ToShortDateString());
}
if (instructorToSave.TermDate != DateTime.MinValue)
{
myCommand.Parameters.AddWithValue("@TermDate", instructorToSave.TermDate.ToShortDateString());
}
if (instructorToSave.Description != null)
{
myCommand.Parameters.AddWithValue("@Description", instructorToSave.Description);
}
if (instructorToSave.EntityTypeId != 0)
{
myCommand.Parameters.AddWithValue("@EntityTypeId", instructorToSave.EntityTypeId);
}
//Add return output parameter to command object
myCommand.Parameters.Add(HelperDAL.GetReturnParameterInt("ReturnValue"));
myConnection.Open();
myCommand.ExecuteNonQuery();
result = (int)myCommand.Parameters["@ReturnValue"].Value;
}
myConnection.Close();
}
return(result);
}
public static int Save(Gym gymToSave)
{
int result = 0;
ExecuteTypeEnum queryId = ExecuteTypeEnum.InsertItem;
if (gymToSave.GymId > 0)
{
queryId = ExecuteTypeEnum.UpdateItem;
}
using (SqlConnection myConnection = new SqlConnection(AppConfiguration.ConnectionString))
{
using (SqlCommand myCommand = new SqlCommand("usp_ExecuteGym", myConnection))
{
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.Parameters.AddWithValue("@QueryId", queryId);
myCommand.Parameters.AddWithValue("@GymId", gymToSave.GymId);
if (gymToSave.GymName != null)
{
myCommand.Parameters.AddWithValue("@GymName", gymToSave.GymName);
}
if (gymToSave.Abbreviation != null)
{
myCommand.Parameters.AddWithValue("@GymAbbreviation", gymToSave.Abbreviation);
}
if (gymToSave.WebSite != null)
{
myCommand.Parameters.AddWithValue("@WebSite", gymToSave.WebSite);
}
if (gymToSave.Description != null)
{
myCommand.Parameters.AddWithValue("@Description", gymToSave.Description);
}
//if (gymToSave.CreateDate != DateTime.MinValue)
// myCommand.Parameters.AddWithValue("@CreateDate", gymToSave.CreateDate.ToShortDateString());
myCommand.Parameters.Add(HelperDAL.GetReturnParameterInt("ReturnValue"));
myConnection.Open();
myCommand.ExecuteNonQuery();
result = (int)myCommand.Parameters["@ReturnValue"].Value;
}
myConnection.Close();
}
return(result);
}
public static int Save(Location locationToSave)
{
int result = 0;
ExecuteTypeEnum queryId = ExecuteTypeEnum.InsertItem;
if (locationToSave.ID > 0)
{
queryId = ExecuteTypeEnum.UpdateItem;
}
using (SqlConnection myConnection = new SqlConnection(AppConfiguration.ConnectionString))
{
using (SqlCommand myCommand = new SqlCommand("usp_ExecuteLocation", myConnection))
{
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.Parameters.AddWithValue("@QueryId", queryId);
myCommand.Parameters.AddWithValue("@LocationId", locationToSave.ID);
if (locationToSave.Name != null)
{
myCommand.Parameters.AddWithValue("@LocationName", locationToSave.Name);
}
if (locationToSave.Address01 != null)
{
myCommand.Parameters.AddWithValue("@Abbreviation", locationToSave.Address01);
}
if (locationToSave.Address02 != null)
{
myCommand.Parameters.AddWithValue("@Website", locationToSave.Address02);
}
if (locationToSave.City != null)
{
myCommand.Parameters.AddWithValue("@Description", locationToSave.City);
}
myCommand.Parameters.Add(HelperDAL.GetReturnParameterInt("ReturnValue"));
myConnection.Open();
myCommand.ExecuteNonQuery();
result = (int)myCommand.Parameters["@ReturnValue"].Value;
}
myConnection.Close();
}
return(result);
}
public static int Save(int entityId, EntityType entityTypeToSave)
{
int result = 0;
ExecuteTypeEnum queryId = ExecuteTypeEnum.InsertItem;
if (entityTypeToSave.EntityTypeId > 0)
{
queryId = ExecuteTypeEnum.UpdateItem;
}
using (SqlConnection myConnection = new SqlConnection(AppConfiguration.ConnectionString))
{
using (SqlCommand myCommand = new SqlCommand("usp_ExecuteEntityType", myConnection))
{
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.Parameters.AddWithValue("@QueryId", queryId);
myCommand.Parameters.AddWithValue("@EntityId", entityId);
myCommand.Parameters.AddWithValue("@EntityTypeId", entityTypeToSave.EntityTypeId);
if (entityTypeToSave.EntityTypeName != null)
{
myCommand.Parameters.AddWithValue("@EntityTypeName", entityTypeToSave.EntityTypeName);
}
if (entityTypeToSave.DisplayName != null)
{
myCommand.Parameters.AddWithValue("@DisplayName", entityTypeToSave.DisplayName);
}
if (entityTypeToSave.Description != null)
{
myCommand.Parameters.AddWithValue("@Description", entityTypeToSave.Description);
}
myCommand.Parameters.Add(HelperDAL.GetReturnParameterInt("ReturnValue"));
myConnection.Open();
myCommand.ExecuteNonQuery();
result = (int)myCommand.Parameters["@ReturnValue"].Value;
}
myConnection.Close();
}
return(result);
}
public static int Save(int instructorId, EmailAddress emailToSave)
{
int result = 0;
ExecuteTypeEnum queryId = ExecuteTypeEnum.InsertItem;
if (emailToSave.EmailId > 0)
{
queryId = ExecuteTypeEnum.UpdateItem;
}
using (SqlConnection myConnection = new SqlConnection(AppConfiguration.ConnectionString))
{
using (SqlCommand myCommand = new SqlCommand("usp_ExecuteEmail", myConnection))
{
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.Parameters.AddWithValue("@QueryId", queryId);
myCommand.Parameters.AddWithValue("@EmailId", emailToSave.EmailId);
myCommand.Parameters.AddWithValue("@InstructorId", instructorId);
myCommand.Parameters.AddWithValue("@EntityTypeId", emailToSave.EmailType.EntityTypeId);
if (emailToSave.EmailValue != null)
{
myCommand.Parameters.AddWithValue("@EmailAddress", emailToSave.EmailValue);
}
myCommand.Parameters.Add(HelperDAL.GetReturnParameterInt("ReturnValue"));
myConnection.Open();
myCommand.ExecuteNonQuery();
result = (int)myCommand.Parameters["@ReturnValue"].Value;
}
myConnection.Close();
}
return(result);
}
public T ExecuteScalarFunction <T>(string query, SqlServerParameter parameter, ExecuteTypeEnum type)
{
using (_con = new SqlConnection(ConnectionString))
{
_cmd = new SqlCommand
{
Connection = _con
};
_cmd.Parameters.Clear();
_cmd.CommandType = type == ExecuteTypeEnum.StoredProcedure ? CommandType.StoredProcedure : CommandType.Text;
_cmd.CommandText = query;
_cmd.CommandTimeout = ConnectDbTimeOut;
if (parameter != null)
{
_cmd.Parameters.AddRange(parameter.ToArray());
}
SqlParameter returnValue = _cmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Text);
returnValue.Direction = ParameterDirection.ReturnValue;
_con.Open();
_cmd.ExecuteNonQuery();
if (_con.State == ConnectionState.Open)
{
_con.Close();
}
return((T)returnValue.Value);
}
}
/// <summary>
/// Saves the Person to the Databese. Determines to INSERT or UPDATE based on
/// valid personId
/// </summary>
/// <param name="personToSave"></param>
/// <returns></returns>
public static int Save(Person personToSave)
{
int result = 0;
ExecuteTypeEnum queryId = ExecuteTypeEnum.InsertItem;
if (personToSave.PersonId > 0)
{
queryId = ExecuteTypeEnum.UpdateItem;
}
using (SqlConnection myConnection = new SqlConnection(AppConfiguration.ConnectionString))
{
using (SqlCommand myCommand = new SqlCommand("usp_ExecutePerson", myConnection))
{
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.Parameters.AddWithValue("@QueryId", queryId);
if (personToSave.PersonId > 0)
{
myCommand.Parameters.AddWithValue("@PersonId", personToSave.PersonId);
}
if (personToSave.FirstName != null)
{
myCommand.Parameters.AddWithValue("@FirstName", personToSave.FirstName);
}
if (personToSave.LastName != null)
{
myCommand.Parameters.AddWithValue("@LastName", personToSave.LastName);
}
if (personToSave.DisplayFirstName != null)
{
myCommand.Parameters.AddWithValue("@DisplayFirstName", personToSave.DisplayFirstName);
}
if (personToSave.Gender != null)
{
myCommand.Parameters.AddWithValue("@Gender", personToSave.Gender);
}
if (personToSave.BirthDate != DateTime.MinValue)
{
myCommand.Parameters.AddWithValue("@BirthDate", personToSave.BirthDate.ToShortDateString());
}
//Add return output parameter to command object
myCommand.Parameters.Add(HelperDAL.GetReturnParameterInt("ReturnValue"));
myConnection.Open();
myCommand.ExecuteNonQuery();
result = (int)myCommand.Parameters["@ReturnValue"].Value;
}
myConnection.Close();
}
return(result);
}
// executes stored procedure with DB parameteres if they are passed
private object ExecuteProcedure(string procedureName, ExecuteTypeEnum executeType, List <MySqlParameter> parameters)
{
object returnObject = null;
if (Connection != null)
{
if (Connection.State == ConnectionState.Open)
{
if (executeType == ExecuteTypeEnum.ExecuteFunctionTable || executeType == ExecuteTypeEnum.ExecuteFunctionScalar)
{
string par = "";
foreach (MySqlParameter dbParameter in parameters)
{
par += "@" + dbParameter.ParameterName + ",";
}
if (par.Length > 1)
{
par = par.TrimEnd(',');
}
if (executeType == ExecuteTypeEnum.ExecuteFunctionTable)
{
procedureName = "SELECT * FROM " + procedureName + "(" + par + ")";
}
if (executeType == ExecuteTypeEnum.ExecuteFunctionScalar)
{
procedureName = "SELECT " + procedureName + "(" + par + ")";
}
Command = new MySqlCommand(procedureName, Connection);
foreach (MySqlParameter dbParameter in parameters)
{
Command.Parameters.AddWithValue("@" + dbParameter.ParameterName, dbParameter.Value);
}
Command.CommandType = CommandType.Text;
}
else
{
Command = new MySqlCommand(procedureName, Connection);
Command.CommandType = CommandType.StoredProcedure;
// pass stored procedure parameters to command
if (parameters != null)
{
Command.Parameters.Clear();
foreach (MySqlParameter dbParameter in parameters)
{
MySqlParameter parameter = new MySqlParameter();
parameter.ParameterName = "@" + dbParameter.ParameterName;
parameter.Direction = dbParameter.Direction;
parameter.Value = dbParameter.Value ?? DBNull.Value;
Command.Parameters.Add(parameter);
}
}
}
switch (executeType)
{
case ExecuteTypeEnum.ExecuteReader:
case ExecuteTypeEnum.ExecuteFunctionTable:
returnObject = Command.ExecuteReader();
break;
case ExecuteTypeEnum.ExecuteNonQuery:
returnObject = Command.ExecuteNonQuery();
break;
case ExecuteTypeEnum.ExecuteScalar:
case ExecuteTypeEnum.ExecuteFunctionScalar:
returnObject = Command.ExecuteScalar();
break;
default:
break;
}
}
}
return(returnObject);
}
