Named Parameters in Microsoft RAZOR/CSHTML Database Queries

Parameters are GOOD. Use them to help reduce sql injection threats!

I searched all over the web to find a way to use named parameters in RAZOR. The sequential numbering scheme that System.Web.Helpers uses is ok, but often naming parameters gives you a lot more flexibility.

Here is my solution;

Start by going to Newtonsoft to download and install his great, free json library. http://www.newtonsoft.com/json
Unzip the Newtonsoft code. Copy the Newtonsoft.Json.dll from the bin/Net40 directory to your bin directory on azure or whatever hosting service you use.

Include these at the top of your RAZOR/CSHTML page

        @using System.Text;
        @using System.Configuration
        @using Newtonsoft.Json;
        @using System.Data.SqlClient
        

Next, add this code;

        @{ 
            var lastnameSearch = "Bob";
            var conString = ConfigurationManager.ConnectionStrings["quotes2"].ToString();
            var con = new SqlConnection(conString);
            try {
                   con.Open();

                   //Use your connect string, database table and query and you should get a nice JSON string.
                   var command = new SqlCommand("SELECT TOP 30 firstname, lastname from contacts where lastname like @lastname;", con);

                   command.Parameters.Add("@lastname", "%" + lastnameSearch + "%");
                   SqlDataReader reader = command.ExecuteReader();

                    StringBuilder sb = new StringBuilder();
                    StringWriter sw = new StringWriter(sb);    

                    using (JsonWriter jsonWriter = new JsonTextWriter(sw)) 
                    {    
                        jsonWriter.WriteStartArray();

                        while (reader.Read())
                        {
                            jsonWriter.WriteStartObject();

                            int fields = reader.FieldCount;

                            for (int i = 0; i < fields; i++)
                            { 
                                jsonWriter.WritePropertyName(reader.GetName(i));
                                jsonWriter.WriteValue(reader[i]);
                            }

                            jsonWriter.WriteEndObject();
                        }

                        jsonWriter.WriteEndArray();
                    }

                Response.Write(sb.ToString());
                reader.Close();
            } catch (SqlException sqlException) {
                Response.Write(sqlException.ToString() + "\n");
            } finally {
                con.Close(); 
            }

        }
        
Use your connect string, database table and query and you should get a nice JSON string.

- Doug
QuoteLight.com
last update: 02/01/2015