Please find the C# code where right now I'm inserting as a normal query, but I have created a stored procedure for insert which takes 7 parameters

try
{
    string query = @"insert into dbo.PhotoContest values('"  
                        photo.contestTitle   @"' , '"  
                        photo.contestLocation   @"' , '"  
                        photo.contestDescription   @"' , '"  
                        photo.votingType   @"' , '"  
                        photo.voteDate   @"' , '"  
                        photo.voteEndDate   @"' , '"  
                        photo.partDate   @"' , '"  
                        photo.partEndDate   @"' , '"  
                        photo.uploads   @"' , '"  
                        photo.photoOption   @"' , '"  
                        photo.caption   @"')";

    // method to execute the query
    DataTable table = new DataTable();

    using (var con = new SqlConnection(ConfigurationManager.ConnectionStrings["PhotoContestDB"].ConnectionString))
    using (var cmd = new SqlCommand(query, con))
    using (var da = new SqlDataAdapter(cmd))
    {
        cmd.CommandType = CommandType.Text;
        da.Fill(table);
    }

    return "Added successfully!";
}

And this is my stored procedure which I created in my database:

DROP PROCEDURE IF EXISTS dbo.InsertPhotoContest;
GO

CREATE PROCEDURE dbo.InsertPhotoContest
    (@PhotoContestID int OUTPUT,
     @contestTitle varchar(500),
     @contestLocation varchar(500),
     @contestDescription varchar(500),
     @votingType varchar(255),
     @voteDate datetime,
     @voteEndDate datetime,
     @partDate datetime,
     @partEndDate datetime,
     @uploads varchar(500),
     @photoOption varchar(500),
     @caption varchar(500))
AS
BEGIN;
    INSERT INTO dbo.PhotoContest (contestTitle, contestLocation, contestDescription, 
                                  votingType, voteDate, voteEndDate, 
                                  partDate, partEndDate, uploads, photoOption, caption)
    VALUES (@contestTitle, @contestLocation, @contestDescription, 
            @votingType, @voteDate, @voteEndDate,
            @partDate, @partEndDate, @uploads, @photoOption, @caption);

    SELECT @PhotoContestID = SCOPE_IDENTITY();

    SELECT * 
    FROM dbo.PhotoContest 
    WHERE PhotoContestID = @PhotoContestID;
END;                                        

I just need to know how to convert my query into stored procedure. As you can see in my code 11 parameters which I need to insert. In my photo contest table, there is photoContestID which is identity (which means it auto increments), first is my stored procedure correct, also wanted to know how would I use my stored procedure in my C# backend code?

CodePudding user response：

You need something like this - and I would not really return the whole inserted row from the stored procedure (since you've already passed in all the input parameter values anyway) - just read the return new ID value from your OUTPUT parameter.

// set up your connection and command
using (var con = new SqlConnection(.....))
using (var cmd = new SqlCommand("dbo.InsertPhotoContest", con))
{
    // define it as stored procedure
    cmd.CommandType = CommandType.StoredProcedure;
    
    // add input parameters and set their value
    cmd.Parameters.Add("@contestTitle", SqlDbType.VarChar, 500).Value = photo.contestTitle;
    cmd.Parameters.Add("@contestLocation", SqlDbType.VarChar, 500).Value = photo.contestLocation;
    // and so forth, for all 11 input parameters
    
    // separate output parameter
    cmd.Parameters.Add("@PhotoContestID", SqlDbType.Int).Direction = ParameterDirection.Output;
    
    // open connection, execute stored procedure, close connection
    con.Open();
    
    cmd.ExecuteNonQuery();

    // get newly assigned ID        
    int newPhotoContestID = Convert.ToInt32(cmd.Parameters["@PhotoContestID"].Value);
    
    con.Close();
}

CodePudding user response：

All you need to do is this

DataTable table = new DataTable();

using (var con = new SqlConnection(ConfigurationManager.ConnectionStrings["PhotoContestDB"].ConnectionString))
using (var cmd = new SqlCommand("dbo.InsertPhotoContest", con)) // SP NAME
using (var da = new SqlDataAdapter(cmd))
{
    
    cmd.CommandType = CommandType.StoredProcedure; // CHANGE THIS
    cmd.Parameters.Add(new SqlParameter("contestTitle", . . . . )); 
    // ADD MORE PARAMETERS HERE

    con.Open();
    da.Fill(table);
} // this will dispose all objects and close conn inside dispose

In your SP you don't need parameter @PhotoContestID int OUTPUT, because you are returning the entire row and you can do int id = (int)table.Rows[0]["YOUR-COLUMN-NAME"];

You could also invest into how to use OUTPUT INSERTED with your INSERT and SP return value

CodePudding user response：

You have to use dynamic query and then execute that dynamic query https://www.geeksforgeeks.org/dynamic-sql/amp/

Flow this