How to connect to SQL Server from .NET Core without using Entity Framework?

First, you have to add the NuGet package System.Data.SqlClient

 Install-Package System.Data.SqlClient 

Then you can use the traditional way, which uses SqlConnection. Here is an example:

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

Copied from here.

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?

Dependencies means that what you should have installed on your machine in order to use the package or NuGet will install it for you. To understand how dependencies work in .NET, take a look here.

Note - if a package targets the .NET Standard library, it will probably mostly work on both .NET core and the .NET Standard framework.

Here is a solution for an ASP.NET MVC Core 3.1 project tested in Visual Studio 2019 community edition.

Create a small database in SQL Express.

Then add a few lines to appsettings.json for the connection strings:

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

Then use code similar to the following in your controller ....

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 = "[email protected]";

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

        }
    }
}

Test it with something like:

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