CODEWITHSUNDEEP
c#asp.net-mvcstored-procedures
Masriyah
Masriyah

Reputation: 2505

Passing stored procedure parameters in c# .net application

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

Upvotes: 1

Views: 3150

Answers (4)

Tiago Crizanto
Tiago Crizanto

Reputation: 312

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

Upvotes: 0

adopilot
adopilot

Reputation: 4520

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

Upvotes: 0

MethodMan
MethodMan

Reputation: 18843

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

Upvotes: 1

p.s.w.g
p.s.w.g

Reputation: 149020

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

Upvotes: 2

Related Questions