/// <summary>
/// Execute parameterized SQL
/// </summary>
/// <returns>Number of rows affected</returns>
public int Execute(string sql, object parms = null, IDbTransaction transaction = null, int?commandTimeout = default(int?), CommandType?commandType = default(CommandType?))
{
using (var cn = this.CreateConnection())
{
//DB.SetConnectionUser(cn, sql);
try
{
return(cn.Execute(sql, parms, transaction, commandTimeout, commandType));
}
catch (System.Data.SqlClient.SqlException ex)
{
throw DataException.WrapSqlException(ex, sql, parms);
}
}
}
/// <summary>
/// Return a sequence of dynamic objects <para />
/// Example:
/// var people = DB.Query("SELECT * FROM Person.Person");
/// foreach(var person in people) Response.WriteLine(person.FirstName); <para />
/// var employees = DB.Query("SELECT * FROM HumanResources.Employee WHERE JobTitle=@jobTitle", new { jobTitle = "Chief Executive Officer" } ); etc..
/// </summary>
public IEnumerable <dynamic> Query(string sql, object parms = null, IDbTransaction transaction = null, bool buffered = true, int?commandTimeout = default(int?), CommandType?commandType = default(CommandType?))
{
using (var cn = this.CreateConnection())
{
//DB.SetConnectionUser(cn, sql);
try
{
return(cn.Query(sql, parms, transaction, buffered, commandTimeout, commandType));
}
catch (System.Data.SqlClient.SqlException ex)
{
throw DataException.WrapSqlException(ex, sql, parms);
}
}
}
/// <summary>
/// Inserts a record into the table and returns a single identity key generated for the insert. <para />
/// Usage:
/// int userIndex = DB.Insert("INSERT INTO Users (UserName, FirstName, LastName) VALUES (@UserName, @FirstName, @LastName)", new { UserName="******", FirstName="etc", LastName="etc" }); <para />
/// The @@IDENTITY (actually SCOPE_IDENTITY()) will be automatically added.
/// </summary>
/// <returns></returns>
public int Insert(string sql, object parms, IDbTransaction transaction = null, bool buffered = true, int?commandTimeout = default(int?), CommandType?commandType = default(CommandType?))
{
using (var conn = this.CreateConnection())
{
//DB.SetConnectionUser(cn, sql);
try
{
return(conn.Query <int>(sql + "; SELECT SCOPE_IDENTITY();", parms, transaction, buffered, commandTimeout, commandType).Single());
}
catch (System.Data.SqlClient.SqlException ex)
{
throw DataException.WrapSqlException(ex, sql, parms);
}
}
}
