Friday, 10 October 2014

Exporting datagrid to CSV using Save Dialog in C#.NET

Below is the code for exporting the data of a data grid in C#.Net

Datagrid is as follows:

System.Windows.Forms.SaveFileDialog saveDlg = new System.Windows.Forms.SaveFileDialog();
                saveDlg.InitialDirectory = @"C:\";
                saveDlg.Filter = "CSV files (*.csv)|*.csv";
                saveDlg.FilterIndex = 0;
                saveDlg.RestoreDirectory = true;
                saveDlg.Title = "Export csv File To";
                if (saveDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                    string CsvFpath = saveDlg.FileName;
                    System.IO.StreamWriter csvFileWriter = new StreamWriter(CsvFpath, false);
                    string columnHeaderText = "";
                    int countColumn = comparisonGrid.Columns.Count - 1;
                    if (countColumn >= 0)
                        columnHeaderText = (comparisonGrid.Columns[0].Header).ToString();

                    // Writing column headers
                    for (int i = 1; i <= countColumn; i++)
                        columnHeaderText = columnHeaderText + ',' + (comparisonGrid.Columns[i].Header).ToString();

                    // Writing values row by row
                    for (int i = 0; i <= comparisonGrid.Items.Count - 2; i++)
                        string dataFromGrid = "";
                        for (int j = 0; j <= comparisonGrid.Columns.Count - 1; j++)
                            if (j == 0)
                                dataFromGrid = ((DataRowView)comparisonGrid.Items[i]).Row.ItemArray[j].ToString();
                                dataFromGrid = dataFromGrid + ',' + ((DataRowView)comparisonGrid.Items[i]).Row.ItemArray[j].ToString();

The content of output csv file looks like:

Thursday, 9 October 2014

Exporting data grid to Excel with Save Dialog box in C#.Net

I’ve the following data grid (say comparisonGrid) and I'll export this data into excel by following the below steps.

Step 1: Include the following namespace to your code behind file (.cs file)
using Excel = Microsoft.Office.Interop.Excel;

Step 2:  Now you would need to instantiate the instances of Excel application, excel workbook and worksheet as below (I'll do that in the export button click event):
                Excel.Application xlApp;
                Excel.Workbook xlWorkBook;
                Excel.Worksheet xlWorkSheet;
                object misValue = System.Reflection.Missing.Value;
                xlApp = new Excel.ApplicationClass();
                xlWorkBook = xlApp.Workbooks.Add(misValue);
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

Step 3: Fill in the excel sheet with the values of the data grid (cell by cell) as follows:
               for (int i = 0; i <= comparisonGrid.Items.Count - 1; i++)
                    for (int j = 0; j <= comparisonGrid.Columns.Count - 1; j++)
                             xlWorkSheet.Cells[i + 1, j + 1] =  
                             ( (DataRowView)comparisonGrid.Items[i]).Row.ItemArray[j].ToString();

Step 4: For saving the file using “Save dialog” box, you would add the following code:
               System.Windows.Forms.SaveFileDialog saveDlg = new System.Windows.Forms.SaveFileDialog();
                saveDlg.InitialDirectory = @"C:\";
                saveDlg.Filter = "Excel files (*.xls)|*.xls";
                saveDlg.FilterIndex = 0;
                saveDlg.RestoreDirectory = true;
                saveDlg.Title = "Export Excel File To";
             if (saveDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                    string path = saveDlg.FileName;
                    xlWorkBook.Saved = true;
                    xlWorkBook.Close(true, misValue, misValue);

You may encounter the below error:
Error 1 Interop type 'Microsoft.Office.Interop.Excel.ApplicationClass' cannot be embedded. Use the applicable interface instead.
Fix:In your Project, expand the "References", find the Microsoft Office Interop reference. Right click it and select properties, and change "Embed Interop Types" to false. Add the following refrence “using Excel = Microsoft.Office.Interop.Excel;”

Monday, 4 August 2014

Creating WCF Client from Service Metadata

Below are the steps to create WCF clients from service metadata using Visual Studio 2013:

I'm assuming you've a WCF service created and running on WCF Test client.

1. Open Visual Studio command prompt. You can find in under Start Menu -> All Programs -> Visual Studio Tools. If you're working on Windows 7, please make sure you "Run as administrator".
2. Type in the below command:

svcutil.exe /out:<OutputFileName>.cs /config:<configFileName>.config <Service URL>

For example:
If the service url is http://localhost:54225/TestService.svc

Type in the below command:
svcutil.exe /out:WCFGeneratedProxy.cs /config:output.config http://localhost:54225/TestService.svc

This will generate the proxy file and an output.config file. Add WCFGeneratedProxy.cs file to your project and add the content of output.config file to the web.config file.

Sunday, 12 January 2014

Working with LINQ to SQL

Creating an ASP.NET website and working with LINQ

You can use LINQ to XML for following purposes-

1. For creating xml file.
2. For loading an xml file.
3. For querying xml data.
4. Manipulating xml data.
5. Transforming the xml into another shape.

In this example we will load an already created xml file. We will use Contacts.xml file. The content of this file is as shown below-

<id> 1 </id>
<name> Name1 </name>
     <department> IT </department>
<id> 2 </id>
<name> Name2 </name>
     <department> IT </department>
     <id> 3 </id>
     <name> Name3 </name>
     <department> CS </department>
     <id> 4 </id>
     <name> Name4 </name>
     <department> CS </department>

Follow the below steps to query through the xml file-

1. Open Visual Studio and create an ASP.NET website called Linq2Xml by using C#.
2. Add the above created Contacts.xml file to the App_Code directory of your solution.
3. Add a new class file called Contact.cs to the solution. This class represents an contact object and will be used when the xml is turned to a collection of strongly typed objects. The following code shows an example-

namespace Linq2Xml
    public class Contact
        public int id { get; set; }
        public string name { get; set; }
        public string department { get; set; }

4. Add a new class file called ContactServices.cs. This class will expose methods that use LINQ to work with the xml file. Add following code to this file.

 i. Add a class level variable to read the class file. Also, add the reference for namespace System.Xml.Linq.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Xml.Linq;

namespace Linq2Xml
    public class ContactServices
        XElement _conXml = XElement.Load("c:\\....\\visual studio 2010\\Projects\\Linq2Xml\\Linq2Xml\\App_Data\\Contacts.xml");
. . . 

// define methods here

ii. Add a method called GetDepartments to return the distinct departments from the xml file.

public List<string> GetDepartments()
            var deptQuery =
                from con in _conXml.Descendants("contact")
                group con by con.Element("department").Value
                    into conGroup
                    select conGroup.First().Element("department").Value;
            return deptQuery.ToList();
iii. Add another method called GetContactsByDept that takes department as a parameter. It will then query the xml file and returns department with a matching name. The query will transform data into a list of Contact object.

public List<Contact> GetContactsByDept(string department)
            IEnumerable<Contact> conQuery =
                from con in _conXml.Descendants("contact")
                where con.Element("department").Value == department
                select new Contact
                    id = Convert.ToInt32(con.Element("id").Value),
                    department = con.Element("department").Value,
                    name = con.Element("name").Value,
            return conQuery.ToList();

5. Open the default.aspx page. Add a label, Dropdown list, Button, GridView and ObjectDataSource controls to the page.

6. Configure the ObjectDataSource to point to ContactServices.GetDepartments method. 

7. Configure the dropdown list to use the ObjectDataSource.

The markup inside the MainContent placeholder should look similar to the following-

<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
    <asp:Label ID="Label1" runat="server" Text="Select a department:"></asp:Label>
    <br />
    <br />
    <asp:DropDownList ID="DropDownListDepts" runat="server" DataSourceID="ObjectDataSourceDept">
    <asp:Button ID="Button1" runat="server" Text="Update " onclick="Button1_Click" />
    <br />
    <br />
    <asp:GridView ID="GridViewContacts" runat="server">
    <br />
    <asp:ObjectDataSource ID="ObjectDataSourceDept" runat="server" SelectMethod="GetDepartments"

8. Add a click event to the button control. Inside the code for the click event, call the ContactServices. GetContactsByDept method and pass the selected method from the dropdown list. Update the data in the gridView control with the results. The following code shows an example-
protected void Button1_Click(object sender, EventArgs e)
            ContactServices conSrv = new ContactServices();
            GridViewContacts.DataSource =

9. Run your application. Select a department and click on Update button.  Your result should look similar to the below figure.