Populate a sqldatatable with a row from sqldatareader

Question

0.00/5 (No votes)

See more:

Hello,
I've a little problem, I need your support..
This is the situation:
I made a query using a sqlcommand ed a slqdatareader to read the result, foreach row resulting from this query, I need to make another query.
I've write a while loop, if the datareader has rows, while datareader read, I need to do another query in a different table, using the datareader[0] value as parameter, and I would insert every row in a table; when loop has ended, the table will be the data source of a data grid. My question is how can I insert every single row from the sqldatareader in the table ?
Language is C#.
Thanks in advance.
Domenico.

What I have tried:

I googled and I've tried varius solutions, but nothing fit to my problem.

Posted 14-Nov-18 4:09am

dom71_01

Updated 14-Nov-18 7:32am

Add a Solution

Comments

CHill60 14-Nov-18 10:30am

Show the code you have so far to give us context

Aydin Homay 14-Nov-18 10:36am

What is the table? is a DataTable, DataSet, or you mean DataGridView

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Aydin Homay · Answer 1 · 2018-11-14T04:46:00

Hi,

In a more general way if you are looking for inserting a record into a SQL database you can find a nice and very well explain article in the below links:

Simple ADO.NET Database Read, Insert, Update and Delete using C#.[^]

Inserting values into a SQL Server database using ado.net via C# - Stack Overflow[^]

After making sure that you know how to do CRUD on a db via ADO.Net then we can continue with the following steps:

You stored the result of query in a sqlDataReader now you need to create a custom DataTable that contains the same columns (DataColumns) that your sqlDataReader contains and then use Rows property of DataTable to add records one-by-one and at the end bind your DataTable to your DataGridView.

C#

var dataTable = new DataTable();
dataTable.Columns.Add("Col1");
dataTable.Columns.Add("Col2");
dataTable.Rows.Add(new object[]{"Rod", "Stephens"});
var bindingSource = new BindingSource();
bindingSource.DataSource = dataTable;
DataGridView1.DataSource = bindingSource;

A more complete example can be found at the following link:
Build a DataTable and bind it to a DataGridView in C#C# Helper[^]

If this did not solve your problem then please leave a comment and I will assist you by improving my solution until your problem gets solved.

Cheers,
AH

Member 12893295 · Answer 2 · 2018-11-14T06:43:00

There are 2 way to do this
1)When you got the sqldatareader object then read that object if reader has data the you do another query on basis of reader object the again you got the reader object that time you need to create the Datarow object and insert the reader object values into datarow then add datarow into datatable.finally assign datatable to grid
2)Create 1 stored procedure into which create one cursor.Collect the data into cursor.Read the cursor row by row then write the another query with parameter of cursor row the collect the data into temp variable then return the temp variable.

dom71_01 · Answer 3 · 2018-11-14T05:04:00

Solution 2

Thank you,
but this isn't what I'm looking for; I know how to make a CRUD operations on SQL database. My problem is another. I make a query on a database table and with every record returned by it, I need to perform another query to different table and insert every result in a sqldatatable, and use this last table as data source for a grid view. This table isn't an SQL database object.
Regards.
DR.

Posted 14-Nov-18 5:04am

dom71_01

Comments

Aydin Homay 14-Nov-18 11:15am

I think instate of writing a comment you posted an answer to your own question. I am going to update my solution to cover your question you can also remove this answer ;-)

dom71_01 · Answer 4 · 2018-11-14T07:32:00

Solution 4

I've used the first solution, but what I haven't found is how can I insert a single record in a datatable.
Here's an example of my code:

C#

... first query..
if (datareader.HasRows())
{
   while(datareader.Read())
   {
      query = ("SELECT field1, field2, field3 FROM table " +
         "WHERE field4 = '" + datareader[0].ToString() + "'");
      command.commandtext = query;
      sqlDataReader dataR = command.ExecuteReader();
      // NOW I'VE THE RECORD. THE METHOD TO INSERT IT INTO THE TABLE ??
      dataGridView1.DataSource = table;
   }
}

I hope I was clear.
Thanks.

Posted 14-Nov-18 7:32am

dom71_01

Comments

Richard Deeming 16-Nov-18 11:14am

If you want to update your question, click the green "Improve question" link and update your question.

If you want to reply to a solution, click the "Have a Question or Comment?" button under that solution.

DO NOT post your update or comment as a new "solution".

Richard Deeming 16-Nov-18 11:15am

Also, your code is vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

Richard Deeming 16-Nov-18 11:16am

And finally, you can almost certainly achieve what you're trying to do with a single query, using a join. If you update your question with an example of both queries, someone will be able to show you how.

dom71_01 19-Nov-18 8:33am

Sorry, this is my first question and I've used the wrong way to add a comment.
Thank you.

Populate a sqldatatable with a row from sqldatareader

4 solutions

Solution 1

Solution 3

Solution 2

Solution 4

Add your solution here

Preview 0