populating a dataset/datatable from another dataset

Hello Amar, you need to take in consideration that the query syntax that you are using to query your DB is SQL, the DataSet or DataTable objects don't has a method to return a result based on SQL, please see if you can accomplish what you want to do by using the DataTable.Select method, it can receive a filter expression and sort expression to return a DataRow array of the matched rows.
Example:

// Asumming that myData is a System.Data.DataTable object with data.
// Return rows where FirstName start with the letter A
DataRow[] myRows = myData.Select("FirstName Like 'A*'");

Select allOpen in new window

More info about that method can be found here:
http://msdn.microsoft.com/en-us/library/det4aw50.aspx
And about filter expressions here:
http://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression.aspx

I've had a look but the problem with the filter expressions is they don't allow you to create expressions like the following: abc??123-234-* (with a question mark meaning any single character) ideally I'd like to use regular expression like syntax in the where clause...any ideas?

Well I've put together two different approaches you might take to "filter/query/select" a datatable from a dataset into a seperate new datatable.

Hope it helps.

private void GetData1()
    {
        using (SqlConnection conn = new SqlConnection("Data Source=WT101\\WT101A;Initial Catalog=DevTesting;Integrated Security=True"))
        {
            SqlCommand cmd = new SqlCommand("SELECT * FROM Items", conn);
            DataTable dTable = new DataTable();

            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            dTable.Load(dr);
            dr.Close();
            conn.Close();

            //Mostly the focus is on datatables but I put this here just to
            //involve the relationship of a dataset
            DataSet dSet = new DataSet();
            dSet.Tables.Add(dTable);

            //requires using System.Collections.Generic;
            //create a generic list of IEnumerable<datarow>
            //using a linq query to filter from the 1st datatable
            IEnumerable<DataRow> query =
                from Item in dTable.AsEnumerable()
                where Item.Field<Byte>("ItemNumber") > -1
                select Item;
            
            //feed the list to a new datatable
            DataTable dTable2 = query.CopyToDataTable<DataRow>();
        }
    }



    //A more old fashioned approach
    private void GetData2()
    {

        using (SqlConnection conn = new SqlConnection("Data Source=WT101\\WT101A;Initial Catalog=DevTesting;Integrated Security=True"))
        {
            SqlCommand cmd = new SqlCommand("SELECT * FROM Items", conn);
            DataTable dTable = new DataTable();

            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            dTable.Load(dr);
            dr.Close();
            conn.Close();

            DataSet dSet = new DataSet();
            dSet.Tables.Add(dTable);

            DataTable dTable2 = new DataTable();
            dTable2 = dTable.Copy();
            dTable2.Clear();
            DataRow[] dRows = dTable.Select("ItemNumber > -1");

            foreach(DataRow r in dRows)
            {
                dTable2.ImportRow(r);
            }
        }
    }

Select allOpen in new window

Originally:

Ive been looking for a quite a while but i cant seem to find a way to populate a dataset/datatable from another dataset/datatable using a select query.

Updated:

I've had a look but the problem with the filter expressions is they don't allow you to create expressions like the following: abc??123-234-* (with a question mark meaning any single character) ideally I'd like to use regular expression like syntax in the where clause...any ideas?

If you are going to try to use regular expression syntax rather than sql syntax it may be more powerful in some respects. I'd have to work with it awhile to decide what I thought.

SQL type syntax in the filter property should allow you to query datatables the most efficiently and the filter expressions have complexity:

http://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression.aspx

I think you can combine linq with regex and maybe get where you're going like this:

var source = myDataTable.AsEnumerable();  

var results =
 from matchingItem in source
 where Regex.IsMatch(matchingItem.Field<string>("Name"), "<put Regex here>")              
 select matchingItem;  

var list = results.ToList();

If you combined something like this with my sample it would probably work out... a direction to look at anyway.

that is fantastic stuff to get going with guys! much appreciated...

i've figured out another aproach but its involves doing it the old fashioned way using a connection object. and copying the database to folder the application has access to....the maximum table size will typically 30,000 rows and at the very most (very rarely) - 300,000 rows, i hope that helps put it into context. although id like it to work with a million rows eventually.

hopefully it gives you more of an idea as to what im doing...let me know if you think the way i propose will work or not...
in the meantime i'll try implementing what you guys have suggested...but it might take a day or two as id have to learn all about LINQ and regex in c #.

string sqlString = "SELECT id_number, name, title FROM Items";
	   SqlCommand cmd = new SqlCommand(sqlString, conn);
            DataTable dTable = new DataTable();

            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            dTable.Load(dr);
            dr.Close();
            conn.Close();

            DataSet dSet = new DataSet();
            dSet.Tables.Add(dTable);
			
	//im building up the query in the second dataset as follows
	//the where clause wont be any more complicated than whats given below (for now)
	//each object will have a different where clause like the one given i
	//where the question marks mean any character (like access sql)
	string sqlString2 = " where id_number like '1234-3??-????-ABC12-A*'" 
			//                                         '1234-3__-____-ABC12-A%' or do i have to use this?
	string sqlStringFinal = sqlString1 + sqlString2
			
	//is there anyway i can use the above sqlStringFinal to populate the datatable?
	//i'll only be connecting to access databases...im half tempted to copy the the whole database 
	//into a folder for the apps to use...that way i can just use the connection object fill the datatables that way
			
	   SqlCommand cmd = new SqlCommand(sqlStringFinal, conn);
            DataTable dTable2 = new DataTable();

            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            dTable.Load(dr);
            dr.Close();
            conn.Close()
			
	   DataSet dSet2 = new DataSet();
            dSet.Tables.Add(dTable2);	

            //what do you guys think...i think regular exp and linq would be nice to use
	   //but i've never used it and time is of the essence (isnt it always!)

Select allOpen in new window

Before I fiddle with your code too much I will remark on your comments from your latest code.

Making your database available is ideal for this issue. You just made it sound as if that was not possible in any way.

1. You dont have to re-invent the wheel.

2. You can minimize the data you have to work with before even making the datasets.

3. The approaches supplied from your original questions are simply not going to scale well at all. Loading multiple datasets with hundreds of thousands of records and manipulating them in memory is going to be heavy lifting for your server.

4. Using Access is a bad approach even at the beginning for all but the most basic student project, small business office tool or hobbyist application. SQL Express would be a much better starting point. It overcomes all the limitations/glitchiness of Access, is as easy and flexible to use and implement and its free. Just food for thought.

More simple observation...

If I were in a situation where I had to build a website based off a datasource that was limited(Access) and I could only read from that datasource briefly (once per day/week) then I would consider doing the following:

I would think about the process of making a local version of that same database using SQL(or sqlexpress) and building import functions/conversion processes. Once a week or day or whatever I would import my data from Access. Then I would run all my code efficiently from my local version of a better brand of database where I have corrected all the deficiencies of the original access db. My code is going to scale now. I can use more traditional data methods to read/write...etc.

The trade off is I would be putting the energy into communication/translation between sql/access rather than complex manipulation of datasets.

Fantastic solutions guys...the regular expressions enabled me to query the dataset directly without having to use the connection object repeatedly! i've split the points as yv989c provided the regular expression too. Again thank you for your help!