CODEWITHSUNDEEP
c#sqlsql-server
Luke Makk
Luke Makk

Reputation: 1197

sql server query not giving output in C#

string query = @"select p.package_id, pd.download_id
                     from Tools.ftp.package p
                     join Tools.ftp.package_download pd 
                     on p.package_id = pd.package_id
                     where p.package_name = 'foo'";

Trying to pull data from sql server in C# with SqlConnection class, I get output without the where statement but a blank file with it left in, also odd behavior with more complicated joins. Why is that. Is there anything to watch for behavior wise when writing queries in C#, ie formatting issues and whatnot.

Here's the whole thing.

StreamWriter fileout = new StreamWriter(@"C:\test\output9.csv");

    string myConnectionString = @"Data Source=foobar;Initial Catalog=DB;User id=user;Password=pw";
    SqlConnection Conn = new SqlConnection(myConnectionString);


    string query = @"select p.package_id, pd.download_id
                 from Tools.ftp.package p
                 join Tools.ftp.package_download pd 
                 on p.package_id = pd.package_id
                 where p.package_name = 'foo'";

    SqlCommand cmd = new SqlCommand(query, Conn);
    cmd.Connection = Conn;
    Conn.Open();

    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataTable dt = new DataTable();

    da.Fill(dt);
    Conn.Close();
    da.Dispose();


    foreach (DataRow dataRow in dt.Rows)
    {
        foreach (var item in dataRow.ItemArray)
        {
            fileout.Write(item+ ",");
        }
        fileout.Write("\n");
    }
    fileout.Close();

Upvotes: 0

Views: 226

Answers (1)

D Stanley
D Stanley

Reputation: 152521

I can take out the join clause and keep the where clause in and it gives me output

That means there are no matching records in Tools.ftp.package_download Try a left join instead:

select p.package_id, pd.download_id
     from Tools.ftp.package p
     LEFT join Tools.ftp.package_download pd 
     on p.package_id = pd.package_id
     where p.package_name = 'foo'

Upvotes: 2

Related Questions