示例#1
0
 /// <summary>
 /// 执行查询返回DataTable
 /// </summary>
 /// <param name="sql">Sql语句</param>
 /// <returns>成功返回DataTable,失败则返回 null</returns>
 static public System.Data.DataTable ExecuteQueryToDataTable(string sql, System.Data.Common.DbConnection Conn)
 {
     if (Conn is System.Data.SQLite.SQLiteConnection)
     {
         var cmd = Conn.CreateCommand() as System.Data.SQLite.SQLiteCommand;
         cmd.CommandText    = sql;
         cmd.CommandTimeout = 180;
         var       da = new System.Data.SQLite.SQLiteDataAdapter(cmd);
         DataTable dt = new DataTable();
         da.Fill(dt);
         return(dt);
     }
     if (Conn is OracleConnection)
     {
         var cmd = Conn.CreateCommand() as OracleCommand;
         cmd.CommandText    = sql;
         cmd.CommandTimeout = 180;
         var       da = new OracleDataAdapter(cmd);
         DataTable dt = new DataTable();
         da.Fill(dt);
         return(dt);
     }
     else
     {
         System.Data.DataTable   dt     = new System.Data.DataTable();
         System.Data.IDataReader reader = ExecuteQuery(sql, Conn);
         dt.Load(reader);
         return(dt);
     }
 }//ExecuteQueryToDataTable(string sql)
示例#2
0
        } //Execute(string sql)

        /// <summary>
        /// 执行Sql语句
        /// </summary>
        /// <param name="sql">Sql语句</param>
        /// <param name="Conn">数据库连接对象</param>
        /// <param name="param">参数</param>
        /// <returns>返回受影响行数</returns>
        public static int Execute(string sql, System.Data.Common.DbConnection Conn, System.Data.Common.DbParameter[] param)
        {
            if (Conn == null)
            {
                Dbhelper.ErrLog(
                    "Dbhelper.Execute(string sql, System.Data.Common.DbConnection Conn, System.Data.Common.DbParameter[] param):连接对象为空!");
                return(0);
            }
            System.Data.IDbCommand cmd = Conn.CreateCommand();
            cmd.CommandText = sql;
            for (int i = 0; i < param.Length; i++)
            {
                cmd.Parameters.Add(param[i]);
            }
            try
            {
                return(cmd.ExecuteNonQuery());
            }
            catch (Exception ex)
            {
                Dbhelper.ErrLog(
                    "Dbhelper.Execute(string sql, System.Data.Common.DbConnection Conn, System.Data.Common.DbParameter[] param):" +
                    ex.Message + "/nsql=" + sql);
                return(0);
            }
        } //Execute(string sql,System.Data.IDataParameter[] param)
示例#3
0
 public void RunNativeWithMapper()
 {
     using (var cmd = _connection.CreateCommand())
     {
         cmd.CommandText = sql;
         cmd.CommandType = CommandType.Text;
         using (var reader = cmd.ExecuteReader())
         {
             while (reader.Read())
             {
                 var customer = new Customer();
                 ObjectFactory.Map(reader, customer);
             }
         }
     }
 }
        public static string GenerateAllTables(this System.Data.Common.DbConnection connection, GeneratorBehavior generatorBehavior = GeneratorBehavior.Default)
        {
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }

            var conneciontName = connection.GetType().Name.ToLower();
            var tables         = new List <string>();
            var sql            = generatorBehavior.HasFlag(GeneratorBehavior.View) ? TableSchemaSqls[conneciontName].Split("where")[0] : TableSchemaSqls[conneciontName];

            using (var command = connection.CreateCommand(sql))
                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        tables.Add(reader.GetString(0));
                    }
                }

            var sb = new StringBuilder();

            sb.AppendLine("namespace Models { ");
            tables.ForEach(table => sb.Append(connection.GenerateClass(
                                                  string.Format(QuerySqls[conneciontName], table), table, generatorBehavior: generatorBehavior
                                                  )));
            sb.AppendLine("}");
            return(sb.ToString());
        }
示例#5
0
        internal IDbCommand SetupCommand(IDbConnection cnn, Action <IDbCommand, object> paramReader)
        {
            var cmd  = cnn.CreateCommand();
            var init = GetInit(cmd.GetType());

            init?.Invoke(cmd);
            if (Transaction != null)
            {
                cmd.Transaction = Transaction;
            }
            cmd.CommandText = CommandText;
            if (CommandTimeout.HasValue)
            {
                cmd.CommandTimeout = CommandTimeout.Value;
            }
            else if (SqlMapper.Settings.CommandTimeout.HasValue)
            {
                cmd.CommandTimeout = SqlMapper.Settings.CommandTimeout.Value;
            }
            if (CommandType.HasValue)
            {
                cmd.CommandType = CommandType.Value;
            }
            paramReader?.Invoke(cmd, Parameters);
            return(cmd);
        }
示例#6
0
        public static byte[] GetFlagFromDb(string iso)
        {
            byte[] baResult = null;

            using (System.Data.Common.DbConnection con = GetConnection())
            {
                if (con.State != System.Data.ConnectionState.Open)
                {
                    con.Open();
                }

                using (System.Data.Common.DbCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = "SELECT SUBSTRING(b64, 23, 40000000000000000) AS b64 FROM flags WHERE flag = @iso";

                    System.Data.Common.DbParameter country = cmd.CreateParameter();
                    country.ParameterName = "@iso";
                    country.Value         = iso;
                    country.DbType        = System.Data.DbType.String;
                    cmd.Parameters.Add(country);

                    object obj = cmd.ExecuteScalar();
                    string b64 = System.Convert.ToString(obj);
                    baResult = System.Convert.FromBase64String(b64);
                } // End Using cmd

                if (con.State != System.Data.ConnectionState.Closed)
                {
                    con.Close();
                }
            } // End Using con

            return(baResult);
        }
示例#7
0
        public virtual void InsertUpdateDataTable(string tableSchema, string tableName, System.Data.DataTable dt)
        {
            string strSQL = "SELECT * FROM ";

            if (tableSchema != null)
            {
                strSQL += this.QuoteObjectIfNecessary(tableSchema) + ".";
            }

            strSQL += this.QuoteObjectIfNecessary(tableName) + " WHERE (1 = 2) ";

            using (System.Data.Common.DbConnection connection = this.Connection)
            {
                using (System.Data.Common.DbDataAdapter daInsertUpdate = this.m_factory.CreateDataAdapter())
                {
                    using (System.Data.Common.DbCommand cmdSelect = connection.CreateCommand())
                    {
                        cmdSelect.CommandText = strSQL;

                        System.Data.Common.DbCommandBuilder cb = this.m_factory.CreateCommandBuilder();
                        cb.DataAdapter = daInsertUpdate;

                        daInsertUpdate.SelectCommand = cmdSelect;
                        daInsertUpdate.InsertCommand = cb.GetInsertCommand();
                        daInsertUpdate.UpdateCommand = cb.GetUpdateCommand();
                        daInsertUpdate.DeleteCommand = cb.GetDeleteCommand();

                        daInsertUpdate.Update(dt);
                    } // End Using cmdSelect
                }     // End Using daInsertUpdate
            }         // End Using connection
        }             // End Sub InsertUpdateDataTable
示例#8
0
        } //Execute(string sql,System.Data.IDataParameter[] param)

        /// <summary>
        /// 执行一个事务
        /// </summary>
        /// <param name="sqls">Sql语句组</param>
        /// <returns>成功时返回true</returns>
        public static bool ExecuteTrans(string[] sqls, System.Data.Common.DbConnection Conn)
        {
            System.Data.IDbTransaction myTrans;
            if (Conn == null)
            {
                Dbhelper.ErrLog("Dbhelper.ExecuteTrans(string[] sqls):连接对象为空!");
                return(false);
            }
            System.Data.IDbCommand cmd = Conn.CreateCommand();
            myTrans         = Conn.BeginTransaction();
            cmd.Transaction = myTrans;
            try
            {
                foreach (string sql in sqls)
                {
                    if (sql != null)
                    {
                        cmd.CommandText = sql;

                        cmd.ExecuteNonQuery();
                    }
                }
                myTrans.Commit();
            }
            catch (Exception ex)
            {
                myTrans.Rollback();
                Dbhelper.ErrLog("Dbhelper.ExecuteTrans(string[] sqls):" + ex.Message);
                return(false);
            }
            return(true);
        } //Execute(string sql)
示例#9
0
        // AnySqlWebAdmin.Code.Trash.PgIntegratedSecurityTest.TestNpgSql();
        public static void TestNpgSql()
        {
            var a = new { Test = 5, Result = "Success" };
            var b = new { Test = 3, Result = "foo" };
            var c = new { Test1 = 3, Result = "foo" };

            System.Type t = a.GetType();
            System.Console.WriteLine(t);

            if (object.ReferenceEquals(a.GetType(), b.GetType()))
            {
                System.Console.WriteLine("Two anony = equal");
            }


            Npgsql.NpgsqlConnectionStringBuilder csb = new Npgsql.NpgsqlConnectionStringBuilder();

            csb.Database = "osm_test"; // must be set

            csb.Host = "localhost";
            // csb.Host = "127.0.0.1"; // doesn't work
            // csb.Host = System.Environment.MachineName; // doesn't work
            csb.Port = 5432;

            csb.IntegratedSecurity = true;
            csb.Username           = System.Environment.UserName; // Works when user exists
            // csb.Username = "******"; // works as root

            bool   ret = false;
            string sql = "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'public'; ";

            using (System.Data.Common.DbConnection conn = Npgsql.NpgsqlFactory.Instance.CreateConnection())
            {
                conn.ConnectionString = csb.ConnectionString;

                if (conn.State != System.Data.ConnectionState.Open)
                {
                    conn.Open();
                }

                using (System.Data.Common.DbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;

                    int?foo = (int?)cmd.ExecuteScalar();

                    if (foo.HasValue)
                    {
                        ret = foo.Value != 0;
                    }
                } // End Using cmd

                if (conn.State != System.Data.ConnectionState.Closed)
                {
                    conn.Close();
                }
            } // End Using conn

            System.Console.WriteLine(ret);
        } // End Sub TestNpgSql
示例#10
0
        }//ExecuteQueryToDataTable(string sql)

        /// <summary>
        /// 执行查询返回DataReader
        /// </summary>
        /// <param name="sql">Sql语句</param>
        /// <param name="Conn">连接对象</param>
        /// <returns>成功时返回Reader对象,失败时返回null</returns>
        static public System.Data.IDataReader ExecuteQuery(string sql, System.Data.Common.DbConnection Conn)
        {
            System.Data.IDataReader reader = null;
            if (Conn == null)
            {
                return(null);
            }
            try
            {
                if (Conn.State == System.Data.ConnectionState.Closed)
                {
                    Conn.Open();
                }
                System.Data.IDbCommand cmd = Conn.CreateCommand();
                cmd.CommandText = sql;
                reader          = cmd.ExecuteReader();
                return(reader);
            }
            catch (Exception ex)
            {
                if (ex.Message.Contains("死锁"))
                {
                    WriteLog(ex.Message + " 再做列锁循环!ExecuteQuery");
                    System.Threading.Thread.Sleep(200);
                    return(ExecuteQuery(sql, Conn));
                }
                else
                {
                    DBCoreHelper.ErrLog("DBCoreHelper.ExecuteQuery(string sql, System.Data.Common.DbConnection Conn):" + ex.Message + ";  \n sql =" + sql);
                }
                return(null);
            }
        }//ExecuteQuery(string sql)
示例#11
0
        } //ExecuteQueryToDataTable(string sql)

        /// <summary>
        /// 执行查询返回DataReader
        /// </summary>
        /// <param name="sql">Sql语句</param>
        /// <param name="Conn">连接对象</param>
        /// <returns>成功时返回Reader对象,失败时返回null</returns>
        public static System.Data.IDataReader ExecuteQuery(string sql, System.Data.Common.DbConnection Conn, System.Data.Common.DbParameter[] param = null)
        {
            System.Data.IDataReader reader = null;
            if (Conn == null)
            {
                return(null);
            }
            try
            {
                if (Conn.State == System.Data.ConnectionState.Closed)
                {
                    Conn.Open();
                }
                System.Data.IDbCommand cmd = Conn.CreateCommand();
                cmd.CommandText = sql;
                if (param != null)
                {
                    for (int i = 0; i < param.Length; i++)
                    {
                        cmd.Parameters.Add(param[i]);
                    }
                }

                reader = cmd.ExecuteReader();
                return(reader);
            }
            catch (Exception ex)
            {
                Dbhelper.ErrLog("Dbhelper.ExecuteQuery(string sql, System.Data.Common.DbConnection Conn):" + ex.Message);
                return(null);
            }
        } //ExecuteQuery(string sql)
示例#12
0
        private void UpdateWrongValues(System.Data.Common.DbConnection connection, SqlServerValidationToolkitContext ctx)
        {
            string q = CompiledQuery;
            var    c = connection.CreateCommand();

            c.CommandText = q;
            var reader = c.ExecuteReader();

            List <WrongValue> correctedWrongValues = Validation_WrongValue.ToList();

            while (reader.Read())
            {
                //the query returns the id of the invalid value and the errortype-id
                int invalidValueId = reader.GetInt32(0);

                string errorTypeCode = reader.GetString(1);


                WrongValue existingWrongValue = Validation_WrongValue
                                                .SingleOrDefault(wv =>
                                                                 wv.Errortype.CodeForValidationQueries == errorTypeCode
                                                                 &&
                                                                 wv.Id == invalidValueId
                                                                 );

                string value = GetValue(invalidValueId, connection);

                if (existingWrongValue == null)
                {
                    ErrorType errorType = ctx.Errortypes.Where(et => et.CodeForValidationQueries == errorTypeCode).SingleOrDefault();

                    if (errorType == null) //errorType should exist
                    {
                        throw new Exception("ErrorType not found for code '" + errorTypeCode + "'");
                    }

                    WrongValue wrongValue = new WrongValue()
                    {
                        Errortype = errorType,
                        Id        = invalidValueId,
                        Value     = value
                    };
                    Validation_WrongValue.Add(wrongValue);
                }
                else
                {
                    existingWrongValue.Value = value;
                    correctedWrongValues.Remove(existingWrongValue);
                }
            }

            _log.Info(string.Format("{0} wrong values are corrected", correctedWrongValues.Count));
            foreach (var wvCorrected in correctedWrongValues)
            {
                wvCorrected.Is_Corrected = true;
            }
        }
示例#13
0
        /// <summary>
        /// 更新 Apq_Tables, Apq_Columns 表内容
        /// </summary>
        /// <param name="DBName">目标数据库名</param>
        /// <returns>影响行数</returns>
        public int Refresh(string DBName)
        {
            #region 参数检测
            if (string.IsNullOrEmpty(DBName))
            {
                throw new ArgumentNullException("DBName");
            }
            #endregion

            System.Data.Common.DbCommand    Command       = Connection.CreateCommand();
            Apq.Data.Common.DbCommandHelper CommandHelper = new Common.DbCommandHelper(Command);
            CommandHelper.AddParameter("@Apq_Tables", Apq_Tables);
            Command.CommandText = "SELECT OBJECT_ID( @Apq_Tables )";

            Common.DbConnectionHelper.Open(Connection);
            #region Apq_Tables
            if (System.Convert.IsDBNull(Command.ExecuteScalar()))
            {
                Command.CommandText = string.Format(Sqls.Apq_TablesCreate,
                                                    Apq.Data.SqlClient.Common.EncodeString(Apq_Tables),
                                                    Apq.Data.SqlClient.Common.EncodeString(Apq_Columns),
                                                    Apq.Data.SqlClient.Common.EncodeString(DBName)
                                                    );
                Command.ExecuteNonQuery();
            }

            Command.CommandText = string.Format(Sqls.Apq_TablesUpdate,
                                                Apq.Data.SqlClient.Common.EncodeString(Apq_Tables),
                                                Apq.Data.SqlClient.Common.EncodeString(Apq_Columns),
                                                Apq.Data.SqlClient.Common.EncodeString(DBName)
                                                );
            Command.ExecuteNonQuery();
            #endregion

            #region Apq_Columns
            CommandHelper.AddParameter("@Columns", Apq_Columns);
            Command.CommandText = "SELECT OBJECT_ID( @Columns )";
            if (System.Convert.IsDBNull(Command.ExecuteScalar()))
            {
                Command.CommandText = string.Format(Sqls.Apq_ColumnsCreate,
                                                    Apq.Data.SqlClient.Common.EncodeString(Apq_Tables),
                                                    Apq.Data.SqlClient.Common.EncodeString(Apq_Columns),
                                                    Apq.Data.SqlClient.Common.EncodeString(DBName)
                                                    );
                Command.ExecuteNonQuery();
            }

            Command.CommandText = string.Format(Sqls.Apq_ColumnsUpdate,
                                                Apq.Data.SqlClient.Common.EncodeString(Apq_Tables),
                                                Apq.Data.SqlClient.Common.EncodeString(Apq_Columns),
                                                Apq.Data.SqlClient.Common.EncodeString(DBName)
                                                );
            return(Command.ExecuteNonQuery());

            #endregion
        }
示例#14
0
        private async Task SubscribeQuestionAnswerAsync(Guid questionId, System.Data.Common.DbConnection connection)
        {
            var command = connection.CreateCommand();

            command.CommandText = $"SELECT Id FROM Answers WHERE QuestionId = '{questionId.ToString()}';";
            var scoreDependency = new SqlDependency((SqlCommand)command);

            scoreDependency.OnChange += OnAnswerAdded;
            SqlDependency.Start(configuration.GetConnectionString("SqlServer"));
            _ = await command.ExecuteReaderAsync();
        }
示例#15
0
        /// <summary>
        /// Insert Data
        /// </summary>
        /// <param name="pFileString">File String</param>
        /// <param name="pSelectSQL">Select Statement</param>
        /// <param name="pDataRows">DataRows</param>
        /// <returns>bool</returns>
        public static bool InsertData(string pFileString, string pSelectSQL, System.Data.DataRow[] pDataRows)
        {
            bool result = false;

            System.Data.Common.DbConnection     Conn    = null;
            System.Data.Common.DbDataAdapter    Adapter = null;
            System.Data.Common.DbCommandBuilder builder = null;
            System.Data.Common.DbCommand        cmd     = null;
            DataSet   dataSet = new DataSet();
            DataTable Temp    = new DataTable();

            string connString = ParseConnectionString(pFileString);

            if (DataSource != null)
            {
                IDbDriver driver = DataSource.CreateDatabaseObject(new System.Data.Common.DbConnectionStringBuilder());
                driver.ConnectionString = connString;
                Conn    = (System.Data.Common.DbConnection)driver.GetConnection();
                Adapter = driver.GetDbAdapter(pSelectSQL);
                Adapter.FillSchema(dataSet, SchemaType.Source);
                Adapter.Fill(Temp);
                builder             = driver.GetDbCommandBuilder(Adapter);
                builder.QuotePrefix = "[";
                builder.QuoteSuffix = "]";

                try
                {
                    Conn.Open();

                    //cmd = builder.GetInsertCommand(true);
                    cmd = Conn.CreateCommand();
                    cmd.CommandTimeout = 1500;

                    //builder.GetInsertCommand((true);

                    foreach (DataRow R in pDataRows)
                    {
                        //string SQL = GetInsertSQL(pSelectSQL.Substring(pSelectSQL.LastIndexOf (" From ") + 6, pSelectSQL.Length - pSelectSQL.LastIndexOf(" From ") - 6),R);
                        string SQL = GetInsertSQL(R.Table.TableName, R);
                        cmd.CommandText = SQL;
                        cmd.ExecuteNonQuery();
                    }
                }
                finally
                {
                    Conn.Close();
                }
            }

            result = true;
            return(result);
        }
示例#16
0
        public System.Data.Common.DbCommand GetCommand(System.Data.Common.DbConnection con)
        {
            if (this.m_cmd != null)
            {
                this.m_cmd.Connection = con;
                return(this.m_cmd);
            }

            System.Data.Common.DbCommand cmd = con.CreateCommand();
            cmd.CommandText = this.m_sql;

            return(cmd);
        }
示例#17
0
        /// <summary>
        /// Returns the value of the column for the id
        /// </summary>
        private string GetValue(int invalidValueId, System.Data.Common.DbConnection connection)
        {
            string selectValueSqlFormat = "SELECT [{0}] FROM [{1}] WHERE [{2}]={3}";
            string selectValueSql       = string.Format(selectValueSqlFormat,
                                                        Column.Name,
                                                        Column.Source.Name,
                                                        Column.Source.IdColumnName,
                                                        invalidValueId);
            var c = connection.CreateCommand();

            c.CommandText = selectValueSql;
            var result = c.ExecuteScalar();

            return(result.ToString());
        }
        /// <summary>
        ///
        /// </summary>
        /// <param name="pFileString"></param>
        /// <param name="pTableName"></param>
        /// <param name="pIsConnectionString"></param>
        /// <returns></returns>
        public override bool CheckDatabaseExistance(string pFileString, string pTableName, bool pIsConnectionString = false)
        {
            System.Data.Common.DbConnection Conn = null;
            //System.Data.Common.DbDataAdapter Adapter = null;
            System.Data.DataTable DataTable = new System.Data.DataTable();
            bool result = false;

            string connString = pFileString;

            string[] restrictions = new string[] { null, null, pTableName };

            if (DataSource != null)
            {
                IDbDriver driver = this;
                driver.ConnectionString = connString;
                Conn = (System.Data.Common.DbConnection)driver.GetConnection();
                try
                {
                    Conn.Open();
                    Conn.ChangeDatabase("master");

                    System.Data.Common.DbCommand cmd = Conn.CreateCommand();
                    cmd.CommandText = "SELECT database_id FROM sys.databases WHERE Name = @name";
                    System.Data.Common.DbParameter parameter = cmd.CreateParameter();
                    parameter.ParameterName = "@name";
                    parameter.Value         = driver.DbName;
                    cmd.Parameters.Add(parameter);

                    object ret = cmd.ExecuteScalar();
                    if (ret != null)
                    {
                        int databaseID = (int)ret;
                        result = databaseID > 0;
                    }
                }
                catch (Exception ex)
                {
                    // do nothing
                }
                finally
                {
                    Conn.Close();
                }
            }

            return(result);
        }
示例#19
0
        //public static System.Data.DataTable ExecuteStoredProcedure(this Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade databaseFacade
        // )
        //{
        //    return ExecuteStoredProcedure(databaseFacade, "foo", ("abc", "def"), ("abc", 5));
        //}


        public static System.Data.DataTable ExecuteStoredProcedure(
            this Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade databaseFacade,
            string procedureName,
            params System.ValueTuple <string, object>[] parameters
            )
        {
            System.Data.DataTable dt = new System.Data.DataTable();

            // using (System.Data.Common.DbConnection con = databaseFacade.GetDbConnection())
            System.Data.Common.DbConnection con = databaseFacade.GetDbConnection();
            {
                using (System.Data.Common.DbCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = procedureName;
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;

                    if (parameters != null)
                    {
                        for (int i = 0; i < parameters.Length; ++i)
                        {
                            System.Data.Common.DbParameter p = cmd.CreateParameter();
                            p.ParameterName = parameters[i].Item1;
                            p.Value         = parameters[i].Item2;
                            p.DbType        = GetDbType(p.Value);

                            if (p.Value == null)
                            {
                                p.Value = System.DBNull.Value;
                            } // End if (p.Value == null)

                            cmd.Parameters.Add(p);
                        } // Next i
                    }     // End if (parameters != null)


                    // cmd.ExecuteNonQuery();
                    // cmd.ExecuteReader();
                    using (System.Data.Common.DbDataAdapter da = System.Data.Common.ProviderExtensions.CreateDataAdapter(con, cmd))
                    {
                        da.Fill(dt);
                    } // End Using da
                }     // End Using cmd
            }         // End Using con

            return(dt);
        } // End Function ExecuteStoredProcedure
示例#20
0
        private void GetView(MyBlogCore.SqlFactory factory, string viewPath)
        {
            string query = @"
SELECT Content, LastModified FROM Views WHERE Location = @Path; 
UPDATE Views SET LastRequested = GetUtcDate() WHERE Location = @Path; 
";

            try
            {
                using (System.Data.Common.DbConnection cnn = factory.Connection)
                {
                    using (System.Data.Common.DbCommand cmd = cnn.CreateCommand())
                    {
                        cmd.CommandText = query;
                        cmd.AddWithValue("@Path", viewPath);

                        if (cnn.State != System.Data.ConnectionState.Open)
                        {
                            cnn.Open();
                        }

                        using (System.Data.Common.DbDataReader reader = cmd.ExecuteReader())
                        {
                            _exists = reader.HasRows;
                            if (_exists)
                            {
                                reader.Read();
                                _viewContent  = System.Text.Encoding.UTF8.GetBytes(reader["Content"].ToString());
                                _lastModified = System.Convert.ToDateTime(reader["LastModified"]);
                            } // End if (_exists)
                        }     // End Using reader
                    }         // End Using cmd

                    if (cnn.State != System.Data.ConnectionState.Closed)
                    {
                        cnn.Close();
                    }
                } // End Using cnn
            }
            catch (System.Exception ex)
            {
                // if something went wrong, Exists will be false
            }
        }
示例#21
0
        Task <IDictionary <string, object> > IHealthCheckable.HealthCheckAsync()
        {
            IDictionary <string, object> returnValue = new Dictionary <string, object>();

            System.Data.Common.DbConnection dbConnection = null;
            try { dbConnection = _initiativeContext?.Database?.GetDbConnection(); }
            catch (Exception err) { returnValue["dbError"] = err.Message; }

            if (dbConnection != null)
            {
                try { returnValue["host"] = dbConnection.DataSource; } catch (Exception err) { returnValue["host"] = err.Message; }
                try { returnValue["database"] = dbConnection.Database; } catch (Exception err) { returnValue["database"] = err.Message; }

                try
                {
                    _initiativeContext.Database.OpenConnection();
                    try { returnValue["serverVersion"] = dbConnection.ServerVersion; } catch (Exception err) { returnValue["serverVersion"] = err.Message; }

                    // The following should produce the same as serverVersion, but to be sure we'll run it again.
                    // Also, we'll time it and supply that result

                    using (var cmd = dbConnection.CreateCommand())
                    {
                        cmd.CommandText = "SELECT @@VERSION";
                        var watch = new Stopwatch();
                        watch.Start();
                        var result = cmd.ExecuteScalar();
                        watch.Stop();
                        returnValue["version"]          = result;
                        returnValue["pingMilliseconds"] = watch.Elapsed.TotalMilliseconds;

                        cmd.CommandText = "SELECT TOP (1) [MigrationId] FROM[CoeIdeas].[dbo].[__EFMigrationsHistory] ORDER BY MigrationId DESC";
                        returnValue["CurrentMigration"] = cmd.ExecuteScalar();
                    }
                }
                catch (Exception) { /* eat the exception */ }
                finally
                {
                    _initiativeContext.Database.CloseConnection();
                }
            }

            return(Task.FromResult(returnValue));
        }
示例#22
0
        public Response ValidationSchedule(string bz)
        {
            using (DZEntities dzEntities = new DZEntities())
            {
                Response response = new Response();
                response.IsSuccess = true;

                System.Data.EntityClient.EntityConnection entityConnection = (System.Data.EntityClient.EntityConnection)dzEntities.Connection;
                entityConnection.Open();
                System.Data.Common.DbConnection storeConnection = entityConnection.StoreConnection;
                System.Data.Common.DbCommand    cmd             = storeConnection.CreateCommand();
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.CommandText = "P_UN_VALIDATION";

                OracleParameter[] sqlpara = new OracleParameter[3];
                sqlpara[0] = new OracleParameter("p_bz", bz);
                sqlpara[1] = new OracleParameter("p_ErrCode", OracleDbType.Varchar2, 30);
                sqlpara[2] = new OracleParameter("p_ErrMsg", OracleDbType.Varchar2, 1000);

                sqlpara[0].Direction = ParameterDirection.Input;
                sqlpara[1].Direction = ParameterDirection.Output;
                sqlpara[2].Direction = ParameterDirection.Output;

                cmd.Parameters.Add(sqlpara[0]);
                cmd.Parameters.Add(sqlpara[1]);
                cmd.Parameters.Add(sqlpara[2]);

                cmd.ExecuteNonQuery();


                if (cmd.Parameters[1].Value.ToString() == "1")
                {
                    response.IsSuccess   = true;
                    response.MessageText = cmd.Parameters[2].Value.ToString();
                }
                else
                {
                    response.IsSuccess   = false;
                    response.MessageText = cmd.Parameters[2].Value.ToString();
                }
                cmd.Dispose();
                return(response);
            }
        }
示例#23
0
        } //ExecuteQuery(string sql)

        /// <summary>
        /// 执行Sql语句
        /// </summary>
        /// <param name="sql">Sql语句</param>
        /// <param name="Conn">数据库连接对象</param>
        /// <returns>返回受影响行数</returns>
        public static int Execute(string sql, System.Data.Common.DbConnection Conn)
        {
            if (Conn == null)
            {
                Dbhelper.ErrLog("Dbhelper.Execute(string sql, System.Data.Common.DbConnection Conn):连接对象为空!");
                return(0);
            }
            System.Data.IDbCommand cmd = Conn.CreateCommand();

            cmd.CommandText = sql;
            try
            {
                return(cmd.ExecuteNonQuery());
            }
            catch (Exception ex)
            {
                Dbhelper.ErrLog("Dbhelper.ExecuteQuery(string sql, System.Data.Common.DbConnection Conn):" + ex.Message +
                                "/nsql=" + sql);
                return(0);
            }
        } //Execute(string sql)
示例#24
0
        private void test()
        {
            string query = @"SELECT * FROM Users WHERE SomeProp = {0} 
                AND SomeId IN (SELECT id FROM fn_myfunc({1})";

            string someProp = "'5'";
            int    someId   = 3;


            var users = this.m_ctx.issues.FromSql(query, someProp, someId)
                        .Include(u => u.closed_on)
                        .AsNoTracking()
                        .ToList();

            var books = this.m_ctx.issues.FromSql("EXEC GetAllBooks").ToList();


            var authorId = new System.Data.SqlClient.SqlParameter("@AuthorId", 1);
            var books2   = this.m_ctx.issues
                           .FromSql("EXEC GetBooksByAuthor @AuthorId", authorId)
                           .ToList();


            // this.m_ctx.Database.ProviderName
            // this.m_ctx.Database.createpa

            // This is EF6...
            // https://weblogs.asp.net/Dixin/EntityFramework.Functions


            this.m_ctx.Database.GetDbConnection();

            using (System.Data.Common.DbConnection con = this.m_ctx.Database.GetDbConnection())
            {
                using (var cmd = con.CreateCommand())
                {
                    var p = cmd.CreateParameter();
                }
            }
        }
示例#25
0
        }//Execute(string sql)

        /// <summary>
        /// 执行Sql语句
        /// </summary>
        /// <param name="sql">Sql语句</param>
        /// <param name="Conn">数据库连接对象</param>
        /// <returns>返回受影响行数</returns>
        static public int ExecuteWithTrans(string sql, System.Data.Common.DbConnection Conn, System.Data.Common.DbTransaction tran)
        {
            if (Conn == null)
            {
                DBCoreHelper.ErrLog("DBCoreHelper.ExecuteWithTrans(string sql, System.Data.Common.DbConnection Conn):连接对象为空!");
                return(0);
            }
            if (Conn.State == System.Data.ConnectionState.Closed)
            {
                Conn.Open();
            }
            System.Data.IDbCommand cmd = Conn.CreateCommand();
            cmd.CommandTimeout = 180;
            cmd.Transaction    = tran;
            cmd.CommandText    = sql;
            try
            {
                var count = cmd.ExecuteNonQuery();
                cmd.Dispose();
                return(count);
            }
            catch (Exception ex)
            {
                cmd.Dispose();
                if (ex.Message.Contains("死锁"))
                {
                    WriteLog(ex.Message + " 再做列锁循环!ExecuteWithTrans");
                    System.Threading.Thread.Sleep(200);
                    return(Execute(sql, Conn));
                }
                else
                {
                    DBCoreHelper.ErrLog("DBCoreHelper.ExecuteWithTrans(string sql, System.Data.Common.DbConnection Conn):" + ex.Message + "/nsql=" + sql);
                    return(0);
                }
            }
        }//Execute(string sql)
示例#26
0
        public List <contacts> GetAllContacts()
        {
            var command = connection.CreateCommand();

            command.CommandText = "SELECT * FROM contacts";

            List <contacts> contactsList = new List <contacts>();

            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    contacts contact = new contacts();
                    contact.name      = reader.GetValue(0).ToString();
                    contact.birthdate = DateTime.Parse(reader.GetValue(1).ToString());
                    contact.address   = reader.GetValue(2).ToString();
                    contactsList.Add(contact);
                }
            }
            return(contactsList);
        }
 internal IDbCommand SetupCommand(IDbConnection cnn, Action<IDbCommand, object> paramReader)
 {
     var cmd = cnn.CreateCommand();
     var init = GetInit(cmd.GetType());
     init?.Invoke(cmd);
     if (Transaction != null)
         cmd.Transaction = Transaction;
     cmd.CommandText = CommandText;
     if (CommandTimeout.HasValue)
     {
         cmd.CommandTimeout = CommandTimeout.Value;
     }
     else if (SqlMapper.Settings.CommandTimeout.HasValue)
     {
         cmd.CommandTimeout = SqlMapper.Settings.CommandTimeout.Value;
     }
     if (CommandType.HasValue)
         cmd.CommandType = CommandType.Value;
     paramReader?.Invoke(cmd, Parameters);
     return cmd;
 }
示例#28
0
        public static void DataToXML()
        {
            string table_schema = "geoip";
            string table_name   = "geoip_blocks_temp";

            table_schema = "public";
            table_name   = "t_sys_language_monthnames";


            using (System.Data.DataTable dt = new System.Data.DataTable())
            {
                dt.TableName = "record";

                using (System.Data.DataSet ds = new System.Data.DataSet(table_name))
                {
                    ds.Tables.Add(dt);
                    // dt.Namespace = "foo";

                    using (System.Data.Common.DbConnection con = Npgsql.NpgsqlFactory.Instance.CreateConnection())
                    {
                        con.ConnectionString = GetCS();

                        using (System.Data.Common.DbCommand cmd = con.CreateCommand())
                        {
                            cmd.CommandText = "SELECT * FROM " + table_schema + "." + table_name;

                            using (System.Data.Common.DbDataAdapter da = Npgsql.NpgsqlFactory.Instance.CreateDataAdapter())
                            {
                                da.SelectCommand = cmd;

                                if (con.State != System.Data.ConnectionState.Open)
                                {
                                    con.Open();
                                }

                                da.Fill(dt);

                                if (con.State != System.Data.ConnectionState.Open)
                                {
                                    con.Close();
                                }
                            } // End Using da
                        }     // End Using cmd
                    }         // End Using con

                    string exportFilename = System.IO.Path.Combine(@"d:\", table_name + ".xml");


                    //using (System.IO.Stream fs = System.IO.File.OpenWrite(exportFilename))
                    //{
                    //    using (System.IO.TextWriter sw = new System.IO.StreamWriter(fs, System.Text.Encoding.UTF8))
                    //    {
                    //        // System.IO.StringWriter sw = new System.IO.StringWriter();
                    //        // dt.WriteXml(sw, System.Data.XmlWriteMode.IgnoreSchema);
                    //        dt.WriteXml(sw, System.Data.XmlWriteMode.IgnoreSchema);
                    //    } // End Using sw

                    //} // End Using fs


                    System.Xml.XmlWriterSettings xs = new System.Xml.XmlWriterSettings();
                    xs.Indent             = true;
                    xs.IndentChars        = "    ";
                    xs.NewLineChars       = System.Environment.NewLine;
                    xs.OmitXmlDeclaration = false;
                    // xs.Encoding = System.Text.Encoding.UTF8; // doesn't work with pgsql
                    xs.Encoding = new System.Text.UTF8Encoding(false);

                    // <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
                    using (System.Xml.XmlWriter writer = System.Xml.XmlWriter.Create(exportFilename, xs))
                    {
                        dt.WriteXml(writer, System.Data.XmlWriteMode.IgnoreSchema);
                    } // End Using writer

                    System.Console.WriteLine(dt.Rows.Count);
                } // End Using ds
            }     // End Using dt
        }         // End Sub DataToXML
示例#29
0
        }         // End Sub DataToXML

        public static void LargeDataToXML()
        {
            string table_schema = "geoip";
            string table_name   = "geoip_blocks_temp";

            // table_schema = "public";
            // table_name = "t_sys_language_monthnames";


            System.Xml.XmlWriterSettings xs = new System.Xml.XmlWriterSettings();
            xs.Indent             = true;
            xs.IndentChars        = "    ";
            xs.NewLineChars       = System.Environment.NewLine;
            xs.OmitXmlDeclaration = false; // // <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
            // xs.Encoding = System.Text.Encoding.UTF8; // doesn't work with pgsql
            xs.Encoding = new System.Text.UTF8Encoding(false);

            string exportFilename = System.IO.Path.Combine(@"d:\", table_name + ".xml");

            // System.Text.StringBuilder builder = new System.Text.StringBuilder();
            // using (System.Xml.XmlWriter writer = System.Xml.XmlWriter.Create(builder, xs))
            using (System.Xml.XmlWriter writer = System.Xml.XmlWriter.Create(exportFilename, xs))
            {
                writer.WriteStartDocument();
                writer.WriteStartElement(table_name);

                writer.WriteAttributeString("xmlns", "xsi", null, System.Xml.Schema.XmlSchema.InstanceNamespace);
                // writer.WriteAttributeString("xsi", "schemaLocation", null, System.Xml.Schema.XmlSchema.InstanceNamespace);



                using (System.Data.Common.DbConnection con = Npgsql.NpgsqlFactory.Instance.CreateConnection())
                {
                    con.ConnectionString = GetCS();

                    using (System.Data.Common.DbCommand cmd = con.CreateCommand())
                    {
                        cmd.CommandText = "SELECT * FROM " + table_schema + "." + table_name;


                        if (con.State != System.Data.ConnectionState.Open)
                        {
                            con.Open();
                        }

                        using (System.Data.Common.DbDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.SequentialAccess))
                        {
                            if (dr.HasRows)
                            {
                                int fc = dr.FieldCount;

                                string[] columnNames = new string[fc];
                                // System.Type[] columnTypes = new System.Type[fc];

                                for (int i = 0; i < dr.FieldCount; ++i)
                                {
                                    columnNames[i] = dr.GetName(i);
                                    // columnTypes[i] = dr.GetFieldType(i);
                                } // Next i

                                while (dr.Read())
                                {
                                    // object[] thisRow = new object[dr.FieldCount];

                                    writer.WriteStartElement("record");

                                    for (int i = 0; i < fc; ++i)
                                    {
                                        writer.WriteStartElement(columnNames[i]);
                                        object obj = dr.GetValue(i);

                                        if (obj != System.DBNull.Value)
                                        {
                                            writer.WriteValue(obj);
                                        }
                                        else
                                        {
                                            writer.WriteAttributeString("xsi", "nil", System.Xml.Schema.XmlSchema.InstanceNamespace, "true");
                                        }

                                        writer.WriteEndElement();
                                    } // Next i

                                    writer.WriteEndElement();
                                } // Whend
                            }     // End if (dr.HasRows)
                        }         // End Using dr

                        if (con.State != System.Data.ConnectionState.Open)
                        {
                            con.Close();
                        }
                    } // End Using cmd
                }     // End Using con

                writer.WriteEndElement();
            } // ENd Using writer
        }     // End Sub LargeDataToXML
示例#30
0
        static void Main()
        {
#if false
            System.Windows.Forms.Application.EnableVisualStyles();
            System.Windows.Forms.Application.SetCompatibleTextRenderingDefault(false);
            System.Windows.Forms.Application.Run(new Form1());
#endif

            // https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.5
            // Postgres 9.5 (released since 2016-01-07) offers an "upsert" command:
            // INSERT ... ON CONFLICT DO NOTHING/UPDATE
            // INSERT INTO countries (country) VALUES ('France'),('Japan') ON CONFLICT DO NOTHING;

            // Wrong
            // INSERT INTO TestTable(some_field) VALUES('37F582A1-7D28-4052-837B-1AB065FDA88A') WHERE 0 = (SELECT COUNT(*) FROM TestTable WHERE some_field = '37F582A1-7D28-4052-837B-1AB065FDA88A')

            // Correct:
            // INSERT INTO TestTable(some_field) SELECT '37F582A1-7D28-4052-837B-1AB065FDA88A' WHERE 0 = (SELECT COUNT(*) FROM TestTable WHERE some_field = '37F582A1-7D28-4052-837B-1AB065FDA88A')

            // Better:
            // INSERT INTO invoices (invoiceid, billed) SELECT '12345', 'TRUE' WHERE NOT EXISTS (SELECT 1 FROM invoices WHERE invoiceid = '12345')


            string sql = @"
INSERT INTO TABLE (fields)
SELECT fields 
WHERE 0 = (SELECT COUNT(*) FROM table WHERE PK = @pk) ";


            sql = @"
UPDATE table 
    SET  field1 = value1 
        ,field2 = value2 
WHERE PK = @pk";


            // Get non-computed columns for table
            sql = @"
SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
WHERE (1=1)
AND TABLE_SCHEMA = 'dbo' 
AND TABLE_NAME = 'T_Benutzer' 
AND COLUMNPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), COLUMN_NAME,'IsComputed') = 0 
";


            // Get PK Info
            sql = @"
SELECT 
	 tc.TABLE_CATALOG 
	,tc.TABLE_SCHEMA 
	,tc.TABLE_NAME 
	,tc.CONSTRAINT_NAME 
	,kcu.COLUMN_NAME 
	,kcu.ORDINAL_POSITION 
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc

LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS kcu  
	ON kcu.TABLE_SCHEMA = tc.TABLE_SCHEMA 
	AND kcu.TABLE_NAME = tc.TABLE_NAME 
	AND kcu.TABLE_CATALOG = tc.TABLE_CATALOG 
	AND kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME 
	

WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY' 
-- AND tc.TABLE_CATALOG = '...'
AND tc.TABLE_SCHEMA = 'dbo' 
AND tc.TABLE_NAME = 'T_ZO_Inventory_Dwg'

ORDER BY TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_NAME, ORDINAL_POSITION 
";


            using (System.Data.Common.DbConnection con = GetConnection())
            {
                if (con.State != System.Data.ConnectionState.Open)
                {
                    con.Open();
                }

                using (System.Data.Common.DbCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = "SQL";

                    // dimMin
                    System.Data.Common.DbParameter width = cmd.CreateParameter();
                    width.ParameterName = "@dimMin";
                    width.Value         = 123;
                    width.DbType        = System.Data.DbType.Double;
                    cmd.Parameters.Add(width);

                    // cmd.ExecuteReader()


                    // using (System.Data.Common.DbDataAdapter da = new System.Data.SqlClient.SqlDataAdapter((System.Data.SqlClient.SqlCommand)cmd)) { da.Fill(dt); }
                }

                if (con.State != System.Data.ConnectionState.Closed)
                {
                    con.Close();
                }
            }


            System.Console.WriteLine(System.Environment.NewLine);
            System.Console.WriteLine(" --- Press any key to continue --- ");
            System.Console.ReadKey();
        }
示例#31
0
        public static void SaveAssembly(string assemblyName, string path)
        {
            string sql = @"
--DECLARE @__assemblyname nvarchar(260)
--SET @__assemblyname = 'Microsoft.SqlServer.Types'
SELECT 
	 A.name
	,AF.content 
FROM sys.assembly_files AS AF 
INNER JOIN sys.assemblies AS A 
	ON AF.assembly_id = A.assembly_id 
	
WHERE AF.file_id = 1 
AND A.name = @__assemblyname
;
";


            AnySqlWebAdmin.SqlService service = new AnySqlWebAdmin.SqlService();


            using (System.Data.Common.DbConnection con = service.Connection)
            {
                using (System.Data.Common.DbCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = sql;


                    var p = cmd.CreateParameter();
                    p.ParameterName = "__assemblyname";
                    p.DbType        = System.Data.DbType.String;
                    p.Value         = assemblyName;

                    cmd.Parameters.Add(p);


                    if (con.State != System.Data.ConnectionState.Open)
                    {
                        con.Open();
                    }

                    using (System.Data.IDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection))
                    {
                        reader.Read();
                        //SqlBytes bytes = reader.GetSqlBytes(0);
                        const int BUFFER_SIZE = 1024;
                        byte[]    buffer      = new byte[BUFFER_SIZE];

                        int col       = reader.GetOrdinal("content");
                        int bytesRead = 0;
                        int offset    = 0;

                        // write the byte stream out to disk
                        using (System.IO.FileStream bytestream = new System.IO.FileStream(path, System.IO.FileMode.CreateNew))
                        {
                            while ((bytesRead = (int)reader.GetBytes(col, offset, buffer, 0, BUFFER_SIZE)) > 0)
                            {
                                bytestream.Write(buffer, 0, bytesRead);
                                offset += bytesRead;
                            } // Whend

                            bytestream.Close();
                        } // End Using bytestream

                        reader.Close();
                    } // End Using reader

                    if (con.State != System.Data.ConnectionState.Closed)
                    {
                        con.Close();
                    }
                }
            }
        } // End Function SaveAssembly