none
How to define SqlParameter object for a Sql Server User Defined Data Type RRS feed

  • Question

  • I have added a new type in Sql Server using sp_addtype system store procedure
    EXEC sp_addtype NAME, 'varchar(30)', 'NOT NULL';

    I have a store procedure which has a parameter of above type
    CREATE PROCEDURE dbo.GetEmployeeDetail(@emp dbo.NAME)
    AS
    BEGIN
    ...
    END

    Now in the C# project I need to a create a command object with parameter @emp of type dbo.NAME, I could not find how to define this other than SqlParameter("@emp", SqlDbType.VarChar, 30) which defeat the purpose of creating the user defined data type.

     

     

     

    Friday, February 5, 2010 2:18 PM

Answers

  • My point is that it doesn't matter whether you specify the SqlDbType or not.

    If you specify SqlDbType.VarChar, 30 and you set the parameter to be something greater than 30 in length, it will truncate it when it gets to the database. .NET will not throw an error and you will never know that happened.

    This behavior takes place whether the parameter in the Stored Proc is a UDT or not. It makes no difference. Just to be sure, I tested this before I posted. If you're seeing different results than this, please post a repro.
    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Monday, February 8, 2010 12:20 AM

All replies

  • Hi,

    To enable the use of UDT in .NET, you can create UDTs in .NET and then use them to code in SQL Server. The below article gives you steps on how to do it.



    Hope this helps and answers your questions.


    HTH

    technocrat_aspire
    Sunday, February 7, 2010 2:36 PM
  • Thanks for the reply, but the answer you provided is for .NET CLR based user defined data type where as I was looking for T-SQL based user defined data type.
    Sunday, February 7, 2010 3:08 PM
  • Yes. What you can do is to register the same in SQL Server and it then becomes a UDT in SQL Server too. You will find details in the link I gave you.


    HTH
    technocrat_aspire

    Sunday, February 7, 2010 3:35 PM
  • Thanks for the reply, but these kind of User Defined Data Type ( T-SQL based) exists before Microsoft introduced the CLR based User defined data type (UDDT), my question is regarding the T-SQL based UDDT which has nothing to do with CLR based UDDT. In fact they are totally unrelated. I am looking for solution to T-SQL based UDDT, I hope this is clear. Thanks for your time for posting the reply to this question.
    Sunday, February 7, 2010 3:43 PM
  • Now in the C# project I need to a create a command object with parameter @emp of type dbo.NAME, I could not find how to define this other than SqlParameter("@emp", SqlDbType.VarChar, 30) which defeat the purpose of creating the user defined data type

    Well, it kind of depends on how you're handling your parameters. You don't actually have to specify the SqlDbType. If you already have the value when you're creating the parameter you could do it two ways:

    SqlParameter parm = new SqlParameter("@emp", MyEmpValue);

    or:

    MyCommand.Parameters.AddWithValue("@emp", MyEmpValue);


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Sunday, February 7, 2010 4:19 PM
  • I simplified the requirement, actually I need to execute the store procedure multiple time in a loop so I just create command object once and set the parameters in the loop.
    Sunday, February 7, 2010 4:36 PM
  • OK, so the Command.Parameters.AddWithValue() method works for you?
    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Sunday, February 7, 2010 4:42 PM
  • That's not a acceptable answer. Wonder nobody knows about it or it is wrong forum to ask.
    If some one is not clear about T-SQL base user defined data type (Pre-CLR based), see this

    http://msdn.microsoft.com/en-us/library/aa933121%28SQL.80%29.aspx

    Sunday, February 7, 2010 11:12 PM
  • It's not acceptable? I don't know why not. Especially in this particular case when you are simply talking about a string. As far as the Stored Proc is concerned, using a parameter the way I described above (with the .AddWithValue() method) will work just fine. The Stored Proc doesn't care. Am I missing something?


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Sunday, February 7, 2010 11:33 PM

  • Well the better solution for this

    EXEC sp_addtype NAME, 'varchar(30)', 'NOT NULL';

    is

    SqlParameter("@emp", SqlDbType.VarChar, 30)

    but as I said if this will fail if I change the NAME UDT in sql server from varchar(30) to varchar(10) (not a hard error but data trimming will take place) unless I change the front end code too. I want to avoid this front end code change when the UDT is changed.
    Sunday, February 7, 2010 11:44 PM
  • My point is that it doesn't matter whether you specify the SqlDbType or not.

    If you specify SqlDbType.VarChar, 30 and you set the parameter to be something greater than 30 in length, it will truncate it when it gets to the database. .NET will not throw an error and you will never know that happened.

    This behavior takes place whether the parameter in the Stored Proc is a UDT or not. It makes no difference. Just to be sure, I tested this before I posted. If you're seeing different results than this, please post a repro.
    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Monday, February 8, 2010 12:20 AM