How to access another command within a sql command in c#

Question

I am creating a migration application to migrate data from one app to another.

using (var conn = new System.Data.SqlClient.SqlConnection(""))
{
    conn.Open();
    using (var cmd = conn.CreateCommand())
    {
        cmd.CommandText = "SELECT * from Client";
        using (var reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                Client client = new Client();
                client.Active = reader["ActiveStatus"] == DBNull.Value ? false: Convert.ToBoolean(reader["ActiveStatus"]);
                client.Country = reader["Country"] == DBNull.Value ? 1 : Convert.ToInt32(reader["Country"]);
                client.Info = reader["Information"] == DBNull.Value ? "" : (string)reader["Information"];
            }
        }
    }
}

In the above, I need to get client.ProductId from another table using the Id. How can I create another query inside the above to get the data? Should I add a new command?

whats the relationship between `Client` table and the "another" table? Why not use `Joins` and fetch the whole thing once instead of hitting the database repeatedly? — j4rey, Jan 30 '19 at 07:59
Possible duplicate of [Get data using inner join from Dapper](https://stackoverflow.com/questions/49935928/get-data-using-inner-join-from-dapper) — mjwills, Jan 30 '19 at 08:07
Assuming you would like to perform two separate calls you could perform two select operations in scope of one Command and return DataSet of two tables with SqlDataAdapter. — LukaszBalazy, Jan 30 '19 at 08:31
You can create a class including a function that calls a single SQL value `scalar` then call it via code `public class Class1 { private SqlConnection con = new SqlConnection("myConnectionString"); private SqlCommand cmd = new SqlCommand(); private object obj; public object GetCell(string strsql) { cmd = new SqlCommand(strsql, con); if (con.State == ConnectionState.Open) con.Close(); con.Open(); obj = cmd.ExecuteScalar; con.Close(); return obj; } }` — Moiyd, Jan 30 '19 at 09:01

senthil ss · Answer 1 · 2019-01-30T10:54:00.357

use JOIN between two tables instead of multiple sql command. JOIN would be much faster and easier. Better use this. For Reference

 cmd.CommandText = "SELECT Cl.ProductId,
                           Pr.ProductId,
                           Cl.ActiveStatus,
                           Cl.Country,
                           Cl.Information
                   from Client cl 
                   [INNER/LEFT/RIGHT] JOIN Product Pr 
                   on Cl.ProductId = Pr.ProductId";

How to access another command within a sql command in c#

1 Answers1