How can we fill data table in sqlcommand.

Question

0.00/5 (No votes)

See more:

ASP.NET

Posted 24-Mar-12 1:59am

Mas11

Add a Solution

5 solutions

Solution 1

Try:

C#

using (SqlConnection con = new SqlConnection(strConnect))
    {
    con.Open();
    using (SqlDataAdapter da = new SqlDataAdapter("SELECT MyColumn1, MyColumn2 FROM myTable", con))
        {
        DataTable dt = new DataTable();
        da.Fill(dt);
        ...
        }
    }

Posted 24-Mar-12 2:01am

OriginalGriff

Comments

Mas11 24-Mar-12 8:12am

Dear bro by using SQLCommand not DataAdaptor.

OriginalGriff 24-Mar-12 8:24am

You can't directly fill a datatable from an SQL command - it would return a SqlDataReader, which is not the same thing, and can't be directly converted - you would have to create your own DataTable, assign the columns, and then loop through the reader manually converting rows to DataRows and adding them. Much more efficient (and easier) to use a DataAdapter.
The DataAdapter uses an SqlCommand to fill the datatable itself, which is available via DataAdapter.SqlCommand

Is there a specific reason why you want to use an SqlCommand directly?

Uday P.Singh 25-Mar-12 3:00am

5!

Mas11 28-Mar-12 8:32am

Original Griff thanks a lot for your valuable guidance.. I catch it..

OriginalGriff 28-Mar-12 9:51am

You're welcome!

Solution 2

Here is the generic code to fill data table for sql command as:

C#

public static DataTable GetDataTable(SqlCommand cmd)
{
    try
    {
        SqlDataAdapter dataAdapt = new SqlDataAdapter();
        dataAdapt.SelectCommand = cmd;
        DataTable dataTable = new DataTable();
        dataAdapt.Fill(dataTable);
        return dataTable;
        
    }
    catch (SqlException ex)
    {
        MessageBox.Show(ex.Message);
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

Posted 24-Mar-12 2:03am

Ganesan Senthilvel

Comments

Mas11 24-Mar-12 8:13am

Dear bro by using SQLCommand not DataAdaptor.

Uday P.Singh 25-Mar-12 3:00am

5!

Solution 3

C#

what about this one ?

C#

string constr = @"Data Source=.;Initial Catalog=test;Integrated Security=True";
SqlConnection con = new SqlConnection(constr);
con.Open();
SqlDataAdapter adpt = new SqlDataAdapter("select * from  Items",con);
DataTable dt = new DataTable();
adpt.Fill(dt);

Posted 24-Mar-12 19:15pm

Mohamed Mitwalli

Comments

Uday P.Singh 25-Mar-12 3:01am

5!

Solution 5

You don't have to use DataAdapter, you can use the SqlCommand to fill a DataTable like this:

C#

SqlDataReader dr = sqlCmd.ExecuteReader()

var tb = new DataTable();
tb.Load(dr);

Posted 2-Sep-15 11:38am

BingeCoder

Comments

Rizwan Chattha 3-Dec-15 0:06am

But where is query?

MohmdNader 30-Sep-19 3:13am

Thank You , It's working well it's the right answer

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)

Uday P.Singh · Accepted Answer · 2012-03-24T20:44:00

You need to understand the ado.net architecture[^] first as all the above answers are correct.

DataAdapter provides the bridge between the DataSet object(DataTable etc.) and the data source.

The DataAdapter uses Command objects to execute SQL commands at the data source to both load the DataSet with data, and reconcile changes made to the data in the DataSet back to the data source.

hope it helps :)

How can we fill data table in sqlcommand.

5 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Solution 5

Add your solution here

Preview 0

Existing Members

...or Join us