C# SQLiteConnection.ExecuteReader方法代码示例

		public void TypeMapping()
		{
			using (SQLiteConnection conn = new SQLiteConnection(m_csb.ConnectionString))
			{
				conn.Open();
				conn.Execute(@"create table Test (
Id integer primary key,
String text,
Int32 int not null,
NullableInt32 int,
Int64 integer not null,
NullableInt64 integer,
Double double not null,
NullableDouble double,
Bool bool not null,
NullableBool bool
);");
				conn.Execute(@"insert into Test (Id, String, Int32, NullableInt32, Int64, NullableInt64, Double, NullableDouble, Bool, NullableBool)
values(1, 'two', 3, 4, 5, 6, 7.8910, 11.121314, 1, 0);");

				using (var reader = conn.ExecuteReader(@"select * from Test"))
				{
					Assert.IsTrue(reader.Read());
					object[] values = new object[10];
					Assert.AreEqual(10, reader.GetValues(values));
					Assert.AreEqual(1L, (long) values[0]);
					Assert.AreEqual("two", (string) values[1]);
					Assert.AreEqual(3, (int) values[2]);
					Assert.AreEqual(4, (int) values[3]);
					Assert.AreEqual(5L, (long) values[4]);
					Assert.AreEqual(6L, (long) values[5]);
					Assert.AreEqual(7.8910d, (double) values[6]);
					Assert.AreEqual(11.121314d, (double) values[7]);
					Assert.AreEqual(true, (bool) values[8]);
					Assert.AreEqual(false, (bool) values[9]);
				}
			}
		}

		public void TypeConversion(string columnType, object value, params object[] typesAndValues)
		{
			using (SQLiteConnection conn = new SQLiteConnection(m_csb.ConnectionString))
			{
				conn.Open();
				conn.Execute(@"create table Test (Id integer primary key autoincrement, Value {0});".FormatInvariant(columnType));
				conn.Execute(@"insert into Test (Value) values(@value);", new { value });

				// test that GetValue returns the right value
				using (var reader = conn.ExecuteReader(@"select Value from Test"))
				{
					Assert.IsTrue(reader.Read());
					object actual = reader.GetValue(0);
					Assert.AreEqual(value ?? DBNull.Value, actual);
				}

				// test that each specified GetX method returns the right value
				foreach (var typeAndValue in GetTypesAndValues(typesAndValues))
				{
					using (var reader = conn.ExecuteReader(@"select Value from Test"))
					{
						Assert.IsTrue(reader.Read());
						var methodInfo = reader.GetType().GetMethod("Get" + typeAndValue.Key);
						object actual = methodInfo.Invoke(reader, new object[] { 0 });
						object expected = typeAndValue.Value ?? DBNull.Value;
						if (expected == DBNull.Value)
						{
							Assert.IsNull(actual);
						}
						else
						{
							Assert.AreEqual(expected, actual);
							Assert.AreEqual(expected.GetType(), actual.GetType());
						}
					}
				}

				// test that all other GetX methods throw
				foreach (var type in s_availableTypes.Except(GetTypesAndValues(typesAndValues).Select(x => x.Key)))
				{
					using (var reader = conn.ExecuteReader(@"select Value from Test"))
					{
						Assert.IsTrue(reader.Read());
						var methodInfo = reader.GetType().GetMethod("Get" + type);
						try
						{
							methodInfo.Invoke(reader, new object[] { 0 });
							Assert.Fail("No exception thrown for {0}".FormatInvariant(type));
						}
						catch (TargetInvocationException ex)
						{
							Assert.IsTrue(new[] { typeof(InvalidCastException), typeof(FormatException), typeof(OverflowException) }.Contains(ex.InnerException.GetType()));
						}
					}
				}
			}
		}

		public void IndexedParameters()
		{
			using (SQLiteConnection conn = new SQLiteConnection(m_csb.ConnectionString))
			{
				conn.Open();
				conn.Execute(@"create table Test (Id integer primary key, String text);");
				using (var cmd = new SQLiteCommand(@"insert into Test(Id, String) values(?, ?)", conn))
				{
					cmd.Parameters.Add(new SQLiteParameter { DbType = DbType.Int32, Value = 1 });
					cmd.Parameters.Add(new SQLiteParameter { DbType = DbType.String, Value = "test" });
					cmd.ExecuteNonQuery();
				}
				using (var reader = conn.ExecuteReader(@"select String from Test where Id = 1"))
				{
					Assert.IsTrue(reader.Read());
					Assert.AreEqual("test", reader.GetString(0));
				}
			}
		}

		public void BackUpDatabase()
		{
			using (SQLiteConnection disk = new SQLiteConnection(m_csb.ConnectionString))
			using (SQLiteConnection memory = new SQLiteConnection("Data Source=:memory:"))
			{
				disk.Open();
				disk.Execute(@"create table Test (Id integer primary key, String text); insert into Test(Id, String) values(1, 'one'), (2, 'two'), (3, 'three');");

				memory.Open();
				disk.BackupDatabase(memory, "main", "main", -1, null, 0);

				using (var reader = memory.ExecuteReader("select Id from Test where length(String) = @len", new { len = 3 }))
				{
					var results = reader.ReadAll<long>().ToList();
					Assert.That(results, Is.EqualTo(new long[] { 1, 2 }));
				}
			}
		}

		public void NamedParameterStringValue(string value)
		{
			using (SQLiteConnection conn = new SQLiteConnection(m_csb.ConnectionString))
			{
				conn.Open();
				conn.Execute(@"create table Test (Id integer primary key, String text);");
				using (var cmd = new SQLiteCommand(@"insert into Test(Id, String) values(1, @value)", conn))
				{
					var param = cmd.Parameters.Add("value", DbType.String);
					param.Value = value;
					cmd.ExecuteNonQuery();
				}
				using (var reader = conn.ExecuteReader(@"select String from Test where Id = 1"))
				{
					Assert.IsTrue(reader.Read());
					Assert.AreEqual(value, reader.GetString(0));
				}
			}
		}