Passing stored procedure parameters in c# .net application

Question

I have a stored procedure and I am connecting it to my project and I wanted to know how I can pass the different parameter types in:

Stored procedure:

  [dbo].[UploadAssignment]  
          @studentId int    
    , @guid uniqueidentifier  
    , @originalfilename nvarchar(500)   
    , @uploaddate datetime

In my project:

public virtual IEnumerable<T> GetUploadStudentSubmission<T>(int studentId, .."How should i format the remaining parameters")  
{  
   SqlCommand _command = new SqlCommand("dbo.UploadAssignment");  
   _command.CommandType = CommandType.StoredProcedure;  
   _command.Parameters.Add(new SqlParameter { ParameterName = "studentId",SqlDbType = SqlDbType.Int, Value = sectionId});  
   _command.Parameters.Add(new SqlParameter { ParameterName = "guid", SqlDbType = SqlDbType.Int, Value = guid });  
   _command.Parameters.Add(new SqlParameter { ParameterName = "originalfilename", SqlDbType = SqlDbType.Int, Value = originalfilename });  
   _command.Parameters.Add(new SqlParameter { ParameterName = "uploaddate", SqlDbType = SqlDbType.Int, Value = uploaddate });
}

I would suggest changing your _command.Parameters.Add to _command.Parameters.AddWithValue("@paramname", variable) let the Database handle the Sql DataType — MethodMan
– MethodMan, Commented Jun 3, 2013 at 19:59
@DJKRAZE thanks for the tip but i was actually concerned about passing the parameters in the method. — Masriyah
– Masriyah, Commented Jun 3, 2013 at 20:05

p.s.w.g · Accepted Answer · 2013-06-03 20:11:07Z

2

The official documentation states:

The ParameterName is specified in the form @paramname.

So you need to include the @ in the parameter name. Other than that, you just need to pass in the relevant parameters just as you would to any other function, like this:

public virtual IEnumerable<T> GetUploadStudentSubmission<T>(
    int studentId, Guid guid, string originalfilename, DateTime uploaddate)
{  
    SqlCommand _command = new SqlCommand("dbo.UploadAssignment");  
    _command.CommandType = CommandType.StoredProcedure;  
    _command.Parameters.Add(new SqlParameter { ParameterName = "@studentId",SqlDbType = SqlDbType.Int, Value = sectionId});  
    _command.Parameters.Add(new SqlParameter { ParameterName = "@guid", SqlDbType = SqlDbType.UniqueIdentifier, Value = guid });  
    _command.Parameters.Add(new SqlParameter { ParameterName = "@originalfilename", SqlDbType = SqlDbType.NVarChar, Value = originalfilename });  
    _command.Parameters.Add(new SqlParameter { ParameterName = "@uploaddate", SqlDbType = SqlDbType.DateTime, Value = uploaddate });
}

edited Jun 3, 2013 at 20:11

answered Jun 3, 2013 at 19:59

p.s.w.g

150k31 gold badges307 silver badges339 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Masriyah Over a year ago

Thanks for providing an answer but i main concern is with the parameters being passed in the method: 'public virtual IEnumerable<T> GetUploadStudentSubmission<T>(int studentId, .."How should i format the remaining parameters")'

MethodMan · Accepted Answer · 2013-06-03 20:11:27Z

1

public virtual IEnumerable<T> GetUploadStudentSubmission<T>(int studentId, Guid guid, string originalfilename, DateTime uploaddate)  
{  
    SqlCommand _command = new SqlCommand("dbo.UploadAssignment");  
    _command.CommandType = CommandType.StoredProcedure;  
    _command.Parameters.AddWithValue("@studentId",sectionId);  
    _command.Parameters.AddWithValue("@guid", guid );  
    _command.Parameters.AddWithValue("@originalfilename",  originalfilename);  
    _command.Parameters.AddWithValue("@uploaddate", uploaddate);
}

answered Jun 3, 2013 at 20:11

MethodMan

18.9k6 gold badges41 silver badges53 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:05:25Z

0

Microsoft also provides the great Enterprise Library which beside other things contains a DataAccess block and handles parameters.

See this answer for more details https://stackoverflow.com/a/3038469/69433

edited May 23, 2017 at 12:05

CommunityBot

11 silver badge

answered Jun 3, 2013 at 20:23

adopilot

4,58015 gold badges69 silver badges96 bronze badges

Comments

Tiago Crizanto · Accepted Answer · 2013-09-01 15:12:12Z

0

You can also use the following code:

public virtual IEnumerable<T> GetUploadStudentSubmission<T>(int studentId, Guid guid,    string originalfilename, DateTime uploaddate)  
{ 
    var command = Database.GetStoredProcCommand("[dbo].[UploadAssignment]");
    Database.AddInParameter(command, "studentId", DbType.Int32, studentId);
    Database.AddInParameter(command, "guid", DbType.Guid, guid);
    Database.AddInParameter(command, "originalfilename", DbType.String, originalfilename);
    Database.AddInParameter(command, "uploaddate", DbType.DateTime, uploaddate);

    var reader = Database.ExecuteReader(command);
    commandText = command.CommandAsSql();
    reader.Close();
}

edited Sep 1, 2013 at 15:12

answered Jun 3, 2013 at 20:16

Tiago Crizanto

32210 silver badges24 bronze badges

Collectives™ on Stack Overflow

Passing stored procedure parameters in c# .net application

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related