How to apply this query? 1

[newbie14]

Dear All,
I have 3 columns each called as pmCode,sl1Code and sl2Code. So when I am entering a new code I want to check if this code exist? How can I run a query to make sure a code doesnt exist in any of the 3 columns? My second query I want to run is that when I enter any code I want to see if it exist in any of the 3 column and I would like to know which column is it from ? Any solution? Thx.

 

[phinoppix]

Is this ado.net datatable problem or t-sql question?

 

[jmeckley]

this sounds like a sql problem. ado.net will execute the sql, but it doesn't care what the sql is.

1st query: does it exist

select count(1) 
from [table] 
where [pmCode] =@value
  or  [sl1Code] = @value 
  or  [sl2Code] = @value

use (int)command.ExecuteScalar > 0 to determine if the value exists.
2nd query: just get the record from the database

select [table].*
from [table] 
where [pmCode] =@value
  or  [sl1Code] = @value 
  or  [sl2Code] = @value

then determine the field in code.

In my experience, the more logic I put into my sql statments the more difficult the system is to maintain. I try to keep all my logic in the code and just access the database to get and put records.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[newbie14]

Dear Jason,
Thank for your sql statements. But just that for second sql I need more things is that which column does that code belonging to? Can I do that ? Thx.

 

[jmeckley]

don't put the logic in the sql statement. process the logic after you load the record into a datatable or custom object

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[newbie14]

Dear Jason,
I am a bit confuse with your explanation. Can you please give me more clarifications? Thx.

 

[jmeckley]

How can I run a query to make sure a code doesnt exist in any of the 3 columns?

My second query I want to run is that when I enter any code I want to see if it exist in any of the 3 column

and I would like to know which column is it from ?

this can actually be done with 1 query

select [table].*
from [table]
where [pmCode] =@value
  or  [sl1Code] = @value
  or  [sl2Code] = @value

once these record(s) are loaded into a datatable (or custom entity) you can preform all the logic you need.

lets assume you load the record into a datatable. create a service which will compare the existing record and determine which column it came from

class MyService
{
   private readonly DataTable table;

   public MyService(DataTable table)
   {
       this.table = table;
   }

   public bool value_exists(object value)
   {
       return table[0]["column 1"] == value
              || table[0]["column 2"] == value
              || table[0]["column 3"] == value ;
   }

   public string name_of_column_containing_value(object value)
   {
       if(table[0]["column 1"] == value) return "column 1";
       if(table[0]["column 1"] == value) return "column 2";
       return "column 3";
   }
}

in the client code

var value = textbox.text;
var table = get_record_from_repository_by(value);
var service = new MyService(table);
var column_name = string.Empty;
if(service.value_exists(value))
   column_name = service.name_of_column_containing_value(value);

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[newbie14]

Dear Jason,
Can I do like this first I run this query select

.*
from

where [pmCode] =@value
or [sl1Code] = @value
or [sl2Code] = @value
Then second step I go and do this 3 different query. Like this
query1= select

.*
from

where [pmCode] =@value

query2= select

.*
from

where [sl1Code] =@value

query3= select

.*
from

where [sl2Code] =@value

I feel this is the only way to decide is from which column right ?

 

[jmeckley]

you can do it that way. the difference is your hitting the db 4 times and putting the bulk of the work on sql. i'm suggesting hitting the db once and putting the work in code.

if you think about your current solution your attempting to get the same row 4 times, why? get it once, and preform the necessary logic.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[newbie14]

Dear Jason,
I think I get your idea now better so when I do a select it should be right. Then I do a logic compare which of the column is matching am I right ? Thanks.

select pmCode,sl1Code,sl2Code
from

where [pmCode] =@value
or [sl1Code] = @value
or [sl2Code] = @value

 

[jmeckley]

exactly

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[newbie14]

Dear Jason,
Thx for confirming and guiding me ya.