DotNet Brother - .Net Online Training : 02/15/13

INSERTING DATA TO DATABASE USING LINQ TO SQL
Few months ago, I’ve created a simple demo about “Creating a Simple Registration Form using the ADO.NET way”. In this article, I'm going to demonstrate how to create a simple form that would allows users to insert data to the database using L2S.

As an overview, LINQ to SQL is a technology that allow you to query sql server. LINQ to SQL is an O/RM (object relational mapping) implementation that ships in the .NET Framework "Orcas" release, and which allows you to model a relational database using .NET classes. You can then query the database using LINQ, as well as update/insert/delete data from it.

I will not cover much on details about it in this article so if you need to know more about this technology then you can refer to this link: http://msdn.microsoft.com/en-us/library/bb425822.aspx

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:

There are two basic ways on how to display two fields from database in the DropDownList. The first one is concatenating it in the sql query and the second one is concatening it programmatically in codes. Manipulating the sql query to concatenate two fields. Here’s the code block below.

private void BindDropDownList()
    {

        DataTable dt = new DataTable();
        SqlConnection connection = new SqlConnection(GetConnectionString());
        try
        {

            connection.Open();
            string sqlStatement = "SELECT CustomerID + ' ---- ' + ContactName AS Name, CustomerID FROM Customers";
            SqlCommand sqlCmd = new SqlCommand(sqlStatement, connection);
            SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);

            sqlDa.Fill(dt);
            if (dt.Rows.Count > 0)
            {
                DropDownList1.DataSource = dt;
                DropDownList1.DataTextField = "Name";
                DropDownList1.DataValueField = "CustomerID"; 
                DropDownList1.DataBind();
            }
        }
        catch (System.Data.SqlClient.SqlException ex)
        {
            string msg = "Fetch Error:";
            msg += ex.Message;
            throw new Exception(msg);
        }
        finally
        {
            connection.Close();
        }
    }


As you can see above, we concatenate two fields  “CustomerID and ContactName” by separating it by "----"  to make it as one field called “Name” in the sql query .

Programmatically concatenating two fields in the DropDownList. Here’s the code block below.








private void BindDropDownList()
    {

        DataTable dt = new DataTable();
        string id = string.Empty;
        string name = string.Empty;
        string newName = string.Empty;

        SqlConnection connection = new SqlConnection(GetConnectionString());
        try
        {
            connection.Open();
            string sqlStatement = "SELECT * FROM Customers";
            SqlCommand sqlCmd = new SqlCommand(sqlStatement, connection);
            SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);

            sqlDa.Fill(dt);
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    id = dt.Rows[i]["CustomerID"].ToString();
                    name = dt.Rows[i]["ContactName"].ToString();
                    newName = id + " ---- " + name;
                    DropDownList1.Items.Add(new ListItem(newName,id));
                }
            }
        }
        catch (System.Data.SqlClient.SqlException ex)
        {
            string msg = "Fetch Error:";
            msg += ex.Message;
            throw new Exception(msg);
        }
        finally
        {
            connection.Close();
        }
    }


As you can see above, we loop through the DataTable and passed the field values in the string variable called “id and name” and set the concatenated values in a string variable called “newName”. To test, call the method BindDropDownList() at Page_Load event. The page output should look like below.

15 February 2013

INSERTING DATA TO DATABASE USING LINQ TO SQL

DISPLAYING TWO COLUMN FIELDS IN DROPDOWNLIST CONTROL