如何从多个sql表中读取（只知道列名而不是表名）c＃ [英] how to read from multiple sql tables (knowing only the column names and not table names) c#

问题描述

大家好，



所以我在这里再问一个问题！好的，我的问题是如果我只知道列名，我如何从多个sql表中读取数据。 EG。



eqcas数据库



[用户信息] - cas_user_ext - eqcas

电子邮件地址 - 电子邮件

全名 - 全名



[用户PIN码] - cat_validation - eqcas

名称 - 名称

主要针脚 - 主要针脚

辅助针 - 次要针



cas_user_ext（表格）

cat_validation（表格）



text（列名）

  public  EQData（）
 {
 SQLConnect sqlcon =  new  SQLConnect（）; 
 SqlDataReader myReader =  null ; 
  //   SqlCommand myCommand = new SqlCommand（select x_id，fullname，email FROM cas_user_ext WHERE fullname = @fullname ， 
  //   sqlcon.Connection）;  
 SqlCommand myCommand =  new  SqlCommand（  select x_id，fullname，email ，name，primarypin，secondarypin FROM cas_user_ext，cat_validation WHERE name = @name，
 sqlcon.Connection）; 
 myCommand.Parameters.AddWithValue（  @ name，  ivan1）; 
 myReader = myCommand.ExecuteReader（）; 
  while （myReader.Read（））
 {
 x_id = myReader [   x_id]。ToString（）; 
 fullname = myReader [  fullname]。ToString（）; 
 email = myReader [  email]。ToString（）; 
 name = myReader [  name]。ToString（）; 
 primarypin = myReader [  primarypin]。ToString（）; 
 secondarypin = myReader [  secondarypin]。ToString（）; 
} 
 sqlcon.Close（）; 
} 
} 
 

我也正在使用课程来获取信息（以上是我班级的一部分）







所以我想说要从中检索数据所有列的两个表，但我唯一的信息是用户名称。我将如何实现这一目标？



当我运行我的应用程序时，我正在检索cas_user_ext表的正确信息，但不检索cat_validation，cat_validation检索不同的用户信息。 />


提前谢谢

解决方案

现在，为了使查询更有意义，你需要知道数据库结构。这是必须的。



当您知道查询中列出的表名时，通常需要一个连接来正确连接两个表中的行。查看 http://en.wikipedia.org/wiki/Join_(SQL) 中的联接定义[ ^ ]

< blockquote>你需要用cas_user_ext xid加入cat_validation id。



试试这个sql：

选择ue.x_id，ue。 full_cn，ue.email，ct.name，ct.primarypin，ct.secondarypin from cat_validation as ct join cas_user_ext as ue on ct.id = ue.x_id where ct.name = @name

好的，所以我最终找到了一些有用的信息。

 SqlCommand myCommand =  new  SqlCommand（ 选择x_id，fullname，email，name，primarypin， secondarypin FROM cas_user_ext，cat_validation WHERE x_id = @key AND id = @key，
 sqlcon.Connection）; 
 myCommand.Parameters.AddWithValue（  @ key，  501）; 
 

所以我设法从两个表中检索正确的数据。我现在的问题是我如何检索key.id让我们说名称列（假设我只有用户的用户名）

Hi all,

So here i am asking a question once again! Okay so my question is how do i read data from multiple sql tables if i only know the column names. EG.

eqcas database

[User Info] - cas_user_ext - eqcas
Email adress - "email"
Full Name - "fullname"

[User Pin] - cat_validation - eqcas
Name - "name"
Primary Pin - "primarypin"
Secondary Pin - "secondarypin"

cas_user_ext (table)
cat_validation (table)

"text" (the column names)

    public EQData()
    {
        SQLConnect sqlcon = new SQLConnect();
        SqlDataReader myReader = null;
        //SqlCommand myCommand = new SqlCommand("select x_id, fullname, email FROM cas_user_ext WHERE fullname = @fullname",
                                                                                //sqlcon.Connection);
        SqlCommand myCommand = new SqlCommand("select x_id, fullname, email, name, primarypin, secondarypin FROM cas_user_ext, cat_validation WHERE name = @name",
                                                                    sqlcon.Connection);
        myCommand.Parameters.AddWithValue("@name", "ivan1");
        myReader = myCommand.ExecuteReader();
        while (myReader.Read())
        {
            x_id = myReader["x_id"].ToString();
            fullname = myReader["fullname"].ToString();
            email = myReader["email"].ToString();
            name = myReader["name"].ToString();
            primarypin = myReader["primarypin"].ToString();
            secondarypin = myReader["secondarypin"].ToString();
        }
        sqlcon.Close();
    }
}

and also i'm using a class to get the info (the above is part of my class)



So lets say i want to retrieve data from from both tables for all the columns, but the only info i have is the users "name". How would i achieve that?

When i run my application i am retrieving the correct information for cas_user_ext table but not for cat_validation, cat_validation retrieve a different users information.

Thanks in advance

解决方案

Now, in order to make a query that makes sense, you need to know the database structure. That's a must.

When you know the table names as you have listed in your query you typically need a join to correctly join rows in both tables. Have a look at join definitions from http://en.wikipedia.org/wiki/Join_(SQL)[^]

You'll need to join the cat_validation id with cas_user_ext xid.

Try this sql:
select ue.x_id, ue.fullname, ue.email, ct.name, ct.primarypin, ct.secondarypin from cat_validation as ct join cas_user_ext as ue on ct.id = ue.x_id where ct.name = @name

Okay, so i have managed to get a little further by finally finding some info that helps.

SqlCommand myCommand = new SqlCommand("select x_id, fullname, email, name, primarypin, secondarypin FROM cas_user_ext, cat_validation WHERE x_id = @key AND id = @key",
                                                            sqlcon.Connection);
myCommand.Parameters.AddWithValue("@key", "501");

So i have managed to retrieve the correct data from both tables. The problem i have now is how do i retrieve the key.id for lets say the name column (assuming i have the username of the user only)

这篇关于如何从多个sql表中读取（只知道列名而不是表名）c＃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！