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

    }