locked
db.Query vs SqlCommand RRS feed

  • Question

  • User616014865 posted

    Hello,  I use MS SQL database.

    In Razor, I could use new syntax, db.Query,  or the old syntax, SqlCommand.

    They both work.   However, I am used to old syntax and using Parameter to deal with troublemakers like  quotation marks, tab and  comma.

    How these troublemakers are dealt in new syntax?

    What are the advantages and the disadvantages in new syntax compared to the old syntax?

    Friday, May 22, 2015 11:27 AM

Answers

  • User-821857111 posted

    The Database helper in WebMatrix is a wrapper around the "old" syntax (plain ADO.NET). As you have observed, you can still use the old syntax but it is a lot more verbose. The following two examples are functionally equivalent:

    [Database helper]
    var db = Database.Open("YourConnectionStringName");
    var data = db.Query("SELECT * From Products WHERE CategoryId = @0", Request["id"]);
    [ADO.NET]
    using(var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["YourConnectionStringName"].ConnectionString))
    using(var cmd = new SqlCommand("SELECT * From Products WHERE CategoryId = @0", conn)){
        cmd.Parameters.AddwithValue("@0", Request["id"]);
        var dt = new DataTable();
        var da = new SqlDataAdapter(cmd);
        da.Fill(dt);
    }

    They both use parameters - the first example does all of that under the hood. The Database helper was designed for newcomers to ASP.NET. It reduces the learning curve quite a bit and is less formidable. 

    On the other hand, you can use the Entity Framework with WebMatrix. It also reduces the amount of repetitive boiler-plate code you have to write to work with data, although you need to learn LINQ: Entity Framework Code First Development With WebMatrix



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 22, 2015 4:17 PM

All replies

  • User-821857111 posted

    The Database helper in WebMatrix is a wrapper around the "old" syntax (plain ADO.NET). As you have observed, you can still use the old syntax but it is a lot more verbose. The following two examples are functionally equivalent:

    [Database helper]
    var db = Database.Open("YourConnectionStringName");
    var data = db.Query("SELECT * From Products WHERE CategoryId = @0", Request["id"]);
    [ADO.NET]
    using(var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["YourConnectionStringName"].ConnectionString))
    using(var cmd = new SqlCommand("SELECT * From Products WHERE CategoryId = @0", conn)){
        cmd.Parameters.AddwithValue("@0", Request["id"]);
        var dt = new DataTable();
        var da = new SqlDataAdapter(cmd);
        da.Fill(dt);
    }

    They both use parameters - the first example does all of that under the hood. The Database helper was designed for newcomers to ASP.NET. It reduces the learning curve quite a bit and is less formidable. 

    On the other hand, you can use the Entity Framework with WebMatrix. It also reduces the amount of repetitive boiler-plate code you have to write to work with data, although you need to learn LINQ: Entity Framework Code First Development With WebMatrix



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 22, 2015 4:17 PM
  • User616014865 posted

    There is slight difference between db.Query and cmd.

    One returns Collection[SystemObject] and the other returns familiar DataTable.

    Since I like DataRow which is an independent variable that I can edit and keep in Session, I prefer to use old ADO.Net syntax.

    Tuesday, May 26, 2015 11:40 AM
  • User-821857111 posted

    To be pedantic, cmd doesn't return a datatable. You can use a DataAdapter to fill a DataTable using a command, but you could also use a cmd to fill a collection of objects.

    Tuesday, May 26, 2015 2:52 PM