Working with ASP.Net, jQuery, LINQ, and SQL Without Using Object Relational Designer

Originally posted by Christian Apostolu Tuesday, November 22, 2011

The one thing I have noticed since I started writing articles is that the majority of articles that are written for the .Net framework in Visual Studio are written based on the PAID version of Visual Studio. What about all of the people that use the Express version? When I started writing I made the decision to write all of my example code using Express. Yes, for my normal work I use the paid version which my company pays for but I wanted to be able to write to as broad an audience that I could. This has forced me to do more research so that I can duplicate the tools and templates that are provided in the paid version of VS.

A good example of this is using LINQ to connect to SQL. In the paid version of VS there is a template called LINQ to SQL which uses a drag and drop interface called Object Relational Designer. I have to admit it's nice not having to get your hands dirty and write the code by hand. So what can the Express version guy do? That's what this article attempts to help you with.

LINQ1db.cs

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

 

namespace jPost
{
//Specifies certain attributes of a class that represents a database.
[System.Data.Linq.Mapping.DatabaseAttribute(Name = "TestLab")]
public partial class JobPost : System.Data.Linq.DataContext //The DataContext is the source of all entities mapped over a database connection.Represents the main entry point for the LINQ to SQL framework.
{
//The MappingSource class is used to map common language runtime(CLR) objects to entities in the underlying data source.
private static System.Data.Linq.Mapping.MappingSource mappingSource = new AttributeMappingSource();

#region Extensibility Method Definitions
partial void OnCreated();
partial void InsertJobPost2(JobPost2 instance);
partial void UpdateJobPost2(JobPost2 instance);
partial void DeleteJobPost2(JobPost2 instance);

#endregion

//Method Overloading
//We can add an overloaded method with a different signature and IntelliSense will now present options, one for each signature of the overloaded method.
//The compiler finds each method of the same name and each call to that method with different parameters.
//It is able to tell the difference by comparing the signatures of the methods.
#region Overloaded Methods
static JobPost()
{
}

public JobPost(string connection) :
base(connection, mappingSource)
{
    OnCreated();
}

public JobPost(System.Data.IDbConnection connection) :
base(connection, mappingSource)
{
    OnCreated();
}

public JobPost(string connection, System.Data.Linq.Mapping.MappingSource mappingSource) :
base(connection, mappingSource)
{
    OnCreated();
}

public JobPost(System.Data.IDbConnection connection, System.Data.Linq.Mapping.MappingSource mappingSource) :
base(connection, mappingSource)
{
    OnCreated();
}
#endregion

public System.Data.Linq.Table<JobPost2> JobPost2
{
get
{
return this.GetTable<JobPost2>();
}
}

}

//The INotifyPropertyChanged interface is used to notify clients, typically binding clients, that a property value has changed.
//http://msdn.microsoft.com/en-us/library/system.componentmodel.inotifypro...
//The INotifyPropertyChanging interface is used to notify clients, typically binding clients, that a property value is changing.
//http://msdn.microsoft.com/en-us/library/system.componentmodel.inotifypro...

[Table(Name = "dbo.JobPost2")]
public partial class JobPost2 : INotifyPropertyChanging, INotifyPropertyChanged
{

private static PropertyChangingEventArgs emptyChangingEventArgs = new PropertyChangingEventArgs(String.Empty);

private int _Post_Id;

private string _JobPosting;

#region Extensibility Method Definitions
partial void OnLoaded();
partial void OnValidate(System.Data.Linq.ChangeAction action);
partial void OnCreated();
partial void OnPost_IdChanging(int value);
partial void OnPost_IdChanged();
partial void OnJobPostingChanging(string value);
partial void OnJobPostingChanged();
#endregion

public JobPost2()
{
    OnCreated();
}
//http://msdn.microsoft.com/en-us/library/system.data.linq.mapping.columna...
//Use this attribute to designate a member of an entity class to represent a column in a database table.
[Column(Storage = "_Post_Id", AutoSync = AutoSync.OnInsert, DbType = "Int NOT NULL", CanBeNull = false)]
public int Post_Id
{
get
{
return this._Post_Id;
}
set
{
if ((this._Post_Id != value))
{
    this.OnPost_IdChanging(value);
    this.SendPropertyChanging();
    this._Post_Id = value;
    this.SendPropertyChanged("Post_Id");
    this.OnPost_IdChanged();
}
}
}

//http://msdn.microsoft.com/en-us/library/system.data.linq.mapping.columna...
//Use this attribute to designate a member of an entity class to represent a column in a database table.
[Column(Storage = "_JobPosting", DbType = "Varchar(100) NOT NULL", CanBeNull = false)]
public string JobPosting
{
get
{
return this._JobPosting;
}
set
{
if ((this._JobPosting != value))
{
    this.OnJobPostingChanging(value);
    this.SendPropertyChanging();
    this._JobPosting = value;
    this.SendPropertyChanged("JobPosting");
    this.OnJobPostingChanged();
}
}
}

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));
}
}
}
}

Here we have what is similar to the LINQ To SQL template that is provided by the paid version of VS. Interspersed throughout the code I added comments and links to provide you with a way to get a better understanding of how it works. The code is based on code I found on the net and rewrote and stripped down for our example. When I downloaded the project that this code was in I noticed at the top that this was generated code that I was working with. The code generator used is a Microsoft product called SQLMetal. The best part about this is that it's Free!!

SQLMetal is a command line app that is included in the Windows SDK. A link to the SDK as well as info on SQLMetal can be found in our Tools section. It has the ability to produce C# or VB code based on the file extension you use. Let's take a look at how we can generate our file.

  1. Go to the Start Menu and open up the Command Prompt.
  2. Change the directory to where SQLMetal is located. It should be similar to this:

    C:\Program Files\Microsoft SDKs\Windows\v7.1\Bin\NETFX 4.0 Tools

  3. Type this on the command line: sqlmetal.exe /server:server name /database:database name /code:C:\Folder to place file\Filename.cs /context:Data context name /namespace:namespace name

  4. Press Enter.  

The file created is the mapping of the database. This could end up being a very large file if you have a lot of tables in it. You can also map a SQL Express database file. There are parameters you can add to get stored procedures and functions.

LINQ1.aspx.cs

using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Data.SqlClient;
using System.Data.Linq;
using System.Linq.Expressions;
using System.Data.Linq.Provider;
using System.Data.Linq.Mapping;
using System.Reflection;
using System.Data.Linq.SqlClient;
using System.Xml.Linq;
using System.Configuration;
using System.Web.Services;
using jPost;

namespace TestLab1
{
public partial class LINQ11 : System.Web.UI.Page
{
private JobPost db;
string strConn = ConfigurationManager.ConnectionStrings["TestLabConnection"].ConnectionString;
static string strConn1 = ConfigurationManager.ConnectionStrings["TestLabConnection"].ConnectionString;

protected void Page_Load(object sender, EventArgs e)
{
   LinqToSqlSelect01();
}

public void LinqToSqlSelect01()
{
    JobPost db2 = new JobPost(strConn);
   var q =
from c in db2.JobPost2
select new { c.Post_Id, c.JobPosting };

GridView1.DataSource = q;
   GridView1.DataBind();

}

public class Info
{
public int jpId { get; set; }
public string jpPosting { get; set; }
}

[WebMethod]
public static IEnumerable<Info> SelectPosting3()
{
    JobPost db2 = new JobPost(strConn1);

var q = (from a in db2.JobPost2
select new Info{ jpId = a.Post_Id, jpPosting = a.JobPosting });

return q;

}

 

}
}

At the top of the code behind page notice that we are using jPost which is the namespace JobPost datacontext class that's in LINQ1db.cs. Next we create an instance of our JobPost class and define our connection to our server. The connection string resides in our web.config file. The method LinqToSqlSelect01 is used to send the data retrieved by LINQ to a Gridview on the UI. SelectPosting3 is a method called from the UI using jQuery and the data is displayed using HTML Tags. To allow jQuery the ability to talk to the code page, we need to add at the top "using System.Web.Services;" and decorate our method with "[WebMethod]". We will talk about the jQuery aspect when we discuss the UI. Methods in the code behind that are called by jQuery have to be static. The reason why is that these methods can't interact with the instance properties and methods of your Page class, because a page method call creates no instance of the Page or any of its controls. Since it doesn't create an instance of the Page and its controls, there is no Postback or annoying page refresh.

LINQ1.aspx

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title></title>
<script type="text/javascript" src="Scripts/jquery-1.4.1.min.js"></script>
<script type="text/javascript" >

$(function () {

$('#Button1').click(getJPosting);

});

 

function getJPosting() {
  var $container = $('#container');

$.ajax({

type: "POST",

url: "LINQ1.aspx/SelectPosting3",

data: "{}",

contentType: "application/json; charset=utf-8",

dataType: "json",

success: function (response) {

var rFeeds = response.d;

$.each(rFeeds, function (index, rFeed) {

//var $Feed2 = $('<div><p><strong>Name: ' + rFeed + '</strong><br />' + '</p></div>');
  var $Feed2 = $('<div><p><strong>Posting Id: </strong>' + rFeed.jpId + '<br /><strong>Job Posting: </strong>' +
  rFeed.jpPosting + '</p></div>');

$container.append($Feed2);
});

},

failure: function (msg) {

$('#output').text(msg);

}

});

}

</script>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>

<div id="container">

</div>
<p>Retrieve a list of job postings.</p>
<input type="button" id="Button1" value="Get Jobs"/>
<br />

</div>
</form>
</body>
</html>

 

In the Head tag we include the Script tag that points to the jQuery library. The second Script tag contains two Javascript functions. The first is for the button click event which calls the getJPosting function. The getJPosting function uses jQuery.ajax() which performs an asynchronous HTTP (Ajax) request. To briefly go over what is going on, the url is made up of the UI page and the method that exists on the code page. Since our method has no parameters to pass, data has empty brackets. Our datatype is JSON (JavaScript Object Notation) a data-interchange format that is language independent. Upon success of the call to SelectPosting3 we loop through the data retrieved and put each record into HTML tags which are then added to the div tag called "container". For further explanation of jQuery and JSON you can find their links in our Tools section. Also a couple of my prior articles go into more detail also.

SQL Table JobPost2

CREATE TABLE [dbo].[JobPost2](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Post_Id] [int] NOT NULL,
[JobPosting] [varchar](100) NOT NULL,
[Applicant] [varchar](100) NOT NULL,
[Type] [varchar](50) NOT NULL,
[CreatedDate] [datetime] NOT NULL,
CONSTRAINT [PK_JobPost2] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

To complete our code the table script for JobPost2 which is the example table we are using.

In conclusion, my main point was to tie ASP.Net, jQuery, LINQ, and SQL together in a way that anyone including those using the Express version of Visual Studio could do it easily. Along the way we discovered a very useful code generator SQLMetal that does much of the heavy lifting for us. Until next time, happy coding!

Author: Christian Apostolu
Christian Apostolu's picture
About:

Owner/founder of Crush The Soul, software developer, amateur athlete, frustrated musician. 

"Once we accept our limits, we go beyond them." - Albert Einstein

 

Category: