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, Jun 03 '13 at 19:59
@DJKRAZE thanks for the tip but i was actually concerned about passing the parameters in the method. — Masriyah, Jun 03 '13 at 20:05

p.s.w.g · Accepted Answer · 2013-06-03T20:11:07.963

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 });
}

Thanks for providing an answer but i main concern is with the parameters being passed in the method: 'public virtual IEnumerable GetUploadStudentSubmission(int studentId, .."How should i format the remaining parameters")' — Masriyah, Jun 03 '13 at 20:03

score 1 · Answer 2 · answered Jun 03 '13 at 20:11

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);
}

Tiago Crizanto · Answer 3 · 2013-09-01T15:12:12.127

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();
}

score 0 · Answer 4 · edited May 23 '17 at 12:05

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 '17 at 12:05

Community

1
1

answered Jun 03 '13 at 20:23

adopilot

4,340
12
65
92

Passing stored procedure parameters in c# .net application

4 Answers4