从c#中具有相同主键的两个表中获取不同的列值

时间:2022-04-18 08:18:01

I have two tables with same name/schema but with different values. I need to find the row which has same primary key(1st column) but different values. ex. my-table:

我有两个具有相同名称/架构但具有不同值的表。我需要找到具有相同主键(第1列)但不同值的行。恩。我的表:

id   name   age
1    ram    25
2    mohan   30

my-table:

id  name  age
3   harry  26
**1   ram   35**
3   tony   45

So I need 2 row from 2 table with value 35. It should return whole row as data table or data row. I am using oracle database. needed c# code for this solution. and it should work for multiple column values for other tables also. my code..

所以我需要2个表中的2行,值为35.它应该返回整行作为数据表或数据行。我正在使用oracle数据库。这个解决方案需要c#代码。它也适用于其他表的多个列值。我的代码..

public OracleCommand getColumns(OracleConnection connection, DataTable table, int i, string tab, DataTable table3)
{
    int columCount = table.Columns.Count;
    string [] colArray = new string[columCount];
    string pkey = table.Columns[0].ColumnName;
    string pkeyValue = table.Rows[i][0].ToString();
    string query2 = "SELECT * FROM " + tab +
                " WHERE " + tab + "." + pkey + " = '" + pkeyValue + "'";
    OracleCommand command = new OracleCommand();
    int k = 0;
    int  X =0;

    for(int j=1 ; j<colArray.Length;j++)
    {
        string column = table.Columns[j].ColumnName;
        string columnValue = table.Rows[i][j].ToString();
        string add = " OR " + tab + "." + column + " = '" + columnValue + "'";
        query2 += add;
        command.CommandText = query2;
        command.CommandType = CommandType.Text;
        command.Connection = connection;
        var check = command.ExecuteNonQuery();
        if (check == null)
        {
            k++;
        }
        else
            X++;   
    }
     return command;
}

1 个解决方案

#1


Here is example for your tables, I am presenting data from t2 which do not match rows in t1:

这是您的表的示例,我将呈现t2中与t1中的行不匹配的数据:

using Oracle.DataAccess.Client;

...

public string OraText(string pkey, string[] tables, string[] columns)
{
    string sSQL = "select " + pkey + "";
    foreach (string s in columns)
    {
        sSQL += ", " + tables[1] + "." + s;
    }
    sSQL += Environment.NewLine + "  from t1 join t2 using (" + pkey + ") "
        + Environment.NewLine + "  where 1=0 ";
    foreach (string s in columns)
    {
        sSQL += " or " + tables[0] + "." + s + " <> " + tables[1] + "." + s;
    }

    return sSQL;
}

private void Form1_Load(object sender, EventArgs e)
{
    OracleConnection oc = new OracleConnection(
        "User Id=scott;Password=tiger;Data Source=XE");
    oc.Open();

    string[] tables = {"t1", "t2"};
    string[] columns = {"name", "age"};

    string sSQL = OraText("id", tables, columns);
    OracleCommand oracmd = new OracleCommand(sSQL, oc);
    OracleDataReader reader = oracmd.ExecuteReader();
    while (reader.Read())
    {
        Console.WriteLine(reader.GetValue(0) + " " 
            + reader.GetValue(1) + " " +reader.GetValue(2));
    }

    oc.Close();
}

Console output:

1 ram 35

#1


Here is example for your tables, I am presenting data from t2 which do not match rows in t1:

这是您的表的示例,我将呈现t2中与t1中的行不匹配的数据:

using Oracle.DataAccess.Client;

...

public string OraText(string pkey, string[] tables, string[] columns)
{
    string sSQL = "select " + pkey + "";
    foreach (string s in columns)
    {
        sSQL += ", " + tables[1] + "." + s;
    }
    sSQL += Environment.NewLine + "  from t1 join t2 using (" + pkey + ") "
        + Environment.NewLine + "  where 1=0 ";
    foreach (string s in columns)
    {
        sSQL += " or " + tables[0] + "." + s + " <> " + tables[1] + "." + s;
    }

    return sSQL;
}

private void Form1_Load(object sender, EventArgs e)
{
    OracleConnection oc = new OracleConnection(
        "User Id=scott;Password=tiger;Data Source=XE");
    oc.Open();

    string[] tables = {"t1", "t2"};
    string[] columns = {"name", "age"};

    string sSQL = OraText("id", tables, columns);
    OracleCommand oracmd = new OracleCommand(sSQL, oc);
    OracleDataReader reader = oracmd.ExecuteReader();
    while (reader.Read())
    {
        Console.WriteLine(reader.GetValue(0) + " " 
            + reader.GetValue(1) + " " +reader.GetValue(2));
    }

    oc.Close();
}

Console output:

1 ram 35