Saturday, October 6, 2012

Conversion of one object collection into another using Linq

In Today's example we will convert an object collection into another object collection using Linq.

As we have seen earlier, using Linq we can eliminate lot of coding effort and can save time also.

For current example i have created an employee collection which contains all the employee details. But some of the employees are authors also.

Now we have to populate an Author collection by filtering the authors from the employee collection.

//Employee class. For the employees who are author also, the //IsAuthor property contains true value
public class Employee
{       
  public string firstName;
  public string lastName;
  public bool IsAuthor;
  public string Country;

  public static ArrayList GetEmployees()
  {
    ArrayList oArrayList = new ArrayList();

    //adding employee details to the array list   
    oArrayList.Add(new Employee { firstName = "Rahul", lastName = "Gupta",Country="India", IsAuthor = true });
    oArrayList.Add(new Employee { firstName = "William", lastName = "Johns", Country = "United States", IsAuthor = true});
    oArrayList.Add(new Employee { firstName = "Stephen", lastName = "Smith", Country = "Australia", IsAuthor = true });
   
   return (oArrayList);
  }
}

//Author class 
public class Author
{
  public string AuthorName;
  public string Country;
  
  //This display method writes the output to the console window
  public static void DisplayAuthorDetails(Author[] objAuthors)
  {
   //looping through the collection
   foreach (Author c in objAuthors)
     Console.WriteLine("Author Name:  {0}  Country:  {1}", c.AuthorName, c.Country);

   Console.ReadLine();
  }
}

//Program class - which contains the Main method of execution
class Program
{
  static void Main(string[] args)
  {

   ArrayList alEmployees = Employee.GetEmployees();

   

   //Cast-Converts elements of an IEnumerable to specified type

   //Using Cast to convert Employee object to Author object
   //Where-filters a sequence of values based on predicate
   //Where is used to find all the authors from the employees collection
   Author[] objAuthors = alEmployees.Cast<Employee>()                               
                         .Where(e=> e.IsAuthor==false)
                         .Select(e => new Author { AuthorName = string.Format("{0} {1}",
                                 e.firstName, e.lastName), Country = e.Country})
                         .ToArray<Author>();
                               
   Author.DisplayAuthorDetails(objAuthors);
  }
}

Output:-



Happy Coding......





Saturday, September 15, 2012

Reading CSV file data into a DataTable using C#.Net


Today we will try reading a CSV file data into a DataTable using C#.Net.

The CSV file contains the data in the following format:-
1) The first row contains the column names
2) The data delimiter(separator) is Comma (",") for the columns.

The sample CSV file (CustomerDetails.csv) contains the following data:-
The "CustomerDetails.csv" is placed in the Bin directory of the project.




//initialising a StreamReader type variable and will pass the file location
StreamReader oStreamReader = new StreamReader("CustomerDetails.csv");

DataTable oDataTable = null;
int RowCount = 0;
string[] ColumnNames = null;
string[] oStreamDataValues=null;
//using while loop read the stream data till end
while (!oStreamReader.EndOfStream)
{
  String oStreamRowData = oStreamReader.ReadLine().Trim();
  if (oStreamRowData.Length > 0)
  {
    oStreamDataValues = oStreamRowData.Split(',');
    //Bcoz the first row contains column names, we will poluate 
    //the column name by
    //reading the first row and RowCount-0 will be true only once
    if (RowCount == 0)
    {
      RowCount = 1;
      ColumnNames = oStreamRowData.Split(',');
      oDataTable = new DataTable();

      //using foreach looping through all the column names
      foreach (string csvcolumn in ColumnNames)
      {
       DataColumn oDataColumn = new DataColumn(csvcolumn.ToUpper(), typeof(string));

      //setting the default value of empty.string to newly created column
      oDataColumn.DefaultValue = string.Empty;

      //adding the newly created column to the table
      oDataTable.Columns.Add(oDataColumn);
     }
    }
    else
    {
      //creates a new DataRow with the same schema as of the oDataTable            
      DataRow oDataRow = oDataTable.NewRow();

      //using foreach looping through all the column names
      for (int i = 0; i < ColumnNames.Length; i++)
      {
        oDataRow[ColumnNames[i]] = oStreamDataValues[i] == null ? string.Empty : oStreamDataValues[i].ToString();
      }
     
      //adding the newly created row with data to the oDataTable       
      oDataTable.Rows.Add(oDataRow);
    }
  }
}
//close the oStreamReader object
oStreamReader.Close();
//release all the resources used by the oStreamReader object
oStreamReader.Dispose();

//Looping through all the rows in the Datatable
foreach (DataRow oDataRow in oDataTable.Rows)

{

  string RowValues=string.Empty;

  //Looping through all the columns in a row

  foreach (string csvcolumn in ColumnNames)

  {
    //concatenating the values for display purpose
    RowValues += csvcolumn + "=" + oDataRow[csvcolumn].ToString()+";  ";
  }
  //Displaying the result on the console window
  Console.WriteLine(RowValues);
}

Output:-



Happy Coding.......

Friday, September 7, 2012

Generating Create table and Insert script with data using SQL Server 2008 Management Studio

In today's example we will see a way to generate create table and insert script using SQL Server 2008 Management studio.

This feature is available from SQL Server 2008 version and is really helpful, if you have data already available in one or multiple tables and you want to generate insert script for that data.

Following are the steps which needs to be followed for generating the insert script:-

Step 1:- Right click on the Database which has the table using which you want to generate the insert script. 

        <Right Click on Database> -> Task -> Generate Scripts


Step 2:- Following window will be shown

Step 3:- Click on "Choose Object" option. 

By default "Script entire database and all database objects" will be selected.

As i have to generate insert scripts for only for one table. I will click on 2nd option.

Step 4:- Click on "Select specific database objects". 

Here you can see the list of objects which are available for your database.

Step 5:Then select the table option and all the tables for the 
         selected Database will be shown.

I have to generate the create and insert script for "Roles" table so i have selected the same from the tables list
Click on next button.

Step 6:
- In this screen different options are available.

1)Select a location where you want to save the file.
2)Publish to a web service
3)Whether single file per object or Single file for all the 
  objects
4)Saving to Clipboard
5)Saving to new query window
Click on the "Advanced" button.

Step 7:- This is the most important step. 
There are three options available:
1) Data only - If you want to generate the insert script for data 
               only.
2)Schema and Data - If you want to generate the create table 
                    script with the insert script of the 
                    available data.
3)Schema only - If you want to generate the create table script.

Step 8:- I have selected "Schema and data" option
Click Ok button.

And then click on Next button for rest of the options.

Finally following screen will be shown with successful result.

And the following script is generated for my table and data available in that table:


USE [ReportServer$SQLSERVER2008]
GO
/****** Object:  Table [dbo].[Roles]    Script Date: 09/07/2012 22:33:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Roles](
[RoleID] [uniqueidentifier] NOT NULL,
[RoleName] [nvarchar](260) NOT NULL,
[Description] [nvarchar](512) NULL,
[TaskMask] [nvarchar](32) NOT NULL,
[RoleFlags] [tinyint] NOT NULL,
 CONSTRAINT [PK_Roles] PRIMARY KEY NONCLUSTERED 
(
[RoleID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[Roles] ([RoleID], [RoleName], [Description], [TaskMask], [RoleFlags]) VALUES (N'af71b93f-9845-4c7e-82fd-9c3590e49f88', N'Browser', N'May view folders, reports and subscribe to reports.', N'0010101001000100', 0)
INSERT [dbo].[Roles] ([RoleID], [RoleName], [Description], [TaskMask], [RoleFlags]) VALUES (N'f45d0872-5c54-46db-a54a-8148a5eeb270', N'Content Manager', N'May manage content in the Report Server.  This includes folders, reports and resources.', N'1111111111111111', 0)
INSERT [dbo].[Roles] ([RoleID], [RoleName], [Description], [TaskMask], [RoleFlags]) VALUES (N'c2073b90-d63f-4128-9739-82ffb11393b4', N'Model Item Browser', N'Allows users to view model items in a particular model.', N'1', 2)
INSERT [dbo].[Roles] ([RoleID], [RoleName], [Description], [TaskMask], [RoleFlags]) VALUES (N'c70a4cd7-d31c-45f7-8d37-7e0b54b49031', N'My Reports', N'May publish reports and linked reports; manage folders, reports and resources in a users My Reports folder.', N'0111111111011000', 0)


Amazing work right. Truly speaking it can save a lot of time for developers as normally we generate script manually most of the time which takes lot of time.


Happy Coding......

Thursday, August 30, 2012

Generic code to read XML document without knowing Root Node and Parent Node using LINQ

Recently i came across a situation where i was supposed to write code for reading a XML file where the structure of the document was fixed but following information was changing.

1) RootNode text,
2) ParentNode text
3) Number of Child nodes inside the parent node

So in Today's example we will see a way to write customized or generic code to read XML document where the Root Node, Parent Node and number of Child nodes can vary using LINQ.

Customers.xml
<?xml version="1.0" encoding="utf-8" ?>
<Customers>
  <Customer>
    <name>Aryan Mehta</name>
    <city>Bangalore</city>
    <country>India</country>
  </Customer>
  <Customer>
    <name>Steve Martin</name>
    <city>Sydney</city>
    <country>Australia</country>
  </Customer>
</Customers>


For this example i am using "Customer.xml" as the sample xml file.

//Loading the customers xml into the XDocument object
XDocument oXDocument = XDocument.Load(AppDomain.CurrentDomain.BaseDirectory + "Customers.xml");

//Retrieving the Root Node of the xml document dynamically
string RootNodeName = oXDocument.Root.Name.ToString();

//retrieving all the distinct elements inside a Root Node           
var ElementNodes = oXDocument.Root.DescendantNodes().OfType<XElement>().Select(x => x.Name).Distinct();

//ElementNodes.First will give the first node inside Root Node
//which is nothing but the Parent Node
var CustomerInfo = from CustomerInfoNode in oXDocument.Element(RootNodeName).Elements(ElementNodes.First())
                   select CustomerInfoNode;

//Looping through all the Customers elements in the CustomerInfo Element
foreach (XElement oXElement in CustomerInfo)
{
  //For separating the customer information
  Console.WriteLine("----Customer Information----");

 //Looping through Customer details of a particular Customer
 foreach (XElement oXElementData in oXElement.Elements())
 {
   //displaying the customer details in the console window
   Console.WriteLine(oXElementData.Name.ToString() + "="
                     oXElementData.Value.ToString());
 }
 Console.WriteLine("");
}
Console.ReadLine();

Output


When i use the same code for following "Employees.xml" which is with different Root Node and Parent Node.

Employees.xml
<?xml version="1.0" encoding="utf-8" ?>
<Employees>
  <Employee>
    <name>Aryan Mehta</name>
    <city>Bangalore</city>
  </ Employee >
  < Employee >
    <name>Steve Martin</name>
    <city>Sydney</city>
  </ Employee >
</ Employees >

Output

So the code of today's example can be used with different XML document having dynamic Root Node and Parent Node. But the only restriction is that the structure of the XML node should be same.

I hope this example has served the purpose of giving an idea on how to read a XML document in a generic way using LINQ.


Happy coding....


Sunday, August 26, 2012

Win 7 - Windows Service Installation Error - System.InvalidOperationException


I was getting the following error when i tried to install a simple WCF service as part of Windows Service in my machine which has WIN 7 as the operating system.

An exception occurred during the Install phase.
System.InvalidOperationException: Cannot open Service Control Manager on computer '.'. This operation might require other privileges.

The inner exception System.ComponentModel.Win32Exception was thrown with the following error message: Access is denied.

After lot of searching i found out there was one basic permission issue, When installing the Windows Service in a WIN 7 machine.

Solution:-

When using the InstallUtil command to install the service, we need to run the "Visual Studio Command Prompt(2010)" as an Administrator.


Then the Visual Studio Command Prompt window will open and you can write the InstallUtil command to install the Windows Service in your machine:-


This works fine for me. I didn't change any setting using the Registry.

Happy Coding..........

Saturday, August 11, 2012

Customizing the XML root node using Linq

Sometimes we come across a situation where the XML data, our application received from different sources needs to be having the Root node as expected by our application.

Obviously the restriction can be applied on other application for defining the XML as expected by the calling application. But is the datasource are dynamic then it can create some issues.

In today's example we will see a simple way to customize XML root node using the LINQ as per our requirement. 

For this example to work System.Xml.Linq namespace should be referenced.

Customer.xml

<?xml version="1.0" encoding="utf-8" ?>
<Customers>
  <Customer>
    <name>Aryan Mehta</name>
    <city>Bangalore</city>
    <country>India</country>
  </Customer>
  <Customer>
    <name>Steve Martin</name>
    <city>Sydney</city>
    <country>Australia</country>
  </Customer>
  <Customer>
    <name>Sandra Smith</name>
    <city>New York</city>
    <country>United States</country>
  </Customer>
</Customers>

//Loading the customers xml into the XElement object
XElement oXElement = XElement.Load(AppDomain.CurrentDomain.BaseDirectory + "Customers.xml");

//select all the elements using Linq 
var AllElements = from p in oXElement.Elements() select p;


//Defining oXElementReturn to hold the customized xml
XElement oXElementReturn = null;

//Adding customized root node to the return XElement
oXElementReturn = new XElement("CustomizedRootNode");

//Looping through all the elements in the xElement
foreach (XElement xElement1 in AllElements)
{
   //Creating a customized parent node inside root node
   XElement oXElementNode = new XElement("CustomizedParentNode");

   //Looping through all the elements inside parent node
   foreach (XElement xElement2 in xElement1.Descendants())
   {                   
     //Adding the node name and values to the parent node
     XElement oNewXElement = new 
               XElement(xElement2.Name.ToString());
     oNewXElement.Value = xElement2.Value;
     oXElementNode.Add(oNewXElement);
   }
   //Finally the parent node is added to the root node
   oXElementReturn.Add(oXElementNode);
}
//Once the customized return xElement is ready, the output can be displayed on console window
Console.WriteLine(oXElementReturn.ToString());

Output:- 


As it can be seen the xml document is customized as per my needs. I am not saying this is the perfect way but this is one of the way you can use to customize the Xml Root node and Parent Node.

And this can be extended to modify the Xml elements also as per your requirement.

I have used Console.Write for writing the output to demonstrate the output retrieved. You can use any way the data is required to be displayed or stored in a collection.


Happy Coding.........