DotNetOcean: Linq to SQL Screen Shot

STEP 1: Creating a new Website in Visual Studio

To get started then lets go ahead and fire up Visual Studio 2008 and create a new WebSite by selecting File > New WebSite.

STEP 2: Adding a DBML file

Since we are going to use L2S then we need to add .dbml file. To do this, just right click on the application root and select Add New Item. On the template select LINQ to SQL Classes file. See below screen shot:

Now rename your dbml file the way you want it and then click OK. Note that I’m using the Northwind database for this demo and on that case I renamed the dbml file to Northwind to make it friendlier.

Now open up server explorer in Visual Studio and browse the database that you wan’t to work on (in this case the Northwind database). Just for the purpose of this example I’m going to use the Customers table from the northwind database and drag it to the Northwind.dbml design surface. See the screen shot below:

That’s simple! Isn’t it?

What happens there is that by time you drag a table in the design surface, L2S will automatically generates the Business object for you within the DataContext and let you query against it.The DataContext is the main gateway by which you retrieve objects from the database and resubmit changes. You use it in the same way that you would use an ADO.NET Connection. In fact, the DataContext is initialized with a connection or connection string you supply. The purpose of the DataContext is to translate your requests for objects into SQL queries made against the database and then assemble objects out of the results. The DataContext enables language-integrated query by implementing the same operator pattern as the standard query operators such as Where and Select

STEP 3: Setting up the GUI

Now let’s go ahead and create our form for data entry. For the simplicity of this demo, I just set up the form like below:

<title>Untitled Page</title>

.style1{width: 400px;}

.style1 td {width:200px;}

</style>

</head>

<body>

<asp:Literal ID="LiteralMessage" runat="server"></asp:Literal>

<tr>

<td>Company ID</td>

</tr>

<tr>

<td>Company Name</td>

</tr>

<tr>

<td>Contact Name</td>

</tr>

<tr>

<td>Contact Title</td>

</tr>

<tr>

<td>Address</td>

</tr>

<tr>

</tr>

<tr>

<td>Region</td>

</tr>

<tr>

<td>Postal Code</td>

</tr>

<tr>

<td>Country</td>

</tr>

</table>

<asp:Button ID="Button1" runat="server" Text="Save" onclick="Button1_Click" />

</form>

</body>

</html>

STEP 4: Creating the SaveCustomerInfo() method

After setting up our GUI then let’s go ahead and create the method for inserting the data to the database using L2S. Here are the code blocks below:

using System;

using System.Configuration;

using System.Data;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Xml.Linq;

public partial class _Default : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

}

protected void Button1_Click(object sender, EventArgs e)

{

SaveCustomerInfo();

}

private void SaveCustomerInfo()

{

using (NorthwindDataContext context = new NorthwindDataContext())

{

//Create a new instance of the Customer object

Customer cust = new Customer();

//Add new values to each fields

cust.CustomerID = TextBoxID.Text;

cust.CompanyName = TextBoxCompanyName.Text;

cust.ContactName = TextBoxContactName.Text;

cust.ContactTitle = TextBoxContactTitle.Text;

cust.Address = TextBoxAddress.Text;

cust.City = TextBoxCity.Text;

cust.Region = TextBoxRegion.Text;

cust.PostalCode = TextBoxPostalCode.Text;

cust.Country = TextBoxCountry.Text;

//Insert the new Customer object

context.Customers.InsertOnSubmit(cust);

//Sumbit changes to the database

context.SubmitChanges();

//Display Message for successful operation

LiteralMessage.Text = "<p style='color:Green;'>Information Successfully saved!</p>";

}

As you can see, the code above was very straight forward. First we have created a new instance of the DataContext which we had created on STEP 2 and wrapped it inside the “using” block; this is to ensure that the DataContext will be disposed after its processing. Second we created a new instance of the Customer object that was defined within the DataContext, this object has properties which will be filled with values that comes from the user inputs. Third we inserted a new Customer object to the Customers set and then call the context.SubmitChanges to update our database. Lastly, L2S will do the rest for you ;).

Note: The Customer and Customers set objects are automatically created once you’ve added the Customer table in the .dmbl design surface.

STEP 5: Run the code

Running the code above will look something like below on the browser:

From there we can fill in those fields with values we want. Just for this demo, notice that I have filled in those fields with a sample data. Hitting the save button will invoke the method SaveCustomerInfo() which is responsible for doing the insert operation. Now if we look at the database we can see that the data we entered was successfully being saved to the database. See the screen shot below:

Cool right? ;)

Okay, I know that you have few questions that pops in your mind now and these are:

· What happened behind the scene? How does it actually save the data to the database?

· How does the query being constructed? Does it handle SQL Injection?

· How does the connection string being set up? What If I want to set the connection string manually?

· Does L2S always open the connection to the database once we created a new instance of the DataContext?

To answer the questions that you have in mind then I would suggest you to give this FAQ a read: http://msdn.microsoft.com/en-us/library/bb386929.aspx

I hope someone find this useful!

Fetching Data from Database and Populating fields in the Form using LINQ to SQL

In my previous example I have demonstrated how to create a simple form that would allow users to insert data to the database using L2S. In this example I’m going to demonstrate how to fetch and filter data from database and fill the fields in the form using L2S. This example is basically a continuation of my previous example here. So I would suggest you to take a look at my previous example first before you proceed reading this example.

STEP 1: Setting up the GUI

To get started, add a new WebForm to your application and set up the GUI. Again just for the simplicity of this demo,I just set up the GUI like this:

.style1{width: 400px;}

.style1 td {width:200px;}

</style>

</head>

<body>

<asp:DropDownList ID="DropDownListCustomerID" runat="server"

AutoPostBack="true"

onselectedindexchanged="DropDownListCustomerID_SelectedIndexChanged">

</asp:DropDownList>

<br />

<tr>

<td>Company Name</td>

</tr>

<tr>

<td>Contact Name</td>

</tr>

<tr>

<td>Contact Title</td>

</tr>

<tr>

<td>Address</td>

</tr>

<tr>

</tr>

<tr>

<td>Region</td>

</tr>

<tr>

<td>Postal Code</td>

</tr>

<tr>

<td>Country</td>

</tr>

</table>

</form>

</body>

</html>

If you notice, I set the ReadOnly attribute of each TextBox to True; this is because we don’t need users to edit the fields in the form once the TextBox is filled with data.

STEP 2: Populating the DropDownList with the list of Customers.

Now on our code behind page let’s go ahead and create the method for fetching the list of customers. Here’s the code block below:

private List<Customer> GetCustomers(){

using (NorthwindDataContext context = new NorthwindDataContext()){

return (from c in context.Customers select c).ToList();

}

The code above is the LINQ syntax for querying data. It basically query the Customers object that is generated from the DataContext and then fetch the results.

Since we are done creating the method for fetching the list of customers then we can simply call that method and populate the DropDownList with the results. Typically we do this at Page_Load event within Not IsPostBack block like below:

protected void Page_Load(object sender, EventArgs e)

{

if (!Page.IsPostBack) {

DropDownListCustomerID.DataSource = GetCustomers();

DropDownListCustomerID.DataTextField = "ContactName";

DropDownListCustomerID.DataValueField = "CustomerID";

DropDownListCustomerID.DataBind();

}

As you can see the code above is very straight forward and self explanatory. Running the code above will display something like below:

Let’s proceed and continue with the next Step.

STEP 3: Populating the form with Customer’s Information

The next step is we are going to populate the form with the customer information based on the CustomerID selected from the DropDownList.

Note: Since the form will be populated based on the selected item from the DropDownList then you’ll need to set up the AutoPostBack attribute to TRUE in the DropDownList so that the SelectedIndexChanged event will fire up.

Here’s the code block below for fetching the customer information based on customer ID:

private List<Customer> GetCustomerInfo(string customerID) {

using (NorthwindDataContext context = new NorthwindDataContext()){

return(from c in context.Customers

where c.CustomerID == customerID

select c).ToList();

}

The code above is the LINQ syntax for querying data. As you can see we created a new instance of the DataContext and query the Customers object based on the parameter we passed on the GetCustomerInfo() method. Once we invoke the LINQ ToList() function, this LINQ query will issue a parameterize SQL query to the database in which the SQL Server can understand and then bring back the results to the DataContext.

One of the cool things about L2S is we don’t need to worry about how the query is being constructed because L2S will take care of that for you including mapping of the data types from your table columns, mapping relationships between tables, etcetera, etcetera and etcetera. Always keep in mind that L2S is an ORM (Object Relational Mapper) and so we don’t need to deal directly with databases, tables and columns but instead we deal with the objects that is in the DataContext and query the data against it using LINQ syntax.

STEP 4: Populating the Forms with Data

The next step is to populate our form with data based on the selected value from the DropDownList. To do this, we can simply call the method GetCustomerInfo() at the SelectedIndexChanged event of DropDownList like below:

protected void DropDownListCustomerID_SelectedIndexChanged(object sender, EventArgs e) {

var customerInfo = GetCustomerInfo(DropDownListCustomerID.SelectedValue);

TextBoxCompanyName.Text = customerInfo[0].CompanyName;

TextBoxContactName.Text = customerInfo[0].ContactName;

TextBoxContactTitle.Text = customerInfo[0].ContactTitle;

TextBoxAddress.Text = customerInfo[0].Address;

TextBoxCity.Text = customerInfo[0].City;

TextBoxRegion.Text = customerInfo[0].Region;

TextBoxPostalCode.Text = customerInfo[0].PostalCode;

TextBoxCountry.Text = customerInfo[0].Country;

}

The code above calls the method GetCustomerInfo() and pass the selected value of the DropDownList as a parameter to that method. We then store it a customerInfo implicit typed variable and assign each TextBox with the corresponding data returned from the query.

When you run the code above and select an item in the DropDownList, you will see that the textbox fields will be populated with data based from what you have selected in the DropDownList.

That's it! I Hope someone find this post useful!

Editing, Updating and Deleting Data in the Form using LINQ to SQL

In my previous example we talked about how to fetch data from database and how to populate the form with data using L2S. In this example I’m going to extend a little bit of what I have demonstrated in my previous example. Basically I’m going to show you the basic way on how to edit and delete data from the form and update the database using L2S technology. Since this is a continuation of my previous example so I would suggest you to refer that first before you go any further.

STEP 1: Setting up the GUI

Just for the purpose of this demo, I set up the GUI like below:

.style1{width: 400px;}

.style1 td {width:200px;}

</style>

</head>

<body>

<asp:DropDownList ID="DropDownListCustomerID" runat="server"

AutoPostBack="true"

onselectedindexchanged="DropDownListCustomerID_SelectedIndexChanged">

</asp:DropDownList>

<br />

<asp:Literal ID="LiteralMessage" runat="server"></asp:Literal><br />

<asp:Button ID="ButtonEdit" runat="server" Text="Edit" Enabled="false" onclick="ButtonEdit_Click" />

<asp:Button ID="ButtonDelete" runat="server" Text="Delete" Enabled="false" />

<asp:Button ID="ButtonUpdate" runat="server" Text="Update" Enabled="false"/>

<asp:Button ID="ButtonCancel" runat="server" Text="Cancel" Enabled="false"/>

<asp:Panel ID="PanelCustomerInfo" runat="server" Enabled="false">

<tr>

<td>Company Name</td>

</tr>

<tr>

<td>Contact Name</td>

</tr>

<tr>

<td>Contact Title</td>

</tr>

<tr>

<td>Address</td>

</tr>

<tr>

</tr>

<tr>

<td>Region</td>

</tr>

<tr>

<td>Postal Code</td>

</tr>

<tr>

<td>Country</td>

</tr>

</table>

</asp:Panel>

</form>

</body>

</html>

If you look at my previous example you will notice that the html markup above is a bit similar. The things that are added above are Buttons for Edit, Delete, Update and Cancel which is by default set their enable property to false, this is to prevent users from doing certain actions when the page is loaded in the browser. Aside from that I have also move the form fields inside a Panel control for validation purposes and removed the ReadOnly attribute for each TextBox.

STEP 2: Populating the DropDownList with the list of Customers and populate the form with the Customers information.

Now the next step if we are going to populate the DropDownList with the list of customers and populate the form with the customers information based on the customer ID selected from the DropDownList. Here are the code blocks below:

private List<Customer> GetCustomers(){

using (NorthwindDataContext context = new NorthwindDataContext()){

return (from c in context.Customers select c).ToList();

}

private List<Customer> GetCustomerInfo(string customerID) {

using (NorthwindDataContext context = new NorthwindDataContext()){

return(from c in context.Customers

where c.CustomerID == customerID

select c).ToList();

}

private void BindCustomersToList(){

DropDownListCustomerID.DataSource = GetCustomers();

DropDownListCustomerID.DataTextField = "ContactName";

DropDownListCustomerID.DataValueField = "CustomerID";

DropDownListCustomerID.DataBind();

}

protected void Page_Load(object sender, EventArgs e){

if (!Page.IsPostBack) {

BindCustomersToList();

}

protected void DropDownListCustomerID_SelectedIndexChanged(object sender, EventArgs e) {

var customerInfo = GetCustomerInfo(DropDownListCustomerID.SelectedValue);

TextBoxCompanyName.Text = customerInfo[0].CompanyName;

TextBoxContactName.Text = customerInfo[0].ContactName;

TextBoxContactTitle.Text = customerInfo[0].ContactTitle;

TextBoxAddress.Text = customerInfo[0].Address;

TextBoxCity.Text = customerInfo[0].City;

TextBoxRegion.Text = customerInfo[0].Region;

TextBoxPostalCode.Text = customerInfo[0].PostalCode;

TextBoxCountry.Text = customerInfo[0].Country;

ButtonEdit.Enabled = true;

ButtonDelete.Enabled = true;

}

In this step I will not elaborate more on details because I have already demonstrated this in my previous example here. The only thing that’s added in the code above is we are setting the Enabled attribute of the Edit and Delete Button to true so that by the time users select certain Customer from the DropDownList then that’s the time that they can do certain operations like editing and deleting.

Running the code above will show something like this in the browser:

Selecting customers from the DropDownList

After selecting customers from the DropDownList

STEP 3: Editing the Form

Here’s the code for the Edit Button

protected void ButtonEdit_Click(object sender, EventArgs e)

{

PanelCustomerInfo.Enabled = true;

DropDownListCustomerID.Enabled = false;

ButtonEdit.Enabled = false;

ButtonDelete.Enabled = false;

ButtonUpdate.Enabled = true;

ButtonCancel.Enabled = true;

LiteralMessage.Text = string.Empty;

}

As you can see there’s nothing special about the codes above. It just basically does some basic validations when you hit the Edit button on the form.

STEP 4: Updating the Form

Here’s the code for the Update method

private void UpdateCustomerInfo(string ID)

{

using (NorthwindDataContext context = new NorthwindDataContext())

{

var customer = (from c in context.Customers

where c.CustomerID == ID

select c).Single();

customer.CompanyName = TextBoxCompanyName.Text;

customer.ContactName = TextBoxContactName.Text;

customer.ContactTitle = TextBoxContactTitle.Text;

customer.Address = TextBoxAddress.Text;

customer.City = TextBoxCity.Text;

customer.Region = TextBoxRegion.Text;

customer.PostalCode = TextBoxPostalCode.Text;

customer.Country = TextBoxCountry.Text;

context.SubmitChanges();

LiteralMessage.Text = "<p style='color:Green;'>Information Updated!</p>";

}

As you can see, the code above is very straight forward and self explanatory. What happened there is we created a new instance of the DataContext and then we query the Customer object based on the ID using the LINQ syntax and passed it in a variable customer. The Single function is an eager function which returns the only element of a sequence that satisfies a specified condition. Once the LINQ Single function is invoked then DataContext will issue a parameterize SQL query to the database in which the SQL Server can understand and then bring back the results to the DataContext.

From there, we can then assign the customer fields based on the TextBox values and then call the context.SubmitChanges() method to update the database with the changes we made.

Now let’s try to run the code and see what happens:

Invoking the Edit Button

On Editing

After Invoking the Update Button

STEP 5: Deleting Customer

Here’s the code for the Delete method

private void DeleteCustomerInfo(string ID)

{

using (NorthwindDataContext context = new NorthwindDataContext())

{

var customer =(from c in context.Customers

where c.CustomerID == ID

select c).First();

context.Customers.DeleteOnSubmit(customer);

context.SubmitChanges();

LiteralMessage.Text = "<p style='color:Green;'>Information Deleted!</p>";

}

Just like in the update method, the code above creates a new instance of the DataContext and then query the customer entity based on the ID. Note that since I am using the northwind database in this demo, then deleting of customer data directly will throw an exception because this table is being referenced to other table like Orders . So in order for the code above to work and just for the simplicity of this demo, I remove the relationships to the table that referenced it. If you wan't to implement cascade delete then you have to delete the related information to the other table. So for example if you have customer and this customer has orders then you'll have to perform delete in both tables to avoid exceptions. Here's an article that you can refer:Cascading Deletes in LINQ to SQL

Since we don’t want users to delete the information right away, we need to prompt them a confirmation message if they wish to continue the deletion or not. To do this we could simply hook up the javascript confirm function in the delete button. Take a look at the highlighted code below:

<asp:Button ID="ButtonDelete" runat="server" Text="Delete" Enabled="false"

onclick="ButtonDelete_Click" OnClientClick="return confirm('The selected customer will be deleted. Do you wish to continue?');return false;" />

Now let’s create the method for clearing the text fields and then call the method created above on click on the delete button. Here are the code blocks below:

public static void ClearFormFields(Control Parent)

    {
        if (Parent is TextBox)
        { (Parent as TextBox).Text = string.Empty; }
        else
        {
            foreach (Control c in Parent.Controls)
                ClearFormFields(c);
        }
    }

protected void ButtonDelete_Click(object sender, EventArgs e)

{

//Call the DELETE Method

DeleteCustomerInfo(DropDownListCustomerID.SelectedValue);

//Rebind the DropDownList to reflect the changes after deletion

BindCustomersToList();

//Clear the fields

ClearFormFields(Page);

}

Here's the output below when running the page and perform the delete:

On Deletion

After Deletion

STEP 6: Cancelling Operation

Here’s the code for the Cancel Button

protected void ButtonCancel_Click(object sender, EventArgs e)

{

PanelCustomerInfo.Enabled = false;

DropDownListCustomerID.Enabled = true;

ButtonEdit.Enabled = true;

ButtonDelete.Enabled = true;

ButtonUpdate.Enabled = false;

ButtonCancel.Enabled = false;

}

There's nothing fancy about the code above, It just basically toggles the enable property of the button when you hit the Cancel button on the form so that it will return to its default state.

SUMMARY:
In this demo we have learned about the basics on how to perform Edit,Update and Delete using the LINQ to SQL technology.
That's it! I hope someone find this useful!

Thanks shibashish mohanty