CODEWITHSUNDEEP
c#htmlsqlsql-serverdapper
user2433662
user2433662

Reputation:

Getting Started with Dapper and SQL Server database

I am working on a personal project to display information on a webpage. I haven't done SQL connections in over a year and this is also my first time using dapper so I am having trouble getting started. I have a table (dbo.BallData) stored in a database using SQL Server Management Studio:

+-------+-----------+---------------+--------------+-------------+-------------+--------------+---------+
| ID    | COMPANY   | NAME          | WEIGHT (lbs) | CORE        | COVERSTOCK  | SURFACE      | LAYOUT  |
| 1     | Hammer    | True Blood    | 15           | First Blood | Polyester   | 4000 Polish  | NULL    |
| 2     | Columbia  | Eruption Pro  | 15           | Resurgence  | CR300       | 1500 Polish  | NULL    |
+-------+-----------+---------------+--------------+-------------+-------------+--------------+---------+

I would like to display the information as follows. Currently the information is just typed out in HTML:

Company: Hammer
Name: True Blood
Weight (lbs): 15
Core: First Blood
Coverstock: Polyester
Surface: 4000 Polish
Layout:

I am working in C#, but cannot remember how to connect to a sql server database from visual studio. I am just hoping that someone can get started and then I can go from there. I have dapper included in my project. I have a file named BallData.cs where I am trying to connect to the database so that I can pull data out of it. Currently the file is just bare-boned because, like I said, I don't know where to begin. 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;

namespace PracticeApp.App_Code.Entities
{
    public class BallData
    {

    }
}

Any help would be greatly appreciated. Whether it is pointing me to a tutorial or giving some examples to help me get going. Of course, I am not looking for the completed app. I am just looking for help on getting connected and applying dapper so that I can get in the information displayed. Thank you to anyone who is willing to assist me!

Upvotes: 1

Views: 5001

Answers (2)

Marc Gravell
Marc Gravell

Reputation: 1062820

First you need an object model; for example:

public class Foo {
    public int ID {get;set;}
    public string Company {get;set;}
    public string Name {get;set;}
    // ...
}

Then you need a connection:

using(var conn = OpenConnection()) {
    var items = conn.Query<Foo>("select * from YourTable");
}

DbConnection OpenConnection() {
    return new SqlConnection({some connection string});
}

But to do something useful needs a bit more context.

Rendering as html would be best done using a tool like razor:

@foreach(Foo row in Model.Items)
{
    <tr>
      <td>@row.Company</td>
      <td>@row.Name</td>
    </tr>
}

Upvotes: 3

HoboCannibaL
HoboCannibaL

Reputation: 191

Here is how to use the IDE for your connection using a datasource http://msdn.microsoft.com/en-us/library/s4yys16a(v=vs.90).aspx also, I can give you an example of how to connect programmatically, but it's in VB and you can just translate that to c#.

Dim objConnection As New SqlConnection(ConfigurationManager.ConnectionStrings.Item("Connection").ConnectionString)
Dim objSelectCommand As New SqlCommand("SQL statement if you desire it", objConnection)
Dim objDataReader As SqlDataReader = Nothing

objConnection.Open()
objDataReader = objSelectCommand.ExecuteReader

I hope this helps you out.

Upvotes: 1

Related Questions