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.
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.
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;
}
}
var table = new DataTable();
using (var da = new SqlDataAdapter("SELECT * FROM mytable", "connection string"))
{
da.Fill(table);
}
Source: Stackoverflow.com