Step-By-Step to DLinq

Linq - Start Using It and the Single method explored

2008-01-26T23:06:00.000-08:00

Let's start off by registering users on your website or you application. You have a very simple form where the user can enter his desired username n password. We will validate if that username is already in use and also make sure that the password is in the required format.

I am gonna write up some back-end code for a web application when the user enters his information and click on the submit button.


protected void btnRegisterUser_Click(object sender, EventArgs e)
{
String _username = tbUsername.Text.Trim().ToLower().Replace("'", "''");
String _password = tbPassword.Text.Trim().ToLower().Replace("'", "''");

MemberDataContext MemberDC = new MemberDataContext();
Member new_member = new Member();
new_member.Username = _username;
new_member.Password = _password;
 MemberDC.Members.InsertOnSubmit(new_member);
MemberDC.SubmitChanges();
}

I had a tough time inserting data in the database since all the examples out there don't include the line I marked in bold. The line tells the MemberDataContext to insert the new_member object in the Members table. However, the data is not saved until MemberDC.SubmitChanges() is not executed.

This was the basic example. However, you might not want two users to use the same username on the system. So, you will want to perform validation. From experience, I suggest using the Single method from the collection of LINQ functions. The Single method always tries returns a single row from the database. If the result of the query returns zero or more than one row, an exception is thrown.

I will modify the btnRegisterUser_Click method to reflect the username validation.


protected void btnRegisterUser_Click(object sender, EventArgs e)
{
 MemberDataContext MemberDC = new MemberDataContext();
 String _username = tbUsername.Text.Trim().ToLower().Replace("'", "''");
 try
 {
   var member = MemberDC.Members.Single(u => u.Username == _username);
   //The username is already in use
   return;
 }
 catch (Exception ex) {}

 String _password = tbPassword.Text.Trim().ToLower().Replace("'", "''");
 Member new_member = new Member();
 new_member.Username = _username;
 new_member.Password = _password;
 MemberDC.Members.InsertOnSubmit(new_member);
 MemberDC.SubmitChanges();
}

Its easy to understand what will happen when a user tries to register an existing username. The Single method will not throw an exception and that means u have to return an error.

You could use the same concept to login a member.

protected void btnLoginSubmit_Click(object sender, EventArgs e)
{
String _username = tbUserName.Text.Replace("'", "''").Trim();
String _password = tbPassword.Text.Replace("'", "''").Trim();

try
{
MembersDataContext MemberDC = new MembersDataContext();
Member member = MemberDC.Members.Single(m => m.Username == _username && m.Password == _password);
FormsAuthentication.RedirectFromLoginPage(member.Username.ToString(), true);
}
catch(Exception ex)
{
//Unsuccessful attempt
errorText.Text = "Invalid username/password combination.";
}
}

Earlier in the post, I spoke about validating the password too. There are 2 options.

1. Modify Member.designer.cs (bad idea bcuz of this warning)
//------------------------------------------------------------------------------
//
// This code was generated by a tool.
// Runtime Version:2.0.50727.1433
//
// Changes to this file may cause incorrect behavior and will be lost if
// the code is regenerated.
//
//------------------------------------------------------------------------------

2. Exploit the concept of partial classes.
We will create a class file Member.cs and add the following code.


using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Text.RegularExpressions;

namespace LinqDemo
{
public partial class Member
{
partial void OnPasswordChanging(string value)
{
    Regex new_password = new Regex("^[A-Z].*?[0-9]$");
    if (new_password.IsMatch(value) == false)
    {
        throw new Exception("Password does not start with a Capital Letter and end in a number");
    }
}
}
}

Note, the namespace marked in bold. If you are missing that, make sure you define the name space in this file as well as Member.deginer.cs. Absence of that code would not allow the compiler to combine both the partial classes. Also, note the partial modifier on the me OnPasswordChanging method. This is a new feature in .net 3.0. More about partial methods here.

Next up, exploring LINQ across relations.

Linq - Getting Started

2008-01-26T11:54:00.001-08:00

Get yourself a copy of Visual Studio 2008. It ships with a LINQ to SQL Designer. The designer will be your best friend throughout the LINQ to SQLing process.

I shall design a simple schema starting with something very common to websites today - a SQL table storing member information. It has 3 fields.

ID - auto incrementing integer
Username - char(10)
Password - char(10)

Lets dive into the designer.

How to bring up the designer?
Right-click on App_Code folder under your website and click on Add New Item. Select Linq to SQL Classes. I renamed the file as Member.dbml

Visual Studio creates Member.dbml and brings up the designer interface.

The designer is divided into two panes. The left pane is where one should drag n drop the sql tables. The right pane is where you drop stored procedures which get converted into methods on the classes. We shall explore the left page first.

To drag n drop sql tables, open the server explorer n connect to the database. I dragged n dropped the Members table. You will notice that Visual Studio will convert the table names from plural to singular. Here, Members is converted to Member.

At this moment, if you open up Member.designer.cs file, these are the contents.


using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;



[System.Data.Linq.Mapping.DatabaseAttribute(Name="LinqDemo")]
public partial class MemberDataContext : System.Data.Linq.DataContext
{
 
 private static System.Data.Linq.Mapping.MappingSource mappingSource = new AttributeMappingSource();
 
  #region Extensibility Method Definitions
  partial void OnCreated();
  partial void InsertMember(Member instance);
  partial void UpdateMember(Member instance);
  partial void DeleteMember(Member instance);
  #endregion
 
 public MemberDataContext() : 
   base(global::System.Configuration.ConfigurationManager.ConnectionStrings["LinqDemoConnectionString"].ConnectionString, mappingSource)
 {
  OnCreated();
 }
 
 public MemberDataContext(string connection) : 
   base(connection, mappingSource)
 {
  OnCreated();
 }
 
 public MemberDataContext(System.Data.IDbConnection connection) : 
   base(connection, mappingSource)
 {
  OnCreated();
 }
 
 public MemberDataContext(string connection, System.Data.Linq.Mapping.MappingSource mappingSource) : 
   base(connection, mappingSource)
 {
  OnCreated();
 }
 
 public MemberDataContext(System.Data.IDbConnection connection, System.Data.Linq.Mapping.MappingSource mappingSource) : 
   base(connection, mappingSource)
 {
  OnCreated();
 }
 
 public System.Data.Linq.Table Members
 {
  get
  {
   return this.GetTable();
  }
 }
}

Basically, Visual Studio creates a DataContext class (in this case MemberDataContext) which will be your conduit to the data stored in the sql tables. The MemberDataContext class has a few constructors. You will mostly need the default constructor.

Once you save the Member.dbml file, the following code is added to Member.designer.cs


[Table(Name="dbo.Members")]
public partial class Member : INotifyPropertyChanging, INotifyPropertyChanged
{
 
   private static PropertyChangingEventArgs emptyChangingEventArgs = new PropertyChangingEventArgs(String.Empty);
 
   private int _ID;
 
   private string _Username;
 
   private string _Password;
 
   #region Extensibility Method Definitions
   partial void OnLoaded();
   partial void OnValidate(System.Data.Linq.ChangeAction action);
   partial void OnCreated();
   partial void OnIDChanging(int value);
   partial void OnIDChanged();
   partial void OnUsernameChanging(string value);
   partial void OnUsernameChanged();
   partial void OnPasswordChanging(string value);
   partial void OnPasswordChanged();
   #endregion
 
   public Member()
   {
       OnCreated();
   }
 
   [Column(Storage="_ID", AutoSync=AutoSync.OnInsert, DbType="Int NOT NULL IDENTITY", IsPrimaryKey=true, IsDbGenerated=true)]
   public int ID
   {
       get
       {
           return this._ID;
       }
       set
       {
           if ((this._ID != value))
           {
               this.OnIDChanging(value);
               this.SendPropertyChanging();
               this._ID = value;
               this.SendPropertyChanged("ID");
               this.OnIDChanged();
           }
       }
   }
 
   [Column(Storage="_Username", DbType="NChar(10) NOT NULL", CanBeNull=false)]
   public string Username
   {
       get
       {
           return this._Username;
       }
       set
       {
           if ((this._Username != value))
           {
               this.OnUsernameChanging(value);
               this.SendPropertyChanging();
               this._Username = value;
               this.SendPropertyChanged("Username");
               this.OnUsernameChanged();
           }
       }
   }
 
   [Column(Storage="_Password", DbType="NChar(10) NOT NULL", CanBeNull=false)]
   public string Password
   {
       get
       {
           return this._Password;
       }
       set
       {
           if ((this._Password != value))
           {
               this.OnPasswordChanging(value);
               this.SendPropertyChanging();
               this._Password = value;
               this.SendPropertyChanged("Password");
               this.OnPasswordChanged();
           }
       }
   }
 
   public event PropertyChangingEventHandler PropertyChanging;
 
   public event PropertyChangedEventHandler PropertyChanged;
 
   protected virtual void SendPropertyChanging()
   {
       if ((this.PropertyChanging != null))
       {
           this.PropertyChanging(this, emptyChangingEventArgs);
       }
   }
 
   protected virtual void SendPropertyChanged(String propertyName)
   {
       if ((this.PropertyChanged != null))
       {
           this.PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
       }
   }
}

This is the fun part. Visual Studio created a partial class called Member (the name corresponds to name shown in the designer). I hope you know what a partial class is. If not, in simple words, partial classes allow you to define a class across multiple files.

Notice [Table(Name="dbo.Members")] definition above the class definition. It means that this class has been mapped to the Members table.

Now, the Member class has 3 private fields of interest.

private int _ID;
private string _Username;
private string _Password;

These correspond to the fields in the Members table. For each private field from the table, you will find the definition of a public property. Lets investigate the ID property.


[Column(Storage="_ID", AutoSync=AutoSync.OnInsert, DbType="Int NOT NULL IDENTITY", IsPrimaryKey=true, IsDbGenerated=true)]
  public int ID
  {
      get
      {
          return this._ID;
      }
      set
      {
          if ((this._ID != value))
          {
              this.OnIDChanging(value);
              this.SendPropertyChanging();
              this._ID = value;
              this.SendPropertyChanged("ID");
              this.OnIDChanged();
          }
      }
  }

The get property is self explanatory. The set property is more interesting.
Lets say you have a page to change the password. A member whose ID is 100 changes his password and you are relying on Linq to generate the sql statements for the update. Linq keeps track of what fields have changed and generates appropriate sql statements.

So, just by simple drag n drop actions, you have created a Object Mapper for your Member Table.

Next up, how do you actually start using it.

Introduction - Linq to SQL or DLinq

2008-01-26T11:06:00.000-08:00

Hmmm. What can I say here. Most of you must be knowing what LINQ means and more specifically what is DLINQ. I read numerous blogs and articles to understand how LINQ can help .net developers who believed that Object Relational Mapping is a key to application development.

LINQ is to .net what Hibernate is to Java. LINQ goes a few steps ahead and facilitates querying not only SQL databases, but also XML, objects, etc. How convenient is that!!! No need to learn SQL and XQuery in detail. And how about not worrying about SQL Stored Procedures. According to me, Stored Procedures are not easy to use, very difficult to master and possess limited functionality. Well, this is not the place to bitch about SPs, so I will move on.

The purpose of this blog is to educate myself and readers about how DLINQ can be very efficiently used to make the development time brief and the experience enjoyable. I started playing with LINQ a few days ago, and immediately realized I could not use it. Visual Studio Intellisense would not show me the methods I most read about in the drop downs. After some research and help from a friend, we managed to get it working.

Throughout the entire series, I will be exploring and talking about how to start using DLINQ and explore all the LINQ methods one by one. So stay tuned.