Пример #1
0
        /// <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);
            }
        }
Пример #3
0
        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);
        }
Пример #4
0
        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);
            }
        }
Пример #6
0
        /// <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);
        }
Пример #7
0
        /// <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));
        }
Пример #8
0
        /// <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));
        }
Пример #9
0
        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);
            }
        }
Пример #11
0
        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));
        }
Пример #12
0
        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()));
        }
Пример #13
0
        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()));
        }
Пример #14
0
        /// <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);
        }
Пример #15
0
        /// <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);
        }
Пример #16
0
        /// <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);
                    }
                }
            }
        }