C# SqliteCommand.Dispose方法代码示例

		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.");
		}

//.........这里部分代码省略.........

			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){
					foreach (DataColumn myColumn in myTable.Columns){
						Console.WriteLine(" " + myRow[myColumn]);
					}
				}
			}

			/*Console.WriteLine("read and display data using DataAdapter/DataTable...");
			DataTable dt = new DataTable();
			adapter.Fill(dt);
			DataView dv = new DataView(dt);
			foreach (DataRowView myRow in dv) {
				foreach (DataColumn myColumn in myRow.Row.Table.Columns) {
					Console.WriteLine(" " + myRow[myColumn.ColumnName]);
				}
			}*/
       		       		            
			try {
				dbcmd.CommandText = "SELECT NDESC INVALID SYNTAX FROM MONO_TEST WHERE NID=2";
				dbcmd.ExecuteNonQuery();
				Console.WriteLine("Should not reach here.");
			} catch (Exception e) {
				Console.WriteLine("Testing a syntax error: " + e.GetType().Name + ": " + e.Message);
			}

			/*try {
				dbcmd.CommandText = "SELECT 0/0 FROM MONO_TEST WHERE NID=2";
				Console.WriteLine("Should not reach here: " + dbcmd.ExecuteScalar());
			} catch (Exception e) {
				Console.WriteLine("Testing an execution error: " + e.GetType().Name + ": " + e.Message);
			}*/

			dataset.Dispose();
			adapter.Dispose();
			reader.Close();
			dbcmd.Dispose();
			dbcon.Close();
		}

 protected void CloseCommand(SqliteCommand cmd)
 {
     cmd.Connection.Close();
     cmd.Connection.Dispose();
     cmd.Dispose();
 }

        private void DoExpire()
        {
            SqliteCommand cmd = new SqliteCommand("delete from tokens where validity < datetime('now', 'localtime')");
            ExecuteNonQuery(cmd, m_Connection);

            cmd.Dispose();

            m_LastExpire = System.Environment.TickCount;
        }

        public bool CheckToken(UUID principalID, string token, int lifetime)
        {
            if (System.Environment.TickCount - m_LastExpire > 30000)
                DoExpire();

            SqliteCommand cmd = new SqliteCommand("update tokens set validity = datetime('now, 'localtime', '+" + lifetime.ToString() + 
                " minutes') where UUID = '" + principalID.ToString() + "' and token = '" + token + "' and validity > datetime('now', 'localtime')");

            if (ExecuteNonQuery(cmd, m_Connection) > 0)
            {
                cmd.Dispose();
                return true;
            }

            cmd.Dispose();

            return false;
        }

        public bool SetToken(UUID principalID, string token, int lifetime)
        {
            if (System.Environment.TickCount - m_LastExpire > 30000)
                DoExpire();

            SqliteCommand cmd = new SqliteCommand("insert into tokens (UUID, token, validity) values ('" + principalID.ToString() + 
                "', '" + token + "', datetime('now', 'localtime', '+" + lifetime.ToString() + " minutes'))");

            if (ExecuteNonQuery(cmd, m_Connection) > 0)
            {
                cmd.Dispose();
                return true;
            }

            cmd.Dispose();
            return false;
        }

        private void SetupDb()
        {
            SqliteCommand command = new SqliteCommand ();
            command.Connection = conn;
            command.CommandText =
             "CREATE TABLE songs (						    " +
                        "       id		    INTEGER PRIMARY KEY NOT NULL,   	" +
                        "       filename	    STRING  NOT NULL,		    	" +
                        "       title		    STRING  NOT NULL,               " +
                        "       artists		    STRING  NOT NULL,               " +
                        "       performers	    STRING  NOT NULL,               " +
                        "       album		    STRING  NOT NULL,               " +
                        "       tracknumber	    INTEGER NOT NULL,               " +
                        "       year		    INTEGER NOT NULL,               " +
                        "       duration	    INTEGER NOT NULL,               " +
                        "       mtime		    INTEGER NOT NULL,               " +
                        "       gain		    FLOAT   NOT NULL                " +
                        ")";
            command.ExecuteNonQuery ();
            command.Dispose ();

            command = new SqliteCommand ();
            command.Connection = conn;
            command.CommandText =
             "CREATE TABLE albums (						    " +
                        "       id		    INTEGER PRIMARY KEY NOT NULL,	" +
                        "       name		    STRING  NOT NULL,           " +
                        "       artists		    STRING  NOT NULL,           " +
                        "       songs		    STRING  NOT NULL,           " +
                        "       performers	    STRING  NOT NULL,           " +
                        "       year		    INTEGER NOT NULL           " +
                        ")";
            command.ExecuteNonQuery ();
            command.Dispose ();
        }