C# .NET - How to load data from database to datatable?

Asked By sangavi d on 28-Jul-09 04:41 AM
I have a scenario where I want to load the data from database to data table. Any idea on this?

re - Adam Houldsworth replied to sangavi d on 28-Jul-09 04:49 AM

Hi,

There are a couple of ways.  You can load a DataTable straight from a SqlDataReader:

http://msdn.microsoft.com/en-us/library/system.data.datatable.load.aspx

You can also do the data reader approach manually pulling out the values you require.

You can also use a TableAdapter - you create the necessary select command and map the returned SQL columns across to DataTable columns:

http://msdn.microsoft.com/en-us/library/bz9tthwx%28VS.80%29.aspx

Hopefully this will give you a good start.

Adam

re - Web Star replied to sangavi d on 28-Jul-09 04:50 AM

 strcon = System.Web.HttpContext.Current.Application["ConString"].ToString();

objcon = new SqlConnection(strcon);

objcon.Open();

string SQL ="select * from tblname"

 SqlCommand objCommand = new SqlCommand(SQL, objcon);
            SqlDataAdapter adapter = new SqlDataAdapter();
            DataSet DS = new DataSet();
            adapter.SelectCommand = objCommand;
            adapter.Fill(DS);
            //here ds contain a table

DataTable dt = DS.Table[0];

Santhosh N replied to sangavi d on 28-Jul-09 04:50 AM

you could do that in the following way

// create a new data adapter based on the specified query.

        SqlDataAdapter da = http://www.google.com/search?q=new+msdn.microsoft.com SqlDataAdapter();

        //set the SelectCommand of the adapter

        da.SelectCommand = cmd; //create the cmd object based on your requirement

        // create a new DataTable

        DataTable dtGet = http://www.google.com/search?q=new+msdn.microsoft.com DataTable();

        //fill the DataTable

        da.Fill(dtGet);

        //return the DataTable

        return dtGet;

Hi - Ravenet Rasaiyah replied to sangavi d on 28-Jul-09 05:08 AM
Hi

You can write like this way
private void LoadDataToTable()
{
DataTable table = null;
using (SqlConnection connection =new SqlConnection("con"))
{
using (SqlCommand cmd=connection.CreateCommand())
{
cmd.CommandText = "select * from emplyee";
cmd.CommandType = System.Data.CommandType.Text;
using (SqlDataReader reader=cmd.ExecuteReader())
{
if (reader.HasRows)
{
table = new DataTable();
table.Load(reader);
}
}
}
}
}

Thank you