C# Query.ExecuteDataTable方法代码示例

		private void runButton_Click(object sender, EventArgs e)
		{
			#region Usage

			// Initialize custom table data object
			MyTableData myTableData = new MyTableData();
			myTableData.ColumnNames = new string[] {"ID", "FirstName", "LastName"};
			myTableData.ColumnTypes = new Type[] { typeof(int), typeof(string), typeof(string) };
			myTableData.Rows.Add(new object[] { 1, "Nancy", "Davolio" });
			myTableData.Rows.Add(new object[] { 2, "Andrew", "Fuller" });
			myTableData.Rows.Add(new object[] { 3, "Janet", "Leverling" });
			myTableData.Rows.Add(new object[] { 4, "Margaret", "Peacock" });
			myTableData.Rows.Add(new object[] { 5, "Steven", "Buchanan" });
			myTableData.Rows.Add(new object[] { 6, "Michael", "Suyama" });
			myTableData.Rows.Add(new object[] { 7, "Robert", "King" });
			myTableData.Rows.Add(new object[] { 8, "Laura", "Callahan" });
			myTableData.Rows.Add(new object[] { 9, "Anne", "Dodsworth" });

			// Create the custom table binding for the data object
			MyTableBinding myTableBinding = new MyTableBinding("Persons", myTableData);

			Query query = new Query();

			// Add the custom table binding to the data context.
			query.DataContext.Tables.Add(myTableBinding);

			// Run a query that sorts the persons.
			query.Text = "SELECT * FROM Persons";

			dataGridView1.DataSource = query.ExecuteDataTable();

			#endregion
		}

		public override bool Execute()
		{
			Query query = new Query();

			foreach (ITaskItem dataSetFile in _dataSetFiles)
			{
				try
				{
					DataSet dataSet = new DataSet();
					dataSet.ReadXml(dataSetFile.ItemSpec);
					query.DataContext.AddTablesAndRelations(dataSet);
				}
				catch (Exception ex)
				{
					Log.LogError("Cannot load DataSet {0}: {1}", dataSetFile.ItemSpec, ex.Message);				
				}
			}

			foreach (ITaskItem sourceFile in _sourceFiles)
			{
				try
				{
					XmlDocument xmlDocument = new XmlDocument();
					xmlDocument.Load(sourceFile.ItemSpec);
					xmlDocument.PreserveWhitespace = true;

					foreach (XmlNode sampleCodeNode in xmlDocument.SelectNodes("//sampleCode[@language='NQuery.SQL']"))
					{
						string sampleCode = sampleCodeNode.InnerText;

						query.Text = sampleCode;
						try
						{
							Log.LogMessage(MessageImportance.Normal, "Executing query '{0}'", sampleCode);
							DataTable result = query.ExecuteDataTable();
							XmlNode resultTable = ConvertDataTableToXml(xmlDocument, result);
							sampleCodeNode.ParentNode.InsertAfter(resultTable, sampleCodeNode);
						}
						catch (NQueryException ex)
						{
							LogQueryError(ex, sampleCodeNode, sourceFile.ItemSpec);
						}
					}

					xmlDocument.Save(sourceFile.ItemSpec);
				}
				catch (Exception ex)
				{
					Log.LogError("Cannot run sample code on file {0}: {1}", sourceFile.ItemSpec, ex.Message);
				}
			}

			return !Log.HasLoggedErrors;
		}

		private void delagateFunctionButton_Click(object sender, EventArgs e)
		{
			#region Delegate Function Usage
			
			Query query = new Query();

			// This adds the delegate 'MyFunctionDelegate' as a function to the data context.
			query.DataContext.Functions.Add("FunctionFromDelegate", new MyFunctionDelegate(MyFunctionDelegateImplementation));

			query.Text = "SELECT FunctionFromDelegate(42) AS Result";
			dataGridView1.DataSource = query.ExecuteDataTable();

			#endregion
		}

		private void staticFunctionContainerButton_Click(object sender, EventArgs e)
		{
			#region Static Function Container Usage

			Query query = new Query();

			// This adds all static functions of the type StaticFunctionContainer that are 
			// marked with the FunctionBinding attribute to the data context.
			query.DataContext.Functions.AddFromContainer(typeof(StaticFunctionContainer));

			query.Text = "SELECT StaticFunctionFromContainer() AS Result";
			dataGridView1.DataSource = query.ExecuteDataTable();
			
			#endregion
		}

		private void instanceFunctionContainerButton_Click(object sender, EventArgs e)
		{
			#region Instance Function Container Usage

			Query query = new Query();

			// This adds both all instance and static functions of containerInstance that
			// are marked with the FunctionBinding attribute to the data context.
			InstanceFunctionContainer containerInstance = new InstanceFunctionContainer(42);
			query.DataContext.Functions.AddFromContainer(containerInstance);

			query.Text = "SELECT InstanceFunctionFromContainer() AS Result";
			dataGridView1.DataSource = query.ExecuteDataTable();
			
			#endregion
		}

		private void runParameterWithCustomPropertiesButton_Click(object sender, EventArgs e)
		{
			#region Parameter with Custom Properties

			Query query = new Query();

			Dictionary<string, object> myDictionary = new Dictionary<string, object>();
			myDictionary["DateTimeProp"] = DateTime.Now;
			myDictionary["IntProp"] = 42;
			myDictionary["StringProp"] = Environment.UserName;

			query.Parameters.Add("@Param", typeof (Dictionary<string, object>), myDictionary, DictionaryPropertyProvider.GetProperties(myDictionary));
			query.Text = "SELECT @Param.IntProp, @Param.StringProp, @Param.DateTimeProp";
			dataGridView1.DataSource = query.ExecuteDataTable();

			#endregion
		}

		private void runMyReflectionProviderButton_Click(object sender, EventArgs e)
		{
			#region MyReflectionProvider Usage

			Query query = new Query();

			List<MyDataType> myDataType2Values = new List<MyDataType>();
			myDataType2Values.Add(new MyDataType(1, "One"));
			myDataType2Values.Add(new MyDataType(2, "Two"));
			myDataType2Values.Add(new MyDataType(3, "Three"));

			query.DataContext.MetadataContext.MethodProviders.Register(typeof(MyDataType), new MyReflectionProvider());
			query.DataContext.Tables.Add(myDataType2Values, "MyDataTypeValues");
			query.Text = "SELECT t.GetIntValue(), t.GetStringValue(), t.DO_IT(10) FROM MyDataTypeValues t";
			dataGridView1.DataSource = query.ExecuteDataTable();
			
			#endregion
		}

		private void runMyPropertyProviderButton_Click(object sender, EventArgs e)
		{
			#region MyPropertyProvider Usage

			Query query = new Query();

			List<MyDataType2> myDataType2Values = new List<MyDataType2>();
			myDataType2Values.Add(new MyDataType2(1, "One", DateTime.Now));
			myDataType2Values.Add(new MyDataType2(2, "Two", DateTime.Now.AddDays(1)));
			myDataType2Values.Add(new MyDataType2(3, "Three", DateTime.Now.AddDays(2)));

			query.DataContext.MetadataContext.PropertyProviders.Register(typeof(MyDataType2), new MyPropertyProvider());
			query.DataContext.Tables.Add(myDataType2Values, "MyDataType2Values");
			query.Text = "SELECT * FROM MyDataType2Values";
			dataGridView1.DataSource = query.ExecuteDataTable();

			#endregion
		}

		public void LogicalExpressionContainingNulls3()
		{
			Query query = new Query();
			query.Text = @"
				SELECT	CASE WHEN FalseTable.Value = 1 AND NullTable.Value = 1 THEN 'True' ELSE 'False' END AS ShouldBeFalse,
						CASE WHEN NullTable.Value = 1 AND FalseTable.Value = 1 THEN 'True' ELSE 'False' END AS ShouldBeFalse,
						CASE WHEN TrueTable.Value = 1 OR NullTable.Value = 1 THEN 'True' ELSE 'False' END AS ShouldBeTrue,
						CASE WHEN NullTable.Value = 1 OR TrueTable.Value = 1 THEN 'True' ELSE 'False' END AS ShouldBeTrue
				FROM	(SELECT 1 AS Value) AS TrueTable,
						(SELECT 0 AS Value) AS FalseTable,
						(SELECT NULL AS Value) AS NullTable
			";

			DataTable result = query.ExecuteDataTable();

			Assert.AreEqual(1, result.Rows.Count);
			Assert.AreEqual("False", result.Rows[0][0]);
			Assert.AreEqual("False", result.Rows[0][1]);
			Assert.AreEqual("True", result.Rows[0][2]);
			Assert.AreEqual("True", result.Rows[0][3]);
		}

		public void HashMatchWithMultikeysAndProbeResidual()
		{
			Rec1[] records1 = new Rec1[]
				{
					new Rec1(1, 1),
					new Rec1(1, 2),
					new Rec1(2, 1),
					new Rec1(2, 2),
			};

			Rec1[] records2 = new Rec1[]
				{
					new Rec1(1, 2),
				};
			
			Query query = new Query();
			query.DataContext.Tables.Add(records1, "Records1");
			query.DataContext.Tables.Add(records2, "Records2");
			
			query.Text = @"
SELECT	*
FROM	Records2 r2, Records1 r1
WHERE	r1.Key = r2.Key
AND		r1.SecKey = r2.SecKey
";
			DataTable result = query.ExecuteDataTable();
			Assert.AreEqual(1, result.Rows.Count);
			Assert.AreEqual(1, result.Rows[0][0]);
			Assert.AreEqual(2, result.Rows[0][1]);
			Assert.AreEqual(1, result.Rows[0][2]);
			Assert.AreEqual(2, result.Rows[0][3]);
		}

		public void SingleValueGetsConvertedCorrectly()
		{
			Dto[] dtos = new Dto[] {new Dto(1, 4), new Dto(2, 5), new Dto(2, 6)};
			
			Query query = new Query();
			query.Text = "SELECT SUM(ID) Int32Sum, SUM(Value) Int16Sum, SUM(CAST(Value AS INT64)) Int64Sum FROM Dtos GROUP BY ID ORDER BY 1";
			query.DataContext = new DataContext();
			query.DataContext.Tables.Add(dtos, "Dtos");
			
			DataTable dataTable = query.ExecuteDataTable();

			Assert.AreEqual(typeof(int), dataTable.Columns["Int32Sum"].DataType);
			Assert.AreEqual(typeof(long), dataTable.Columns["Int16Sum"].DataType);
			Assert.AreEqual(typeof(long), dataTable.Columns["Int64Sum"].DataType);
			
			Assert.AreEqual(2, dataTable.Rows.Count);
			Assert.AreEqual(1, dataTable.Rows[0]["Int32Sum"]);
			Assert.AreEqual((long)4, dataTable.Rows[0]["Int16Sum"]);
			Assert.AreEqual((long)4, dataTable.Rows[0]["Int64Sum"]);

			Assert.AreEqual(4, dataTable.Rows[1]["Int32Sum"]);
			Assert.AreEqual((long)11, dataTable.Rows[1]["Int16Sum"]);
			Assert.AreEqual((long)11, dataTable.Rows[1]["Int64Sum"]);
		}

		public void TestSampleQuery()
		{
			// Get a lists with all types and all members

			List<Type> typeList = new List<Type>();
			List<MemberInfo> memberList = new List<MemberInfo>();
			foreach (Assembly assembly in new Assembly[] { typeof(int).Assembly, typeof(Uri).Assembly, typeof(XmlDocument).Assembly })
			{
				Type[] types = assembly.GetTypes();
				typeList.AddRange(types);

				foreach (Type t in types)
					memberList.AddRange(t.GetMembers());
			}

			// Create data context containing the tables "Types" and "Members"

			DataContext dataContext = new DataContext();
			dataContext.Tables.Add(typeList.ToArray(), "Types");
			dataContext.Tables.Add(memberList.ToArray(), "Members");

			Query query = new Query(dataContext);
			query.Text = @"
                SELECT  t.Assembly.ManifestModule.Name AS AssemblyName,
                        m.MemberType.ToString() AS MemberType,
                        COUNT(*) AS MemberCount
                FROM    Types t
                            INNER JOIN Members m ON m.DeclaringType.AssemblyQualifiedName = t.AssemblyQualifiedName
                GROUP   BY t.Assembly.ManifestModule.Name, m.MemberType
            ";

			query.ExecuteDataTable();
		}

		public void EnumBitTesting()
		{
			const int None = 0x0;
			const int Value1 = 0x0001;
			const int Value2 = 0x0002;
			const int Value3 = 0x0004;

			List<MyClassWithEnumMembers> myClasses = new List<MyClassWithEnumMembers>();
			myClasses.Add(new MyClassWithEnumMembers(None));
			myClasses.Add(new MyClassWithEnumMembers(Value1));
			myClasses.Add(new MyClassWithEnumMembers(Value2));
			myClasses.Add(new MyClassWithEnumMembers(Value3));
			myClasses.Add(new MyClassWithEnumMembers(Value1 | Value2));
			myClasses.Add(new MyClassWithEnumMembers(Value3 | Value2));
			myClasses.Add(new MyClassWithEnumMembers(Value1 | Value3));
			myClasses.Add(new MyClassWithEnumMembers(Value2 | Value3));

			DataTable result;

			Query query = new Query();
			query.DataContext.Tables.Add(myClasses, "MyClasses");
			query.Parameters.Add("MyByteEnum", typeof (MyByteEnum), MyByteEnum.Value1);
			query.Parameters.Add("MySByteEnum", typeof (MySByteEnum), MySByteEnum.Value1);
			query.Parameters.Add("MyShortEnum", typeof (MyShortEnum), MyShortEnum.Value1);
			query.Parameters.Add("MyUShortEnum", typeof (MyUShortEnum), MyUShortEnum.Value1);
			query.Parameters.Add("MyIntEnum", typeof (MyIntEnum), MyIntEnum.Value1);
			query.Parameters.Add("MyUIntEnum", typeof (MyUIntEnum), MyUIntEnum.Value1);
			query.Parameters.Add("MyLongEnum", typeof (MyLongEnum), MyLongEnum.Value1);
			query.Parameters.Add("MyULongEnum", typeof (MyULongEnum), MyULongEnum.Value1);

			// Check bitwise-AND

			query.Text = @"
				SELECT	c.MyByteEnum,
                        c.MySByteEnum,
                        c.MyShortEnum,
                        c.MyUShortEnum,
                        c.MyIntEnum,
                        c.MyUIntEnum,
                        c.MyLongEnum,
                        c.MyULongEnum
				  FROM  MyClasses c
				 WHERE  (c.MyByteEnum & @MyByteEnum) != 0
				   AND  (c.MySByteEnum & @MySByteEnum) != 0
				   AND  (c.MyShortEnum & @MyShortEnum) != 0
				   AND  (c.MyUShortEnum & @MyUShortEnum) != 0
				   AND  (c.MyIntEnum & @MyIntEnum) != 0
				   AND  (c.MyUIntEnum & @MyUIntEnum) != 0
				   AND  (c.MyLongEnum & @MyLongEnum) != 0
				   AND  (c.MyULongEnum & @MyULongEnum) != 0
			";

			result = query.ExecuteDataTable();
			Assert.AreEqual(3, result.Rows.Count);
			Assert.AreEqual(MyByteEnum.Value1, (MyByteEnum) result.Rows[0][0]);
			Assert.AreEqual(MySByteEnum.Value1, (MySByteEnum)result.Rows[0][1]);
			Assert.AreEqual(MyShortEnum.Value1, (MyShortEnum)result.Rows[0][2]);
			Assert.AreEqual(MyUShortEnum.Value1, (MyUShortEnum)result.Rows[0][3]);
			Assert.AreEqual(MyIntEnum.Value1, (MyIntEnum)result.Rows[0][4]);
			Assert.AreEqual(MyUIntEnum.Value1, (MyUIntEnum)result.Rows[0][5]);
			Assert.AreEqual(MyLongEnum.Value1, (MyLongEnum)result.Rows[0][6]);
			Assert.AreEqual(MyULongEnum.Value1, (MyULongEnum)result.Rows[0][7]);

			Assert.AreEqual(MyByteEnum.Value1 | MyByteEnum.Value2, (MyByteEnum)result.Rows[1][0]);
			Assert.AreEqual(MySByteEnum.Value1 | MySByteEnum.Value2, (MySByteEnum)result.Rows[1][1]);
			Assert.AreEqual(MyShortEnum.Value1 | MyShortEnum.Value2, (MyShortEnum)result.Rows[1][2]);
			Assert.AreEqual(MyUShortEnum.Value1 | MyUShortEnum.Value2, (MyUShortEnum)result.Rows[1][3]);
			Assert.AreEqual(MyIntEnum.Value1 | MyIntEnum.Value2, (MyIntEnum)result.Rows[1][4]);
			Assert.AreEqual(MyUIntEnum.Value1 | MyUIntEnum.Value2, (MyUIntEnum)result.Rows[1][5]);
			Assert.AreEqual(MyLongEnum.Value1 | MyLongEnum.Value2, (MyLongEnum)result.Rows[1][6]);
			Assert.AreEqual(MyULongEnum.Value1 | MyULongEnum.Value2, (MyULongEnum)result.Rows[1][7]);

			Assert.AreEqual(MyByteEnum.Value1 | MyByteEnum.Value3, (MyByteEnum)result.Rows[2][0]);
			Assert.AreEqual(MySByteEnum.Value1 | MySByteEnum.Value3, (MySByteEnum)result.Rows[2][1]);
			Assert.AreEqual(MyShortEnum.Value1 | MyShortEnum.Value3, (MyShortEnum)result.Rows[2][2]);
			Assert.AreEqual(MyUShortEnum.Value1 | MyUShortEnum.Value3, (MyUShortEnum)result.Rows[2][3]);
			Assert.AreEqual(MyIntEnum.Value1 | MyIntEnum.Value3, (MyIntEnum)result.Rows[2][4]);
			Assert.AreEqual(MyUIntEnum.Value1 | MyUIntEnum.Value3, (MyUIntEnum)result.Rows[2][5]);
			Assert.AreEqual(MyLongEnum.Value1 | MyLongEnum.Value3, (MyLongEnum)result.Rows[2][6]);
			Assert.AreEqual(MyULongEnum.Value1 | MyULongEnum.Value3, (MyULongEnum)result.Rows[2][7]);

			// Check bitwise-OR

			query.Text = @"
				SELECT	c.MyByteEnum | @MyByteEnum,
                        c.MySByteEnum | @MySByteEnum,
                        c.MyShortEnum | @MyShortEnum,
                        c.MyUShortEnum | @MyUShortEnum,
                        c.MyIntEnum | @MyIntEnum,
                        c.MyUIntEnum | @MyUIntEnum,
                        c.MyLongEnum | @MyLongEnum,
                        c.MyULongEnum | @MyULongEnum
                  FROM  MyClasses c
			";
			result = query.ExecuteDataTable();
			Assert.AreEqual(8, result.Rows.Count);

			Assert.AreEqual(MyByteEnum.Value1, (MyByteEnum) result.Rows[0][0]);
			Assert.AreEqual(MyByteEnum.Value1, (MyByteEnum)result.Rows[1][0]);
			Assert.AreEqual(MyByteEnum.Value2 | MyByteEnum.Value1, (MyByteEnum)result.Rows[2][0]);
//.........这里部分代码省略.........

		public void ShortCircuitLogicalExpression3()
		{
			Query query = new Query();
			query.DataContext.Functions.AddFromContainer(typeof(RaiseErrorContainer));
			query.Parameters.Add("FalseParam", typeof(bool), false);
			query.Parameters.Add("TrueParam", typeof(bool), true);
			query.Text = @"
				SELECT	FalseParam AND RaiseError('Should not be called!') = 1 AS ShouldBeFalse,
						TrueParam OR RaiseError('Should not be called!') = 1 AS ShouldBeTrue			
			";

			DataTable result = query.ExecuteDataTable();

			Assert.AreEqual(1, result.Rows.Count);
			Assert.AreEqual(false, result.Rows[0][0]);
			Assert.AreEqual(true, result.Rows[0][1]);
		}

		public void ShortCircuitLogicalExpression2()
		{
			Query query = new Query();
			query.DataContext.Functions.AddFromContainer(typeof(RaiseErrorContainer));
			query.Text = @"
				SELECT	FalseTable.Value AND RaiseError('Should not be called!') = 1 AS ShouldBeFalse,
						TrueTable.Value OR RaiseError('Should not be called!') = 1 AS ShouldBeTrue
				FROM	(SELECT TRUE AS Value) AS TrueTable,
						(SELECT FALSE AS Value) AS FalseTable
			";

			DataTable result = query.ExecuteDataTable();

			Assert.AreEqual(1, result.Rows.Count);
			Assert.AreEqual(false, result.Rows[0][0]);
			Assert.AreEqual(true, result.Rows[0][1]);
		}