Create a user defined table type in c# to use in sql server stored procedure

by

Simplest option is to create a DataTable in C# code and pass it as a parameter to your procedure. Assuming that you have created a User Defined Table Type as:

CREATE TYPE [dbo].[userdefinedtabletype] AS TABLE(
    [ID] [varchar](255) NULL,
    [Name] [varchar](255) NULL
)

then in your C# code you would do:

DataTable dt = new DataTable();
dt.Columns.Add("ID", typeof (string));
dt.Columns.Add("Name", typeof (string));
//populate your Datatable

SqlParameter param = new SqlParameter("@userdefinedtabletypeparameter", SqlDbType.Structured)
{
    TypeName = "dbo.userdefinedtabletype",
    Value = dt
};
sqlComm.Parameters.Add(param);

Remember to specify SqlDbType.Structured as the type of parameter and specify the name you have used in creating your UDT.

More Related Contents:

  1. Taking lot of time to read from SQL Server using c#
  2. What is the connection string for localdb for version 11
  3. System.Data.SqlClient.SqlException: Login failed for user
  4. Cannot load Counter Name data because an invalid index -Exception
  5. How do I rotate a target object around axis A by how much a different object is rotated around axis B?
  6. Rename a file in C#
  7. How to get HttpContext.Current in ASP.NET Core? [duplicate]
  8. Paging with LINQ for objects
  9. Read XML Attribute using XmlDocument
  10. Using FileSystemWatcher to monitor a directory
  11. reference assignment is atomic so why is Interlocked.Exchange(ref Object, Object) needed?
  12. How to form a correct MySQL connection string? [closed]
  13. Detecting network connection speed and bandwidth usage in C#
  14. ref and out parameters in C# and cannot be marked as variant
  15. FtpWebRequest FTP download with ProgressBar
  16. C# – how to determine whether a Type is a number
  17. How to make Check Box List in ASP.Net MVC
  18. Manipulating images with .NET Core
  19. C# – How to copy a single Excel worksheet from one workbook to another?
  20. EF5 Code First – Changing A Column Type With Migrations
  21. Date of Birth validation keeps showing
  22. Getting time span between two times in C#?
  23. C# – Get the item type for a generic list
  24. Can .NET load and parse a properties file equivalent to Java Properties class?
  25. Customize automatic response on validation error
  26. How do I default Visual Studio to C# projects instead of VB.NET?
  27. using static Regex.IsMatch vs creating an instance of Regex
  28. Basic render 3D perspective projection onto 2D screen with camera (without opengl)
  29. How can I force a log out of all users for a website?
  30. How to write to the main exe’s .config userSettings section?

Leave a Comment