C# SqliteCommand.ExecuteScalar方法代码示例

 public void DataCount(string table_name,out int count)
 {
     count=0;
     DbCommand = DbConnection.CreateCommand();
     DbCommand.CommandText = "SELECT COUNT(*) FROM " + table_name;
     count = System.Convert.ToInt32(DbCommand.ExecuteScalar());
 }

 /// <summary>
 /// Allows the programmer to retrieve single items from the DB.
 /// </summary>
 /// <param name="sql">The query to run.</param>
 /// <returns>A string.</returns>
 public string ExecuteScalar(string sql)
 {
     SqliteConnection cnn = new SqliteConnection(dbConnection);
     cnn.Open();
     SqliteCommand cmd = new SqliteCommand(cnn) { CommandText = sql };
     object value = cmd.ExecuteScalar();
     if (value != null)
         return value.ToString();
     else
         return "";
 }

 public static int ExecuteIntCommand(SqliteCommand command)
 {
     try
       {
       return Convert.ToInt32(command.ExecuteScalar());
       }
       catch (Exception ex)
       {
       MessageBox.Show(ex.Message);
       return -1;
       }
 }

    //判断表是否存在
    public bool IsTableExist(string table_name)
    {
        if (table_name == null)
        {
            return false;
        }

        DbCommand = DbConnection.CreateCommand();
        DbCommand.CommandText = "SELECT COUNT(*) FROM sqlite_master WHERE TYPE='table' AND NAME='"+ table_name+"'";
        //DbReader = DbCommand.ExecuteReader();
        if (0 == System.Convert.ToInt32(DbCommand.ExecuteScalar()))
            return false;
        else return true;

    }

        public List<CodeRecord> Load()
        {
            var rv = new List<CodeRecord>();

            // load code points
            using ( var tx = con.BeginTransaction() )
            using ( var cmd = new SqliteCommand( con ) ){
                cmd.Transaction = tx;
                cmd.CommandText = @"SELECT fullname, assembly, sourcefile, classname, name FROM methods";
                using ( var sth = cmd.ExecuteReader() ) {
                    while ( sth.HasRows && sth.Read() ){
                        var rec = new CodeRecord();
                        rec.FullMethodName = Convert.ToString( sth["fullname"] );
                        rec.Assembly = Convert.ToString( sth["assembly"] );
                        rec.SourceFile = Convert.ToString( sth["sourcefile"] );
                        rec.ClassName = Convert.ToString( sth["classname"] );
                        rec.Name = Convert.ToString( sth["name"] );

                        // get call count
                        var calls = new SqliteCommand( con );
                        calls.CommandText = "SELECT hits FROM calls WHERE assembly = :ASSEMBLY AND fullname = :FULLNAME";
                        calls.Parameters.Add( new SqliteParameter( ":ASSEMBLY", rec.Assembly ) );
                        calls.Parameters.Add( new SqliteParameter( ":FULLNAME", rec.FullMethodName ) );
                        var ccount = Convert.ToInt32( calls.ExecuteScalar() );
                        rec.CallCount = ccount;

                        // get lines
                        var lines = new SqliteCommand( con );
                        lines.CommandText = "SELECT line, hits FROM lines WHERE assembly = :ASSEMBLY AND fullname = :FULLNAME";
                        lines.Parameters.Add( new SqliteParameter( ":ASSEMBLY", rec.Assembly ) );
                        lines.Parameters.Add( new SqliteParameter( ":FULLNAME", rec.FullMethodName ) );
                        using ( var lsth = lines.ExecuteReader() ){
                            while ( lsth.HasRows && lsth.Read() ) {
                                var l = Convert.ToInt32( lsth["line"] );
                                var hc = Convert.ToInt32( lsth["hits"] );
                                rec.AddLines(l);
                                rec.SetHits( l, hc );
                            }
                        }

                        rv.Add(rec);
                    }
                }
                tx.Commit();
            }

            return rv;
        }

		public static void RunTests(string dbPath)
	    {
			using (var cnn = new SqliteConnection("Data Source=" + dbPath))
			{
				cnn.Open();

				// commit
				using (var trn = cnn.BeginTransaction(IsolationLevel.Serializable))
				using (var cmd = new SqliteCommand("CREATE TABLE IF NOT EXISTS nugettest (id INTEGER PRIMARY KEY AUTOINCREMENT, data TEXT);", cnn))
				{
					cmd.ExecuteNonQuery();

					cmd.CommandText = "DELETE FROM nugettest;";
					cmd.ExecuteNonQuery();

					cmd.CommandText = "INSERT INTO nugettest (data) VALUES (@someData);";
					cmd.Parameters.AddWithValue("@someData", "data here");

					cmd.ExecuteNonQuery();

					trn.Commit();
				}

				// rollback
				using (var trn = cnn.BeginTransaction(IsolationLevel.Serializable))
				using (var cmd = new SqliteCommand("INSERT INTO nugettest (data) VALUES (@someData);", cnn))
				{
					cmd.Parameters.AddWithValue("@someData", "data here");
					cmd.ExecuteNonQuery();

					trn.Rollback();
				}

				// check
				using (var cmd = new SqliteCommand("SELECT COUNT(*) nugettest;", cnn))
				{
					if (Convert.ToInt32(cmd.ExecuteScalar()) != 1)
					{
						throw new Exception("Something bad happened!");
					}
				}
			}
	    }

    //Called from initialize jump, jumpRj
    public static int GraphLinkInsert(string tableName, string eventName, string graphFileName, bool dbconOpened, SqliteCommand mycmd)
    {
        if(! dbconOpened) {
            Sqlite.Open();
        }
        mycmd.CommandText = "INSERT INTO graphLinkTable" +
                "(uniqueID, tableName, eventName, graphFileName, other1, other2)" +
                " VALUES (NULL, \"" + tableName + "\", \"" + eventName + "\", \"" + graphFileName + "\", \"\", \"\")" ;
        LogB.SQL(mycmd.CommandText.ToString());
        mycmd.ExecuteNonQuery();
        //int myLast = dbcon.LastInsertRowId;
        //http://stackoverflow.com/questions/4341178/getting-the-last-insert-id-with-sqlite-net-in-c
        string myString = @"select last_insert_rowid()";
        mycmd.CommandText = myString;
        int myLast = Convert.ToInt32(mycmd.ExecuteScalar()); // Need to type-cast since `ExecuteScalar` returns an object.
        if(! dbconOpened) {
            Sqlite.Close();
        }

        return myLast;
    }

        public string GetEmailByCustomerNumber(string num)
        {
            string output = "";
            try
            {
            
                using (SqliteConnection connection = new SqliteConnection(_connectionString))
                {
                    connection.Open();

                    string sql = "select email from CustomerLogin where customerNumber = " + num;
                    SqliteCommand cmd = new SqliteCommand(sql, connection);
                    output = (string)cmd.ExecuteScalar();
                }
                
            }
            catch (Exception ex)
            {
                log.Error("Error getting email by customer number", ex);
                output = ex.Message;
            }
            
            return output;
        }

        //
        // MembershipProvider.GetNumberOfUsersOnline
        //
        public override int GetNumberOfUsersOnline()
        {
            TimeSpan onlineSpan = new TimeSpan(0, System.Web.Security.Membership.UserIsOnlineTimeWindow, 0);
            DateTime compareTime = DateTime.Now.Subtract(onlineSpan);

            SqliteConnection conn = new SqliteConnection(connectionString);
            SqliteCommand cmd = new SqliteCommand("SELECT Count(*) FROM `" + tableName + "`" +
                    " WHERE LastActivityDate > $LastActivityDate AND ApplicationName = $ApplicationName", conn);

            cmd.Parameters.Add("$CompareDate", DbType.DateTime).Value = compareTime;
            cmd.Parameters.Add("$ApplicationName", DbType.String, 255).Value = pApplicationName;

            int numOnline = 0;

            try
            {
                conn.Open();

                numOnline = Convert.ToInt32(cmd.ExecuteScalar());
            }
            catch (SqliteException e)
            {
                if (WriteExceptionsToEventLog)
                {
                    WriteToEventLog(e, "GetNumberOfUsersOnline");

                    throw new ProviderException(exceptionMessage);
                }
                else
                {
                    throw e;
                }
            }
            finally
            {
                conn.Close();
            }

            return numOnline;
        }

        public override long InsertWithOnConflict (String table, String nullColumnHack, ContentValues initialValues, ConflictResolutionStrategy conflictResolutionStrategy)
        {
            if (!String.IsNullOrWhiteSpace(nullColumnHack)) {
                var e = new InvalidOperationException("{0} does not support the 'nullColumnHack'.".Fmt(Tag));
                Log.E(Tag, "Unsupported use of nullColumnHack", e);
                throw e;
            }

            var command = GetInsertCommand(table, initialValues, conflictResolutionStrategy);

            var lastInsertedId = -1L;
            try {
                command.ExecuteNonQuery();

                // Get the new row's id.
                // TODO.ZJG: This query should ultimately be replaced with a call to sqlite3_last_insert_rowid.
                var lastInsertedIndexCommand = new SqliteCommand("select last_insert_rowid()", Connection, currentTransaction);
                lastInsertedId = (Int64)lastInsertedIndexCommand.ExecuteScalar();
                lastInsertedIndexCommand.Dispose();
                if (lastInsertedId == -1L) {
                    Log.E(Tag, "Error inserting " + initialValues + " using " + command.CommandText);
                } else {
                    Log.V(Tag, "Inserting row " + lastInsertedId + " from " + initialValues + " using " + command.CommandText);
                }
            } catch (Exception ex) {
                Log.E(Tag, "Error inserting into table " + table, ex);
            } finally {
                command.Dispose();
            }
            return lastInsertedId;
        }

		public string GetFilepath(string hash)
		{
			using (SqliteCommand sqc = new SqliteCommand(connection)) {
				sqc.CommandText = "SELECT path FROM Files WHERE hash=:hash";
				sqc.Parameters.Add(new SqliteParameter("hash", hash));
				object result = sqc.ExecuteScalar();
				return (result == null) ? null : (string)result;
			}
		}

        protected void OnButtonOkClicked(object sender, EventArgs e)
        {
            string sql;
            if (NewItem)
            {
                sql = "INSERT INTO basis (name, width, delta_l, delta_h) " +
                    "VALUES (@name, @width, @delta_l, @delta_h)";
            }
            else
            {
                sql = "UPDATE basis SET name = @name, width = @width, delta_l = @delta_l, delta_h = @delta_h WHERE id = @id";
            }
            SqliteTransaction trans = ((SqliteConnection)QSMain.ConnectionDB).BeginTransaction();
            MainClass.StatusMessage("Запись основы...");
            try
            {
                SqliteCommand cmd = new SqliteCommand(sql, (SqliteConnection)QSMain.ConnectionDB, trans);

                cmd.Parameters.AddWithValue("@id", ItemId);
                cmd.Parameters.AddWithValue("@name", entryName.Text);
                cmd.Parameters.AddWithValue("@width", spinW.ValueAsInt);
                cmd.Parameters.AddWithValue("@delta_l", spinL.ValueAsInt);
                cmd.Parameters.AddWithValue("@delta_h", spinH.ValueAsInt);

                cmd.ExecuteNonQuery();

                if(NewItem)
                {
                    sql = @"select last_insert_rowid()";
                    cmd = new SqliteCommand(sql, (SqliteConnection)QSMain.ConnectionDB, trans);
                    ItemId = Convert.ToInt32(cmd.ExecuteScalar());

                    sql = "UPDATE basis SET ordinal = @id WHERE id = @id";
                    cmd = new SqliteCommand(sql, (SqliteConnection)QSMain.ConnectionDB, trans);
                    cmd.Parameters.AddWithValue("@id", ItemId);
                    cmd.ExecuteNonQuery();
                }

                // Запись Номенклатур
                foreach(object[] nomenclature in NomenclatureStore)
                {
                    if((bool) nomenclature[(int)NomenclatureCol.selected])
                    {
                        if((long) nomenclature[(int)NomenclatureCol.id] > 0)
                            sql = "UPDATE basis_items SET count = @count WHERE id = @id";
                        else
                            sql = "INSERT INTO basis_items (basis_id, item_id, count) VALUES (@basis_id, @item_id, @count)";

                        cmd = new SqliteCommand(sql, (SqliteConnection)QSMain.ConnectionDB, trans);
                        cmd.Parameters.AddWithValue("@basis_id", ItemId);
                        cmd.Parameters.AddWithValue("@id", nomenclature[(int)NomenclatureCol.id]);
                        cmd.Parameters.AddWithValue("@item_id", nomenclature[(int)NomenclatureCol.nomenclature_id]);
                        cmd.Parameters.AddWithValue("@count", nomenclature[(int)NomenclatureCol.count]);
                        cmd.ExecuteNonQuery();
                    }
                    else if((long) nomenclature[(int)NomenclatureCol.id] > 0)
                    {
                        sql = "DELETE FROM basis_items WHERE basis_id = @basis_id AND item_id = @item_id";

                        cmd = new SqliteCommand(sql, (SqliteConnection)QSMain.ConnectionDB, trans);
                        cmd.Parameters.AddWithValue("@basis_id", ItemId);
                        cmd.Parameters.AddWithValue("@item_id", nomenclature[(int)NomenclatureCol.nomenclature_id]);
                        cmd.ExecuteNonQuery();
                    }
                }

                if(ImageChanged)
                {
                    if(NewItem)
                    {
                        sql = @"select last_insert_rowid()";
                        cmd = new SqliteCommand(sql, (SqliteConnection)QSMain.ConnectionDB, trans);
                        ItemId = Convert.ToInt32(cmd.ExecuteScalar());
                    }

                    sql = "UPDATE basis SET image_size = @image_size, image = @image WHERE id = @id";
                    cmd = new SqliteCommand(sql, (SqliteConnection)QSMain.ConnectionDB, trans);
                    cmd.Parameters.AddWithValue("@id", ItemId);
                    cmd.Parameters.AddWithValue("@image_size", ImageHelper.OriginalFile.Length);
                    cmd.Parameters.AddWithValue("@image", ImageHelper.OriginalFile);
                    cmd.ExecuteNonQuery();
                }

                trans.Commit();
                MainClass.StatusMessage("Ok");
                Respond(Gtk.ResponseType.Ok);
            }
            catch (Exception ex)
            {
                trans.Rollback();
                QSMain.ErrorMessageWithLog(this, "Ошибка записи основы!", logger, ex);
            }
        }

		public void ScalarReturn()
		{
			// This should return the 1 line that got inserted in CreateTable() Test
			using (_conn)
			using (SqliteCommand cmd = new SqliteCommand("SELECT COUNT(*) FROM t1 WHERE  t LIKE '%äöüß'", _conn))
			{
				_conn.Open();
				Assert.AreEqual(1, Convert.ToInt32(cmd.ExecuteScalar()));
			}
		}

 /// <summary>
 /// 执行查询语句，返回查询结果的第一行第一列
 /// </summary>
 /// <param name="sql">查询语句</param>
 /// <param name="parameters">查询语句所需要的参数</param>
 /// <returns></returns>
 public string ExecuteScalar(string sql)
 {
     using (SqliteConnection connection = new SqliteConnection(dbConnectionString))
     {
         connection.Open();
         using (SqliteCommand command = new SqliteCommand(sql, connection))
         {
             object value = command.ExecuteScalar();
             return value != null ? value.ToString() : "";
         }
     }
 }

        /// <summary>
        /// Returns the current sequence value for the given Sequence from the DB.
        /// </summary>
        /// <param name="ASequenceName">Name of the Sequence.</param>
        /// <param name="ATransaction">An instantiated Transaction in which the Query
        /// to the DB will be enlisted.</param>
        /// <param name="ADatabase">Database object that can be used for querying.</param>
        /// <returns>Sequence Value.</returns>
        public System.Int64 GetCurrentSequenceValue(String ASequenceName, TDBTransaction ATransaction, TDataBase ADatabase)
        {
            string stmt = "SELECT MAX(sequence) FROM " + ASequenceName + ";";

            using (SqliteCommand cmd = new SqliteCommand(stmt, (SqliteConnection)ATransaction.Connection))
            {
                return Convert.ToInt64(cmd.ExecuteScalar());
            }
        }