//====================================================================================================
// Function for executing queries that return data of a single field
public object ExecuteScalar(ISqlQueryData queryData)
{
ResetConnection();
if (!queryData.IsUsable)
{
return(false);
}
using (var command = new SqlCommand(queryData.SqlQuery, conn))
{
command.CommandType = queryData.CommandType;
if (queryData.HasParameters)
{
for (int i = 0; i < queryData.NrOfParameters; i++)
{
command.Parameters.Add(queryData.SqlParameter.ElementAt(0), queryData.GetNextParameterType());
command.Parameters[i].Value = queryData.GetNextParameterValue();
}
}
conn.Open();
var result = command.ExecuteScalar();
conn.Close();
return(result);
}
}
//====================================================================================================
// Function for executing queries that do not return data (e.g. UPDATE, INSERT, DELETE, etc.)
// returns number of affected rows
public int ExecuteNonQuery(ISqlQueryData queryData)
{
ResetConnection();
using (var command = new SqlCommand(queryData.SqlQuery, conn))
{
command.CommandType = queryData.CommandType;
if (queryData.HasParameters)
{
for (int i = 0; i < queryData.NrOfParameters; i++)
{
command.Parameters.Add(queryData.SqlParameter.ElementAt(0), queryData.GetNextParameterType());
command.Parameters[i].Value = queryData.GetNextParameterValue();
}
}
var idParameter = new SqlParameter("@ID", SqlDbType.Int)
{
Direction = ParameterDirection.Output
};
command.Parameters.Add(idParameter);
conn.Open();
int result = command.ExecuteNonQuery();
conn.Close();
return(result);
}
}
//====================================================================================================
// Function for execution regular SELECT queries that return multiple fields
public SqlDataReader ExecuteReader(ISqlQueryData queryData)
{
ResetConnection();
using (var command = new SqlCommand(queryData.SqlQuery, conn))
{
command.CommandType = queryData.CommandType;
if (queryData.HasParameters)
{
for (int i = 0; i < queryData.NrOfParameters; i++)
{
command.Parameters.Add(queryData.SqlParameter.ElementAt(0), queryData.GetNextParameterType());
command.Parameters[i].Value = queryData.GetNextParameterValue();
}
}
conn.Open();
SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
return(reader);
}
}
public int GetSingleValue(ISqlQueryData queryData)
{
int result = Int32.Parse(RunQuery.GetInstance.ExecuteScalar(queryData).ToString());
return(result);
}
public int Create(ISqlQueryData queryData)
{
int lastWrittenId = (int)(decimal)RunQuery.GetInstance.ExecuteScalar(queryData);
return(lastWrittenId);
}
public bool Delete(ISqlQueryData queryData)
{
int result = RunQuery.GetInstance.ExecuteNonQuery(queryData);
return((result >= 1) ? true : false);
}
public SqlDataReader Get(ISqlQueryData queryData)
{
SqlDataReader reader = query.ExecuteReader(queryData);
return((reader.HasRows) ? reader : null);
}
