// execute update command, return number of modified records private static int executeUpdate(string sql) { using (SQLiteCommand cmd = new SQLiteCommand(sql)) { return(SQLiteDBMS.execUpdate(cmd)); } }
// create a new user internal static void newUser(string id, string pass, GPLCAuthority auth) { try { using (SQLiteCommand sql = new SQLiteCommand("INSERT INTO User values(@id, @pass, @auth)")) { sql.Parameters.Add("@id", DbType.String).Value = id; sql.Parameters.Add("@pass", DbType.String).Value = CryptoUtil.encryptSHA1(pass); sql.Parameters.Add("@auth", DbType.String).Value = auth.ToString(); SQLiteDBMS.execUpdate(sql); } } catch (SQLiteException ex) { switch (ex.ErrorCode) { case 1: createSchema(); newUser(id, pass, auth); break; case 19: default: break; } } }
public static int deleteScaling(long tag_id) { using (SQLiteCommand cmd = new SQLiteCommand( "delete FROM Scaling WHERE tag_id=@tag_id")) { cmd.Parameters.Add("@tag_id", DbType.Int64).Value = tag_id; return(SQLiteDBMS.execUpdate(cmd)); } }
public static int deleteTag(long id) { // delete a tag also delete its scale info deleteScaling(id); using (SQLiteCommand cmd = new SQLiteCommand( "delete FROM Tag WHERE id=@id")) { cmd.Parameters.Add("@id", DbType.Int64).Value = id; return(SQLiteDBMS.execUpdate(cmd)); } }
public static int deletePLC(long id) { // delete a plc also delete its tags deleteTags(id); using (SQLiteCommand cmd = new SQLiteCommand( "delete FROM PLC WHERE id=@id")) { cmd.Parameters.Add("@id", DbType.Int64).Value = id; return(SQLiteDBMS.execUpdate(cmd)); } }
// create user schema private static void createSchema() { try { using (SQLiteCommand sql = new SQLiteCommand("CREATE TABLE User (id varchar(20) PRIMARY KEY, pass text, auth text)")) { SQLiteDBMS.execUpdate(sql); } } catch (SQLiteException) { } }
private static int deletePLCs(long project_id) { // delete PLCs also delete their tags foreach (PLC plc in getPLCList(project_id)) { deleteTags(plc.id); } using (SQLiteCommand cmd = new SQLiteCommand( "delete FROM PLC WHERE project_id=@project_id")) { cmd.Parameters.Add("@project_id", DbType.Int64).Value = project_id; return(SQLiteDBMS.execUpdate(cmd)); } }
/* update methods for model * using parameters instead of directly string utility * can avoid malicious operation such as SQL injection. * */ public static int updateProject(long id, string name, string addr, double lat, double lng, long oid) { using (SQLiteCommand cmd = new SQLiteCommand( "UPDATE Project SET id=@id, name=@name, addr=@addr, lat=@lat, lng=@lng WHERE id=@oid")) { cmd.Parameters.Add("@id", DbType.Int64).Value = id; cmd.Parameters.Add("@name", DbType.String).Value = name; cmd.Parameters.Add("@addr", DbType.String).Value = addr; cmd.Parameters.Add("@lat", DbType.Double).Value = lat; cmd.Parameters.Add("@lng", DbType.Double).Value = lng; cmd.Parameters.Add("@oid", DbType.Int64).Value = oid; return(SQLiteDBMS.execUpdate(cmd)); } }
private static int deleteTags(long plc_id) { // delete tags also delete their scale info foreach (Tag tag in getTagList(plc_id)) { deleteScaling(tag.id); } // delete items belonging to the plc using (SQLiteCommand cmd = new SQLiteCommand( "delete FROM Tag WHERE plc_id=@plcid")) { cmd.Parameters.Add("@plcid", DbType.Int64).Value = plc_id; return(SQLiteDBMS.execUpdate(cmd)); } }
public static int updateScaling(string scale_type, double raw_hi, double raw_lo, double scale_hi, double scale_lo, long tag_id) { using (SQLiteCommand cmd = new SQLiteCommand( "UPDATE Scaling SET scale_type=@scale_type, raw_hi=@raw_hi, raw_lo=@raw_lo," + " scale_hi=@scale_hi, scale_lo=@scale_lo WHERE tag_id=@tag_id")) { cmd.Parameters.Add("@scale_type", DbType.String).Value = scale_type.ToString(); cmd.Parameters.Add("@raw_hi", DbType.Double).Value = raw_hi; cmd.Parameters.Add("@raw_lo", DbType.Double).Value = raw_lo; cmd.Parameters.Add("@scale_hi", DbType.Double).Value = scale_hi; cmd.Parameters.Add("@scale_lo", DbType.Double).Value = scale_lo; cmd.Parameters.Add("@tag_id", DbType.Int64).Value = tag_id; return(SQLiteDBMS.execUpdate(cmd)); } }
public static int updateTag(long id, string alias, int addr, DataType data_type, string format, string unit) { using (SQLiteCommand cmd = new SQLiteCommand( "UPDATE Tag SET alias=@alias, addr=@addr, data_type=@data_type, format=@format, unit=@unit" + " WHERE id=@id")) { cmd.Parameters.Add("@alias", DbType.String).Value = alias; cmd.Parameters.Add("@addr", DbType.Int32).Value = addr; cmd.Parameters.Add("@data_type", DbType.String).Value = data_type.ToString(); cmd.Parameters.Add("@format", DbType.String).Value = format; cmd.Parameters.Add("@unit", DbType.String).Value = unit; cmd.Parameters.Add("@id", DbType.Int64).Value = id; return(SQLiteDBMS.execUpdate(cmd)); } }
public static int updatePLC(long id, int netid, string ip, int port, string alias, int polling_rate) { using (SQLiteCommand cmd = new SQLiteCommand( "UPDATE PLC SET net_id=@net_id, net_ip=@net_ip, net_port=@net_port, alias=@alias," + " polling_rate=@polling_rate WHERE id=@id")) { cmd.Parameters.Add("@id", DbType.Int64).Value = id; cmd.Parameters.Add("@net_id", DbType.Int32).Value = netid; cmd.Parameters.Add("@net_ip", DbType.String).Value = ip; cmd.Parameters.Add("@net_port", DbType.Int32).Value = port; cmd.Parameters.Add("@alias", DbType.String).Value = alias; cmd.Parameters.Add("@polling_rate", DbType.Int32).Value = polling_rate; return(SQLiteDBMS.execUpdate(cmd)); } }