I have a simple .Net Framework routine which runs a query and returns a DataTable object. I need to port this to .Net Core, however I infer that SQLAdapter and DataTable are not supported
SqlConnection con = new SqlConnection(m_ConnectString);
SqlCommand cmd = new SqlCommand(strQuery);
SqlDataAdapter sda = new SqlDataAdapter();
// assign the transaction and connection to the command object
cmd.Connection = con;
sda.SelectCommand = cmd;
DataTable dt = new DataTable();
// execute query and soak up results
sda.Fill(dt);
return dt;
Can anyone suggest how I can reimplement this code, using what is supported ? Thanks
SqlDBAdapter
and DataTable
are now supported.
You must use VS2017 Preview 15.3, target .net core 2.0, and add NuGet packages for System.Data.Common
as well as System.Data.SqlClient
. Code below.
See https://blogs.msdn.microsoft.com/devfish/2017/05/15/exploring-datatable-and-sqldbadapter-in-asp-net-core-2-0/ for more info.
public static DataTable ExecuteDataTable(SqlConnection conn, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
{
DataTable dt = new DataTable();
dt.Columns.Add("CustomerID");
dt.Columns.Add("CustomerName");
SqlDataReader dr = ExecuteReader(conn, cmdType, cmdText, cmdParms);
while (dr.Read())
{
dt.Rows.Add(dr[0], dr[1]);
}
return dt;
}
public static DataTable ExecuteDataTableSqlDA(SqlConnection conn, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
{
System.Data.DataTable dt = new DataTable();
System.Data.SqlClient.SqlDataAdapter da = new SqlDataAdapter(cmdText, conn);
da.Fill(dt);
return dt;
}
UPDATE: This answer corresponds to .NET Core 1.x (which was the latest at the time I wrote this). If you are using .NET Core 2.x (in beta as of July/2017), check Joe's answer.
Original answer:
Recommended read: Porting to .NET Core
I quote:
- System.Data. While the base layer is already part of .NET Core, i.e. the provider model and SQL client, some features are currently not available, such as schema support and DataTable/DataSet.
You can use SqlDataReader but not SqlAdapter or DataTable.
Start by adding System.Data.SqlClient
NuGet Package.
Then you can...
var con = new SqlConnection("...");
var cmd = con.CreateCommand();
cmd.CommandText = "...";
var reader = cmd.ExecuteReader();
// populate your custom data structure
Does IList<Dictionary<string, object>>
works for you?
var results = new List<Dictionary<string, object>>();
while (reader.Read())
{
results.Add(Enumerable.Range(0, reader.FieldCount).ToDictionary(reader.GetName, reader.GetValue));
}
return results;
So now you can read using results[0]["FirstName"].ToString()
Unless you want to switch to Entity Framework Core, in that case this tutorial is handy.
Instead of DataAdapter/DataTable you may use one of the existing DAL libraries for .NET Core that support CRUD operations over low-level ADO.NET interfaces. Recently I've published NReco.Data: provider-independent DAL that supports automatic SQL statements generation, abstract queries and simple record CRUD operations.
For example, code snippet from the question can be reimplemented in the following way:
var con = new SqlConnection(m_ConnectString);
var dbFactory = new NReco.Data.DbFactory(
System.Data.SqlClient.SqlClientFactory.Instance);
var dbCmdBuilder = new NReco.Data.DbCommandBuilder(dbFactory);
var dbAdapter = new NReco.Data.DbDataAdapter(con, dbCmdBuilder);
var selectRecordsList = dbAdapter.Select(
new Query("some_table") ).ToList<Dictionary<string,object>>();
Complex SQL queries may be executed as application-level data views:
dbCmdBuilder.Views["some_view"] = new DbDataView(
@"SELECT @columns FROM Employee emp
LEFT JOIN Company c ON (c.Id=emp.CompanyId)
@where[ WHERE {0}] @orderby[ ORDER BY {0}]
") {
FieldMapping = new Dictionary<string,string>() {
{"Id", "emp.Id"},
{"*", "emp.*, c.Title as CompanyTitle"}
}
};
var someViewRS = dbAdapter.Select( new Query("some_view") ).ToRecordSet();
NReco.Data doesn't try to replace SQL with the its own Query (like LINQ does); instead of that it allows you to make simple DB-independent queries from business logic and encapsulate complex SQL syntax with special app-level dataviews that accessed like read-only tables.
Also it is possible to specify raw SQL query directly with Select
method overload (like FromSql in EF Core):
var userModels = dbAdapter.Select("select * from users where id={0}", 5).ToList<User>();
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With