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 = ;
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 = ;
SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@PASSWORD", "") };
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;
}
}