lundi 30 mars 2015

Whats the best approach (design pattern) to access database in C#?

I am a newber in design pattern. Currently I am developing a system where I have a releation DB. What would be the best approach to CRUD from my DB? My current code looks like the follow (C# code):


I defined a inteface with commons functions to all classes.



namespace Model
{
public interface ICommon
{
void insert();
void update();
void delete();
}
}


The Common class (abstract one) implements ICommon interface and few orders methods and attributes.



namespace Model
{
public abstract class Common : ICommon
{
public Guid RecId { set; get; }

public abstract void insert();
public abstract void update();
public abstract void delete();
public abstract List<Common> find();

/// <summary>
/// Insert or update the record
/// </summary>
public void save()
{
if (this.RecId == Guid.Empty)
{
this.insert();
}
else
{
this.update();
}
}
}
}


Then, the proper class (UserTable class for example) extends the Common class and implements the abstracts methods and others particulars attributes.


The way that I am doing my CRUD its from StoresProcedures and SqlParameter, SqlCommand and SqlConnection. Here it is a example:



class CustTableModel : Common
{
public string SerialNumber { set; get; }
public string ApplicationVersion { set; get; }
public string KernelVersion { set; get; }
public string Name { set; get; }
public bool Active { set; get; }

public override void insert()
{
List<SqlParameter> parameters = new List<SqlParameter>();
SqlParameter parameter;

// SerialNumber
parameter = new SqlParameter("@serialNumber", System.Data.SqlDbType.Int);
parameter.Value = this.SerialNumber;
parameters.Add(parameter);

// ApplicationVersion
parameter = new SqlParameter("@applicationVersion", System.Data.SqlDbType.Int);
parameter.Value = this.ApplicationVersion;
parameters.Add(parameter);

// KernelVersion
parameter = new SqlParameter("@kernelVersion", System.Data.SqlDbType.Int);
parameter.Value = this.KernelVersion;
parameters.Add(parameter);

// Name
parameter = new SqlParameter("@name", System.Data.SqlDbType.Int);
parameter.Value = this.Name;
parameters.Add(parameter);

// Active
parameter = new SqlParameter("@active", System.Data.SqlDbType.Bit);
parameter.Value = this.Active;
parameters.Add(parameter);

DBConn.execute("CUSTTABLE_INSERT", parameters); // The code of DBConn is below.
}
}


Just to a better understanding, here it is the DBConn class:



public class DBConn
{
protected SqlConnection sqlConnection;
protected string command { set; get; }
protected List<SqlParameter> parameters { set; get; }

protected void openConnection()
{
this.sqlConnection = new SqlConnection();
this.sqlConnection.ConnectionString = "Data Source=.\\SQLEXPRESS;Initial Catalog=JYL_SOAWS_DB;Integrated Security=True";
this.sqlConnection.Open();
}

protected void closeConnection()
{
if (this.sqlConnection.State == System.Data.ConnectionState.Open)
{
this.sqlConnection.Close();
}
}

/// <summary>
/// Executa o processo no banco.
/// </summary>
/// <returns>Quantidade de registros afetados.</returns>
protected SqlDataReader run()
{
SqlCommand command = new SqlCommand();
SqlDataReader ret;

this.openConnection();

command.CommandType = System.Data.CommandType.StoredProcedure;
command.Connection = this.sqlConnection;
command.CommandText = this.command;

if (this.parameters != null)
{
foreach (SqlParameter parameter in this.parameters)
{
command.Parameters.Add(parameter);
}
}

ret = command.ExecuteReader();

this.closeConnection();

return ret;
}

/// <summary>
/// Interface da classe à outros objetos.
/// </summary>
/// <param name="commandName">Nome da store procedure a ser executada.</param>
/// <param name="parameters">A lista com os parâmetros e valores.</param>
/// <returns>Numero de registros afetados.</returns>
public static SqlDataReader execute(string commandName, List<SqlParameter> parameters = null)
{
DBConn conn = new DBConn();

conn.command = commandName;
conn.parameters = parameters;

return conn.run();
}
}


I am pretty sure that there is a better way.


Could anyone help me? Thanks is advance.


Aucun commentaire:

Enregistrer un commentaire