Using Stored Procedure to Insert parameters from DataTable

Question

I am trying to insert data using a stored procedure that has two tables. This first table is data is through text boxes the second data is through a grid which I stored in the database and passed to be inserted. The problem is when reading datatable and inserting it says there are too many parameter which happens to add in the for loop. Any suggestion how to handle this as the SP? Thanks in advance.

CODE:

try
{
  SqlConnection conn = new SqlConnection();
  conn.ConnectionString = strConnection;
  SqlCommand cmd = new SqlCommand();
  cmd.Connection = conn;
  cmd.CommandTimeout = 120;
  cmd.CommandType = CommandType.StoredProcedure;
  cmd.CommandText = "insFamilyDetails";
  cmd.Parameters.AddWithValue("@strHusbandName", strHusbandName);
  cmd.Parameters.AddWithValue("@strRelation", strRelation);
  ....
  ....
   // Child Details
  for (int i = 0; i < strChildredDetails.Rows.Count; i++)
  {
    cmd.Parameters.AddWithValue("@strChildName", strChildredDetails.Rows[i][0].ToString());
    cmd.Parameters.AddWithValue("@strDOB", strChildredDetails.Rows[i][1]);
    cmd.Parameters.AddWithValue("@strBaptisedon", strChildredDetails.Rows[i][2]);
    cmd.Parameters.AddWithValue("@strFirstComOn", strChildredDetails.Rows[i][3]);
    cmd.Parameters.AddWithValue("@strConfirmedOn", strChildredDetails.Rows[i][4]);
    cmd.Parameters.AddWithValue("@strMarried", "0");
    cmd.Parameters.AddWithValue("@strAlive", "1");
  }
  conn.Open();
  ReturnValue = Convert.ToBoolean(cmd.ExecuteNonQuery());
  conn.Close();
}
catch (Exception e)
{
 DL_LogAppErrors(e.ToString(), System.Reflection.MethodBase.GetCurrentMethod().Name, "Insert Family Details");
 return ReturnValue;
}

return ReturnValue;

Ruly · Accepted Answer · 2013-04-01 07:49:27Z

1

I assume from the code you're going to add into a main table, and Child table. For this case, you need to separate the process into two:

Add the data for in main table
Loop to add the child data
Note: you need to clear the parameters before adding a new set, OR instead of adding new parameters, change the value of existing parameters

EDIT: Using Transaction

con.Open();
SqlTransaction trans = con.BeginTransaction();

try {
    // Execute the SP here
    // After all SP executed, call the commit method
    trans.Commit();
} catch (Exception ex) {
    // An error happened, rollback
    trans.RollBack();
}
con.Close();

edited Apr 1, 2013 at 7:49

answered Apr 1, 2013 at 7:03

Ruly

3701 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Adrian Over a year ago

I tried that way, it works but what I found is say if the Main table data is inserted and the child data fails then there is an issue. So in the SP I added a Transaction and Roll back if either of them fails

Ruly Over a year ago

So are you saying that you create one SP to enter data into both table (main and child)?

Adrian Over a year ago

Yes. There are two insert statement in a single SP. If the main fails it will not insert into the child and if the child fails too the transaction is rolled back. Not sure if this is the correct method, any suggestions?

Ruly Over a year ago

That means if you have 4 child, you need to execute the SP 4 times, and each SP will insert a data into the main table. What I usually do is create two SP, one for main table and one for child, and in coding, create a transaction and if some error happened, rollback.

Adrian Over a year ago

@Rudy should I put both the SP in the try part, is that what you mean?

|

Community · Accepted Answer · 2017-05-23 12:21:07Z

1

You are adding parameters in command in each iteration of the loop. After first iteration you are trying to add same parameter name in parameter collection. You probably need to clear the collection of parameter on each iteration using SqlParameterCollection.Clear. Clear the parameter collection after executing command (In loop body).

conn.Open();
for (int i = 0; i < strChildredDetails.Rows.Count; i++)
{
    cmd.Parameters.AddWithValue("@strChildName", strChildredDetails.Rows[i][0].ToString());
    cmd.Parameters.AddWithValue("@strDOB", strChildredDetails.Rows[i][2]);
    cmd.Parameters.AddWithValue("@strBaptisedon", strChildredDetails.Rows[i][2]);
    cmd.Parameters.AddWithValue("@strFirstComOn", strChildredDetails.Rows[i][3]);
    cmd.Parameters.AddWithValue("@strConfirmedOn", strChildredDetails.Rows[i][4]);
    cmd.Parameters.AddWithValue("@strMarried", "0");
    cmd.Parameters.AddWithValue("@strAlive", "1");
    ReturnValue = Convert.ToBoolean(cmd.ExecuteNonQuery());
    cmd.Parameters.Clear();
}
conn.Close();

If you have many records to insert in a table then you can send the comma separated values in SP and split then in SP and insert them. It will save db calls. This post will show how you can do that.

edited May 23, 2017 at 12:21

CommunityBot

11 silver badge

answered Apr 1, 2013 at 6:55

Adil

148k25 gold badges217 silver badges207 bronze badges

2 Comments

Adrian Over a year ago

There are two tables 1 parent and 2 the child. 1 parent many children - Children is got from a grid. I need to get the parent id after it is inserted and use that ID to insert into the child table. Not sure if this the right way I am doing it

Adil Over a year ago

If you have many records to insert in a table then you can send the comma separated values in SP and split then in SP and insert them. It will save db calls.

Arun · Accepted Answer · 2013-04-01 06:59:40Z

0

For each row you want to insert you have to call the ExecuteNonQuery() function ie, it should be inside the for loop and after that clear the parameter collection at the end of loop.

  conn.Open();
// Child Details
  for (int i = 0; i < strChildredDetails.Rows.Count; i++)
  {
   cmd.Parameters.AddWithValue("@strHusbandName", strHusbandName);
   cmd.Parameters.AddWithValue("@strRelation", strRelation);
  ....
  ....
    cmd.Parameters.AddWithValue("@strChildName", strChildredDetails.Rows[i][0].ToString());
    cmd.Parameters.AddWithValue("@strDOB", strChildredDetails.Rows[i][1]);
    cmd.Parameters.AddWithValue("@strBaptisedon", strChildredDetails.Rows[i][2]);
    cmd.Parameters.AddWithValue("@strFirstComOn", strChildredDetails.Rows[i][3]);
    cmd.Parameters.AddWithValue("@strConfirmedOn", strChildredDetails.Rows[i][4]);
    cmd.Parameters.AddWithValue("@strMarried", "0");
    cmd.Parameters.AddWithValue("@strAlive", "1");
  ReturnValue = Convert.ToBoolean(cmd.ExecuteNonQuery());
cmd.Parameters.Clear();

}

answered Apr 1, 2013 at 6:59

Arun

9787 silver badges17 bronze badges

2 Comments

Adrian Over a year ago

it inserts two records in the main table and two record in the child table. 1 Parent and 2 children.

Arun Over a year ago

Your stored procedure needs to change, You can change it to use User defined type as input parameter, Create a table type and send the data otherwise use separate SPs for Main table data and Child table data..

Nilesh Thakkar · Accepted Answer · 2013-04-01 07:30:38Z

As already said, you need to have ExecuteNonQuery inside for each loop, if you want to insert records of your grid.

Alternate option would be to Use Table Valued Paramter if you're using SQL Server 2008, that would make life more easy and you don't have to make round trip for each record of your gridview. Just pass the datatable.

Please check this link.

Edit:

For SQL Server 2005, you might want to use XML. Please check this link.

public string SerializeObject<T>(T Obj)
{
string strxml = string.Empty;
using (StringWriter sw = new StringWriter())
{
    XmlSerializer xs = new XmlSerializer(typeof(T));
    xs.Serialize(sw, Obj);
    strxml = sw.ToString();
}
return strxml;
}

Link contains above function, pass your datatable to this function, check out the generated XML and use same casing in stored procedure for elements in XML as XML is case sensitive.

Collectives™ on Stack Overflow

Using Stored Procedure to Insert parameters from DataTable

4 Answers 4

6 Comments

2 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related