Fast, lightweight and strongly typed library to query stored procedures. It offers easy and simple to use strongly typed API, supports TVPs as well as multiple result sets.
- Install-Package SqlQuery
- Open SqlQuery.tt and setup connection string, ie.
sqlQuery.ConnectionString = "Server=.;Database=MyDatabaseName;Trusted_Connection=True;";- Add your Stored Procedure name and save file, to collect result sets T4 will execute stored proc in transaction and roll it back
.Add(new SqlProc("dbo.GetAllMakesAndModels").ResultSets("Make", "Model"))- Setup is now done, you can now query stored proc:
var result = Sql.DbRepository.DboGetAllMakesAndModels();
result.Makes[0].MakeName; // contains your first result set
result.Models[0].ModelName // contains your second result setRun & voilla, you just queried database.
Code below declares dbo.GetAllMakesAndModels stored proc and names result sets "Make" and "Model" respectively. Naming result sets is optional, T4 will create collections named Set1, Set2, Set3 and so on.
sqlQuery.Add(new SqlProc("dbo.GetAllMakesAndModels").ResultSets("Make", "Model"));All parameters are discovered automatically with classes generated for TVP (with strongly typed constructor - in case database definition changes your code will not compile and highlight all touch points to fix), for example to add models declare this in SqlProcs.tt:
sqlQuery.Add(new SqlProc("dbo.AddModels"));Then in your CS file use:
var models = new List<AddModelsTvp>
{
new AddModelsTvp("RS5", 4000, true), // strongly typed constructor
new AddModelsTvp("Q7", 3000, true)
};
Sql.DbRepository.DboAddModels(1, models); // strongly typed proc parametersAnd we added 2 new Audi models.
Some procs may require specific parameter(s) to work and return result sets, you can specify them using SetParameterValue, they are output "as specified":
sqlQuery.Add(new SqlProc("dbo.AddModels").SetParameterValue("makeId", "1"));By default all properties on result classes are non nullable to make it easier to consume data. Any nullable column will be replaced with default(myType). If you want to return nullable items declare them explicitly by adding semicolon after result set and listing column names:
sqlQuery.Add(new SqlProc("dbo.GetAllMakesAndModels").ResultSets("Make", "Model;EngineCapacityCc;IsManufactured"));Now EngineCapacityCc and IsManufactured in "Model" result set are nullable int? and bool? respectively:
var result = await Sql.DbRepository.DboGetAllMakesAndModelsAsync();
var hasEngineCc = result.Models.First().EngineCapacityCc.HasValue ? "Has engine capacity" : "doesn't";You can also execute arbitrary SQL instead of stored procedure, only requirement is to pass "name" for calling purposes, for example:
sqlQuery.Add(new SqlRawQuery("GetAllTablesInDatabase", "SELECT TOP 10 * FROM sys.tables").ResultSets("Table"));Then call in the same was as sproc, using passed "name":
var allTables = (await DAO.Sql.DbRepository.GetAllTablesInDatabaseAsync()).Tables;To change connection string use static method below or integrate into Dependency Injection:
Sql.ConnectionString = "Server=MyAddress;Database=MyDatabaseName;Trusted_Connection=True;";Recommended approach for bigger applications is to integrate with your IoC container, sample for NInject:
public class MyModule : NinjectModule
{
public override void Load()
{
Bind<ISqlExecuteConnectionManager>().To<SqlConnectionManager>();
Bind<ISqlExecute>().To<SqlExecute>();
Bind<IDbRepository>().To<DbRepository>();
}
}You need to implement SqlConnectionManager, easiest option is to inherit from generated SqlExecuteConnectionManager class:
public class SqlConnectionManager : SqlExecuteConnectionManager
{
public override string GetConnectionString()
{
return "Server=MyAddress;Database=MyDatabaseName;Trusted_Connection=True;";
}
}If you want more control then you can implement interface instead:
public class SqlConnectionManager : ISqlExecuteConnectionManager
{
public string GetConnectionString() => System.Configuration.ConfigurationManager.ConnectionStrings["MainDatabase"].ConnectionString;
public SqlConnection GetSqlConnection() => new SqlConnection(GetConnectionString());
}In real life you'll likely need Async queries, DbRepository has Async versions for each proc returning Task (remember to check IsFaulted and inspect Exception in continuations):
static async Task QueryAsync()
{
var result = await Sql.DbRepository.DboGetAllMakesAndModelsAsync();
result.Makes.ForEach(make =>
Console.WriteLine(string.Join("\n",
result.Models
.Where(model => model.MakeId == make.MakeId)
.Select(model => $"{make.MakeName} {model.ModelName}"))));
}Sometimes you need extra property or method to aggregate or interpret results. This can be done thanks to partial classes:
public partial class DboGetAllMakesAndModelsResult
{
public partial class Model
{
public bool IsEngineOver2000cc => this.EngineCapacityCc > 2000;
}
}- Generating dictionaries from database as Enum in code
- Handling timeouts and deadlocks
Should you encounter a problem try to open SqlQuery.tt and select "Current Document" in "Error list". This will show only issues related to SqlQuery instead of flooding window with large numbers of errors caused by side effects.
Remember TT will only regenerate if you save SqlProcs.tt or "Run Custom Tool" in Visual Studio. Any suggestions are welcome.
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent