Filtering a DataSet or a DataTable 2

[GarethKruse]

Hello,

I'm new to SQL Server T-SQL and C#.

In a method of a web application, I create an SQLDataAdapter and execute a stored procedure on the SQL Server from within the C# method. The corresponding query result is filled into a DataSet. Now I want to filter the result set in the same method according to some criteria like:

FilteredResult =
filter (FilledDataSet or DataTable, criteria)

where criteria is something like:

select ....
from ...
where ....
.
.
.



Could you give me a simple example?

Thank you in advance.

Gareth

 

[JohnEfford]

See below for two methods of filtering a datatable.

	class Test
	{
		/// <summary>
		/// The main entry point for the application.
		/// </summary>
		[STAThread]
		static void Main(string[] args)
		{
			DataTable dt;
			DataRow[] drs;
			DataRow dr;

			//Create a datatable
			dt = new DataTable();
			dt.Columns.Add(&quot;Id&quot;,Type.GetType(&quot;System.Int16&quot;));
			dt.Columns.Add(&quot;Name&quot;,System.Type.GetType(&quot;System.String&quot;));
			
			//Add two rows to the data table
			dr = dt.NewRow();
			dr[&quot;ID&quot;]=10;
			dr[&quot;Name&quot;]=&quot;John&quot;;
			dt.Rows.Add(dr);

			dr = dt.NewRow();
			dr[&quot;ID&quot;]=11;
			dr[&quot;Name&quot;]=&quot;Bobski&quot;;
			dt.Rows.Add(dr);

			//Filter using the datatable select which returns an array of datarows
			//load the array of data roes with all rows with an ID = 10
			drs = dt.Select(&quot;id = 11&quot;);

			Console.WriteLine(&quot;Using datatable select&quot;);
			Console.WriteLine(&quot;ID={0} and Name={1}&quot;,drs[0][&quot;ID&quot;].ToString(),drs[0][&quot;Name&quot;] );
			//Line below causes error because select only returns one row			
			//Console.WriteLine(&quot;ID={0} and Name={1}&quot;,drs[1][&quot;ID&quot;].ToString(),drs[1][&quot;Name&quot;] );
			
			//Filter Using the 
			dt.DefaultView.RowFilter = &quot;id=11&quot;;
			Console.WriteLine(&quot;Using a view on the datatable&quot;);
			Console.WriteLine(&quot;ID={0} and Name={1}&quot;,dt.DefaultView[0][&quot;ID&quot;].ToString(),dt.DefaultView[0][&quot;Name&quot;] );
			//Line Below causes error as the dataview only contains one row
			//Console.WriteLine(&quot;ID={0} and Name={1}&quot;,dt.DefaultView[1][&quot;ID&quot;].ToString(),dt.DefaultView[1][&quot;Name&quot;] );

			String Ret;
			Ret=Console.ReadLine();
		
		}
	}

A data set holds a collection of datatables, you do not need to use a dataset unless you intend to create in memory relationships between datatables.

regards

 

[GarethKruse]

Thank you very much JohnEfford.

Best regards,

Gareth