public static List <Tag> getTagList(long plc_id)
{
try
{
using (SQLiteConnection conn = SQLiteDBMS.getConnection())
{
conn.Open();
SQLiteCommand cmd = new SQLiteCommand(
"SELECT * FROM Tag WHERE plc_id =" + plc_id, conn);
List <Tag> list = new List <Tag>();
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
list.Add(new Tag(reader.GetInt64(0), reader.GetString(1),
reader.GetInt32(2), reader.GetString(3), reader.GetString(4),
reader.GetString(5), reader.GetInt64(6)
));
}
}
return(list);
}
}
catch (SQLiteException ex)
{
// table not exist
if (ex.ErrorCode == 1)
{
createTagTable();
}
}
return(new List <Tag>());
}
public static Scaling getScaling(long tag_id)
{
try
{
using (SQLiteConnection conn = SQLiteDBMS.getConnection())
{
conn.Open();
SQLiteCommand cmd = new SQLiteCommand(
"SELECT * FROM Scaling WHERE tag_id =" + tag_id, conn);
List <Tag> list = new List <Tag>();
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
return(new Scaling(reader.GetString(0), reader.GetDouble(1),
reader.GetDouble(2), reader.GetDouble(3), reader.GetDouble(4)));
}
}
}
}
catch (SQLiteException ex)
{
// table not exist
if (ex.ErrorCode == 1)
{
createScalingTable();
}
}
return(null);
}
/* method for bulk insert (not tested yet)
* using transaction to achieve this demand
* a transaction is an atomic sql operation
* operations in a transaction are zero-or-none.
* */
public static void insertTag(List <Tag> list, long plc_id)
{
using (SQLiteConnection conn = SQLiteDBMS.getConnection())
{
using (SQLiteTransaction transaction = conn.BeginTransaction())
{
using (SQLiteCommand cmd = conn.CreateCommand())
{
cmd.CommandText =
"INSERT INTO Tag (alias, addr, data_type, format, unit, plc_id) "
+ "values (@alias, @addr, @data_type, @format, @unit, @plc_id)";
cmd.Parameters.Add("@alias", DbType.String);
cmd.Parameters.Add("@addr", DbType.Int32);
cmd.Parameters.Add("@data_type", DbType.String);
cmd.Parameters.Add("@format", DbType.String);
cmd.Parameters.Add("@unit", DbType.String);
foreach (Tag tag in list)
{
insertTag(cmd, tag, plc_id);
}
}
transaction.Commit();
}
}
}
// check authentication from database
internal static GPLCAuthority Authenticate(string id, string pass)
{
try
{
using (SQLiteConnection conn = SQLiteDBMS.getConnection())
{
conn.Open();
SQLiteCommand cmd =
new SQLiteCommand("SELECT auth FROM User WHERE id=@id AND pass=@pass", conn);
cmd.Parameters.Add("@id", DbType.String).Value = id;
cmd.Parameters.Add("@pass", DbType.String).Value = CryptoUtil.encryptSHA1(pass);
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
String str = reader.GetString(0);
//str = CryptoUtil.Decrypt(str, id);
return((str.Equals("Administrator")) ? GPLCAuthority.Administrator : GPLCAuthority.Operator);
}
else
{
throw new WrongIdPassException();
}
}
}
}
catch (SQLiteException ex)
{
if (ex.ErrorCode == 1)
{
createSchema();
}
}
return(GPLCAuthority.Anonymous);
}
// get row ID of the last inserted record
private static int getLastInsertRowId()
{
using (SQLiteConnection conn = SQLiteDBMS.getConnection())
{
conn.Open();
SQLiteCommand cmd = new SQLiteCommand("select last_insert_rowid()", conn);
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
return((reader.Read()) ? reader.GetInt32(0) : -1);
}
}
}
public static List <PLC> getPLCList(long project_id)
{
try
{
using (SQLiteConnection conn = SQLiteDBMS.getConnection())
{
conn.Open();
SQLiteCommand cmd = new SQLiteCommand(
"SELECT * FROM PLC WHERE project_id=" + project_id, conn);
List <PLC> pList = new List <PLC>();
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
long id = reader.GetInt64(0);
PLC p = new PLC(id, reader.GetString(1),
reader.GetInt32(2), reader.GetString(3),
reader.GetInt32(4), reader.GetInt32(5), null);
pList.Add(p);
}
}
return(pList);
}
}
catch (SQLiteException ex)
{
// table not exist
if (ex.ErrorCode == 1)
{
createPLCTable();
}
return(new List <PLC>());
}
catch (Exception)
{
return(new List <PLC>());
}
}
/* methods that get list of model
* */
public static List <ProjectData> getProjectList()
{
try
{
List <ProjectData> pList = new List <ProjectData>();
using (SQLiteConnection conn = SQLiteDBMS.getConnection())
{
conn.Open();
SQLiteCommand cmd = new SQLiteCommand("SELECT * FROM Project", conn);
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
// get column values
pList.Add(
new ProjectData(reader.GetInt64(0), reader.GetString(1),
reader.GetString(2), reader.GetDouble(3), reader.GetDouble(4), null)
);
}
}
}
return(pList);
}
catch (SQLiteException ex)
{
// table not exist
if (ex.ErrorCode == 1)
{
createProjectTable();
}
return(new List <ProjectData>());
}
catch (Exception)
{
return(new List <ProjectData>());
}
}
