Comparing 2 columns values

yokzu

Hi,
I have 2 table and 1 column in each one like this;
------------------------------------------------------------------

Column1                Column2
1                           3
2                           4
3                           5
4                           6
...                          ...
------------------------------------------------------------------

I want to compare column1's values with column2, and get the values which arent exist in column2. For example in this instance, I want to get "1" and "2" values.

Obversely, when I compare column2's values with column1, I want to get only these values; "5" and "6"

I tryed datatable, datareader and lots of codes but I didnt get the logic. How can I do that?

Regards.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Satyapriya Nayak15y ago
Accepted answer
Hi sleepy,

Here is your solution

Default2.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Default2" %>

    Untitled Page










            Text="compare column1's values with column2" onclick="Button1_Click" />




            Text="compare column2's values with column1" onclick="Button2_Click" />


Default2.aspx.cs code

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
public partial class Default2 : System.Web.UI.Page
{
    string strConnString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
    string str;
    SqlCommand com;

    protected void Button1_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection(strConnString);
        con.Open();
        str = "select * from t1 except select * from t2";
        com = new SqlCommand(str, con);
        SqlDataReader reader = com.ExecuteReader();
        GridView1.DataSource = reader;
        GridView1.DataBind();
        con.Close();
    }
    protected void Button2_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection(strConnString);
        con.Open();
        str = "select * from t2 except select * from t1";
        com = new SqlCommand(str, con);
        SqlDataReader reader = com.ExecuteReader();
        GridView1.DataSource = reader;
        GridView1.DataBind();
        con.Close();
    }
}

Thanks
If this post helps you mark it as answer
Vulpes15y ago
It's quite easy if you use the LINQ extension method Except():

using System;
using System.Data;
using System.Linq;

class Test
{
static void Main()
{
DataTable dt = new DataTable();
DataColumn dc1 = new DataColumn("Column1",typeof(int));
DataColumn dc2 = new DataColumn("Column2", typeof(int));
dt.Columns.Add(dc1);
dt.Columns.Add(dc2);
DataRow dr1 = dt.NewRow();
dr1[0] = 1;
dr1[1] = 3;
DataRow dr2 = dt.NewRow();
dr2[0] = 2;
dr2[1] = 4;
DataRow dr3 = dt.NewRow();
dr3[0] = 3;
dr3[1] = 5;
DataRow dr4 = dt.NewRow();
dr4[0] = 4;
dr4[1] = 6;
dt.Rows.Add(dr1);
dt.Rows.Add(dr2);
dt.Rows.Add(dr3);
dt.Rows.Add(dr4);

int[] ia1 = new int[dt.Rows.Count];
int[] ia2 = new int[dt.Rows.Count];

for(int i = 0; i < ia1.Length; i++) ia1[i] = (int)dt.Rows[i][0];
for(int i = 0; i < ia2.Length; i++) ia2[i] = (int)dt.Rows[i][1];

Console.WriteLine("The following are in column1 but not in column2\n");

int[] ia3 = ia1.Except(ia2).ToArray();

foreach(int i in ia3) Console.WriteLine(i);

Console.WriteLine("\nThe following are in column2 but not in column1\n");

int[] ia4 = ia2.Except(ia1).ToArray();

foreach(int i in ia4) Console.WriteLine(i);

Console.ReadKey();
}
}