locked
Whats better - Pass DataTable of JSON from the application to Stored Procedure? RRS feed

  • Question

  • User46076047 posted

    What is the best way to pass C# list of objects to stored procedure? JSON or DataTable (SQL Table Type)?

    I have list of Guids that i need to pass to the stored procedure. 

    one option in to serialize the list<Guid> object to JSON and pass it to the SP as a string and then convert the json string to a table in the SP.

    the other option is to convert the C# List<Guid> object to DataTable add pass it as a Table Type param to the SP and then work directly on that table.

    What is the best way to do it in terms of performance and more?

    Tuesday, November 28, 2017 7:01 PM

All replies

  • User-335504541 posted

    Hi OmTechGuy,


    >>one option in to serialize the list<Guid> object to JSON and pass it to the SP as a string and then convert the <g class="gr_ gr_9 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="9" data-gr-id="9">json</g> string to a table in the SP 

    Since you have to convert the list to a table, I suggest you <g class="gr_ gr_30 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar replaceWithoutSep" id="30" data-gr-id="30">to</g> convert it to <g class="gr_ gr_28 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling" id="28" data-gr-id="28">datatable</g> then pass it to SP.

    For example:

    The Sp:

    CREATE TYPE [dbo].[GuidList] AS TABLE(
        [Item] [NVARCHAR](MAX) NULL
    );
    go;
    CREATE PROCEDURE [dbo].[sp_GuidList]
           @list GuidList READONLY
    AS
    BEGIN
        -- Just return the items we passed in
        SELECT l.Item FROM @list l;
    END
    

    The C# code:

            public static string connstring= "connstring";
            static void Main(string[] args)
            {
                using (var con = new SqlConnection(connstring))
                {
                    con.Open();
                    List<Guid> guidList = new List<Guid>() {
                           Guid.NewGuid(),Guid.NewGuid(),Guid.NewGuid(),Guid.NewGuid(),
                                Guid.NewGuid(),Guid.NewGuid(),Guid.NewGuid(),Guid.NewGuid()};
    using (SqlCommand cmd = new SqlCommand("exec sp_GuidList @list", con)) { var pList = new SqlParameter("@list", SqlDbType.Structured); pList.TypeName = "dbo.GuidList"; pList.Value = ConvertDataTable(guidList);//convert list to DataTable cmd.Parameters.Add(pList); using (var dr = cmd.ExecuteReader()) { while (dr.Read())//read from store procedure Console.WriteLine(dr["Item"].ToString()); } } } } public static DataTable ConvertDataTable(List<Guid>list) { DataTable table = new DataTable(); table.Columns.Add("Item", typeof(Guid)); foreach (var guid in list) { table.Rows.Add(guid); } return table; }

    You could refer to the links below for more information:

    https://www.aspsnippets.com/Articles/Send-Pass-DataTable-as-parameter-to-Stored-Procedure-in-C-and-VBNet.aspx

    https://stackoverflow.com/questions/7097079/c-sharp-sql-server-passing-a-list-to-a-stored-procedure

    Best Regards,

    Billy

    Wednesday, November 29, 2017 6:53 AM