Datatable class in asp.net core

Question

If you have tried to use SQLClient in asp.net core, you might have noticed the absence of the DataTables and DataSets, tables structures used to I/O of database.

For output data we have the option of SqlDataReader. But for the input data, I'm yet to find a solution to this problem -e.g. if you want pass a table to SP by parameter in framework 461, we use 'SqlDbType = SqlDbType.Structured and DataTable class'. Any ideas anyone?

Library that I use: https://github.com/XML-Travelgate/xtg-data-sqlclient

Not sure how this applies to .net core, but [_"System.Data.SqlClient supports populating table-valued parameters from DataTable, DbDataReader or IEnumerable objects."_](https://msdn.microsoft.com/en-us/library/bb675163(v=vs.110).aspx) - or you can create a custom type on the server with `CREATE TYPE dbo.xxx AS TABLE` — stuartd, Oct 05 '16 at 11:52
.net core doesn't allow you to use those object types, and I need an alternative for asp.net core. Thanks for your answer. — Vicenç Pizà, Oct 05 '16 at 14:07
According to [the relevant issue on .net-core](https://github.com/dotnet/corefx/issues/8622) some of them _are_ available, look at [this comment](https://github.com/dotnet/corefx/issues/8622#issuecomment-223673368) and then [this one](https://github.com/dotnet/corefx/issues/8622#issuecomment-223980960) — stuartd, Oct 05 '16 at 14:31

score 5 · Answer 1 · answered Oct 06 '16 at 10:02

Solution:

        List<SqlDataRecord> datatable = new List<SqlDataRecord>();
        SqlMetaData[] sqlMetaData = new SqlMetaData[2];
        sqlMetaData[0] = new SqlMetaData("id", SqlDbType.Int);
        sqlMetaData[1] = new SqlMetaData("name", SqlDbType.VarChar, 50);
        SqlDataRecord row = new SqlDataRecord(sqlMetaData);
        row.SetValues(new object[] { 1, "John" });
        datatable.Add(row);
        row = new SqlDataRecord(sqlMetaData);
        row.SetValues(new object[] { 2, "Peter" });
        datatable.Add(row);

        var task = dbBase.ExecProcedureDataTableWithParamsAsync<object>("VIEWTABLE", new List<SqlParameter>()
            {
                new SqlParameter()
                {
                     ParameterName = "@paramtable",
                     SqlDbType = SqlDbType.Structured,
                     Direction = ParameterDirection.Input,
                     Value = datatable
                }
            });

score 0 · Answer 2 · answered May 30 '17 at 05:25

0

DataTable, DataSet etc can now be implemented using .NET Core 2.0 and Visual Studio 2017 Preview 15.3 +

answered May 30 '17 at 05:25

Ozesh

6,536
1
25
23

Datatable class in asp.net core

2 Answers2

Linked