case sensitive database and sql command parameters

[Lucky]

Hi guys,
I'm having problem with case sensitive collation of SQL Database. one
my client is having case sensitive database. While developing the Data
Layer i didn't consider this scenario.
the problem is I've all Store Procedures in the Database and I'm using
sqlcommand to execute those SPs with Parameters.
for e.g.
Data Layer:
......
SqlCommand cmd = new SqlCommand();
cmd.Parameters. Add("@param1", SqlDbType.VarCh ar);
........

Case Sensitive Database:

PROCEDURE [dbo].[SPxyz]
.....
@Param1 [nvarchar](4000),
......

you can notice that the parameter in the C# Code is in small cape
"param1" while it is declare with capital latter in Database "Param1".

this is causing the failure while executing the procedure through the
code.

Is it possible to ignore the case sensitivity while executing the SP
through code?

I would appreciate your response over my Query. if there is a ways to
ignore the case, then it will save lot of time to me otherwise i've to
go through several SPs to check for the parameter casing.

thanks,
Lucky

[sloan]

Re: case sensitive database and sql command parameters


I learnt this the hard way in 1999.

...

My advice is to always create your dev db with case sensitivity.
(Of course you know that, and are looking for a work around that works NOW).

I don't know of a work around.

The first time I had to do it, it scarred me enough for life....that I start
out my dev db's with case sensitivity now.
Especially when I deploy to an unknown client setup.
It took about 3-4 says to fix every tsql stored procedure and view.....you
can't imagine the permutations of somthing as simple as
@EmployeeID
@EmployeeId
@employeeID
@employeeId
and a few mistypes like
@eMployeeID

Good luck.

There are of course ways to force a collation on a db create.....if you give
the client tsql scripts.
I've found that drama is harder than using a dev/case sensitive db.



"Lucky" <tushar.n.patel @gmail.comwrote in message
news:f9f9536b-a323-4a0a-996a-50a6f0a7f72b@w1 g2000prd.google groups.com...

Hi guys,
I'm having problem with case sensitive collation of SQL Database. one
my client is having case sensitive database. While developing the Data
Layer i didn't consider this scenario.
the problem is I've all Store Procedures in the Database and I'm using
sqlcommand to execute those SPs with Parameters.
for e.g.
Data Layer:
.....
SqlCommand cmd = new SqlCommand();
cmd.Parameters. Add("@param1", SqlDbType.VarCh ar);
.......
>
Case Sensitive Database:
>
PROCEDURE [dbo].[SPxyz]
....
@Param1 [nvarchar](4000),
.....
>
you can notice that the parameter in the C# Code is in small cape
"param1" while it is declare with capital latter in Database "Param1".
>
this is causing the failure while executing the procedure through the
code.
>
Is it possible to ignore the case sensitivity while executing the SP
through code?
>
I would appreciate your response over my Query. if there is a ways to
ignore the case, then it will save lot of time to me otherwise i've to
go through several SPs to check for the parameter casing.
>
thanks,
Lucky

[cfps.Christian]

Re: case sensitive database and sql command parameters

I was reading that it was tough to alter the database, and I agree
with sloan that it wouldn't be too fun to do that. If you're
dynamically adding your parameters then I would consider adding a
method that will capitalize the first letter of the string.