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?

Larsenal · Accepted Answer · 2010-09-17T23:11:29.163

7

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.

edited Sep 17 '10 at 23:11

answered Sep 16 '10 at 00:09

Larsenal

49,878
43
152
220

1

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. – Mike Brind Sep 18 '10 at 08:21
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. – infocyde Sep 20 '10 at 01:34
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. – infocyde Sep 20 '10 at 01:44

score 3 · Answer 2 · edited Oct 17 '12 at 14:41

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>

Webmatrix and Stored Procedures

2 Answers2