Webmatrix and Stored Procedures

Question

I'm fooling around with WebMatrix, and so far the best way I've figured out how to use stored procedures with Razor/WebMatrix is like so-

@if (IsPost) {

   var LinkName = Request["LinkName"];
   var LinkURL  = Request["LinkURL"];

   string sQ = String.Format("execute dbo.myprocname @LinkName=\"{0}\",
 @LinkURL=\"{1}",LinkName, LinkURL);

   db.Execute(sQ);
}

Note, I'm not doing any sort of checking for SQL injections or anything like that, which I think would be uber necessary. Am I missing something?

Schweiz

Answer 1

The Execute method accepts parameters.

@if (IsPost) {
  var LinkName = Request["LinkName"];
  var LinkURL = Request["LinkURL"];
  string SQL = "exec dbo.myprocname @0, @1";
  db.Execute(SQL, LinkName, LinkURL);
}

Update: I've updated my answer so that the parameters for the sproc are given placeholders that are numbered rather than named.

Horta

It should work now that Larsenal has updated the answer to use the @0,@1 notation for the placeholders. That's how the Database helper maps parameter values to placeholders internally, by index, not name. – Racklin

Here is the problem. What if the proc has 10 params but I only need to pass two. If I don't name them, they are expected in the order in which they are declared, so I would have to put in place holder values something like exec dbo.myproc null, null, @0, 1, "server", @1" I no likey. Plus what if the order of the params changes, then I have to go back and update the Razor code in webmatrix. I know, it is still in beta, and webmatrix is targeting entry level folks, but it would be nice to be able to name the params rather than use the index. – Schweiz

I guess the database helper at this time doesn't support parameter names, so the original code that I posted is the only work around to use named params and stored procs, and this work around opens up a few cans of worms. Hopefully the next beta will have a better way of using stored procs, like supporting parameter naming somehow. – Schweiz

Answer 2

well, this is what I found is easiest and you can use named parameters. Meaning, if your stored procedure has several optional parameters, you can only pass the ones you need or want to pass.

@{
    var db = Database.Open("your database name");
    var param1 = "informationhere";
    var param2 = "informationhere";
    // or var param2 = 15247 (no quotes necessary if param is an integer datatype)
    var procRows = db.Query("Exec dbo.procName @RealParameterName1=@0, @RealParameterName2=@1", param1, param2);
}

<table>
@foreach( var procRow in procRows )
{
    <tr>
        <td>@procRow.ColumnName1</td>
        <td>@procRow.ColumnName2</td>
        <td>@procRow.ColumnName3</td>
        //etc
    </tr>
}    
</table>

Villainy