CLR parameter-length?

[sniper1983]

Hi there,

I'm wondering whether one of you could clarify how to define the maxlength in bits when inserting a parameter in a stored procedure f.ex. What I might do is something like this:

[Microsoft.SqlServer.Server.SqlProcedure]
    public static void CreateUser([B]SqlChars pInitials [/B]... )
    {
    SqlCommand mSqlCommand = new SqlCommand("", new SqlConnection("context connection=true"));
    mSqlCommand.CommandText = @"INSERT INTO .... ";

....

The databaseField to insert data might only accept VarChar(32) So I have to check for this (max 32 chars) when inserting to the DB. How can I do that in the parameterlist?

[dickersonka]

you don't have to, it will automatically trim it

or lets see you get a string field that you are going to insert, with max length of 5

string fName = "jonathan";

if(fName.length > 5)
{
//you can handle what to do with the length here
//we can trim it, throw an error, or whatever else
}

//create command text with our parameter
mSqlCommand.CommandText = @"INSERT INTO table (FIRST_NAME) values " + fName;

[sniper1983]

Thank you.. I know that I can test it inside but if I want to use the parameters directly into the sqlstring then its not that practical. But you say that I don't have to. But after inserting the stored procedure my sqlchars is analysed to pInitials (nvarchar(max)), but this "max" can it automatically test this when used in the sql. Like this:

public static void CreateUser(SqlChars pInitials, SqlChars pFirstName, SqlChars pSirName, SqlInt16 pSecurityLevel, SqlChars pPassword,
SqlChars pEmailAddress, SqlChars pPhoneNumber, SqlChars pMobileNumber)
{
SqlCommand mSqlCommand = new SqlCommand("", new SqlConnection("context connection=true"));
SqlCommand.CommandText = @"INSERT INTO MAIN.dbo.USER (Initials, FirstName, SirName, SecurityLevel, Password,
EmailAddress, PhoneNumber, MobileNumber) VALUES
(@pInitials, @pFirstName, @pSirName, @pSecurityLevel, @pPassword,
@pEmailAddress,@pPhoneNumber,@pMobileNumber)";
mSqlCommand.Connection.Open();

SqlPipe pipe = SqlContext.Pipe;
pipe.ExecuteAndSend(mSqlCommand);
mSqlCommand.Connection.Close();
}

[dickersonka]

you want to test the parameter? like pFirstName?

also when inserting, rather than messing with sqlchars class, i would just use a string

[sniper1983]

you want to test the parameter? like pFirstName?

also when inserting, rather than messing with sqlchars class, i would just use a string

Yes I want to secure that a value is no longer than the maximum chars in the db column. Ex varchar(30) for instance.
I could do it like this
if (pFirstName.length > 30) {
Continue.. }
else{
Throw an exception..}

But I was hoping that I could use the CLR-methods parameters directly into the sql-string as shown. Is this possible or should I actually check every parameter in the input about the right length?

[dickersonka]

i don't understand what you can't do, you can check the length, but there isn't something to throw an exception from the runtime automatically

[sniper1983]

Forget the exception, forget the check on the length of the individual parameters. What I'm asking is if it is nessesary to check the length of the incomming parameter to the function before using them in the sql-string.
I'm having some parameters in the CLR-function/stored procedure defined as sqlvarchar for example; And I have a sql-string. What I want to do is to secure that the length of the parameter does not exceed the length of the DB-field to insert the value into. Can't I do this without checking each parameters length inside the CLR-stored procedure?
Like this (The text in red is what I want to do but the syntax is not right. In this case securing the db-field Initials with the dbtype varchar(30) ):

public static void CreateUser(SqlChars(30) pInitials)
{
SqlCommand mSqlCommand = new SqlCommand("", new SqlConnection("context connection=true"));
SqlCommand.CommandText = @"INSERT INTO MAIN.dbo.USER (Initials) Values (@pInitials)";
mSqlCommand.Connection.Open();

SqlPipe pipe = SqlContext.Pipe;
pipe.ExecuteAndSend(mSqlCommand);
mSqlCommand.Connection.Close();
}

[dickersonka]

no you must create your own validation code

think of it this way, what happens if you change the length of your field in the db, how can the code know

you can either check it code side, or create a stored procedure that will check the length along with the insert