SAMPLE RECORDS
Query for getting top 10 records based on the company values with duplicate values
select T.Country 'Text', T.Company'Value'
from (
select Country , Company,
dense_rank() over(order by Companydesc) as rn
from TableName
) T
where T.rn <= 10
The repository and unit of work patterns are intended to create an abstraction layer between the data access layer and the business logic layer of an application. Implementing these patterns can help insulate your application from changes in the data store and can facilitate automated unit testing or test-driven development (TDD).
UnitOfwork
public class UnitOfWork : IUnitOfWork
{
public DataBaseEntities _context;
#region Constructor
public UnitOfWork()
{
_context = new DataBaseEntities ();
}
#endregion
#region Implement Inteface Methods
public void Commit()
{
_context.SaveChanges();
}
public void CommitAndRefreshChanges()
{
bool saveFailed = false;
do
{
try
{
_context.SaveChanges();
saveFailed = false;
}
catch (DbUpdateConcurrencyException ex)
{
saveFailed = true;
ex.Entries.ToList()
.ForEach(entry =>
{
entry.OriginalValues.SetValues(entry.GetDatabaseValues());
});
}
} while (saveFailed);
}
public void RollbackChanges()
{
// set all entities in change tracker
// as 'unchanged state'
_context.ChangeTracker.Entries()
.ToList()
.ForEach(entry => entry.State = System.Data.Entity.EntityState.Unchanged);
}
public IEnumerable ExecuteQuery(string sqlQuery, params object[] parameters)
{
return _context.Database.SqlQuery(sqlQuery, parameters);
}
public int ExecuteCommand(string sqlCommand, params object[] parameters)
{
return _context.Database.ExecuteSqlCommand(sqlCommand, parameters);
}
private bool _disposed = false;
protected virtual void Dispose(bool disposing)
{
if (!this._disposed)
{
if (disposing)
{
_context.Dispose();
}
}
this._disposed = true;
}
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}
#endregion
public List ExecuteQueryForDropdown(string sqlQuery)
{
var result = db.Database.SqlQuery(sqlQuery).ToList();
return result;
}
public IQueryable Filter(Expression<Func> filter, string orderByColumnName, bool ascending, out int total, int index = 0, int size = 50)
{
int skipCount = index * size;
var resetSet = filter != null ? table.Where(filter).AsQueryable() : table.AsQueryable();
total = resetSet.Count();
if (!string.IsNullOrEmpty(orderByColumnName))
{
resetSet = resetSet.OrderBy(orderByColumnName, ascending);
resetSet = skipCount == 0 ? resetSet.Take(size) : resetSet.Skip(skipCount).Take(size);
}
return resetSet.AsQueryable();
}
#region Repositories
#endregion
}
Interface of UnitOfWork
public interface IUnitOfWork : IDisposable
{
///
/// Commit all changes made in a container.
///
void Commit();
///
/// If the entity have fixed properties and any optimistic concurrency problem exists,
/// then 'client changes' are refreshed - Client wins
///
void CommitAndRefreshChanges();
///
/// Rollback tracked changes. See references of UnitOfWork pattern
///
void RollbackChanges();
///
/// Execute specific query with underliying persistence store
///
/// Entity type to map query results
///
/// SELECT idCustomer,Name FROM dbo.[Customers] WHERE idCustomer > {0}
///
///
///
///
IEnumerable ExecuteQuery(string sqlQuery, params object[] parameters);
///
/// Execute arbitrary command into underliying persistence store
///
///
/// Command to execute
///
/// SELECT idCustomer,Name FROM dbo.[Customers] WHERE idCustomer > {0}
///
///
/// A vector of parameters values
/// The number of affected records
int ExecuteCommand(string sqlCommand, params object[] parameters);
List ExecuteQueryForDropdown(string sqlQuery);
IQueryable Filter(Expression<Func> filter, string orderByColumnName, bool ascending, out int total, int index = 0, int size = 20);
}
Generic Repository
public class RepositoryBase where T : class
{
internal TSPEntities _dataContext;
internal DbSet _dbset;
public RepositoryBase(TSPEntities context)
{
this._dataContext = context;
this._dbset = context.Set();
}
///
/// Paging Query with filter
///
///
///
///
///
public virtual List Get(Expression<Func> filter = null, Func<IQueryable, IOrderedQueryable> orderBy = null, string includeProperties = "")
{
IQueryable query = _dbset;
if (filter != null)
{
query = query.Where(filter);
}
foreach (var includeProperty in includeProperties.Split
(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries))
{
query = query.Include(includeProperty);
}
if (orderBy != null)
{
return orderBy(query).ToList();
}
else
{
return query.ToList();
}
}
///
/// Get selected records with paging
///
///
///
///
///
///
///
public virtual IEnumerable GetPaged(int pageIndex, int pageCount, System.Linq.Expressions.Expression<Func> orderByExpression, bool ascending)
{
var set = _dbset;
if (ascending)
{
return set.OrderBy(orderByExpression)
.Skip(pageCount * pageIndex)
.Take(pageCount);
}
else
{
return set.OrderByDescending(orderByExpression)
.Skip(pageCount * pageIndex)
.Take(pageCount);
}
}
public virtual void Add(T entity)
{
_dbset.Add(entity);
}
public virtual void Delete(T entity)
{
_dbset.Remove(entity);
}
public virtual void Delete(Expression<Func> where)
{
IEnumerable objects = _dbset.Where(where).AsEnumerable();
foreach (T obj in objects)
_dbset.Remove(obj);
}
public virtual T GetById(long id)
{
return _dbset.Find(id);
}
public virtual T GetById(string id)
{
return _dbset.Find(id);
}
public virtual IEnumerable GetAll()
{
return _dbset.ToList();
}
public virtual IQueryable GetAllQueryable()
{
return _dbset.AsQueryable();
}
public virtual IEnumerable GetMany(Expression<Func> where)
{
return _dbset.Where(where).ToList();
}
public virtual IQueryable GetManyQueryable(Expression<Func> where)
{
return _dbset.AsQueryable().Where(where);
}
public T Get(Expression<Func> where)
{
return _dbset.Where(where).FirstOrDefault();
}
public IQueryable Filter(Expression<Func> filter, string orderByColumnName, bool ascending, out int total, int index = 0, int size = 50)
{
int skipCount = index * size;
var resetSet = filter != null ? table.Where(filter).AsQueryable() : table.AsQueryable();
total = resetSet.Count();
if (!string.IsNullOrEmpty(orderByColumnName))
{
resetSet = resetSet.OrderBy(orderByColumnName, ascending);
resetSet = skipCount == 0 ? resetSet.Take(size) : resetSet.Skip(skipCount).Take(size);
}
return resetSet.AsQueryable();
}
}
protected void btnExport_Click(object sender, EventArgs e)
{
DataTable dtTable = new DataTable();
DataRow dtRow;
dtTable.Columns.Add("SNo", typeof(int));
dtTable.Columns.Add("Address", typeof(string));
for (int i = 0; i <= 9; i++)
{
dtRow = dtTable.NewRow();
dtRow[0] = i;
dtRow[1] = "Address " + i.ToString();
dtTable.Rows.Add(dtRow);
}
Response.ContentType = "Application/x-msexcel";
Response.AddHeader("content-disposition", "attachment;filename=test.csv");
Response.Write(ExportToCSVFile(dtTable));
Response.End();
}
private string ExportToCSVFile(DataTable dtTable)
{
StringBuilder sbldr = new StringBuilder();
if (dtTable.Columns.Count != 0)
{
foreach (DataColumn col in dtTable.Columns)
{
sbldr.Append(col.ColumnName + ',');
}
sbldr.Append("\r\n");
foreach (DataRow row in dtTable.Rows)
{
foreach (DataColumn column in dtTable.Columns)
{
sbldr.Append(row[column].ToString() + ',');
}
sbldr.Append("\r\n");
}
}
return sbldr.ToString();
}
}