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]

Nick H · Accepted Answer · 2020-02-10T12:59:21.310

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))

edited Feb 10 '20 at 12:59

answered Feb 10 '20 at 12:54

Nick H

464
1
5
12

tempid is not primary key.I just want to insert like the image.Could you correct me? – codeseeker Feb 10 '20 at 12:59
Try this as your proc. You'll need to update your calls data table to match the MyDataTable column names. `CREATE TYPE dbo.MyDataTable AS TABLE (gods NVARCHAR(200)); GO ALTER PROC dbo.AssignGod @dt AS dbo.MyDataTable READONLY, @tempId INT AS BEGIN SET NOCOUNT ON; BEGIN INSERT dbo.TempleGod (templeid, gid) VALUES ( @tempId, (SELECT gods FROM @dt WHERE gods NOT IN ( SELECT gods FROM dbo.TempleGod )) ); END END` – Nick H Feb 10 '20 at 13:19
Tried.But not working.i have changed datatable column with gods is that correct now? – codeseeker Feb 10 '20 at 13:34
Changed a little – codeseeker Feb 10 '20 at 14:00
I wouldn't name the type the same as the table. Test code for proc below and it works Are you certain the array has values in your c#? ` DECLARE @t TempleGod INSERT INTO TempleGod (gods, tid) VALUES ( N'test', -- gods - nvarchar(200) 1 -- tid - int ) EXEC [AssignGod] @t ` – Nick H Feb 10 '20 at 14:13
Solved.The problem was the column data type was string and i was trying to insert int.I have changed by converting string array into Int.Now it works well – codeseeker Feb 10 '20 at 14:20

score -1 · Answer 2 · answered Feb 10 '20 at 12:35

-1

Try changing your insert statement to this:

INSERT dbo.TempleGod(templeid,gid)   
SELECT @tempId, gods   FROM @dt   WHERE gods NOT IN (SELECT gods FROM dbo.TempleGod))

answered Feb 10 '20 at 12:35

Ezra

529
4
5

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

2 Answers2