CODEWITHSUNDEEP
c#.netsql-serversql-server-2008user-defined-types
user3035305
user3035305

Reputation: 295

SQL UDT Type parameter pass in C#

I have a SQL UDT Type as 

CREATE TYPE [dbo].[UDTName] FROM nvarchar(50) NULL
GO

and I using it in a procedure to insert data into my DB. My procedure is something like this

CREATE PROCEDURE [dbo].[proc_name]
(
  @param1 int,
  @param2 int,
  @param3 UDTName,
  @param4 UDTComment,
  @param5 UDTType   = '' output,
  @param6 UDTType   = '' output
)

When I am calling this procedure from my C# code, UDT type data is not any saving in the table.

My C# code:

  private IDictionary<string, string> RunQuery(string procName, List<Parameter> input) {
foreach (Parameter inputPair in input)
                    {
         SqlParameter sp = new SqlParameter("@" + inputPair.Name , inputPair.Type);  
                    // I have tried SQLDbType as Varchar, nVarchar also
         sp.Direction = System.Data.ParameterDirection.Input
         sp.SqlDbType = SqlDbType.Udt;
         // i have an if condition around it to check if it is UDT type only then add a UdtTypeName 
         sp.UdtTypeName = "UDTName"; // I have tried dbo.UDTName also
         sp.Size = inputPair.Size; //setting only in case of varchar
         sp.Value = inputPair.Value;

         cmd.Parameters.Add(sp);

}
  cmd.CommandType = CommandType.StoredProcedure;

                param1.ParameterName = "@param5";
                param1.SqlDbType = System.Data.SqlDbType.VarChar;
                param1.Size = 255;
                param1.Direction = System.Data.ParameterDirection.Output;
                cmd.Parameters.Add(param1);

 _reader = cmd.ExecuteReader();
}

Parameter class is 

     public class Parameter
        {
            public string Name { get; set; }
            public object Value { get; set; }
            public object Type { get; set; }
            public bool isUDT { get; set; }
            public int Size { get; set; }
public bool UdtTypeName  { get; set; }
        }

But it is not inserting data into my DB and throwing following exception:

Result Message: System.Exception : Specified type is not registered on the target server.System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089.

If I am using varchar SQLDbType in place of UDTType SQLDbType in my code, it is running successfully but not inserting data into the tables.

I did some research but everywhere people are using Table Value Pair base UDT type.

Note: I don't have privileges to alter anything in SQL DB.

Edit

I tried datatable also

My code:

 DataTable dataTable = new DataTable("UDTName");
                            dataTable.Columns.Add();
                            dataTable.Rows.Add("my_value");

                            var inputParam = cmd.Parameters.AddWithValue("@param3", dataTable);
                            inputParam.SqlDbType = SqlDbType.Structured;
                            inputParam.Direction = System.Data.ParameterDirection.Input;

It is showing below error:

Result Message: System.Exception : Operand type clash: table type is incompatible with nvarchar(50)

Upvotes: 3

Views: 10358

Answers (2)

helix
helix

Reputation: 376

You need to correctly specify 2 types (sp.SqlDbType, sp.UdtTypeName), name and the value.

It looks like you are either setting a wrong value or incorrect sp.UdtTypeName.

Check the way you are creating SqlParameter:

SqlParameter sp = new SqlParameter("@" + inputPair.Name , inputPair.Type);

The problem here is that your inputPair.Type is of type object.

So the following constructor is called:

public SqlParameter( string parameterName , object value )

instead of the other which you might expect:

public SqlParameter( string parameterName , SqlDbType dbType )

Your code should be something like this instead:

SqlParameter sp = new SqlParameter("@" + inputPair.Name , value);
sp.SqlDbType = SqlDbType.Udt;
sp.UdtTypeName = "UDTName";

https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter.udttypename(v=vs.90).aspx

Upvotes: 3

Learner
Learner

Reputation: 175

Try with following code,

var testingparam = command.Parameters.AddWithValue("@param3", myDataTable);
testingparam.SqlDbType = SqlDbType.Structured;

I have used this after creating a user defined type in SQL Management tools.

Upvotes: 0

Related Questions