Documentation

Connections

Connections are SQL Data's way of managing connections to the data store(s) that you are working with. The idea is simple. In order to connect to a data store and get data, you need to specify where it is. SQL data makes this extremely easy with a method directly off of the store called AddConnection. This can be called anywhere in your code and at anytime although, it makes sense in most scenarios to call it early in some sort of initialization code. Once you add a connection, SQL Data will be aware of it for the life of the running application. Let's see it in action.

Data.Store.AddConnection("connection name", "connection string");

The Query Method: Executing Statements (Without Returning Results)

SQL Data allows you to execute any T-SQL statement directly against the data store. This can be achieved by using the Query method. When called, this method returns the number of rows affected by the query. There are several overloads to this method.

// Uses the default connection
int rows = Data.Store.Query("statement");

// Uses a specified connection
int rows = Data.Store.Query("statement", "connection");

// Uses the default connection and passes parameters
int rows = Data.Store.Query("statement", Parameter[]);

// Uses a specified connection and passes parameters
int rows = Data.Store.Query("statement", "connection", Parameter[]);

The Query<T> Method: Fetching Data

With SQL Data, we use the Query<T> method to fetch data into classes. The type used in this method can be any c# class. Like the Query method, there are several overloaded variations:

// Uses the default connection
IEnumerable<T> results = Data.Store.Query<T>("statement");

// Uses a specified connection
IEnumerable<T> results = Data.Store.Query<T>("statement", "connection");

// Uses the default connection and passes parameters
IEnumerable<T> results = Data.Store.Query<T>("statement", Parameter[]);

// Uses a specified connection and passes parameters
IEnumerable<T> results = Data.Store.Query<T>("statement", "connection", Parameter[]);

The Scalar<T> Method: Fetching a Single Result

When needing to return a single result from the database, use the Scalar method. Scalar excepts the same arguments and has the same overloads as Query<T> but only returns the first result of a statement. There is also no need to use the TOP clause. SQL Data will only return one result efficiently. This is how you use it.

// Uses the default connection
T result = Data.Store.Scalar<T>("statement");

// Uses a specified connection
T result = Data.Store.Scalar<T>("statement", "connection");

// Uses the default connection and passes parameters
T result = Data.Store.Scalar<T>("statement", Parameter[]);

// Uses a specified connection and passes parameters
T result = Data.Store.Scalar<T>("statement", "connection", Parameter[]);

QueryDynamic Method: Fetching Data Dynamically

SQL Data enables you to fetch data even when you do not have a container class through the use of the QueryDynamic method. This method is very powerful and efficient and follows the same syntax as the other methods.

// Uses the default connection
IEnumerable<dynamic> results = Data.Store.QueryDynamic("statement");

// Uses a specified connection
IEnumerable<dynamic> results = Data.Store.QueryDynamic("statement", "connection");

// Uses the default connection and passes parameters
IEnumerable<dynamic> results = Data.Store.QueryDynamic("statement", Parameter[]);

// Uses a specified connection and passes parameters
IEnumerable<dynamic> results = Data.Store.QueryDynamic("statement", "connection", Parameter[]);

Entity

When writing classes to model your database, SQL Data supplies you with the Entity class which adds additionaly features to your model classes. It is important to note that SQL Data fully supports POCO classes, although some of the features that we will discuss shortly require the use of the Entity class and Data Attributes. When you derive a class from Entity, you automatically get methods on your class to make CRUD operations incredibly easy. Let's see an example.

public class Person : Entity
{
    // The key attribute is required if you want to use these helper methods
    [Key]
    public int Id { get; set; }
    public int Age { get; set; }
    public string Name { get; set; }
}

// Insert a new Person record in the database
Person person = new Person();
person = person.Create();

// Update an existing Person record in the database
person = person.Update();

// Delete an existing Person record in the database
person.Delete();

Data Attributes

In addition to the Key attribute, there are several others at your disposal in SQL Data.

Connection Attribute - Specifies which connection the class is associated with

[Connection("UserDB")]
public class Person : Entity

Key Attribute - Designates a property as the Primary Key in the associated table

[Key]
public int Id { get; set; }

Ignore Attribute - Specifies that a property should not be fetched from or sent to the database

[Ignore]
public bool IsActive { get; set; }

ReadOnly Attribute - Specifies that a property should be fetched from but not sent to the database

[ReadOnly]
public DateTime TimeStamp { get; set; }

Name Attribute - Specifies the true name of the table in the database when the class name differs (this should be used to decorate a class when the name differs from the database table name)

[Name("Payment")]
public class PaymentInfo : Entity

All<T> Method: Fetching an Entire Table

When you want to obtain an entire table for querying, a very nice way to do this is to use the All method. This is especially useful when using Linq to manipulate your data. Here is how you use it.

var results = Data.Store.All<Person>();

// Since results are always IEnumerable, we can easily use this in a Linq expression
var results = from people in Data.Store.All<Person>() where people.Age % 2 == 0 select people;

// or

var results = Data.Store.All<Person>().Select(x => x.Age % 2 == 0);

CreateAll<T>: Insanely Fast Bulk Insert

One of the coolest features of SQL Data is the ability to do bulk inserts on any IList. This is by far the simplest way to insert large amounts of data into the database and is much faster than iterating through the elements and calling Create on each one. And it is super simple too.

// MyListOfPeople is a List<Person>
MyListOfPeople.CreateAll();

Parameters

Most of the methods in SQL Data have an overload with a Parameter[] argument which allows you to specify any number of Parameters. To use parameters in a statement you follow the same rules with a typical SqlCommand. Each parameter in the statement is prefaced with an @ symbol. Then, you pass in a Parameter using this parameter name. Let's see an example.

var name = "A%";
var results = Data.Store.Query<Person>("SELECT * FROM [Person] WHERE Name LIKE @Name", 
Parameter.Create("Name", name));

You could just as easily pass in a normal instance or Parameter, or even a List<Parameter>. Parameter.Create() is just a simple way of creating a Parameter object.





Last edited Oct 12, 2013 at 9:15 PM by tjscience, version 10