Code Simplified – Viral Sarvaiya

Code Simplified – Viral Sarvaiya, Web Developer Friends, dot net Developer, Sql Server Developer

Posts Tagged ‘dataset’

Convert data from Generic List to DataTable.

Posted by Viral Sarvaiya on May 9, 2013

Today i am sharing very good a function which convert all Generic list’s data to Datatable is as below

public DataTable LINQToDataTable<T>(IEnumerable<T> varlist)
  DataTable dtReturn = new DataTable();

  // column names
  PropertyInfo[] oProps = null;

  if (varlist == null) return dtReturn;

  foreach (T rec in varlist)
    // Use reflection to get property names, to create table, Only first time, others will follow
    if (oProps == null)
      oProps = ((Type)rec.GetType()).GetProperties();
      foreach (PropertyInfo pi in oProps)
        Type colType = pi.PropertyType;

        if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition() == typeof(Nullable<>)))
          colType = colType.GetGenericArguments()[0];

        dtReturn.Columns.Add(new DataColumn(pi.Name, colType));
    DataRow dr = dtReturn.NewRow();

    foreach (PropertyInfo pi in oProps)
      dr[pi.Name] = pi.GetValue(rec, null) == null ? DBNull.Value : pi.GetValue(rec, null);

  return dtReturn;

Hope this will helps you.


Posted in .Net, C#, General, LINQ | Tagged: , , , , , , , , , , | Leave a Comment »

DataTable to CSV File and CSV file to Dataset in

Posted by Viral Sarvaiya on April 21, 2011


here i give you 2 simple function for the export Datatable/Dataset to CSV file format and import CSV file to dataset/DataTable.

1. DataTable/DataSet to CSV file

private void DataTableToCSVFile(string filename)
HttpContext context = HttpContext.Current;
foreach (DataColumn column in dtExcelUpdown.Columns)
context.Response.Write(column.ColumnName + ",");
foreach (DataRow row in dtExcelUpdown.Rows)
for (int i = 0; i < dtExcelUpdown.Columns.Count; i++)
context.Response.Write(row[i].ToString().Replace(",", string.Empty) + ",");
context.Response.ContentType = "text/csv";
context.Response.AppendHeader("Content-Disposition", "attachment; filename=" + filename + ".csv");

2. CSV file to DataSet/DataTable

from the upload control you have to save to directory and then this path and file name give as a parameter of the function.

private DataSet GetCVSFile(string pathName, string fileName)
OleDbConnection ExcelConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties=Text;");
OleDbCommand ExcelCommand = new OleDbCommand(@"SELECT * FROM " + fileName, ExcelConnection);

OleDbDataAdapter ExcelAdapter = new OleDbDataAdapter(ExcelCommand);


DataSet ExcelDataSet = new DataSet();

return ExcelDataSet;


enjoy coding…..

Posted in .Net, ASP.NET, feature | Tagged: , , , , , , , , | Leave a Comment »

Removing Duplicate Records from Dataset/DataTable

Posted by Viral Sarvaiya on September 27, 2010

Hello friends….

Hear i demonstrate, how to remove the rows from the dataset that have duplicate rows.

Duplicate DataTable looks like,

Name                                         Company

Viral                                          BNF Tech

Sandeep                                   Gateway Tech

Dharmik                                   Om info

Malhar                                       BNF Tech

Viral                                           BNF Tech

Dharmik                                   Om info

Viral                                           BNF Tech

Sandeep                                   Gateway Tech

now this is the function that remove the duplicate rows from the DataTable as below,

public DataTable DuplicateRowRemove(DataTable dt, string Col)
 Hashtable hTable = new Hashtable();
 ArrayList ArrDupli = new ArrayList();

 foreach (DataRow r in dt.Rows)
 if (HeshTbl.Contains(r[Col]))
 HeshTbl.Add(r[Col], string.Empty);


 foreach (DataRow R in ArrDupli)

 return dt;

so, after the removing the duplicate data DataTable shows as blow,

Name                                         Company

Viral                                          BNF Tech

Sandeep                                   Gateway Tech

Dharmik                                   Om info

Malhar                                       BNF Tech

thanks you…..

Malhar                                       BNF Tech

Posted in ASP.NET | Tagged: , , , , | 9 Comments »

difference between dataset and datareader

Posted by Viral Sarvaiya on October 6, 2009

Here Points to ponder contain the difference between Dataset and DataReader.

Points to ponder:

  • DataSet Object works under Disconnected mode, While DataReader Object has Connected mode.
  • DataSet Object has Read/Write access, While DataReader Object has Read-only access.
  • DataSet Object Supports multiple tables from different databases, While DataReader Object Supports a single table based on a single SQL query of one database.
  • DataSet Object has Greater overhead to enable additional features, While DataReader Object being Lightweight object with very little overhead.
  • DataSet Object is Bind to multiple controls, While DataReader Object is Bind to a single control.
  • DataSet Object supports Forward and backward scanning of data, While DataReader Object supports Forward-only scanning of data.
  • DataSet Object has Slower access to data, While DataReader Object has Faster access to data.
  • DataSet Object is Supported by Visual Studio .NET tools, While DataReader Object Must be manually coded.

Connected Mode:

A DataReader is a stream of data that is returned from a database query. When the query is executed, the first row is returned to the DataReader via the stream. The stream then remains connected to the database, poised to retrieve the next record. The DataReader reads one row at a time from the database and can only move forward, one record at a time.

As the DataReader reads the rows from the database, the values of the columns in each row can be read and evaluated, but they cannot be edited.

Unlike the DataSet, the DataReader is not implemented directly in the System.Data namespace.

Rather, the DataReader is implemented through a specific managed provider’s namespace such as System.Data.SqlClient.SqlDataReader.

Because all DataReaders, including the OleDbDataReader, the SqlDataReader, and other managed provider’s DataReaders implement the same IDataReader interface, they should all provide the same base set of functionality.

Each DataReader is optimized for a specific data provider. If the database you are developing against has a managed provider for ADO.NET, then you should take advantage of it. Otherwise, you can use the System.Data.OleDb or the System.Data.Odbc namespaces, which expose more generic managed providers that can access a variety of data sources.

If you are developing against SQL Server™ or Oracle, it would be more efficient to use the provider that was made specifically for these databases. In this column, I will query the SQL Server Northwind database using the System.Data.SqlClient namespace.

The SqlDataReader can only retrieve one row at a time from the data source and in order for it to get the next record, it has to maintain its connection to the data source.

The DataSet, however, doesn’t need to know about where it gets its data.

The DataReader can only get its data from a data source through a managed provider.

The DataSet can also get its data from a data source via a managed provider, but the data source can also be loaded manually, even from an XML file on a hard drive.

In ASP.NET, DataReader objects can be used for more robust situations such as binding themselves to an ASP.NET DataGrid or a DropDownList server control.

The following code demonstrates how to retrieve a list of products from the Northwind database using a SqlDataReader object:

   1: string sSQL = "SELECT * FROM Products";

   2: string sConnString =

   3:     "Server=(local);Database=Northwind;Integrated Security=SSPI;";

   4: using (SqlConnection oCn = new SqlConnection(sConnString))

   5: {

   6:     SqlCommand oSelCmd = new SqlCommand(sSQL, oCn);

   7:     oSelCmd.CommandType = CommandType.Text;

   8:     oCn.Open();

   9:     SqlDataReader oDr = oSelCmd.ExecuteReader();

  10:     DataGrid1.DataSource = oDr;

  11:     DataGrid1.DataBind();

  12: }

Now Read the Data using

   1: SqlDataReader oDr = oCmd.ExecuteReader();

   2: while(oDr.Read()) {

   3:     Console.WriteLine(oDr[0]);

   4: }

Supporting Multiple Resultsets

The DataReader supports access to multiple resultsets, one at a time, in the order they are retrieved. This code is easily modified to handle multiple resultsets. The following code retrieves a SqlDataReader and loops through its rows, again writing the first column’s value for each row to the console:

   1: SqlDataReader oDr = oCmd.ExecuteReader();

   2:     do {

   3:         while(oDr.Read())

   4:         {

   5:             Console.WriteLine(oDr[0]);

   6:         }

   7:         Console.WriteLine(oDr[0]);

   8:     }

   9:     while(oDr.NextResult());

Once all of the rows from the first resultset are traversed, the rowset from the second query is retrieved and its rows are traversed and written. This process can continue for several resultsets using a single SqlDataReader.

The Read method of the SqlDataReader loads the next record so that it can be accessed and moves the position of the cursor ahead. It returns a Boolean value indicating the existence of more records. This feature can help circumvent a common problem in classic ADO development: the endless loop.

The DataReader in the previous code sample shows how to get the value for a column from the DataReader using its ordinal index position.

Disconnected Mode:

The DataSet is the main data storage tool in the ADO.NET disconnected architecture. Unlike the DataReader, the DataSet is not connected directly to a database through a Connection object when you populate it.

Instead, to fill a DataSet from a database you first create a DataAdapter object for the provider and associate it with a SqlConnection object. Then the SqlDataAdapter can broker the data retrieval for the DataSet by issuing a SqlCommand against the database through the SqlConnection, retrieving the data, and filling the DataSet.

You can think of the SqlDataAdapter as a bridge between the connected and disconnected objects.

One of its purposes is to serve as the route for a rowset to get from the database to the DataSet.

For example, when the SqlDataAdapter’s Fill method is executed it opens its associated SqlConnection object (if not already open) and issues its associated SqlCommand object against the SqlConnection. Behind the scenes, a SqlDataReader is created implicitly and the rowset is retrieved one row at a time in succession and sent to the DataSet.

Once all of the data is in the DataSet, the implicit SqlDataReader is destroyed and the SqlConnection is closed.

The following code shows how a DataSet can be filled from the Products table of the Northwind database:

   1: string sSQL = "SELECT * FROM Products";

   2: string sConnString =

   3:     "Server=(local);Database=Northwind;Integrated Security=SSPI;";

   4: SqlDataAdapter oDa = new SqlDataAdapter();

   5: DataSet oDs = new DataSet();

   6: using(SqlConnection oCn = new SqlConnection(sConnString))

   7: {

   8:     SqlCommand oSelCmd = new SqlCommand(sSQL, oCn);

   9:     oSelCmd.CommandType = CommandType.Text;

  10:     oDa.SelectCommand = oSelCmd;

  11:     oDa.Fill(oDs, "Products");

  12: }

The DataSet can read and load itself from an XML document as well as export its rowset to an XML document. Because the DataSet can be represented in XML, it can be easily transported across processes, a network, or even the Internet via HTTP.

Unlike the DataReader, the DataSet is not read-only.

A DataSet can be modified, and rows can be added or deleted. Changes to a DataSet can be sent to the database via a managed provider’s objects.

Another key difference between the DataSet and the DataReader is that the DataSet is fully navigable. Its rows can be traversed forward or backward. The DataReader can be traversed forward only.

In addition, a DataSet is highly flexible in that its DataTable objects can be filtered vertically or horizontally and they can be sorted or even searched.

The DataSet is independent of any one data provider as it relies on a DataAdapter specific to each provider to broker the data between the DataSet and the database.

Not only can the DataSet be loaded from XML, it can also be loaded manually.

   1: //-- Create the table

   2: DataTable oDt = new DataTable("Employees");

   3: DataRow oRow;

   4: oDt.Columns.Add("EmployeeID", System.Type.GetType("System.Int32"));

   5: oDt.Columns.Add("FirstName", System.Type.GetType("System.String"));

   6: oDt.Columns.Add("LastName", System.Type.GetType("System.String"));

   7: oDt.Constraints.Add("PK_Employees", oDt.Columns["EmployeeID"], true);

   8: oDt.Columns["EmployeeID"].AutoIncrement = true;

   9: oDt.Columns["EmployeeID"].AutoIncrementSeed = -1000;

  10: oDt.Columns["EmployeeID"].AutoIncrementStep = -1;

  11: oDs.Tables.Add(oDt);


  13: //-- Add the rows

  14: oRow = oDs.Tables["Employees"].NewRow();

  15: oRow["FirstName"] = "Haley";

  16: oRow["LastName"] = "Smith";

  17: oDs.Tables["Employees"].Rows.Add(oRow);

  18: oRow = oDs.Tables["Employees"].NewRow();

  19: oRow["FirstName"] = "Madelyn";

  20: oRow["LastName"] = "Jones";

  21: oDs.Tables["Employees"].Rows.Add(oRow);


  23: //-- Bind it to a DataGrid

  24: grdEmployees.DataSource = oDs.Tables["Employees"];

Because the DataSet is disconnected, its use can reduce the demand on database servers. It does, however, increase the memory footprint in the tier where it is stored, so be sure to account for that when designing around a DataSet as your data store.

Scaling up on the middle tier using parallel servers and load balancing is a common way to handle the increased load so that session-based information can be stored in objects such as the DataSet.

When to Use the DataSet

The DataSet’s disconnected nature allows it to be transformed into XML and sent over the wire via HTTP if appropriate. This makes it ideal as the return vehicle from business-tier objects and Web services.

DataSet objects are a good choice when the data must be edited or rows added or deleted from the database.

Since the DataSet can be traversed in any direction, all of these features are available. This flexibility also makes the DataSet an easy choice when the situation calls for multiple iterations of the rowset.

If a rowset is intended to be bound to more than one read-only ASP.NET server control, you should consider using a DataSet instead.

The DataSet also works well when a rowset must be persisted between page calls to the Session or Cache objects.

When to Use the DataReader

Binding a DataReader to a DropDownList or even a read-only DataGrid in ASP.NET works well as the data can be retrieved and displayed in the list but does not need to be persisted for editing.

When populating a list or retrieving 10,000 records for a business rule.

When a huge amount of data must be retrieved to a business process, even on a middle tier, it can take a while to load a DataSet, pass the data to it on the business tier from the database, and then store it in memory. The footprint could be quite large and with numerous instances of it running (such as in a Web application where hundreds of users may be connected), scalability would become a problem. If this data is intended to be retrieved and then traversed for business rule processing, the DataReader could speed up the process as it retrieves one row at a time and does not require the memory resources that the DataSet requires.

When output or return parameters need to be retrieved, a DataReader will not allow you to get them until the DataReader and its connection are closed. If data must be bound to a read-only list in a Web Form, a DataReader is a very good choice.

Reference :

Posted in ASP.NET, Sql Server | Tagged: , , | Leave a Comment »

Get the record of the excel file in the dataset

Posted by Viral Sarvaiya on September 3, 2009

here i demonstrate that how to get the data of the excel file in to the dataset.

the function have the two arguments,
1) path of the excel file, from where the excel file is.
2) name of the excel file.

Public Function ReadXLS(ByVal path As String, ByVal filename As String) As DataSet

Dim ds As New DataSet
Dim strConn As String
//this is the connection string
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & path & filename & ";Extended Properties=Excel 8.0;"
//take the connection of the OLEDB
Dim con As New OleDbConnection(strConn)
Dim dt As DataTable
If con.State = ConnectionState.Open Then
End If
dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
If dt Is Nothing Then
Return Nothing
End If
//take the name of the all the sheet of the excel file to the erray
Dim excelSheets() As String = New [String](dt.Rows.Count - 1) {}
Dim i As Integer = 0
For Each row As DataRow In dt.Rows
excelSheets(i) = row("TABLE_NAME").ToString()
If con.State = ConnectionState.Open Then
Dim adpt As OleDbDataAdapter = New OleDbDataAdapter("select * from [" & excelSheets(i) & "]", con)
return ds
i += 1

end if
End Function

i put this code, that have some error in a particular situation, when u have a numeric and varchar datatype mix in a single column then this code give error, the particular datatype is converting NULL in the dataset.

the solution is change the connection string as below…

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & path & filename & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1;ImportMixedTypes=Text;TypeGuessRows=10"""

now the error has been solved.

to know other interesting problem click here

enjoy coding….

Posted in ASP.NET | Tagged: , , | 2 Comments »

How to Use datatable in

Posted by Viral Sarvaiya on July 3, 2009

hear i explain the use of the data table

first u have to take a new data table

Dim myDataTable As New DataTable

then take a column

Dim myDataColumn As DataColumn

give the name of the collumns

myDataColumn = New DataColumn()
myDataColumn.DataType = Type.GetType("System.String")
myDataColumn.ColumnName = "First column name"

myDataColumn = New DataColumn()
myDataColumn.DataType = Type.GetType("System.String")
myDataColumn.ColumnName = "Second column name"

take a row of the datatable

Dim row As DataRow

give the value to that row

row = myDataTable.NewRow()

row("First column Name") = value
row("Second column Name") = value

and finnaly add that row to the table.


now you can use mydatatable as data table as per requirement of the project

Posted in ASP.NET | Tagged: , , , | Leave a Comment »

%d bloggers like this: