Click here to Skip to main content
12,349,554 members (27,749 online)
Click here to Skip to main content
Add your own
alternative version

Stats

239.9K views
5.5K downloads
30 bookmarked
Posted

Insert and retrieve data through stored procedure

, 15 Feb 2007 CPOL
Rate this:
Please Sign up or sign in to vote.
All about stored procedure
Sample image

Introduction

There are several advantages of using stored procedures instead of standard SQL. First, stored procedures allow a lot more flexibility offering capabilities such as conditional logic. Second, because stored procedures are stored within the DBMS, bandwidth and execution time are reduced. This is because a single stored procedure can execute a complex set of SQL statements. Third, SQL Server pre-compiles stored procedures such that they execute optimally. Fourth, client developers are abstracted from complex designs. They would simply need to know the stored procedure's name and the type of data it returns.

Creating Stored Procedure?

Enterprise Manager provides an easy way to create stored procedures. First, select the database to create the stored procedure on. Expand the database node, right-click on "Stored Procedures" and select "New Stored Procedure...". You should see the following:

CREATE PROCEDURE [OWNER].[PROCEDURE NAME] AS

Substitute OWNER with "dbo" (database owner) and PROCEDURE NAME with the name of the procedure. For example:

CREATE PROCEDURE [dbo].[GetProducts] AS

So far, we are telling SQL Server to create a new stored procedure with the name GetProducts. We specify the body of the procedure after the AS clause:

CREATE PROCEDURE [dbo].[GetProducts] AS SELECT ProductID, ProductName FROM Products

Click on the Check Syntax button in order to confirm that the stored procedure is syntactically correct. Please note that the GetProducts example above will work on the Northwind sample database that comes with SQL Server. Modify it as necessary to suite the database you are using.

Now that we have created a stored procedure, we will examine how to call it from within a C# application

Calling Stored Procedure

A very nice aspect of ADO.NET is that it allows the developer to call a stored procedure in almost the exact same way as a standard SQL statement.

1. Create a new C# Windows Application project.

2. From the Toolbox, drag and drop a DataGrid onto the Form. Resize it as necessary.

3. Double-click on the Form to generate the Form_Load event handler. Before entering any code, add "using System.Data.SqlClient" at the top of the file.

Enter the following code:

private void Form1_Load(object sender, System.EventArgs e) {

SqlConnection conn = new SqlConnection("Data
Source=localhost;Database=db_first;Integrated Security=SSPI");
SqlCommand command = new SqlCommand("GetProducts", conn);
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataSet ds = new DataSet();
adapter.Fill(ds, "Products");
this.dg_Data.DataSource = ds;

this.dg_Data.DataMember = "Products";

Inserting Data to Stored Procedure

Using other SQL statements such as INSERT, UPDATE or DELETE follow the same procedure. First, create a stored procedure that may or may not accept parameters, and then call the stored procedure from within the code supply the necessary values if parameters are needed. The following example illustrates how to insert a new user in a users table that has a username and password field.

Sql Server Code

CREATE PROCEDURE [dbo].[InsertUser] (
@Username varchar(50), @Password varchar(50)
)
AS INSERT INTO Users VALUES(@Username, @Password)


C# code

string username = Username.Text // get username from user string //
password = Password.Text // get password from user 
<p>
SqlConnection conn = new SqlConnection"
DataSource=localhost;Database=db_First;Integrated Security=SSPI");
SqlCommand command = new SqlCommand("InsertUser", conn);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@Username", SqlDbType.VarChar).Value = username;
command.Parameters.Add("@Password", SqlDbType.VarChar).Value = password;
conn.Open(); int rows = command.ExecuteNonQuery();
conn.Close();</p>

We check the value of rows to see if it is equal to one. Since our stored procedure only did one insert operation and if it is successful, the ExecuteNonQuery() method should return 1 to indicate the one row that was inserted. For other SQL statements, especially UPDATE and DELETE statements that affect more than one row, the stored procedure will return the number of rows affected by the statement.

Conclusion

Stored procedures offer developers a lot of flexibility with many features not available using standard SQL. ADO.NET allows us to use stored procedures in our applications seamlessly. The combination of these two allows us to create very powerful appliations rapidly.

enjoyyyyyyyyyyyyyyyyyyyyyyyyyy

Sample imageSee my Online Profile

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Tariqaziz
Web Developer
Pakistan Pakistan
Always make your absence felt in such a way that someone misses you but dont let your absense be so long that someone starts learning to live without you.

You may also be interested in...

Comments and Discussions

 
Questionhow to insert data in service based database using stored procedure in c# app Pin
Member 1256268723-Jun-16 3:07
memberMember 1256268723-Jun-16 3:07 
QuestionRegarding DB Pin
Member 1198024313-Sep-15 19:59
memberMember 1198024313-Sep-15 19:59 
Questionnice article Pin
Member 1102782028-May-15 4:37
memberMember 1102782028-May-15 4:37 
QuestionInsertion probelm Pin
master tech15-Aug-14 23:16
membermaster tech15-Aug-14 23:16 
QuestionConfused...!! Pin
sushil shrestha22-May-13 19:01
membersushil shrestha22-May-13 19:01 
AnswerRe: Confused...!! Pin
Tariqaziz26-Jun-13 20:33
memberTariqaziz26-Jun-13 20:33 
GeneralMy vote of 5 Pin
le dang tuan3-Aug-12 17:13
memberle dang tuan3-Aug-12 17:13 
GeneralMy vote of 4 Pin
abhihits1-Oct-11 22:54
memberabhihits1-Oct-11 22:54 
GeneralInsert Data to more than one table with store procedure. Pin
ratulalam15-Mar-11 20:01
memberratulalam15-Mar-11 20:01 
QuestionHOW TO: SQL & C# Pin
Shahriar Nour Khondokar2-Jun-10 23:11
memberShahriar Nour Khondokar2-Jun-10 23:11 
General[My vote of 1] request Pin
Md. Marufuzzaman2-Nov-09 5:53
groupMd. Marufuzzaman2-Nov-09 5:53 
GeneralRetrieving values from SQL databases Pin
NithinL20-Jun-08 2:11
memberNithinL20-Jun-08 2:11 
Generalstored procedures Pin
Mujz.........28-May-07 18:14
memberMujz.........28-May-07 18:14 
GeneralIts not working Pin
Mujz.........24-May-07 19:49
memberMujz.........24-May-07 19:49 
GeneralRe: Its not working Pin
Christian Graus24-May-07 19:52
staffChristian Graus24-May-07 19:52 
GeneralRe: Its not working Pin
Mujz.........24-May-07 20:05
memberMujz.........24-May-07 20:05 
QuestionHow to read Excel Files in C# Pin
Mujz.........24-May-07 19:17
memberMujz.........24-May-07 19:17 
AnswerRe: How to read Excel Files in C# Pin
Tariqaziz24-May-07 19:38
memberTariqaziz24-May-07 19:38 
QuestionDelete Pin
hemalvadgama9-Apr-07 1:05
memberhemalvadgama9-Apr-07 1:05 
GeneralSP for update and delete Pin
dani99el30-Mar-07 4:47
memberdani99el30-Mar-07 4:47 
GeneralRe: SP for update and delete Pin
Tariqaziz1-Apr-07 20:43
memberTariqaziz1-Apr-07 20:43 
GeneralRe: SP for update and delete Pin
Tariqaziz29-May-07 19:11
memberTariqaziz29-May-07 19:11 
GeneralRe: SP for update and delete Pin
dani99el30-May-07 15:37
memberdani99el30-May-07 15:37 
GeneralRe: SP for update and delete Pin
overloaded Name14-Jun-11 1:30
memberoverloaded Name14-Jun-11 1:30 
Generalgood work Pin
Tariqaziz15-Feb-07 21:26
memberTariqaziz15-Feb-07 21:26 
GeneralRe: good work Pin
kryzchek19-Feb-07 7:45
memberkryzchek19-Feb-07 7:45 
GeneralRe: good work Pin
overloaded Name14-Jun-11 1:32
memberoverloaded Name14-Jun-11 1:32 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.160621.1 | Last Updated 16 Feb 2007
Article Copyright 2007 by Tariqaziz
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid