Is it possible to pass an object to a store procedure to update ,delete and insert operation? how?

Question

2.67/5 (3 votes)

See more:

I want to pass an object to a store procedure in SQL-Server 2008. In store procedure I want to take data from my object and then insert that data to a table. Is it possible in SQL-Server? how? Here my object is custom class object.
please help me.

Posted 26-Sep-13 19:42pm

Tanumay99

Updated 29-Sep-13 18:08pm

v2

Add a Solution

Comments

Tanumay99 27-Sep-13 1:44am

Please help me If any one knows.

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)

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) · Answer 1 · 2013-09-26T20:54:00

Refer - Passing an object collection as a parameter into SQL Server stored procedure[^].

Quote:

Assuming SQL Server 2008+, you can do this using a table-valued parameter. First in SQL Server create a table type:

SQL

CREATE TYPE dbo.HobbiesTVP AS TABLE
(
  HobbyID INT PRIMARY KEY,
  HobbyName NVARCHAR(50),
  HobbyTypeID INT
);

Then your stored procedure would say:

SQL

@Hobbies dbo.HobbiesTVP READONLY

In C# (sorry I don't know vb.net equivalent) it would be as follows (but if you just have one UserID, this doesn't need to be part of the collection, does it?):

C#

// as Steve pointed out, you may need to have your hobbies in a DataTable.

DataTable HobbyDataTable = new DataTable();
HobbyDataTable.Columns.Add(new DataColumn("HobbyID"));
HobbyDataTable.Columns.Add(new DataColumn("HobbyName"));
HobbyDataTable.Columns.Add(new DataColumn("HobbyTypeID"));

// loop through objHobbyCollection and add the values to the DataTable,
// or just populate this DataTable in the first place

using (connObject)
{
    SqlCommand cmd = new SqlCommand("dbo.User_Update", connObject);
    cmd.CommandType = CommandType.StoredProcedure;
    // other params, e.g. @UserID
    SqlParameter tvparam = cmd.Parameters.AddWithValue("@Hobbies", HobbyDataTable);
    tvparam.SqlDbType = SqlDbType.Structured;
    // ...presumably ExecuteNonQuery()
}

Also look at - Table Value Parameters in SQL Server 2008 and .NET (C#)[^]

OriginalGriff · Answer 2 · 2013-09-26T19:45:00

Solution 1

Yes, it's possible - just use a parameter (or more than one)

SQL stored procedure parameters[^]

Posted 26-Sep-13 19:45pm

OriginalGriff

Comments

Tanumay99 27-Sep-13 2:13am

actually I want to pass object, with its multiple property. Not a single value or multiple value as parameter in store procedure. please try to under stand my question.

VeeraAnnapureddy · Answer 3 · 2013-09-26T21:18:00

ALTER PROC usp_Getdetails
(
       @Parameter       INT,
       @Id		INT,
       @BookName	VARCHAR(100),
       @Price		INT,
       @CreatedBy       INT, 
       @LastUpDatedBy   INT, 
       @return		INT	= NULL OUTPUT,
       @errorId		INT	= NULL OUTPUT,
       @errorMessage	VARCHAR(1000) = NULL OUTPUT  
  )    
  AS
  SET NOCOUNT ON 
  BEGIN
  BEGIN TRY
  BEGIN TRAN
  
  IF NOT EXISTS (SELECT 1 FROM Getdetails where Id = @Id)
  BEGIN
  
  IF @Parameter =1
  
  BEGIN
  
  INSERT Getdetails
  (
   BookName,    Price,      ActiveYN,   
   DeletedYN,   CreatedOn,  CreatedBy,  LastUpDatedOn
  )
  VALUES
  (  
  @BookName,	@Price,      1,
  0,            GETDATE(),   @CreatedBy,  GETDATE()
  )
  
  END   
   
  END   
   
  ELSE
     
  BEGIN
        
  IF @Parameter =2
        
     BEGIN
     
     UPDATE Getdetails
     
     SET DeletedYN = 1,
            LastUpDatedOn = GETDATE()
            WHERE Id=@Id
         
    END
    
    ELSE
   
    IF @Parameter =3
   
    BEGIN
    UPDATE Getdetails
    SET
          BookName	=	@BookName,
          Price		=	@Price,
          ActiveYN	=       1,
          DeletedYN	=	0,
          LastUpDatedBy	=	@LastUpDatedBy
          
          WHERE Id=@Id
       END
       
   --   SELECT @errorMessage = 'Record Already Exists'
        
        END
        COMMIT TRAN
        END TRY
        BEGIN CATCH
        ROLLBACK TRAN
        
        SELECT @return              = -1,
                     @errorId       =  ERROR_NUMBER(),
                     @errorMessage  =  ERROR_MESSAGE()
    RETURN -1
    END CATCH
               --SELECT @errorMessage = 'Record Inserted Successfully'
       
        
     END
     SET NOCOUNT OFF

Is it possible to pass an object to a store procedure to update ,delete and insert operation? how?

3 solutions

Solution 2

Solution 1

Solution 3

Add your solution here

Preview 0