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