Inheritance: System.Data.Common.DbCommand, ICloneable
Esempio n. 1
2
        public async Task Load(SQLiteConnection connection)
        {
            using (var command = new SQLiteCommand("SELECT * FROM `Tracks`", connection))
            {
                var reader = await command.ExecuteReaderAsync();
                while (await reader.ReadAsync())
                {
                    PlayableBase track;
                    using (var xmlReader = reader.GetTextReader(8))
                        track = (PlayableBase) _serializer.Deserialize(xmlReader);

                    track.Title = reader.GetString(0);
                    track.Artist = _artistProvider.ArtistDictionary[reader.ReadGuid(1)];

                    var albumGuid = reader.ReadGuid(2);
                    if (albumGuid != Guid.Empty)
                        track.Album = _albumsProvider.Collection[albumGuid];
                    track.Guid = reader.ReadGuid(3);
                    track.LastTimePlayed = reader.GetDateTime(4);
                    track.MusicBrainzId = reader.GetValue(5)?.ToString();
                    track.Duration = XmlConvert.ToTimeSpan(reader.GetString(6));

                    var coverId = reader.ReadGuid(7);
                    if (coverId != Guid.Empty)
                        track.Cover = _imageProvider.Collection[coverId];

                    Collection.Add(track.Guid, track);
                    Tracks.Add(track);
                }
            }

            _connection = connection;
        }
Esempio n. 2
1
        public async Task<List<InventurItem>> GetDataAsync()
        {
            //using (var db = new InventurContext())
            //{
            //    return await (from i in db.InventurItems
            //                  where i.Exported == false
            //                  orderby i.ChangedAt descending
            //                  select i).ToListAsync();
            //}

            using (var command = new SQLiteCommand($"select * from {TABNAME} where Exported=0 orderby ChangedAt DESC", _dbTool.ConnectDb()))
            {
                using (var reader = await command.ExecuteReaderAsync())
                {
                    var items = new List<InventurItem>();
                    if (await reader.ReadAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            var i = new InventurItem { ID = Convert.ToInt32(reader["ID"]), CreatedAt = Convert.ToDateTime(reader["CreatedAt"]), ChangedAt = Convert.ToDateTime(reader["ChangedAt"]), EANCode = reader["EANCode"].ToString(), Amount = Convert.ToInt32(reader["Amount"]), Exported = Convert.ToBoolean(reader["Exported"]) };
                            items.Add(i);
                        }
                    }
                    return items;
                }
            }
        }
Esempio n. 3
1
        /// <summary>
        ///   Removes the old entries (entries that have been in the database more than the specified time) from the database.
        /// </summary>
        /// <param name="olderThan"> The number of days in the database after which the entry is considered old... </param>
        public static void CleanNewsOlderThan(int olderThan)
        {
            try
            {
                using (SQLiteConnection sqLiteConnection = new SQLiteConnection(ConnectionString))
                {
                    sqLiteConnection.Open();

                    DateTime olderDate = DateTime.Now;
                    TimeSpan daySpan = new TimeSpan(olderThan, 0, 0, 0);
                    olderDate = olderDate.Subtract(daySpan);

                    SQLiteCommand sqLiteCommand = new SQLiteCommand(sqLiteConnection)
                                                      {
                                                          CommandText = "DELETE " +
                                                                        "FROM NEWS_STORAGE " +
                                                                        "WHERE NEWSITEM_AQUISITION_DATE <= ?"
                                                      };
                    sqLiteCommand.Parameters.AddWithValue(null, olderDate);
                    sqLiteCommand.ExecuteNonQuery();

                    sqLiteConnection.Close();
                }
            }
            catch (Exception ex)
            {
                ErrorMessageBox.Show(ex.Message, ex.ToString());
                Logger.ErrorLogger("error.txt", ex.ToString());
            }
        }
Esempio n. 4
0
        public static void addAccount(string name, string passwd, string url, string provider, string blogname)
        {
            string conn_str = "Data Source=" + path + pwd_str;

            SQLiteConnection conn = new SQLiteConnection(conn_str);

            conn.Open();

            SQLiteCommand cmd = new SQLiteCommand();

            String sql = "Insert INTO BlogAccount(BlogUrl,BlogName,Provider,AccountName,Password) Values(@url,@blogname,@provider,@name,@password)";

            cmd.CommandText = sql;
            cmd.Connection = conn;

            cmd.Parameters.Add(new SQLiteParameter("url", url));
            cmd.Parameters.Add(new SQLiteParameter("blogname", blogname));
            cmd.Parameters.Add(new SQLiteParameter("provider", provider));
            cmd.Parameters.Add(new SQLiteParameter("name", name));
            cmd.Parameters.Add(new SQLiteParameter("password", passwd));

            cmd.ExecuteNonQuery();

            conn.Close();
        }
        private void button3_Click(object sender, EventArgs e)
        {
            ///enters selected row into a string variable
            string country = dataGridView2.CurrentCell.Value.ToString();
            ///gets team table from database
            string ViewTable = "SELECT * FROM 'Athlete Information' WHERE \"TeamName\" = '" + country + "';";

            using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
            {
                using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
                {
                    con.Open();                            ///opens DB

                    if (con.State == ConnectionState.Open) /// if connection.Open was successful
                    {
                        ///this sends sql commands to database
                        DataSet ds = new DataSet();
                        var     da = new SQLiteDataAdapter(ViewTable, con);
                        da.Fill(ds);
                        dataGridView1.DataSource = ds.Tables[0].DefaultView;
                    }
                    else
                    {
                        MessageBox.Show("Connection failed.");
                    }
                }
                con.Close();
            }
        }
        public string AddComment(NewsFeedModel comment)
        {
            string status = "";
            string query;
            DataTable dt1 = new DataTable();
            SQLiteCommand cmd;

            try
            {
                dbConn.Open();

                query = "insert into news_feed values (" + comment.UserId + ", '" + comment.FirstName + "', '" + comment.LastName + "', '" + comment.Comment + "', "+ comment.Month +
                        ", " + comment.Day + ", " + comment.Year + ", " + comment.Hour + ", " + comment.Minute + ")";

                cmd = new SQLiteCommand(query, dbConn);
                cmd.ExecuteNonQuery();

                status = "Comment Added";

                dbConn.Close();
            }
            catch (SQLiteException ex)
            {
                Console.Write(ex.ToString());
                status = ex.ToString();
                dbConn.Close();
            }

            return status;
        }
    private static int InsertNewBook(
        string filePath,
        string title,
        string author,
        DateTime publicationDate,
        string isbn)
    {
        SQLiteConnection connection = GetConnection(filePath);

        connection.Open();
        using (connection)
        {
            SQLiteCommand insertBookCommand = new SQLiteCommand(
                @"INSERT INTO Books (BookTitle, BookAuthor, PublicationDate, ISBN)
                  VALUES (@bookTitle, @bookAuthor, @publicationDate, @isbn)", connection);

            insertBookCommand.Parameters.AddWithValue("@bookTitle", title);
            insertBookCommand.Parameters.AddWithValue("@bookAuthor", author);
            insertBookCommand.Parameters.AddWithValue("@publicationDate", publicationDate);
            insertBookCommand.Parameters.AddWithValue("@isbn", isbn);

            int rowsAffected = insertBookCommand.ExecuteNonQuery();
            return rowsAffected;
        }
    }
Esempio n. 8
0
 private void DeleteCardBase()
 {
     if (dataGridView1.SelectedRows.Count > 0)
     {
         id = Int32.Parse(dataGridView1.SelectedRows[0].Cells[0].Value.ToString());
         if (dataGridView1.RowCount != 0) index = dataGridView1.SelectedRows[0].Index;
         DialogResult result = MessageBox.Show("Czy na pewno chcesz usunąć bazę karty numer " + id + "? \n\nOperacji nie można cofnąć.", "Ważne", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
         if (result == DialogResult.Yes)
             using (SQLiteConnection conn = new SQLiteConnection(connString))
             {
                 conn.Open();
                 SQLiteCommand command = new SQLiteCommand(conn);
                 command.CommandText = "DELETE FROM [CardBase] WHERE id = @id";
                 command.Parameters.Add(new SQLiteParameter("@id", id));
                 command.ExecuteNonQuery();
                 conn.Close();
                 Odswierz();
                 if (dataGridView1.RowCount != 0)
                 {
                     if (index == dataGridView1.RowCount) dataGridView1.CurrentCell = dataGridView1.Rows[index - 1].Cells[0];
                     else dataGridView1.CurrentCell = dataGridView1.Rows[index].Cells[0];
                 }
             }
     }
 }
Esempio n. 9
0
        protected void Page_Load(object sender, EventArgs e)
        {

            LabelError.Text = "";

            try
            {

                string sql = "SELECT email FROM users WHERE RoleId = 'Admin'";
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.Connection = new SQLiteConnection(Code.DAL.ConnectionString);
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = sql;

                DataTable dt = Code.DAL.ExecuteCmdTable(cmd);

                if (dt.Rows.Count > 0)
                {
                    PanelAlreadySetup.Visible = true;
                    PanelNotSetup.Visible = false;
                }
                else
                {
                    PanelAlreadySetup.Visible = false;
                    PanelNotSetup.Visible = true;
                }


            }
            catch (Exception ex)
            {
                LabelError.Text = ex.Message;
            }
        }
Esempio n. 10
0
 private int findNOCert()
 {
     try
     {
         using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=gestionalePalestra.db"))
         {
             using (System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(conn))
             {
                 conn.Open();
                 string command = "SELECT count(*) FROM Certificato WHERE Presente='NO';";
                 cmd.CommandText = command;
                 using (System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader())
                 {
                     if (reader.Read())
                     {
                         return(reader.GetInt32(0)); //ritorna la prima colonna, quella avente il count
                     }
                 }
                 conn.Close();
             }
         }
         return(0);
     }
     catch
     {
         return(-1);
     }
 }
Esempio n. 11
0
 /** Void function adds female to database for the event
      @param string for score entry
      @param string for particular event score derived from
      @param string for first name of athlete
      @param string for last name of athlete
 */
 public void EnterScore(double entry, string selectedEvent, string Fname, string Lname)
 {
     //!< Creates variable for connection string
     using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
     {
         using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
         {
             con.Open(); //!< Opens DB
                         //!< Checks connection and fills grid view with Race table
             if (con.State == ConnectionState.Open)
             {
                 //!< Adds entry from event to the specific event
                 string sql = String.Format("UPDATE \"" + selectedEvent + "\" SET Score = " + entry +
                 " WHERE \"Athlete First Name\" = '" + Fname + "' AND \"Athlete Last Name\" = '" + Lname + "';");
                 DataSet ds = new DataSet();
                 var da = new SQLiteDataAdapter(sql, con);
                 da.Fill(ds);
             }
             else
             {
                 MessageBox.Show("Connection failed.");
             }
         }
     }
     MessageBox.Show("Score has been entered into " + Fname + " " + Lname + "'s score");
 }
Esempio n. 12
0
    public void deleteAthlete(string Fname, string Lname)
    {
        //!< Creates variable for connection string
        using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
        {
            using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
            {
                con.Open(); //!< Opens DB
                            //!< Checks connection and fills grid view with Race table
                if (con.State == ConnectionState.Open)
                {
                    //!< Adds entry to athlete information
                    string sql = String.Format("DELETE FROM 'Athlete Information' WHERE AthleteFirstName = '" + Fname +
                        "' AND AthleteLastName = '" + Lname + "';");
                    DataSet ds = new DataSet();
                    var da = new SQLiteDataAdapter(sql, con);
                    da.Fill(ds);
                }
                else
                {
                    MessageBox.Show("Connection failed.");
                }
            }

            MessageBox.Show("Entry has been deleted from database!");
            con.Close();
        }
    }
Esempio n. 13
0
    /** Void function adds male to database for the event
        @param string for athlete's team name
        @param string for first name of athlete
        @param string for last name of athlete
    */
    public void registerAthlete(string Fname, string Lname, string Gender, string team)
    {
        //!< Creates variable for connection string
        using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
        {
            using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
            {
                con.Open(); //!< Opens DB
                            //!< Checks connection and fills grid view with Race table
                if (con.State == ConnectionState.Open)
                {
                    //!< Adds entry to athlete information
                    string sql = String.Format("INSERT INTO 'Athlete Information' ('TeamName' , 'AthleteFirstName', 'AthleteLastName', 'Gender') " +
                    "VALUES ('" + team + "', '" + Fname + "', '" + Lname + "', '" + Gender + "');");
                    DataSet ds = new DataSet();
                    var da = new SQLiteDataAdapter(sql, con);
                    da.Fill(ds);

                }
                else
                {
                    MessageBox.Show("Connection failed.");
                }
            }
            MessageBox.Show("Entry has been entered into Database!");
            con.Close();
        }
    }
Esempio n. 14
0
    public void DeleteTeam(string TeamName)
    {
        //!< Impeded sql statement to delete value from database
        string DeleteDB = "DELETE FROM 'team information' WHERE TeamName = '" + TeamName + "';";
        using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
        {
            using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
            {
                con.Open(); //!< Opens DB

                if (con.State == ConnectionState.Open) //!< If connection, open was successful
                {
                    //!< This sends sql commands to database
                    DataSet ds = new DataSet();
                    var da = new SQLiteDataAdapter(DeleteDB, con);
                    da.Fill(ds);
                }
                else
                {
                    MessageBox.Show("Connection failed.");
                }

            }
            con.Close();
            MessageBox.Show(TeamName + " has been removed from database!");
        }
    }
Esempio n. 15
0
        static void Main(string[] args)
        {
            string createQuery = @"CREATE TABLE IF NOT EXISTS
                                [Mytable](
                                [ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                                [NAME] NVARCHAR(2048) NOT NULL,
                                [GENDER] NVARCHAR(2048) NOT NULL)";

            System.Data.SQLite.SQLiteConnection.CreateFile("simple.db3");
            using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("Data Source=database.simple.db3"))
            {
                using (System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(conn))
                {
                    conn.Open();
                    cmd.CommandText = createQuery;

                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "INSERT INTO mytable(NAME,GENDER) values('alex','male')";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "INSERT INTO mytable(NAME,GENDER) values('diane','female')";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "select * from Mytable";
                    using (System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Console.WriteLine(reader["NAME"] + ":" + reader["GENDER"]);
                        }
                        conn.Close();
                    }
                }
            }
            Console.ReadLine();
        }
Esempio n. 16
0
        private void TeamForm_Load(object sender, EventArgs e)
        {
            string ViewTable = "SELECT * FROM 'Team Information'";//gets team table from database

            using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
            {
                using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
                {
                    con.Open();                            //opens DB

                    if (con.State == ConnectionState.Open) // if connection.Open was successful
                    {
                        //this sends sql commands to database
                        DataSet ds = new DataSet();
                        var     da = new SQLiteDataAdapter(ViewTable, con);
                        da.Fill(ds);
                        dataGridView1.DataSource = ds.Tables[0].DefaultView;
                    }
                    else
                    {
                        MessageBox.Show("Connection failed.");
                    }
                }
                con.Close();
            }
        }
Esempio n. 17
0
        private void button1_Click(object sender, EventArgs e)
        {
            //object decleration
            Team AddTeam = new Team();

            AddTeam.countryName = textBox1.Text;//gets team from text box
            //calls member function that adds team name to DB
            AddTeam.AddTeam(AddTeam.countryName);

            //this updates the table with added entry
            string ViewTable = "SELECT * FROM 'Team Information'";//gets team table from database

            using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
            {
                using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
                {
                    con.Open();                            //opens DB

                    if (con.State == ConnectionState.Open) // if connection.Open was successful
                    {
                        //this sends sql commands to database
                        DataSet ds = new DataSet();
                        var     da = new SQLiteDataAdapter(ViewTable, con);
                        da.Fill(ds);
                        dataGridView1.DataSource = ds.Tables[0].DefaultView;
                    }
                    else
                    {
                        MessageBox.Show("Connection failed.");
                    }
                }
                con.Close();
            }
        }
 /// <summary>
 /// Сохраняет изменения в таблице
 /// </summary>
 /// <param name="question">вопрос</param>
 public void SaveOrUpdate(SQLiteConnection conn, QuestionResult question)
 {
     if (question.ID == 0)
     {
         int ID = indexService.NextID(TABLE_NAME);
         using (SQLiteCommand insertSQL = new SQLiteCommand("insert into QUESTION_RESULT (ID, QuestionTitle, TEST_RESULT_ID) values (@ID, @QuestionTitle, @TEST_RESULT_ID)", conn))
         {
             insertSQL.Parameters.AddWithValue("@QuestionTitle", question.QuestionTitle);
             insertSQL.Parameters.AddWithValue("@TEST_RESULT_ID", question.testResult.ID);
             insertSQL.Parameters.AddWithValue("@ID", ID);
             insertSQL.ExecuteNonQuery();
             question.ID = ID;
         }
         
     }
     else
     {
         using (SQLiteCommand insertSQL = new SQLiteCommand("UPDATE QUESTION_RESULT SET QuestionTitle = @QuestionTitle, TEST_RESULT_ID = @TEST_RESULT_ID WHERE ID = @ID", conn))
         {
             insertSQL.Parameters.AddWithValue("@QuestionTitle", question.QuestionTitle);
             insertSQL.Parameters.AddWithValue("@TEST_RESULT_ID", question.testResult.ID);
             insertSQL.Parameters.AddWithValue("@ID", question.ID);
             insertSQL.ExecuteNonQuery();
         }
     }
 }
 public object Convert(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture)
 {
     using (SQLiteConnection conn = new SQLiteConnection("StaticConfig.DataSource"))
     {
         using (SQLiteCommand cmd = new SQLiteCommand())
         {
             cmd.Connection = conn;
             conn.Open();
             SQLiteHelper sh = new SQLiteHelper(cmd);
             int countCurrent = 0;
             string sqlCommandCurrent = String.Format("select count(*) from QuestionInfo where Q_Num={0};", value);//判断当前题号是否存在
             try
             {
                 Int32.Parse((string)value);
                 countCurrent = sh.ExecuteScalar<int>(sqlCommandCurrent);
                 conn.Close();
                 if (countCurrent > 0)
                 {
                     return "更新";
                 }
                 else
                 {
                     return "保存";
                 }
             }
             catch (Exception)
             {
                 return "保存";
             }
         }
     }
 }
Esempio n. 20
0
    /** Function to add a team to be officially registered
        @param string for the Team's name (which is country name)
    */
    public void AddTeam(string TeamName)
    {
        //!< Impeded sql statement to insert value into database
        string InsertDB = "INSERT INTO 'Team Information' ('TeamName') VALUES('" + TeamName + "');";
        using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
        {
            using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
            {
                con.Open(); //!< Opens DB

                if (con.State == ConnectionState.Open) //!< If connection, open was successful
                {
                    //!< This sends sql commands to database
                    DataSet ds = new DataSet();
                    var da = new SQLiteDataAdapter(InsertDB, con);
                    da.Fill(ds);
                }
                else
                {
                    MessageBox.Show("Connection failed.");
                }

            }
            con.Close();
            MessageBox.Show(TeamName + " has been added to database!");
        }
    }
        public SQLiteCommand CreateAddProductCommand(SQLiteConnection conn, SQLiteTransaction transaction)
        {
            var cmd = new SQLiteCommand(_sql, conn, transaction);
            CreateParameters(cmd);

            return cmd;
        }
 //Yollanan barkod numarası database de kayıtlı mı kontrol et.
 private string[] BarcodeControl(string barkod)
 {
     string[] barkodDizi = new string[4];
     try
     {
         this.baglanti.Open();
         this.komut = new SQLiteCommand("SELECT * FROM ilaclar WHERE barkod='" + barkod + "';", this.baglanti); //Veritabanında böyle bir barkod kayıtlı mı?
         SQLiteDataReader okunan = komut.ExecuteReader();
         while (okunan.Read())
         {
             barkodDizi[0] = okunan[0].ToString();
             barkodDizi[1] = okunan[1].ToString();
             barkodDizi[2] = okunan[2].ToString();
             barkodDizi[3] = okunan[3].ToString();
             //MessageBox.Show("barkod: " + okunan[0] + "\nilac: " + okunan[1]);
         }
         okunan.Close();
         komut.Dispose();
         baglanti.Close();
     }
     catch (Exception ex)
     {
         MessageBox.Show(ex.ToString());
     }
     return(barkodDizi);
 }
Esempio n. 23
0
        public static void crear()
        {
            SQLiteConnection.CreateFile("db/interna.wpdb");
            ObjConnection.Open();

            string sql1 = "create table cliente " +
                         "(id INTEGER PRIMARY KEY AUTOINCREMENT," +
                         "nombre VARCHAR(20)," +
                         "direccion VARCHAR(20)," +
                         "telefono VARCHAR(20)" +
                         ")";
            SQLiteCommand command1 = new SQLiteCommand(sql1, ObjConnection);
            command1.ExecuteNonQuery();

            string sql = "create table equipo " +
                         "(id INTEGER PRIMARY KEY AUTOINCREMENT," +
                         "marca VARCHAR(20)," +
                         "modelo VARCHAR(20)," +
                         "color VARCHAR(20)," +
                         "foto VARCHAR(20)," +
                         "fechaIng VARCHAR(20)," +
                         "fechaEnt VARCHAR(20)," +
                         "pagado INTEGER DEFAULT 0," +
                         "arreglado INTEGER DEFAULT 0," +
                         "entregado INTEGER DEFAULT 0," +
                         "precio FLOAT," +
                         "obser VARCHAR(40),"+
                         "id_cliente INTEGER," +
                         "FOREIGN KEY(id_cliente) REFERENCES cliente(id)" +
                         ")";
            SQLiteCommand command = new SQLiteCommand(sql, ObjConnection);
            command.ExecuteNonQuery();

            ObjConnection.Close();
        }
Esempio n. 24
0
        public static void GetItemsFromDatabase()
        {
            using (
                System.Data.SQLite.SQLiteConnection con =
                    new System.Data.SQLite.SQLiteConnection("data source=" + inputDir + ".db3"))
            {
                con.Open();

                using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
                {
                    com.CommandText = "SELECT Spectrum.ID,peptide,charge,path FROM Spectrum, Files Where Spectrum.codex = Files.codex";
                    using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            //store id and peptide into an object holding those values
                            //holderForPairs.Add(reader["id"].ToString() + "\t" + reader["peptide"].ToString());
                            inputs.Add(new InputListItems(reader["ID"].ToString(), reader["peptide"].ToString(), reader["charge"].ToString(), reader["path"].ToString()));
                        }
                    }
                }
                con.Close(); // Close the connection to the database
            }
            new MakeCombinations(inputs);
        }
        private void button2_Click(object sender, EventArgs e)
        {
            //stores name of athlete seleceted from DB
            string DeleteFName = dataGridView1.SelectedCells[1].Value.ToString();
            string DeleteLName = dataGridView1.SelectedCells[2].Value.ToString();
            //object created to delete entry
            Registration DelAthlete = new Registration();

            //member function deletes athlete from registration
            DelAthlete.deleteAthlete(DeleteFName, DeleteLName);
            //this will call a refreshed table
            string ViewTable = "SELECT * FROM 'Athlete Information'";//gets team table from database

            using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
            {
                using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
                {
                    con.Open();                            //opens DB

                    if (con.State == ConnectionState.Open) // if connection.Open was successful
                    {
                        //this sends sql commands to database
                        DataSet ds = new DataSet();
                        var     da = new SQLiteDataAdapter(ViewTable, con);
                        da.Fill(ds);
                        dataGridView1.DataSource = ds.Tables[0].DefaultView;
                    }
                    else
                    {
                        MessageBox.Show("Connection failed.");
                    }
                }
                con.Close();
            }
        }
Esempio n. 26
0
        public void CreateDatabase()
        {
            using (SQLiteConnection conn = new SQLiteConnection(DBConfig.DataSource))
            {
                SQLiteConnection.CreateFile(DBConfig.DatabaseFile);
                //System.Data.SQLite.SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
                //connstr.DataSource = DBConfig.DataSource;
                ////connstr.Password = "******";//设置密码,SQLite ADO.NET实现了数据库密码保护
                //conn.ConnectionString = connstr.ToString();
                conn.Open();
                //创建表
                System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
                cmd.Connection = conn;
                System.Data.SQLite.SQLiteHelper help = new SQLiteHelper(cmd);
                SQLiteTable table = new SQLiteTable("X");
                table.Columns.Add(new SQLiteColumn("ID", ColType.Integer, true, true, true, null));
                table.Columns.Add(new SQLiteColumn("Title", ColType.Text));
                table.Columns.Add(new SQLiteColumn("ReferURL", ColType.Text));
                table.Columns.Add(new SQLiteColumn("CoverURL", ColType.Text));
                table.Columns.Add(new SQLiteColumn("CoverURL2", ColType.Text));
                table.Columns.Add(new SQLiteColumn("CoverFile", ColType.Text));
                table.Columns.Add(new SQLiteColumn("CoverFile2", ColType.Text));
                table.Columns.Add(new SQLiteColumn("IsMovie", ColType.Boolean));
                table.Columns.Add(new SQLiteColumn("LocalPath", ColType.Text));
                table.Columns.Add(new SQLiteColumn("DatePublish", ColType.Text));
                table.Columns.Add(new SQLiteColumn("Tag", ColType.Text));

                help.CreateTable(table);

                conn.Close();
            }
        }
Esempio n. 27
0
        public Block_Edit(int id_)
        {
            InitializeComponent();
            id = id_;

            using (SQLiteConnection conn = new SQLiteConnection(connString))
            {
                conn.Open();
                SQLiteCommand command = new SQLiteCommand(conn);
                command.CommandText = "SELECT * FROM Block WHERE id=@id";
                command.Parameters.Add(new SQLiteParameter("@id", id));
                using (command)
                {
                    using (SQLiteDataReader rdr = command.ExecuteReader())
                    {
                        while (rdr.Read())
                        {

                            labelName.Text = rdr.GetValue(1).ToString();
                            txtImieNazw.Text = rdr.GetValue(1).ToString();

                        }
                    }
                }
                conn.Close();
            }
        }
Esempio n. 28
0
        private void button5_Click(object sender, EventArgs e)
        {
            if (textBox13.Text != "" && textBox14.Text != "" && textBox15.Text != "" && textBox16.Text != "")
            {
                string appPath = Path.GetDirectoryName(Application.ExecutablePath);
                System.Data.SQLite.SQLiteConnection sqlConnection1 =
                    new System.Data.SQLite.SQLiteConnection(@"Data Source=" + appPath + @"\DBPInc.s3db ;Version=3;");

                System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
                cmd.CommandType = System.Data.CommandType.Text;

                //comando sql para insercion
                cmd.CommandText = "INSERT INTO Proveedor Values ('" + textBox13.Text + "', '" + textBox14.Text + "', '" + textBox15.Text + "', '" + textBox16.Text + "')";

                cmd.Connection = sqlConnection1;

                sqlConnection1.Open();
                cmd.ExecuteNonQuery();

                sqlConnection1.Close();

                textBox13.Text = "";
                textBox14.Text = "";
                textBox15.Text = "";
                textBox16.Text = "";
            }
            else
            {
                MessageBox.Show("Faltaron campos por llenar");
            }
        }
Esempio n. 29
0
        public List<AttachmentDetail> getWitAttachments()
        {
            List<AttachmentDetail> attachments;
            try
            {
                sql_con = new SQLiteConnection(Common.localDatabasePath, true);
                sql_cmd = new SQLiteCommand("select * from wit_attachments", sql_con);

                sql_con.Open();
                SQLiteDataReader reader = sql_cmd.ExecuteReader();

                attachments = new List<AttachmentDetail>();
                while (reader.Read())
                {
                    AttachmentDetail attachment = new AttachmentDetail();
                    attachment.fileAssociationId = StringUtils.ConvertFromDBVal<string>(reader["file_association_id"]);
                    attachment.fileName = StringUtils.ConvertFromDBVal<string>(reader["file_name"]);
                    attachment.witId = StringUtils.ConvertFromDBVal<string>(reader["wit_id"]);
                    attachment.fileMimeType = StringUtils.ConvertFromDBVal<string>(reader["file_mime_type"]);
                    attachment.fileAssociationId = StringUtils.ConvertFromDBVal<string>(reader["file_association_id"]);
                    attachment.seqNumber = StringUtils.ConvertFromDBVal<string>(reader["seq_number"]);
                    attachment.extention = StringUtils.ConvertFromDBVal<string>(reader["extention"]);
                   
                    attachments.Add(attachment);
                }

            }
            catch (SQLiteException e) { throw e; }
            finally { sql_con.Close(); }

            return attachments;

        }
Esempio n. 30
0
        public void CreateLocalDatabase()
        {
            try
            {
                System.Data.SQLite.SQLiteConnection.CreateFile(@"Databases/EmployeesDB.db");
                Console.WriteLine("Succesfully Created Local Database");
                Console.WriteLine("Creating tables...");

                string EmployeesTableQuery = @"CREATE TABLE [Employees] (
                                          [ID]    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
	                                      [FullName]  TEXT NOT NULL,
                                          [BirthDay]  TEXT NOT NULL,
	                                      [Qualification] TEXT,
	                                      [FirstDay]  TEXT NOT NULL                            
                                          )";


                using (connection = new System.Data.SQLite.SQLiteConnection(@"data source=Databases/EmployeesDB.db"))
                {
                    System.Data.SQLite.SQLiteCommand command = new System.Data.SQLite.SQLiteCommand(connection);
                    connection.Open();
                    command.CommandText = EmployeesTableQuery;
                    command.ExecuteNonQuery();

                    connection.Close();
                }
            }
            catch (Exception exception)
            {
                Console.WriteLine(exception);
            }
        }
Esempio n. 31
0
        public static DataSet ExecuteDataSet(string SqlRequest, SQLiteConnection Connection)
        {
            DataSet dataSet = new DataSet();
            dataSet.Reset();

            SQLiteCommand cmd = new SQLiteCommand(SqlRequest, Connection);
            try
            {
                Connection.Open();
                SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(cmd);
                dataAdapter.Fill(dataSet);
            }
            catch (SQLiteException ex)
            {
                Log.Write(ex);
                //Debug.WriteLine(ex.Message);
                throw; // пересылаем исключение на более высокий уровень
            }
            finally
            {
                Connection.Dispose();
            }

            return dataSet;
        }
Esempio n. 32
0
        private void button2_Click(object sender, EventArgs e)
        {
            try
            {
                folio       = Int32.Parse(dataGridView1.SelectedRows[0].Cells[0].Value.ToString());
                nombre      = dataGridView1.SelectedRows[0].Cells[1].Value.ToString();
                sexo        = dataGridView1.SelectedRows[0].Cells[2].Value.ToString();
                edad        = Int32.Parse(dataGridView1.SelectedRows[0].Cells[3].Value.ToString());
                estadocivil = dataGridView1.SelectedRows[0].Cells[5].Value.ToString();


                string appPath = Path.GetDirectoryName(Application.ExecutablePath);
                System.Data.SQLite.SQLiteConnection sqlConnection1 =
                    new System.Data.SQLite.SQLiteConnection(@"Data Source=" + appPath + @"\EXCL.s3db ;Version=3;");

                System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
                cmd.CommandType = System.Data.CommandType.Text;
                //comando sql para insercion
                cmd.CommandText = "DELETE FROM Expediente WHERE [Folio] = " + folio + " AND [Nombre] = '" + nombre + "' AND [Sexo] = '" + sexo + "' AND [Edad] = " + edad + " AND [Estadocivil] = '" + estadocivil + "'";

                cmd.Connection = sqlConnection1;

                sqlConnection1.Open();
                cmd.ExecuteNonQuery();

                sqlConnection1.Close();
                MessageBox.Show("Expediente eliminado exitosamente");
            }
            catch
            {
                MessageBox.Show("No se pueden borrar datos", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
Esempio n. 33
0
        /// <summary>
        /// Constructor creates a database if it doesn't exist and creates the database's tables
        /// </summary>
        public DataBaseRepository()
        {
            // "using " keywords ensures the object is properly disposed.
            using (var conn = new SQLiteConnection(Connectionstring))
            {
                try
                {
                    if (!File.Exists(Startup.dbSource))
                    {
                        SQLiteConnection.CreateFile(Startup.dbSource);
                    }

                    conn.Open();
                    //Create a Table
                    string query = $"create table IF NOT EXISTS {TableName} (Id INTEGER PRIMARY KEY, Date VARCHAR NOT NULL DEFAULT CURRENT_DATE, Title nvarchar(255) not null, Content nvarchar(1000) Not NULL) ";
                    SQLiteCommand command = new SQLiteCommand(query, conn);
                    command.ExecuteNonQuery();
                }
                //TODO: Handle try catch better cath a more specific error type. 
                catch (SQLiteException ex )
                {
                    Console.WriteLine(ex.ToString());
                }
                conn.Close();
            }
        }
Esempio n. 34
0
        public static DataTable GetDataTable(string sql)
        {
            DataTable dt = new DataTable();

            try

            {

                SQLiteConnection cnn = new SQLiteConnection(@"Data Source=C:\Projects\showdownsharp\db\showdown.db");

                cnn.Open();

                SQLiteCommand mycommand = new SQLiteCommand(cnn);

                mycommand.CommandText = sql;

                SQLiteDataReader reader = mycommand.ExecuteReader();

                dt.Load(reader);

                reader.Close();

                cnn.Close();

            } catch {

            // Catching exceptions is for communists

            }

            return dt;
        }
Esempio n. 35
0
 private DoorInfo Retrieve(string DoorID)
 {
     var ret = new DoorInfo();
     var cs = ConfigurationManager.ConnectionStrings["DoorSource"].ConnectionString;
     var conn = new SQLiteConnection(cs);
     conn.Open();
     var cmd = new SQLiteCommand(conn);
     cmd.CommandText = "SELECT DoorID, Location, Description, EventID FROM Doors WHERE DoorID = @DoorID LIMIT 1";
     cmd.Parameters.Add(new SQLiteParameter("@DoorID", DoorID));
     SQLiteDataReader res = null;
     try
     {
         res = cmd.ExecuteReader();
         if (res.HasRows && res.Read())
         {
             ret.DoorID = DoorID;
             ret.Location = res.GetString(1);
             ret.Description = res.GetString(2);
             ret.EventID = res.GetInt64(3);
         }
         return ret;
     }
     catch(Exception ex)
     {
         throw;
     }
     finally
     {
         if (null != res && !res.IsClosed)
             res.Close();
     }
 }
        internal static void PrepareCommand(SQLiteCommand command, SQLiteConnection connection, SQLiteTransaction transaction,
                                           CommandType commandType, string commandText, SQLiteParameter[] commandParameters,
                                           out bool mustCloseConnection)
        {
            if (command == null) throw new ArgumentNullException("command");
            if (string.IsNullOrEmpty(commandText)) throw new ArgumentNullException("commandText");

            if (connection.State == ConnectionState.Open)
                mustCloseConnection = false;
            else
            {
                mustCloseConnection = true;
                connection.Open();
            }

            command.Connection = connection;
            command.CommandText = commandText;

            if (transaction != null)
            {
                if (transaction.Connection == null)
                    throw new ArgumentException(
                        "The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
                command.Transaction = transaction;
            }

            command.CommandType = commandType;

            if (commandParameters != null)
                AttachParameters(command, commandParameters);
            return;
        }
        public static List <CoinBalanceDTO> GetCoinBalance(int idCashier, SQLiteConnection dbConnection)
        {
            List <CoinBalanceDTO> coinBalances = new List <CoinBalanceDTO>();

            using (var dbCommand = new System.Data.SQLite.SQLiteCommand(dbConnection))
            {
                dbCommand.CommandText = @"
                    SELECT ID_COIN_BALANCE, COIN_VALUE, QUANTITY
                        FROM COIN_BALANCE
                        WHERE ID_CASHIER = @ID_CASHIER
                        ORDER BY COIN_VALUE DESC";
                dbCommand.Parameters.AddWithValue("@ID_CASHIER", idCashier);

                using (var reader = dbCommand.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        coinBalances.Add(new CoinBalanceDTO()
                        {
                            idCoinBalance = Convert.ToInt32(reader["ID_COIN_BALANCE"]),
                            coinValue     = Convert.ToInt32(reader["COIN_VALUE"]),
                            quantity      = Convert.ToInt32(reader["QUANTITY"])
                        });
                    }
                }
            }

            return(coinBalances);
        }
Esempio n. 38
0
 private int findNextToPay()
 {
     try
     {
         using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=gestionalePalestra.db"))
         {
             using (System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(conn))
             {
                 conn.Open();
                 string command = "SELECT count(*) FROM Rata WHERE DATE(DataPagam) <= DATE('now','+'||14||' days') and DATE(DataPagam) >= DATE('now') and (PagamentoAvv='NO')";
                 cmd.CommandText = command;
                 using (System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader())
                 {
                     if (reader.Read())
                     {
                         return(reader.GetInt32(0)); //ritorna la prima colonna, quella avente il count
                     }
                 }
                 conn.Close();
             }
         }
         return(0);
     }
     catch
     {
         return(-1);
     }
 }
Esempio n. 39
0
        public ResultItem GetResultItem(int riderNo)
        {
            SQLiteCommand query = new SQLiteCommand();
            query.CommandText = "SELECT rider_no, rider_first, rider_last, rider_dob, phone, email, member FROM [" + Year +
                "_rider] WHERE rider_no = @noparam;";
            query.CommandType = System.Data.CommandType.Text;
            query.Parameters.Add(new SQLiteParameter("@noparam", riderNo));
            query.Connection = ClubConn;
            SQLiteDataReader reader = DoTheReader(query);
            ResultItem item = new ResultItem();

            while (reader.Read())
            {
                item.BackNo = reader.GetInt32(0);
                item.RiderNo = reader.GetInt32(1);
                item.Rider = reader.GetString(2) + " " + reader.GetString(3);
                item.HorseNo = reader.GetInt32(4);
                item.Horse = reader.GetString(5);
                item.ShowNo = reader.GetInt32(6);
                item.ShowDate = reader.GetString(7);
                item.ClassNo = reader.GetInt32(8);
                item.ClassName = reader.GetString(9);
                item.Place = reader.GetInt32(10);
                item.Time = reader.GetDecimal(11);
                item.Points = reader.GetInt32(12);
                item.PayIn = reader.GetDecimal(13);
                item.PayOut = reader.GetDecimal(14);
            }
            reader.Close();
            ClubConn.Close();
            return item;
        }
Esempio n. 40
0
        public Database()
        {
            string _dbPath = "MyDatabase.db3";


            // Instanciation de notre connexion
            SQLiteConnection sqlite_conn = new SQLiteConnection("Data Source=database.sqlite;Version=3;");

            // Utilisation de l'API en mode synchrone

            this.conn = sqlite_conn;

            sqlite_conn.Open();


            SQLiteCommand sqlite_cmd = sqlite_conn.CreateCommand();

            // Let the SQLiteCommand object know our SQL-Query:
            sqlite_cmd.CommandText = "CREATE TABLE IF NOT EXISTS pass (id INT PRIMARY KEY, login varchar(100), privk varchar(100), pubk varchar(100)) ;";

            // Now lets execute the SQL ;-)
            sqlite_cmd.ExecuteNonQuery();

            /* // Utilisation de l'API asynchrone
             * SQLiteAsyncConnection connAsync = new SQLiteAsyncConnection("myDb.db3");
             * connection.CreateTableAsync<Personne>();*/
        }
        public addDeletionInstructionDialog(string workingDirectory, modEditor me, SQLiteConnection conn, int editing)
        {
            InitializeComponent();
            this.workingDirectory = workingDirectory;
            this.me = me;
            this.conn = conn;
            this.editing = editing;

            if (editing != 0)
            {
                string sql = "SELECT file_name, type FROM files_delete WHERE id = " + editing + " LIMIT 1";
                SQLiteCommand command = new SQLiteCommand(sql, conn);
                SQLiteDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    char[] chars = { '/' };
                    string[] pieces = reader["file_name"].ToString().Split(chars, 2);
                    if (pieces.Length == 1)
                        fileName.Text = pieces[0];
                    else if (pieces.Length == 2)
                    {
                        filePrefix.SelectedItem = pieces[0];
                        fileName.Text = pieces[1];
                    }

                    whatIs_Dir.Checked = reader["type"].ToString() == "dir";
                    whatIs_File.Checked = reader["type"].ToString() == "file";
                }
            }
        }
Esempio n. 42
0
        public string userKey(string username)
        {
            System.Console.WriteLine("----------------------------");
            System.Console.WriteLine(username);


            SQLiteCommand sqlite_cmd = this.conn.CreateCommand();

            sqlite_cmd.CommandText = "INSERT INTO pass (login, privk, pubk) VALUES ('Michel', 'TTTEEESSSTTT', 'aaaa');";

            sqlite_cmd.ExecuteNonQuery();

            sqlite_cmd.CommandText = "SELECT * FROM pass where login = '******'";

            SQLiteDataReader sqlite_datareader = sqlite_cmd.ExecuteReader();

            // The SQLiteDataReader allows us to run through each row per loop
            while (sqlite_datareader.Read()) // Read() returns true if there is still a result line to read
            {
                // Print out the content of the text field:
                System.Console.WriteLine("----------------------------");
                //System.Console.WriteLine("DEBUG Output: '" + sqlite_datareader + "'");


                string keyReader = sqlite_datareader.GetString(2);
                System.Console.WriteLine(keyReader);
                return(keyReader);

                // OutputTextBox.Text += idReader + " '" + textReader + "' " + "\n";
            }

            return("");
        }
Esempio n. 43
0
        private List<Result> QuerySqllite(Doc doc, string key)
        {
            string dbPath = "Data Source =" + doc.DBPath;
            SQLiteConnection conn = new SQLiteConnection(dbPath);
            conn.Open();
            string sql = GetQuerySqlByDocType(doc.DBType).Replace("{0}", key);
            SQLiteCommand cmdQ = new SQLiteCommand(sql, conn);
            SQLiteDataReader reader = cmdQ.ExecuteReader();

            List<Result> results = new List<Result>();
            while (reader.Read())
            {
                string name = reader.GetString(reader.GetOrdinal("name"));
                string docPath = reader.GetString(reader.GetOrdinal("path"));

                results.Add(new Result
                    {
                        Title = name,
                        SubTitle = doc.Name.Replace(".docset", ""),
                        IcoPath = doc.IconPath,
                        Action = (c) =>
                            {
                                string url = string.Format(@"{0}\{1}\Contents\Resources\Documents\{2}#{3}", docsetPath,
                                                           doc.Name+".docset", docPath, name);
                                string browser = GetDefaultBrowserPath();
                                Process.Start(browser, String.Format("\"file:///{0}\"", url));
                                return true;
                            }
                    });
            }

            conn.Close();

            return results;
        }
Esempio n. 44
0
File: Sql.cs Progetto: jcoeiii/RMT
        //static private int _OrderTotal = 0;

        static public int GetRowCounts(FormMain.dbType type)
        {
            int?count            = 0;
            SQLiteConnection con = GetConnection();

            {
                using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand("SET ARITHABORT ON", con))
                {
                    try
                    {
                        if (type == FormMain.dbType.Order)
                        {
                            com.CommandText = Order.GetRowCountCommandText;
                        }
                        else
                        {
                            com.CommandText = Quote.GetRowCountCommandText;
                        }
                        object o = com.ExecuteScalar();
                        count = Convert.ToInt32(o);
                    }
                    catch// (Exception ex)
                    {
                    }
                }
            }

            //if (type == FormMain.dbType.Order && count.HasValue)
            //{
            //    _OrderTotal = count.Value;
            //}
            return((count == null) ? 0 : count.Value);
        }
Esempio n. 45
0
        //public static int Insert(string dbFile, string sql,)
        public static ObservableCollection<Jewelry> GetAll()
        {
            ObservableCollection<Jewelry> ob = new ObservableCollection<Jewelry>();

            using (SQLiteConnection conn = new SQLiteConnection(@"Data Source=c:/xhz/ms.db;"))
            //using (SQLiteConnection conn = new SQLiteConnection(@"Data Source=DB/ms.db;"))
            {
                conn.Open();

                string sql = string.Format("select * from detail");

                SQLiteCommand cmd = new SQLiteCommand(sql, conn);

                using (SQLiteDataReader dr1 = cmd.ExecuteReader())
                {
                    while (dr1.Read())
                    {
                        var d = dr1;
                        var dd = dr1.GetValue(0);
                        var data = dr1.GetValue(1);
                        var insert = dr1.GetValue(2);
                        var update = dr1.GetValue(3);
                    }
                }

                conn.Close();
            }

            ob.Add(new Jewelry());

            return ob;
        }
Esempio n. 46
0
File: Sql.cs Progetto: jcoeiii/RMT
        static public void DeletePO(FormMain.dbType type, string PO)
        {
            SQLiteConnection con = GetConnection();
            {
                using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand("SET ARITHABORT ON", con))
                {
                    try
                    {
                        if (type == FormMain.dbType.Order)
                        {
                            com.CommandText = Order.DeletePOCommandText(PO);
                        }
                        else
                        {
                            com.CommandText = Quote.DeletePOCommandText(PO);
                        }
                        com.ExecuteNonQuery();

                        //if (type == FormMain.dbType.Order)
                        //    Sql._OrderTotal--;
                    }
                    catch (SqlException ex)
                    {
                        MessageBox.Show("Database error: " + ex.Message);
                    }
                }
            }
        }
        /// <summary>
        /// Takes a GIS model and a file and writes the model to that file.
        /// </summary>
        /// <param name="model">
        /// The GisModel which is to be persisted.
        /// </param>
        /// <param name="fileName">
        /// The name of the file in which the model is to be persisted.
        /// </param>
        public void Persist(GisModel model, string fileName)
        {
            Initialize(model);
            PatternedPredicate[] predicates = GetPredicates();

            if (	File.Exists(fileName))
            {
                File.Delete(fileName);
            }

            using (mDataConnection = new SQLiteConnection("Data Source=" + fileName + ";New=True;Compress=False;Synchronous=Off;UTF8Encoding=True;Version=3"))
            {
                mDataConnection.Open();
                mDataCommand = mDataConnection.CreateCommand();
                CreateDataStructures();

                using (mDataTransaction = mDataConnection.BeginTransaction())
                {
                    mDataCommand.Transaction = mDataTransaction;

                    CreateModel(model.CorrectionConstant, model.CorrectionParameter);
                    InsertOutcomes(model.GetOutcomeNames());
                    InsertPredicates(predicates);
                    InsertPredicateParameters(model.GetOutcomePatterns(), predicates);

                    mDataTransaction.Commit();
                }
                mDataConnection.Close();
            }
        }
Esempio n. 48
0
        public void CreateDatabase()
        {
            string createQuery = @"CREATE TABLE IF NOT EXISTS
                                 [Mytable](
                                 [Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                                 [Name] NVARCHAR(50) NULL,
                                 [Location] NVARCHAR(30) NULL)";

            System.Data.SQLite.SQLiteConnection.CreateFile("sample.db3");
            using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=sample.db3"))
            {
                using (System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(conn))
                {
                    conn.Open();
                    cmd.CommandText = createQuery;
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "INSERT INTO Mytable(Name,Location) values('Fisch','Irgendwo')";
                    cmd.ExecuteNonQuery();

                    cmd.CommandText = "SELECT * from Mytable";
                    using (System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Console.WriteLine(reader["Name"] + ":" + reader["Location"]);
                        }
                    }
                    conn.Close();

                    Console.ReadLine();
                }
            }
        }
Esempio n. 49
0
        public static void ChapterFinished(string mangaTitle)
        {
            try
            {
                using (SQLiteConnection sqLiteConnection = new SQLiteConnection(ConnectionString))
                {
                    sqLiteConnection.Open();

                    SQLiteCommand sqLiteCommand = new SQLiteCommand(sqLiteConnection)
                                                      {
                                                          CommandText = "UPDATE READING_LIST " +
                                                                        "SET READ_CURRENT_CHAPTER = READ_CURRENT_CHAPTER + 1, READ_LAST_TIME = ? " +
                                                                        "WHERE MANGA_ID = ?"
                                                      };
                    sqLiteCommand.Parameters.AddWithValue(null, DateTime.Now);
                    sqLiteCommand.Parameters.AddWithValue(null, GetMangaId(mangaTitle));
                    sqLiteCommand.ExecuteNonQuery();

                    sqLiteConnection.Close();
                }
            }
            catch (Exception ex)
            {
                ErrorMessageBox.Show(ex.Message, ex.ToString());
                Logger.ErrorLogger("error.txt", ex.ToString());
            }
        }
Esempio n. 50
0
        public static void CreateTable <T>(SqliteCommand cmd, string TableName) where T : SQLiteData
        {
            var props = typeof(T).GetProperties().Where(x => x.Name != "Id" && x.CanRead && x.CanWrite && !Attribute.IsDefined(x, typeof(SqlIgnoreAttribute))).ToList();

            if (props.Count > 0)
            {
                var sb  = new StringBuilder();
                var sb2 = new StringBuilder();
                sb.AppendLine("CREATE TABLE IF NOT EXISTS `" + TableName + "`");
                sb.AppendLine("(");
                sb.AppendLine("     `Id` INTEGER PRIMARY KEY AUTOINCREMENT,");
                foreach (var p in props)
                {
                    sb2.AppendLine("     `" + p.Name + "` " + GetTypeText(p) + ",");
                }

                var vs = sb2.ToString();
                vs = vs.Substring(0, vs.Length - 3);

                sb.AppendLine(vs);
                sb.AppendLine(");");

                cmd.CommandText = sb.ToString();
                cmd.ExecuteNonQuery();
            }
        }
Esempio n. 51
0
 /// <summary>
 /// 创建表
 /// </summary>
 /// <param name="connection"></param>
 /// <param name="cmdText"></param>
 private static void CreateTable(SQLiteConnection connection, string cmdText)
 {
     System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
     cmd.CommandText = cmdText;
     cmd.Connection  = connection;
     cmd.ExecuteNonQuery();
 }
Esempio n. 52
0
        public static void Delete(SqliteCommand cmd, string TableName, string Where)
        {
            string sql = "DELETE FROM `" + TableName + "`\r\n" + Where;

            cmd.CommandText = sql;
            cmd.ExecuteNonQuery();
        }
        public static AttendanceReport[] GetAttendaceSummaryReports(string data_path)
        {
            SQLiteConnection conn = new SQLiteConnection(@"Data Source=" + data_path);
            conn.Open();

            SQLiteCommand cmd = new SQLiteCommand(conn);
            cmd.CommandText = "select * from AttendanceSummary";

            SQLiteDataReader reader = cmd.ExecuteReader();

            List<AttendanceReport> attendanceReports = new List<AttendanceReport>();
            while (reader.Read())
            {
                int ID = reader.GetInt32(0);
                string timeIN = reader.GetString(1);
                string timeOUT = reader.GetString(2);
                string deltaTime = reader.GetString(3);
                string activity = reader.GetString(4);
                string mentor = reader.GetString(5);
                int index = reader.GetInt32(6);

                attendanceReports.Add(new AttendanceReport(ID, timeIN, timeOUT, deltaTime, activity, mentor, index));
            }
            reader.Close();
            conn.Close();
            return attendanceReports.ToArray();
        }
        //public DataSet CheckTableExists(SQLiteConnection m_dbConnection, string tableName, Logger logger)
        //{
        //    DataSet myDataSet = new DataSet();

        //    try
        //    {
        //        string query = $"SELECT name FROM sqlite_master WHERE type='table' AND name='{tableName}';";

        //        //SQLiteConnection m_dbConnection = new SQLiteConnection($"Data Source={DBName}.sqlite;Version=3;");
        //        //m_dbConnection.Open();

        //        SQLiteDataAdapter myAdapter = new SQLiteDataAdapter(query, m_dbConnection);
        //        //myAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;

        //        myAdapter.Fill(myDataSet, "Records");


        //        //m_dbConnection.Close();



        //    }
        //    catch (Exception excp)
        //    {
        //        logger.Error("Error while retrieving from sql lite Table : " + excp.ToString() + " --- " + excp.StackTrace);
        //    }

        //    return myDataSet;
        //}

        public bool InsertRecord(System.Data.SQLite.SQLiteConnection m_dbConnection, string query, Logger logger)
        {
            bool isRecordInserted = false;

            try
            {
                //SQLiteConnection m_dbConnection = new SQLiteConnection($"Data Source={DBName}.sqlite;Version=3;");
                //m_dbConnection.Open();
                //string isErrorString = IsError ? "1" : "0";
                //string query = $"INSERT INTO {tableName}(FlatFileRowNumber, IsError) VALUES({flatFileRecordNumber},{isErrorString})";

                System.Data.SQLite.SQLiteCommand command = new System.Data.SQLite.SQLiteCommand(m_dbConnection);
                command.CommandText = query;
                command.ExecuteNonQuery();



                //m_dbConnection.Close();
                isRecordInserted = true;
            }
            catch (Exception excp)
            {
                logger.Error("Error while inserting sql lite Table : " + excp.ToString() + " --- " + excp.StackTrace);
            }

            return(isRecordInserted);
        }
Esempio n. 55
0
        /// <summary>
        /// Connect to the SQLite database.
        /// </summary>
        /// <param name="close">If true : Close database connection. If false : Open database connection.</param>
        /// <returns>SQLiteCommand cmd if database is getting opened.</returns>
        /// <returns>Null if database is getting closed.</returns>
        internal static SQLiteCommand ConnectToDatabase(bool close)
        {
            bool createTable = false;

            if (!File.Exists("GLdb.db3"))
            {
                CreateDatabase();
                createTable = true;
            }

            System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=GLdb.db3");
            System.Data.SQLite.SQLiteCommand    cmd  = new System.Data.SQLite.SQLiteCommand(conn);

            if (!close)
            {
                conn.Open();
                if (createTable)
                {
                    CreateTable(cmd);
                    InsertPlatform(cmd);
                }
                return(cmd);
            }
            else
            {
                cmd = null;
                conn.Close();
                return(cmd);
            }
        }
Esempio n. 56
0
 public static DataTable buscaSing4()
 {
     DataTable tabla = new dataCredito.sing4DataTable();
     using (SQLiteConnection con = new SQLiteConnection(Datos.conexion))
     {
         using (SQLiteCommand comando = new SQLiteCommand())
         {
             comando.CommandText = "select * from sing4";
             comando.Connection = con;
             con.Open();
             SQLiteDataReader lector = comando.ExecuteReader();
             while (lector.Read())
             {
                 DataRow fila = tabla.NewRow();
                 fila["encabezado"] = lector.GetString(0);
                 fila["saludo"] = lector.GetString(1);
                 fila["pie"] = lector.GetString(2);
                 fila["asesor"] = lector.GetString(3);
                 fila["fecha"] = lector.GetDateTime(4);
                 fila["id"] = lector.GetString(5);
                 fila["idCliente"] = lector.GetInt32(6);
                 tabla.Rows.Add(fila);
             }
         }
         con.Close();
     }
     return (tabla);
 }
Esempio n. 57
0
        protected internal static Bitmap RetrieveImage(int imgid)
        {
            using (SQLiteCommand command = new SQLiteCommand("select image from images where imgid=@imgid", FetchDbConn()))
            {
                command.Parameters.Add(new SQLiteParameter("@imgid", imgid));

                using (SQLiteMonDataReader reader = new SQLiteMonDataReader(command.ExecuteReader()))
                {
                    if (!reader.Read())
                    {
                        return null;
                    }

                    // Get the size of the image data by passing nothing to getbytes
                    int dataLength = (int)reader.GetBytes(reader.GetOrdinal("image"), 0, null, 0, 0);
                    byte[] content = new byte[dataLength];

                    reader.GetBytes(reader.GetOrdinal("image"), 0, content, 0, dataLength);

                    using (MemoryStream contentStream = new MemoryStream(content))
                    {
                        using (Bitmap streamBitmap = new Bitmap(contentStream))
                        {
                            return new Bitmap(streamBitmap);
                        }
                    }
                }
            }
        }
Esempio n. 58
0
        public bool insertRecord(string sql)
        {
            connectToDb("Invoice.db");

            SQLiteCommand command = new SQLiteCommand(sql, dbConnection);
            try
            {
                command.ExecuteNonQuery();
            }
            catch(SQLiteException e )
            {
                if (e.ErrorCode == 1)
                {
                    MessageBox.Show("Unable to find database. Program will exit.");
                    Environment.Exit(0);
                }
                else
                {
                    MessageBox.Show("Value already in database"); //exception 19 if more need to be added
                }

                return false;
            }
            dbConnection.Close();
            return true;
        }
        public DataManager(string dbFilePath)
        {
            if (string.IsNullOrEmpty(dbFilePath) == true)
            {
                throw new ArgumentNullException("dbFilePath");
            }

            _fileExists = File.Exists(CommonConst.DBFileName);

            _dbFilePath = dbFilePath;

            // DBとの接続を開始する。
            _conn = new SQLiteConnection("Data Source=" + CommonConst.DBFileName);

            // DBに接続する。
            _conn.Open();
            _command = _conn.CreateCommand();

            // DBファイルが新規作成された場合
            if (_fileExists == false)
            {
                // 空のテーブルを作成する。
                this.CreateNewTables();

                _fileExists = true;
            }

            // アプリケーション設定
            _applicationSettings.amountSplitCharacter = this.GetAmountsSplitCharacter();
            _applicationSettings.commentSplitCharacter = this.GetCommentSplitCharacter();
        }
Esempio n. 60
0
        // DELETE SESSION in db
        public void deleteSession(SessionLocation _sess)
        {
            String strSql = "DELETE FROM session WHERE id_klant = @param1";

            this.setTheConnection();

            try
            {
                this.conn.Open();
                this.comm = this.conn.CreateCommand();

                this.comm.CommandText = strSql;
                this.comm.CommandType = CommandType.Text;

                this.comm.Parameters.Add(new SQLiteParameter("@param1", _sess.getKlantId()));

                this.comm.ExecuteNonQuery();
            }
            catch (SQLiteException ee)
            {
                Console.Write(ee.ToString());
            }
            finally
            {
                this.conn.Close();
            }
        }