We help IT Professionals succeed at work.

populating a dataset/datatable from another dataset

Amar_Hussain
Amar_Hussain asked
on
Hi Experts,
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.

I have multiple datachild objects which each have a dataset object and a select query with different where clauses. i have previously imported the master dataset into a main form. the database which the master dataset was filled with will not be available once the the data has been imported (hence the need to query the dataset).

i would like to fill each of the datachild object datasets using the select query (saved as a string) using the master dataset as the main datasource. Im thinking this would be easy but for the life of me cannot find any information anywhere on how to do this.
Any help would be appreciated.

Amar
Comment
Watch Question

Carlos VillegasFull Stack .NET Developer

Commented:
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*'");

Open 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

Author

Commented:
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?

Commented:
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);
            }
        }
    }

Open in new window

Carlos VillegasFull Stack .NET Developer
Commented:
If you want to returns rows based on a regular expressions you can achieve that in this way, example:
DataTable myData = new DataTable();
myData.Columns.Add("FirstName", typeof(string));
// Sample data.
myData.Rows.Add("Jackson Redford");
myData.Rows.Add("Mark Redford");
myData.Rows.Add("Joseph Smith");

// Get the results
List<DataRow> myRows = new List<DataRow>();
foreach (DataRow row in myData.Rows)
{
    if (System.Text.RegularExpressions.Regex.IsMatch(row["FirstName"].ToString(), ".a.*"))
        myRows.Add(row);
}

// Or by using linq
IEnumerable<DataRow> myRowsUsingLinq =
from row in myData.AsEnumerable()
where System.Text.RegularExpressions.Regex.IsMatch(row["FirstName"].ToString(), ".a.*")
select row;

Open in new window

Commented:
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.

Author

Commented:
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!)

Open in new window

Commented:
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.

Commented:
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.
Commented:
Here's another example taken from your last sample/question. If you are going to do datasets/datatables and want to do advanced pattern matching it is probably the only way to go without linq maybe helping out.

I've put in notes that may help.


private void GetData3()
    {

        using (SqlConnection conn = new SqlConnection("Data Source=WT101\\WT101A;Initial Catalog=DevTesting;Integrated Security=True"))
        {
            SqlCommand cmd = new SqlCommand("SELECT ItemId, Name, Title FROM Items2", 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();
            //This type of pattern matching is not possible in filtering unfortunately
            //It would work perfectly against SQL directly, but not as a filter expression
            //DataRow[] dRows = dTable.Select("(ItemId LIKE '%1234-3__-____-ABC12-A%')");
            DataRow[] dRows = dTable.Select();


            foreach (DataRow r in dRows)
            {
                //regex makes my brain bleed but I think this is the equivalent of your example from above
                //I dont really think we want to * at the end but its doing no harm
                if (System.Text.RegularExpressions.Regex.IsMatch(r["ItemId"].ToString(), @"1234\-3..\-....\-ABC12\-A*"))
                {
                    dTable2.ImportRow(r);
                }
            }
        }
    }

Open in new window

Author

Commented:
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!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.