public void Should_be_able_to_create_a_query()
{
const string sql = "uspDoSomething";
var query1 = new ProcedureQuery(sql);
var query2 = ProcedureQuery.Create(sql);
}
public void Should_be_able_to_create_a_query()
{
const string sql = "uspDoSomething";
var query1 = new ProcedureQuery(sql);
var query2 = ProcedureQuery.Create(sql);
}
public void Should_be_able_prepare_a_query()
{
const string sql = "uspDoSomething";
var guid = Guid.NewGuid();
var mc = new MappedColumn<Guid>("Id", DbType.Guid);
var query = new ProcedureQuery(sql).AddParameterValue(mc, guid);
var dataParameterCollection = new Mock<IDataParameterCollection>();
var dataParameterFactory = new Mock<IDbDataParameterFactory>();
dataParameterFactory.Setup(m => m.Create("@Id", DbType.Guid, guid));
var dataSource = new DataSource("data-source", dataParameterFactory.Object);
var command = new Mock<IDbCommand>();
dataParameterCollection.Setup(m => m.Add(It.IsAny<IDbDataParameter>())).Verifiable();
command.SetupGet(m => m.Parameters).Returns(dataParameterCollection.Object);
command.SetupSet(m => m.CommandText = sql).Verifiable();
command.SetupSet(m => m.CommandType = CommandType.StoredProcedure).Verifiable();
query.Prepare(dataSource, command.Object);
command.VerifyAll();
dataParameterFactory.VerifyAll();
}
/// <summary>
/// Выполнение хранимой процедуры по добавлению нового документа в базу данных
/// </summary>
/// <param Name="session"></param>
/// <param Name="query"></param>
/// <returns></returns>
public static bool ExecuteStorageProcedure(ISession session, ProcedureQuery query)
{
using (var ts = session.BeginTransaction())
{
try
{
var command = new SqlCommand {
Connection = (SqlConnection)session.Connection
};
ts.Enlist(command);
command.CommandType = CommandType.StoredProcedure;
command.CommandText = query.Procedure;
command.Parameters.AddRange(query.Parameters.ToArray());
command.ExecuteNonQuery();
ts.Commit();
return(true);
}
catch (SqlException e)
{
return(false);
}
}
}
public static DataTable QueryList(T obj)
{
string name = obj.GetType().Name + "_Select";
ProcedureQuery.Select(obj);
SqlDataAdapter adapter = new SqlDataAdapter(name, Connected.Connection);
adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
DataTable table = new DataTable();
adapter.Fill(table);
return(table);
}
public bool QueryInserter(T entity)
{
ProcedureQuery.Insert(entity);
SqlCommand comm = new SqlCommand(entity.GetType().Name + "_Insert", Connected.Connection);
comm.CommandType = CommandType.StoredProcedure;
foreach (var item in entity.GetType().GetProperties())
{
if (Tools.FindID(item))
{
continue;
}
if (Tools.IsNull(item.GetValue(entity)))
{
comm.Parameters.AddWithValue("@" + item.Name, item.GetValue(entity));
}
}
return(Commond.ExecuteQuery(comm));
}
public void Should_be_able_prepare_a_query()
{
const string sql = "uspDoSomething";
var guid = Guid.NewGuid();
var mc = new MappedColumn <Guid>("Id", DbType.Guid);
var query = new ProcedureQuery(sql).AddParameterValue(mc, guid);
var dataParameterCollection = new Mock <IDataParameterCollection>();
var command = new Mock <IDbCommand>();
dataParameterCollection.Setup(m => m.Add(It.IsAny <IDbDataParameter>())).Verifiable();
command.SetupGet(m => m.Parameters).Returns(dataParameterCollection.Object);
command.SetupSet(m => m.CommandText = sql).Verifiable();
command.SetupSet(m => m.CommandType = CommandType.StoredProcedure).Verifiable();
command.Setup(m => m.CreateParameter()).Returns(new SqlParameter());
query.Prepare(command.Object);
command.VerifyAll();
}
public static void QueryUpdate(T entity, int id)
{
ProcedureQuery.Update(entity);
SqlCommand command = new SqlCommand(entity.GetType().Name + "_Update", Connected.Connection);
command.CommandType = System.Data.CommandType.StoredProcedure;
foreach (PropertyInfo item in entity.GetType().GetProperties())
{
if (Tools.FindID(item))
{
item.SetValue(entity, id);
}
if (Tools.IsNull(item.GetValue(entity)))
{
command.Parameters.AddWithValue("@" + item.Name, item.GetValue(entity));
}
}
///return Commond.ExecuteQuery(command);
}
public ActionResult Create(Document doc, IEnumerable <HttpPostedFileBase> fileupload)
{
doc.AuthorId = int.Parse(Session["id"].ToString());
using (ISession session = NHibernateHelper.OpenSession())
{
doc.Id = session.Query <Document>().ToList().Count + 1;
foreach (var file in fileupload)
{
if (file != null && file.ContentLength > 0)
{
var extention = System.IO.Path.GetExtension(file.FileName);
var path = System.IO.Path.Combine(Server.MapPath("~/Content/Files/"), doc.NameDocument + extention);
file.SaveAs(path);
ProcedureQuery query = new ProcedureQuery("sp_InsertDocument");
query.AddInt32("id", doc.Id);
query.AddString("name", doc.NameDocument);
query.AddInt32("authorId", doc.AuthorId);
query.AddDateTime("date", doc.Date);
query.AddString("binaryFile", path);
if (!NHibernateHelper.ExecuteStorageProcedure(session, query))
{
return(View("Create"));
}
}
else
{
return(View("Create"));
}
}
}
return(RedirectToAction("Index"));
}
