sql store procedures

Question

0.00/5 (No votes)

See more:

Hi All,

I have developed one windows form and able to save/update/delete the data with sql database.

I used all sql expressions in form code with queries.

How to handle all sql operations through store procedures.

Regards,
Raju G

Posted 8-Jul-13 19:24pm

rajugknr

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Rajan Maheshwari · Answer 1 · 2013-07-08T19:30:00

GO

SQL

ALTER proc [dbo].[AddNewPatientRecord](@PatientMob varchar(15),@PatientName varchar(50),@PatientAge int,@PatientSex varchar(1),@PatientAddress varchar(60),@FosaUser varchar(1),@DoctName varchar(30))
as
insert into Patient values(@PatientMob,@PatientName,@PatientAge,@PatientSex,@PatientAddress,@FosaUser,@DoctName)

This can be used as an example when you write stored procedure
Its populating a table of add a new patient record

Rest in c# you can use a function to use this stored procedure like

C#

public void AddNewRecord(string PatientMob, string PatientName, int PatientAge, string PatientSex, string PatientAddress, string FosaUser, string DoctName)
      {
          cmd = new SqlCommand("AddNewPatientRecord", con);
          cmd.CommandType = CommandType.StoredProcedure;
          cmd.Parameters.AddWithValue("@PatientMob", SqlDbType.VarChar).Value = PatientMob;
          cmd.Parameters.AddWithValue("@PatientName", SqlDbType.VarChar).Value = PatientName;
          cmd.Parameters.AddWithValue("@PatientAge", SqlDbType.Int).Value = PatientAge;
          cmd.Parameters.AddWithValue("@PatientSex", SqlDbType.VarChar).Value = PatientSex;
          cmd.Parameters.AddWithValue("@PatientAddress", SqlDbType.VarChar).Value = PatientAddress;
          cmd.Parameters.AddWithValue("@FosaUser", SqlDbType.VarChar).Value = FosaUser;
          cmd.Parameters.AddWithValue("@DoctName", SqlDbType.VarChar).Value = DoctName;

          cmd.ExecuteNonQuery();
      }

jaideepsinh · Answer 2 · 2013-07-08T19:33:00

Solution 2

Refer this link here you get you solution:

how to call stored procedure in asp.net c# code[^]

Accept as answer if help.

Posted 8-Jul-13 19:33pm

jaideepsinh

Mukesh Ghosh · Answer 3 · 2013-07-08T19:34:00

Please check bellow link

http://forums.asp.net/t/1608207.aspx/1[^]

also e.g for your help

SQL

-- =============================================
-- Author: Mukesh Ghosh
-- Create date: 09 Jul, 2013
-- Description: Save Settings
/*
  EXEC stp_Save_Settings 
    	@Key = NULL,
		@Value = NULL
*/
-- =============================================
CREATE PROCEDURE stp_Save_Settings
	@Key VARCHAR(50),
	@Value NVARCHAR(500)
AS
BEGIN
	-- SET NOCOUNT ON ADDED TO PREVENT EXTRA RESULT SETS FROM
	SET NOCOUNT ON;
	
	BEGIN TRY
    
		
    
	END TRY
	BEGIN CATCH
	
		DECLARE @ErrorMessage NVARCHAR(4000),
			    @ErrorSeverity INT,
		        @ErrorState INT

		SELECT @ErrorMessage = ERROR_MESSAGE(),
		       @ErrorSeverity = ERROR_SEVERITY(),
		       @ErrorState = ERROR_STATE();

		-- USE RAISERROR INSIDE THE CATCH BLOCK TO RETURN ERROR
		RAISERROR (@ErrorMessage, -- MESSAGE TEXT.
		           @ErrorSeverity, -- SEVERITY.
			       @ErrorState -- STATE. 
		          )
	END CATCH
    
END
GO
-- =============================================
-- Author: Mukesh Ghosh
-- Create date: 09 Jul, 2013
-- Description: Delete Settings
/*
  EXEC stp_Delete_Settings 
    
*/
-- =============================================
CREATE PROCEDURE stp_Delete_Settings
	
AS
BEGIN
	-- SET NOCOUNT ON ADDED TO PREVENT EXTRA RESULT SETS FROM
	SET NOCOUNT ON;
	
	BEGIN TRY
    
		DELETE FROM tbl_App_Settings
		WHERE 

		RETURN 1
    
	END TRY
	BEGIN CATCH
	
		DECLARE @ErrorMessage NVARCHAR(4000),
			    @ErrorSeverity INT,
		        @ErrorState INT

		SELECT @ErrorMessage = ERROR_MESSAGE(),
		       @ErrorSeverity = ERROR_SEVERITY(),
		       @ErrorState = ERROR_STATE();

		-- USE RAISERROR INSIDE THE CATCH BLOCK TO RETURN ERROR
		RAISERROR (@ErrorMessage, -- MESSAGE TEXT.
		           @ErrorSeverity, -- SEVERITY.
			       @ErrorState -- STATE. 
		          )
	END CATCH
    
END
GO
-- =============================================
-- Author: Mukesh Ghosh
-- Create date: 09 Jul, 2013
-- Description: Get Settings Details
/*
  EXEC stp_Get_SettingsDetails 
    
*/
-- =============================================
CREATE PROCEDURE stp_Get_SettingsDetails
	
AS
BEGIN
	-- SET NOCOUNT ON ADDED TO PREVENT EXTRA RESULT SETS FROM
	SET NOCOUNT ON;
	
	BEGIN TRY
    
		SELECT Key,Value
		FROM   tbl_App_Settings
		WHERE  
    
	END TRY
	BEGIN CATCH
	
		DECLARE @ErrorMessage NVARCHAR(4000),
			    @ErrorSeverity INT,
		        @ErrorState INT

		SELECT @ErrorMessage = ERROR_MESSAGE(),
		       @ErrorSeverity = ERROR_SEVERITY(),
		       @ErrorState = ERROR_STATE();

		-- USE RAISERROR INSIDE THE CATCH BLOCK TO RETURN ERROR
		RAISERROR (@ErrorMessage, -- MESSAGE TEXT.
		           @ErrorSeverity, -- SEVERITY.
			       @ErrorState -- STATE. 
		          )
	END CATCH
    
END
GO

sql store procedures

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0