Pass GUID into stored procedure method C#

Question

I am mostly new to c# so i am looking for some guidance here. I am testing a method that i need to pass a list of guids to and run a stored procedure that returns values based on the guids i pass to it which i can then print to the console. I can get the method to work when i pass only one guid but when i pass a list of guids it seems to not work.

I feel like i am lacking some understanding here around how i should pass the list of guids and return it. I get conversion errors trying to return List.

Here is how far i have got but i feel like i am stuck now and cant progress anymore from any info i have found online.

class Program
{
    static void Main(string[] args)
    {
        List<Guid> tempguid = new List<Guid>();
        tempguid.Add(Guid.Parse("472USFA0-B705-9A73-ABD4-3B1870AF1409"));
        tempguid.Add(Guid.Parse("FA97E6BB-0875-5UB9-967A-87ECC396F9F0"));

        GetValue(tempguid);
        Console.WriteLine(GetValue);
    }

    public void GetValue(List<Guid> tempguid)
    {
        using (SqlConnection conn = new SqlConnection("connection string here"))
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand("stored procedure here", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@id", tempguid));

            using (SqlDataReader rdr = cmd.ExecuteReader())
            {
                while (rdr.Read())
                {
                    Console.WriteLine((string)rdr["value"]);
                }
                Console.ReadLine();
            }
        }
    }
}

Should i be passing the list like this GetValue(List tempguid)?

EDIT

ok so if i use a TVP.

Something like:

CREATE TYPE [dbo].[Identity] AS TABLE(

[Id] [uniqueidentifier] NOT NULL
)
GO

Then my procedure will look something along the lines of:

CREATE OR ALTER PROCEDURE [dbo].[procedure_name]
@id dbo.Identity READONLY
as
SELECT t.[id]
      ,t.[value]
  FROM [dbo].[table1] t
  Inner Join @id i on i.Id = t.id

How do i use this TVP in c# for my stored procedure?

Answer 1

you need a foreach loop on GUID list. Try like:

foreach (var g in tempguid)
{
    cmd.Parameters.Add(new SqlParameter("@id", g));
    using (SqlDataReader rdr = cmd.ExecuteReader())
    {
       while (rdr.Read())
       {
          Console.WriteLine((string)rdr["value"]);
       }
       Console.ReadLine();
    }
    cmd.Parameteres.Clear();
}

Answer 2

You can't pass a list to sp. You need to convert your guids into a csv string example:

var param = string.Join(", ", tempguid);

Then

cmd.Parameters.Add(new SqlParameter("@id", param));

Then after receiving your parameter on the sp pass into String_Split. Goodluck!

Answer 3

Passing values to TVPs from ADO .NET is very straightforward, and requires very little extra code compared to passing data to regular parameters.

• For the data type you specify SqlDbType.Structured.
• You specify the name of the table type in the TypeName property of the parameter.
• You set the Value property of the parameter to something suitable.

As mentioned in the above link System.Data.SqlClient supports populating table-valued parameters from DataTable, DbDataReader or IEnumerable<T> \ SqlDataRecord objects.

If you already have the list of Guids coming from other sources in your case tempguid then you could use a datatable to pass the details to stored procedure.

DataTable tvp = new DataTable();
tvp.Columns.Add(new DataColumn("Id", typeof(Guid)));

// populate DataTable from your List here
foreach (var id in tempguid)
   tvp.Rows.Add(id);

And change the ADO.NET code like below -

conn.Open();
SqlCommand cmd = new SqlCommand("dbo.tvpProcedure", conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter tvpParameter = new SqlParameter();
tvpParameter.ParameterName = "@id";
tvpParameter.SqlDbType = System.Data.SqlDbType.Structured;
tvpParameter.Value = tvp;
tvpParameter.TypeName = "dbo.testTVP";
cmd.Parameters.Add(tvpParameter);

 using (SqlDataReader rdr = cmd.ExecuteReader())
   {
     while (rdr.Read())
      {
        Console.WriteLine((string)rdr["value"]);
      }
       Console.ReadLine();
    }

Side notes:

• Looks like the GUIDs you have shown in the code seems invalid as they contain non- hexadecimal values.

  472USFA0-B705-9A73-ABD4-3B1870AF1409
     ^^
  FA97E6BB-0875-5UB9-967A-87ECC396F9F0
                 ^
  

• Change the Type name something meaningful instead of dbo.Identity in my case I used dbo.testTVP

Further information -

http://www.sommarskog.se/arrays-in-sql-2008.html