This post is a demonstration/instructions for very easily and cleanly working with SQL data in a .NET application using LINQ to SQL. There are lots of resources out there about this technology but my goal here is to present a very clean and thin interface to utilizing LING to SQL. Read on for explanations and samples.
Setup:
First things first we will want to add a few custom classes to our project. I suggest creating a Models folder and adding these classes along with all other data models and repositories to this folder for simplicity and segregation. Add the following files to your Models folder.DataRepository.cs
using System;
namespace Models
{
public class DataRepository<Type>
{
private static Type _instance;
public static Type Instance
{
/// <summary>
/// Get a single instance of this repository.
/// </summary>
get
{
if (_instance == null)
_instance = Activator.CreateInstance<Type>();
return _instance;
}
}
}
}
IDataRepository.cs
using System;
using System.Linq;
using System.Linq.Expressions;
namespace Models
{
/// <summary>
/// This insterface provides the core logic for a Data Repository. It must be passed a DataEntity type and will create the
/// list, get, update, delete, and save base methods for that table.
/// </summary>
/// <typeparam name="Type"></typeparam>
interface IDataRepository<Type>
{
/// <summary>
/// Returns a new row object of Type.
/// </summary>
/// <returns>Type</returns>
Type New();
/// <summary>
/// Add the passed in row to the table. Usuly called directly after New().
/// </summary>
/// <param name="record">Type</param>
void Add(Type record);
/// <summary>
/// Delete the passed in row.
/// </summary>
/// <param name="record">Type</param>
void Delete(Type record);
/// <summary>
/// Save all pending changes to the table.
/// </summary>
void Save();
/// <summary>
/// Return all records from the table. Can be further filtered with Where() in the controller or with custom extensions.
/// </summary>
/// <returns>Type</returns>
IQueryable<Type> Find();
/// <summary>
/// Get a single record with the passed in where clause.
/// </summary>
/// <param name="predicate">Expression</param>
/// <returns>Type</returns>
Type Get(Expression<Func<Type, bool>> predicate);
/// <summary>
/// Check that a given record exists in the database.
/// </summary>
/// <param name="predicate">Expression</param>
/// <returns>bool</returns>
bool Contains(Expression<Func<Type, bool>> predicate);
/// <summary>
/// Get a list of columns metadata from the binding model.
/// </summary>
/// <typeparam name="T">Table to get the data for.</typeparam>
/// <returns>Dynamic object of ColumnDetail</returns>
dynamic GetColumns<T>();
}
}
ColumnDetail.cs
using System;
using System.Data.Linq.Mapping;
using Proteus.Extensions;
namespace Model
{
/// <summary>
/// This is a suctom selection of column details for quick access in code.
/// </summary>
public class ColumnDetail
{
/// <summary>
/// Column Name.
/// </summary>
public string Name { get; private set; }
/// <summary>
/// Coulmn maximum length or 0 if not a varchar.
/// </summary>
public int Length { get; private set; }
/// <summary>
/// Column SQL DB Type.
/// </summary>
public string DbType { get; private set; }
/// <summary>
/// Column can be nul.
/// </summary>
public bool CanBeNull { get; private set; }
/// <summary>
/// Column C# type.
/// </summary>
public Type Type { get; private set; }
/// <summary>
/// Create a new ColumnDetail class from the MetaDataMember.
/// </summary>
/// <param name="member">Column Metadata.</param>
public ColumnDetail(MetaDataMember member)
{
Name = member.Name;
Length = int.Parse(member.Length());
DbType = member.DbType;
CanBeNull = member.CanBeNull;
Type = member.Type;
}
}
}
ModelExtensions.cs
using System.Collections.ObjectModel;
using System.Data.Linq.Mapping;
using System.Linq;
using System.Text.RegularExpressions;
using Proteus.Helpers;
using System.Collections.Generic;
using System.Dynamic;
using System.Collections;
namespace Model
{
public static class MetaDataMemberExtensions
{
/// <summary>
/// Regex to get the string length from a sql data type.
/// </summary>
private static Regex getSqlLength = new Regex(@"\((\d+)\)");
/// <summary>
/// Get the max length of a field from its sql data type.
/// </summary>
/// <param name="self">Column</param>
/// <returns>Mx Length</returns>
public static string Length(this MetaDataMember self)
{
if (self.Type == typeof(string) && self.DbType != "Text")
return getSqlLength.Match(self.DbType).Groups[1].Value;
return "0";
}
/// <summary>
/// Convert column data into a dyncmi ExpandoObject.
/// </summary>
/// <param name="self"></param>
/// <returns></returns>
public static dynamic ToExpando(this ReadOnlyCollection<MetaDataMember> self)
{
return self.ToDictionary(s => s.Name, s => new ColumnDetail(s)).ToExpando();
}
/// <summary>
/// Extension method that turns a dictionary of string and object to an ExpandoObject
/// </summary>
public static ExpandoObject ToExpando(this IDictionary<string, object> dictionary)
{
var expando = new ExpandoObject();
var expandoDic = (IDictionary<string, object>)expando;
// go through the items in the dictionary and copy over the key value pairs)
foreach (var kvp in dictionary)
{
// if the value can also be turned into an ExpandoObject, then do it!
if (kvp.Value is IDictionary<string, object>)
{
var expandoValue = ((IDictionary<string, object>)kvp.Value).ToExpando();
expandoDic.Add(kvp.Key, expandoValue);
}
else if (kvp.Value is ICollection)
{
// iterate through the collection and convert any strin-object dictionaries
// along the way into expando objects
var itemList = new List<object>();
foreach (var item in (ICollection)kvp.Value)
{
if (item is IDictionary<string, object>)
{
var expandoItem = ((IDictionary<string, object>)item).ToExpando();
itemList.Add(expandoItem);
}
else
{
itemList.Add(item);
}
}
expandoDic.Add(kvp.Key, itemList);
}
else
{
expandoDic.Add(kvp);
}
}
return expando;
}
/// <summary>
/// Extension method that turns a dictionary of string and object to an ExpandoObject
/// </summary>
public static ExpandoObject ToExpando<T>(this IDictionary<string, T> dictionary)
{
var expando = new ExpandoObject();
var expandoDic = (IDictionary<string, object>)expando;
// go through the items in the dictionary and copy over the key value pairs)
foreach (var kvp in dictionary)
{
// if the value can also be turned into an ExpandoObject, then do it!
if (kvp.Value is IDictionary<string, T>)
{
var expandoValue = ((IDictionary<string, object>)kvp.Value).ToExpando();
expandoDic.Add(kvp.Key, expandoValue);
}
else if (kvp.Value is ICollection)
{
// iterate through the collection and convert any strin-object dictionaries
// along the way into expando objects
var itemList = new List<object>();
foreach (var item in (ICollection)kvp.Value)
{
if (item is IDictionary<string, object>)
{
var expandoItem = ((IDictionary<string, object>)item).ToExpando();
itemList.Add(expandoItem);
}
else
{
itemList.Add(item);
}
}
expandoDic.Add(kvp.Key, itemList);
}
else
{
expandoDic.Add(kvp.Key, kvp.Value);
}
}
return expando;
}
}
}Add a Model
Next we will need to create data Models that reflect the database objects we wish to access. There is a bit of a structural decision to be made here. We can either create one file with all out models and methods in it or create separate files for each screen/model grouping. If we create one single model it may become very complex/confusing and the individual models will not be group together logical. On the other hand if we have complex relationships between tables then we may need to add the same table to different files creating redundant models. Either way we will want to create separate Repositories for each screen/model grouping to take advantage of the helper classes. I recommend adding separate files for each editable/primary tables for more logical code.
- To create a LINQ to SQL Model add a new "LINQ to SQL Classes" file to your project. Make sure you create it in the Models folder. You will want to use the plural version of the primary table in this data model as this filename. We will use Users for our example.
- Now you will need to switch to the Database Explorer tab and log into your database. You can log into a development/test box here ad the production connection information will be overridden later.
- Locate your primary table that this model will be view/editing and add it to the Model by dragging it into your data model. You will notes that if the table name is plural(Users) the model will actually be the singular version of this(User).
- In some cases you may have model name conflicts if you have to use the same linked table in multiple models. You can get around this by changing the name of the model manually. For example if multiple models link to a States table that on each model change the States model name to States1, States2, Etc.
- If needed you can add other linked tables to the model as well to allow you to automatically load and interact with lined data from this model. This will be more efficient than manualy joining data in code.
- As an alternate to this or if the links are complex you could create Views and use those but there are editing limitations when using views.
- You can also add custom Stored Procedures to the model so they can be easily called from .NET by dragging then into the methods section. These should however not be used for general update/delete logic only complex custom SQL code.
- Finally once the model is accurate save and close the file.
As a sample here is the Users Model:
Users.dbml
User
userId(P)
userName
userPassword
userState(F)
State
StateCode(P)
StateName
bit PurgeUserData(userId)Add a Repository
Out last piece of the puzzle is to create a helper Repository for simple and quick strong typed access to the model without much coding. To do this create the following class and fill in the necessary operations.
- Create a new class in the Models folder with the same name as the model but add Repository or Repo to the end. (UsersRepository)
- Update the class definition with the following code where the first type is the repository and the second is the primary model.
: DataRepository<ShopMarketsRepository>, IDataRepository<ShopMarket>- Now right click on the IDataRepository and select Implement Interface. This will create a generic helper class but none of the methods will have any code.
- Next add a connection to the database at the top of the class with the folloing bit of code. where DbConnectionString is the name of the connection string in the web.config or app.config file.
- This will allow us to set the database we are connecting to with the installation/environment instead of hard coding it into the database. Thous could also be set from a variable or other source.
UsersDataContext dc = new UsersDataContext(System.Web.Configuration.WebConfigurationManager.AppSettings["DbConnectionString"]) {};- Updated the methods with the sample code below. Any features that are not needed can just be left as is and will throw a NotImplemented Exception if called.
using System;
using System.Linq;
using System.Linq.Expressions;
namespace Models
{
public class UsersRepository : DataRepository<UsersRepository>, IDataRepository<User>
{
// This is the data connection to the database.
UsersDataContext dc = new UsersDataContext(System.Web.Configuration.WebConfigurationManager.AppSettings["umbracoDbDSN"]) {};
// This method will create a new instance of the record but not insert it into the database.
public User New()
{
User record = new User();
// Set Default Data
return record;
}
// This method will add a record to the database. If the record is already in the database it will generate an exception on saving.
public void Add(User record)
{
dc.Users.InsertOnSubmit(record);
}
// This method will remove a record from the database. You can delate a record without loading it in a stored procedure but that change won't be reflected in the current data in memory.
public void Delete(User record)
{
dc.Users.DeleteOnSubmit(record);
}
// Apply all pending changes to this model, and any linked models, in the database.
public void Save()
{
dc.SubmitChanges();
}
// Find all record from the primary table.
public IQueryable<User> Find()
{
return dc.Users;
}
// This will list all record from a linked table, not those record linked to a specific item.
public IQueryable<States> FindStates()
{
return dc.States;
}
// Like the Find method this will return records but apply a filter to them first. You could filter the Find results directly in your code but if this is cleaner.
public IQueryable<User> FindAllUsersByState(string state)
{
return (from n in dc.Users
where n.userState == state
select n);
}
// Select a single record matching a where clause, or the first if there are multiple matches. If no record is found null will be returned.
public User Get(Expression<Func<User, bool>> predicate)
{
return Find().SingleOrDefault(predicate);
}
// Again a optional helper method to keep SQL logic out of the main application code.
public User Get(string userId)
{
return Get(s => s.userId == userId);
}
// Check to see if there are one or more matches to a filter clause.
public bool Contains(Expression<Func<User, bool>> predicate)
{
return Get(predicate) != null;
}
// Again a shorthand to keep the calls to the Repository clean.
public bool CheckLogin(string username, string password)
{
return Contains(s => s.userName == username && s.userPassword == password);
}
// This is a generic method that should be included on all Repositories to allow quick access to the column metadata information such as data types or maximum field lengths.
public dynamic GetColumns<T>()
{
return dc.Mapping.MappingSource.GetModel(this.GetType()).GetMetaType(typeof(T)).PersistentDataMembers.ToExpando();
}
// A sample Stored procedure that updates other table not included in the data model and returns a status code.
public bool PurgeUserData(string userId)
{
return dc.PurgeUserData(userId);
}
}
}
- As you can see there are some auto-generated methods for the primary table but other helper methods can be added for support tables can be added as well. All of the code could just use the Find(), Get(), and Contains() methods but I encourage the shorthand versions where every needed as they make for cleaner code in the application and much less redundancy in the database interaction code. There are some examples of this blow.
- Also note that there are not methods directly update fields in the database. If a record is loaded and then updated the changes will automatically be written back to the database when Save() is called.
Using a Repository
Below is a collection of sweat and simple data operations using this new Repository and LINQ to SQL model.
UsersRepository repo = new UsersRepository();
myList.DataSource = repo.Find();var rows = repo.Find().Select(s => new {Username = s.userName, State = s.userState});var users =repo.Find.Select(i => new SelectListItem() { Value = i.userId, Text = i.userName });
@Html.DropDownList("users", users)var users = UsersRepository.Instance.Find.Select(i => new SelectListItem() { Value = i.userId, Text = i.userName });
@Html.DropDownList("users", users)var users = repo.Fine(s => s.userPassword != null);With a helper method
var localUsers = repo.FineByState(Request.QueryString["state"]);User record = repo.Get(s => s.userName == "Jeremy");User record = repo.Get(form["userId"]);record.States.stateNamerecord.stateNameif(UsersRepository.Instance.CheckLogin(username, password)) {}User record = repo.New();
record.userName = "bob";
record.userPassword = "asd";
record.userState = "MI";
repo.Add(record);
repo.Save();User record = repo.New();
UpdateModel(record);
repo.Add(record);
repo.Save();record.userPassword = password;
record.Save();repo.PurgeUserData(record.userId);
repo.Delete(record);
repo.Save();Users.Columns.userName.LengthThe final step to cleaning up and ensuring the code you write to interact with the database is as clean and efficient as possible is to add partial classed to your Models to extend the record with custom properties and logic and add data validation rules. All this code runs from the context of the current record but has full access to query and update the current and other records as needed.
- Locate the LINQ to SQL model in your project browser, right click, and select View Code. This will create a new code file related to this model.
- Delete the default class added in this file and instead ass the following class instead.
public partial class User {
}- In this class we will want to add the folloing region for quick access to column metadata using the included helper methods.
#region Column Details
private static dynamic columns;
public static dynamic Columns
{
get
{
if (columns == null)
columns = new FormLandingPageRepository().GetColumns<FormContactPage>();
return columns;
}
set { columns = value; }
}
#endregion- Next we want to add and custom properties to present record data in a custom format so that we don't have to embed formatting in the view code and to reduce redundant code. Here are a few examples.
#region Custom Properties
public string userDetail
{
get
{
return String.Format("{0} (State {1})", userName, State.stateName);
}
}
public string stateName
{
get
{
return State.stateName;
}
}
public bool isActive
{
get
{
return !String.IsNullOrEmpty(userPassword);
}
}
#endregion- And finally we will place our data validation rules here as well.
#region Data Validation
partial void OnuserNameChanging(string value)
{
if (String.IsNullOrEmpty(value))
throw new Exception("Please enter a Username.");
if(UserRepository.Instance.Contains(value))
{
throw new Exception("This username is already in user, please choose another.");
}
}
partial void OnuserNameChanged()
{
// Auto Trim fields
if (userName.Length > Columns.userName.Length)
_userName = userName.Substring(0, Columns.userName.Length);
// Or throw and error
if (userName.Length > Columns.userName.Length)
throw new Exception("Your Username is to long.");}
partial void OnuserPasswordChanging(string value)
{
if (String.IsNullOrEmpty(value))
throw new Exception("Please enter a Password.");
}
partial void OnuserStateChanging(string value)
{
if (String.IsNullOrEmpty(value))
throw new Exception("Please select a State.");
}
// This validation code runes after all the individual fields are validated and allows for record wide validation rules that are not specific to a single field.
partial void OnValidate(System.Data.Linq.ChangeAction action)
{
if (action == System.Data.Linq.ChangeAction.Insert || action == System.Data.Linq.ChangeAction.Update)
{
phoneNumber = String.Format("{0}-{1}-{2}", phoneNpa, phoneNxx, phoneExt);
}
}
#endregion
No comments:
Post a Comment