/// <summary>
/// Apply this command to be able run dotCover from test runner environment wiht host protection.
/// </summary>
private void SetTrustWorthyOn(System.Data.Entity.Database checkQueries)
{
string mdfFile = Path.Combine(this.TestContext.DeploymentDirectory, DBF_FILE_NAME);
string strustworthyCommand = string.Format(@"ALTER DATABASE ""{0}"" SET TRUSTWORTHY ON", mdfFile);
checkQueries.ExecuteSqlCommand(strustworthyCommand);
}
/// <summary>
/// EF SQL 语句返回 dataTable
/// </summary>
/// <param name="db"></param>
/// <param name="sql"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public static DataSet ExecuteSqlDataSet(this System.Data.Entity.Database db, string sql, IDbDataParameter[] parameters)
{
using (var conn = new SqlConnection(db.Connection.ConnectionString))
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
var cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = sql;
if (parameters != null && parameters.Length > 0)
{
foreach (var item in parameters)
{
cmd.Parameters.Add(item);
}
}
var adapter = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adapter.Fill(ds);
return(ds);
}
}
public static System.Data.DataTable SqlQuery(this System.Data.Entity.Database database, string sql, System.Data.CommandType commandType, params object[] parameters)
{
var conn = database.Connection;
var connState = conn.State;
if (connState != System.Data.ConnectionState.Open)
{
conn.Open();
}
var cmd = conn.CreateCommand();
cmd.CommandText = sql;
cmd.CommandType = commandType;
cmd.Parameters.AddRange(parameters);
var reader = cmd.ExecuteReader();
var dt = new System.Data.DataTable();
dt.Load(reader);
if (connState != System.Data.ConnectionState.Open)
{
conn.Close();
}
return(dt);
}
private void InsertInvoices(System.Data.Entity.Database db, Guid invoiceId, DateTime invoiceDate, long amount,
int quantity, DateTime?voidedDate = null, bool hasBalance = false, string number = null)
{
// insert invoice
db.ExecuteSqlCommand(Utils.Invoice.GetInsertScript(invoiceId, _clientId, invoiceDate, _userId, voidedDate, number));
// insert invoice item
db.ExecuteSqlCommand(Utils.InvoiceItem.GetInsertScript(Guid.NewGuid(), invoiceId, quantity, amount));
long payment = (quantity * amount);
if (hasBalance)
{
if (quantity == 1)
{
payment = (amount / 2);
}
else
{
payment = amount;
}
}
// insert payment
db.ExecuteSqlCommand(Utils.Payment.GetInsertScript(Guid.NewGuid(), invoiceId, payment));
}
public static IDataReader SqlToReader(this System.Data.Entity.Database database, string sql, params object[] parameters)
{
using (var command = database.Connection.CreateCommand())
{
command.CommandText = sql;
if (null != parameters &&
parameters.Length > 0)
{
var dbParameters = new DbParameter[parameters.Length];
if (parameters.All(p => p is DbParameter))
{
for (var i = 0; i < parameters.Length; i++)
{
dbParameters[i] = (DbParameter)parameters[i];
}
}
else if (!parameters.Any(p => p is DbParameter))
{
var sb = new StringBuilder(sql).Append(" ");
var length = parameters.Length;
for (int i = 0; i < length; i++)
{
sb.Append("@p" + i);
if (i < length - 1)
{
sb.Append(", ");
}
dbParameters[i] = command.CreateParameter();
dbParameters[i].ParameterName = string.Format("p{0}", i);
dbParameters[i].Value = parameters[i] ?? DBNull.Value;
}
command.CommandText = sb.ToString();
}
else
{
throw new InvalidOperationException("couldn't mix dbparameter and other objects");
}
command.Parameters.AddRange(dbParameters);
}
if (command.Connection.State != ConnectionState.Open)
{
command.Connection.Open();
}
var dataReader = command.ExecuteReader();
return(dataReader);
}
}
/// <summary>
/// 获得当前数据库类型
/// </summary>
/// <param name="database"></param>
/// <returns></returns>
public static DataBaseType GetDbType(this System.Data.Entity.Database database)
{
DataBaseType dbType = DbConnections.GetDbTypeByConn(database.Connection);
if (dbType == DataBaseType.Sqlite)
{
SQLiteProviderFactory factory = SQLiteProviderFactory.Instance;
}
return(dbType);
}
/// <summary>
/// 表是否存在
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
public static bool TableExists(this System.Data.Entity.Database database, string tableName)
{
if (database == null || tableName == null)
{
return(false);
}
IDBHelper dbHelper = DBHelper.DBHelper.GetDBHelper(database.Connection);
return(dbHelper.TableExists(tableName));
}
/// <summary>
/// 返回一个数据
/// </summary>
/// <param name="context">数据库上下文</param>
/// <param name="sql">SQL语句或者存储过程名字</param>
/// <param name="sqlType">SQL类型</param>
/// <param name="parameters">数据库参数</param>
/// <returns></returns>
public static object SqlQueryScalar(this System.Data.Entity.Database database, string sql, CommandType sqlType = CommandType.Text, params DbParameter[] parameters)
{
if (database == null || sql == null)
{
return(null);
}
IDBHelper dbHelper = DBHelper.DBHelper.GetDBHelper(database.Connection);
return(dbHelper.QueryScalar(sql, sqlType, parameters));
}
public static DataTable SqlQueryForDataTatable(this System.Data.Entity.Database database, string sql, params DbParameter[] parameters)
{
if (database == null || sql == null)
{
return(null);
}
IDBHelper dbHelper = DBHelper.DBHelper.GetDBHelper(database.Connection);
return(dbHelper.QueryTable(sql, parameters));
}
public static IDbDataParameter MakeInParam(this System.Data.Entity.Database db, string parameterName, DbType dataType, object value)
{
using (var conn = new SqlConnection(db.Connection.ConnectionString))
{
var parameter = conn.CreateCommand().CreateParameter();
parameter.Direction = ParameterDirection.Input;
parameter.SqlValue = value;
parameter.ParameterName = parameterName;
parameter.DbType = dataType;
return(parameter);
}
}
public static bool DataExists(this System.Data.Entity.Database database, string tableName, string key, string value)
{
if (database == null)
{
return(false);
}
IDBHelper dbHelper = DBHelper.DBHelper.GetDBHelper(database.Connection);
Dictionary <string, string> dic = new Dictionary <string, string>();
dic.Add(key, value);
return(dbHelper.Exists(tableName, dic));
}
public ActionResult NoInTrun()
{
string dateStr = Request["dateStr"];
DBModel.ApartmentCashEntities ef = new DBModel.ApartmentCashEntities();
System.Data.Entity.Database db = ef.Database;
using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(db.Connection.ConnectionString))
{
conn.Open();
System.Data.SqlClient.SqlTransaction trans = conn.BeginTransaction();
try
{
string sql2 = string.Format(@"
update a
set a.UserID=b.UserID
from DateWash a
inner join DateWash b on a.DateWashID=b.DateWashID+1
where a.DateStr>'{0}'", dateStr);
System.Data.SqlClient.SqlCommand cmd2 = new System.Data.SqlClient.SqlCommand(sql2, conn, trans);
cmd2.ExecuteNonQuery();
string sql = string.Format("update DateWash set UserID=null where DateStr='{0}'", dateStr);
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(sql, conn, trans);
cmd.ExecuteNonQuery();
trans.Commit();
}
catch (Exception ee)
{
trans.Rollback();
json.success = false;
json.msg = ee.Message;
if (ee.InnerException != null)
{
json.msg += " " + ee.InnerException.Message;
}
}
}
json.success = true;
json.msg = "操作成功";
return(Content(json.ToString()));
}
public ActionResult CreateDateWashPerson()
{
int createCount = Request["createCount"].ToInt();
string [] personList = Request["personList"].Split(',');
string startDate = Request["startDate"];
DBModel.ApartmentCashEntities ef = new DBModel.ApartmentCashEntities();
System.Data.Entity.Database db = ef.Database;
using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(db.Connection.ConnectionString))
{
conn.Open();
System.Data.SqlClient.SqlTransaction trans = conn.BeginTransaction();
try
{
for (int i = 0; i < createCount; i++)
{
string userID = personList[i % personList.Length];
string dateStr = DateTime.Parse(startDate).AddDays(i).ToString("yyyy-MM-dd");
string sql = "update DateWash set UserID=@UserID where DateStr=@DateStr";
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(sql, conn, trans);
cmd.Parameters.AddWithValue("@UserID", userID);
cmd.Parameters.AddWithValue("@DateStr", dateStr);
cmd.ExecuteNonQuery();
}
trans.Commit();
}
catch (Exception ee)
{
trans.Rollback();
json.success = false;
json.msg = ee.Message;
if (ee.InnerException != null)
{
json.msg += " " + ee.InnerException.Message;
}
}
}
json.success = true;
json.msg = "生成成功";
return(Content(json.ToString()));
}
/// <summary>
/// 执行SQL语句
/// </summary>
/// <returns></returns>
public static int SqlExecuteNonQuery(this System.Data.Entity.Database database, string sqlText, params DbParameter[] commandParameters)
{
int n = 0;
if (database == null || sqlText == null)
{
return(n);
}
IDBHelper dbHelper = DBHelper.DBHelper.GetDBHelper(database.Connection);
n = dbHelper.ExecuteNonQuery(sqlText, commandParameters);
return(n);
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">多条SQL语句</param>
public static int ExecuteSqlsTran(this System.Data.Entity.Database database, List <string> SQLStringList, bool failStop = true)
{
int n = 0;
if (database == null || SQLStringList == null || SQLStringList.Count == 0)
{
return(n);
}
IDBHelper dbHelper = DBHelper.DBHelper.GetDBHelper(database.Connection);
n = dbHelper.ExecuteSqlsTran(SQLStringList, failStop);
return(n);
}
/// <summary>
/// Cleans up all tables in test database
/// </summary>
protected void ClearTestLab()
{
const string DELETE_COMMAND = @"DELETE FROM ";
// first clear dependences from both Favorites and groups table because of constraints
System.Data.Entity.Database checkQueries = this.CheckDatabase.Database;
this.SetTrustWorthyOn(checkQueries);
checkQueries.ExecuteSqlCommand(DELETE_COMMAND + "FavoritesInGroup");
checkQueries.ExecuteSqlCommand(DELETE_COMMAND + "History");
checkQueries.ExecuteSqlCommand(DELETE_COMMAND + "Favorites");
checkQueries.ExecuteSqlCommand(DELETE_COMMAND + "BeforeConnectExecute");
checkQueries.ExecuteSqlCommand(DELETE_COMMAND + "Security");
checkQueries.ExecuteSqlCommand(DELETE_COMMAND + "DisplayOptions");
checkQueries.ExecuteSqlCommand(DELETE_COMMAND + "Groups");
checkQueries.ExecuteSqlCommand(DELETE_COMMAND + "CredentialBase");
checkQueries.ExecuteSqlCommand(DELETE_COMMAND + "Credentials");
}
public static void ExecSqlStatement(this System.Data.Entity.Database db, string sql, List <IDbDataParameter> parameters)
{
using (var conn = new SqlConnection(db.Connection.ConnectionString))
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
var cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = sql;
if (parameters != null && parameters.Count > 0)
{
foreach (var item in parameters)
{
cmd.Parameters.Add(item);
}
}
cmd.ExecuteNonQuery();
}
}
/// <summary>
/// EF SQL 语句返回 dataTable
/// </summary>
/// <param name="db"></param>
/// <param name="sql"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public static IDataReader ExecuteReader(this System.Data.Entity.Database db, string sql, IDbDataParameter[] parameters)
{
using (var conn = new SqlConnection(db.Connection.ConnectionString))
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
var cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = sql;
if (parameters != null && parameters.Length > 0)
{
foreach (var item in parameters)
{
cmd.Parameters.Add(item);
}
}
return(cmd.ExecuteReader());
}
}
//public static DataTable ExecuteDbCommand<T>(this System.Data.Entity.Database database, string sql, params object[] parameters)
//{
// var response = new List<T>();
// using (var cmd = database.Connection.CreateCommand()) {
// var properties = typeof(T).GetProperties(BindingFlags.DeclaredOnly | BindingFlags.Instance | BindingFlags.Public);
// cmd.CommandText = sql;
// cmd.Parameters.AddRange(parameters);
// if (cmd.Connection.State != ConnectionState.Open) {
// cmd.Connection.Open();
// }
// using (var dataReader = cmd.ExecuteReader()) {
// while (dataReader.Read()) {
// var row = new Dictionary<string, object>();
// for (var i = 0; i < dataReader.FieldCount; i++) {
// var type = dataReader.GetFieldType(i);
// row.Add(dataReader.GetName(i), TypeValueGetter(() => {
// var val = dataReader.GetValue(i);
// return val == DBNull.Value ? null : val;
// }));
// }
// var instance = Activator.CreateInstance<T>();
// foreach (var column in row.Keys) {
// foreach (var property in properties) {
// if (column == property.Name)
// property.SetValue(instance, row[column]);
// }
// }
// response.Add(instance);
// }
// }
// }
// return response;
//}
public static IEnumerable <dynamic> DynamicListFromSql(this System.Data.Entity.Database Database, string Sql, params object[] parameters)
{
using (var cmd = Database.Connection.CreateCommand())
{
cmd.CommandText = Sql;
if (cmd.Connection.State != ConnectionState.Open)
{
cmd.Connection.Open();
}
if (null != parameters &&
parameters.Length > 0)
{
var dbParameters = new DbParameter[parameters.Length];
if (parameters.All(p => p is DbParameter))
{
for (var i = 0; i < parameters.Length; i++)
{
dbParameters[i] = (DbParameter)parameters[i];
}
}
else if (!parameters.Any(p => p is DbParameter))
{
var sb = new StringBuilder(Sql).Append(" ");
var length = parameters.Length;
for (int i = 0; i < length; i++)
{
sb.Append("@p" + i);
if (i < length - 1)
{
sb.Append(", ");
}
dbParameters[i] = cmd.CreateParameter();
dbParameters[i].ParameterName = string.Format("p{0}", i);
dbParameters[i].Value = parameters[i] ?? DBNull.Value;
}
cmd.CommandText = sb.ToString();
cmd.CommandTimeout = 180;
}
else
{
throw new InvalidOperationException("couldn't mix dbparameter and other objects");
}
cmd.Parameters.AddRange(dbParameters);
}
using (var dataReader = cmd.ExecuteReader())
{
while (dataReader.Read())
{
var row = new ExpandoObject() as IDictionary <string, object>;
for (var fieldCount = 0; fieldCount < dataReader.FieldCount; fieldCount++)
{
row.Add(dataReader.GetName(fieldCount), dataReader[fieldCount]);
}
yield return(row);
}
}
}
}