stored procedure in ado.net

Question

How to make a stored procedure in ado.net and why????? plz give me any example on stored procedure in ado.net?????

Yogesh Kumar · Answer

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.

Code Alone · Answer

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/

Jefferson S. Motta · Answer

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.

Jignesh Kumar · Answer

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

Pravat Kumar · Answer

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; } }

Vincent Maverick Durano · Answer

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 } }

Kapil Singh Kumawat · Answer

Hello, Stored procedure is a set of sql statement used to accomplish your tasks. You can call the stored procedure from ado.net code.

Packiaraj Santhiyagu · Answer

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

Jes Sie · Answer

Avoid using stored procedure with name that starts "sp_".

Mageshwaran R · Answer

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 (); }

stored procedure in ado.net

Answers (10)