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.
