jocapc / belgrade-sqlclient Goto Github PK

Small async ADO.NET helper library

Home Page: http://jocapc.github.io/Belgrade-SqlClient/

License: Other

C# 100.00%

belgrade-sqlclient's Introduction

Belgrade SqlClient

Belgrade Sql Client is a lightweight data access library that supports the latest features that are available in SQL Server and Azure SQL Database such as:

JSON, temporal, and graph support
Row-level security with SESSION_CONTEXT

Functions in this library use standard ADO.NET classes such as DataReader and SqlCommand. Library uses these classes in full async mode, providing optimized concurrency. There are no constraints in the term of support of the latest SQL features. Any feature that can be used with Transact-SQL can be used with this library.

This library is used in SQL Server 2016+/Azure SQL Database Sql Server GitHub samples.

Setup
Initializing data access components
Executing command
Mapping results
Streaming results

Setup

You can download source of this package and build your own version of Belgrade Sql Client, or take the library from NuGet. To install Belgrade SqlClient using NuGet, run the following command in the Package Manager Console:

Install-Package Belgrade.Sql.Client

Initializing data access components

The core component in this library is Command. Command is object that executes any T-SQL command or query. In order to initialize Command, you can provide standard SqlConnection to the constructor:

const string ConnString = "Server=<SERVER>;Database=<DB>;Integrated Security=true";
ICommand cmd = new Command(ConnString);

Now you have a fully functional object that you can use to execute queries.

Executing commands

Command has Exec method that executes a query or stored procedure that don't return any results. Exec() is used in update statements, for example:

await cmd.Sql("EXEC dbo.CalculateResults").Exec();

This command will open a connection, execute the procedure, and close the connection when it finishes.

Usually you would need to pass the parameters to some stored procedure when you execute it. You can set the T-SQL query text, add parameters to the command, and execute it.

cmd.Sql("EXEC UpdateProduct @Id, @Product");
cmd.Param("Id", DbType.Int32, id);
cmd.Param("Product", DbType.String, product);
await cmd.Exec();

It is just an async wrapper around standard SqlComand that handles errors and manage connection.

Mapping query results

Map() method enables you to execute any T-SQL query and get results in callback method:

await cmd
        .Sql("SELECT * FROM sys.objects")
        .Map(row => {
                    	// Populate some C# object from the row data.
                    });

You can provide a function that accepts DataReader as an argument and populates fields from DataReader into some object or list.

Streaming results

Stream is a method that executes a query against database and stream the results into an output stream.

await cmd
        .Sql("SELECT * FROM Product FOR JSON PATH")
        .Stream(Response.Body);

Method Stream may accept following parameters:

First parameter is an output stream where results of query will be pushed. This can be response stream of web Http request, output stream that writes to a file, or any other output stream.
Second (optional) parameter is a text content that should be sent to the output stream if query does not return any results. By default, nothing will be sent to output stream if there are not results form database. Usually default content that should be sent to output stream is an empty array "[]" or empty object "{}".

More info

Belgrade SqlClient has some additional functionalities such as error handling, logging, retrying errors, etc. You can find more informaiton about other features in documentation.

belgrade-sqlclient's People

Contributors

Stargazers

Watchers

Forkers

ddpruitt vkuttyp ammogcoder mariuszberner mollsandhersh tforsberg vrdate jeffdoolittle ilkhom-s mumairanwaar arielcortez rezanid fredirk supersdar

belgrade-sqlclient's Issues

Problem when use Belgrade-SqlClient with SignalR

When i use Belgrade-SqlClient with SignalR (Microsoft.AspNetCore.SignalR.Server)
I run my API (use Belgrade-SqlClient) so long and not end localhost:5000/api/header/menu ,it always loading.
I use default API (not use Belgrade-SqlClient) it can run without error.

My english is not good. Sorry for mistake.

Performance considration issue

I've developed web API / OData app based on Owin pipeline which is hosted on asp.net core / kestrel using full .net framework
I convert OData query to SQL query, then I execute that SQL query on SQL server 2016. Using SQL server 2016's json support, I produce json result based on OData spec. So it's ready to be sent to client. I use Web API's PushContentStream which provides me a response stream. I use that stream and your amazing library to send that json to client. Everything is fine.
I know that my question is not directly related to your project )-: But could you please tell me does it work for me in a way asp.net core/owin send file feature is working? Send file feature writes file directly to network card, so It's very fast. If I use your library with Web api's push stream content, does it work like send file feature? Does it write SQL db's response to network card directly? If not, can I achieve something like that by developing asp.net core middleware which passes It's stream to your library? Sorry for interruption.

Pass User-Defined Table Types as parameter to Stored Procedure

Hi, is it possible to pass User-Defined Table Types in
await sqlCmd .Proc("SomeStoreProcedure") .Param("aUserDefinedTypeParam", aSqlParameterInstance)?
Thanks

Need to pass appname in connection string

Hi Jovan

Thank you for the nifty tool. In my REST api project, I am using the SqlPipe.Sql(cmd).Stream(ms, "[]") but for every cmd, I need to pass a new connection string with an included application name. Currently, the stream logic in the belgrade sqlclient is using the connectionstring given in the base command created during startup. I need an ability to pass the connection string. I tried to download and build locally with this additional method included but I am unable to proceed with the build due to the following error.

Severity Code Description Project File Line Suppression State
Error MSB3325 Cannot import the following key file: BelgradeSqlClient.pfx. The key file may be password protected. To correct this, try to import the certificate again or manually install the certificate to the Strong Name CSP with the following key container name: VS_KEY_3C6256A74AF3876F Code C:\Program Files (x86)\Microsoft Visual Studio\2017\Professional\MSBuild\15.0\Bin\Microsoft.Common.CurrentVersion.targets 3214

Please help resolve this urgent requirement.

thank you,
Sudhaa

How to execute stored precedure

How to execute stored procedure with parameters for select command and stream the result in response.body

Also how do i pass database name as parameter

Fails: Install-Package Belgrade.Sql.Client

Tried installing it from console and NPM UI. But it fails with below exception.
Environment: VS 2015 - Win7

Install-Package : The underlying connection was closed: An unexpected error occurred on a send.At line:1 char:1

Install-Package Belgrade.Sql.Client

  + CategoryInfo          : NotSpecified: (:) [Install-Package], Exception
  + FullyQualifiedErrorId : NuGetCmdletUnhandledException,NuGet.PackageManagement.PowerShellCmdlets.InstallPackageCommand

An overload to avoid writing direct to response.body

Following your examples:

    [HttpGet]
    [Route("customer_details/{customerid:int}")]
    public async Task customer_details(int customerid)
    {
      await cmd
        .Proc("customer_details")
        .Param("customerid", customerid)
        .Stream(Response.Body, "{}");
    }

Works quite well, but i feel we lose all the other enabled ASP.Net pipeline features like compression, because we are writing directly to the Response.Body

Please is it possible to have an overload that will work like this:

[HttpGet]
[Route("customer_details/{customerid:int}")]
public async Task<ActionResult> customer_details(int customerid)
{
  return Ok(await cmd
        .Proc("customer_details")
        .Param("customerid", customerid)
        .Stream("{}");
}

So we don't exit the ASP.Net pipeline by writing to Response.Body

Also this: https://docs.microsoft.com/en-us/aspnet/core/fundamentals/middleware/request-response?view=aspnetcore-3.0

Thanks, again for this helper library!

QueryMapper select query returns number of rows instead of record

Code below returns string st as "4", instead of a DB record. Same exact query string executed in SSMS returns the record. Var facilityId is int type = 4. QueryMapper always returns the value of facilityId var instead of the record.

                qry = $@"SELECT TOP 1 * FROM dbo.FacilityMaster WHERE id = {facilityId} AND LOWER(status) = 'ok'";
                string st = await (new QueryMapper(ConnectionString)
                    .OnError(ex => { httpResponse += ex.ToString(); httpStatus = HttpStatusCode.InternalServerError; }))
                    .GetStringAsync(qry);
                if (String.IsNullOrEmpty(st))
                {
                    string er = $"Error: No data for query: {qry}";
                    log.Error(er);
                    return new HttpResponseMessage() { Content = new StringContent(er), StatusCode = HttpStatusCode.InternalServerError };
                }

Output parameters

Hi, is there a recommended way to handle stored procedure output parameters?

CLR-Belgrade-SqlClient with .Net framework

Hi,
I trying to use CLR-Belgrade-SqlClient with .net framework :
using System.Web.Http;
using System.Web.Http.Cors;
using Belgrade.SqlClient;
using System.Threading.Tasks;
using System.Data.SqlClient;
using Belgrade.SqlClient.SqlDb;

namespace WebDataLayer.Controllers
{
[EnableCors(origins: "", headers: "", methods: "*")]
[Route("api/[controller]")]

public class MyProductController : ApiController
{
    IQueryPipe sqlQuery = null;
    const string ConnString = "Server=.;Database=PWS;Integrated Security=true";
   
    public MyProductController(IQueryPipe sqlQueryService)
    {
        this.sqlQuery = sqlQueryService;
        //this.sqlQuery = new QueryPipe(new SqlConnection(ConnString));
    }
    // GET api/MyProduct/Load

    public async Task Load()
    {
       await sqlQuery.Stream("select * from Products for json path", Response.Body, "[]");
       
    }
}

}
But Response.Body is causing an error .
Also where can i call my connectionstring ConnString ?
Thanks

Other DB

Would be possible to implement this library for other db systems as MySql or Postgres?

Errors thrown in SqlPipe.OnError do not propagate to middleware Error Handler

I have a Controller method as below:
` [HttpGet]
public async Task NonExistentTable()
{
await SqlPipe
.OnError(ex =>
{
this.Response.StatusCode = 500;
throw ex;
}
)
.Stream("select * from NonExistentTable FOR JSON PATH", Response.Body, "[]");

}`

and in the Startup.cs I have as below in the Configure:
` app.UseExceptionHandler(errorApp =>
{
errorApp.Run(async context =>
{
context.Response.StatusCode = 500; // or another Status accordingly to Exception Type
context.Response.ContentType = "application/json";

                var error = context.Features.Get<IExceptionHandlerFeature>();
                if (error != null)
                {
                    var ex = error.Error;
                    var err = new ErrorDto()
                    {
                        Code = 500,
                        Message = ex.Message // or your custom message
                    }.ToString();

                   await context.Response.Body.WriteAsync(Encoding.ASCII.GetBytes(err), 0, err.Length).ConfigureAwait(false);
                }
            });
        });`

When the NonExistentTable API is called, the exception thrown inside the OnError handler, doesn't get handled in the UseExceptionHandler..

The issue happens only when SqlPipe.Stream is used, any other exception thrown from other methods gets handled as expected..

What am I missing here?