C# NuoDbConnection.Open方法代码示例

        public void TestNoPrepareNamedParamIn()
        {
            using (NuoDbConnection connection = new NuoDbConnection(TestFixture1.connectionString))
            {
                connection.Open();
                new NuoDbCommand("drop procedure nunit_test if exists", connection).ExecuteNonQuery();
                new NuoDbCommand("create procedure nunit_test(in p1 string, in p2 string) as throw p1; end_procedure", connection).ExecuteNonQuery();

                NuoDbCommand cmd = new NuoDbCommand("nunit_test", connection);
                cmd.CommandType = CommandType.StoredProcedure;
                NuoDbParameter param1 = new NuoDbParameter();
                param1.ParameterName = "p2";
                param1.Direction = ParameterDirection.Input;
                param1.Value = "goodbye";
                cmd.Parameters.Add(param1);
                NuoDbParameter param2 = new NuoDbParameter();
                param2.ParameterName = "p1";
                param2.Direction = ParameterDirection.Input;
                param2.Value = "hello";
                cmd.Parameters.Add(param2);
                try
                {
                    cmd.ExecuteNonQuery();
                    Assert.Fail();
                }
                catch (Exception e)
                {
                    Assert.AreEqual("hello", e.Message);
                }
            }
        }

        internal static void CreateGameTable()
        {
            using (NuoDbConnection connection = new NuoDbConnection(connectionString))
            {
                connection.Open();
                try
                {
                    DbCommand dropCommand = new NuoDbCommand("drop table Game", connection);
                    dropCommand.ExecuteNonQuery();
                }
                catch (Exception)
                {
                    // table is allowed to be missing
                }
                DbCommand createCommand = new NuoDbCommand("create table Game" +
                                                            "(" +
                                                            "   Id       bigint generated always as identity not NULL primary key," +
                                                            "   Date     DATE" +
                                                            ")", connection);
                createCommand.ExecuteNonQuery();

                DbCommand insertCommand = new NuoDbCommand("Insert into Game (Date) Values (?)", connection);
                insertCommand.Prepare();

                insertCommand.Parameters[0].Value = "1970-01-01";

                insertCommand.ExecuteNonQuery();

            }
        }

        public void TestAsynchronousReader2()
        {
            NuoDbConnection connection = new NuoDbConnection(connectionString);
            NuoDbCommand command = new NuoDbCommand("select * from hockey", connection);

            connection.Open();

            AsyncCallback callback = new AsyncCallback(HandleCallback);
            IAsyncResult result = command.BeginExecuteReader(callback, command);
        }

        public static void Init()
        {
            Utils.CreateHockeyTable();
            using (NuoDbConnection connection = new NuoDbConnection(TestFixture1.connectionString))
            {
                DbCommand command = new NuoDbCommand("select count(*) from hockey", connection);

                connection.Open();
                tableRows = (int)command.ExecuteScalar();
            }
        }

        public void TestMultipleReturnResultSets()
        {
            using (NuoDbConnection connection = new NuoDbConnection(TestFixture1.connectionString))
            {
                connection.Open();
                new NuoDbCommand("drop procedure nunit_test if exists", connection).ExecuteNonQuery();

                try
                {
                    new NuoDbCommand("create procedure nunit_test() " +
                        " returns table t1(field1 string, field2 integer), t2(column1 string, column2 string, column3 integer) " +
                        " as " +
                        "   insert into t1 values ('rset 1, row1', 0), ('rset 1, row2',1); " +
                        "   insert into t2 values ('rset 2, row1', 'first', 100), ('rset 2, row2','second', 101); " +
                        " end_procedure", connection).ExecuteNonQuery();
                }
                catch (NuoDbSqlException e)
                {
                    if (e.Code.Code == -1)
                    {
                        // the server doesn't support multiple result sets as return value for procedures
                        return;
                    }
                    else
                        throw;
                }
                NuoDbCommand cmd = new NuoDbCommand("nunit_test", connection);
                cmd.CommandType = CommandType.StoredProcedure;
                using (DbDataReader reader = cmd.ExecuteReader())
                {
                    Assert.IsTrue(reader.Read());
                    Assert.AreEqual("rset 1, row1", reader["field1"]);
                    Assert.AreEqual(0, reader["field2"]);
                    Assert.IsTrue(reader.Read());
                    Assert.AreEqual("rset 1, row2", reader["field1"]);
                    Assert.AreEqual(1, reader["field2"]);
                    Assert.IsFalse(reader.Read());

                    Assert.IsTrue(reader.NextResult());
                    Assert.IsTrue(reader.Read());
                    Assert.AreEqual("rset 2, row1", reader["column1"]);
                    Assert.AreEqual("first", reader["column2"]);
                    Assert.AreEqual(100, reader["column3"]);
                    Assert.IsTrue(reader.Read());
                    Assert.AreEqual("rset 2, row2", reader["column1"]);
                    Assert.AreEqual("second", reader["column2"]);
                    Assert.AreEqual(101, reader["column3"]);
                    Assert.IsFalse(reader.Read());

                    Assert.IsFalse(reader.NextResult());
                }
            }
        }

        public void TestBulkLoadPerformance()
        {
            using (NuoDbConnection cnn = new NuoDbConnection(connectionString))
            {
                cnn.Open();
                Utils.DropTable(cnn, "temp");

                DbCommand createCommand = new NuoDbCommand("create table temp (col1 integer, col2 integer)", cnn);
                int result = createCommand.ExecuteNonQuery();

                DbCommand cmm = cnn.CreateCommand();
                cmm.CommandText = "insert into temp(col1, col2) values(?, ?)";
                cmm.Parameters.Add(new NuoDbParameter { DbType = DbType.Int32, ParameterName = "col1" });
                cmm.Parameters.Add(new NuoDbParameter { DbType = DbType.Int32, ParameterName = "col2" });
                cmm.Prepare();

                const int ROW_NUMBER = 40000;
                DateTime start = DateTime.Now;
                for (var i = 1; i <= ROW_NUMBER; i++)
                {
                    cmm.Parameters["col1"].Value = i;
                    cmm.Parameters["col2"].Value = 2 * i;
                    cmm.ExecuteNonQuery();
                }
                DateTime end = DateTime.Now;
                double insertTime = (end - start).TotalMilliseconds;

                Utils.DropTable(cnn, "temp2");
                createCommand = new NuoDbCommand("create table temp2 (col1 integer, col2 integer)", cnn);
                createCommand.ExecuteNonQuery();

                NuoDbBulkLoader loader = new NuoDbBulkLoader(connectionString);
                loader.DestinationTableName = "TEMP2";

                DbCommand command = new NuoDbCommand("select * from temp", cnn);
                DbDataReader reader = command.ExecuteReader();

                loader.BatchProcessed += new BatchProcessedEventHandler(loader_BatchProcessed);
                start = DateTime.Now;
                loader.WriteToServer(reader);
                end = DateTime.Now;

                double loadTime = (end - start).TotalMilliseconds;

                reader.Close();

                Console.WriteLine("{0} insert = {1}\n{0} bulk load = {2}\n", ROW_NUMBER, insertTime, loadTime);

                Assert.IsTrue(loadTime < insertTime, "BulkLoad takes more time than manual insertion");
            }
        }

        public void TestNoPrepareAnonymousParamOut()
        {
            using (NuoDbConnection connection = new NuoDbConnection(TestFixture1.connectionString))
            {
                connection.Open();
                new NuoDbCommand("drop procedure nunit_test if exists", connection).ExecuteNonQuery();
                new NuoDbCommand("create procedure nunit_test(out p1 string) as p1='hello'; end_procedure", connection).ExecuteNonQuery();

                NuoDbCommand cmd = new NuoDbCommand("nunit_test", connection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.ExecuteNonQuery();
                Assert.AreEqual("hello", cmd.Parameters["p1"].Value);
            }
        }

        public void TestAsynchronousScalar1()
        {
            using (NuoDbConnection connection = new NuoDbConnection(connectionString))
            {
                NuoDbCommand countCommand = (NuoDbCommand)connection.CreateCommand();
                countCommand.CommandText = "select count(*) from hockey";

                connection.Open();

                IAsyncResult result = countCommand.BeginExecuteScalar();

                int count = (int)countCommand.EndExecuteScalar(result);
            }
        }

		protected bool NuoDbDatabaseExists(string connectionString)
		{
			try
			{
				//just try to connect
				using (var conn = new NuoDbConnection(connectionString))
				{
					conn.Open();
				}
				return true;
			}
			catch
			{
				return false;
			}
		}

        public void DB4329()
        {
            using (NuoDbConnection connection = new NuoDbConnection(TestFixture1.connectionString))
            {
                connection.Open();
                Utils.DropTable(connection, "ExpenseTest");
                DbCommand createCommand = new NuoDbCommand("Create table ExpenseTest" +
                                                           "(" +
                                                           "SourceExpenseId int," +
                                                           "ExpenseAmount numeric(15,2)" +
                                                           ")", connection);
                createCommand.ExecuteNonQuery();

                DbCommand insertCommand = new NuoDbCommand("Insert Into ExpenseTest(SourceExpenseId, ExpenseAmount) Values (?,?)", connection);
                insertCommand.Prepare();

                insertCommand.Parameters[0].Value = -1254524;
                insertCommand.Parameters[1].Value = -135.35;
                insertCommand.ExecuteNonQuery();

                insertCommand.Parameters[0].Value = 100100100;
                insertCommand.Parameters[1].Value = -1325465.35;
                insertCommand.ExecuteNonQuery();

                insertCommand.Parameters[0].Value = 100100101;
                insertCommand.Parameters[1].Value = 200000.35;
                insertCommand.ExecuteNonQuery();

                DbCommand selectCommand = new NuoDbCommand("select SourceExpenseId, ExpenseAmount from ExpenseTest", connection);
                using (DbDataReader reader = selectCommand.ExecuteReader())
                {
                    bool hasNext=reader.Read();
                    Assert.IsTrue(hasNext);
                    Assert.AreEqual(-1254524, reader[0]);
                    Assert.AreEqual(-135.35, reader[1]);
                    hasNext = reader.Read();
                    Assert.IsTrue(hasNext);
                    Assert.AreEqual(100100100, reader[0]);
                    Assert.AreEqual(-1325465.35, reader[1]);
                    hasNext = reader.Read();
                    Assert.IsTrue(hasNext);
                    Assert.AreEqual(100100101, reader[0]);
                    Assert.AreEqual(200000.35, reader[1]);
                }

            }
        }

        public void TestAsynchronousReader1()
        {
            using (NuoDbConnection connection = new NuoDbConnection(connectionString))
            {
                NuoDbCommand command = new NuoDbCommand("select * from hockey", connection);

                connection.Open();
                IAsyncResult result = command.BeginExecuteReader();

                using (DbDataReader reader = command.EndExecuteReader(result))
                {
                    while (reader.Read())
                    {
                        Console.WriteLine("\t{0}\t{1}\t{2}\t{3}", reader[0], reader[1], reader[2], reader["id"]);
                    }
                }
            }
        }

        public void TestNoPrepareAnonymousParamIn()
        {
            using (NuoDbConnection connection = new NuoDbConnection(TestFixture1.connectionString))
            {
                connection.Open();
                new NuoDbCommand("drop procedure nunit_test if exists", connection).ExecuteNonQuery();
                new NuoDbCommand("create procedure nunit_test(in p1 string) as throw p1; end_procedure", connection).ExecuteNonQuery();

                NuoDbCommand cmd = new NuoDbCommand("nunit_test", connection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("hello");
                try
                {
                    cmd.ExecuteNonQuery();
                    Assert.Fail();
                }
                catch (Exception e)
                {
                    Assert.AreEqual("hello", e.Message);
                }
            }
        }

        public void TestBulkLoadOnStoredProcedureCommand()
        {
            using (NuoDbConnection connection = new NuoDbConnection(TestFixture1.connectionString))
            {
                connection.Open();
                new NuoDbCommand("drop table temp if exists", connection).ExecuteNonQuery();
                new NuoDbCommand("create table temp (col string)", connection).ExecuteNonQuery();
                new NuoDbCommand("drop procedure nunit_test if exists", connection).ExecuteNonQuery();
                new NuoDbCommand("create procedure nunit_test(input_data string) " +
                    " as " +
                    "   insert into temp values (input_data); " +
                    " end_procedure", connection).ExecuteNonQuery();

                DataTable metadata = new DataTable("dummy");
                metadata.Columns.Add("xyz", typeof(string));
                DataRow[] rows = new DataRow[10];
                for (int i = 0; i < rows.Length; i++)
                {
                    rows[i] = metadata.NewRow();
                    rows[i][0] = Convert.ToString(i);
                }

                NuoDbCommand loader = new NuoDbCommand(connection);
                loader.CommandType = CommandType.StoredProcedure;
                loader.CommandText = "nunit_test";
                loader.ExecuteBatch(rows);

                DbCommand command = new NuoDbCommand("select count(*) from temp", connection);
                object val = command.ExecuteScalar();

                Assert.AreEqual(10, val);

                command = new NuoDbCommand("select col from temp", connection);
                val = command.ExecuteScalar();

                Assert.AreEqual("0", val);
            }
        }

        public void TestPreparedInsertWithGeneratedKeys2()
        {
            using (NuoDbConnection connection = new NuoDbConnection(connectionString))
            {
                connection.Open();
                DbTransaction transaction = connection.BeginTransaction();

                DbCommand maxIdCmd = connection.CreateCommand();
                maxIdCmd.CommandText = "select max(id) from hockey";
                int maxId = (int)maxIdCmd.ExecuteScalar();

                DbCommand updateCommand = connection.CreateCommand();
                updateCommand.CommandText = "insert into hockey (number, name) values (?, ?)";
                updateCommand.Prepare();
                updateCommand.Parameters[0].Value = 99;
                updateCommand.Parameters[1].Value = "xxx";

                DbDataReader reader = updateCommand.ExecuteReader();
                Assert.IsNotNull(reader, "The command should return a generated keys recordset");
                Assert.IsTrue(reader.Read(), "There must be at least one ID in the generated keys recordset");
                int lastId = (int)reader.GetValue(0);
                Assert.IsTrue(lastId > maxId, "The generated ID must be greater than the existing ones");

                DbCommand selectCommand = connection.CreateCommand();
                selectCommand.CommandText = "select name from hockey where id = ?";
                selectCommand.Parameters.Add(lastId);
                string value = (string)selectCommand.ExecuteScalar();
                Assert.AreEqual("xxx", value);

                transaction.Rollback();
            }
        }

        public void TestBulkLoad_DataReaderWithMappingOrdinal2Ordinal()
        {
            CreateTargetForBulkLoad();

            NuoDbBulkLoader loader = new NuoDbBulkLoader(connectionString);
            loader.BatchSize = 2;
            loader.DestinationTableName = "TEMP";
            loader.ColumnMappings.Add(1, 0);

            using (NuoDbConnection connection = new NuoDbConnection(connectionString))
            {
                DbCommand command = new NuoDbCommand("select number, position as xyz2 from hockey order by number", connection);

                connection.Open();
                DbDataReader reader = command.ExecuteReader();
                loader.WriteToServer(reader);
                reader.Close();

                command = new NuoDbCommand("select count(*) from hockey", connection);
                object val = command.ExecuteScalar();

                VerifyBulkLoad((int)val, "Fan");
            }
        }