Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

invoking a sql server stored proc from mvc controller

Posted on 2013-12-23
7
Medium Priority
?
448 Views
Last Modified: 2013-12-23
Im wishing to call a stored procedure from my delete controller but am not sure how.
This app does use entity framework so im not sure how that comes into play.



My controller code is simply this:

 public ActionResult Delete(int id)
        {
            
       

      //         return to view;        
        }

Open in new window


I want to be able to invoke the sproc from the controller method above.

The sproc returns @@rowcount because I was told that you have to return a value for entity framework to find it in context.  

Any help is appreciated.
0
Comment
Question by:hougie40
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 15

Accepted Solution

by:
unknown_routine earned 1600 total points
ID: 39736378
You need something similar to below:

SqlConnection sqlConnection1 = new SqlConnection("Your Connection String");
SqlCommand cmd = new SqlCommand();


cmd.CommandText = "StoredProcedureName";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = sqlConnection1;

sqlConnection1.Open();

//Return number of rows
cmd.ExecuteNonQuery() = cmd.ExecuteReader();
0
 
LVL 13

Expert Comment

by:Ashok
ID: 39736681
Here is an example of calling SP:

using (Entities context = new Entities())
{
  string ConnectionString = (context.Connection as EntityConnection).StoreConnection.ConnectionString;
    SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(ConnectionString);
    builder.ConnectTimeout = 2500;
    SqlConnection con = new SqlConnection(builder.ConnectionString);
    System.Data.Common.DbDataReader sqlReader;
    con.Open();
    using (SqlCommand cmd = con.CreateCommand())
    {
        cmd.CommandText = "GetEmployeeData";
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        cmd.CommandTimeout = 0;

      sqlReader = (System.Data.Common.DbDataReader)cmd.ExecuteReader();
      IEnumerable<EmployeeDetail> empDetails = context.Translate<EmployeeDetail>(sqlReader).ToList();
    }
}


HTH
Ashok
0
 
LVL 13

Expert Comment

by:Ashok
ID: 39736690
What is the name of your edmx (entity) class?

What is the name of your Stored Procedure?

What (and how many) input parameters does your Stored Procedure have?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:hougie40
ID: 39736724
In the sproc its just going to be a series of delete statements and its just going to take an id and return the rowcount.  The first example that unknown_routine left does work fine.  I am not sure if I need to wire it up to Entity Framework edmx or not but when I try the delete it works because when I retun back to the View the record is gone and is gone from sql server also.

     public ActionResult Delete(int id)
        {
          

                SqlConnection sqlConnection1 = new SqlConnection("server=blahblah");
                SqlCommand cmd = new SqlCommand();


                cmd.CommandText = "exec dbo.spDeleteVehicleAccidentByAccidentId " + "@AccidentId = " + id.ToString();
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Connection = sqlConnection1;

                sqlConnection1.Open();
                try
                {
                //Return number of rows
                cmd.ExecuteNonQuery();

                AddFeedbackMessage("The record has been deleted.");

            }
            catch (Exception ex) {
             Logging.LogError(ex);
            }

            return RedirectToAction("Search");
        }

Open in new window


Here is the sproc.  

ALTER PROCEDURE [dbo].[spDeleteVehicleAccidentByAccidentId]
	-- Add the parameters for the stored procedure here
	@AccidentId int


AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	

--BEGIN TRY
      --BEGIN TRANSACTION
     
	 --Accident Citation
	  DELETE Accident.tbAccidentCitation
      FROM Accident.tbAccidentCitation AS AC
      INNER JOIN Accident.tbAccidentDriver AS AD
            ON AC.DriverId = AD.Id
      WHERE AD.AccidentID = @AccidentID;

      --Accident Driver
	  DELETE Accident.tbAccidentDriver
      WHERE AccidentID = @AccidentID;

	  --other vehicle driver
      DELETE Accident.tbAccidentOtherVehicleDriver
      FROM Accident.tbAccidentOtherVehicleDriver AS OVD
      INNER JOIN Accident.tbAccidentOtherVehicle AS OV
            ON OVD.OtherVehicleId = OV.Id
      WHERE OV.AccidentID = @AccidentID;

	  --other vehicle owner
	   DELETE Accident.tbAccidentOtherVehicleOwner
      FROM Accident.tbAccidentOtherVehicleOwner AS OVO
      INNER JOIN Accident.tbAccidentOtherVehicle AS OV
            ON OVO.OtherVehicleId = OV.Id
      WHERE OV.AccidentID = @AccidentID;

	  --other vehicle insurance
	   DELETE Accident.tbAccidentOtherVehicleInsurance
      FROM Accident.tbAccidentOtherVehicleInsurance AS OVI
      INNER JOIN Accident.tbAccidentOtherVehicle AS OV
            ON OVI.OtherVehicleId = OV.Id
      WHERE OV.AccidentID = @AccidentID;

	   --other vehicle passenger
	   DELETE Accident.tbAccidentOtherVehiclePassenger
      FROM Accident.tbAccidentOtherVehiclePassenger AS OVP
      INNER JOIN Accident.tbAccidentOtherVehicle AS OV
            ON OVP.OtherVehicleId = OV.Id
      WHERE OV.AccidentID = @AccidentID;


	  --Adjuster Participant
      DELETE Accident.tbAccidentAdjuster
      From Accident.tbAccidentAdjuster as AA
	  Inner Join Accident.tbAccidentParticipant as AP
	  ON AP.Id = AA.AdjusterParticipantId
	  WHERE AP.AccidentID = @AccidentID;

	   --Accident Police
      DELETE Accident.tbAccidentPoliceInformation
      From Accident.tbAccidentPoliceInformation as API
	  Inner Join Accident.tbAccidentParticipant as AP
	  ON AP.Id = API.AuthorityParticipantId
	  WHERE AP.AccidentID = @AccidentID;

      DELETE Accident.tbAccidentUnit
      WHERE AccidentID = @AccidentID;

      DELETE Accident.tbAccidentTrailer
      WHERE AccidentID = @AccidentID;

      DELETE Accident.tbAccidentClaimant
      FROM Accident.tbAccidentClaimant AS AC
      INNER JOIN Accident.tbAccidentParticipant AS AP
            ON AC.ClaimantParticipantID = AP.ID  
      WHERE AP.AccidentID = @AccidentID;

      DELETE Accident.tbAccidentParticipant
      WHERE AccidentID = @AccidentID;

      DELETE Accident.tbAccidentOrderStop
      WHERE AccidentID = @AccidentID;

      DELETE Accident.tbAccidentPoliceInformation
      WHERE AccidentID = @AccidentID;
	  
	  DELETE Accident.tbAccidentAnalysis
      WHERE AccidentID = @AccidentID;

	    
	  DELETE Accident.tbAccidentCost
      WHERE AccidentID = @AccidentID;

	  DELETE Accident.tbAccidentContributingFactor
      WHERE AccidentID = @AccidentID;

	  DELETE Accident.tbAccidentDiary
      WHERE AccidentID = @AccidentID;

	  --Finally delete the accident record master
	  DELETE Accident.tbAccident
	  Where Id = @AccidentId

	  select @@ROWCOUNT

Open in new window

0
 
LVL 13

Expert Comment

by:Ashok
ID: 39736764
You are using new database connection (not existing database connection).

Also, you are forgetting to close the connection.
0
 
LVL 13

Assisted Solution

by:Ashok
Ashok earned 400 total points
ID: 39736774
sqlConnection1.Open();
                try
                {
                //Return number of rows
                int i = cmd.ExecuteNonQuery();
sqlConnection1.Close();
                AddFeedbackMessage("The record has been deleted.");
            }
            catch (Exception ex) {
             Logging.LogError(ex);
            }
0
 

Author Closing Comment

by:hougie40
ID: 39736815
It doesnt appear I will need to hook into the edmx to pull this off.  That is subject to change but for now im going with a simpler solution involving a direct connection to sql server.  Thank you both.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question