stored procedure in ado.net

asha pawar

8y2.6kLearn iOS Programming

How to make a stored procedure in ado.net and why?????

plz give me any example on stored procedure in ado.net?????

10 Replies

Know the answer? Post it — somebody with the same question will find it here.

Mageshwaran R7y ago
Hi,
string SpName="Storeprocedure";
using(sqlconnection sqlcon=new sqlconnection(connectionstring))
{
sqlcon.open();
sqlcommand sqlcmnd=new sqlcommand(SpName,sqlcon);
sqlcmnd.commandtype=commandtype.storeprocedure;
sqlcmnd.parameters.Addwithvalue("@name",sqldbtype.nvarchar).value=txtname.text;
sqlcmnd.Executenonquery();
sqlCon.close();

}
+2
Code Alone8y ago
Hi,

Kindly use below article, which answers all questions like :
What is Store Procedure?
Why do we use SET NOCOUNT ON in stored procedure?
How many types of Store procedure are there?
How to write comment in SQL SERVER?
What is naming convention for store procedure?
Step by Step for How to create SELECT QUERY base store procedure which return all records?
How to execute store procedure in SQL SERVER?
What is Parameter?
Step by Step for How to create PARAMETER base SELECT QUERY store procedure which return records as per parameter passed.?
Step by Step for How to create a INSERT QUERY base store procedure?
Step by Step for How to create a UPDATE QUERY base store procedure?
Step by Step for How to create a DELETE QUERY base store procedure?
Follow below URL :
https://www.c-sharpcorner.com/article/how-to-create-a-stored-procedure-in-sql-server-management-studio/
+6
Jes Sie8y ago
Avoid using stored procedure with name that starts "sp_".
+3
Yogesh Kumar8y ago
Store Procedure:- First you have to understand what is store procedure and why its is used?

So Store Procedure is a set of Structured Query Language (SQL) statements with an assigned name, which are stored in a relational database management system as a group, so it can be reused and shared by multiple programs.

It can be used in two type we can create individual stored procedure for individual query (like insertion ,deletion,selection)

Now benefits of stored procedure is it compile once and execute n times.that's why we can use it take less time.

In ado.net there is optional to use store procedure but i sugest you to use it because it is more benefitial to use and you can also use a class were uyou can define in .Net

and you are not creating stored procedure in ado.net you can create in sql or mysql server

Now how to create stored procedure

like if we have table employee

and column or field attribute are id,name,age

now if we want to create individual store procedure

for insert

create procedure sp_emp_insert

(

@id char(4),

@name varchar(50),

@age int

)

as

begin

insert into employee values(@id,@name,@age)

end

like this we also create for deletion updation select and select all

Now if u want to do all operation in one stored procedure

CREATE PROCEDURE SP_EMP_OP

(

@ID CHAR(4),

@NAME VARCHAR(50),

@AGE INT,

@ACTION CHAR(1)

)

AS

BEGIN

IF(@ACTION='I')

INSERT INTO EMPLOYEE

VALUES(@ID,@NAME,@AGE)

ELSE IF(@ACTION='U')

UPDATE EMPLOYEE SET NAME=@NAME,AGE=@AGE

WHERE ID=@ID

ELSE IF(@ACTION='D')

DELETE FROM EMPLOYEE WHERE ID=@ID

ELSE IF(@ACTION='A')

SELECT * FROM EMPLOYEE

ELSE IF(@ACTION='S')

SELECT * FROM EMPLOYEE WHERE ID=@ID

END

Here we can see that there is extra varible declare action for taking query like I for insert,D for delete,U for update,S for select one,A for select all

when you give the data you have only pass at the end action than your query is perform.
+8
Vincent Maverick Durano8y ago
Searching the term "using stored procedure with ADO.NET" at google should give you tons of examples out there.

But for the completeness and to give you a quick snippet, you can do something like this:
using(SqlConnection connection = new SqlConnection("YOUR CONNECTION STRING HERE")){
            using(SqlCommand cmd = new SqlCommand("YourStoredProcedureName",connection)){
                cmd.CommandType = CommandType.StoredProcedure;

                DataTable dt = new DataTable();
                SqlDataAdapter ad = new SqlDataAdapter(cmd);
                ad.Fill(dt);

                //check the returned rows
                if (dt.Rows.Count > 0) {
                       //populate the result to a grid
                       GridView1.DataSource = dt;
                       GridView1.DataBind();
                }
                else
                {
                     //No records found
                }
}
+4
Kapil Singh Kumawat8y ago
Hello, Stored procedure is a set of sql statement used to accomplish your tasks. You can call the stored procedure from ado.net code.
+3
Pravat Kumar8y ago
How to make a stored procedure in ado.net and why?????
1. Stored Procedures are compiled and their execution plan is cached and used again, when the same SP is executed again.
2. Stored Procedures are reduced duplicating code unnecessarily at applications If multiple applications want to use the same query.
3. Stored procedure execution plans can be reused, staying cached in memory, reducing server overhead.
4. The stored procedure can reduce network traffic, boosting application performance.
plz give me any example on stored procedure in ado.net????
CREATE PROCEDURE Sp_GetCategories
AS
BEGIN
SET NOCOUNT ON;
SELECT * FROM Categories
END
public DataTable GetCategories()
{
try
{
using (SqlConnection sqlConnection = new SqlConnection(strConnectionString))
{
using (SqlCommand sqlCommand = new SqlCommand())
{
DataTable dtCat = new DataTable();
sqlCommand.Connection = sqlConnection;
sqlCommand.CommandText = "Sp_GetCategories";
sqlCommand.CommandType = CommandType.StoredProcedure;
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
sqlDataAdapter.Fill(dtCat);
sqlConnection.Close();
return dtCat;
}
}
}
catch (Exception ex)
{
throw ex;
}
}
+5
Jefferson S. Motta8y ago
Stored Procedure (SP) is your code direct in database.
You can add triggers, insert data, delete data, etc.
If you desing a large app with many developers/programmers, it's ok to make everyone respect the business layer.
But if you are a single developer, even in a big App, not necessarily you need create stored procedures. And if you has many customers with the same database, you'll need a way to admin the creation and updades changes in SP.

ADO.NET is just the tool that you will use in .NET Framework, SP can be called from ADO Classic too.
+6
Packiaraj Santhiyagu8y ago
Hi, read this blog it will help. https://support.microsoft.com/en-in/help/310070/how-to-call-a-parameterized-stored-procedure-by-using-ado-net-and-visu
+4
Jignesh Kumar8y ago
Hi Asha,

Ado .net and store procedure both are differnet.

Store procedure we can create in SQL server and via Ado .net we can call store proceder in our application.

Store procedure is set of sql staement which we can store as store procedure and we can use again and again when we need it.

Ado .Net is provides bridge betwwen front end application and database. using ado .net we can fetchdata from database and DML operation on database.

Please find few links for store procedure with ado .net :

https://www.c-sharpcorner.com/blogs/insert-delete-and-update-using-stored-procedure-in-asp-net1

https://www.c-sharpcorner.com/article/crud-operation-using-single-stored-procedure-in-asp-net-mvc-of-real-time-proje/

Thanks
+6