C# SqliteCommand.ExecuteReader方法代码示例

        protected IDataReader ExecuteReader(SqliteCommand cmd)
        {
            SqliteConnection newConnection =
                    (SqliteConnection)((ICloneable)m_Connection).Clone();
            newConnection.Open();

            cmd.Connection = newConnection;
            return cmd.ExecuteReader();
        }

		public void InsertRandomValuesWithParameter()
		{
			SqliteParameter textP = new SqliteParameter();
			textP.ParameterName = "textP";
			textP.SourceColumn = "t";
		
			SqliteParameter floatP = new SqliteParameter();
			floatP.ParameterName = "floatP";
			floatP.SourceColumn = "nu";
		
			SqliteParameter integerP = new SqliteParameter();
			integerP.ParameterName ="integerP";
			integerP.SourceColumn = "i";

			SqliteParameter blobP = new SqliteParameter();
			blobP.ParameterName = "blobP";
			blobP.SourceColumn = "b";

			Random random = new Random();
			StringBuilder builder = new StringBuilder();
			for (int k=0; k < random.Next(0,100); k++)
			{
				builder.Append((char)random.Next(65536));
			}
			
			SqliteCommand insertCmd = new SqliteCommand("DELETE FROM t1; INSERT INTO t1  (t, f, i, b ) VALUES(:textP,:floatP,:integerP,:blobP)",_conn);
			
			insertCmd.Parameters.Add(textP);
			insertCmd.Parameters.Add(floatP);
			insertCmd.Parameters.Add(blobP);
			insertCmd.Parameters.Add(integerP);
			
			textP.Value=builder.ToString();
			floatP.Value=Convert.ToInt64(random.Next(999));
			integerP.Value=random.Next(999);
			blobP.Value=System.Text.Encoding.UTF8.GetBytes("\u05D0\u05D1\u05D2" + builder.ToString());
			
			SqliteCommand selectCmd = new SqliteCommand("SELECT * from t1", _conn);

			using(_conn)
			{
				_conn.Open();
				int res = insertCmd.ExecuteNonQuery();
				Assert.AreEqual(res,1);
				
				using (IDataReader reader = selectCmd.ExecuteReader()) {
					Assert.AreEqual(reader.Read(), true);
					Assert.AreEqual(reader["t"], textP.Value);
					Assert.AreEqual(reader["f"], floatP.Value);
					Assert.AreEqual(reader["i"], integerP.Value);
					Assert.AreEqual(reader["b"], blobP.Value);
					Assert.AreEqual(reader.Read(), false);
				}
			}
		}

        protected IDataReader ExecuteReader(SqliteCommand cmd, SqliteConnection connection)
        {
            lock (connection)
            {
                SqliteConnection newConnection =
                        (SqliteConnection)((ICloneable)connection).Clone();
                newConnection.Open();

                cmd.Connection = newConnection;
                //Console.WriteLine("XXX " + cmd.CommandText);

                return cmd.ExecuteReader();
            }
        }

		public void Select()
		{
			SqliteCommand simpleSelect = new SqliteCommand("SELECT * FROM t1;  ", _conn); // check trailing spaces
			using(_conn)
			{
				_conn.Open();
				SqliteDataReader dr = simpleSelect.ExecuteReader();
				while(dr.Read())
				{
					string test = dr[0].ToString();
					Assert.AreEqual(dr["T"], stringvalue); // also checks case-insensitive column
					Assert.AreEqual(dr["F"], 123);
					Assert.AreEqual(dr["I"], 123);
					Assert.AreEqual(dr["B"], "123");
				}
				Assert.IsTrue(dr.FieldCount>0);
			}
		}

        public string Get(string scope, string key)
        {
            string command = "SELECT `value` FROM `generic` WHERE `key` = @key";
            if (!String.IsNullOrEmpty(scope))
                command += " AND `scope` = @scope";

            lock (this)
            {
                using (SqliteCommand cmd = new SqliteCommand(command, m_Connection))
                {
                    cmd.Parameters.Add("@key", key);
                    if (!String.IsNullOrEmpty(scope))
                        cmd.Parameters.Add("@scope", scope);

                    using (IDataReader result = cmd.ExecuteReader())
                    {
                        if (result.Read())
                            return result.GetString(0);
                        else
                            return null;
                    }
                }
            }
        }

//        /// <summary>
//        /// Some... logging functionnality
//        /// </summary>
//        /// <param name="asset"></param>
//        private static void LogAssetLoad(AssetBase asset)
//        {
//            string temporary = asset.Temporary ? "Temporary" : "Stored";
//            string local = asset.Local ? "Local" : "Remote";
//
//            int assetLength = (asset.Data != null) ? asset.Data.Length : 0;
//
//            m_log.Debug("[ASSET DB]: " +
//                                     string.Format("Loaded {5} {4} Asset: [{0}][{3}] \"{1}\":{2} ({6} bytes)",
//                                                   asset.FullID, asset.Name, asset.Description, asset.Type,
//                                                   temporary, local, assetLength));
//        }

        /// <summary>
        /// Check if an asset exist in database
        /// </summary>
        /// <param name="uuid">The asset UUID</param>
        /// <returns>True if exist, or false.</returns>
        override public bool ExistsAsset(UUID uuid)
        {
            lock (this) {
                using (SqliteCommand cmd = new SqliteCommand(SelectAssetSQL, m_conn))
                {
                    cmd.Parameters.Add(new SqliteParameter(":UUID", uuid.ToString()));
                    using (IDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            reader.Close();
                            return true;
                        }
                        else
                        {
                            reader.Close();
                            return false;
                        }
                    }
                }
            }
        }

        // ============================================
        // PUBLIC Methods
        // ============================================
        /// Return True if Specified Table Exists
        public bool TableExists(string table)
        {
            string sql = "SELECT name FROM sqlite_master WHERE type='table' AND [email protected];";
            SqliteCommand sqlCmd = new SqliteCommand(sql, this);
            sqlCmd.Parameters.Add("@Table", table);
            SqliteDataReader sqlReader = sqlCmd.ExecuteReader();

            bool tableExists = false;
            if (sqlReader.Read())
                tableExists = true;

            sqlReader.Close();
            return(tableExists);
        }

		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>
 /// Fetches the entry at the contentId specified, and returns a ContentEntry
 /// that is the row.
 /// </summary>
 /// <param name="contentId">The contentId to fetch.</param>
 /// <param name="IncludeImageData">Should image data be included?</param>
 /// <returns>A ContentEntry that is the row, or null if it wasn't found.</returns>
 public ContentEntry GetEntry(uint contentId, bool IncludeImageData)
 {
     SqliteCommand cmd = new SqliteCommand("SELECT * FROM [content] WHERE [contentId] = @cid LIMIT 1", sqlite);
     cmd.Parameters.Add(new SqliteParameter("@cid", (int)contentId));
     sqlite.Open();
     ContentEntry entry;
     SqliteDataReader reader = cmd.ExecuteReader();
     reader.Read();
     entry = this.GetEntryFromReader(reader, IncludeImageData);
     sqlite.Close();
     return entry;
 }

        /// <summary>
        /// Checks if a record doesn't appear in the contentList table.
        /// </summary>
        /// <param name="contentId">The ContentID to check.</param>
        /// <returns>true if the record is orphaned.</returns>
        private bool CheckIfRecordIsOrphaned(uint contentId)
        {
            SqliteCommand query = new SqliteCommand(@"SELECT [contentId] FROM [contentlist] WHERE [contentId][email protected]", sqlite);
            query.Parameters.Add(new SqliteParameter("@cid", (int)contentId));
            SqliteDataReader r = query.ExecuteReader();

            uint count = 0;
            while (r.Read())
            {
                count++;
            }

            return count == 0;
        }

 private bool ExistsFirstLastName(String fname, String lname)
 {
     string FindUser = "select * from users where (username=:username and surname=:surname)";
     using (SqliteCommand cmd = new SqliteCommand(FindUser, g_conn))
     {
         cmd.Parameters.Add(new SqliteParameter(":username", fname));
         cmd.Parameters.Add(new SqliteParameter(":surname", lname));
         try
         {
             using (IDataReader reader = cmd.ExecuteReader())
             {
                 if (reader.Read())
                 {
                     reader.Close();
                     return true;
                 }
                 else
                 {
                     reader.Close();
                     return false;
                 }
             }
         }
         catch (Exception ex)
         {
             m_log.Error("[USER DB]: Exception searching for user's first and last name: " + ex.ToString());
             return false;
         }
     }
 }

        private EstateSettings DoLoad(SqliteCommand cmd, UUID regionID, bool create)
        {
            EstateSettings es = new EstateSettings();
            es.OnSave += StoreEstateSettings;

            IDataReader r = cmd.ExecuteReader();

            if (r.Read())
            {
                foreach (string name in FieldList)
                {
                    if (m_FieldMap[name].GetValue(es) is bool)
                    {
                        int v = Convert.ToInt32(r[name]);
                        if (v != 0)
                            m_FieldMap[name].SetValue(es, true);
                        else
                            m_FieldMap[name].SetValue(es, false);
                    }
                    else if (m_FieldMap[name].GetValue(es) is UUID)
                    {
                        UUID uuid = UUID.Zero;

                        UUID.TryParse(r[name].ToString(), out uuid);
                        m_FieldMap[name].SetValue(es, uuid);
                    }
                    else
                    {
                        m_FieldMap[name].SetValue(es, Convert.ChangeType(r[name], m_FieldMap[name].FieldType));
                    }
                }
                r.Close();
            }
            else if (create)
            {
                r.Close();

                List<string> names = new List<string>(FieldList);

                names.Remove("EstateID");

                string sql = "insert into estate_settings ("+String.Join(",", names.ToArray())+") values ( :"+String.Join(", :", names.ToArray())+")";

                cmd.CommandText = sql;
                cmd.Parameters.Clear();

                foreach (string name in FieldList)
                {
                    if (m_FieldMap[name].GetValue(es) is bool)
                    {
                        if ((bool)m_FieldMap[name].GetValue(es))
                            cmd.Parameters.Add(":"+name, "1");
                        else
                            cmd.Parameters.Add(":"+name, "0");
                    }
                    else
                    {
                        cmd.Parameters.Add(":"+name, m_FieldMap[name].GetValue(es).ToString());
                    }
                }

                cmd.ExecuteNonQuery();

                cmd.CommandText = "select LAST_INSERT_ROWID() as id";
                cmd.Parameters.Clear();

                r = cmd.ExecuteReader();

                r.Read();

                es.EstateID = Convert.ToUInt32(r["id"]);

                r.Close();

                cmd.CommandText = "insert into estate_map values (:RegionID, :EstateID)";
                cmd.Parameters.Add(":RegionID", regionID.ToString());
                cmd.Parameters.Add(":EstateID", es.EstateID.ToString());

                // This will throw on dupe key
                try
                {
                    cmd.ExecuteNonQuery();
                }
                catch (Exception)
                {
                }

                es.Save();
            }

            LoadBanList(es);

            es.EstateManagers = LoadUUIDList(es.EstateID, "estate_managers");
            es.EstateAccess = LoadUUIDList(es.EstateID, "estate_users");
            es.EstateGroups = LoadUUIDList(es.EstateID, "estate_groups");
            return es;
        }

        /// <summary>
        /// Get (fetch?) the friendlist for a user
        /// </summary>
        /// <param name="friendlistowner">UUID of the friendlist owner</param>
        /// <returns>The friendlist list</returns>
        override public List<FriendListItem> GetUserFriendList(UUID friendlistowner)
        {
            List<FriendListItem> returnlist = new List<FriendListItem>();

            using (SqliteCommand cmd = new SqliteCommand(SelectFriendsByUUID, g_conn))
            {
                cmd.Parameters.Add(new SqliteParameter(":ownerID", friendlistowner.ToString()));

                try
                {
                    using (IDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            FriendListItem user = new FriendListItem();
                            user.FriendListOwner = friendlistowner;
                            user.Friend = new UUID((string)reader[0]);
                            user.FriendPerms = Convert.ToUInt32(reader[1]);
                            user.FriendListOwnerPerms = Convert.ToUInt32(reader[2]);
                            returnlist.Add(user);
                        }
                        reader.Close();
                    }
                }
                catch (Exception ex)
                {
                    m_log.Error("[USER DB]: Exception getting friends list for user: " + ex.ToString());
                }
            }

            return returnlist;
        }

		static void Main(string[] args)
		{
			Console.WriteLine("If this test works, you should get:");
			Console.WriteLine("Data 1: 5");
			Console.WriteLine("Data 2: Mono");

			Console.WriteLine("create SqliteConnection...");
			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";
			Console.WriteLine("setting ConnectionString using: " + 
				connectionString);
			dbcon.ConnectionString = connectionString;
				
			Console.WriteLine("open the connection...");
			dbcon.Open();

			Console.WriteLine("create SqliteCommand to CREATE TABLE MONO_TEST");
			SqliteCommand dbcmd = new SqliteCommand();
			dbcmd.Connection = dbcon;
			
			dbcmd.CommandText = 
				"CREATE TABLE MONO_TEST ( " +
				"NID INT, " +
				"NDESC TEXT )";
			Console.WriteLine("execute command...");
			dbcmd.ExecuteNonQuery();

			Console.WriteLine("set and execute command to INSERT INTO MONO_TEST");
			dbcmd.CommandText =
				"INSERT INTO MONO_TEST  " +
				"(NID, NDESC )"+
				"VALUES(5,'Mono')";
			dbcmd.ExecuteNonQuery();

			Console.WriteLine("set command to SELECT FROM MONO_TEST");
			dbcmd.CommandText =
				"SELECT * FROM MONO_TEST";
			SqliteDataReader reader;
			Console.WriteLine("execute reader...");
			reader = dbcmd.ExecuteReader();

			Console.WriteLine("read and display data...");
			while(reader.Read()) {
				Console.WriteLine("Data 1: " + reader[0].ToString());
				Console.WriteLine("Data 2: " + reader[1].ToString());
			}

			Console.WriteLine("read and display data using DataAdapter...");
			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){
					foreach (DataColumn myColumn in myTable.Columns){
						Console.WriteLine(myRow[myColumn]);
					}
				}
			}

			
			Console.WriteLine("clean up...");
			dataset.Dispose();
			adapter.Dispose();
			reader.Close();
			dbcmd.Dispose();
			dbcon.Close();

			Console.WriteLine("Done.");
		}

        public bool Convert()
        {
            try
            {
                SqliteConnection conn = new SqliteConnection(m_assetConnectionString);
                conn.Open();

                Assembly assem = GetType().Assembly;
                Migration m = new Migration(conn, assem, "AssetStore");

                if (m.Version == 0)
                {
                    //fetch all assets with mediaurl and construct RexAssetData objects
                    List<RexAssetData> rexAssets = new List<RexAssetData>();

                    using (SqliteCommand cmd = new SqliteCommand(assetSelect, conn))
                    {
                        using (IDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                if (((String)reader["MediaURL"]) != "")
                                {
                                    UUID id = new UUID((String) reader["UUID"]);
                                    string mediaUrl = (String)reader["MediaURL"];
                                    byte refreshRate = 0;
                                    object refRate = reader["RefreshRate"];
                                    if (refRate is byte)
                                    {
                                        refreshRate = (byte)refRate;
                                    }
                                    RexAssetData data = new RexAssetData(id, mediaUrl,refreshRate);
                                    rexAssets.Add(data);
                                }
                            }
                        }
                    }
                    conn.Close();

                    //Now add them to ModreX database
                    NHibernateRexAssetData rexAssetManager = new NHibernateRexAssetData();
                    rexAssetManager.Initialise(m_rexConnectionString);
                    foreach (RexAssetData data in rexAssets)
                    {
                        rexAssetManager.StoreObject(data);
                    }

                    //finally remove realXtend properties and update version number
                    conn.Open();
                    //TODO: remove realXtend properties
                    // this is not done yet because SQLite is missing drop column feature
                    m.Version = 1;
                }

                conn.Close();
                return true;
            }
            catch (Exception e)
            {
                m_log.ErrorFormat("[AssetStore] Migration failed. Reason: {0}", e);
                return false;
            }
        }