MS SQL Filter String in C#

Dear Experts,

 I wonder if this string is correct:

                  string selectSql = @"select distinct top 10 pd.EmployeeID, e.FirstName + N' ' + e.LastName as EmployeeName
                                          from PayrollDetail pd
                                                inner join Employee e
                                                on pd.EmployeeID = e.EmployeeID
                                          where GrossPay > 0";
                  
                   if (!string.IsNullOrEmpty(payCode))
                        selectSql += " and PayCode = '" + payCode + "'";
                  
 When the raw SQL String is this when payCode is not null :

select distinct top 100 pd.EmployeeID, e.FirstName + N' ' + e.LastName as EmployeeName
                                          from PayrollDetail pd
                                                inner join Employee e
                                                on pd.EmployeeID = e.EmployeeID
                                          where GrossPay > 0 And PayCode = '063015'

Many thanks.
JimiJ13I T ConsultantAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
try:

string selectSql = @"select distinct top 10 pd.EmployeeID, e.FirstName + ' ' + e.LastName as EmployeeName
                                          from PayrollDetail pd
                                                inner join Employee e
                                                on pd.EmployeeID = e.EmployeeID
                                          where GrossPay > 0";
                  
                   if (!string.IsNullOrEmpty(payCode))
                        selectSql += " and PayCode = '" + payCode + "'";

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Yes, should work that way, as long as the change from TOP 100 to TOP 10 is intentional.
JimiJ13I T ConsultantAuthor Commented:
Ryan Chong,

I tried your suggestion but still doesn't work.

The Top 10 or 100 are immaterial. It's the payCode doesn't get applied. I can't see the difference between the original and suggested code except the ( N' '). Can you explain?  

 
 
Thanks.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
>> I wonder if this string is correct ...
not too sure but what are you actually try to ask here? is your script returning an error when variable payCode is null, etc?
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
There is no difference, and the N is not required if the other columns in the expression are nvarchar already. If they are varchar, the N forces a nvarchar result.
But the query itself does not change.
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
The code is correct. payCode is applied as restriction only if it is neither empty nor null. Are you certain that var is populated, and it is a string var?
JimiJ13I T ConsultantAuthor Commented:
I am sure there value for payCode because if I try to execute this from query Window it works:

select distinct top 100 pd.EmployeeID, e.FirstName + N' ' + e.LastName as EmployeeName
                                          from PayrollDetail pd
                                                inner join Employee e
                                                on pd.EmployeeID = e.EmployeeID
                                          where GrossPay > 0 And PayCode = '063015'


This is how the method is done and being called:
public static void SendEmails(string payDRN, string payCode)
            {
         
                  
                  string selectSql = @"select distinct top 10 pd.EmployeeID, e.FirstName + ' ' + e.LastName as EmployeeName, e.Sex
                                          from PayrollDetail pd
                                                inner join Employee e
                                                on pd.EmployeeID = e.EmployeeID
                                          where GrossPay > 0";
                 
                   if (!string.IsNullOrEmpty(payDRN))
                        selectSql += " and PayDRN = '" + payDRN + "'";
                        
                           else if (!string.IsNullOrEmpty(payCode))
                        selectSql += " and PayCode = '" + payCode + "'";
                        
                        
                  using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["StringName"].ConnectionString))
                  {
                        
                        
                        using (SqlCommand cmd = new SqlCommand(selectSql, con))
                        {
                              con.Open();

                              using (SqlDataReader dr = cmd.ExecuteReader())  
                              {

                                         ... more codes follow




Then I call it:
SendEmails("", "063015");


Thanks.
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
not too sure if you only accept either one (payDRN or payCode but not both)  in your logic or both are acceptable?

if both are acceptable, you may change:

if (!string.IsNullOrEmpty(payDRN))
                        selectSql += " and PayDRN = '" + payDRN + "'";
                        
                           else if (!string.IsNullOrEmpty(payCode))
                        selectSql += " and PayCode = '" + payCode + "'";

Open in new window


to:

if (!string.IsNullOrEmpty(payDRN))
                        selectSql += " and PayDRN = '" + payDRN + "'";
                        
 if (!string.IsNullOrEmpty(payCode))
                        selectSql += " and PayCode = '" + payCode + "'";

Open in new window


?

or you can tell us further details what's problem you're facing right now..
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
I cannot see an issue. But you can only provide either payDRN or payCode that way, with payDRN having precedence.
Check the selectSQL content pre executing it - it should resemble to the SQL as provided manually.
JimiJ13I T ConsultantAuthor Commented:
Either payDRN or payCode only is allowed. Executing selectSQL manually  as mentioned earlier is as expected as follows:
select distinct top 100 pd.EmployeeID, e.FirstName + N' ' + e.LastName as EmployeeName
                                          from PayrollDetail pd
                                                inner join Employee e
                                                on pd.EmployeeID = e.EmployeeID
                                          where GrossPay > 0 And PayCode = '063015'

Thanks.
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Then what we see as code is correct, and I don''t understand your issue.
JimiJ13I T ConsultantAuthor Commented:
Neither I understand the issue that's why I ask the experts.
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
If the sql of http:#a40882702 is the exact same as generated by the function, it works. payCode restriction is applied, this cannot be the issue. However, we can only help with something we see, everything else is poor guessing.
Try to restate your issue please.
JimiJ13I T ConsultantAuthor Commented:
Thanks for the helps. I finally traced what's real issue is - lacks 1 more filter.
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
I don't agree to the closure.
a) http:#a40882490 is no change to your code.
b) grade "B" is not justified. We did all we could to confirm your code is correct.
c) points are not assigned for effort on EE.

Please tell why you think the way you closed the question is correct. Strictly seen, http:#a40882491 is the only correct answer to your question ("I wonder if this string is correct").
JimiJ13I T ConsultantAuthor Commented:
Qlemo,

I agree with your point, but can I still change the grading? The point given to each is personal.


Thanks for the comments.
JimiJ13I T ConsultantAuthor Commented:
Thank you guys! Your pointers led me to the right focus to sort the issue.
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Sorry to be a pain, but I still cannot see how Ryan's post could have helped at all. It is a unfair to accept a "nonsense" comment, and give it even more points than a correct answer. I understand you want to give Ryan points, and that is ok (in your realm), but you need to obey to the rules - you cannot accept that comment, it changes nothing, confirms nothing, does not contribute. Remove it from the question and nothing is missing.
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
the accepted answer which I posted just to mention that the N can be removed since it's not really required there.

I more curious on what's the "real problem" within ur scripts. the real problem still exist? again we need to understand further so we can provide relevant suggestions or solutions.
JimiJ13I T ConsultantAuthor Commented:
Qlemo,

I'm posted because I need help, to clear doubts, not to have more problems. I don't just wait but also sort with my own way to solve my issue. The result set of the query I posted is to be used to iterate the second layer which is SP, and I discovered to be unmatched in terms of parameters. Needless to say that with or without anybody's inputs, I can also discover the issue but could be slower.  
 
Now, with regards to giving points, I like demonstrative experts for they are very helpful to me. I felt your style as intimidating. Please don't question me why? I also give more points to the 1st responder.
 
 
 
I hope there is no more question after this.
 
 
 
Many Thanks.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.