Use this for really fast data mappings and type conversions from SQL stored procedures to .NET objects, and vice versa.
This dll uses another dll called FastMember and is compiled using the .NET 4.0 framework.
Download SqlDataLayer.zip (42KB) and add the extracted contents to your project as references.
Also a random FluentExtender appeared!
Provides an SqlConnection so you can manually get the data yourself.
Note: The extension Get<> on the DataReader will be covered later.
Also notice how the DataReader wasn't disposed? Thats because you dont have to dispose it.
The SqlProvider class keeps an internal reference to the DataReader it gives you, and when the SqlProvider object is disposed it will also dispose of the SqlDataReader object.
Notice how the ExecuteScalar takes a default value, so if your query returns null you can give your variable a value anyway; such as:
Also see how the SqlProvider object has a few constructors. So you dont have to build the connection string yourself you can use the SqlConnectionString class.
This is the meat of it, the main reason why you would want to use this framework.
Quick mapping from your procedure result to your model and your model to your procedure.
In your project you create a model that defines one row returned from a stored procedure.
Then you can map the first row returns from your procedure to that model, or if you have many rows; an IEnumerable of that model.
To load a single to model, you must first define your model, as a row of output you expect.
For example I will use the following model.
Note: You will learn about the attributes later, ignorge them for now.
So now you have a model defining the data you want populated, you must match that with a stored procedure that returns the data you want.
Now its as simple as:
In this case the stored procedure returns three rows, but since we called LoadModel we only cared about the first row.
The username and password were returned in the first row, and the output and return value were mapped because the model's properties had the appropriate attribues on them.
You can also load many models if your stored procedure has many rows it returns, for example:
The console looks as following:
Note: The only prestique to get this to work, is to set the connection string:
This can of course be overloaded on the LoadModels method:
Need to pass parameters to your stored procedure to get some data out? No problem.
If your stored procedure takes many parameters, you might be better off creating a request model to store your parameters, then convert that model to an array of SqlParameters.
For example you might create the model:
Then its a simple matter of using the ToSqlParameters() extension method (explained later) on the object:
Below are the signatures for the LoadModel methods:
First thing lets create a stored procedure that takes a model.
The idea here is you create a model that describes the parameters of a procedure.
Here is the output of the procedure when executed:
So lets create a model that describes the procedure's behaviour:
Note: once again the attributes are described later.
Now lets create this model, populate it with data, then save it.
Below are the signatures for the SaveModel methods:
This is a class that formally defines the sql connection string.
Basically it helps you form a valid connection string, without you having to remember the specific format of one.
Here is the class definition:
This class takes both the SqlConnectionString object, as well as user defined connection strings and stores each as a key - value pair.
The class is thread safe, and can handle many connection strings (if you load / save from different databases for example).
Here is the class definition:
If you set a connection string in the SqlLayer.ConnectionStrings property, when you load or save a model,
the SqlLayer class will use the SqlLayer.ConnectionString.Current string as the default.
Below are some example of using ConnectionStrings with the SqlLayer:
There are four attributes in the DataLayer.Sql.Attributes namespace.
These attributes can be put on properties or fields.
Only public members will be read, including inherted public members as well as public static members (where a member is a property or a field).
The SqlAliasAttribute has two different behaviours depending on the context.
If you are loading a model, then the SqlAliasAttribute takes a csv of case insensitive names that the column could be called.
For example, your procedure could return the username as [Username] or [Name] depending on what table it reads from.
Another example is your procedure returns a column as [Username] but your model's member is called CustomerName.
Below is a property that handles all three cases:
The other conext is where you're saving a model.
In this case the SqlAlias doesn't take a csv, but rather a single name.
This name should be the same as the parameter name that your procedure expects.
For example, your procedure expects password, but your model defines a member called CustomerPassword.
The SqlIgnorgeAttribute behaves almost the same in both contexts.
If you are loading a model, and the procedure returns a column with the same name as your member with a SqlIgnorgeAttribute on it, then this column will be skipped.
If you are saving a model, then any member with the SqlIgnorgeAttribute on it will not be sent to the procedure as an SqlParameter.
Below is an example of its usage:
The SqlOutputAttribute behaves the same in both contexts. However this is the most complicated of the SqlAttributes.
If your procedure has an OUTPUT parameter and your property (by the same name or alias) has the SqlOutputAttribute,
then the property will have the OUTPUT value after the load / save method is called.
Below is an example of its usage, with an alias name (were the alias name is the name of the parameter in the procedure).
The SqlLayer will deduce the correct type for the OUTPUT parameter based on the member's type.
However there are some cases where we can't figure out the type completely just from the member.
Mainly when the type is string or byte.
This is because these types can really be on any size, so you need to let the SqlLayer know what kind of size to expect (if your not sure, then overshoot).
Below is an example for a string property (if your table's column is of type VARCHAR (64) for example this is what you would do).
You can have more control over how the SqlParameters are created by using the SqlOutputAttribute's constructor's overloads.
See below for the SqlOutputAttribute's class definition.
The SqlReturnAttribute behaves the same in both contexts.
This attribute's usage is fairly simple, just put it on the property (of type int of course) that you want to have the procedure's return value.
See below for an example:
The extension methods live in the same namespace as the SqlDataLayer (DataLayer.Sql).
Below is the list of the extension methods found in that namespace:
Below is the class definition for SqlExtensions.
The Get extension returns a column from the current row, it also casts the value to the correct type.
For example if you had a column called [TotalCost] that was of type decimal, you could get it as follows:
One of the overloads for the Get extension takes a function, the purpose of this is purely for syntactic sugar.
Lets say your row returns values that you want to put into different objects, here is an example of why you would use this overload:
The ToSqlParameters extension looks at all the public members (including static and inherted public members) and converts them to SqlParameters.
It will also look for the SqlAttributes and apply them as necessary.
Note: The SqlAliasAttribute should only be one name (if it's a csv, only the first value will be taken).
The GetAttribute extension takes a class, an attribute and a member name.
The method will look for the member name, and check if that member has the specified attribute on it.
If it does, then that is returned, otherwise the default is returned (probably null).
The GetSize extension will return the size, in bytes that a value type (a primitive, enum, or struct) will consume in memory.
The ToClrType extension takes a SqlDbType and returns a Type.
Basically its a mapping of database types to runtime types.
The ToSqlType extension takes a Type and returns a SqlDbType.
Basically its a mapping of runtime types to database types (and the inverse of the ToClrType extension).