从数据库中搜索数据 [英] Search data from databases

问题描述

我有两个数据库在database1我有table1（custid，productid，customername）

Database2：table2（productname，productid，pice，mfg）



我想显示特定custid购买的所有产品





这是我的代码：

C＃

I have two database In database1 I am having table1 (custid,productid,customername )
Database2 : table2 (productname,productid, pice, mfg)

I want to display all the products purchased by particular custid


Here is my code :
C#

using System.Data.SqlClient;

    // .....

    string queryString = "Select custID from Table1 where custId ="+ textbox1.text; 
    String TempCustID;

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        SqlCommand command = new SqlCommand(queryString, connection);
        connection.Open();
        SqlDataReader reader = command.ExecuteReader();
        
        while (reader.Read())
        {
          if (!reader.IsDBNull(0))
          {
             TempCustID = reader[0].ToString();
              
String stringprovider = "@database connection string ";
Sqlconnection con2 = new sqlconnection(stringprovider);
Con2.open();
Cmd2.connection=con2;
Cmd2.commandType = new commandType.text;
 Cmd2.commandText = "select * from Table2 where Productid = @productid";
Cmd2.parameters.addwithvalue("@productid",TempCustID);
          }
        }
        reader.Close();
Dataset Ds = new dataset();
Oledbdataadaptaer da1 = new oledbdataadapter(cmd2);
Datatable Table2 = new Data table();
Da1.fill(table2);
Datagridview2.source = table2;


    }

在此，我只获得该客户的第一个产品详细信息不要一次显示所有产品。

请建议。

In this I am getting only the first product details of that customer its not displaying all the products at a time.
Please suggest.

推荐答案

将其分为两种方法。首先将根据条件从数据库1中获取客户ID。将这些客户ID传递给第二种方法并获取产品详细信息。您可以从第一种方法创建客户ID列表，并在第二种方法中构建SQL IN子句。请参阅构建SQL，其中来自字符串列表中的语句在一行？ [ ^ ]

separate this into two methods. first will fetch the customer IDs from database one based on the condition. pass those customer IDs to second method and fetch the product details. You may create list of customer IDs from first method and build SQL IN clause in second method. refer Building SQL "where in" statement from list of strings in one line?[^]

我同意DamithSL还有一种方法可以做到这一点。



你可以使用：

Hi,

I do agree with "DamithSL" and there is one more way to do this as well.

you can use :

SELECT *
FROM server1table
    INNER JOIN server2.database.dbo.server2table ON .....

为了更好地理解，请按照这里......



这里更新的方法：

For better understanding follow here...

Your updated method here:

using System.Data.SqlClient;

// .....

string queryString = "Select productID from Table1 where custid="+ textbox1.text;

List TempproductID = new List() ;


using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(queryString, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();

while (reader.Read())
{
if (!reader.IsDBNull(0))
{
Tempproductid.add( reader[0].ToString());


}
}
reader.Close();
tempproductid.toarray();
}

method 2:
String stringprovider = "@database connection string ";
string strProductIds = " (";
for ( int I = 0; I < tempproductid.count; I++)
{
	strProductIds += Convert.ToString(tempproductid[I]) + ",";
}

strProductIds = strProductIds.Remove(strProductIds.Length - 1);
strProductIds = strProductIds + ")"

Sqlconnection con2 = new sqlconnection(stringprovider); 
Con2.open();
Cmd2.connection=con2; 
Cmd2.commandType = new commandType.text; 
Cmd2.commandText = "select * from Table2 where Productid in @productid"; 
Cmd2.parameters.addwithvalue("@productid",strProductIds);

Dataset Ds = new dataset(); 
sqldataadaptaer da1 = new sqldataadapter(cmd2); 
Datatable Table2 = new Data table(); 
Da1.fill(table2);
Datagridview2.source = table2;
}

谢谢，

Sisir Patro

Thanks,
Sisir Patro

这篇关于从数据库中搜索数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！