Search data from databases

Question

0.00/5 (No votes)

See more:

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#

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.

Posted 23-Sep-15 13:27pm

Member 12005342

Updated 23-Sep-15 13:39pm

PIEBALDconsult

v2

Add a Solution

Comments

Member 12005342 24-Sep-15 17:24pm

As suggested I created two methods

I stored first database value to array and trying to use in database 2 all working fine but only last value is getting printed

Here is my code:
method1:

using System.Data.SqlClient;

// .....

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

List<string> TempproductID = new List<string>() ;

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 ";
for ( int I = 0; I < tempproductid.count; I++)
{
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",Tempproductid[i].to string());

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

In that for loop only one value is getting in datagridview not getting all

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

DamithSL · Answer 1 · 2015-09-23T13:56:00

Solution 1

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?[^]

Posted 23-Sep-15 13:56pm

DamithSL

Comments

Member 12005342 25-Sep-15 4:01am

As suggested I created two methods

I stored first database value to array and trying to use in database 2 all working fine but only last value is getting printed

Here is my code:
method1:

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 ";
for ( int I = 0; I < tempproductid.count; I++)
{
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",Tempproductid[i].to string());

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

In that for loop only one value is getting in datagridview not getting all

Sisir Patro · Answer 2 · 2015-09-23T18:07:00

Hi,

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

you can use :

SQL

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;
}

Thanks,
Sisir Patro