Razor templates - connect to database

Kasper Birk Nielsen

Posted on 02/01/2014 14:38:30

Is it possible to make a razor template, that performs a custom SELECT statement on the "ecomproducts" table, and then output all rows with a foreach loop - all done with c# code?

Replies

Mikkel Ricky

Posted on 03/01/2014 07:47:19

Using Dynamicweb.eCommerce.Products.Product.GetProductsBySql you can get a collection of Products from a custom select:

@{
	var sql = "select top 10 * from EcomProducts";
	var products = Dynamicweb.eCommerce.Products.Product.GetProductsBySql(sql);
}

<ul>
	@foreach (var product in products) {
		<li>@product.Name</li>
	}
</ul>

You can also get the rows using a DataReader if you need the raw values from the database:

<ul>
	@using (var reader = Dynamicweb.Database.CreateDataReader(sql)) {
		while (reader.Read()) {
			<li>@reader["ProductName"]</li>
		}
	}
</ul>

Best regards,
Mikkel

Kasper Birk Nielsen

Posted on 03/01/2014 12:07:08

Thanks Mikkel,

How can I then get POST data or parameter from URL, if I want to use this, to make a custom product search form? Are URL parameters to be read with:

Request.Params["paramName"]  ?

...or does DW have special methods for that?

Mikkel Ricky

Posted on 03/01/2014 12:52:33

Request.Params is the way to go for creating a custom product search. In the example below I use a DataReader to create a Product rather than using the raw values from the reader itself.

<ul>
    @{
        using (var command = Dynamicweb.Database.CreateConnection().CreateCommand())
        {
            command.CommandText = "select top 10 * from EcomProducts where ProductName like '%'+@ProductName+'%'";
            command.Parameters.Add(new System.Data.SqlClient.SqlParameter()
            {
                ParameterName = "ProductName",
                SqlDbType = System.Data.SqlDbType.NVarChar,
                Value = System.Web.HttpContext.Current.Request.Params["name"] ?? ""
            });

            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    var product = new Dynamicweb.eCommerce.Products.Product(reader);
                    <li>@product.Name</li>
                }
            }
        }
    }
</ul>

Kasper Birk Nielsen

Posted on 06/01/2014 15:37:00

Thanks Mikkel, this works great :)

Do we have any function to sanitize input for preventing sql injections?

Mikkel Ricky

Posted on 06/01/2014 15:51:35

If you use a SqlCommand (as the second example does) and add any users input (and other values) using the Parameters property, then you don't need to escape or sanitize the input yourself. The SqlCommand class does this for you.

You must be logged in to post in the forum

Developer forum

Razor templates - connect to database

Replies