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;
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;
.AddWithValue() is a bit dangerous - since you're not specifying the datatype of the parameter, ADO.NET must guess from your value what you want. It's doing a good job at guessing - but it's still just guessing, and sometimes this guess can be horribly off. So I would always recommend to specify the SqlDbType for your SqlParameter and thus not use .AddWithValue() ....
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);
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 .....
You may use like it
return new SqlParameter[]
{
new SqlParameter("@Firstname", SqlDbType.VarChar)
{
Value = Firstname.Text
},
new SqlParameter("@Lastname", SqlDbType.VarChar)
{
Value = Lastname.Text
},
};
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"
// 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;
The command.Parameters.Add is deprecated. Rather use command.Parameters.AddWithValue .
For this, you would call it many times for each parameter.
