A library for easily calling Stored Procedures in .NET. Works great with Entity Framework Code First models. Code Only Stored Procedures will not create any Stored Procedures on your database. Instead, its aim is to make it easy to call your existing stored procedures by writing simple code.
This library is released via NuGet. You can go to the CodeOnlyStoredProcedures NuGet Page for more information on releases, or just grab it:
Install-Package CodeOnlyStoredProcedures
There are a number ways you can use this library to make working with stored procedures easier. First, you're going to need a connection to a Database. If you're using Entity Framework, you can grab it from the Database.Connection on your DbContext. Otherwise, you can create a new connection however you want. Once you have an IDbConnection db, you can start executing those stored procs:
The easiest way is to use the dynamic syntax:
IEnumerable<Person> people = db.Execute().usp_GetPeople();They will return a tuple:
Tuple<IEnumerable<Person>, IEnumerable<Family>> results = db.Execute().usp_GetFamilies();Or, if you want to use the fluent syntax:
var results = StoredProcedure.Create("usp_GetFamilies")
.WithResults<Person, Family>()
.Execute(connection);The library will try to build the hierarchies for you, by following these rules
- The parent model should contain an enumerable property with the child type
- It can be an array -
Child[] - It can be any generic enumerable type -
IEnumerable<Child>,IList<Child>,ICollection<Child>, etc.
- Each model should have a property named
Idor{ClassName}Id
- If the property isn't named Id or
{ClassName}Id, you can decorate the property that should be used as the Id with the KeyAttribute -[Key] MyId { get; set; }
- The child model should have a property named
{ParentClass}Id
- If the child's foreign key isn't
{ParentClass}Id, you should decorate the enumerable child property with the ForeignKeyAttribute -[ForeignKey("MyParentId")] IEnumerable<Child> Children { get; set; }
For example, these would all work:
public class Parent
{
public int Id { get; set; }
public IEnumerable<Child> Children { get; set; }
}
public class Child
{
public int ParentId { get; set; }
}public class Parent
{
[Key]
public int Property { get; set; }
public IEnumerable<Child> Children { get; set; }
}
public class Child
{
public int ParentId { get; set; }
}public class Parent
{
public int Id { get; set; }
[ForeignKey("ParentPropertyKey")]
public IEnumerable<Child> Children { get; set; }
}
public class Child
{
public int ParentPropertyKey { get; set; }
}You can then get the hierarchical items like so: IEnumerable<Parent> res = db.Execute().usp_GetParentsAndChildren();
You can declare the order of the result sets using the fluent syntax:
IEnumerable<Second> res = StoredProcedure.Create("usp_GetHierarchy")
.WithResults<First, Second, Third>()
.AsHierarchical<Second>()
.Execute(connection);Just use ExecuteAsync it that way.
Task<IEnumerable<Person>> task = db.ExecuteAsync().usp_GetPeople();Using .NET 4.5 (or the Async NuGet package in 4.0)? That's easy too.
IEnumerable<Person> people = await db.Execute().usp_GetPeople();Even easier. Your interface is basically doing the work for you.
public IEnumerable<Person> GetPeople()
{
return this.db.Execute().usp_GetPeople();
}
public Task<IEnumerable<Person>> GetPeopleAsync()
{
return this.db.ExecuteAsync().usp_GetPeople();
}All those tasks do need to be cancellable. So, pass your token
public Task<IEnumerable<Person>> GetPeopleAsync(CancellationToken token)
{
return this.db.ExecuteAsync(token).usp_GetPeople();
}Sometimes, you need to have a longer execution timeout. Just tell us how many seconds you need.
IEnumerable<Widget> widgets = db.Execute(3600).sp_getAllTheWidgetsInTheWorld();Okay, so send it in!
IEnumerable<Widget> widgets = db.Execute().sp_getWidgets(weight: 42, name: "Frob");Those work. So do Input/Output parameters (you know these keywords, right?).
int count;
int smallest = 15;
db.Execute().sp_getWidgetCount(count: out count, smallest_widget: ref smallest);This one isn't that tough. It is just an out parameter with a special (case-INseNsiTivE) name.
int count;
db.Execute().sp_getWidgetCount(ReturnValue: out count);They can't. At least, not the simple way. You can declare a helper class though.
private class WidgetParameters
{
[StoredProcedureParameter(Direction = ParameterDirection.ReturnValue)]
public int Count { get; set; }
[StoredProcedureParameter("smallest_widget", Direction = ParameterDirection.ReturnValue)]
public int Smallest { get; set; }
}
var input = new WidgetParameters { Smallest = 15 };
await db.ExecuteAsync().sp_getWidgetCount(input);Some people hate magically typed C#. I get it. Luckily, you can use magic strings instead!
var sp = new StoredProcedure<Person>("dbo", "usp_GetPeople");
var people = sp.Execute(db);I aim to please. There is a fluent syntax also.
var people = StoredProcedure.Create("usp_getPeople")
.WithResults<People>()
.Execute(db);Either the fluent syntax or the class syntax can execute asynchronously:
Task<IEnumerable<Person>> StoredProcedure.Create("usp_getPeople")
.WithResults<People>()
.ExecuteAsync(token); // yep, you an cancel it!Well, it isn't as easy, but still pretty simple.
It is important to know that a StoredProcedure is immutable. This means that if you call any of the following methods on an instance of the class, the original reference will not be modified.
var people = StoredProcedure.Create("usp_getPeople")
.WithResults<People>()
.WithParameter("name", "Bob")
.Execute(db);You must pass an Action<int> (be careful if you call this asynchronously that you don't access the result until after the task completes).
int retVal;
StoredProcedure.Create("sp_getWidgetCount")
.WithReturnValue(rv => retVal = rv)
.Execute(db);Yep, and you can use them similarly to return values.
int count, smallest;
StoredProcedure.Create("sp_getWidgetCount")
.WithOutputParameter("count", i => count = i)
.WithInputOutputParameter("smallest_widget", 15, i => smallest = i)
.Execute(db);You can create an input class like with the dynamic syntax. It might be easier to pass out or in/out parameters this way.
private class WidgetParameters
{
[StoredProcedureParameter(Direction = ParameterDirection.ReturnValue)]
public int Count { get; set; }
[StoredProcedureParameter("smallest_widget", Direction = ParameterDirection.ReturnValue)]
public int Smallest { get; set; }
}
var input = new WidgetParameters { Smallest = 15 };
StoredProcedure.Create("sp_getWidgetCount")
.WithInput(input)
.Execute(db);Sometimes, databases return data in a form that isn't quite what you want. These help correct that. You can apply them to model properties individually
public class Person
{
[Trim]
public string FirstName { get; set; }
[Trim, Intern]
public string LastName { get; set; }
}Or, if you want to apply the same transformer to all properties of a type
var people = StoredProcedure.Create("usp_getPeople")
.WithResults<People>()
.WithDataTransformer(new TrimAllStringsTransformer())
.Execute(db);Currently, there are transformers for interning strings, trimming strings, and automatically converting numeric types (like double to decimal). These are extensible, in case you have additional use cases.
You can rename them on your model.
public class Person
{
[Column("ResultId")]
public int Id { get; set; }
}Sure, you can again choose between dynamic or fluent syntax. This is an example in fluent:
IEnumerable<InputRow> rows = ...;
StoredProcedure.Create("dbo", "usp_TakesLotsOfInput")
.WithTableValuedParameter("parameterName", rows, "schemaOfTable", "typeOfTable")
.Execute(dbConnection);
[TableValuedParameter(Schema = "schemaOfTable", TableName = "typeOfTable")]
public class InputRow { ... }