上使用
我们如何从.net core 不使用实体框架连接到SQL Server？
您可以简单地使用使用SqlConnection
这是一个示例
public class BaseDataAccess
{
protected string ConnectionString { get; set; }
public BaseDataAccess()
{
}
{
public BaseDataAccess(string connectionString)
private SqlConnection GetConnection()
this.ConnectionString = connectionString;
}
{
if (connection.State != ConnectionState.Open)
SqlConnection connection = new SqlConnection(this.ConnectionString);
connection.Open();
return connection;
SqlCommand command = new SqlCommand(commandText, connection as SqlConnection);
}
protected DbCommand GetCommand(DbConnection connection, string commandText, CommandType commandType)
{
protected SqlParameter GetParameter(string parameter, object value)
command.CommandType = commandType;
return command;
}
{
parameterObject.Direction = ParameterDirection.Input;
SqlParameter parameterObject = new SqlParameter(parameter, value != null ? value : DBNull.Value);
return parameterObject;
}
SqlParameter parameterObject = new SqlParameter(parameter, type); ;
protected SqlParameter GetParameterOut(string parameter, SqlDbType type, object value = null, ParameterDirection parameterDirection = ParameterDirection.InputOutput)
{
if (type == SqlDbType.NVarChar || type == SqlDbType.VarChar || type == SqlDbType.NText || type == SqlDbType.Text)
{
}
parameterObject.Size = -1;
}
parameterObject.Direction = parameterDirection;
if (value != null)
{
parameterObject.Value = value;
}
else
{
parameterObject.Value = DBNull.Value;
}
return parameterObject;
DbCommand cmd = this.GetCommand(connection, procedureName, commandType);
protected int ExecuteNonQuery(string procedureName, List<DbParameter> parameters, CommandType commandType = CommandType.StoredProcedure)
{
int returnValue = -1;
try
{
using (SqlConnection connection = this.GetConnection())
{
if (parameters != null && parameters.Count > 0)
{
cmd.Parameters.AddRange(parameters.ToArray());
}
using (DbConnection connection = this.GetConnection())
returnValue = cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
//LogException("Failed to ExecuteNonQuery for " + procedureName, ex, parameters);
throw;
}
return returnValue;
}
protected object ExecuteScalar(string procedureName, List<SqlParameter> parameters)
{
object returnValue = null;
try
{
{
}
DbCommand cmd = this.GetCommand(connection, procedureName, CommandType.StoredProcedure);
if (parameters != null && parameters.Count > 0)
{
cmd.Parameters.AddRange(parameters.ToArray());
}
returnValue = cmd.ExecuteScalar();
}
}
catch (Exception ex)
{
//LogException("Failed to ExecuteScalar for " + procedureName, ex, parameters);
throw;
return returnValue;
}
ds = cmd.ExecuteReader(CommandBehavior.CloseConnection);
protected DbDataReader GetDataReader(string procedureName, List<DbParameter> parameters, CommandType commandType = CommandType.StoredProcedure)
{
DbDataReader ds;
try
{
DbConnection connection = this.GetConnection();
{
DbCommand cmd = this.GetCommand(connection, procedureName, commandType);
if (parameters != null && parameters.Count > 0)
{
cmd.Parameters.AddRange(parameters.ToArray());
}
}
}
catch (Exception ex)
{
}
//LogException("Failed to GetDataReader for " + procedureName, ex, parameters);
throw;
}
return ds;
}
可以在这里找到更多
update
您必须添加Nuget软件包
Install-Package System.Data.SqlClient
that is still confusing for me... .Net Core & .Net standard vs regular .Net: How do we know which packages we can use with .Net core?
依赖项意味着您应该在计算机上安装的内容以使用软件包或Nuget将为您安装要了解更多依赖性在.net中的工作方式，请查看 here
注意如果Nuget软件包目标.net standard库主要在.NET Core和.NET标准框架
如果您对另一个答案中的BaseDataAccess类格式感到惊讶，并且引用了与我相同的文章，则这里是格式化的示例...希望它可以节省一些时间
public class BaseDataAccess
{
protected string ConnectionString { get; set; }
public BaseDataAccess()
{
}
public BaseDataAccess(string connectionString)
{
this.ConnectionString = connectionString;
}
private SqlConnection GetConnection()
{
SqlConnection connection = new SqlConnection(this.ConnectionString);
if (connection.State != ConnectionState.Open)
connection.Open();
return connection;
}
protected DbCommand GetCommand(DbConnection connection, string commandText, CommandType commandType)
{
SqlCommand command = new SqlCommand(commandText, connection as SqlConnection);
command.CommandType = commandType;
return command;
}
protected SqlParameter GetParameter(string parameter, object value)
{
SqlParameter parameterObject = new SqlParameter(parameter, value != null ? value : DBNull.Value);
parameterObject.Direction = ParameterDirection.Input;
return parameterObject;
}
protected SqlParameter GetParameterOut(string parameter, SqlDbType type, object value = null, ParameterDirection parameterDirection = ParameterDirection.InputOutput)
{
SqlParameter parameterObject = new SqlParameter(parameter, type); ;
if (type == SqlDbType.NVarChar || type == SqlDbType.VarChar || type == SqlDbType.NText || type == SqlDbType.Text)
{
parameterObject.Size = -1;
}
parameterObject.Direction = parameterDirection;
if (value != null)
{
parameterObject.Value = value;
}
else
{
parameterObject.Value = DBNull.Value;
}
return parameterObject;
}
protected int ExecuteNonQuery(string procedureName, List<DbParameter> parameters, CommandType commandType = CommandType.StoredProcedure)
{
int returnValue = -1;
try
{
using (SqlConnection connection = this.GetConnection())
{
DbCommand cmd = this.GetCommand(connection, procedureName, commandType);
if (parameters != null && parameters.Count > 0)
{
cmd.Parameters.AddRange(parameters.ToArray());
}
returnValue = cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
//LogException("Failed to ExecuteNonQuery for " + procedureName, ex, parameters);
throw;
}
return returnValue;
}
protected object ExecuteScalar(string procedureName, List<SqlParameter> parameters)
{
object returnValue = null;
try
{
using (DbConnection connection = this.GetConnection())
{
DbCommand cmd = this.GetCommand(connection, procedureName, CommandType.StoredProcedure);
if (parameters != null && parameters.Count > 0)
{
cmd.Parameters.AddRange(parameters.ToArray());
}
returnValue = cmd.ExecuteScalar();
}
}
catch (Exception ex)
{
//LogException("Failed to ExecuteScalar for " + procedureName, ex, parameters);
throw;
}
return returnValue;
}
protected DbDataReader GetDataReader(string procedureName, List<DbParameter> parameters, CommandType commandType = CommandType.StoredProcedure)
{
DbDataReader ds;
try
{
DbConnection connection = this.GetConnection();
{
DbCommand cmd = this.GetCommand(connection, procedureName, commandType);
if (parameters != null && parameters.Count > 0)
{
cmd.Parameters.AddRange(parameters.ToArray());
}
ds = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
}
catch (Exception ex)
{
//LogException("Failed to GetDataReader for " + procedureName, ex, parameters);
throw;
}
return ds;
}
}
这是ASP.NET MVC Core 3.1在Visual Studio 2019社区版中测试的项目的解决方案。
在SQL Express中创建一个小数据库。
然后将几行添加到 appsettings.json 的连接字符串：
"ConnectionStrings": {
//PROD on some server
"ProdConnection": "Server=somePRODServerofYours;Database=DB_xxxxx_itemsubdb;User Id=DB_xxxxx_user;Password=xxsomepwdxx;Integrated Security=false;MultipleActiveResultSets=true;encrypt=true",
//DEV on localhost
"DevConnection": "Server=someDEVServerofYours;Database=DB_xxxxx_itemsubdb;User Id=DB_xxxxx_user;Password=xxsomepwdxx;Integrated Security=false;MultipleActiveResultSets=true;"
},
然后使用与控制器中的以下代码相似的代码..
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Hosting;
using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Configuration;
using System.Data.SqlClient;
using System.Data;
namespace SomeNameSpace.Controllers
{
//This Model class should be saved somewhere else in your project.
//It is placed here for simplicity.
public class XtraSimpleContent
{
public string UserName { get; set; }
public string References { get; set; }
public string CreatedTime { get; set; }
}
public class CodeNotesController : Controller
{
public IConfiguration Configuration { get; }
public string connStr = String.Empty;
public CodeNotesController(IConfiguration configuration, IWebHostEnvironment env)
{
Configuration = configuration;
if (env.IsDevelopment())
{
connStr = Configuration.GetConnectionString("DevConnection");
}
else
{
connStr = Configuration.GetConnectionString("ProdConnection");
}
}
[HttpGet]
public async Task<IActionResult> CodeActionMethodToConnectToSQLnetCore()
{
//add using System.Data.SqlClient;
// using System.Data;
//Along with the using statements, you need the system assembly reference.
//To add assembly you can do the following.
// install nuget package. Right Click on Project > Manage Nuget Packages >
// Search & install 'System.Data.SqlClient' and make sure it is compatible with the type of project (Core/Standard);
List<XtraSimpleContent> aListOfItems = new List<XtraSimpleContent>();
string commandText = @"SELECT * FROM [dbo].[ItemSubmissions]
WHERE SUBMITTEREMAIL = @SUBMITTEREMAIL
ORDER BY CreationDatetime DESC";
using (var connection = new SqlConnection(connStr))
{
await connection.OpenAsync(); //vs connection.Open();
using (var tran = connection.BeginTransaction())
{
using (var command = new SqlCommand(commandText, connection, tran))
{
try
{
command.Parameters.Add("@SUBMITTEREMAIL", SqlDbType.NVarChar);
command.Parameters["@SUBMITTEREMAIL"].Value = "me@someDomain.org";
SqlDataReader rdr = await command.ExecuteReaderAsync(); // vs also alternatives, command.ExecuteReader(); or await command.ExecuteNonQueryAsync();
while (rdr.Read())
{
var itemContent = new XtraSimpleContent();
itemContent.UserName = rdr["RCD_SUBMITTERNAME"].ToString();
itemContent.References = rdr["RCD_REFERENCES"].ToString();
itemContent.CreatedTime = rdr["CreationDatetime"].ToString();
aListOfItems.Add(itemContent);
}
await rdr.CloseAsync();
}
catch (Exception Ex)
{
await connection.CloseAsync()
string msg = Ex.Message.ToString();
tran.Rollback();
throw;
}
}
}
}
string totalinfo = string.Empty;
foreach (var itm in aListOfItems)
{
totalinfo = totalinfo + itm.UserName + itm.References + itm.CreatedTime;
}
return Content(totalinfo);
}
}
}
用类似：
进行测试 https://localhost:44302/CodeNotes/CodeActionMethodToConnectToSQLnetCore
with ukrguru.sqljson package
AppSettings.json：
"ConnectionStrings": {
"SqlJsonConnection": "Server=localhost;Database=SqlJsonDemo;Integrated Security=SSPI"
}
startup.cs
services.AddSqlJson(Configuration.GetConnectionString("SqlJsonConnection"));
dbcontroller.cs
[ApiController]
[Route("api/[controller]")]
public class DbController : ControllerBase
{
private readonly string _prefix = "api.";
private readonly DbService _db;
public DbController(DbService db) => _db = db;
[HttpGet("{proc}")]
public async Task<string> Get(string proc, string data = null)
{
try
{
return await _db.FromProcAsync($"{_prefix}{proc}", data);
}
catch (Exception ex)
{
return await Task.FromResult($"Error: {ex.Message}");
}
}
[HttpPost("{proc}")]
public async Task<dynamic> Post(string proc, [FromBody] dynamic data = null)
{
try
{
return await _db.FromProcAsync<dynamic>($"{_prefix}{proc}",
(object)data == null ? null : data);
}
catch (Exception ex)
{
return await Task.FromResult($"Error: {ex.Message}");
}
}
}