/// <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); } } } }