XamDataGrid  has a property HighLightingAlternateRecords which has to be set to true if we need  alternating rows highlighted, but when a XamDataGrid is used to display treeview setting this property does not show alternating rows highlighted. This article shows an alternative way to display the same.

The XamDataGrid has two events XamDataGrid_RecordCollapsed and  XamDataGrid_RecordExpanded, which we can use to achieve this functionality.

First of all we need to display a treeView in XamdataGrid, to do the same we can follow the step mentioned in the below link

http://community.infragistics.com/forums/p/16974/213729.aspx#213729

So now I assume that we have a class (Node) defined which contains the properties required to display the TreeView nodes and we are going to bind an ObservableCollection<Node> to the XamDataGrid.DataSource as shown in the above link.

To get the alternate rows highlighting we need to add one more property to the class Node called as IsVisuallyExpanded to know if the tree node is expanded to show all its child items.

 We also need to get the list of items that are bound to the XamDataGrid in a linear fashion without the tree structure and by using the IsVisuallyExpanded property that we added, we can get only those nodes which are currently visible in the tree view (excluding the child items which are collapsed).

 When the tree node is collapsed, we first find which node was collapsed and for this node and all its child nodes we set the IsVisuallyExpaned property to false as shown below.

 void XamDataGrid1_RecordCollapsed(object sender, RecordCollapsedEventArgs e) {

           var treeNodeThatWasCollapsed = ((e.Record as DataRecord).DataItem as Node);

            foreach (var child in treeNodeThatWasCollapsed.ChildNodes) {

             child.IsVisuallyExpanded = false;

            }

            SetBackgroundColor();

        }

 Similarly when the tree node is expanded, we again find the node which was expanded and set the IsVisuallyExpanaded property to true for both the node as well as all its child nodes as show below.

void XamDataGrid1_RecordExpanded(object sender, RecordExpandedEventArgs e) {

             var treeNodeThatWasExpanded = ((e.Record as DataRecord).DataItem as Node);

            treeNodeThatWasExpanded.IsVisuallyExpanded = true;

            foreach (var child in treeNodeThatWasExpanded.ChildNodes) {

                child.IsVisuallyExpanded = true;

            }

            SetBackgroundColor();

        } 

Then in each of these events we call a function which will first clear the background color for all the nodes and then loop through only the currently visible records and change the color accordingly for alternate rows as shown below in SetBackgroundColor() method.

 void SetBackgroundColor() {

            bool isColored = true;

            var backColor = new System.Windows.Media.SolidColorBrush(System.Windows.Media.Color.FromRgb(231, 241, 255));

            if (_model != null) {

                this._model.FlatListOfVisibleNodes.ForEach(x => x.BackgroundColor = System.Windows.Media.Brushes.Transparent);

                foreach (var rec in this._model.FlatListOfVisibleNodes) {

                    if (isColored) {

                        rec.BackgroundColor = backColor;

                    }

                    isColored = !isColored;

                }

            }

        }

Using Linq we can create the xml document very easily. This document explains as to how we can create the xml document using Linq.

Let’s create a simple xml document which has customer details and order details for each customer. For this we need to have the customer’s collection which contains the required data to create the xml. Create the required customer class and the order class as shown below.

    public class Customers    {

        public int Id { get; set; }

        public string Name {get; set;}

        public string Phone { get; set; }

        public string Email { get; set; }

        public List<Orders> Orders {get; set;}

    }

     public class Orders    {

        public string OrderName { get; set; }

        public int Quantity { get; set; }

        public string Date { get; set; }

           }

Then we create the Customers collection as shown below.

List<Customers> customers = new List<Customers>()            {

                New Customers(){Id=1,Name=”Customer1″,Phone=”9845884650″, Email=”Customer1@gmail.com”,

                Orders=new List<Orders>()                {

                    New Orders(){ Date=DateTime.Today.ToShortDateString(), OrderName=”OrderName1″, Quantity=2},

                    New Orders(){Date=DateTime.Today.AddDays(1).ToShortDateString(), OrderName=”OrderName2″, Quantity=3},

                    new Orders(){Date=DateTime.Today.AddDays(2).ToShortDateString(), OrderName=”OrderName3″, Quantity=4},

                }},

                new Customers(){Id=2,Name=”Customer2″,Phone=”9845884651″, Email=”Customer2@gmail.com”,

                Orders=new List<Orders>()                {

                    new Orders(){Date=DateTime.Today.AddDays(3).ToShortDateString(), OrderName=”OrderName1″, Quantity=5},

                    new Orders(){Date=DateTime.Today.AddDays(4).ToShortDateString(), OrderName=”OrderName2″, Quantity=6},

                    new Orders(){Date=DateTime.Today.AddDays(5).ToShortDateString(), OrderName=”OrderName3″, Quantity=7},

                }},

                new Customers(){Id=3,Name=”Customer3″,Phone=”9845884652″, Email=”Customer3@gmail.com”,

                Orders=new List<Orders>()               {

                    new Orders(){Date=DateTime.Today.AddDays(6).ToShortDateString(), OrderName=”OrderName1″, Quantity=8},

                    new Orders(){Date=DateTime.Today.AddDays(7).ToShortDateString(), OrderName=”OrderName2″, Quantity=9},

                    new Orders(){Date=DateTime.Today.AddDays(8).ToShortDateString(), OrderName=”OrderName3″, Quantity=10},

                }}

            };

Once we have the customers collection, then to create the xml document we create the root XElement first and then query the customers collection and foreach customer collection we add a new Child xelement as shown below.

// The root element

XElement xelement = new XElement(“Customers”,

from customer in customers

// Adding each customer info as the child element

select new XElement(“Customer”,

new XAttribute(“ID”, customer.Id),

new XAttribute(“Email” ,customer.Email),

new XElement(“Name”, customer.Name),

new XElement(“Phone”, customer.Phone),

// Each customer can have more then one orders, so querying the orders collection and adding the exisitng order details

from order in customer.Orders

select new XElement(“Order”,

new XAttribute(“OrderDate”, order.Date),    

new XElement(“OrderName”, order.OrderName),                                          

new XElement(“OrderQuantity”, order.Quantity))));

                             

 

The generate xml document wil be as shown below

 

<Customers>

  <Customer Email=”Customer1@gmail.com”>

    <Name>Customer1</Name>

    <Phone>9845884650</Phone>

    <Order OrderDate=”8/2/2010″>

      <OrderName>OrderName1</OrderName>

      <OrderQuantity>2</OrderQuantity>

    </Order>

    <Order OrderDate=”8/3/2010″>

      <OrderName>OrderName2</OrderName>

      <OrderQuantity>3</OrderQuantity>

    </Order>

    <Order OrderDate=”8/4/2010″>

      <OrderName>OrderName3</OrderName>

      <OrderQuantity>4</OrderQuantity>

    </Order>

  </Customer>

  <Customer Email=”Customer2@gmail.com”>

    <Name>Customer2</Name>

    <Phone>9845884651</Phone>

    <Order OrderDate=”8/5/2010″>

      <OrderName>OrderName1</OrderName>

      <OrderQuantity>5</OrderQuantity>

    </Order>

    <Order OrderDate=”8/6/2010″>

      <OrderName>OrderName2</OrderName>

      <OrderQuantity>6</OrderQuantity>

    </Order>

    <Order OrderDate=”8/7/2010″>

      <OrderName>OrderName3</OrderName>

      <OrderQuantity>7</OrderQuantity>

    </Order>

  </Customer>

  <Customer Email=”Customer3@gmail.com”>

    <Name>Customer3</Name>

    <Phone>9845884652</Phone>

    <Order OrderDate=”8/8/2010″>

      <OrderName>OrderName1</OrderName>

      <OrderQuantity>8</OrderQuantity>

    </Order>

    <Order OrderDate=”8/9/2010″>

      <OrderName>OrderName2</OrderName>

      <OrderQuantity>9</OrderQuantity>

    </Order>

    <Order OrderDate=”8/10/2010″>

      <OrderName>OrderName3</OrderName>

      <OrderQuantity>10</OrderQuantity>

    </Order>

  </Customer>

</Customers>

To implement a Custom EventArgs we need to follow these steps:

1. Create your custom event argument class and derive it from the EventArgs class. The eventArgs class contains no data and is used with events that do not need to pass event state.
2. Give your class a meaningful name ending in EventArgs, e.g.: MailReceivedEventArgs.
3. Implement additional data members and properties to support event state that you need to pass to event handlers. It’s best to make state immutable, so we should use private read-only data members and use public properties to provide read-only access to data members.
4. Implement a public constructor that allows you to set the initial configuration of the event state.
5. Make your argument class as sealed if you do not intend other event argument classes to extend it.
6. Make your event argument class serializable so that the runtime can marshal instances of it across application domain and machine boundaries.

 

Consider the below event argument class named MailReceivedEventArgs

Using system;

namespace DemoClass

{

[Serializable]

public sealed class MailReceivedEventArgs: EventArgs

{

  //private readonly members that hold the event state that is to be

 // distributed to all event handlers. The MailReceviedEventArgs class will specify

// who sent the received mail and what the subject is.

    private readonly string from;

    private readonly string subject;

 

   // Constructor

   public MailReceivedEventArgs(string from, string subject)

{

    this.form = form;

   this.subject = subject;

}

 

//Readonly properties to provide access to event state

public string From{ get {return from;}}

public string Subject { get {return subject;}}

 

//Class which uses the MailReceivedEventArgs

public class Test

{

  public static void Main()

{

 MailReceivedEventArgs args = new MailReceivedEventArgs(€œJyothi€, €œDocumet€);

 Console.WriteLine(€œFrom: {0}, Subject: {1}€, args.From, args.Subject);

Console.ReadLine();

}

}

}

 

}

The .NET framework provides simple thread pool implementation accessible through the members of the ThreadPool static class. We can access this class from System.Threading Namespace. 

Write a method which has to be executed using thread pool. Then we can use the System.Threading.ThreadPool.QueueUserWorkItem and pass the method that we want to execute. One constrain to call this method and to pass the method that we want to execute, is that the method’s signature must match that define by the system.threading.WaitCallbackDelegate; i.e. It must take a single object argument and return void.

QueueUserWorkItem method Queues a method for execution. The method executes when a thread pool thread becomes available. As a thread from the thread pool becomes available, it takes the next work item from the queue and executes it. It returns true if the method is successfully queued else System.OutOfMemoryException is thrown if the work item could not be queued. 

Consider the following example which passes the DisplayDate method twice to the thread pool once with argument and once without any argument. In the below example the thread.Sleep() method is added so that we can see the different thread execution. 

class ThreadPoolingClass    {

        private class DateInfo        {

            private int iterations;

            private string date;

             public DateInfo(int iterations, string date)            {

                this.iterations = iterations;

                this.date = date;

            }

           public int Iterations { get { return iterations; } }

            public string Date { get { return date; } }

             public static void DisplayDate(object state)            {

                DateInfo config = state as DateInfo;

              //If config is null it means that no arguments were passed so use default values.

                if (config == null)               {

                    for (int count = 0; count < 3; count++)                    {

                        Console.WriteLine(DateTime.Now.ToString(CultureInfo.CreateSpecificCulture(“en-GB”)));

                        System.Threading.Thread.Sleep(1000);

                    }

                }     else

                {

                    for (int count = 0; count < config.iterations; count++)                    {

                        Console.WriteLine(config.Date);

                        System.Threading.Thread.Sleep(1000);

                     }

                }

             }

            static void Main(string[] args)            {

                System.Threading.ThreadPool.QueueUserWorkItem(DisplayDate);

                DateInfo info = new DateInfo(4, “Date passed by argument:” + DateTime.Now.ToString(CultureInfo.CreateSpecificCulture(“nl-NL”)));

                 System.Threading.ThreadPool.QueueUserWorkItem(DisplayDate, info);

                 Console.WriteLine(“Main method complete.Press enter”);

                Console.ReadLine();

            }

        }

    }

 The output of the above program is as shown below

We can also see the different threads being called for execution in the Thread window. We can see the thread window by clicking Debug/windows/Thread. 

When the main program starts executing the main thread will be used as shown below.

 

 

 

 

 

                                                          When the DisplayDate method is called different worker thread is being used as shown below.

This document helps in resolving the CodeAnalysis Warning CA1002 which we usually get when we are trying to return a list from a query using Linq.

Consider the below mentioned query which results in CA1002 warning Do not expose generic lists when we run the code analysis.

public static List<GroupForDisplay> GetGroupsByContactId(int contactId)

        {

            using (Extranet ent = new Extranet()) {

                var groups = (from manager in ent.Manager

                              where manager.Contact.ContactId == contactId

                              orderby manager.Group.GroupId

                              select new GroupForDisplay

                              {

                                   GroupId = manager.Group.GroupId,

                                   GroupName = manager.Group.Name,

                                   LastLoggedInDate = manager.Group.LastLoggedInDate,

                                   ParentGroupId = manager.Group.ParentGroupId,

                                   ResponseShared = manager.Group.ResponseShared

                              }).ToList();

                return groups;

            }

        }

The warning message:

Warning 25 CA1002: Microsoft.Design : Change ‘List<GroupForDisplay>’ in ‘GroupDac.GetParentGroupsRecursively(GroupForDisplay, List<GroupForDisplay>, int)’ to use Collection<T>, ReadOnlyCollection<T> or KeyedCollection<K,V>  C:\Project\Action Planning Solution\Action.Planning.WebClient\Dal\GroupDac.cs        105     ActionPlanning.WebClient

CA1002 rule says that we should not expose a List<T> property because any one can Add, Remove or Insert without notifying the class which exposes the property.

And one other situation  is if we want to be notified when an item is added /Removed/Inserted in the list then we do not have any methods which would helps us in notifying this.

If we use the ReadOnlyCollection then it cannot be modified and also if we use collection then we can use easily notify when an item was Added/Removed.

To rectify the error when using LINQ we would first try to use IQualrable<T> or the IEnumarable<T> as shown below.

public static IQueryable<GroupForDisplay> GetGroupsByContactId(int contactId)

        {

            using (Extranet ent = new Extranet()) {

                var groups = (from manager in ent.Manager

                              where manager.Contact.ContactId == contactId

                              orderby manager.Group.GroupId

                              select new GroupForDisplay

                              {

                                  GroupId = manager.Group.GroupId,

                                  GroupName = manager.Group.Name,

                                  LastLoggedInDate = manager.Group.LastLoggedInDate,

                                  ParentGroupId = manager.Group.ParentGroupId,

                                  ResponseShared = manager.Group.ResponseShared

                              });

                return groups;

            }

        }

This would not show the warning message when we run the codeananlysis, however if we want to use the IQueryable<GroupForDisplay>/ IEnumerable<GroupForDisplay> that the above method is returning elsewhere in the application then we would get the objectdisposed exception as showbelow.

€œThe ObjectContext instance has been disposed and can no longer be used for operations that require a connection€.

In Linq we cannot iterate through query object oustside the scope of €˜Extranet ent’ context.We can iterate it ony within the context.

So to use retuned object we have to convert it to ToList () or ToArray () to actually create a List/Array of object that gets disconnected when the context is disposed, but they can be accessed without problem.

So to solve the CA1002 Warning we can use the array of GroupsForDisplay[] and return an array or we can also return  ICollection as shown below.

public static GroupForDisplay[] GetGroupsByContactId(int contactId)

        {

            using (Extranet ent = new Extranet()) {

                var groups = (from manager in ent.Manager

                              where manager.Contact.ContactId == contactId

                              orderby manager.Group.GroupId

                              select new GroupForDisplay

                              {

                                  GroupId = manager.Group.GroupId,

                                  GroupName = manager.Group.Name,

                                  LastLoggedInDate = manager.Group.LastLoggedInDate,

                                  ParentGroupId = manager.Group.ParentGroupId,

                                  ResponseShared = manager.Group.ResponseShared

                              }).ToArray();

                return groups;

            }

        }

 

Or

public static ICollection<GroupForDisplay> GetGroupsByContactId(int contactId)

        {

            using (Extranet ent = new Extranet()) {

                var groups = (from manager in ent.Manager

                              where manager.Contact.ContactId == contactId

                              orderby manager.Group.GroupId

                              select new GroupForDisplay

                              {

                                  GroupId = manager.Group.GroupId,

                                  GroupName = manager.Group.Name,

                                  LastLoggedInDate = manager.Group.LastLoggedInDate,

                                  ParentGroupId = manager.Group.ParentGroupId,

                                  ResponseShared = manager.Group.ResponseShared

                              }).ToList();

                return groups;

            }

        }

This document explains, as to how one can read data from an excel file using openxml.

Here we are considering that we have an excel file which contains the data from which we have to read, in this example we are storing the data read from an excel file into a datatable.

To make use of openxml we first need to add reference to the openxml dll’s and use the following namespaces.

using DocumentFormat.OpenXml.Packaging;

using DocumentFormat.OpenXml.Spreadsheet;

using DocumentFormat.OpenXml;

 To read the data, we first need to open the excel file. The excel file can contain many worksheets in it, so we also need the sheet name as to from which sheet the data has to be read. Depending on the sheet name we get the particular WorkSheetPart, which will actually contain the data. In case there is only one sheet in excel then we can use the workbookPart.WorksheetParts.FirstOrDefault(); to get the WorksheetPart directly without checkiong  for the work sheet name.

Then we need to check if the worksheetpart contains any data and if it contains data then we get the first and last row of the excel sheet using the below lines of code.

Row lastRow = worksheetPart.Worksheet.Descendants<Row>().LastOrDefault();

Row firstRow = worksheetPart.Worksheet.Descendants<Row>().FirstOrDefault();

 Then we check if the first row has values and if it has values we add it as columns to the datatable only if we know that the excel file contains the header column names in the first row, else will loop through all the rows in excel and add the particular values into the datatable.

While looping through each row we first get the row based on the row index using the following code

Row row = worksheetPart.Worksheet.Descendants<Row>() .Where(r => i == r.RowIndex).FirstOrDefault();

 And later check if the row is null or has values, if it has values then we loop through each cell in the row and get the values of the cell. To get the values of the Cell we use another method called GetValue which takes the cell and the SharedStringTablePart as the input arguments. We can get the SharedStringTablePart from the workbookpart.

 public static string GetValue(Cell cell, SharedStringTablePart stringTablePart)    {

        if (cell.ChildElements.Count == 0)            return null;

         //get cell value

        string value = cell.ElementAt(0).InnerText;//CellValue.InnerText;

         //Look up real value from shared string table

        if ((cell.DataType != null) && (cell.DataType == CellValues.SharedString))

            value = stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText;

         return value;

    }

 We also perform one more check i.e. if all the cell values are null, we come out of the loop.  Which means that that there is no data from that row onwards.

The method below ExtractExcelSheetValuesToDataTable is the consolidated method which perfroms the read operation from the excel sheet and returns the datatable.

public static DataTable ExtractExcelSheetValuesToDataTable(string xlsxFilePath, string sheetName)    {

        DataTable dt = new DataTable();

        using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Open(xlsxFilePath, true))        {

            //Access the main Workbook part, which contains data

            WorkbookPart workbookPart = myWorkbook.WorkbookPart;

            WorksheetPart worksheetPart = null;

            if (!string.IsNullOrEmpty(sheetName))            {

                Sheet ss = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName).SingleOrDefault<Sheet>();

                worksheetPart = (WorksheetPart)workbookPart.GetPartById(ss.Id);

            }            else            {

                worksheetPart = workbookPart.WorksheetParts.FirstOrDefault();

            } 

            SharedStringTablePart stringTablePart = workbookPart.SharedStringTablePart;

            if (worksheetPart != null)            {

                Row lastRow = worksheetPart.Worksheet.Descendants<Row>().LastOrDefault();

                Row firstRow = worksheetPart.Worksheet.Descendants<Row>().FirstOrDefault();

                if (firstRow != null)                {

                    foreach (Cell c in firstRow.ChildElements)                    {

                        string value = GetValue(c, stringTablePart);

                        dt.Columns.Add(value);

                    }

                }

                if (lastRow != null)                {

                    for (int i = 2; i <= lastRow.RowIndex; i++)                    {

                        DataRow dr = dt.NewRow();

                        bool empty = true;

                      Row row = worksheetPart.Worksheet.Descendants<Row>() .Where(r => i == r.RowIndex).FirstOrDefault();

                        int j = 0;

                         if (row != null)                        {

                            foreach (Cell c in row.ChildElements)                            {

                                //Get cell value

                                string value = GetValue(c, stringTablePart);

                                 if (!string.IsNullOrEmpty(value) && value != ” “)

                                 empty = false;

                                 dr[j] = value;

                                j++;

                                if (j == dt.Columns.Count)

                                 break;

                            }

                            if (empty)

                                break;

                            dt.Rows.Add(dr);

                        }

                     }

                }

            }

        }

        return dt;

    }

This document explains, as to how one can write data into an excel file using openxml.

Here we are considering that we already have an excel file and we are only writing data into the file and also assuming that the data which has to be written into excel is stored in a datatable.

To make use of openxml we first need to add reference to the openxml dll and use the following namespaces.

using DocumentFormat.OpenXml.Packaging;

using DocumentFormat.OpenXml.Spreadsheet;

using DocumentFormat.OpenXml;

 

To write the data, we first need to open the excel file. The below mentioned code is used to perform the same.

    public static void OpenExcelDocument(DataSet ds, string xlsxFilePath)

    {

        using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Open(xlsxFilePath, true))

        {

 

            //Access the main Workbook part, which contains all references

            WorkbookPart workbookPart = myWorkbook.WorkbookPart;

 

            foreach (DataTable dt  in ds.Tables)

            {

                InsertValuesInSheets(dt.TableName, workbookPart, dt);

            }

 

            workbookPart.Workbook.Save();

          

        }

    }

This method accepts two parameters, one is the dataset and the other is the complete path of the excel file. We are using a dataset here as an excel file can contain n number  of worksheets inside it, so the data that has to be added in each sheet can be stored in a datatable, which is intern added to the dataset. In the above method we are calling the InsertvaluesInsheets method for each table in the dataset.

Now let’s see what this InsertValuesInSheet method does. It accepts three parameters, the sheetname in excel, the workbookpart and the datatable which contains the data to be written. We first need to get the worksheetpart where the data has to be inserted, we use the sheetname as show below to get that particular worksheet part.

Sheet ss = workbookPart.Workbook.Descendants<Sheet>().Where(s=>s.Name== sheetName).SingleOrDefault<Sheet>();

 WorksheetPart           worksheetPart = (WorksheetPart)workbookPart.GetPartById(ss.Id);

 

If incase we have single sheet in the excel file then we do not need the sheetname and instead we can use the below line to get the first worksheet part from excel.

WorksheetPart worksheetPart = workbookPart.WorksheetParts.FirstOrDefault();

Next we get the sheet data and get the rows in the sheet by using the following line of code.

var excelRows = sheetData.Descendants<Row>().ToList();

 

Then we loop through each row in the datatable and check if the row exists in the excel sheet and if it exists, then we get the cells in that row and if cell exists, we update the cell value with the corresponding values from the datatable else we create a new cell, update the cell value and then add the cell to the excel row. Incase the row does not exists then we create a new row and add the corresponding values from the datatable and then added this to the excel sheet. 

Once all the data is written into the excel sheet then we save the changes that we made by calling the following line of code.

worksheetPart.Worksheet.Save();

public static void InsertValuesInSheets(string sheetName,WorkbookPart workbookPart,DataTable dt)

    {

        WorksheetPart worksheetPart = null;

        if (!string.IsNullOrEmpty(sheetName))

        {

            Sheet ss = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName).SingleOrDefault<Sheet>();

            worksheetPart = (WorksheetPart)workbookPart.GetPartById(ss.Id);

        }

        else

        {

            worksheetPart = workbookPart.WorksheetParts.FirstOrDefault();

        }

     

        if (worksheetPart != null)

        {

            SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();

            var excelRows = sheetData.Descendants<Row>().ToList();

    int excelRowIndex = 1;

                for (int rowIndexInTable = 0; rowIndexInTable < dt.Rows.Count; rowIndexInTable++)

                {

                    if (excelRowIndex >= excelRows.Count)

                    {

                        Row contentRow = CreateContentRow(excelRowIndex+1, dt.Rows[rowIndexInTable], dt.Columns.Count);

                        sheetData.AppendChild(contentRow);

                    }

                    else

                    {

                        var cells = excelRows[excelRowIndex].Descendants<Cell>().ToList();

                        for (int colIndexInTable = 0; colIndexInTable < dt.Columns.Count; colIndexInTable++)

                        {

                            Cell cell;

                            if (colIndexInTable >= cells.Count)

                            {

                               cell = new Cell();

                                    excelRows[excelRowIndex].AppendChild(AddValueToCell(cell, dt.Rows[rowIndexInTable][colIndexInTable].ToString()));

                            }

                            else

                            {

                                if (cells.Count == 1)

                                {

                                    cell = new Cell();

                                    excelRows[excelRowIndex].InsertAt(AddValueToCell(cell, dt.Rows[rowIndexInTable][colIndexInTable].ToString()), 0);

                                }

                                else

                                {

                                    cell = cells[colIndexInTable];

                                    AddValueToCell(cell, dt.Rows[rowIndexInTable][colIndexInTable].ToString());

                                }

                            }

 

                        }

                    }

                    excelRowIndex++;

                }

            worksheetPart.Worksheet.Save();

        }

    }

To create a new row and to add the same to the excel sheet we make use of another method called, CreateContentRow as shown below.

In this method we create a new row assign the index of the row and loop through the no.of columns in datatable and create new cell and added it to the row. To create a new cell we use the CreateTextCell method.

public static Row CreateContentRow(int index, DataRow dr, int headerColumnsCount)

    {

 

        //Create new row

 

        Row r = new Row();

 

        r.RowIndex = (UInt32)index;

 

        for (int i = 0; i < headerColumnsCount; i++)

        {

 

            Cell c = CreateTextCell(headerColumns[i], dr[i].ToString(), index);

            r.AppendChild(c);

 

        }

        return r;

    }

 

public static Cell CreateTextCell(string header, string text, int index)

    {

 

        //Create new inline string cell

 

        Cell c = new Cell();

 

        c.DataType = CellValues.InlineString;

 

        //Add text to text cell

 

        InlineString inlineString = new InlineString();

 

        Text t = new Text();

 

        t.Text = text;

 

        inlineString.AppendChild(t);

 

        c.AppendChild(inlineString);

 

        return c;

 

    }

}

Incase the cell is already existing in the excelsheet and we just have to update the data then we use the following method.

public static Cell AddValueToCell(Cell cell, string text)

    {

        cell.DataType = CellValues.InlineString;

        cell.RemoveAllChildren();

        InlineString inlineString = new InlineString();

        Text t = new Text();

        t.Text = text;

        inlineString.AppendChild(t);

        cell.AppendChild(inlineString);

        return cell;

    }

By using all the above methods we can write data into excel file using openxml.

The C# 4.0 is the next release of C#. The major theme in C# 4.0 is dynamic programming. Dynamic here means, that objects structure and behavior is not captured by a static type, or at least not one that the compiler knows about when compiling your program.

The secondary theme is co-evaluation with Visual basic. Further any new features will be introduced in both the languages at the same time.

Features of C# 4.0

1. Dynamically Typed Objects
2. Optional and Named Parameters
3. Improved COM Interoperability
4. Co and Contra Variance

Optional and Named Parameters

Many a times we come across situations where we need to make call to methods that accepts many arguments, and most of the times not all the arguments that the method accepts will be passed, we usually pass null. Sometimes we create Overloads for better use, but we still have to pass the default values for those Overloads.

For e.g. Consider the following method which creates the table cell.

public TableCellInfo CreateTableCellInfo(string text, System.Drawing.Color textColor, int textSize, double width, double height, Nullable<System.Drawing.Color> backColor, BorderInfo leftBorder, BorderInfo rightBorder, BorderInfo topBorder, BorderInfo bottomBorder, SymbolInfo symbol, bool isTextCenterAligned, TextVerticalValues textVerticalValue)

Not all the arguments are required each time we call the method, the string text, width and height of the cell are mostly used.So we create secondary overload of this method and supply default values in that overload. Finally call the overloaded method.

In C# 4.0 what we can do is we can specify the parameter as optional, to declare a parameter as optional all we have to do is set a default value to that parameter.

So the above method would be modified as shown below

public TableCellInfo CreateTableCellInfo(string text, System.Drawing.Color textColor = System.Drawing.Color.Black, int textSize =8, double width, double height, Nullable<System.Drawing.Color> backColor = System.Drawing.Color.White , BorderInfo leftBorder = null, BorderInfo rightBorder=null, BorderInfo topBorder =null, BorderInfo bottomBorder=null, SymbolInfo symbol=null, bool isTextCenterAligned =true, TextVerticalValues textVerticalValue = null)

And where we need to call the method we can call it by specifying the required parameters only i.e text, width and height.

public TableCellInfo CreateTableCellInfo( €œDefaulttext€, 5, 4);

Incase we want to specify a different value other than the default value for the optional parameters then we can make use of the named parameters and pass the required arguments by names as shown blelow.

public TableCellInfo CreateTableCellInfo( €œDefaulttext€, 5, 4, Nullable<System.Drawing.Color> backColor = System.Drawing.Color.White );

Optional parameters allow you to omit arguments to member invocations, whereas named arguments is a way to provide an argument using the name of the corresponding parameter instead of relying on its position in the parameter list.

Simple example:

Consider this method

public void M(int x, int y = 5, int z = 7);

Y and Z are optional parameters

M(1, 2, 3);//ordinary call of M
M(1, 2); // omitting z €€œ equivalent to M(1, 2, 7)
M(1); // omitting both y and z €€œ equivalent to M(1, 5, 7)

In case we want to omit Y then we can use named parameter and pass Z by name as shown below.

M(1, z: 3); // passing z by name

Overload resolution

If we have overloads of the method, while calling the method if more than one signatures of the method are equally good for calling, the method that doesn’t have optional parameter is preferred.

Consider following example to understand this better:

M(string s, int i = 1);
M(object o);
M(int i, string s = €œHello€);
M(int i);

If we want to make a function call like

M(5);

Evaluating the above methods, out of given signature, 2nd (public static void M1(object o) ), 3rd (public static void M1(int i, string s = “Hello”) and 4th method (public static void M1 (int i)) are suitable for call.

Calling a method M1(object o) involves boxing operation and assigning 5 into int is better than converting 5 into object, so that is not a suitable method.

Out of the remaining 2 the method without optional parameter is preferred so the method that is being called is M1(int i) and output is €œint i€.

References:

http://code.msdn.microsoft.com/csharpfuture/Release/ProjectReleases.aspx?ReleaseId=1686

This document intends to provide the steps that can be followed to host a windows application from a web application.
First step would be to deploy the windows application using ClickOnce deployment.
Follow these steps to perform the ClickOnce deployment
1. In Solution Explorer, select the application project.
2. Right-click the project node and choose Publish.
The Publish Wizard appears.
3. On the Where do you want to publish the application? page, enter a valid URL using the format http://www.microsoft.com/foldername, then click Next.

4. In the Will the application be available offline? page, click the appropriate option:
€¢ If you want to enable the application to be run when the user is disconnected from the network, click Yes, this application will be available online or offline. A shortcut on the Start menu will be created for the application.
€¢ If you want to run the application directly from the publish location, click No, this application is only available online. A shortcut on the Start menu will not be created.
Click Next to continue.
5. Click Finish to publish the application.
Publishing status is displayed in the status notification area of the taskbar.
Once the windows application is published to a particular url then the next step would be to call it from a web application.
We can either use Process.start()method or we can use Respone.Redirect().
Below I have used the Response.Redirect(€œURL€) method and I am calling it in the button click event, so when the button is clicked the windows application will be opened.
private void btn_Click(object sender, EventArgs e) {
Response.Redirect(ConfigurationManager.AppSettings["WindowsApplicationURL"].ToString())
}
In the web.config file we can specify the exact path where we have published the windows application as shown below.
We can also pass querystring variable from the web application and use the same in the windows appliation to get only related data.
To pass the querystring we need to modify the url as shown below
http://localhost/GeneratePpt\ + GeneratePpt.application
i.e it will be the complete path where the windows application is published + the folder path + €œ.€ + application.
We also will have to modify the button click event to pass the query string as below:
private void btn_Click(object sender, EventArgs e) {
Response.Redirect(http://localhost/GeneratePpt\GeneratePpt.application?QueryStingId + querystringvalue);
}

VSDBE is a great product with very good features. However when I was creating a database project for one of the existing databases, I got more than 400 errors and it took me a while to figure out how to solve these errors though the solution was very easy.

I thought of writing a document on it so that it can be helpful to anyone who will face similar kind of issues.

Once you are done with importing the schema from the database and if you try to build your database project you might probably see the following list of errors.

Error TSD03006: View: has an unresolved reference to object

Error TSD03006: View: contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects€¦

Error TSD03006: User: has an unresolved reference to object.

Error TSD03006: Column: has an unresolved reference to object.

Error TSD03006: Column: contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects€¦

Most of the errors which you get will be similar, either it will be unresolved reference to View, User, Column, stored procedure or function.

Solving TSD03006 for Views:

As the error clearly says that there is an unresolved reference to the object , the first thing what we would do is to create another project and import the schema for the database which has been referenced in our main project and then add this reference to the main project.

The database project that you just created might again have references to other tables from another database, so you will have the similar build errors in that project as well. First thing we need to do will be to build the second project without any errors, so once you import the project make sure you keep only the things that are referenced in the main project and delete all the other script files like most of the sp’s, views and tables and incase any user login is giving any unresolved reference error then delete those logins as well. Then we have to add this reference to the main project, while adding reference we need to keep in mind that the DBE is case sensitive and make sure we give the exact database name the way it is being used in the view.

For e.g.

CREATE VIEW [dbo].[GetCustomerOrders]

AS

select dbo.CustomerDetails.FirstName as Name, [TestB].dbo.OrderDetails.OrderName as Orders

from dbo.CustomerDetails

join [TestB].[dbo].[OrderDetails] on dbo.CustomerDetails.ID = [TestB].[dbo].OrderDetails.CustomerID;

In this view I am having a reference to a table Orderdetails which is in TestB database. When we are referencing TestB database we need to select the TestB database and then we need to check the Define database variable option and also check the literal checkbox and specify the exact name that has been used in all the views.

This would solve all the errors related to views.

There might be some instance where your database is referencing a table from another server. While adding reference to such a project make sure we define both the server variable and the database variable as shown below.

The view now would look like this

CREATE VIEW [dbo].[GetCustomerOrders]

AS

select dbo.CustomerDetails.FirstName as Name, [TestB].dbo.OrderDetails.OrderName as Orders

from dbo.CustomerDetails

join [TestB].[dbo].[OrderDetails] on dbo.CustomerDetails.ID = [$(ServerName)].[$(DatabaseName)].dbo.OrderDetails.CustomerID;

For detailed information please refer this link: http://download.microsoft.com/download/0/A/E/0AE1153A-8798-474A-93E6-D19299F37C8B/Readme.mht#_Toc213500658

Solving TSD03006 for User:

To solve these errors we need to create a server project and import the database objects.

The server project has both the server level objects and database level objects, we need only the server level objects information as that will contain all the information about the logins, rest of the objects can be deleted. Make sure only server level objects and severrolemembership script exists.

Build this project and add reference of this project to the main project as mentioned above. This will solve user login related errors.

Suppressing the warnings related to sp’s and functions:

If we have cross database reference in a stored procedure or in function then we get warnings saying €œTSD04151: Unresolved reference to €¦.€, we can suppress these warnings for the entire project by right clicking on the project properties and select the build option under which we have an option to suppress warnings, we have to mention the warnings that we want to suppress as shown below.

We can also suppress the warnings for a particular script file by right clicking on the script file and selecting properties under which we have an option to suppress warnings.

Solving error Delpoy01234:

When all the errors and warnings are resolved and when we want to deploy the project to the target database we might get the following error €œThe deployment script was generated, but was not deployed. You can change the deploy action on the Deploy tab of the project properties.
—— Deploy started: Project: TestA, Configuration: Debug Any CPU ——
C:\Program Files\MSBuild\Microsoft\VisualStudio\v9.0\TeamData\Microsoft.Data.Schema.SqlTasks.targets(120,5)Error Deploy01234: The source database schema provider Sql100DatabaseSchemaProvider could not be translated to provider Sql90DatabaseSchemaProvider. Deployment cannot continue.€

This error occurs, if the current project version is greater than the target database version where we want to deploy it.

To solve this method we can change the project version by right clicking on project properties and under project settings tab we have an option to select project version as shown below, change it to a version lower or the same version when compared to the target database where you want to deploy the changes.

These are the most likely errors that can occur while we create a database project.