public void Insert(User user)
{
string Query = " INSERT INTO " + this.TableName +
" ( " +
" Username, " +
" Password " +
" ) " +
" VALUES (@2, @3); " +
" SELECT CAST(scope_identity() AS int) ";
List <SqlParameter> parameters = new List <SqlParameter>();
parameters.Add(new SqlParameter {
ParameterName = "@1", Value = user.ID
});
parameters.Add(new SqlParameter {
ParameterName = "@2", Value = user.Username
});
parameters.Add(new SqlParameter {
ParameterName = "@3", Value = user.Password
});
using (DBSqlHelper DB = new DBSqlHelper()) {
user.ID = Convert.ToInt32(DB.ExecuteScalar(Query, parameters));
}
}
public bool CheckIfDbExists()
{
string Query = " If Not exists (select * from sysobjects where name='" + Application.ProductName + "'" +
" SELECT 0 " +
" ELSE " +
" SELECT 1 ";
bool exists = false;
using (DBSqlHelper DB = new DBSqlHelper()) {
using (SqlDataReader reader = DB.ExecuteReader(Query, null)) {
do
{
if (Convert.ToInt32(reader[0]) == 0)
{
exists = false;
}
else if (Convert.ToInt32(reader[0]) == 1)
{
exists = true;
}
} while (reader.Read());
}
}
return(exists);
}
private void ExecuteQuery(string Query, string MethodName)
{
try {
using (DBSqlHelper DB = new DBSqlHelper()) {
DB.ExecuteNonQuery(Query, null);
}
} catch (Exception e) {
throw new Exception(MethodName + "\n" + e.Message);
}
}
public Exercise SelectBy(string Where, List <SqlParameter> parameters)
{
Exercise obj = null;
string Query = " SELECT * FROM " + this.TableName + " " + Where;
using (DBSqlHelper DB = new DBSqlHelper()) {
using (SqlDataReader reader = DB.ExecuteReader(Query, parameters)) {
while (reader.Read())
{
obj = CreateObject(reader);
}
}
}
return(obj);
}
public void Delete(ref Exercise exercise)
{
string Query = " DELETE FROM " + this.TableName + " WHERE ID = @1 ";
List <SqlParameter> parameters = new List <SqlParameter>();
parameters.Add(new SqlParameter {
ParameterName = "@1", Value = exercise.ID
});
using (DBSqlHelper DB = new DBSqlHelper()) {
if (DB.ExecuteNonQuery(Query, parameters) > 0)
{
exercise = null;
}
}
}
public void UpdateDBVersion(Configuration config)
{
string Query = " UPDATE " + this.TableName +
" SET " +
" DBVersion = @1";
List <SqlParameter> Parameters = new List <SqlParameter>();
Parameters.Add(new SqlParameter()
{
ParameterName = "@1", Value = config.DBVersion
});
using (DBSqlHelper DB = new DBSqlHelper()) {
DB.ExecuteNonQuery(Query, Parameters);
}
}
public Configuration SelectFirstRecord()
{
Configuration Object = null;
string Query = " SELECT TOP 1 * FROM " + this.TableName;
using (DBSqlHelper DB = new DBSqlHelper()) {
using (SqlDataReader reader = DB.ExecuteReader(Query, null)) {
while (reader.Read())
{
Object = CreateObject(reader);
}
}
}
return(Object);
}
public List <Exercise> SelectAllBy(string where, List <SqlParameter> parameters)
{
List <Exercise> obj = new List <Exercise>();
string Query = " SELECT * FROM " + this.TableName + " " + where;
using (DBSqlHelper DB = new DBSqlHelper()) {
using (SqlDataReader reader = DB.ExecuteReader(Query, parameters)) {
while (reader.Read())
{
obj.Add(CreateObject(reader));
}
}
}
return(obj);
}
public void Insert(Exercise exercise)
{
string Query = " INSERT INTO " + this.TableName +
" ( " +
" ExerciseName, " +
" CategoryID, " +
" UserID, " +
" ExerciseDateTime, " +
" ExerciseTime, " +
" ExerciseNote, " +
" ExerciseDescription " +
" ) " +
" VALUES (@2, @3, @4, @5, @6, @7, @8); " +
" SELECT CAST(scope_identity() AS int) ";
List <SqlParameter> parameters = new List <SqlParameter>();
parameters.Add(new SqlParameter {
ParameterName = "@1", Value = exercise.ID
});
parameters.Add(new SqlParameter {
ParameterName = "@2", Value = exercise.ExerciseName
});
parameters.Add(new SqlParameter {
ParameterName = "@3", Value = exercise.CategoryID
});
parameters.Add(new SqlParameter {
ParameterName = "@4", Value = exercise.UserID
});
parameters.Add(new SqlParameter {
ParameterName = "@5", Value = exercise.ExerciseDate
});
parameters.Add(new SqlParameter {
ParameterName = "@6", Value = exercise.ExerciseTime
});
parameters.Add(new SqlParameter {
ParameterName = "@7", Value = exercise.ExerciseNote
});
parameters.Add(new SqlParameter {
ParameterName = "@8", Value = exercise.ExerciseDescription
});
using (DBSqlHelper DB = new DBSqlHelper()) {
exercise.ID = Convert.ToInt32(DB.ExecuteScalar(Query, parameters));
}
}
public void Update(Exercise exercise)
{
string Query = " UPDATE " + this.TableName +
" SET " +
" ExerciseName = @2, " +
" CategoryID = @3, " +
" UserID = @4, " +
" ExerciseDateTime = @5 " +
" ExerciseTime = @6, " +
" ExerciseNote = @7 " +
" ExerciseDescription = @8, " +
" WHERE ID = @1 ";
List <SqlParameter> parameters = new List <SqlParameter>();
parameters.Add(new SqlParameter {
ParameterName = "@1", Value = exercise.ID
});
parameters.Add(new SqlParameter {
ParameterName = "@2", Value = exercise.ExerciseName
});
parameters.Add(new SqlParameter {
ParameterName = "@3", Value = exercise.CategoryID
});
parameters.Add(new SqlParameter {
ParameterName = "@4", Value = exercise.UserID
});
parameters.Add(new SqlParameter {
ParameterName = "@5", Value = exercise.ExerciseDate
});
parameters.Add(new SqlParameter {
ParameterName = "@6", Value = exercise.ExerciseTime
});
parameters.Add(new SqlParameter {
ParameterName = "@7", Value = exercise.ExerciseNote
});
parameters.Add(new SqlParameter {
ParameterName = "@8", Value = exercise.ExerciseDescription
});
using (DBSqlHelper DB = new DBSqlHelper()) {
DB.ExecuteNonQuery(Query, parameters);
}
}
public void Update(User user)
{
string Query = " UPDATE " + this.TableName +
" SET " +
" Username = @2, " +
" Password = @3 " +
" WHERE ID = @1 ";
List <SqlParameter> parameters = new List <SqlParameter>();
parameters.Add(new SqlParameter {
ParameterName = "@1", Value = user.ID
});
parameters.Add(new SqlParameter {
ParameterName = "@2", Value = user.Username
});
parameters.Add(new SqlParameter {
ParameterName = "@3", Value = user.Password
});
using (DBSqlHelper DB = new DBSqlHelper()) {
DB.ExecuteNonQuery(Query, parameters);
}
}
public void Update(Category category)
{
string Query = " UPDATE " + this.TableName +
" SET " +
" CategoryName = @2, " +
" UserID = @3 " +
" WHERE ID = @1 ";
List <SqlParameter> parameters = new List <SqlParameter>();
parameters.Add(new SqlParameter {
ParameterName = "@1", Value = category.ID
});
parameters.Add(new SqlParameter {
ParameterName = "@2", Value = category.CategoryName
});
parameters.Add(new SqlParameter {
ParameterName = "@3", Value = category.UserID
});
using (DBSqlHelper DB = new DBSqlHelper()) {
DB.ExecuteNonQuery(Query, parameters);
}
}