Compare two data tables in c#


Description:

Comparing two datatable and get the final table with difference in it, this kind of requirement is generally arise generally when we are doing multiple insert or update. I have scenario like get the two different tables data, compare it and if there is difference in output then insert that data into the third table. So use the approach to get the data from database into different table and then insert the difference output using SqlBulkCopy in to the third table.

 

Code:

Following is the code to compare two datatable. Just copy and paste this class into your code and its working.

 

using System;

using System.Data;

 

namespace [Namespace Name]

{

public class CompareDataTables

{

public static DataTable CompareTables(DataTable first, DataTable second)

{

first.TableName = “FirstTable”;

second.TableName = “SecondTable”;

 

//Create Empty Table

DataTable table = new DataTable(“Difference”);

 

try

{

//Must use a Dataset to make use of a DataRelation object

using (DataSet ds = new DataSet())

{

//Add tables

ds.Tables.AddRange(new DataTable[] { first.Copy(), second.Copy() });

 

//Get Columns for DataRelation

//DataColumn[] firstcolumns = new DataColumn[ds.Tables[0].Columns.Count];

DataColumn[] firstcolumns = new DataColumn[2];

firstcolumns[0] = ds.Tables[0].Columns[1];

firstcolumns[1] = ds.Tables[0].Columns[7];

 

//for (int i = 0; i < firstcolumns.Length; i++)

//{

// firstcolumns[i] = ds.Tables[0].Columns[i];

//}

 

//DataColumn[] secondcolumns = new DataColumn[ds.Tables[1].Columns.Count];

DataColumn[] secondcolumns = new DataColumn[2];

secondcolumns[0] = ds.Tables[1].Columns[1];

secondcolumns[1] = ds.Tables[1].Columns[7];

 

//for (int i = 0; i < secondcolumns.Length; i++)

//{

// secondcolumns[i] = ds.Tables[1].Columns[i];

//}

 

//Create DataRelation

DataRelation r = new DataRelation(string.Empty, firstcolumns, secondcolumns, false);

 

ds.Relations.Add(r);

 

//Create columns for return table

for (int i = 0; i < first.Columns.Count; i++)

{

table.Columns.Add(first.Columns[i].ColumnName, first.Columns[i].DataType);

}

 

//If First Row not in Second, Add to return table.

table.BeginLoadData();

 

foreach (DataRow parentrow in ds.Tables[0].Rows)

{

DataRow[] childrows = parentrow.GetChildRows(r);

if (childrows == null || childrows.Length == 0)

table.LoadDataRow(parentrow.ItemArray, true);

}

 

table.EndLoadData();

 

}

}

catch (Exception ex)

{

throw ex;

}

 

return table;

}

}

}

 

Code Explanation:

 

  1. CompareTables method is static, so you can call it directly from your class. Just add the appropriate reference.
  2. Pass the two data tables in this method.
  3. Then creates the dataset.
  4. Add two tables into the dataset.
  5. Then important part, define the columns two compare the data table. In my case i just want to check the column 1 & 7. If there is a mismatch between this two then inserts the record. You can modify the code as per your requirement or just uncomment the line above and below my code , which adds all columns.
  6. Creates data relation on that column(s).
  7. Add relation two the dataset.
  8. Create the target or difference table colums
  9. Then load the difference data in it and return the difference table.
Advertisements

CSV or Excel file download in asp.net


Many systems have requirement to download the file in different format like Excel, CSV, etc. For that we are using HttpContext.Current.Response class in System.Web namespace.

Following is the code to download the CSV file:

try

{

HttpContext.Current.Response.Clear();

HttpContext.Current.Response.ClearHeaders();

HttpContext.Current.Response.ClearContent();

HttpContext.Current.Response.AddHeader(“content-disposition”, “attachment; filename = FileName.csv”);

HttpContext.Current.Response.ContentType = “text/csv”;

HttpContext.Current.Response.AddHeader(“Pragma”, “public”);

Response.WriteFile(Server.MapPath(“~/ExcelFiles/FileName.csv”));

HttpContext.Current.Response.End();

Response.Flush();

}

catch (Exception ex)

{

throw ex;

}
We can download the excel file by just changing the HttpContext.Current.Response.ContentType to Application/x-msexcel.

Transaction in Linq To Entity or Entity Framework


A transaction is a series of operations performed as a single unit of work. Entity Framework transactions are a part of its internal architecture. The SaveChanges method operates within a transaction and saves results of the work. It is designed to ensure data integrity.

Imagine we want the SaveChanges to be only a part of more complicated update process. For example, we want to execute several SaveChanges as one operation in the single ObjectContext.
The possible solution is to use the database connection. Here is a sample code:

STP_TAXEntities context = new STP_TAXEntities();

DbTransaction dbTran = null;

Message = string.Empty;

try

{

if (context.Connection.State == ConnectionState.Open)

{

context.Connection.Close();

}

context.Connection.Open();

dbTran = context.Connection.BeginTransaction();

int Count = 0;

Count = context.COUNTRY_TAXQDI_ALLOWABILITY.Where(a => a.COUNTRY_CODE == CountryCode && a.CLIENT_TOKEN == ClientToken).Count();

if (Count > 0)

{

var query = from a in context.COUNTRY_TAXQDI_ALLOWABILITY

where a.COUNTRY_CODE == CountryCode &&   a.CLIENT_TOKEN == ClientToken

select a;

foreach (var item in query)

{

context.COUNTRY_TAXQDI_ALLOWABILITY.DeleteObject(item);

context.SaveChanges();

}

}

else

{

Message = “No security country exception found for selected country.”;

}

dbTran.Commit();

}

catch (Exception)

{

dbTran.Rollback();

throw;

}

finally

{

context.Connection.Close();

}

As we can easily ensure, the transaction is opened on database connection, and SaveChanges are added to this transaction.

Prevent duplicate entry when using SqlBulkCopy


As we know, the fastest way to insert data in Database is to insert bulk data. SqlBulkCopy Class in System.Data.SqlClient Namespace provides simplest and fastest way to copy large amounts of data to SqlServer. Instead of inserting all data row by row, SqlBulkCopy process all data at once, thus making the insertions fast.

An example should be given below:

public void CopyData(DataTable TableToCopy, string SqlTableName)

{

        SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings["conStr"].ToString());

if (con.State == ConnectionState.Open)

{

con.Close();

}

con.Open();

SqlBulkCopy bc = new SqlBulkCopy(con);

bc.ColumnMappings.Add(0, 0);

bc.ColumnMappings.Add(1, 1);

bc.ColumnMappings.Add(2, 2);

bc.ColumnMappings.Add(3, 3);

bc.ColumnMappings.Add(4, 4);

bc.ColumnMappings.Add(5, 5);

if (SqlTableName != “DBTable”)

{

bc.ColumnMappings.Add(6, 6);

bc.ColumnMappings.Add(7, 7);

bc.ColumnMappings.Add(8, 8);

}

bc.DestinationTableName = SqlTableName;

bc.WriteToServer(TableToCopy);

con.Close();

}

Here, WriteToServer() method, do bulk insert in database. So SqlBulkCopy is very useful in the case that when we want to insert number rows in the Database at the same time not one by one. But in some situation we are in need to check for Duplicate records while doing multiple insertion. But SqlBulkCopy does not provide any built-in method or event to do that.

One possible solution is to make the column(s) of the table unique. And for that we have to create a Index on the Table with column names that we want to check for Uniqueness.

Following is the example for the same:

 

CREATE INDEX myIndex on [Table Name]

(

[Column name1],  [Column name2], ..

)

WITH (IGNORE_DUP_KEY = ON)

 

This index would create a unique index for the columns specified and it checks automatically that if any duplicate rows are when we do BulkCopy. If yes then do nothing else inserts bulk data into the Database.

Important


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using DemoBAL;

namespace DemoPresentation
{
public partial class _Default : System.Web.UI.Page
{
#region “Page Events”
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGrid(“0”);
Session[“dtCategory”] = null;
lblMessage.Text = string.Empty;
}
lblMessage.Text = string.Empty;
}
#endregion

#region “Button Events”
protected void btnPrevious_Click(object sender, EventArgs e)
{
BindGrid(false);
}

protected void btnNext_Click(object sender, EventArgs e)
{
BindGrid(true);
}

protected void btnSave_Click(object sender, EventArgs e)
{
if (Session[“dtCategory”] != null)
{
int count = 0;
DataTable dtCategory = (DataTable)Session[“dtCategory”];
foreach (GridViewRow row in grdProducts.Rows)
{
dtCategory.Rows[count][“CategoryName”] = ((TextBox)row.FindControl(“txtCategoryName”)).Text;
count += 1;
}
Session[“dtCategory”] = null;
count = ProductBAL.Update(dtCategory, Convert.ToInt32(lblCurrentPage.Text) – 1, grdProducts.PageSize);
BindGrid(lblCurrentPage.Text);
lblMessage.Text = “[ ” + count + ” ]Record Update!”;
}
}
#endregion

#region “Gridview Events”
protected void grdProducts_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName == “cmdExpand”)
{
int id = Convert.ToInt32(e.CommandArgument);
if (id >= Convert.ToInt32(grdProducts.PageSize))
{
id = id – (Convert.ToInt32(grdProducts.PageSize) * Convert.ToInt32(grdProducts.PageIndex));
}

decimal CategoryId = Convert.ToDecimal(grdProducts.DataKeys[id][“CategoryId”]);
BindSubGrid(((GridView)grdProducts.Rows[id].FindControl(“grdSubProducts”)), CategoryId, id);
}
}

#endregion

#region “Private Methods”
private void BindGrid(string CurrentPage)
{
lblCurrentPage.Text = CurrentPage != “0” ? (Convert.ToInt32(CurrentPage) – 1).ToString() : CurrentPage;
DataTable dtCategory = CategoryBAL.Get(0, Convert.ToInt32(lblCurrentPage.Text), grdProducts.PageSize);
if (dtCategory != null && dtCategory.Rows.Count > 0)
{
grdProducts.DataSource = dtCategory;
grdProducts.DataBind();
int RecordCount = CategoryBAL.Get();
lblPageCount.Text = ” Of ” + ((RecordCount / grdProducts.PageSize) + (RecordCount % 2 == 0 ? 1 : 0)).ToString();
lblCurrentPage.Text = (Convert.ToInt32(lblCurrentPage.Text) + 1).ToString();
Session[“dtCategory”] = dtCategory;
}
else
{
lblPageCount.Text = “0”;
lblCurrentPage.Text = “0”;
}
setButtonVisibility();
}

/// <summary>
/// Bind Page Data as per supplied parameters.
/// </summary>
/// <param name=”IsNext”>Pass true if Next page button and false incase of previous page button click.</param>
private void BindGrid(bool IsNext)
{

DataTable dtCategory = CategoryBAL.Get(0, IsNext == true ? Convert.ToInt32(lblCurrentPage.Text) : Convert.ToInt32(lblCurrentPage.Text) – 2, grdProducts.PageSize);
if (dtCategory != null && dtCategory.Rows.Count > 0)
{
grdProducts.DataSource = dtCategory;
//                grdProducts.PageIndex = (IsNext == true ? grdProducts.PageIndex + 1 : grdProducts.PageIndex – 1);
grdProducts.DataBind();
int RecordCount = CategoryBAL.Get();
lblPageCount.Text = ” Of ” + ((RecordCount / grdProducts.PageSize) + (RecordCount % 2 == 0 ? 1 : 0)).ToString();
lblCurrentPage.Text = IsNext == true ? (Convert.ToInt32(lblCurrentPage.Text) + 1).ToString() : (Convert.ToInt32(lblCurrentPage.Text) – 1).ToString();
Session[“dtCategory”] = dtCategory;
}
else
{
lblPageCount.Text = “0”;
lblCurrentPage.Text = “0”;
}
setButtonVisibility();
}

private void BindSubGrid(GridView grdSubCategory, decimal CategoryId, int rownum)
{
DataTable dtSubCategory = ProductBAL.Get(CategoryId);
if (dtSubCategory != null && dtSubCategory.Rows.Count > 0)
{
grdSubCategory.DataSource = dtSubCategory;
grdSubCategory.DataBind();
((Label)grdProducts.Rows[rownum].FindControl(“lblNodataFound”)).Text = string.Empty;
}
else
{
((Label)grdProducts.Rows[rownum].FindControl(“lblNodataFound”)).Text = “No sub category found!”;
}
}

private void setButtonVisibility()
{
if (Convert.ToInt32(lblCurrentPage.Text) == 1)
{
btnPrevious.Enabled = false;
}
else
{
btnPrevious.Enabled = true;
}

int RecordCount = CategoryBAL.Get();
if (Convert.ToInt32(lblCurrentPage.Text) == (RecordCount / grdProducts.PageSize) + (RecordCount % 2 == 0 ? 1 : 0))
{
btnNext.Enabled = false;
}
else
{
btnNext.Enabled = true;
}
}
#endregion
}
}

Reverse String


public static String Reverse(string input)
{
var length = input.Length;
var buffer = new char[length];
for (var i = 0; i < input.Length; i++)
{
buffer[i] = input[(length – i) – 1];
}
return new String(buffer);
}

private string ReverseWordsLogic2(string str)
{
int count = 0;
char[] charArray = str.ToCharArray();
int len = str.Length – 1;
for (int i = 0; i <= len; i++)
{
if (charArray[i].ToString() == ” ” || charArray[i] == ‘ ‘)
count += 2;
}

string[] words = new string[count + 1];
string word = “”;
int icount = 0;
count = 0;
for (int i = 0; i <= len; i++)
{
if (charArray[i] != ‘ ‘)
{
word += charArray[i];
}
else
{
words[icount] = word;
words[icount + 1] = ” “;
icount += 2;
word = “”;
}
}
words[icount] = word;
word = “”;

int wordlen = words.Length – 1;

for (int i = 0; i <= wordlen; i++)
words[i] = words[wordlen – i];

for (int i = 0; i <= wordlen; i++)
word += word[i].ToString();
return word;
}

Good One


Treeview query

SELECT  P1.emp
FROM Personnel AS P1, Personnel AS P2
WHERE P1.lft BETWEEN P2.lft AND P2.rgt
GROUP BY P1.emp

Update multiple

public static int Update(DataTable dtNewCategory, int PageIndex, int PageSize)
{
DataTable dtOldCategory = new DataTable();
int count = 0;

dtOldCategory = CategoryBAL.Get(0, PageIndex, PageSize);
CategoryInformation objCategoryInformation = new CategoryInformation();
for (int i = 0; i < dtOldCategory.Rows.Count; i++)
{
if (dtOldCategory.Rows[i][“CategoryName”].ToString() != dtNewCategory.Rows[i][“CategoryName”].ToString())
{
objCategoryInformation = Connection.objDemoDataContext.CategoryInformations.Single(m => m.CategoryId == Convert.ToDecimal(dtOldCategory.Rows[i][“CategoryId”]));
objCategoryInformation.CategoryName = dtNewCategory.Rows[i][“CategoryName”].ToString();
count += 1;
}
}
Connection.objDemoDataContext.SubmitChanges();
return count;
}
}

convert datatable class


using System;
using System.Data;
using System.Reflection;

namespace DemoBAL
{
public static class ConvertToDataTable
{
#region “Converting ObjectArray to Datatable”

/// <summary>
/// Method to Convert Datatable from object Array.
/// </summary>
/// <param name=”array”></param>
/// <returns></returns>
public static DataTable ConvertToDatatable(Object[] array)
{

PropertyInfo[] properties = array.GetType().GetElementType().GetProperties();
DataTable dt = CreateDataTable(properties);
if (array.Length != 0)
{
foreach (object o in array)
FillData(properties, dt, o);
}
return dt;
}

/// <summary>
/// Method To Create total column of datatable.
/// </summary>
/// <param name=”properties”></param>
/// <returns></returns>
private static DataTable CreateDataTable(PropertyInfo[] properties)
{
DataTable dt = new DataTable();
DataColumn dc = null;
foreach (PropertyInfo pi in properties)
{
dc = new DataColumn();
dc.ColumnName = pi.Name;
//dc.DataType = pi.PropertyType;
dt.Columns.Add(dc);
}
return dt;
}

/// <summary>
/// Method for Fill data in DataTable.
/// </summary>
/// <param name=”properties”></param>
/// <param name=”dt”></param>
private static void FillData(PropertyInfo[] properties, DataTable dt, Object o)
{
DataRow dr = dt.NewRow();
foreach (PropertyInfo pi in properties)
{
dr[pi.Name] = pi.GetValue(o, null);
}
dt.Rows.Add(dr);
}

#endregion

}
}

How to create dynamic Gridview?


Many times we have the requirement where we have to create columns dynamically.
This article describes you about the dynamic loading of data using the DataTable as the datasource.

Details of the Grid

Let?s have a look at the code to understand better.

Create a gridview in the page,

Drag and drop the GridView on to the page
Or

Manually type GridView definition in the page.


public partial class _Default : System.Web.UI.Page

{

#region constants

const string NAME = “NAME”;

const string ID = “ID”;

#endregion

protected void Page_Load(object sender, EventArgs e)

{

loadDynamicGrid();

}

private void loadDynamicGrid()

{

#region Code for preparing the DataTable

//Create an instance of DataTable

DataTable dt = new DataTable();

//Create an ID column for adding to the Datatable

DataColumn dcol = new DataColumn(ID ,typeof(System.Int32));

dcol.AutoIncrement = true;

dt.Columns.Add(dcol);

//Create an ID column for adding to the Datatable

dcol = new DataColumn(NAME, typeof(System.String));

dt.Columns.Add(dcol);

//Now add data for dynamic columns

//As the first column is auto-increment, we do not have to add any thing.

//Let’s add some data to the second column.

for (int nIndex = 0; nIndex < 10; nIndex++)

{

//Create a new row

DataRow drow = dt.NewRow();

//Initialize the row data.

drow[NAME] = “Row-” + Convert.ToString((nIndex + 1));

//Add the row to the datatable.

dt.Rows.Add(drow);

}

#endregion

//Iterate through the columns of the datatable to set the data bound field dynamically.

foreach (DataColumn col in dt.Columns)

{

//Declare the bound field and allocate memory for the bound field.

BoundField bfield = new BoundField();

//Initalize the DataField value.

bfield.DataField = col.ColumnName;

//Initialize the HeaderText field value.

bfield.HeaderText = col.ColumnName;

//Add the newly created bound field to the GridView.

GrdDynamic.Columns.Add(bfield);

}

//Initialize the DataSource

GrdDynamic.DataSource = dt;

//Bind the datatable with the GridView.

GrdDynamic.DataBind();

}

}

Display image Dynamically on crystal report


This is very common requirement to display image on crystal report from database. Generally we store image path in database not whole image, because it increase the size of the database. I had the same requirement few days ago and search lot about the same. Finally I got the solution and decide to publish the post.

Here I put the code in C#, so just copy and paste the code and change it as per your need.

dsOrgDetails = objOrganisationMasterBal.GetOrganisationDetails();

if (dsOrgDetails.Tables[0].Rows.Count > 0)

{

DataRow drow;

dt.Columns.Add(“OrganizationImage”, System.Type.GetType(“System.Byte[]”));

dt.Columns.Add(“OrganizationName”, System.Type.GetType(“System.String”));

drow = dt.NewRow();

FileStream fs;

BinaryReader br;

if (File.Exists(AppDomain.CurrentDomain.BaseDirectory + “OrganisationLogo\\” + dsOrgDetails.Tables[0].Rows[0][“Logo”].ToString()))

{

// open image in file stream

fs = new FileStream(AppDomain.CurrentDomain.BaseDirectory + “OrganisationLogo\\” + dsOrgDetails.Tables[0].Rows[0][“Logo”].ToString(), FileMode.Open);

}

else

{

// if photo does not exist show the nophoto.jpg file

fs = new FileStream(AppDomain.CurrentDomain.BaseDirectory + “OrganisationLogo\\noimages.jpg”, FileMode.Open);

}

br = new BinaryReader(fs);

// define the byte array of file length

byte[] imgbyte = new byte[fs.Length + 1];

// read the bytes from the binary reader

imgbyte = br.ReadBytes(Convert.ToInt32((fs.Length)));

drow[0] = imgbyte;

//drow[0] = br.Read(imgbyte, 0, imgbyte.Length);

drow[“OrganizationName”] = dsOrgDetails.Tables[0].Rows[0][“OrganisationName”].ToString();

// add the image in bytearray

dt.Rows.Add(drow);

dt.AcceptChanges();

// add row into the data table

br.Close();

// close the binary reader

fs.Close();

}

First get the data from database, here I mean get the image path from the database. Then create DataTable for temporary conversation of path into image. In DataTable, create column of type System.Byte[].

Then create objects of FileStream and BinaryReader. Check that the file is exists or not, if exists then open the file in FileStream’s object. Then assign the FileStream’s object to the BinaryReader and go ahead as per the code.

Now, add the generated DataTable with Image in your DataSet which is assigned to the crystal report’s datasource.

Now, see at crystal report you can find the “OrganizationImage”  in Field Explorer of Crystal Report. Just Drag n Drop the image on crystal report. That’s it.

In some cases, at run time Image is not displayed on crystal report using above code. So for that do one thing.

Comment out this line : drow[0] = imgbyte;

and Uncomment this line : drow[0] = br.Read(imgbyte, 0, imgbyte.Length);

Now, compile the solution and run the report. Again Drag n Drop the image on report. And do the reverse, i mean Uncomment drow[0] = imgbyte this line and Comment  drow[0] = br.Read(imgbyte, 0, imgbyte.Length); this line, and re-compile the solution and run the report. You will get the image on your report.