欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-05-18 15:20:33
DtatTable 在命名空间System.Data下,NetCore2.0及以上支持。但是2017DataTable没有可视化工具,我也没有深研究直接下载的VS2019。然后在网上早了个SQLHelper直接用上了。 示例代码:https://github.com/TengshengHou/Net ......

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;
        }

    }

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

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