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......