Link to home
Start Free TrialLog in
Avatar of tarashea
tarashea

asked on

Need to translate a SQL query in to C#.NET code

I am having trouble adding to a query in C#.NET, I'm not sure the best way, or how to append some table aliases to it.  

here is the query i need to create in SQL:

SELECT DISTINCT Grain_Corn_Parent_Hybrid
FROM Grain_Corn g1
WHERE (Grain_Corn_Parent_Hybrid IS NOT NULL)  
    AND (Grain_Corn_Product_Status = 'Active')  
    AND (Grain_Corn_Parent_Hybrid <> '')  
    AND exists (select * from Grain_Corn g2                  
                            WHERE g2.Grain_Corn_Parent_Hybrid = g1.Grain_Corn_Parent_Hybrid                    
                            AND g2.Child_Hybrid <> g1.Grain_Corn_Parent_Hybrid                  
                            AND (Grain_Corn_Product_Status = 'Active')            
                        )
ORDER BY Grain_Corn_Parent_Hybrid

I am not sure how to add the g1 & g2 and the second select statement in the "AND exists" ...

My .NET code is in the snippet below.

here is the data.
strBaseTbl = Grain_Corn
strMasterCol = Grain_Corn_Parent_Hybrid
strStatusCol = Grain_Corn_Product_Status

Also, because i am adding the "Child_Hybrid" column in the second select, do i need to pass that in as an additional parameter?  
bool getFamilyKeys(string strBaseTbl, string strMasterCol, string strStatusCol, out string[] strKeys, out string[] strValues)
        {
            if (strHost.Length == 0)
                strHost = "blank";
            if (strServer.Length == 0)
                strServer = "SQLEXPRESS";
            if (strDatabase.Length == 0)
                strDatabase = "blank";
            if (strUsername.Length == 0)
                strUsername = "blank";
            if (strPassword.Length == 0)
                strPassword = "blank";
 
            string strConn = "server=" + strHost + "\\" + strServer + ";uid=" + strUsername + ";pwd=" + strPassword + ";database=" + strDatabase + ";";
 
            Storefront.LogMessage(strListName + " :: Conn string - " + strConn, "", "", 1, false);
            SqlConnection sqlConn = new SqlConnection(strConn);
 
            try
            {
                sqlConn.Open();
 
                string strWhere = "";
                if (strStatusCol.Length != 0)
                    if (strStatusCol.Length != 0)
                        strWhere = " where " + strStatusCol + " = 'Active' ";
                if (strWhere.Length != 0)
                {
                    strWhere = strWhere + " AND " + strMasterCol + " IS NOT NULL ";
                    strWhere = strWhere + " AND " + strMasterCol + " <>  '' ";
                }
 
                string strQuery = "";
                strQuery = "select distinct " + strMasterCol;
                strQuery += " from " + strBaseTbl + " ";
                strQuery += strWhere + " ";
                strQuery += " order by " + strMasterCol;

Open in new window

Avatar of Bob Learned
Bob Learned
Flag of United States of America image

What are you trying to do, and what problems are you having, because I can't quite see where you are having a problem?
Avatar of tarashea
tarashea

ASKER

I'm not sure how to add the:

 AND exists (select * from Grain_Corn g2                  
                            WHERE g2.Grain_Corn_Parent_Hybrid = g1.Grain_Corn_Parent_Hybrid                    
                            AND g2.Child_Hybrid <> g1.Grain_Corn_Parent_Hybrid                  
                            AND (Grain_Corn_Product_Status = 'Active')

to the current strWhere clause:

                if (strWhere.Length != 0)
                {
                    strWhere = strWhere + " AND " + strMasterCol + " IS NOT NULL ";
                    strWhere = strWhere + " AND " + strMasterCol + " <>  '' ";
                }

everything i tried didn't work.  
Did you try this?

 strWhere += string.Format(" AND {0} IS NOT NULL AND {0} <>  '' " + ;
                                            "  AND exists (select * from Grain_Corn g2                  
                            WHERE g2.{0} = g1.{0}                  
                            AND g2.{1} <> g1.{0}                  
                            AND ({2} = 'Active')", strMasterCol, strChildCol, strStatusCol);
ok. but the issue i am having, is i need it to stay in the same format kind of.  using "strTable" and not Grain_Corn directly, i just used that table as an example, it could be other tables.  

so would it be something like this?

1                if (strWhere.Length != 0)
2                {
3                    strWhere = strWhere + " AND " + strMasterCol + " IS NOT NULL ";
4                    strWhere = strWhere + " AND " + strMasterCol + " <>  '' ";
5                    strWhere = strWhere + " AND exists (select * from " + strBaseTbl t2 +;              
6                            " WHERE " + t2.strMasterCol + " = " + t1.strMasterCol;                
7                            " AND " + t2.strChildCol + " <> " t1.strMasterCol;
8                            " AND " + strStatusCol + " = 'Active' ";
9                }

but with this above, i get and expected error on the semi-colon after the + on the 5th line as well as the t2 on that line.  theni also get an expected error on the t1.strMasterCol on the 7th line.  I also don't know how to add the "g1" to the original select distinct.  i just don't think i am adding those aliases correctly.
if i declare t1 and t2 as something above the the where clause and query, would that work?  
I tried the code sample you provided and tried to play around with a bit.  it gave me a lot of "expected" errors as well.  I think i am just struggling with the aliases now.  i put what i have currently in the snippet below.

i receive red underline warnings on the t2 in line 16, the t1 in line 18, and the t1 in line 24

1     string t1;
2     string t2;
3
4     try
5     {
6        sqlConn.Open();
7
8        string strWhere = "";
9        if (strStatusCol.Length != 0)
10          if (strStatusCol.Length != 0)
11             strWhere = " where " + strStatusCol + " = 'Active' ";
12       if (strWhere.Length != 0)
13       {
14          strWhere = strWhere + " AND " + strMasterCol + " IS NOT NULL ";
15          strWhere = strWhere + " AND " + strMasterCol + " <>  '' ";
16          strWhere = strWhere + " AND exists (select * from " + strBaseTbl t2;              
17               " WHERE " + t2.strMasterCol + " = " + t1.strMasterCol;
18               " AND " + t2.strChildCol + " <> " t1.strMasterCol;
19               " AND " + strStatusCol + " = 'Active')"; 
20        }
21
22        string strQuery = "";
23        strQuery = "select distinct " + strMasterCol;
24        strQuery += " from " + strBaseTbl t1 + " ";
25        strQuery += strWhere + " ";
26        strQuery += " order by " + strMasterCol;

Open in new window

I recommend you to use Subsonic ( http://www.subsonicproject.com ). Even if you do not need the other features of subsonic, you can build complex queries using Subsonic's Query class.. (and it s free)
ASKER CERTIFIED SOLUTION
Avatar of Bob Learned
Bob Learned
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ok, i tried above, and i received no build errors, good sign.  let me test it on the application.