In this tutorial, we will create an Stored Procedure in SQL Server and execute it in SQL server and in C# Windows Form.
Step1: For Stored Procedure sample, we will use the Northwind database.
Step2: Create SP as following the code and Execute SP.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | USE Northwind GO CREATE PROC insertCustomer @cCustomerId nchar(5), @cCompanyName nvarchar(40), @cContactName nvarchar(30), @cContactTitle nvarchar(30), @cAdress nvarchar(60), @cCity nvarchar(15), @cRegion nvarchar(15), @cPostalCode nvarchar(10), @cCountry nvarchar(15), @cPhone nvarchar(24), @cFax nvarchar(24) AS INSERT INTO Customers VALUES (@cCustomerId,@cCompanyName,@cContactName,@cContactTitle, @cAdress,@cCity,@cRegion,@cPostalCode,@cCountry,@cPhone,@cFax) |
Step3: Write C# Code
Source Code:
1 2 3 | using System.Data.SqlClient; |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | private void button1_Click(object sender, EventArgs e) { SqlConnection con = new SqlConnection("server=.; Initial Catalog=Northwind;Integrated Security=SSPI"); SqlCommand cmd = new SqlCommand(); con.Open(); cmd.Connection = con; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "insertCustomer"; cmd.Parameters.Add("cCustomerId", SqlDbType.NChar, 5).Value = textBox1.Text; cmd.Parameters.Add("cCompanyName", SqlDbType.NVarChar, 40).Value = textBox2.Text; cmd.Parameters.Add("cContactName", SqlDbType.NVarChar, 30).Value = textBox3.Text; cmd.Parameters.Add("cContactTitle", SqlDbType.NVarChar, 30).Value = textBox4.Text; cmd.Parameters.Add("cAdress", SqlDbType.NVarChar, 60).Value = textBox5.Text; cmd.Parameters.Add("cCity", SqlDbType.NVarChar, 15).Value = textBox6.Text; cmd.Parameters.Add("cRegion", SqlDbType.NVarChar, 15).Value = textBox7.Text; cmd.Parameters.Add("cPostalCode", SqlDbType.NVarChar, 10).Value = textBox8.Text; cmd.Parameters.Add("cCountry", SqlDbType.NVarChar, 15).Value = textBox9.Text; cmd.Parameters.Add("cPhone", SqlDbType.NVarChar, 24).Value = textBox10.Text; cmd.Parameters.Add("cFax", SqlDbType.NVarChar, 24).Value = textBox11.Text; cmd.ExecuteNonQuery(); con.Close(); } |
Output:
