C# SqliteCommand.ExecuteScalar方法代码示例

        /// <summary>
        /// Creates a new connection to the icontent.cache file.
        /// </summary>
        /// <param name="filename">The file to connect to.</param>
        public IGADatabaseConnector(String filename)
        {
            try {
                sqlite = new SqliteConnection("URI=file:" + filename + ",version=3");
                sqlite.Open();

                SqliteCommand query = new SqliteCommand("SELECT [appId] FROM [contentlist] LIMIT 1", sqlite);
                Object result = query.ExecuteScalar();
                sqlite.Close();
                if (result == null)
                {
                    this._appID = 0;
                }
                else
                {
                    this._appID = (int)result;
                }

                if (this._appID > 0) {
                    this._appSupported = Common.AppInfos.ContainsKey(this._appID);
                } else {
                    this._appSupported = false;
                }

                if (this._appSupported)
                {
                    _appInfo = Common.AppInfos[this._appID];
                }

            } catch (Exception) {
                throw new DatabaseConnectionFailureException();
            }
        }

 public new object ExecuteScalar(string sql, string connectionString)
 {
     using (SqliteConnection conn = new SqliteConnection(connectionString))
     {
         SqliteCommand cmd = new SqliteCommand(sql, conn);
         cmd.CommandTimeout = 1200;
         conn.Open();
         object retVal = cmd.ExecuteScalar();
         conn.Close();
         return retVal;
     }
 }

		static void Test(bool v3, string encoding) {
			if (!v3)
				Console.WriteLine("Testing Version 2" + (encoding != null ? " with " + encoding + " encoding" : ""));
			else
				Console.WriteLine("Testing Version 3");
				
			System.IO.File.Delete("SqliteTest.db");
		
			SqliteConnection dbcon = new SqliteConnection();
			
			// the connection string is a URL that points
			// to a file.  If the file does not exist, a 
			// file is created.

			// "URI=file:some/path"
			string connectionString =
				"URI=file:SqliteTest.db";
			if (v3)
				connectionString += ",Version=3";
			if (encoding != null)
				connectionString += ",encoding=" + encoding;
			dbcon.ConnectionString = connectionString;
				
			dbcon.Open();

			SqliteCommand dbcmd = new SqliteCommand();
			dbcmd.Connection = dbcon;
			
			dbcmd.CommandText = 
				"CREATE TABLE MONO_TEST ( " +
				"NID INT, " +
				"NDESC TEXT, " +
				"NTIME DATETIME); " +
				"INSERT INTO MONO_TEST  " +
				"(NID, NDESC, NTIME) " +
				"VALUES(1,'One (unicode test: \u05D0)', '2006-01-01')";
			Console.WriteLine("Create & insert modified rows = 1: " + dbcmd.ExecuteNonQuery());

			dbcmd.CommandText =
				"INSERT INTO MONO_TEST  " +
				"(NID, NDESC, NTIME) " +
				"VALUES(:NID,:NDESC,:NTIME)";
			dbcmd.Parameters.Add( new SqliteParameter("NID", 2) );
			dbcmd.Parameters.Add( new SqliteParameter(":NDESC", "Two (unicode test: \u05D1)") );
			dbcmd.Parameters.Add( new SqliteParameter(":NTIME", DateTime.Now) );
			Console.WriteLine("Insert modified rows with parameters = 1, 2: " + dbcmd.ExecuteNonQuery() + " , " + dbcmd.LastInsertRowID());

			dbcmd.CommandText =
				"INSERT INTO MONO_TEST  " +
				"(NID, NDESC, NTIME) " +
				"VALUES(3,'Three, quoted parameter test, and next is null; :NTIME', NULL)";
			Console.WriteLine("Insert with null modified rows and ID = 1, 3: " + dbcmd.ExecuteNonQuery() + " , " + dbcmd.LastInsertRowID());

			dbcmd.CommandText =
				"INSERT INTO MONO_TEST  " +
				"(NID, NDESC, NTIME) " +
				"VALUES(4,'Four with ANSI char: ü', NULL)";
			Console.WriteLine("Insert with ANSI char ü = 1, 4: " + dbcmd.ExecuteNonQuery() + " , " + dbcmd.LastInsertRowID());

			dbcmd.CommandText =
				"INSERT INTO MONO_TEST  " +
				"(NID, NDESC, NTIME) " +
				"VALUES(?,?,?)";
			dbcmd.Parameters.Clear();
			IDbDataParameter param1 = dbcmd.CreateParameter();
			param1.DbType = DbType.DateTime;
			param1.Value = 5;
			dbcmd.Parameters.Add(param1);			
			IDbDataParameter param2 = dbcmd.CreateParameter();
			param2.Value = "Using unnamed parameters";
			dbcmd.Parameters.Add(param2);
			IDbDataParameter param3 = dbcmd.CreateParameter();
			param3.DbType = DbType.DateTime;
			param3.Value = DateTime.Parse("2006-05-11 11:45:00");
			dbcmd.Parameters.Add(param3);
			Console.WriteLine("Insert with unnamed parameters = 1, 5: " + dbcmd.ExecuteNonQuery() + " , " + dbcmd.LastInsertRowID());

			dbcmd.CommandText =
				"SELECT * FROM MONO_TEST";
			SqliteDataReader reader;
			reader = dbcmd.ExecuteReader();

			Console.WriteLine("read and display data...");
			while(reader.Read())
				for (int i = 0; i < reader.FieldCount; i++)
					Console.WriteLine(" Col {0}: {1} (type: {2}, data type: {3})",
						i, reader[i] == null ? "(null)" : reader[i].ToString(), reader[i] == null ? "(null)" : reader[i].GetType().FullName, reader.GetDataTypeName(i));

			dbcmd.CommandText = "SELECT NDESC FROM MONO_TEST WHERE NID=2";
			Console.WriteLine("read and display a scalar = 'Two': " + dbcmd.ExecuteScalar());

			dbcmd.CommandText = "SELECT count(*) FROM MONO_TEST";
			Console.WriteLine("read and display a non-column scalar = 3: " + dbcmd.ExecuteScalar());

			Console.WriteLine("read and display data using DataAdapter/DataSet...");
			SqliteDataAdapter adapter = new SqliteDataAdapter("SELECT * FROM MONO_TEST", connectionString);
			DataSet dataset = new DataSet();
			adapter.Fill(dataset);
			foreach(DataTable myTable in dataset.Tables){
				foreach(DataRow myRow in myTable.Rows){
//.........这里部分代码省略.........

        /// <summary>
        /// Creates a new entry in the cache file.
        /// </summary>
        /// <param name="entry">The entry to create.</param>
        /// <returns>The new entry's contentId.</returns>
        public uint NewEntry(ContentEntry entry)
        {
            uint active = 1;
            if (!entry.Active)
            {
                active = 0;
            }
            uint activate = (uint)((TimeSpan)entry.Activate.Subtract(new DateTime(1970, 1, 1, 0, 0, 0, 0))).TotalSeconds;
            uint expiry = 0;
            if (entry.Expires)
            {
                expiry = (uint)((TimeSpan)entry.Expiry.Subtract(new DateTime(1970, 1, 1, 0, 0, 0, 0))).TotalSeconds;
            }

            String p = entry.GetPropsAsString();

            SqliteCommand query = new SqliteCommand(@"INSERT INTO [content] ([active], [activate], [expire], [dayparts], [contentType], [descriptor], [size], [viewcount], [viewlimit], [displayafter], [props], [data]) VALUES (@active, @activate, @expire, @dayparts, @contentType, @descriptor, @size, @viewcount, @viewlimit, @displayafter, @props, @data); SELECT last_insert_rowid() AS contentId;", sqlite);
            query.Parameters.Add(new SqliteParameter("@active", (int)active));
            query.Parameters.Add(new SqliteParameter("@activate", (int)activate));
            query.Parameters.Add(new SqliteParameter("@expire", (int)expiry));
            query.Parameters.Add(new SqliteParameter("@dayparts", (int)entry.DayParts));
            query.Parameters.Add(new SqliteParameter("@contentType", (int)entry.contentType.contentType));
            query.Parameters.Add(new SqliteParameter("@descriptor", entry.Descriptor));
            query.Parameters.Add(new SqliteParameter("@size", (int)entry.Size));
            query.Parameters.Add(new SqliteParameter("@viewcount", (int)entry.ViewCount));
            query.Parameters.Add(new SqliteParameter("@viewlimit", (int)entry.ViewLimit));
            query.Parameters.Add(new SqliteParameter("@displayafter", (int)entry.DisplayAfter));
            query.Parameters.Add(new SqliteParameter("@props", p));
            query.Parameters.Add(new SqliteParameter("@data", entry.Data));
            // get the contentId back
            sqlite.Open();
            Object cidR = query.ExecuteScalar();
            uint cid = (uint)((long)cidR);

            // shove cid into props
            p = "contentId=" + cid.ToString() + "&" + p;

            // reset connection
            sqlite.Close();
            sqlite.Open();

            // shove in updated props
            query = new SqliteCommand(@"UPDATE [content] SET [props][email protected] WHERE [contentId][email protected]", sqlite);
            query.Parameters.Add(new SqliteParameter("@props", p));
            query.Parameters.Add(new SqliteParameter("@cid", (int)cid));
            if (query.ExecuteNonQuery() == 0)
            {
                sqlite.Close();
                throw new DatabaseUpdateFailureException();
            }

            // reset connection
            sqlite.Close();
            sqlite.Open();

            // shove in corresponding contentlist entry for record
            query = new SqliteCommand(@"INSERT INTO [contentlist] ([appId], [contentType], [contentId], [userInfo], [zoneId]) VALUES (@appid, @contentType, @cid, @userinfo, @zoneid)", sqlite);
            query.Parameters.Add(new SqliteParameter("@appid", this._appID));
            query.Parameters.Add(new SqliteParameter("@contentType", (int)entry.contentType.contentType));
            query.Parameters.Add(new SqliteParameter("@cid", (int)cid));
            query.Parameters.Add(new SqliteParameter("@zoneid", entry.Properties["zoneId"]));
            query.Parameters.Add(new SqliteParameter("@userinfo", entry.Properties["userInfo"]));

            if (query.ExecuteNonQuery() == 0)
            {
                sqlite.Close();
                throw new DatabaseUpdateFailureException();
            }
            sqlite.Close();

            return cid;
        }

 /// <summary>
 /// Exports a single image from the database.
 /// </summary>
 /// <param name="contentId">The contentId to export.</param>
 /// <returns>A byte[] containing the raw image or video data, or null if there was an error or there is no data.</returns>
 public byte[] ExportImage(uint contentId)
 {
     SqliteCommand cmd = new SqliteCommand(@"SELECT [data] FROM [content] WHERE [contentId] = @cid LIMIT 1", sqlite);
     cmd.Parameters.Add(new SqliteParameter("@cid", (int)contentId));
     sqlite.Open();
     byte[] idata = null;
     try
     {
         idata = (byte[])cmd.ExecuteScalar();
     }
     catch (Exception) { } // pass
     sqlite.Close();
     return idata;
 }

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

 private object ExecuteScalar(string sql, IDbConnection conn, IDbTransaction tran)
 {
     sql = sql + " --dataProfilerIgnore";
     SqliteCommand cmd = new SqliteCommand(sql, (SqliteConnection)conn, tran);
     cmd.CommandTimeout = CommandTimeout;
     return cmd.ExecuteScalar();
 }