예제 #1
0
 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>());
 }
예제 #2
0
 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);
 }
예제 #3
0
 /* 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();
         }
     }
 }
예제 #4
0
 // 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);
 }
예제 #5
0
 // 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);
         }
     }
 }
예제 #6
0
 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>());
     }
 }
예제 #7
0
        /* 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>());
            }
        }