我读了很多关于将DataTable插入到SQL表中的帖子,但是如何将SQL表拉入C#/.NET DataTable呢?
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();
}
}
da.Fill(dataTable)
之前,必须先初始化datatable
字段。 - Dabblernltry/catch
或 using()
来处理错误。 - Si8var table = new DataTable();
using (var da = new SqlDataAdapter("SELECT * FROM mytable", "connection string"))
{
da.Fill(table);
}
using
的完全等效方式,你可能就不会那么厌恶它了。 - Ben VoigtUsing
呢?这就像鄙视 With
或者 Try-Catch
一样。我恰恰相反,如果一个类不支持 Using
,我会感到失望。 - SteveCinqusing (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);
}
// create data adapter
SqlDataAdapter da = new SqlDataAdapter(cmd);
// this will query your database and return the result to your datatable
da.Fill(dataTable);
厂商无关版本,仅依赖于ADO.NET接口; 有两种方式:
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];
}
}
}
}
我进行了一些性能测试,第二种方法总是比第一种更出色。
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
看起来更加舒适,但数据适配器执行效率更高(不要混淆一个数据库优于另一个数据库,因为查询都是不同的)。两者之间的差异取决于查询。原因可能是 Load
方法需要检查每一行的各种约束条件(来自文档),而 Fill
方法位于 DataAdapter 上,它们专门用于快速创建 DataTable。请注意保留 HTML 标签。集中式模型:您可以从任何地方使用它!
您只需要从您的函数调用以下格式到这个类
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);
就是这样。它是完美的方法。
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(sql, connectionString);
da.Fill(table);