Calling an stored procedure using ADO.NET Entity Model (Entity Framework LINQ to SQL)

Create new asp.net website in your solution. Now add new project as class library named as Business Layer. Add a new project again and name it DAL.

Add the reference of BusinessLayer to the asp.net website project. Similarly add the reference of DAL project to BusinessLayer. This all we are doing as we are trying to create a layered architecture for the sample application.

Now right click on DAL project and add new item -> ADO.NET Entity Model click ok.

Now it’ll ask you to configure your Model from the database. Here I’m skipping this step assuming that you may know how to configure the model using the wizard.

After configuration your model will be added and opened and will show all your tables you have select from the wizard.

1.Now create an storedProcedure in your database.

2. Come back to you solution and open the sampleModel.edmx from the DAL project and right click.

3. Click on “Update the model from database” it’ll open the same wizard that was appeared while you were adding the Model. But this time it’ll only show the newly added item in the database.

4. Expand the stored procedure item and select your stored procedure from the list.

Now click finish and save the Model.

5. Go to the visual studio menu and  select View->Other Windows -> Entity Data Model Browser

6. Now open the Model Browser and expand EntityContainer and right click on “Fucntion Imports” and click “Add Function Import…”.

7. Name the function “GetAllEmployees” and select the available storedprocedure in the model from the dropdown list GetAllEmployees. Now select the return type so you are going to show the emplyee details so the return type would be the Entity: Employee

8. Click on and save the model.

Now write the code in your business layer to get the returned data from the Stored Prodcedure.

using DAL;

namespace BusinessLayer

{

    public class Employees

    {

        public static List<Employee> GetAllEmployeesInfo()

        {

            btnetEntities data = new btnetEntities();

            var EmpCol = data.GetAllEmployees();

            var EmpItems = from e in EmpCol

                           select new Employee

                           {

                               Eid = e.employee_id,

                               Name = e.employee_name,

                               EMail = e.email

                           };

            List<Employee> ls = new List<Employee>(EmpItems.ToList());

            return ls;

        }

    }

}

In your aspx page bind the grid:

using BusinessLayer;

namespace EFLinqToSqlDemo

{

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

    {

        protected void Page_Load(object sender, EventArgs e)

        {

            GridView1.DataSource = Employees.GetAllEmployeesInfo();

            GridView1.DataBind();

        }

    }

}

And you are done.:))

Here’s the output.

Read More

Binding a DataTable to GridView

Asp.net provides a GridView controls to display table data in Asp.Net pages, the GridViewneeds to be provided with data to populate the same in the UI.

The GridView accepts many types of datasources like DataTable, DataReader, List etc, today we shall see on how to bind the data from a DataTable to a GridView control.

Refer the post Binding a C# List to a GridView control, to Bind a List to the GridView.

First we need to define the GridView control in the .aspx page, here is the code

<asp:GridView

ID="grdUsers"

runat="server"

AutoGenerateColumns="true">

   

<HeaderStyle

BackColor="Navy"

ForeColor="White"

Font-Bold="true" />

   

<RowStyle

BackColor="White"

ForeColor="Black" />

   

<AlternatingRowStyle

BackColor="Wheat"

ForeColor="Black" />

</asp:GridView>

Once we are done with the declaration of the GridView tag, we can switch to the code behind file (.cs/.vb file) to implement the DataBinding logic. Here is the code to bind data to theGridView

string strSQL = "SELECT * FROM USERS";

DataSet dsUsers = new DataSet();

dsUsers = DataAccessLayer.GetDataSet(strSQL, "dtUSers");
//

// Implement your own DataAccess logic here to get data from the DB.
//

grdUsers.DataSource = dsUsers.Tables["dtUSers"];

grdUsers.DataBind();

The DataSet & DataTable objects are present in the System.Data Assembly; hence make sure that a reference to this assembly is added to the project before building the Project.

Build and run the project, you will be able to see the result of the query displayed in the Asp.net form in the GridView control.

Read More

LINQ to SQL Vs ADO.Net Performance Test - Loading a GridView

Asp.Net provides a number of Data Access Technologies, like ADO.Net, LINQ-to-SQL, Entity Framework etc, each one of these technologies has its own advantages and disadvantages, while designing an application; we need to identify the appropriate data access technology to achieve maximum efficiency.

Here we shall evaluate the data access performance of ADO.net and LINQ-to-SQL in populating aGridView with 1000 Rows and 8 Columns.

The configuration of the system used to perform the evaluation is as follows.

OS	Windows XP Professional 2002 SP3
Processor	Pentium® D 2.66 GHz
RAM	3 GB

The performance test was carried out for 25 iterations with both ADO.net and LINQ-to-SQL, the results of the test are as follows.

ADO.Net Average time:              18.28 Milliseconds
LINQ-to-SQL Average time:        24.96 Milliseconds

The test results clearly indicate that the conventional ADO.Net technology has a distinct edge over LINQ-to-SQL, when it comes to Data Loading operations. 

However LINQ-to-SQL is not to be under estimated as it has its own advantages, use the right technology at the right place.

The code used to perform the test is as follows

ADO.Net

SqlConnection objConn;

SqlCommand objCmd;

SqlDataAdapter objDA;

DataSet dsEmployee;

Stopwatch timer;

string strQuery = string.Empty;

timer = new Stopwatch();

timer.Start();

string strConn =ConfigurationManager.ConnectionStrings["EmployeesConnectionString"].ToString();

objConn = new SqlConnection(strConn);

strQuery = "SELECT * FROM Employee";

objCmd = new SqlCommand(strQuery, objConn);

objDA = new SqlDataAdapter(objCmd);

dsEmployee = new DataSet();

objDA.Fill(dsEmployee, "dtEmployee");

grdEmployees.DataSource = dsEmployee.Tables["dtEmployee"];

grdEmployees.DataBind();

timer.Stop();

lblExecutionTime.Text = "Execution Time (Milliseconds):" + timer.ElapsedMilliseconds.ToString();

LINQ to SQL

Stopwatch timer;

EmployeeClassesDataContext dbContext = new EmployeeClassesDataContext();

timer = new Stopwatch();

timer.Start();

var emp = (from e in dbContext.Employees select new { e.ID, e.Name, e.Phone, e.DOB, e.DOJ, e.Email, e.DepartmentID });

grdEmployees.DataSource = emp;

grdEmployees.DataBind();

timer.Stop();

lblExecutionTime.Text = "Execution Time (Milliseconds):" + timer.ElapsedMilliseconds.ToString();

That’s it we have evaluated the performance of ADO.net and LINQ-to-SQL in loading a GridViewwith 1000 Rows and 8 Columns. 

Read More

Binding a C# List to a DropDownList control

Binding a C# List to a DropDownList control

In general we use a DataTable or a DataReader to bind data to a DropDownList control, today we shall see on how to bind an object of type List to a DropDownList control.

To know more about the List object refer to the post C# List  

First let us create a class whoose objects will be stored in the List, here is the code for the class.

public class clsCountry

{

    public string _CountryCode;

    public string _CountryName;

    //

    public clsCountry(string strCode, string strName)

    {

        this._CountryCode = strCode;

        this._CountryName = strName;

    }

    //

    public string CountryCode

    {

        get {return _CountryCode;}

        set {_CountryCode = value;}

    }

    //

    public string CountryName

    {

        get { return _CountryName; }

        set { _CountryName = value; }

    }

}

Next, let us create a list of objects based on our class clsCountry and store them in a Listobject. Here is the code for the List

List<clsCountry> lstCountry = new List<clsCountry>();

lstCountry.Add(new clsCountry("USA", "United States"));

lstCountry.Add(new clsCountry("UK", "United Kingdon"));

lstCountry.Add(new clsCountry("IND", "India"));

Finally we shall bind the List object lstCountry to a DropDownList control. Here the code to bind the data.

drpCountry.DataSource = lstCountry;

drpCountry.DataValueField = "CountryCode";

drpCountry.DataTextField = "CountryName";

drpCountry.DataBind();

Notice that the DataValueField and DataTextField property of the DropDownList control are mapped to the Properties of the class CountryCode and CountryName, hence make sure to create properties for every member of the class so that they can be used while binding the data to controls.

Read More

Importing Excel Data Into Sql Server 2008 step by step

Importing Excel Data Into Sql Server 2008 step by step

The import and export wizard was available even with SQL 2000 has remained an important tool for exporting from and importing into SQL Server data from many different kinds of data sources. It can also be used for transferring data between non-Microsoft data sources. In this article, an example of transferring an MS Excel spreadsheet data to SQL Server 2008 is described. In any of the transformations it is important to realize that data types used in data sources are not exactly the same and that there are differences to be reckoned with. The basic steps to take are to indicate the source of data and the destination to which it needs to be transferred. In order to match the differences some mappings may be necessary if the source and destination are not both SQL Servers.

The MS Excel file PrincetonTemp.xls used in this example is a simple spread sheet data that shows the temperature variations during a year and the maximum recorded temperature. The data type used for the column ’Month’ is text and of the others are numbers.

Figure 1: PrincetonTemp.xls

Invoke the Import and Export Wizard

Bring up the Import and Export wizard from Start | All Programs | Microsoft SQL Server 2008 | Import and Export Data (32 bit). This pops-up the Welcome Wizard as shown. Make sure you read the explanations provided.

Figure 2: Welcome page of Import and Export Wizard

Choose source of data

Click Next. The default page gets displayed. In the 'Choose a Data Source' page click on the handle along the data source and choose Microsoft Excel file as the data source as shown.

Figure 3: Choosing the source of data

Click Next. The 'Select the source from which to copy data' shows up. Use the Browse...button to bring in the location information of PrincetonTemp.xls to the window as shown. The Excel version displayed by default (Microsoft Excel 97-2003) is proper for the MS Access version used in this article. Keep the 'First row has column names' option checked. Note that the MS Access 2007 is not supported.

Figure 4: Choosing the data file

Choosing the data destination

Click Next. The 'Choose the Destination' page shows up with SQL Server Native Client 10.0 as default and the resident server as Hodentek2\Mysorian. The server is configured for Windows authentication. Accept the defaults. In case your server is configured for SQL Server authentication you need to have the information ready. The database is displaying <default>. Click on the handle and choose a database from the drop-down list. Herein TestNorthwind is chosen. You can choose any database including the tempdb. Note that you can begin to create a new database as well, if you choose to do so by using the New...button.

Figure 5: Choosing SQL Server 2008 as the destination

Copying the table

Click Next. The 'specify the Table Copy or Query' page of the wizard shows up. Since we are transferring only one table, accept the default option, ‘Copy data from one or more tables or views ‘.

Figure 6: Choose option to copy a table

Click Next. Since sheet one has the data place check mark for 'Sheet1$' as shown. Only Sheet1 has data in this XLS file. Modify the destination column to read dbo.PrincetonTemp instead of the default [dbo].[Sheet1$] as shown.

Figure 7: Choosing a sheet from the Workbook

Click Next. In the 'Save and Run Package' page of the wizard accept the defaults shown. You could also save it as a package as well for later use.

Figure 8: Choose ‘run’ immediately option

Click Next. The 'Complete the Wizard' page gets displayed. Check if the information is correct (this is a summary of options you have chosen). If it is not correct you can hit the back button and move back to the pages you visited earlier in the reverse order.

Figure 9: The completed wizard

Click Finish. The program starts running and you should see a progress window displaying 'Performing Operation...' as shown.

When the operation is completed you should see the following window and you can keep a copy of the report as to how the import was executed using the Report drop-down button.

Figure 11: Successful execution of transfer

The import in this case was successful as shown above. If there is an error there should be a hyperlink to the message in the Message column of the above window, presently the message is ’12 rows transferred’. Close the wizard. The transfer is finished.

Verifying the import

Open the Microsoft SQL Server Management Studio and login to display the database engine using your Windows credentials. Expand the databases node and the TestNorthwind database node as shown.

Figure 12: A new table in the database

Data type mismatch and the fix

Also check if the data is brought in correctly as shown by right clicking the dbo.PrincetonTemp table and choose 'Select Top 1000 rows'. You can see that the Month names are all showing 'Null'. The ‘text’ data type in the XLS file became nvarchar type.

Figure 13: Null data in the ‘Month’ column

Modify the default mappings

In order to fix this, you can use either Drop table statement or right click and choose delete to delete the table from the TestNorthwind database. In the Delete Object window click OK. Refresh the Tables node by right clicking the Tables and choosing refresh. Now the imported table is gone.

Repeat the process that you did earlier and when you come to the stage shown in Figure.6 click on the table Edit Mappings...button. The Column Mappings page shows up as in the next figure

Figure 14: Column mappings window

The month column data type for the destination is nvarchar (255). The Source had 'Text' as data type for this column. We need to cast it properly. Click on nvarchar in the 'Type' column and change it to ‘char’ as shown. Click OK. Change destination table name from [dbo].[Sheet1$] to [dbo].[PrincetonTemp] as done previously. Click Next.

Figure 15: Modify mapping

In the 'Save and Run Package' page accept defaults as previously. Click Next. The 'Complete the Wizard' page shows up. Click Finish. You get the wizard announcing 'The execution was successful'. Close the wizard.

Refresh the Tables node of the Northwind database in Management Studio. Now right click the PrincetonTemp and choose to select top 1000 rows as before. You will see that all the data in source is in the destination.

Figure 16: The Source data is imported correctly

Read More