Geeks With Blogs

News

Microsoft MVP


DZone MVB


Moderator at CodeASP.NET


Quiz Master







free counters
Free counters
Added on January 19,2012


Follow Me @vmsdurano

A bit About Me



Disclaimer
The opinions expressed herein are my own personal opinions and does not represent the opinions of my employers. Nor does it represent the opinion of my dog, because I don’t have one.


Vinz' Blog (ProudMonkey) "Code, Beer and Music ~ my way of being a programmer"

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:

 

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

    <title>LINQ to SQL Demo Part 2</title>

    <style type="text/css">

        .style1{width: 400px;}

        .style1 td {width:200px;}

    </style>

</head>

<body>

    <form id="form1" runat="server">

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

            AutoPostBack="true"

            onselectedindexchanged="DropDownListCustomerID_SelectedIndexChanged">

        </asp:DropDownList>

        <br />

        <table class="style1">

            <tr>

                <td>Company Name</td>

                <td><asp:TextBox ID="TextBoxCompanyName" runat="server" ReadOnly="true"/></td>

            </tr>

            <tr>

                <td>Contact Name</td>

                <td><asp:TextBox ID="TextBoxContactName" runat="server" ReadOnly="true"/></td>

            </tr>

            <tr>

                <td>Contact Title</td>

                <td><asp:TextBox ID="TextBoxContactTitle" runat="server" ReadOnly="true"/></td>

            </tr>

            <tr>

                <td>Address</td>

                <td><asp:TextBox ID="TextBoxAddress" runat="server" ReadOnly="true"/></td>

            </tr>

            <tr>

                <td>City</td>

                <td><asp:TextBox ID="TextBoxCity" runat="server" ReadOnly="true"/></td>

            </tr>

            <tr>

                <td>Region</td>

                <td><asp:TextBox ID="TextBoxRegion" runat="server" ReadOnly="true"/></td>

            </tr>

            <tr>

                <td>Postal Code</td>

                <td><asp:TextBox ID="TextBoxPostalCode" runat="server" ReadOnly="true"/></td>

            </tr>

            <tr>

                <td>Country</td>

                <td><asp:TextBox ID="TextBoxCountry" runat="server" ReadOnly="true"/></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!

Technorati Tags: ,,
Posted on Thursday, March 11, 2010 3:26 PM ASP.NET , C# , LINQ to SQL | Back to top


Comments on this post: Fetching Data from Database and Populating fields in the Form using LINQ to SQL

# re: Fetching Data from Database and Populating fields in the Form using LINQ to SQL
Requesting Gravatar...
Hey, this article was really useful as it ,makes you understand how to implement the query and collection types with linq
Left by Manoj on Mar 19, 2010 1:02 PM

# re: Fetching Data from Database and Populating fields in the Form using LINQ to SQL
Requesting Gravatar...
can u please tell this abpve code in chsarp
Left by sarah on Apr 13, 2011 3:22 PM

# re: Fetching Data from Database and Populating fields in the Form using LINQ to SQL
Requesting Gravatar...
@sarah,

The code above is already in C#.
Left by Vinz on Apr 13, 2011 4:15 PM

# re: Fetching Data from Database and Populating fields in the Form using LINQ to SQL
Requesting Gravatar...


Can i get this same code in visual basic?
Left by ted on Mar 18, 2012 6:35 PM

# re: Fetching Data from Database and Populating fields in the Form using LINQ to SQL
Requesting Gravatar...
Realy ossum very clear n simple to understand and even in a simple way
Left by Ashish on Apr 25, 2012 7:47 PM

# re: Fetching Data from Database and Populating fields in the Form using LINQ to SQL
Requesting Gravatar...
this is my stored procedure i want to display idcount in text box. the id is displaying -1 how can i get the idcount in text box

ALTER PROCEDURE [dbo].[Transaction]
(

@Patient nvarchar(50),
@E_TO nvarchar(50),
@R_type int,
@User_id uniqueidentifier,
@ReportType nvarchar(50),
@Patient_no int,
@Patient_ID_NO numeric(18,0),
@idcount int output

)
AS
BEGIN

declare @tempid int
set @tempid = 0;
declare @idcnt int
select @idcnt =isnull( max(idcount),0) from Transactions where year(R_date)=year(getdate())
if (@idcnt =0)
set @tempid=1
else
set @tempid = @idcnt +1


INSERT INTO dbo.Transactions (Patient,E_TO,R_date,R_from,User_id,report_type,Patient_no,Patient_ID_NO,idcount)values (@Patient,@E_TO,getdate(),@R_type,@User_id,@ReportType,@Patient_no,@Patient_ID_NO,@tempid)
SET NOCOUNT ON;

SELECT @idcount = ISNULL(idcount,'')
FROM Transactions
WHere Patient = @Patient
END

int? idcount = -1;

try
{

TransDataContext db = new TransDataContext();

try
{

var q = db.Transaction(Patient_name, Export_TO, replay_To_type, null, repType, PatNoVal, PatID, ref idcount);

}

TextBox1.Text = "The ID number is" + "\r\n" + NameTxtBx.Text + "\r\n" + idcount.ToString();
Left by abdul on Jun 30, 2012 7:24 PM

# re: Fetching Data from Database and Populating fields in the Form using asp
Requesting Gravatar...
sir i want to know that how can i assign a data from oracle in asp into variable
Left by ajeet kumar on Feb 01, 2013 4:21 AM

comments powered by Disqus

Copyright © Vincent Maverick Durano | Powered by: GeeksWithBlogs.net