Error in creating a list with SqlParameter as type

Question

To build a dynamic query I am writing some code like this

StringBuilder sql = new StringBuilder();
   sql.Append("SELECT ... all your columns ... FROM yourTable");
   List<SqlParameter> parameters = new List<SqlParameter>(); 
   if (!string.IsNullOrEmpty(paraCategory))
         {
        sql.Append("[Category]=@Category,");
         parameters.AddWithvalue("@Category", paraCategory);
         }
   sql.Length -= 1;
   sql.Append("ORDER BY CreatedDate");
   ds = SqlHelper.ExecuteDataset(GlobalSettings.DbDSN, CommandType.Text, sql.ToString(), parameters);

Now it throws an error like this

Error   1   'System.Collections.Generic.List<System.Data.SqlClient.SqlParameter>' does not contain a definition for 'AddWithvalue' and no extension method 'AddWithvalue' accepting a first argument of type 'System.Collections.Generic.List<System.Data.SqlClient.SqlParameter>' could be found (are you missing a using directive or an assembly reference?)
Error   2   The best overloaded method match for 'Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(string, System.Data.CommandType, string, params System.Data.SqlClient.SqlParameter[])' has some invalid arguments   
Error   3   Argument 4: cannot convert from 'System.Collections.Generic.List<System.Data.SqlClient.SqlParameter>' to 'System.Data.SqlClient.SqlParameter[]'

I am not sure what I need to do now..please lend a hand/

Habib · Accepted Answer · 2013-07-26 12:41:06Z

2

Error 1:

You need to add parameters in your list like:

parameters.Add(new SqlParameter("@Category", paraCategory));

Error 2 and 3

It appears that your method SqlHelper.ExecuteDataset takes an array as an input for SqlParameters, currently you are passing it as a List, just add .ToArray() at the end.

ds = SqlHelper.ExecuteDataset(
                             GlobalSettings.DbDSN, 
                             CommandType.Text, 
                             sql.ToString(), 
                             parameters.ToArray()); //right here

answered Jul 26, 2013 at 12:41

Habib

224k30 gold badges420 silver badges446 bronze badges

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

Comments

Adriaan Stander · Accepted Answer · 2013-07-26 12:41:54Z

1

Change the line

parameters.AddWithvalue("@Category", paraCategory);

to something like

parameters.Add( new SqlParameter("@Category", paraCategory));

answered Jul 26, 2013 at 12:41

Adriaan Stander

167k32 gold badges294 silver badges288 bronze badges

Comments

Remus Rusanu · Accepted Answer · 2013-07-26 12:43:25Z

1

AddWithValue is a method of the SqlParameterCollection. No wonder the compiler complains about using it on a List<SqlParameter>.

That being said, be aware that using the out-of-the-box AddWithValue is a huge anti-pattern. It results in very serious performance problems in the SQL Server engine. See How Data Access Code Affects Database Performance for details. for the record, using new SqlParameter("@name", value) suffers from exactly the same problems. Read the article.

answered Jul 26, 2013 at 12:43

Remus Rusanu

296k42 gold badges459 silver badges583 bronze badges

Collectives™ on Stack Overflow

Error in creating a list with SqlParameter as type

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related