How to Pass string array to Stored procedure and perform insert query

Question

Have seen lot of example for passing array values to stored procedure using table valued parameter and i followed the same but the values are not get inserted inside the table. Here i have to sent a string array having some ids and a int value. i need to insert complete array values with this unique id.

Controller

 [HttpPost]
        public  JsonResult AssignGod(int id,string[] g)
        {
            SqlConnection con = null;              
            try
            {
                con = GetSqlConnectionObject(cs);             


            DataTable dt_god = new DataTable();
            dt_god.Columns.Add("gods", typeof(String));
            dt_god.Columns.Add("tid", typeof(Int32));
            DataRow workRow;
            foreach (string cat in g)
            {
                workRow = dt_god.NewRow();
                workRow["gods"] = cat.Trim();
                workRow["tid"] = id;
                dt_god.Rows.Add(workRow);
            }
            SqlCommand cmd = new SqlCommand("AssignGod", con);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter tvparam = cmd.Parameters.AddWithValue("@templegod", dt_god);
            tvparam.SqlDbType = SqlDbType.Structured;
            cmd.ExecuteNonQuery();

                string result = "success";  
                return Json(result, JsonRequestBehavior.AllowGet);
            }

            catch
            {
                throw;
            }
            finally
            {
                CloseSqlConnectionObject(con);
            }
            return Json("asa", JsonRequestBehavior.AllowGet);
        }

Stored Procedure

CREATE TYPE dbo.TempleGod AS TABLE
 (gods NVARCHAR(200),tid int);
go




ALTER  PROC [dbo].[AssignGod]
                    @templegod AS [dbo].TempleGod READONLY


AS
BEGIN
        SET NOCOUNT ON;

    BEGIN

            BEGIN
            INSERT dbo.TempleGod(templeid,gid) SELECT tid,gods FROM @templegod
            END

    END

While am running this code its not showing any error but the table remains empty
[![Table must be like this][1]][1]

Answer 1

Your datatable column names need to match the type declaration in your proc. You've got 'Category' and then 'gods'
I think you've got a problem in that insert clause as well. In any case, not sure what you're doing, but if that id is a primary key, you're inserting the same one into every row. You may want to move it into the type definition.

I'd also uses the suggested answer: INSERT dbo.TempleGod(templeid,gid)
SELECT @tempId, gods FROM @dt WHERE gods NOT IN (SELECT gods FROM dbo.TempleGod))