In this tutorial, we will be looking at one flavor of Microsoft’s new introduction to the .NET Framework, LINQ to SQL. LINQ (Language-Integrated Query) comes in a variety of forms, including LINQ to XML and LINQ to Objects. We will be creating a LINQ to SQL Class to represent our SQL database, and will be dealing with LINQ statements instead of working directly with SQL.

Getting Started

Before we begin coding, we will need to start a new project and create our database. Open Visual Studio, and then goto File > New Website. Then choose your location and give the website a name – LINQtoSQL will do. When you click Ok, Visual Studio will create the necessary files and we will eventually be provided with our ASPX page. To the right, we have our Solution Explorer. Right-click the App_Data folder and choose Add New Item, then choose SQL Server Database. Give it a name.



Next, open up the Server Explorer on the left (or View > Server Explorer), and right-click the Tables folder on the database we just added, then click Add New Table.


We will then be provided with the table designer. Let’s add three columns: id, name, city. We will set the data types as int, varchar(50), varchar(50) respectively. We will also make id the Primary Key and set its Identity Specification to Yes in the Column Properties at the bottom. This will make the id column the unique identifier for each record, which makes it easier for us to let .NET do all the work for us. Now we can save the table, name it tblPeople, and then close it, which will bring us back to our ASPX page.

Now we have our database ready, we will add some sample data. Right-click the table in the Server Explorer and choose Show Table Data. Now let’s add some sample data – notice we don’t need to complete the id column as it is auto-generated:


We stand behind Server Intellect and their support team. They offer dedicated servers , and they are now offering cloud hosting! 

Now we have our database set up, we can build our LINQ to SQL Class. Right-click on your project in Solution Explorer, and choose Add New Item. Then from the list, choose LINQ to SQL Classes. Give it a name if you want, and when you click Ok it may ask you if you want to place in the App_Code folder. If so, choose Yes.



We should then be provided with the Object Relational Designer:



This is where LINQ creates its class based on our input. We can add our database tables and stored procedures to this designer and modify the relationships between the data, and then Visual Studio will create the class or classes based upon the layout. 

For this example, we only have one table to work with, so the design is going to be very simple. From Server Explorer, drag the table onto the main area of the designer. Our table should be graphically represented like so:


Now we can save the changes we made and close the designer. Let’s now drag a LinqDataSource control onto the ASPX page, and we can use the Smart Tag in design view to configure the data source. We want to use the DataClassesDataContext we just created:





Yes, it is possible to find a good web host. Sometimes it takes a while to find one you are comfortable with. After trying several, we went with Server Intellect and have been very happy thus far. They are by far the most professional, customer service friendly and technically knowledgeable host we’ve found so far.

When we are done with the LinqDataSource Wizard, we should be presented with a new Smart Tag. Check Enable Delete and Enable Insert:



Now we have created our LINQ to SQL Class and linked it to our LinqDataSource, and enabled Deletions and Inserts. Notice that we only need to specify what we want, and Visual Studio will do the hard work for us, behind the scenes. Next up, let’s create a means for us to add new records to the database. We’ll modify the ASPX page to include two textboxes and a submit button (remember, we don’t need to input the id field, as it’s auto-generated) and we can use a DataGrid to display the records from the database. We can either drag one on from the toolbox and use the Smart Tag to assign the datasource, or simply type it in ourselves along with the new textboxes and submit button.


Code Block
Ask a Question
Download Source
Default.aspx
<form id="form1" runat="server">
    Name:
    <asp:textbox id="txtName" runat="server" />
    <br />
    City:
    <asp:textbox id="txtCity" runat="server" />
    <br />
    <asp:DataGrid ID="DataGrid1" runat="server" DataSourceID=
    "LinqDataSource1" Width="334px">
    </asp:DataGrid>

    <asp:button id="butAdd" runat="server" text="Add" />

    <br />

    <asp:linqdatasource id="LinqDataSource1" runat="server" contexttype	name="DataClassesDataContext"

        enabledelete="True" enableinsert="True" tablename="tblPeoples">

    </asp:linqdatasource>

</form>















Server Intellect assists companies of all sizes with their hosting needs by offering fully configured server solutions coupled with proactive server management services. Server Intellect specializes in providing complete internet-ready server solutions backed by their expert 24/365 proactive support team.

Now if we right-click our Default.aspx in the Solution Explorer and choose View in Browser, we will be presented with an input form and a neat table displaying the data from the database. This is similar to using the SqlDataSource and DataGrid or GridView controls, but LINQ has so much more to offer and makes it a lot easier for us to communicate with and manipulate data, as we will see in the rest of this article.





At present, the web application is just displaying data – no real functionality. Let us now add the functionality to add new records to the database. Go into the design view of the ASPX page and double-click on the button to create the event handler for the click event. We will use LINQ to add the contents of the textboxes into the SQL database. We use the connection string that was added to our Web.config when we created the LinqDataSource, and we use the LINQ to SQL Class to reference our database. The class can be seen as a representation of our database, which we can make changes to without making changes to the original database. This allows us to make multiple changes without making multiple calls to the database. We can then commit changes back to the database with the SubmitChanges() method, which is a member of the DataContext class.




Code Block


Ask a Question


Download Source




Default.aspx






protected void butAdd_Click(object sender, EventArgs e)
{
    string connectionString = ConfigurationManager.ConnectionStrings["D    atabaseConnectionString"].ToString();
    DataClassesDataContext dataContext = new DataClassesDataContext(
    connectionString);
 
    tblPeople tblP = new tblPeople();
    tblP.name = txtName.Text;
    tblP.city = txtCity.Text;
    dataContext.tblPeoples.InsertOnSubmit(tblP);
    dataContext.SubmitChanges();
    DataGrid1.DataBind();
}







Note that our database isn’t modified until the SubmitChanges() method is called. This means we can make multiple changes without even making a call to the database, which reduces server load and processing time. In our last line, we call the DataGrid to bind the data source again, so that we can see the newest data.



We can now move onto deletions. We want to add the ability to delete any record in the database. We will do this by populating a DropDownList with the names in the database, and then use a delete button to delete the selected record. We can use the same DataSource for the DropDown, so the code would look something like this:




Code Block


Ask a Question


Download Source




Default.aspx






<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True"
DataSourceID="LinqDataSource1" DataTextField="name" DataValueField="id">
</asp:DropDownList>
<br />
<asp:Button ID="butDelete" runat="server" Text="Delete" onclick="
butDelete_Click" />







In a similar way to how we coded the addition of new records, we add code to the delete button. Double click the delete button in design view and then add the following code.




Code Block


Ask a Question


Download Source




Default.aspx






protected void butDelete_Click(object sender, EventArgs e)
{
    DataClassesDataContext db = new DataClassesDataContext();
    var selectedID = Convert.ToInt16(DropDownList1.SelectedValue);
    tblPeople toDelete = db.tblPeoples.Single(p => p.id == selectedID);
    db.tblPeoples.DeleteOnSubmit( toDelete );
    db.SubmitChanges();
 
    DropDownList1.DataBind();
}








I just signed up at Server Intellect and couldn’t be more pleased with my fully scalable & redundant cloud hosting! Check it out and see for yourself.

Again, we are using LINQ to interact with and modify the class representing our database, then we submit changes back to the database. Notice that we also bind our DropDown again, when we’re done, to update the datasource. We also need to bind the GridView here, and also bind the DropDown on the butAdd handler to make sure our datasources are always up to date. 


Success!


Now we can run our web application and be able to view, add and delete records from the SQL database without writing any SQL statements. Congratulations! We have learned that we can easily use LINQ to interact with a SQL database, making it easy for us to add new records to the database as well as deleting records from it. For the full source file click “download source” below, have a good one!



LINQtoSQL