Call a stored procedure with parameter in c#
C#asp.netSql ServerStored Proceduresado.netC# Problem Overview
I'm able to delete, insert and update in my program and I try to do an insert by calling a created stored procedure from my database.
This button insert I made works well.
private void btnAdd_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(dc.Con);
SqlCommand cmd = new SqlCommand("Command String", con);
da.InsertCommand = new SqlCommand("INSERT INTO tblContacts VALUES (@FirstName, @LastName)", con);
da.InsertCommand.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = txtFirstName.Text;
da.InsertCommand.Parameters.Add("@LastName", SqlDbType.VarChar).Value = txtLastName.Text;
con.Open();
da.InsertCommand.ExecuteNonQuery();
con.Close();
dt.Clear();
da.Fill(dt);
}
This is the start of the button that calls the procedure named sp_Add_contact
to add a contact. The two parameters for sp_Add_contact(@FirstName,@LastName)
. I searched on google for some good examples but found nothing interesting.
private void button1_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(dc.Con);
SqlCommand cmd = new SqlCommand("Command String", con);
cmd.CommandType = CommandType.StoredProcedure;
???
con.Open();
da. ???.ExecuteNonQuery();
con.Close();
dt.Clear();
da.Fill(dt);
}
C# Solutions
Solution 1 - C#
It's pretty much the same as running a query. In your original code you are creating a command object, putting it in the cmd
variable, and never use it. Here, however, you will use that instead of da.InsertCommand
.
Also, use a using
for all disposable objects, so that you are sure that they are disposed properly:
private void button1_Click(object sender, EventArgs e) {
using (SqlConnection con = new SqlConnection(dc.Con)) {
using (SqlCommand cmd = new SqlCommand("sp_Add_contact", con)) {
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = txtFirstName.Text;
cmd.Parameters.Add("@LastName", SqlDbType.VarChar).Value = txtLastName.Text;
con.Open();
cmd.ExecuteNonQuery();
}
}
}
Solution 2 - C#
You have to add parameters since it is needed for the SP to execute
using (SqlConnection con = new SqlConnection(dc.Con))
{
using (SqlCommand cmd = new SqlCommand("SP_ADD", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@FirstName", txtfirstname.Text);
cmd.Parameters.AddWithValue("@LastName", txtlastname.Text);
con.Open();
cmd.ExecuteNonQuery();
}
}
Solution 3 - C#
cmd.Parameters.Add(String parameterName, Object value)
is deprecated now. Instead use cmd.Parameters.AddWithValue(String parameterName, Object value)
> There is no difference in terms of functionality. The reason they
> deprecated the cmd.Parameters.Add(String parameterName, Object value)
in favor of AddWithValue(String parameterName, Object value)
is to give more
> clarity. Here is the MSDN reference for the same
private void button1_Click(object sender, EventArgs e) {
using (SqlConnection con = new SqlConnection(dc.Con)) {
using (SqlCommand cmd = new SqlCommand("sp_Add_contact", con)) {
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@FirstName", SqlDbType.VarChar).Value = txtFirstName.Text;
cmd.Parameters.AddWithValue("@LastName", SqlDbType.VarChar).Value = txtLastName.Text;
con.Open();
cmd.ExecuteNonQuery();
}
}
}
Solution 4 - C#
As an alternative, I have a library that makes it easy to work with procs: https://www.nuget.org/packages/SprocMapper/
SqlServerAccess sqlAccess = new SqlServerAccess("your connection string");
sqlAccess.Procedure()
.AddSqlParameter("@FirstName", SqlDbType.VarChar, txtFirstName.Text)
.AddSqlParameter("@FirstName", SqlDbType.VarChar, txtLastName.Text)
.ExecuteNonQuery("StoredProcedureName");
Solution 5 - C#
public void myfunction(){
try
{
sqlcon.Open();
SqlCommand cmd = new SqlCommand("sp_laba", sqlcon);
cmd.CommandType = CommandType.StoredProcedure;
cmd.ExecuteNonQuery();
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
sqlcon.Close();
}
}
Solution 6 - C#
The .NET Data Providers consist of a number of classes used to connect to a data source, execute commands, and return recordsets. The Command Object in ADO.NET provides a number of Execute methods that can be used to perform the SQL queries in a variety of fashions.
A stored procedure is a pre-compiled executable object that contains one or more SQL statements. In many cases stored procedures accept input parameters and return multiple values . Parameter values can be supplied if a stored procedure is written to accept them. A sample stored procedure with accepting input parameter is given below :
CREATE PROCEDURE SPCOUNTRY
@COUNTRY VARCHAR(20)
AS
SELECT PUB_NAME FROM publishers WHERE COUNTRY = @COUNTRY
GO
The above stored procedure is accepting a country name (@COUNTRY VARCHAR(20)) as parameter and return all the publishers from the input country. Once the CommandType is set to StoredProcedure, you can use the Parameters collection to define parameters.
command.CommandType = CommandType.StoredProcedure;
param = new SqlParameter("@COUNTRY", "Germany");
param.Direction = ParameterDirection.Input;
param.DbType = DbType.String;
command.Parameters.Add(param);
The above code passing country parameter to the stored procedure from C# application.
using System;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace WindowsFormsApplication1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
string connetionString = null;
SqlConnection connection ;
SqlDataAdapter adapter ;
SqlCommand command = new SqlCommand();
SqlParameter param ;
DataSet ds = new DataSet();
int i = 0;
connetionString = "Data Source=servername;Initial Catalog=PUBS;User ID=sa;Password=yourpassword";
connection = new SqlConnection(connetionString);
connection.Open();
command.Connection = connection;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "SPCOUNTRY";
param = new SqlParameter("@COUNTRY", "Germany");
param.Direction = ParameterDirection.Input;
param.DbType = DbType.String;
command.Parameters.Add(param);
adapter = new SqlDataAdapter(command);
adapter.Fill(ds);
for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
{
MessageBox.Show (ds.Tables[0].Rows[i][0].ToString ());
}
connection.Close();
}
}
}
Solution 7 - C#
Here is my technique I'd like to share. Works well so long as your clr property types are sql equivalent types eg. bool -> bit, long -> bigint, string -> nchar/char/varchar/nvarchar, decimal -> money
public void SaveTransaction(Transaction transaction)
{
using (var con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConString"].ConnectionString))
{
using (var cmd = new SqlCommand("spAddTransaction", con))
{
cmd.CommandType = CommandType.StoredProcedure;
foreach (var prop in transaction.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance))
cmd.Parameters.AddWithValue("@" + prop.Name, prop.GetValue(transaction, null));
con.Open();
cmd.ExecuteNonQuery();
}
}
}