Beispiel #1
0
 /// <summary>
 /// Execute a scalar update with the open database
 /// connection.
 /// </summary>
 /// <param name="sql">sql string.</param>
 public static void ScalarUpdate(string sql)
 {
     try
     {
         SQLiteCommand mycommmand = new SQLiteCommand(sql, manual_cnn);
         mycommmand.ExecuteScalar();
         mycommmand.CommandText = "SELECT last_insert_rowid()";
         Console.WriteLine(mycommmand.ExecuteScalar());
     }
     catch (Exception ex)
     {
         throw ex;
     }
 }
        public static void AddBook(string Title, string authorName, DateTime publish, string ISBN)
        {
            string connectionStr = "Data Source=..\\..\\book.db;Version=3;";
            var connection = new SQLiteConnection(connectionStr);
            connection.Open();
            using (connection)
            {
                var addAuthorCommand = new SQLiteCommand("INSERT INTO author(Name) VALUES(@name)", connection);
                addAuthorCommand.Parameters.AddWithValue("@name", authorName);
                addAuthorCommand.ExecuteNonQuery();

                var getAuthIDCommand = new SQLiteCommand("SELECT idAuthor FROM author WHERE Name=" + "@name", connection);
                getAuthIDCommand.Parameters.AddWithValue("@name", authorName);

                var authID = getAuthIDCommand.ExecuteScalar();

                var addBookCommand =
                    new SQLiteCommand(
                        "INSERT INTO book(Name,date,ISBN,AuthorID) VALUES(@title,@publish,@ISBN,@authID)",
                        connection);
                addBookCommand.Parameters.AddWithValue("@title", Title);
                addBookCommand.Parameters.AddWithValue("@publish", publish);
                addBookCommand.Parameters.AddWithValue("@ISBN", ISBN);
                addBookCommand.Parameters.AddWithValue("@authID", authID);
                addBookCommand.ExecuteNonQuery();
            }
        }
Beispiel #3
0
 public List<Stroke> MatchStroke(string stroke, AI.Quadrant q, int language)
 {
     bool b = true;
 REDO:
     conn = new System.Data.SQLite.SQLiteConnection(@"Data Source=CoreInkLib.dll");
     conn.Open();
     SQLiteCommand sqc = new SQLiteCommand("SELECT * FROM strokes WHERE directions = '" + stroke + "' AND quadrant='" + Quadrant2Str(q) + "' AND lang = " + language.ToString() + ";", conn);
     SQLiteDataReader sqr = sqc.ExecuteReader();
     List<Stroke> l = new List<Stroke>();
     while (sqr.Read())
     {
         Stroke st = new Stroke();
         st.sid = Convert.ToInt32(sqr[0]);
         st.vid = Convert.ToInt32(sqr[1]);
         st.cid = Convert.ToInt32(sqr[2]);
         st.thisstroke = Convert.ToInt32(sqr[5]);
         st.quadrant = sqr[4].ToString();
         sqc = new SQLiteCommand("SELECT ifnull(phase,0) FROM strokes WHERE vid = " + st.vid.ToString() + " AND phase >" + st.thisstroke.ToString() + " AND lang=" + language.ToString() + ";", conn);
         st.nextstroke = Convert.ToInt32(sqc.ExecuteScalar());
         foreach (char c in sqr[3].ToString())
         {
             st.directions.Add(Convert.ToInt32(c.ToString()));
         }
         if (b == false)
             st.quadrant = "C";
         l.Add(st);
     }
     conn.Close();
     if (q.third && l.Count == 0 && b)
     {
         q.third = false; q.second = true; b = false;
         goto REDO;
     }
     return l;
 }
        /// <summary>
        /// Given a GUID, returns the current status for that node.
        /// </summary>
        /// <param name="guid">A unique string that identifies a specific node.</param>
        /// <returns>A string with the node status for the given guid</returns>
        public string GetStatus(string guid)
        {
            string status = "";

            string statusQuery = "SELECT status FROM Node_Status WHERE guid = @pGUID";

            SQLiteCommand statusCmd = new SQLiteCommand(statusQuery, conn);

            SQLiteParameter pGUID = new SQLiteParameter("@pGUID", guid);

            statusCmd.Parameters.Add(pGUID);

            try
            {
                conn.Open();
                status = statusCmd.ExecuteScalar().ToString();
                conn.Close();
            }
            catch (SQLiteException ex)
            {
                //if anything is wrong with the sql statement or the database,
                //a SQLiteException will show information about it.
                Debug.Print(ex.Message);

                //always make sure the database connection is closed.
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }

            return status;
        }
Beispiel #5
0
 // Добавляем новый счет
 private void button1_Click(object sender, EventArgs e)
 {
     String NewSchet = textBox1.Text;
     if (String.IsNullOrWhiteSpace(NewSchet)) // Проверяем название счета
     {
         DialogResult Result = MessageBox.Show("Недопустимое название", "", MessageBoxButtons.OK);
     }
     else
     {
         string q = "SELECT COUNT (*) FROM scheta WHERE schet = ('" + NewSchet + "')"; // Проверяем сколько таких же
         SQLiteConnection Connection = new SQLiteConnection(string.Format("Data Source={0};", "budget.db"));
         SQLiteCommand sqlitecommand = new SQLiteCommand(q, Connection);
         Connection.Open();
         sqlitecommand.ExecuteNonQuery();
         int ans = 0;
         ans = Convert.ToInt32(sqlitecommand.ExecuteScalar());
         if (ans == 0) // Если таких 0
         {
             q = "INSERT INTO scheta (schet) VALUES ('" + NewSchet + "')"; // Добавляем
             Connection = new SQLiteConnection(string.Format("Data Source={0};", "budget.db"));
             sqlitecommand = new SQLiteCommand(q, Connection);
             Connection.Open();
             sqlitecommand.ExecuteNonQuery();
             Connection.Close();
             UpdateTable();
         }
         else // Если есть - сообщаем
         {
             DialogResult Result = MessageBox.Show("Счет не уникален", "", MessageBoxButtons.OK);
         }
     }
 }
		public static Project LoadProject(string existingProject)
		{
			try {
				string projectsPath = System.IO.Path.GetDirectoryName( System.Reflection.Assembly.GetExecutingAssembly().Location);
				Project loadedProj=new Project();
				
				string projectSQL = "SELECT project_base64 FROM project";// WHERE id_project=0"+id_samples.ToString();
				SQLiteConnection projectConn = new SQLiteConnection("Data Source="+ projectsPath + @"\\Projects\\" + existingProject + ".db;Version=3;New=False;Compress=True;Synchronous=Off");
				SQLiteCommand cmd = new SQLiteCommand(projectSQL,projectConn);
				projectConn.Open();
				
				loadedProj = (Project)ConversionUtilities.DeserializeBase64((string)cmd.ExecuteScalar());
				
//				using(SQLiteDataReader sqReader = cmd.ExecuteReader())
//				{
//					if (sqReader.Read())
//					{
//						loadedProj = (Project)ConversionUtilities.DeserializeBase64(sqReader.GetString(0));
//					}
//				}
				
				projectConn.Close();
				
				return loadedProj;
			} catch (Exception ex) {
				throw ex;
			}


		}
Beispiel #7
0
        //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);
        }
Beispiel #8
0
 public static bool Check(string username)
 {
     objConnection.Open();
     string cmdstring = "select Username from userdata where Username = '******'";
     SQLiteCommand sqlcmd = new SQLiteCommand(cmdstring,objConnection);
     try
     {
          string result = (string)sqlcmd.ExecuteScalar();
          if (result == "" || result == null)
          {
              objConnection.Close();
              return false;
          }
          else
          {
              objConnection.Close();
              return true;
          }
     }
     catch (System.Data.SQLite.SQLiteException)
     {
         objConnection.Close();
         return false;
     }
 }
Beispiel #9
0
        /// <summary>
        /// Authenticate User with giver login data. Returns null if user does not exist
        /// </summary>
        /// <param name="username"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        public static UserDatabaseError Authenticate(string username, string password, out User user)
        {
            user = null;
            SQLiteConnection connection = new SQLiteConnection(DatabaseManager.CONNECTION_STRING);
            SQLiteCommand command = new SQLiteCommand("SELECT salt FROM users WHERE username=@username and active = 1", connection);
            //SQLiteCommand command = new SQLiteCommand("SELECT count(*) FROM users", connection);
            command.Parameters.AddWithValue("@username", username);
            try
            {
                connection.Open();
            }
            catch
            {
                return UserDatabaseError.ERR_DATABASE_CONNECTION;
            }
            String salt = (string)command.ExecuteScalar();

            if (salt == null || salt == string.Empty)
            {
                connection.Close();
                return UserDatabaseError.ERR_USER_DOES_NOT_EXIST;
            }
            string hash = DatabaseManager.GetSha256(password + salt);
            command.CommandText = "SELECT name, privilege FROM users WHERE username=@username AND sha256p =@password";
            command.Parameters.AddWithValue("@password", hash);

            SQLiteDataReader reader = command.ExecuteReader();
            while (reader.Read())
                user = new User(username, (string)reader["name"], (User.UserPrivilege)(Int64)reader["privilege"]);
            if (user == null)
                return UserDatabaseError.ERR_AUTH;

            connection.Close();
            return UserDatabaseError.ERR_SUCCESS;
        }
Beispiel #10
0
        public int Insert()
        {
            try
            {
                this.Connection.Open();

                //
                // TODO: Check for existing ListID
                //

                SQLiteCommand command = new SQLiteCommand("INSERT INTO List (ListID, ListName) VALUES (@ListID, @ListName); SELECT last_insert_rowid();", this.Connection);
                command.Parameters.AddWithValue("@ListID", this.ListID);
                command.Parameters.AddWithValue("@ListName", this.ListName);
                Object result = command.ExecuteScalar();
                this.ID = int.Parse(result.ToString());
            }
            catch (Exception exception)
            {
            }
            finally
            {
                this.Connection.Close();
            }
            return this.ID;
        }
        public string Get(string id, string key)
        {
            try
            {
                using (var conn = new SQLiteConnection(this._connectionString))
                {
                    const string query = "SELECT value FROM cupcake WHERE id = @id AND key = @key";
                    using (var cmd = new SQLiteCommand(query, conn))
                    {
                        cmd.Parameters.AddWithValue("@id", id);
                        cmd.Parameters.AddWithValue("@key", key);

                        conn.Open();

                        object result = cmd.ExecuteScalar();
                        if (result is DBNull)
                            return null;

                        return (string)result;
                    }
                }
            }
            catch (SQLiteException ex)
            {
                throw new StorageException(ex.Message, ex);
            }
        }
Beispiel #12
0
Datei: DAL.cs Projekt: GregXP/XP
        public object Count(String comando)
        {
            AbreConexao();

            SQLiteCommand cmd = new SQLiteCommand(comando, _cnx);
            return cmd.ExecuteScalar();
        }
        public ViewResult Index(PriceLookup price)
        {
            var connectionString = @"Data Source=" + Server.MapPath("App_Data/prices.s3db");
            var priceDb = new PriceDb(connectionString);
            if (ModelState.IsValid)
            {
                if (!string.IsNullOrEmpty(price.Upc))
                {
                    priceDb.GetPriceByUpc(price);
                    if(price.Price == null)
                        ModelState.AddModelError("NotFound", "UPC not found");
                }
                else if (!string.IsNullOrEmpty(price.Isbn))
                {
                    var sql = "SELECT Price FROM Prices WHERE Isbn = @isbn";
                    using (var conn = new SQLiteConnection(connectionString))
                    {
                        conn.Open();
                        var sqlCommand = new SQLiteCommand(sql, conn);
                        sqlCommand.Parameters.AddWithValue("isbn", price.Isbn);
                        var result = sqlCommand.ExecuteScalar();
                        if (result == null)
                            ModelState.AddModelError("NotFound", "ISBN not found");
                        else
                            price.Price = (decimal)result;
                        conn.Close();
                    }
                }
                else
                    ModelState.AddModelError("Required", "You must enter a UPC or an ISBN");
            }

            return View(price);
        }
Beispiel #14
0
 public long AddUser(string name, string fullName)
 {
     string query = QueryBuilder.Insert("persons", GetNameColumnPair(name),
                                                   GetFullNameColumnPair(fullName));
     using (SQLiteCommand insert = new SQLiteCommand(query, m_conn))
         return (long)insert.ExecuteScalar();
 }
Beispiel #15
0
    void addRowSQL(string sName, string sNote)
    {
        //change sql data
        using (SQLiteConnection connection = new SQLiteConnection(cs)){
            connection.Open();
            SQLiteCommand cmd=new SQLiteCommand(
                string.Format("INSERT INTO Cars (id,name,note) VALUES(NULL,'{0}sql','{1}');",
                          sName, sNote),connection);
            cmd.ExecuteNonQuery();
            //get autoincrement value
            cmd=new SQLiteCommand("SELECT last_insert_rowid()",connection);
            long lastID = (long) cmd.ExecuteScalar();

            System.Console.WriteLine("added new row with "+lastID);
        }
        //change datagrid
        //		DataTable dt = ds.Tables[0];
        //		DataRow dr = dt.NewRow();
        //		dr["name"]=sName;
        //		dr["note"]=sNote;
        //		dr["id"]=lastID;
        //		dt.Rows.Add(dr);
        //		dt.AcceptChanges();
        //		ds.AcceptChanges();
        //		da.Update(ds.Tables[0]);
        doRefresh();
    }
Beispiel #16
0
        public static bool Login(int accountId, string password)
        {
            using (SQLiteConnection con = new SQLiteConnection(Settings.Default.AccountingConnectionString))
            {
                using (SQLiteCommand cmd = new SQLiteCommand())
                {
                    cmd.Connection = con;
                    cmd.CommandText = "SELECT COUNT(accountId) FROM vAccountPassword WHERE accountId = @account AND password = @password";
                    SQLiteParameter pAccount = new SQLiteParameter("account",accountId);
                    pAccount.Direction = ParameterDirection.Input;

                    SQLiteParameter pPassword = new SQLiteParameter("password", password);
                    pPassword.Direction = ParameterDirection.Input;

                    cmd.Parameters.AddRange(new SQLiteParameter[] { pAccount, pPassword });
                    con.Open();
                    if (Convert.ToInt32(cmd.ExecuteScalar()) == 1)
                    {
                        con.Close();
                        return true;
                    }
                    con.Close();
                    return false;
                }
            }
        }
Beispiel #17
0
 public bool Exists(string SQLString)
 {
     using (SQLiteConnection connection = new SQLiteConnection(connectionString))
     {
         using (SQLiteCommand cmd = new SQLiteCommand(SQLString, connection))
         {
             try
             {
                 connection.Open();
                 object obj = cmd.ExecuteScalar();
                 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                 {
                     return false;
                 }
                 else
                 {
                     return true;
                 }
             }
             catch (MySql.Data.MySqlClient.MySqlException e)
             {
                 connection.Close();
                 throw new Exception(e.Message);
             }
             finally
             {
                 cmd.Dispose();
                 connection.Close();
             }
         }
     }
 }
Beispiel #18
0
 public int numberOfUsernameMatches(string username)
 {
     string sql = "SELECT COUNT(*) FROM playerData WHERE username=@username";
     SQLiteCommand command = new SQLiteCommand(sql, this.p_dbConnection);
     command.Parameters.AddWithValue("@username", username);
     return Convert.ToInt32(command.ExecuteScalar());
 }
Beispiel #19
0
        public void AddDirectoriesRecursively(string folder, int? p_id)
        {
            DirectoryInfo di = new DirectoryInfo(folder);

            using (SQLiteCommand command = new SQLiteCommand(ConnectionManager.connection))
            {
                command.CommandText = @"Insert into [folders] (p_id,name) values (@p_id,@name); SELECT last_insert_rowid() AS [ID]";
                command.Parameters.Add(new SQLiteParameter("@p_id", p_id == null ? "null" : p_id.ToString()));
                command.Parameters.Add(new SQLiteParameter("@name", di.Name));
                p_id = int.Parse(command.ExecuteScalar().ToString());
            }

            using (SQLiteCommand command = new SQLiteCommand(ConnectionManager.connection))
            {
                command.CommandText = @"Insert into [files] (p_id,name) values (@p_id,@name)";
                SQLiteParameter _name = new SQLiteParameter("@name");
                command.Parameters.Add(_name);
                command.Parameters.Add(new SQLiteParameter("@p_id", p_id));

                foreach (FileInfo cfi in di.GetFiles())
                {
                    _name.Value = cfi.Name;
                    command.ExecuteNonQuery();
                }
            }

            foreach (DirectoryInfo cdi in di.GetDirectories())
                AddDirectoriesRecursively(cdi.FullName, p_id);
        }
        void addButton_Click(object sender, RoutedEventArgs e)
        {
            Button okButton = sender as Button;
            WrapPanel wp = okButton.Parent as WrapPanel;

            using (SQLiteCommand command = new SQLiteCommand(ConnectionManager.Connection))
            {
                command.CommandText = "insert into param (param_name,param_type,question) values (@param_name,@param_type,@question); SELECT last_insert_rowid();";
                command.Parameters.Add(new SQLiteParameter("@param_name",(wp.Children.FindByName(paramname) as TextBox).Text));
                command.Parameters.Add(new SQLiteParameter("@param_type",((wp.Children.FindByName(paramtype) as ComboBox).SelectedItem as ComboBoxItem).Content.ToString()));

                command.Parameters.Add(new SQLiteParameter("@question", (wp.Children.FindByName(paramquestion) as TextBox).Text));
                //SQLiteParameter p = new SQLiteParameter("@term_group_id");
                //p.Value = DBNull.Value;
                //command.Parameters.Add(p);
                int paramId = int.Parse(command.ExecuteScalar().ToString());

                if ((wp.Children.FindByName(termgroup) as ComboBox).IsEnabled)
                {
                    command.CommandText = "insert into term_param (term_group_id,param_id) values (@term_group_id,@param_id)";
                    command.Parameters.Add(new SQLiteParameter("@term_group_id", ((wp.Children.FindByName(termgroup) as ComboBox).SelectedItem as ComboBoxItem).Tag.ToString()));
                    command.Parameters.Add(new SQLiteParameter("@param_id", paramId));
                    command.ExecuteNonQuery();
                }
            }

            UpdateTable();
        }
Beispiel #21
0
        static void Main(string[] args)
        {
            // On crée nos variables:
            SQLiteConnection connection = new SQLiteConnection();
            
            // Disponible sur le site http://www.connectionstrings.com
            // "Data Source=filename;Version=3;"        
            connection.ConnectionString = "Data Source=test.db;Version=3";
            // Il serait bien sûr possible d'en faire plus, par exemple, mot de passe, mais on va s'en tenir à ça.

            // Ouvre la connexion à la base de donnée
            connection.Open();


            // À présent, il faut se rappeler le SQL que nous avons appris pendant les cours de base de donneés:
            // Créons donc nos tables:

            SQLiteCommand command = new SQLiteCommand("DROP TABLE IF EXISTS Étudiants; CREATE TABLE Étudiants(" +
                " ID integer Primary Key, " +
                " Nom string, " +
                " Prenom string, " +
                " DateDeNaissance date, " +
                " Ville string, " +
                " Technique string, " +
                " NuméroTéléphone string); ");

            command.Connection = connection;
            command.ExecuteNonQuery();

            // Il est possible de faire nos opérations en plus qu'une étape:

            SQLiteCommand command2 = new SQLiteCommand("INSERT INTO Étudiants VALUES ( @ID, @Nom, @Prenom, @DateNaissance, @Ville, @Technique,@No);", connection);
            command2.Parameters.AddWithValue("@ID", null);
            command2.Parameters.AddWithValue("@Nom", "Di Croci");

            SQLiteParameter param3 = new SQLiteParameter("@Prenom");
            param3.Value = "Michel";
            command2.Parameters.Add(param3);

            command2.Parameters.AddWithValue("@DateNaissance", "13/10/1979");
            command2.Parameters.AddWithValue("@Ville", "L'Assomption");
            command2.Parameters.AddWithValue("@Technique", "Informatique");
            command2.Parameters.AddWithValue("@No", "haha!");

            command2.ExecuteNonQuery();

            // Comme vous le constatez, on ne sait pas quel numéro d'enregistrement vient d'être entré...
            // Dans le cas de l'utilisation de clé étrangère (comme dnas notre TP), il peut devenir pratique d'avoir ce numéro
            // dans ce cas, nous devons utiliser select last_insert_rowid

            command.CommandText = "SELECT last_insert_rowid() FROM Étudiants";
            // Comme vous le savez, SELECT nous retourne un élément contrairement à un INSERT ou un UPDATE OU UN DELETE
            // Cela siginifie entre autre que la requête précédente va nous retourner qu'un seul scalaire, sinon nous aurions
            // utilisé le SQLiteDataReader que nous verrons ultérieurement
            object id = command.ExecuteScalar();

            Console.WriteLine(id);

            connection.Close();
        }
Beispiel #22
0
        private static void AddBook(string bookName, DateTime datePublish, long isbn, string author)
        {
            db.Open();
            using (db)
            {
                string bookStr = "INSERT INTO books " +
              "(titleBook, publishDate, ISBN) VALUES " +
              "(@title, @date, @isbn)";
                SQLiteCommand addBook = new SQLiteCommand(bookStr, db.ConnectionHandle);
                addBook.Parameters.AddWithValue("@title", bookName);
                addBook.Parameters.AddWithValue("@date", datePublish);
                addBook.Parameters.AddWithValue("@isbn", isbn);
                addBook.ExecuteNonQuery();

                SQLiteCommand cmdSelectIdentity = new SQLiteCommand("SELECT last_insert_rowid()", db.ConnectionHandle);
                long insertedRecordId = (long)cmdSelectIdentity.ExecuteScalar();

                string authorStr = "INSERT INTO authors " +
                            "(Books_idBooks, AuthorName) VALUES " +
                            "(@bookId, @name)";
                SQLiteCommand addAuthor = new SQLiteCommand(authorStr, db.ConnectionHandle);
                addAuthor.Parameters.AddWithValue("@bookId", (int)insertedRecordId);
                addAuthor.Parameters.AddWithValue("@name", author);
                addAuthor.ExecuteNonQuery();
            }
        }
        public object ExecuteScalar(string sql)
        {
            object value = null;
            try
            {
                var con = CONNECTION.OpenCon();

                var cmd = new SQLiteCommand(sql, con);
                value = cmd.ExecuteScalar();

                cmd.Dispose();
                CONNECTION.CloseCon(con);
            }
            catch(Exception ex)
            {
                SLLog.WriteError(new LogData
                {
                    Source = ToString(),
                    FunctionName = "ExecuteScalar Error!",
                    Ex = ex,
                });
                return null;
            }

            return value;
        }
Beispiel #24
0
        public int AddPerson(Person newPerson)
        {
            CheckAndCreatePersonTable();
              long newId;

              using (SQLiteConnection connection = new SQLiteConnection(GetConnectionString()))
              {
            connection.Open();
            using (SQLiteCommand command = new SQLiteCommand(connection))
            {
              command.CommandText = "INSERT INTO Persons (FirstName, Surname, EMail, Age, Salary) VALUES($FirstName,$Surname,$EMail,$Age,$Salary)";
              command.Parameters.AddWithValue("$FirstName", newPerson.FirstName);
              command.Parameters.AddWithValue("$Surname", newPerson.Surname);
              command.Parameters.AddWithValue("$EMail", newPerson.EMail);
              command.Parameters.AddWithValue("$Age", newPerson.Age);
              command.Parameters.AddWithValue("$Salary", newPerson.Salary);
              command.CommandType = CommandType.Text;
              command.ExecuteNonQuery();

              command.CommandText = "SELECT last_insert_rowid()";
              newId = (long)command.ExecuteScalar();
            }
            connection.Close();
              }
              return (int)newId;
        }
        public static void CheckDBSchema()
        {
            try
            {
                using (SQLiteConnection conn = GetConnection())
                {
                    conn.Open();
                    using (var comm = new SQLiteCommand(conn))
                    {
                        comm.CommandText = "SELECT value from DBProperties WHERE name='dbversion'";
                        var versionNum = (string) comm.ExecuteScalar();

                        if (versionNum == DBVersion)
                        {
                            return;
                        }
                    }
                    conn.Close();
                }
            }
            catch (SQLiteException)
            {
            }
            SpecialTimeLinesRepository.Load();
            SpecialTimeLinesRepository.Export();
            DeleteDB();
            CreateDB();
            SpecialTimeLinesRepository.Import();
            SpecialTimeLinesRepository.Save();
        }
Beispiel #26
0
 public long Count()
 {
     var sql = "SELECT COUNT(*) FROM Patient";
     using (var command = new SQLiteCommand(sql, this.Connection))
     {
         return (long)command.ExecuteScalar();
     }
 }
 public static object ExecuteScalar(string query)
 {
     lock(lootLock) {
         if (lootConn == null) return null;
         SQLiteCommand command = new SQLiteCommand(query, lootConn);
         return command.ExecuteScalar();
     }
 }
Beispiel #28
0
 public long Add(string name, long parentId, CategoryType type)
 {
     string query = QueryBuilder.Insert("categories", GetNameColumnPair(name),
                                                           GetParentIdColumnPair(parentId),
                                                           GetTypeColumnPair(type));
     using (SQLiteCommand insert = new SQLiteCommand(query, m_conn))
         return (long) insert.ExecuteScalar();
 }
Beispiel #29
0
        private void button1_Click(object sender, EventArgs e)
        {
            com.Open();
            SQLiteCommand cmd_r = new SQLiteCommand("SELECT COUNT(*) FROM MEMBER", com);
            int Tabrows = Convert.ToInt32(cmd_r.ExecuteScalar());
            com.Close();
            com.Open();
            SQLiteCommand cmdsearch = new SQLiteCommand("SELECT * FROM MEMBER", com);
            SQLiteDataReader readsearch = cmdsearch.ExecuteReader();
            Form1 f = new Form1();
            string idkmitl = id_d;

            //MessageBox.Show(idkmitl+"naaaa");
            while (readsearch.Read())
            {
                if (readsearch["StanaM"].ToString().Equals("God") || readsearch["StanaM"].ToString().Equals("Admin"))
                {
                    //MessageBox.Show(readsearch["User"].ToString()+"//"+ readsearch["Pass"].ToString());
                    if (readsearch["User"].ToString().Equals(textBox1.Text) && readsearch["Pass"].ToString().Equals(textBox2.Text))
                    {

                        if (readsearch["IDKMITL"].ToString().Equals(idkmitl))
                        {
                            MessageBox.Show("ขออภัยคุณไม่สามารถอนุญาติตัวเองได้", "unsuccessful",
                            MessageBoxButtons.OK, MessageBoxIcon.Error);
                            com.Close();
                            return;
                        }
                        //com.Close();
                        _ctaa = true;
                        ch = true;
                        MessageBox.Show("login sucessful");
                        //return;

                    }
                    else if (Convert.ToInt32(readsearch["ID"].ToString()) == Tabrows  && ctaa == false)
                    {
                        MessageBox.Show("ขออภัยคุณกรอก Username หรือ Password(God, Admin) ไม่ถูกต้อง1", "unsuccessful",
                        MessageBoxButtons.OK, MessageBoxIcon.Error);
                        com.Close();
                        ch = true;
                        return;
                    }

                }
                else if (Convert.ToInt32(readsearch["ID"].ToString()) == Tabrows  && ctaa == false)
                {
                    MessageBox.Show("ขออภัยคุณกรอก Username หรือ Password(God, Admin) ไม่ถูกต้อง2", "unsuccessful",
                    MessageBoxButtons.OK, MessageBoxIcon.Error);
                    com.Close();
                    ch = true;
                    return;
                }
            }
            ch = true;
            com.Close();
            this.Close();
        }
Beispiel #30
0
        public static long LastID()
        {
            using (SQLiteCommand command = new SQLiteCommand(Controller.Connections["Main"]))
            {
                command.CommandText = "SELECT last_insert_rowid();";

                return (long)command.ExecuteScalar();
            }
        }
Beispiel #31
0
        // check to see if a value exist in the table
        public bool existsInTable(String name)
        {
            string sql = "SELECT count(*) FROM playerInfo WHERE name=:Name";
            SQLiteCommand command = new SQLiteCommand(sql, swarchDatabase);
            command.Parameters.AddWithValue(":Name", name);
            int count = Convert.ToInt32(command.ExecuteScalar());

            return (count != 0);
        }
Beispiel #32
0
 private void Open(string cinString)
 {
     this.con = new sl.SQLiteConnection("Data Source=" + cinString + "; FailIfMissing=True");
     this.con.Open();
     var scheme = this.con.GetSchema();
     {
         sl.SQLiteCommand cmd = new sl.SQLiteCommand("select sqlite_version();", con);
         var res = cmd.ExecuteScalar();
         Debug.Assert(this.con.ServerVersion == res.ToString(), "Версия базы не совпадает");
     }
 }
Beispiel #33
0
        // 创建表
        public void CreateTableToSqlite(string tableName, string createTableSql)
        {
            string sql_query_data = string.Format("SELECT count(*) FROM sqlite_master WHERE type='table' AND name='{0}'", tableName);

            cmd.CommandText = sql_query_data;
            cmd.Connection  = conn;
            try
            {
                if (0 == Convert.ToInt32(cmd.ExecuteScalar()))                  // 如果表不存在则创建表
                {
                    cmd.CommandText = createTableSql;                           // 创建注册用户表
                    cmd.Connection  = conn;
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }
        }
Beispiel #34
0
        private void BindGrid()
        // Esse Form de Consulta vai realizar a conexão com banco de dados e mostrar o que tem na tabela com os dados
        //inseridos no Form de cadastro. Basicamente é realizar um Select e associalos ao Fill para que possa ser compreendido
        //pelo Data Grid View , o Data Grid View é um recurso para manipular dados provenientes de um banco de dados.
        //Algumas adaptações via dll e Program.cs(https://stackoverflow.com/questions/3179028/mixed-mode-assembly-in-net-4) foram realizadas para
        //que o Data Grid View pudesse ser utilizado com Drive ODBC do SQLite. Devido a isso a sintaxe de conexão foi alterada...De todo modo funcionou
        {
            String connectionString = @"Data Source=C:\EncontreUmTrampo\cadastro.db";

            System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(connectionString);
            System.Data.SQLite.SQLiteCommand    cmd  = new System.Data.SQLite.SQLiteCommand("select ID,Vaga,Empresa,Area,Local,Data,Etapa from Trampo");
            cmd.Connection = conn;

            conn.Open();
            cmd.ExecuteScalar();
            System.Data.SQLite.SQLiteDataAdapter da = new System.Data.SQLite.SQLiteDataAdapter(cmd);
            System.Data.DataSet ds = new System.Data.DataSet();

            da.Fill(ds);

            dataGridView1.DataSource = ds;
            dataGridView1.DataMember = ds.Tables[0].TableName;
            conn.Close();
        }
Beispiel #35
0
 public object ExecuteScalar(string sql)
 {
     cmd.CommandText = sql;
     return(cmd.ExecuteScalar());
 }
Beispiel #36
0
        /// <summary>
        /// This method creates a new connection, executes the query using the given
        /// execution type and command behavior, closes the connection, and returns
        /// the results.  If the connection string is null, a temporary in-memory
        /// database connection will be used.
        /// </summary>
        /// <param name="commandText">
        /// The text of the command to be executed.
        /// </param>
        /// <param name="executeType">
        /// The execution type for the command.  This is used to determine which method
        /// of the command object to call, which then determines the type of results
        /// returned, if any.
        /// </param>
        /// <param name="commandBehavior">
        /// The command behavior flags for the command.
        /// </param>
        /// <param name="connectionString">
        /// The connection string to the database to be opened, used, and closed.  If
        /// this parameter is null, a temporary in-memory databse will be used.
        /// </param>
        /// <param name="args">
        /// The SQL parameter values to be used when building the command object to be
        /// executed, if any.
        /// </param>
        /// <returns>
        /// The results of the query -OR- null if no results were produced from the
        /// given execution type.
        /// </returns>
        public static object Execute(
            string commandText,
            SQLiteExecuteType executeType,
            CommandBehavior commandBehavior,
            string connectionString,
            params object[] args
            )
        {
            if (connectionString == null)
            {
                connectionString = DefaultConnectionString;
            }

            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                using (SQLiteCommand command = connection.CreateCommand())
                {
                    command.CommandText = commandText;

                    if (args != null)
                    {
                        foreach (object arg in args)
                        {
                            if (arg is SQLiteParameter)
                            {
                                command.Parameters.Add((SQLiteParameter)arg);
                            }
                            else
                            {
                                command.Parameters.Add(new SQLiteParameter(DbType.Object, arg));
                            }
                        }
                    }

                    switch (executeType)
                    {
                    case SQLiteExecuteType.None:
                    {
                        //
                        // NOTE: Do nothing.
                        //
                        break;
                    }

                    case SQLiteExecuteType.NonQuery:
                    {
                        return(command.ExecuteNonQuery(commandBehavior));
                    }

                    case SQLiteExecuteType.Scalar:
                    {
                        return(command.ExecuteScalar(commandBehavior));
                    }

                    case SQLiteExecuteType.Reader:
                    {
                        return(command.ExecuteReader(commandBehavior));
                    }
                    }
                }
            }

            return(null);
        }
Beispiel #37
0
        /// <summary>
        /// This method creates a new connection, executes the query using the given
        /// execution type and command behavior, closes the connection unless a data
        /// reader is created, and returns the results.  If the connection string is
        /// null, a temporary in-memory database connection will be used.
        /// </summary>
        /// <param name="commandText">
        /// The text of the command to be executed.
        /// </param>
        /// <param name="executeType">
        /// The execution type for the command.  This is used to determine which method
        /// of the command object to call, which then determines the type of results
        /// returned, if any.
        /// </param>
        /// <param name="commandBehavior">
        /// The command behavior flags for the command.
        /// </param>
        /// <param name="connectionString">
        /// The connection string to the database to be opened, used, and closed.  If
        /// this parameter is null, a temporary in-memory databse will be used.
        /// </param>
        /// <param name="args">
        /// The SQL parameter values to be used when building the command object to be
        /// executed, if any.
        /// </param>
        /// <returns>
        /// The results of the query -OR- null if no results were produced from the
        /// given execution type.
        /// </returns>
        public static object Execute(
            string commandText,
            SQLiteExecuteType executeType,
            CommandBehavior commandBehavior,
            string connectionString,
            params object[] args
            )
        {
            SQLiteConnection connection = null;

            try
            {
                if (connectionString == null)
                {
                    connectionString = DefaultConnectionString;
                }

                using (connection = new SQLiteConnection(connectionString))
                {
                    connection.Open();

                    using (SQLiteCommand command = connection.CreateCommand())
                    {
                        command.CommandText = commandText;

                        if (args != null)
                        {
                            foreach (object arg in args)
                            {
                                SQLiteParameter parameter = arg as SQLiteParameter;

                                if (parameter == null)
                                {
                                    parameter        = command.CreateParameter();
                                    parameter.DbType = DbType.Object;
                                    parameter.Value  = arg;
                                }

                                command.Parameters.Add(parameter);
                            }
                        }

                        switch (executeType)
                        {
                        case SQLiteExecuteType.None:
                        {
                            //
                            // NOTE: Do nothing.
                            //
                            break;
                        }

                        case SQLiteExecuteType.NonQuery:
                        {
                            return(command.ExecuteNonQuery(commandBehavior));
                        }

                        case SQLiteExecuteType.Scalar:
                        {
                            return(command.ExecuteScalar(commandBehavior));
                        }

                        case SQLiteExecuteType.Reader:
                        {
                            bool success = true;

                            try
                            {
                                //
                                // NOTE: The CloseConnection flag is being added here.
                                //       This should force the returned data reader to
                                //       close the connection when it is disposed.  In
                                //       order to prevent the containing using block
                                //       from disposing the connection prematurely,
                                //       the innermost finally block sets the internal
                                //       no-disposal flag to true.  The outer finally
                                //       block will reset the internal no-disposal flag
                                //       to false so that the data reader will be able
                                //       to (eventually) dispose of the connection.
                                //
                                return(command.ExecuteReader(
                                           commandBehavior | CommandBehavior.CloseConnection));
                            }
                            catch
                            {
                                success = false;
                                throw;
                            }
                            finally
                            {
                                //
                                // NOTE: If an exception was not thrown, that can only
                                //       mean the data reader was successfully created
                                //       and now owns the connection.  Therefore, set
                                //       the internal no-disposal flag (temporarily)
                                //       in order to exit the containing using block
                                //       without disposing it.
                                //
                                if (success)
                                {
                                    connection._noDispose = true;
                                }
                            }
                        }
                        }
                    }
                }
            }
            finally
            {
                //
                // NOTE: Now that the using block has been exited, reset the
                //       internal disposal flag for the connection.  This is
                //       always done if the connection was created because
                //       it will be harmless whether or not the data reader
                //       now owns it.
                //
                if (connection != null)
                {
                    connection._noDispose = false;
                }
            }

            return(null);
        }