September 8, 2008 09:55am pdt

Member Login

Hall of Fame

Yearly

1jaime_oliv... 520,997
2TheLearn... 354,216
3jandromeda 240,713
4Fernando... 215,869
5JimBrandley 205,943
6p_davis 194,294
7angelIII 164,843
8Dhaest 157,837
9burakiewicz 147,113
10anarki_ji... 130,382
11naspinski 122,111
12ZachSmith 118,878
13ShazbotOK 107,960
14CyrexCor... 99,575
15jcoehoorn 96,435
16philipjonat... 91,424
17REA_AN... 88,770
18josgood 83,688
19gauthampj 81,948
20raterus 79,965
21margajet24 79,659
22junges 77,200
23GreenGho... 75,560
24monarch... 73,395
25emoreau 73,286

	[x] Attachment Details

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

Zones: C# Programming Language, Microsoft Visual C#.Net, Visual Studio 2008

Tags: C#.NET

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?

         
           
             1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:

           
           
             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;
           
         

Start your free trial to view this solution

[x]

The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

The Grade of the Solution
The Zone Rank of the Expert Providing the Solution
The Number of Author and Expert Comments
The Number of Experts Contributing
The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

8.4

Question Stats

Zone: Programming

Question Asked By: tarashea

Solution Provided By: TheLearnedOne

Participating Experts: 2

Solution Grade: A

Translate:

Loading Advertisement...

20080723-EE-VQP-34 / EE_QW_2_20070628