Read SQL Table into C DataTable

Question

I ve read a lot of posts about inserting a DataTable into a SQL table  but is there an easy way to pull a SQL table into a  NET DataTable

User · Answer

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    Note in the upvoted answer dataset isn t used   It appeared after my answer  It does     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   Edit  Perhaps I wasn t clear  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 postgress 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

User · Answer

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  gt  0     If Any parameter is there means  we need to add                 for  int i   0  i  lt  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

User · Answer

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         public PullDataTest                       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

User · Answer

Vendor independent version  solely relies on ADO NET interfaces  2 ways   public DataTable Read1 lt T gt  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 lt S  T gt  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  lt  100  i          dt   Read1 lt MySqlConnection gt  query       9800ms     dt   Read2 lt MySqlConnection  MySqlDataAdapter gt  query       2300ms      dt   Read1 lt SQLiteConnection gt  query       4000ms     dt   Read2 lt SQLiteConnection  SQLiteDataAdapter gt  query       2000ms      dt   Read1 lt SqlCeConnection gt  query       5700ms     dt   Read2 lt SqlCeConnection  SqlCeDataAdapter gt  query       5700ms      dt   Read1 lt SqlConnection gt  query       850ms     dt   Read2 lt SqlConnection  SqlDataAdapter gt  query       600ms      dt   Read1 lt VistaDBConnection gt  query       3900ms     dt   Read2 lt VistaDBConnection  VistaDBDataAdapter gt  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

User · Answer

var table   new DataTable        using  var da   new SqlDataAdapter  SELECT   FROM mytable    connection string                da Fill table

[c#] Read SQL Table into C# DataTable

Examples related to c#

Examples related to .net

Examples related to asp.net

Examples related to sql

Examples related to datatable