NetCore 下使用 DataTable 以及可视化工具

dtattable 在命名空间system.data下，netcore2.0及以上支持。但是2017datatable没有可视化工具，我也没有深研究直接下载的vs2019。然后在网上早了个sqlhelper直接用上了。

示例代码：https://github.com/tengshenghou/netcoresample/tree/master/sqlhelperdemo

sqlhelper

 

public class sqlhelper
    {
        iconfiguration _configuration;
        private string _connectionstring;
        public sqlhelper(iconfiguration configuration)
        {
            _configuration = configuration;
            _connectionstring = _configuration.getconnectionstring("defaultconnection");
        }

        public int executenonquery(sqlconnection conn, string cmdtext, sqlparameter[] cmdparms, sqltransaction trans)

        {
            return executenonquery(conn, commandtype.text, cmdtext, cmdparms, trans);
        }

        public int executenonquery(sqlconnection conn, commandtype cmdtype, string cmdtext, sqlparameter[] cmdparms, sqltransaction trans)
        {
            sqlcommand cmd = conn.createcommand();
            int val = 0;
            using (cmd)
            {
                preparecommand(cmd, conn, trans, commandtype.text, cmdtext, cmdparms);
                val = cmd.executenonquery();
                cmd.parameters.clear();
            }
            return val;
        }

        public sqldatareader executereader(sqlconnection conn, commandtype cmdtype, string cmdtext, sqlparameter[] cmdparms)
        {
            sqlcommand cmd = conn.createcommand();
            preparecommand(cmd, conn, null, cmdtype, cmdtext, cmdparms);
            var rdr = cmd.executereader(commandbehavior.closeconnection);
            return rdr;
        }


        public datatable executedatatable(sqlconnection conn, commandtype cmdtype, string cmdtext, sqlparameter[] cmdparms)
        {
            system.data.datatable dt = new datatable();
            sqlcommand cmd = conn.createcommand();
            preparecommand(cmd, conn, null, cmdtype, cmdtext, cmdparms);
            system.data.sqlclient.sqldataadapter da = new sqldataadapter(cmd);
            da.fill(dt);
            return dt;
        }

        public object executescalar(sqlconnection conn, commandtype cmdtype, string cmdtext, sqlparameter[] cmdparms)
        {
            sqlcommand cmd = conn.createcommand();
            preparecommand(cmd, conn, null, cmdtype, cmdtext, cmdparms);
            object val = cmd.executescalar();
            cmd.parameters.clear();
            return val;
        }

        #region private
        private void preparecommand(sqlcommand cmd, sqlconnection conn, sqltransaction trans, commandtype cmdtype, string cmdtext, sqlparameter[] commandparameters)
        {
            if (conn.state != connectionstate.open)
            {
                conn.open();
            }
            //cmd.connection = conn;
            cmd.commandtext = cmdtext;
            if (trans != null)
            {
                cmd.transaction = trans;
            }
            cmd.commandtype = cmdtype;
            //attach the command parameters if they are provided
            if (commandparameters != null)
            {
                attachparameters(cmd, commandparameters);
            }
        }
        private void attachparameters(sqlcommand command, sqlparameter[] commandparameters)
        {
            foreach (sqlparameter p in commandparameters)
            {
                //check for derived output value with no value assigned
                if ((p.direction == parameterdirection.inputoutput) && (p.value == null))
                {
                    p.value = dbnull.value;
                }
                command.parameters.add(p);
            }
        }
        #endregion

        #region  default connection
        public datatable executedatatable(string cmdtext, sqlparameter[] cmdparms)
        {
            datatable datatable;
            using (var sqlconnection = getdefaultsqlconnection())
            {
                datatable = this.executedatatable(sqlconnection, commandtype.text, cmdtext, cmdparms);
            }
            return datatable;
        }

        public int executenonquery(string cmdtext, sqlparameter[] cmdparms)
        {
            int rowsaffected;
            using (var sqlconnection = getdefaultsqlconnection())
            {
                rowsaffected = executenonquery(sqlconnection, cmdtext, cmdparms, null);
            }
            return rowsaffected;
        }

        public object executescalar(string cmdtext, sqlparameter[] cmdparms)
        {
            object retobj;
            using (var sqlconnection = getdefaultsqlconnection())
            {

                retobj = executescalar(cmdtext, cmdparms);
            }
            return retobj;
        }
        #endregion

        public sqlconnection getdefaultsqlconnection()
        {
            return new sqlconnection(_connectionstring);
        }

    }

添加服务

startup->configureservices

services.addtransient(typeof(sqlhelper));

调用测试

    [route("api/[controller]")]
    [apicontroller]
    public class valuescontroller : controllerbase
    {
        sqlhelper _sqlhelper;
        public valuescontroller(sqlhelper sqlhelper)
        {
            _sqlhelper = sqlhelper;
        }
        // get api/values
        [httpget]
        public actionresult<datatable> get()
        {
            return _sqlhelper.executedatatable("select * from sysuser", null);
        }


        // get api/update
        [httpget("update")]
        public actionresult<int> update()
        {
            sqlparameter[] cmdparms = new sqlparameter[] { new sqlparameter("@password", dbnull.value) };
            return _sqlhelper.executenonquery("update sysuser set password =@password where  loginname='admin'", cmdparms);
        }

        // get api/update
        [httpget("updatetransaction")]
        public actionresult<int> updatetransaction()
        {
            int rowsaffected = 0;
            sqlparameter[] cmdparms = new sqlparameter[] { new sqlparameter("@password", "123456") };
            using (var conn = _sqlhelper.getdefaultsqlconnection())
            {
                conn.open();
                var transaction = conn.begintransaction();
                try
                {
                    rowsaffected = _sqlhelper.executenonquery(conn, "update sysuser set password =@password where  loginname='admin'", cmdparms, transaction);
                    throw new exception("test");
                    transaction.commit();
                }
                catch (exception e)
                {
                    transaction.rollback();
                    throw e;
                }

            }
            return rowsaffected;
        }

    }