// ----------------------------------------------------------------------------------------
public static void DeleteCompSubType(int id)
{
// backup the record to the history table before it's deleted
//SaveHistoryRecord(id, "DELETED");
PetaPoco.Database db = new PetaPoco.Database("ElecLibConnString");
db.Delete<CompSubType>(id);
}
// ----------------------------------------------------------------------------------------
public static int GetCompSubTypeCount()
{
PetaPoco.Database db = new PetaPoco.Database("ElecLibConnString");
string sql = "SELECT COUNT(*) FROM CompSubType";
return db.ExecuteScalar<int>(sql);
}
// ----------------------------------------------------------------------------------------
public static IEnumerable<CompSubType> GetCompSubTypesByType(int compTypeID)
{
PetaPoco.Database db = new PetaPoco.Database("ElecLibConnString");
return db.Query<CompSubType>
("SELECT cst.CompSubTypeID, cst.CompTypeID, ct.CompTypeName, cst.CompSubTypeName, cst.CompSubTypeDesc "
+ "FROM CompType AS ct INNER JOIN CompSubType AS cst ON cst.CompTypeID = ct.CompTypeID "
+ "WHERE cst.CompTypeID = " + compTypeID.ToString() + " "
+ "ORDER BY ct.CompTypeName, cst.CompSubTypeName");
}
// ----------------------------------------------------------------------------------------
public static IEnumerable<CompSubType> GetCompSubTypes()
{
// return all the comp sub types
PetaPoco.Database db = new PetaPoco.Database("ElecLibConnString");
return db.Query<CompSubType>
("SELECT cst.CompSubTypeID, cst.CompTypeID, ct.CompTypeName, cst.CompSubTypeName, cst.CompSubTypeDesc "
+ "FROM CompType AS ct INNER JOIN CompSubType AS cst ON cst.CompTypeID = ct.CompTypeID "
+ "ORDER BY ct.CompTypeName, cst.CompSubTypeName");
}
// ----------------------------------------------------------------------------------------
public static CompSubType GetCompSubType(int id)
{
// return a single comp sub type based on the comp sub type ID
string sql = "SELECT cst.CompSubTypeID, cst.CompTypeID, ct.CompTypeName, cst.CompSubTypeName, cst.CompSubTypeDesc "
+ "FROM CompType AS ct INNER JOIN CompSubType AS cst ON cst.CompTypeID = ct.CompTypeID "
+ "WHERE cst.CompSubTypeID = " + id.ToString();
PetaPoco.Database db = new PetaPoco.Database("ElecLibConnString");
return db.Single<CompSubType>(sql);
}
// ----------------------------------------------------------------------------------------
public static CompType InsertCompType(CompType ct)
{
// add the user and timestamp
PetaPoco.Database db = new PetaPoco.Database("ElecLibConnString");
ct.EnteredBy = "ElecLibUser";
ct.EntryDate = DateTime.Now;
int newID = Convert.ToInt32(db.Insert(ct));
ct.Id = newID;
return ct;
}
// ----------------------------------------------------------------------------------------
public static void UpdateCompType(CompType ct)
{
// the incoming ct is the updated info
PetaPoco.Database db = new PetaPoco.Database("ElecLibConnString");
//SaveHistoryRecord(ct.CompTypeID, "UPDATED");
// now update the record with the new data
// touch the update person and time
ct.EntryDate = DateTime.Now;
ct.EnteredBy = "ElecLibUser";
db.Update(ct);
}
// ----------------------------------------------------------------------------------------
public static List<CompSubType> GetCompSubTypes(int startIndex, int pageSize)
{
// return a page of comp sub types
PetaPoco.Database db = new PetaPoco.Database("ElecLibConnString");
string sql = "SELECT cst.CompSubTypeID, cst.CompTypeID, ct.CompTypeName, cst.CompSubTypeName, cst.CompSubTypeDesc "
+ "FROM CompType AS ct INNER JOIN CompSubType AS cst ON cst.CompTypeID = ct.CompTypeID "
+ "ORDER BY ct.CompTypeName, cst.CompSubTypeName";
int skipNum;
if (startIndex == 0)
skipNum = 1;
else if (startIndex == 1)
skipNum = 1;
else
skipNum = startIndex - 1;
List<CompSubType> compSubTypes = db.SkipTake<CompSubType>(skipNum, pageSize, sql);
return compSubTypes;
}
// ----------------------------------------------------------------------------------------
public LookupRepository()
{
// ctor
db = new PetaPoco.Database("ElecLibConnString");
}
// ----------------------------------------------------------------------------------------
public static IEnumerable<CompType> GetCompTypes()
{
// return all the comp types
PetaPoco.Database db = new PetaPoco.Database("ElecLibConnString");
return db.Query<CompType>("SELECT * FROM CompType ORDER BY CompTypeName");
}
// ----------------------------------------------------------------------------------------
public static CompType GetCompType(int compTypeID)
{
PetaPoco.Database db = new PetaPoco.Database("ElecLibConnString");
return db.Single<CompType>
("SELECT CompTypeID, CompTypeName FROM CompType "
+ "WHERE CompTypeID = " + compTypeID.ToString());
}
// ----------------------------------------------------------------------------------------
public static CompType GetCompType(int id)
{
// return a single comp type based on the comp type ID
PetaPoco.Database db = new PetaPoco.Database("ElecLibConnString");
return db.Single<CompType>(id);
}
// ----------------------------------------------------------------------------------------
public static void DeleteCompType(int id)
{
PetaPoco.Database db = new PetaPoco.Database("ElecLibConnString");
db.Delete<CompType>(id);
}
