Witam,
Tak na szybko i poglądowo robisz coś takiego:
namespace FourProgrammersSample
{
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
public class Carabiner : IComparable<Carabiner>, IEquatable<Carabiner>
{
public int Id { get; set; }
public string Value1 { get; set; }
public string Value2 { get; set; }
public int CompareTo(Carabiner other)
{
return this.Id.CompareTo(other.Id);
}
public bool Equals(Carabiner other)
{
return other != null && this.Id.Equals(other.Id);
}
}
interface IGenericRepository<TEntity, in TKey>where TEntity : class
{
void Create(TEntity entity);
TEntity Retrieve(TKey id);
void Update(TEntity entity);
void Delete(TKey id);
IEnumerable<TEntity> List();
}
interface ICarabinerRepository : IGenericRepository<Carabiner, int>
{
}
public sealed class CarabinerRepository : ICarabinerRepository
{
private readonly string connectionString;
public CarabinerRepository(string connectionString)
{
this.connectionString = connectionString;
}
public void Create(Carabiner entity)
{
try
{
using (var connection = new SqlConnection(this.connectionString))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = "INSERT INTO [dbo].[carabiner] VALUES (@id, @value1, @value2)";
command.Parameters.AddWithValue("@id", entity.Id);
command.Parameters.AddWithValue("@value1", entity.Value1);
command.Parameters.AddWithValue("@value2", entity.Value2);
command.ExecuteNonQuery();
}
}
}
catch (Exception exception)
{
Console.WriteLine(exception);
}
}
public Carabiner Retrieve(int id)
{
Carabiner record = null;
try
{
using (var connection = new SqlConnection(this.connectionString))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = "SELECT id, value1, value2 FROM [dbo].[carabiner] WITH (nolock) WHERE id = @id";
command.Parameters.AddWithValue("@id", id);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var value1 = reader.GetString(reader.GetOrdinal("value1"));
var value2 = reader.GetString(reader.GetOrdinal("value2"));
record = new Carabiner { Id = id, Value1 = value1, Value2 = value2 };
}
}
}
}
}
catch (Exception exception)
{
Console.WriteLine(exception);
}
return record;
}
public void Update(Carabiner entity)
{
try
{
using (var connection = new SqlConnection(this.connectionString))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText =
"UPDATE [dbo].[carabiner] SET value1 = @value1, value2 = @value2 WHERE id = @id";
command.Parameters.AddWithValue("@id", entity.Id);
command.Parameters.AddWithValue("@value1", entity.Value1);
command.Parameters.AddWithValue("@value2", entity.Value2);
command.ExecuteNonQuery();
}
}
}
catch (Exception exception)
{
Console.WriteLine(exception);
}
}
public void Delete(int id)
{
try
{
using (var connection = new SqlConnection(this.connectionString))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = "DELETE FROM [dbo].[carabiner] WHERE id = @id";
command.Parameters.AddWithValue("@id", id);
command.ExecuteNonQuery();
}
}
}
catch (Exception exception)
{
Console.WriteLine(exception);
}
}
public IEnumerable<Carabiner> List()
{
var list = new List<Carabiner>();
try
{
using (var connection = new SqlConnection(this.connectionString))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = "SELECT id, value1, value2 FROM [dbo].[carabiner] WITH (nolock)";
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var id = reader.GetInt32(reader.GetOrdinal("id"));
var value1 = reader.GetString(reader.GetOrdinal("value1"));
var value2 = reader.GetString(reader.GetOrdinal("value2"));
list.Add(new Carabiner { Id = id, Value1 = value1, Value2 = value2 });
}
}
}
}
}
catch (Exception exception)
{
Console.WriteLine(exception);
}
return list;
}
}
static class Program
{
static void Main()
{
var connectionString =
@"Data Source=(LocalDB)\v11.0;AttachDbFilename=Database.mdf;Integrated Security=True";
ICarabinerRepository repository = new CarabinerRepository(connectionString);
repository.Create(new Carabiner{Id = 1, Value1 = "CBA", Value2 = "CBA"});
var record = repository.Retrieve(1);
if (record != null)
{
record.Value1 = "ABC";
record.Value2 = "ABC";
repository.Update(record);
}
repository.Delete(1);
repository.Create(new Carabiner { Id = 2, Value1 = "AAA", Value2 = "AAA" });
repository.Create(new Carabiner { Id = 3, Value1 = "AAA", Value2 = "BBB" });
repository.Create(new Carabiner { Id = 4, Value1 = "ABC", Value2 = "CCC" });
var items = repository.List();
foreach (var item in (from item in items where item.Value1.Equals("AAA") select item).ToArray())
{
item.Value2 = "BBB";
repository.Update(item);
}
}
}
}
Pozdrawiam,
mr-owl