Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Can an execute query be run within a .NET Dataset?

Posted on 2011-09-20
6
Medium Priority
?
272 Views
Last Modified: 2012-05-12
Can an execute query be run within a .NET Dataset?
If so can you show me how.
If its not possible how can I accomplish this another way?


I have DataSet which will ultimately have two tables
Singles and Pairs

The Singles Table has the following structure
 
id (int)
Name (string)

I populate the Singles Table with a service call that returns a list of Single objects

The Couples Table has the following structure
Name1 (string)
Name2 (string)
Compatible (bool)


I want to populate the second table with the results of the following query.
INSERT INTO Couples
SELECT  Singles.Name AS Single1, Singles_1.Name AS Single2, 'True' as Compatible
FROM         dbo.Singles Singles_1 INNER JOIN
                      dbo.Singles Singles_1 ON Singles.ID < Singles_1.ID
                     
                     
This is the Code I have so far but have gotten stuck on the last part.

   private void button1_Click(object sender, EventArgs e)
        {
            // Create the container to hold the Singless
            List<IdNameEntity> SinglesList = new List<IdNameEntity>();
           
            MainServiceClient MyServiceClient = new MainServiceClient();
            MyServiceClient.Open();
            SinglesList = MyServiceClient.GetSingles();

            DataTable SinglesTable = new DataTable();
            SinglesTable.Columns.Add(new DataColumn("Singles1Id", typeof(int)));
            SinglesTable.Columns.Add(new DataColumn("Singles1", typeof(string)));
           
            foreach(IdNameEntity Singles in SinglesList )
            {
                DataRow row = SinglesTable.NewRow();
                row.SetField("Singles1Id", Singles.Id);
                row.SetField("Singles1",Singles.Name);
                SinglesTable.Rows.Add(row);
            }

            MyDataSet.Tables.Add(SinglesTable);

            //Now create a Compatibility Table
            DataTable Couples = new DataTable();

            Couples.Columns.Add(new DataColumn("Singles1Id", typeof(int)));
            Couples.Columns.Add(new DataColumn("Singles1", typeof(string)));
            Couples.Columns.Add(new DataColumn("Singles2Id", typeof(int)));
            Couples.Columns.Add(new DataColumn("Singles2", typeof(string)));
            Couples.Columns.Add(new DataColumn("Compatable", typeof(bool)));


            MyDataSet.Tables.Add(Couples);

            //Write a query to populate the Compatibility Table from the Singles Table

            string Comand = "INSERT INTO Couples SELECT  SinglesTable.Id AS Single1Id, SinglesTable.Name AS Single1Name, SinglesTable_1.Id AS Single2Id, SinglesTable_1.Name AS Single2Name, 'True' as Compatible FROM dbo.SinglesTable SinglesTable_1 INNER JOIN dbo.SinglesTable SinglesTable_1 ON SinglesTable.ID < SinglesTable_1.ID ";

       
       
       
        }


Thank you for any assitance you can provide.
0
Comment
Question by:11ptMan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 40
ID: 36570730
You can fill a DataTable instead of a DataSet, which is recommended anyway if you have only one table or if you have many tables that are not linked through a DataRelation. Use the Fill command, the same way you do with a DataSet, but replace this one by your DataTable

Another alternative is a DataReader (there are good samples of its use in online help). You use a DataTable to loop through the lines in the resulting query the same way you do when loop through through a file in sequential access. Read one line, do what you want to do with it, then read the following line, until there is no more line left.

The do what you want to do with it is up to you. You can fill a DataTable, a ComboBox, a DataGridView, a collection.

If all you want to do is get only one value from the database, or execute a command that does not return values, such as INSERT, UPDATE and DELETE, you can also do it directly through the Command object, by executing one of its numerous methods, the most commonly used being ExecuteScalar (to retrieve a unique value) and ExecuteNonQuery (to execute a command that does not return a value). There are good examples of all of those too in online help.
0
 
LVL 30

Expert Comment

by:anarki_jimbel
ID: 36570864
Honestly, I didn't understand the question. Definitely there is a big confusion about how it works.

A DataSet is just a result of a query. Therefore it does not make sense to talk about running a query within a result of query.

I read the answer from JamesBurger and suspect that he didn't understand either. At least, this is my feeling. BTW, can't see big difference between filling a DataSet or a DataTable how JamesBurger proposes. DataSet is basicly a collection of DataTables. So, if you feel a DataSet - you have a Datatable in it.

Anyway, I'd recommend to author to clarify the task.
0
 

Author Comment

by:11ptMan
ID: 36571322
I may need to clarify neither of the tables in the dataset come from a database. I create and populate the first one from a list of objects. I want to populate the second by executing the SQL statement against the first. The SQL statement creates every posible pairing of the items in the first table. I want to save those pairings in the second table. All this happens in memory no DB no connections no filling. The inital list of objects comes from a service call that I have no ability to alter.  Hope this helps to clarify my question.  Thank You
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:11ptMan
ID: 36571323
I may need to clarify neither of the tables in the dataset come from a database. I create and populate the first one from a list of objects. I want to populate the second by executing the SQL statement against the first. The SQL statement creates every posible pairing of the items in the first table. I want to save those pairings in the second table. All this happens in memory no DB no connections no filling. The inital list of objects comes from a service call that I have no ability to alter.  Hope this helps to clarify my question.  Thank You
0
 
LVL 30

Accepted Solution

by:
anarki_jimbel earned 2000 total points
ID: 36571506
No, in DataTables in memory you cannot do that. It is possible to select rows but it is not possible to run SQL queries.

Alternatives:

1. Use LINQ. Honestly, not 100% sure it is achievable with LINQ.

2. Use traditional loops etc.
0
 

Author Closing Comment

by:11ptMan
ID: 36581338
While No was not really the answer I was looking for it was the one I kind of expected.  LINQ will do what i needed without needing any DataSets.  

Here is the Updated version of the code

 private void button6_Click(object sender, EventArgs e)
        {
            // Create the container to hold the Singles
            IQueryable<IdNameEntity> SinglesList;

            MainServiceClient MyServiceClient = new MainServiceClient();
            MyServiceClient.Open();
            SinglesList = MyServiceClient.GetSingles().AsQueryable();

            // Create the container to hold the Couples
            IQueryable<string> Couples;

            Couples = from a in SinglesList
                      from b in SinglesList
                      where a.Id.CompareTo(b.Id) < 0
                      select string.Format("{0}:{1}", a.Id.ToString(), b.Id.ToString());

        }
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question