How to read SQL Table data into a C# DataTable

Question

I've read a lot of posts about inserting a DataTable into a SQL table, but how can I pull a SQL table into a C#/.NET DataTable?

Ole

Umm...use the Fill method on a DataAdapter? – Excavate

msdn.microsoft.com/en-us/library/… – Varanasi

Answer 1

Here, give this a shot (this is just a pseudocode)

using System;
using System.Data;
using System.Data.SqlClient;


public class PullDataTest
{
    // your data table
    private DataTable dataTable = new DataTable();

    // your method to pull data from database to datatable   
    public void PullData()
    {
        string connString = @"your connection string here";
        string query = "select * from table";

        SqlConnection conn = new SqlConnection(connString);        
        SqlCommand cmd = new SqlCommand(query, conn);
        conn.Open();

        // create data adapter
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        // this will query your database and return the result to your datatable
        da.Fill(dataTable);
        conn.Close();
        da.Dispose();
    }
}

Scudo

The datatable field must be initialized before calling da.Fill(dataTable) – Anabaptist

@Scudo What about having output param from t sql beside datatable? how to get output param too? Is it possible? Sample? – Melt

This code is prone to errors and it is not recommended to use the available resources this way. Please see @Tim Rogers answer for the clean solution. – Majors

Apart from that, have a look at LINQ (if haven't already) as it can really do some magic here :-) – Majors

Although this code works, This does not utilize the try/catch or the using() to handle error. – Cystolith

Doesn't da.Fill require a DataSet, not a DataTable? @Xan-KunClark-Davis, do you happen to have a similar example in LINQ? – Phyllis

Answer 2

var table = new DataTable();    
using (var da = new SqlDataAdapter("SELECT * FROM mytable", "connection string"))
{      
    da.Fill(table);
}

Demeanor

This answer would benefit greatly from an example of how to apply a variable from outside of the SqlDataAdapter to the SELECT statement used within said SqlDataAdapter. – Noto

Answer 3

Lots of ways. Use ADO.NET and use fill on the data adapter to get a DataTable:

using (SqlDataAdapter dataAdapter
    = new SqlDataAdapter ("SELECT blah FROM blahblah ", sqlConn))
{
    // create the DataSet 
    DataSet dataSet = new DataSet(); 
    // fill the DataSet using our DataAdapter 
    dataAdapter.Fill (dataSet);
}

You can then get the data table out of the dataset.

In another answer, dataset isn't used. Instead, it uses

// create data adapter
SqlDataAdapter da = new SqlDataAdapter(cmd);
// this will query your database and return the result to your datatable
da.Fill(dataTable);

Which is preferable to mine.

I would strongly recommend looking at entity framework though; using datatables and datasets isn't a great idea. There is no type safety on them which means debugging can only be done at run time. With strongly typed collections (that you can get from using LINQ2SQL or entity framework) your life will be a lot easier.

Datatables = good, datasets = evil. If you are using ADO.NET then you can use both of these technologies (EF, linq2sql, dapper, nhibernate, ORM of the month) as they generally sit on top of ADO.NET. The advantage you get is that you can update your model far easier as your schema changes provided you have the right level of abstraction by levering code generation.

The ADO.NET adapter uses providers that expose the type info of the database, for instance by default it uses a SQL server provider, you can also plug in - for instance - devart PostgreSQL provider and still get access to the type info which will then allow you to as above use your ORM of choice (almost painlessly - there are a few quirks) - I believe Microsoft also provide an oracle provider. The ENTIRE purpose of this is to abstract away from the database implementation where possible.

Marniemaro

Typed datasets have type safety and strongly typed collections, just like EF. But those are only for when your app is tightly coupled to the database. If you're writing a tool that has to work with many different databases, type safety is a hopeless wish. – Labialize

Typed datasets in .net are a horrible creation of xml madness and woe. I have never worked in a place that is willing to accept the overhead of maintaining all that for a microsofts typed datasets. I don't think even microsoft suggests its sensible these days. As for type safety with multiple databases of course you can get it - the point is that you convert it into typed collection asap and pass that about so that you restrict the type issues to a specific place. Orms will help with that and work perfectly well with multiple databases. If you don't like EF use something lighter like dapper. – Marniemaro

You didn't understand me. If you are writing a general purpose tool that has no idea what kind of database it is going to connect to, then type safety is a hopeless wish. – Labialize

Sql is given. Besides, if you don't know what kind of database then why does it even have to be a database? What would be the application of such a generic tool? If you ever need to connect to databases that are really so radically different you would abstract away from it behind a repository pattern and then inside of that you would need different specialized database adapters and at that point you would know about the specifics. The fact is consuming code has type expectations -> type assertions in adapter. Your constraint means you have no idea about database language and so cannot query. – Marniemaro

Suppose you were writing an SSMS clone? – Labialize

It has access to types due to meta data tables and the fact the 'ss' refers to sql server. If you want to ask me direct questions i would be happy to try and answer. That last one had very little effort - lets suppose i already was? now what? – Marniemaro

At compile time I have NO IDEA what tables are in the database the user is going to connect me to. How on earth can a type-safe collection be created at runtime? – Labialize

I can easily query a table, find the types in it and assert that those types are co / contra variant to the types being expected at runtime. Otherwise you are simply asserting a tautology. You are pointing at the layer that provides type safety and saying look you cant write this in a type safe way. And well of course you can't, you defined the problem so you couldn't, however you can write that layer to be able to query the database schema itself so that it can do its job as an adapter and expose the database in a strongly typed way. why use c# if you don't want this? – Marniemaro

You wrote: "using datatables and datasets isn't a great idea. There is no type safety on them which means debugging can only be done at run time. With strongly typed collections (that you can get from using LINQ2SQL or entity framework) your life will be a lot easier." It has been my attempt to show you that there are genuine problems where type safety is an unachievable hope and that your hatred of datasets and love for EF is of no avail. If you don't wish to admit this or discuss this, then this discussion is at and end, and may I add, I am glad of it. – Labialize

I was answering a question that was asked. In the context of that my answer is perfectly valid, still is and I stand by it. You have come in with some irrelevant problem that is not being asked about and saying 'well in this case you have a problem huuur'. Well yes perhaps, but I still maintain I can code to your problem and STILL be correct at the level this question is being asked at. I am not sure what your point is, you are must be trolling. You haven't once shown how datasets would help in the area you are talking about - they are irrelevant so I name you 'troll'. Congratulations. – Marniemaro

Answer 4

Vendor independent version, solely relies on ADO.NET interfaces; 2 ways:

public DataTable Read1<T>(string query) where T : IDbConnection, new()
{
    using (var conn = new T())
    {
        using (var cmd = conn.CreateCommand())
        {
            cmd.CommandText = query;
            cmd.Connection.ConnectionString = _connectionString;
            cmd.Connection.Open();
            var table = new DataTable();
            table.Load(cmd.ExecuteReader());
            return table;
        }
    }
}

public DataTable Read2<S, T>(string query) where S : IDbConnection, new() 
                                           where T : IDbDataAdapter, IDisposable, new()
{
    using (var conn = new S())
    {
        using (var da = new T())
        {
            using (da.SelectCommand = conn.CreateCommand())
            {
                da.SelectCommand.CommandText = query;
                da.SelectCommand.Connection.ConnectionString = _connectionString;
                DataSet ds = new DataSet(); //conn is opened by dataadapter
                da.Fill(ds);
                return ds.Tables[0];
            }
        }
    }
}

I did some performance testing, and the second approach always outperformed the first.

Stopwatch sw = Stopwatch.StartNew();
DataTable dt = null;
for (int i = 0; i < 100; i++)
{
    dt = Read1<MySqlConnection>(query); // ~9800ms
    dt = Read2<MySqlConnection, MySqlDataAdapter>(query); // ~2300ms

    dt = Read1<SQLiteConnection>(query); // ~4000ms
    dt = Read2<SQLiteConnection, SQLiteDataAdapter>(query); // ~2000ms

    dt = Read1<SqlCeConnection>(query); // ~5700ms
    dt = Read2<SqlCeConnection, SqlCeDataAdapter>(query); // ~5700ms

    dt = Read1<SqlConnection>(query); // ~850ms
    dt = Read2<SqlConnection, SqlDataAdapter>(query); // ~600ms

    dt = Read1<VistaDBConnection>(query); // ~3900ms
    dt = Read2<VistaDBConnection, VistaDBDataAdapter>(query); // ~3700ms
}
sw.Stop();
MessageBox.Show(sw.Elapsed.TotalMilliseconds.ToString());

Read1 looks better on eyes, but data adapter performs better (not to confuse that one db outperformed the other, the queries were all different). The difference between the two depended on query though. The reason could be that Load requires various constraints to be checked row by row from the documentation when adding rows (its a method on DataTable) while Fill is on DataAdapters which were designed just for that - fast creation of DataTables.

Thumb

You need to surround the DataTable.Load() with .BeginLoadData() and .EndLoadData() to achieve the same speed as with the DataSet. – Dressmaker

Answer 5

Centerlized Model: You can use it from any where!

You just need to call Below Format From your function to this class

DataSet ds = new DataSet();
SqlParameter[] p = new SqlParameter[1];
string Query = "Describe Query Information/either sp, text or TableDirect";
DbConnectionHelper dbh = new DbConnectionHelper ();
ds = dbh. DBConnection("Here you use your Table Name", p , string Query, CommandType.StoredProcedure);

That's it. it's perfect method.

public class DbConnectionHelper {
   public DataSet DBConnection(string TableName, SqlParameter[] p, string Query, CommandType cmdText) {
    string connString = @ "your connection string here";
    //Object Declaration
    DataSet ds = new DataSet();
    SqlConnection con = new SqlConnection();
    SqlCommand cmd = new SqlCommand();
    SqlDataAdapter sda = new SqlDataAdapter();
    try {
     //Get Connection string and Make Connection
     con.ConnectionString = connString; //Get the Connection String
     if (con.State == ConnectionState.Closed) {
      con.Open(); //Connection Open
     }
     if (cmdText == CommandType.StoredProcedure) //Type : Stored Procedure
     {
      cmd.CommandType = CommandType.StoredProcedure;
      cmd.CommandText = Query;
      if (p.Length > 0) // If Any parameter is there means, we need to add.
      {
       for (int i = 0; i < p.Length; i++) {
        cmd.Parameters.Add(p[i]);
       }
      }
     }
     if (cmdText == CommandType.Text) // Type : Text
     {
      cmd.CommandType = CommandType.Text;
      cmd.CommandText = Query;
     }
     if (cmdText == CommandType.TableDirect) //Type: Table Direct
     {
      cmd.CommandType = CommandType.Text;
      cmd.CommandText = Query;
     }
     cmd.Connection = con; //Get Connection in Command
     sda.SelectCommand = cmd; // Select Command From Command to SqlDataAdaptor
     sda.Fill(ds, TableName); // Execute Query and Get Result into DataSet
     con.Close(); //Connection Close
    } catch (Exception ex) {

     throw ex; //Here you need to handle Exception
    }
    return ds;
   }
  }

Madge

Answer 6

If you use the latest version of C# (after version 8) , the code becomes even simpler, because the using statement does not need braces.

var table = new DataTable();
using var da = new SqlDataAdapter(sql, connectionString);
da.Fill(table);     

Oppugnant