How can I take each array item and insert in a SQL database?

Question

I have the following vb.net code to take the values out of a textbox on a webpage (actually space delimited tags) and split them with a space delimiter into an array. This works exactly how I want.

mySample.Tags = tagsTextBox.Text
Dim tags As String = mySample.Tags
Dim tagarray() As String
Dim count As Integer
tagarray = tags.Split(" ")
For count = 0 To tagarray.Length - 1
Next

My issue is that I don't know how to take each of the values in the array, after this code runs, to insert them as separate records in a table.

I also will not know how many items will be in the array.

Answer 1

As Ian said this may be vurnerable for Sql injections. At the very least you should do a Server.HtmlEncode() for each tag you want to insert.

To insert your data you could do the following:

using (SqlConncetion conn = new SqlConnection(connstring))
using (SqlCommand cmd = conn.CreateCommand())
{

  cmd.CommandText = "INSERT INTO table(tag) values (@tag)";
  cmd.Parameters.Add("@tag", SqlDbType.VarChar);

  conn.Open();

  foreach(string tag in tags)
  {
    cmd.Parameters["@tag"].Value = Server.HtmlEncode(tag);
    cmd.ExecuteNonQuery();
  }
}

This should work properly, but doing it in a stored procedure and you should be safe against sql injections since you use parameters.

Also you should see here for a discussion around the use of parameters.

Answer 2

It all depends on the performance requirements and the general practices you use. Rune's answer can be perfectly fine. If you are inserting 100,000 rows look at a bulk inserter.

If you are used to writing stored procs and you are lucky enough to be running SQL 2008 you can make use of table valued params

This allows you to do stuff like this:

SqlCommand cmd = new SqlCommand("usp_ins_Portfolio", conn);
cmd.CommandType = CommandType.StoredProcedure;
//add the ds here as a tvp
SqlParameter sp = cmd.Parameters.AddWithValue("@Portfolio", ds.Tables[0]);
//notice structured
sp.SqlDbType = SqlDbType.Structured;
cmd.ExecuteNonQuery();

Then a single call to a stored proc can insert all the rows required into the Tag table.

For SQL 2005 and below I usually will use a single comma separated param for all the values, and split it in TSQL inside a stored proc. This tends to perform quite well and avoids mucking around with temp tables. It is also secure, but you have to ensure you use a text input param for the proc or have some sort of limit or batching mechanism in code (so you do not truncate long lists).

For ideas on how to split up lists in TSQL have a look at Erland's excellent article.

Sql 2000 version of the article is here.