public void Add(ArticleReferences entity)
{
if (entity.Refs != null)
{
string sqlProcedure = "AddReference";
CallProcedure(entity, sqlProcedure);
}
}
public IEnumerable <ArticleReferences> List()
{
List <ArticleReferences> articlesRefsList = new List <ArticleReferences>();
string sqlExpression = "SELECT ArticleId, ReferenceId, Name FROM ReferenceToArticle";
using (SqlConnection connection = new SqlConnection(_connectionString))
{
TryOpenConnection(connection);
SqlCommand command = new SqlCommand(sqlExpression, connection);
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
ArticleReferences articleRefs = new ArticleReferences
{
Id = (int)reader["ArticleId"]
};
ArticleReferences article = articlesRefsList.FirstOrDefault(art => art.Id == articleRefs.Id);
if (article != null)
{
article.Refs.Add(new ArticleReference
{
Id = (int)reader["ReferenceId"],
Name = (string)reader["Name"]
});
}
else
{
articlesRefsList.Add(articleRefs);
}
}
}
}
}
return(articlesRefsList);
}
public ArticleReferences GetById(int id)
{
string sqlExpressionReference = "SELECT ReferenceId, Name FROM ReferenceToArticle WHERE ArticleId = @id";
ArticleReferences articlesRefs = new ArticleReferences()
{
Id = id,
Refs = new List <ArticleReference>()
};
using (SqlConnection connection = new SqlConnection(_connectionString))
{
TryOpenConnection(connection);
SqlCommand commandRef = new SqlCommand(sqlExpressionReference, connection);
commandRef.Parameters.Add("@id", SqlDbType.Int);
commandRef.Parameters["@id"].Value = id;
using (SqlDataReader readerRef = commandRef.ExecuteReader())
{
if (readerRef.HasRows)
{
while (readerRef.Read())
{
ArticleReference articleRef = new ArticleReference
{
Id = (int)readerRef["ReferenceId"],
Name = (string)readerRef["Name"]
};
articlesRefs.Refs.Add(articleRef);
}
}
}
}
return(articlesRefs);
}
private void CallProcedure(ArticleReferences articleRefs, string sqlProcedure)
{
using (SqlConnection connection = new SqlConnection(_connectionString))
{
foreach (ArticleReference reference in articleRefs.Refs)
{
TryOpenConnection(connection);
SqlCommand command = new SqlCommand(sqlProcedure, connection)
{
CommandType = CommandType.StoredProcedure
};
SqlParameter idParam = new SqlParameter
{
ParameterName = "@articleId",
Value = articleRefs.Id
};
command.Parameters.Add(idParam);
SqlParameter nameParam = new SqlParameter
{
ParameterName = "@referenceId",
Value = reference.Id
};
command.Parameters.Add(nameParam);
try
{
command.ExecuteNonQuery();
}
catch (SqlException ex)
{
throw ex;
}
}
}
}
public void Edit(ArticleReferences entity)
{
Delete(entity.Id);
Add(entity);
}