c# database connection model

Question

I'm writing a web application in which users can register themselves and log in to a web site.

I've got an ASP.NET web form with a series of text boxes and a submit button. When I did this same thing in PHP, when the submit button was pressed, a User class was created and the contents of the text boxes was passed to an addUser() function that took those parameters and inserted them into the database, and now I'm trying to do the same thing in C#.

Ideally, I want to use an MVC type of design. The controller (the textboxes and button) should gather up the information in the text boxes, and pass it to the model (the User class), which should connect to the database and perform the necessary insert statement. How do I make a class connect to the database? What parameters does it need?

Do I need an SqlDataSource object on the page that I pass to the constructor of the User class? Or something different?

Answer 1

This is a brief excerpt from a sample project I created.

You need to define a model object. Instead of users (because dealing with passwords is a little tricker) this example will be for products in a store.

public class Product
{
    public int Id {get; set;}
    public string Name {get; set;}
    public string Description {get; set;}
    public double Price {get; set;}
}

You need to define the interface for your data layer.

public interface IStoreRepository
    {
    Product GetProductById(int id);

    List<Product> GetAllProducts();

    void AddNewProduct(Product product);

    void UpdateProduct(Product product);

    void DeleteProduct(int id);
    }

Then you'd write a concrete class the implements the data layer interface and performs the actual CRUD (Create Read Update Delete). Here's what an MS SQL Server implementation might look like.

public class MsSqlStoreRepository : IStoreRepository
    {
    private string ConnectionString { get; set; }

    public MsSqlStoreRepository(string connectionString)
        {
        ConnectionString = connectionString;
        }

    private Product GenerateProductFromDataRow(DataRow row)
        {
        return new Product()
        {
            Id = row.Field<int>("id"),
            Name = row.Field<string>("name"),
            Description = row.Field<string>("description"),
            Price = row.Field<double>("price")
        };
        }

    public Product GetProductById(int id)
        {
        var command = new SqlCommand("select id, name, description, price from products where id=@id");
        command.Parameters.AddWithValue("id", id);
        var dt = MsSqlDatabaseHelpers.GetDataTable(command, ConnectionString);
        return dt.AsEnumerable().Select(r => GenerateProductFromDataRow(r)).Single();
        }

    public List<Product> GetAllProducts()
        {
        var command = new SqlCommand("select id, name, description, price from products");
        var dt = MsSqlDatabaseHelpers.GetDataTable(command, ConnectionString);
        return dt.AsEnumerable().Select(r => GenerateProductFromDataRow(r)).ToList();
        }

    public void AddNewProduct(Product product)
        {
        var command = new SqlCommand("insert into products (id, name, description, price) values (@id, @name, @description, @price)");
        command.Parameters.AddWithValue("id", product.Id);
        command.Parameters.AddWithValue("name", product.Name);
        command.Parameters.AddWithValue("description", product.Description);
        command.Parameters.AddWithValue("price", product.Price);
        MsSqlDatabaseHelpers.ExecuteNonQuery(command, ConnectionString);
        }

    public void UpdateProduct(Product product)
        {
        var command = new SqlCommand("update products set name=@name, description=@description, price=@price where id=@id");
        command.Parameters.AddWithValue("id", product.Id);
        command.Parameters.AddWithValue("name", product.Name);
        command.Parameters.AddWithValue("description", product.Description);
        command.Parameters.AddWithValue("price", product.Price);
        MsSqlDatabaseHelpers.ExecuteNonQuery(command, ConnectionString);
        }

    public void DeleteProduct(int id)
        {
        var command = new SqlCommand("delete from products where id=:id");
        command.Parameters.AddWithValue("id", id);
        MsSqlDatabaseHelpers.ExecuteNonQuery(command, ConnectionString);
        }
    }

Now we've followed separation of concerns, and the only remaining thing is to write the actual UI logic. Assuming you're using web forms, it might look like this:

[Inject] //using Ninject to inject MsSqlStoreRepository 
public IStoreRepository Repo { get; set; }

protected void AddProductBtn_Click(object sender, EventArgs e)
{
    Product product = new Product()
    {
        Id = Int32.Parse(IdTB.Text),
        Name = NameTB.Text,
        Description = DescriptionTB.Text,
        Price = Double.Parse(PriceTB.Text)
    };
    Repo.AddNewProduct(product);
    //now clear the form or show success message etc
}

Notice that my webpage has no idea about MsSqlStoreRepository because it solely communicates via the IStoreRepository. Doing so makes absolutely sure that I don't perform any database related stuff in my webpage and that I can freely swap out databases at will by creating a new concrete class that inherits from IStoreRepository. And if you look at the link at the top of my answer, you'll see I have done just that.

Answer 2

Well, in your Razor .cshtml View, you should have a Model for registering users.

This is the point of the Razor Views. To help bind data to your HTML.

It would look like..

@model YourProject.Models.UserRegister

@using (Html.BeginForm()) 
{
    @Html.ValidationSummary(true)
    @Html.AntiForgeryToken()

    <div class="form-horizontal">
        <h4>User Registration</h4>
        <hr />
        @Html.ValidationSummary(true, "", new { @class = "text-danger" })
        <div class="form-group">
            @Html.LabelFor(model => model.UserName, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.UserName, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.UserName, "", new { @class = "text-danger" })
            </div>
        </div>

This would be a replica of your User class.

When the user clicks the submit button on your form, it's going to need a method in the Controller to handle that POST, which is what Josh M. is referring to. Since you used the model's properties above to bind to HTML elements, the user is filling in the properties of your User Model and we are catching it in the Controller.

I wouldn't do any database login, per se, in the Controller, but for simplicity sake, it depends on how you are interacting with a database (Entity Framework, Enterprise Library, etc).

You could call your dbContext from here and use Linq to Commit the changes, or you can write inline SQL to write these to the database, or call a Stored Procedure. You would then have to map these properties of your User class to SQL Parameters.

So, the entire flow:

User fills in Model -> POSTs to Controller -> Map the properties to SQL Parameters OR Commit the changes using your DbContext for Entity Framework.

Answer 3

Your controller should look like this:

[HttpPost]
[ActionName("MyAction")]
public ActionResult MyAction(MyModel model) {
    // ... model has been loaded with data from the form, now work with it.
    // ex: MyDataLayer.Save(model);
}

Set your view's form to POST to "MyAction" and your model will be created and pre-loaded via model binding.