CODEWITHSUNDEEP
c#arrayssql-serverjsonforeach
AcePL
AcePL

Reputation: 560

How to loop through list of complex type objects and pass to a method calling SQL Stored procedure

How to loop through a list of objects in order to pass said object to a method inserting rows in SQL db via stored procedure?

With help in this question I got to this point:

namespace NA.Controllers
{
   public class NC : ApiController
   {
      [Route("AddNote")]
      [HttpPost]
    public HttpResponseMessage PostNote(List<Note> items)
    {
        //NoteJson deserializednote = JsonConvert.DeserializeObject<NoteJson>(item);
        //Note notesdata = new Note(item);
        NotesAccept.Models.INoteRepository Repository = new NotesAccept.Models.NoteDataRepository();
        foreach (Note item in items)
        {

        item = Repository.Add(item);

        }
        var response = Request.CreateResponse<List<Note>>(HttpStatusCode.OK, items);
        return response;
    }
   }
}

but now I'm stuck as item= is now an iteration variable, but I need to pass it to an method:

namespace NA.Models
{
  class NoteDataRepository : INoteRepository
  {
     public void Add(Note item)
     {
        if (item == null)
        {
            throw new ArgumentNullException("item");
        }
        else
        {
            String strConnString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
            SqlConnection con = new SqlConnection(strConnString);
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "BL_IntegrationInsertNote";
            cmd.Parameters.Add("@Client", SqlDbType.VarChar).Value = item.Client.Trim();
            cmd.Parameters.Add("@Case", SqlDbType.VarChar).Value = item.Case;
            cmd.Parameters.Add("@Text", SqlDbType.VarChar).Value = item.Text.Trim();
            cmd.Parameters.Add("@When", SqlDbType.DateTime).Value = item.Date;
            cmd.Parameters.Add("@Ext", SqlDbType.Bit).Value = item.Type;
            cmd.Parameters.Add("@return", SqlDbType.Int).Direction = ParameterDirection.Output;
            cmd.Connection = con;

            try
            {
                con.Open();
                cmd.ExecuteNonQuery();
                string id = cmd.Parameters["@return"].Value.ToString();
                string lblMessage = null;
                lblMessage = "Record inserted successfully. ID = " + id;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
                con.Dispose();
            }
        }    
        //return item;
    }

    IEnumerable<Note> INoteRepository.GetAll()
    {
        throw new NotImplementedException("getitems");
    }
}
}

I'm still green-as-a-grass-newbie to C# so I've no idea how to accomplish that, especially since whole solution is still "copy&Paste" from all over the web, and whole web curiously concentrates on looping through simple types. How to do that with complex type?

As noted in other question, this is a matter of professional life and death (I'm a db dev, not a VS guru, especially not after two days and two nights).

Upvotes: 1

Views: 1024

Answers (3)

Wapac
Wapac

Reputation: 4178

  1. You are still forgetting to assign that ID from DB to the item.

  2. You also still have 

    return item;

    in a method that does not return anything (public void Add(Note item)).

    So just delete that return line.

  3. And replace

    item = Repository.Add(item);

    with just

    Repository.Add(item);

Upvotes: 2

Vinay Pandey
Vinay Pandey

Reputation: 8923

You can pass it as xml and iterate in sql stored procedure and do bulk insert, or you can use table datatype if sql and .net version you are using supports it.

Upvotes: 2

Kevin
Kevin

Reputation: 1472

Try this in the foreach loop:

var tempItem = item;
tempItem = Repository.Add(tempItem);

Upvotes: 1

Related Questions