C# Razor - How to connect, query and display data from SQL server

 

This blog post describes how to make the initial connection to a SQL Server database in razor c# (.cshtml), then execute different types of query the database, finally we look at one method of displaying the data

It is common practice to store connection string in web.config. First of all you need to navigate to the web.config and implement the connection string, if you cant recall the syntax dont worry copy the one below and edit it inline. The connection string sits just inside the <configuration> tag.

<connectionStrings>
          <add name="dbconn2" connectionString="server=localhost;database=myDb;uid=myUser;password=myPass;" />
</connectionStrings>


In your c# page you can now use the connection string to connect to SQL server:

var db = Database.Open("dbconn2");


Once the the connection is made you can now query the database. This can be in many different ways here we look at retrieving a single row and multiple rows. 

When you know that your result set will be a single row for example a pesons contact record use the QuerySingle() method

When multiple rows will be returned then use the Query() method.

.Querysingle()

var row = db.QuerySingle("SELECT firstname, lastname, email FROM contacts WHERE id = @0", recordID);


Once the the connection to SQL server is made we can now query the database, our datbase variable name is 'db'. Querysingle retrieves a single row from the database with different columns, which can be adjusted.

.Query()

var result = db.Query("SELECT * FROM contacts");


This query returns more than one row, the exmaple above retrives all the rows from the table. For example say you had a table of customers  and you wanted to retreive all the customers accounts, we would write:  db.Query("SELECT * FROM customers");

To display results in a table 

When you've queried the database, you can allow the results to display all on one line on the page. Below im am going to display the results form a query, in a table using <th> and <tr>.

<div class="table-responsive">
    <hr />
    <h2>Results</h2>
    <table class="gridtable">
        <tr>
            @foreach (var row in results)
            {
                foreach (var col in row.Columns)
                {
                    <th>@col</th>
                }
                break; // we dont want any more
            }

        </tr>
        @foreach (var row in results)
        {
            <tr>
                <td>@row.name</td>
                <td>@row.phone</td>
            </tr>
        }
    </table>
</div>

In the first foreach we are looping through each row in the database and adding in the colums needed in order to display the data. 'col' short for columns. The second foreach is populating the table with the values from the records. You can change the names of the values that we pull back from the database or add more <td> 's to display more data for each row.