CODEWITHSUNDEEP
c#sql
user1435482
user1435482

Reputation: 117

Adding more number of parameters to sqlparameter class

I have to call a stored procedure but i am having more number of parameters is there any simple way to do this? or simply adding every parameter to sqlparameter class?? like below

 SqlCommand command = new SqlCommand("inserting", con);
 command.CommandType = CommandType.StoredProcedure;
 command.Parameters.Add(new SqlParameter("@Firstname", SqlDbType.NVarChar).Value = TextBox1.Text;

Upvotes: 2

Views: 38906

Answers (7)

Caner
Caner

Reputation: 873

You may use like it

return new SqlParameter[]
            {
                new SqlParameter("@Firstname", SqlDbType.VarChar)
                {
                    Value = Firstname.Text
                },
                new SqlParameter("@Lastname", SqlDbType.VarChar)
                {
                    Value = Lastname.Text
                },
            };

Upvotes: 1

Sachin Verma
Sachin Verma

Reputation: 493

  // Mention size of the nvarchar column   , here i give 500 , you can use its length for @Firstname as you mention in database according to your database 

   SqlCommand command = new SqlCommand("inserting", con);
 command.CommandType = CommandType.StoredProcedure;
 command.Parameters.Add(new SqlParameter("@Firstname", SqlDbType.NVarChar,500).Value = TextBox1.Text;

Upvotes: 0

Chagbert
Chagbert

Reputation: 758

The command.Parameters.Add is deprecated. Rather use command.Parameters.AddWithValue . For this, you would call it many times for each parameter.

Upvotes: -1

Jon Senchyna
Jon Senchyna

Reputation: 8047

Be aware that Paramters.Add has an overload that takes in a string and a DbType, so you don't have to call the Parameter constructor. You could replace the line you are currently using to add a new parameter:

command.Parameters.Add(new SqlParameter("@Firstname", SqlDbType.NVarChar)).Value = TextBox1.Text;

with the following shorter (but functionally equivalent) line:

command.Parameters.Add("@Firstname", SqlDbType.NVarChar).Value = TextBox1.Text;

If you want to add more parameters, you would simply add them to the Parameters property of your command, like so:

SqlCommand command = new SqlCommand("inserting", con);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@Firstname", SqlDbType.NVarChar).Value = TextBox1.Text;
command.Parameters.Add("@Lastname", SqlDbType.NVarChar).Value = TextBox2.Text;

Aside from that, have you tried using Parameters.AddWithValue? You can use this if the data type of your column maps to the type of your value in C#. You can find a mapping of C# to SQL Server data typse here.

You would use it like so:

// Assume your sproc has a parameter named @Age that is a SqlInt32 type
int age = 5;
// Since age is a C# int (Int32), AddWithValue will automatically set
// the DbType of our new paramter to SqlInt32.
command.Parameters.AddWithValue("@Age", 5);

If you need to specify the SqlDbType, AddWithValue returns the parameter you just added, so it's as simple as adding an extra statement to set the DbType property at the end, although at this point, you're better off just using the original .Add function and setting the value.

command.Parameters.AddWithValue("@Firstname", TextBox1.Text).DbType = SqlDbType.NVarChar;

Upvotes: 8

marc_s
marc_s

Reputation: 754468

Just call the command.Parameters.Add method multiple times:

 SqlCommand command = new SqlCommand("inserting", con);
 command.CommandType = CommandType.StoredProcedure;

 command.Parameters.Add("@Firstname", SqlDbType.NVarChar, 100).Value = TextBox1.Text;
 command.Parameters.Add("@Lastname", SqlDbType.NVarChar, 100).Value = TextBox2.Text;
 command.Parameters.Add("@City", SqlDbType.NVarChar, 100).Value = TextBox3.Text;
 command.Parameters.Add("@ID", SqlDbType.Int).Value = Convert.ToInt32(TextBox4.Text);
 ....... and so on .....

Upvotes: 2

Adeel Ahmed
Adeel Ahmed

Reputation: 215

Use Array of type SqlParameter and insert that into SqlCommand

SqlCommand Comm = new SqlCommand("Command text", new SqlConnection("Connection String");
SqlParameter[] param = {new SqlParameter("@Name","Value"), 
                        new SqlParameter("@Name","Value"),
                        ........
                        };
Comm.Parameters.AddRange(param);

Upvotes: 5

Damith
Damith

Reputation: 63065

You can use dapper-dot-net

sample code: 

var dog = connection.Query<Dog>("select Age = @Age, Id = @Id", new { Age = (int?)null, Id = guid });

Insert example:

connection.Execute(@"insert MyTable(colA, colB) values (@a, @b)",
    new[] { new { a=1, b=1 }, new { a=2, b=2 }, new { a=3, b=3 } }
  ).IsEqualTo(3); // 3 rows inserted: "1,1", "2,2" and "3,3"

Upvotes: 0

Related Questions