468,505 Members | 1,902 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,505 developers. It's quick & easy.

Dynamic parametized sql insert - is this a good idea?

I wanted to build an insert sql statement with an unknown number of values. Would this be better as a stored procedure? Is it possible to do this as a stored procedure?

Expand|Select|Wrap|Line Numbers
  1. public static string sqlInsert(string table, ArrayList values, int numValues)
  2. {
  3.     string result = String.empty;
  4.     string sqlValues = String.Empty;
  5.     for (int i = 1; i <= numValues; i++)
  6.     {
  7.         if (i < numValues)
  8.         {
  9.             sqlValues += "@val" + i + ",";
  10.         }
  11.         else
  12.         {
  13.             sqlValues += "@val" + i;
  14.         }
  15.     }
  16.     string sql = "insert into dbo." + table + " values (" + sqlValues + ")";
  17.  
  18.     SqlConnection conSql = new SqlConnection(ConnStr);            
  19.     SqlCommand sc = new SqlCommand(sql, conSql);
  20.  
  21.     int j = 1;
  22.     foreach (string value in values)
  23.     {
  24.         if (String.IsNullOrEmpty(value))
  25.         {
  26.             sc.Parameters.AddWithValue("@val" + j, DBNull.Value);
  27.         }
  28.         else
  29.         {
  30.             sc.Parameters.AddWithValue("@val" + j, value);
  31.         }
  32.         j++;
  33.     }
  34.  
  35.     try
  36.     {
  37.         sc.Connection.Open();
  38.         sc.ExecuteNonQuery();
  39.     }
  40.     catch (Exception ex)
  41.     {
  42.         result = ex.Message;
  43.     }
  44.     finally
  45.     {
  46.         sc.Connection.Close();
  47.     }
  48.     return result;
  49. }
Jan 25 '10 #1
0 1108

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Shailesh | last post: by
3 posts views Thread by Mukesh | last post: by
9 posts views Thread by pbd22 | last post: by
reply views Thread by NPC403 | last post: by
3 posts views Thread by gieforce | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.