在 Oracle 数据库上构建 .NET 应用程序(VB.Net Code)

原创

jhtchina 2021-08-04 16:43:44 博主文章分类：VB.Net ©著作权

文章标签 oracle 用户名数据 ide 配置文件 文章分类 Oracle 数据库

©著作权归作者所有：来自51CTO博客作者jhtchina的原创作品，请联系作者获取转载授权，否则将追究法律责任

Oracle 里面建立数据表

DROP USER store CASCADE;
CREATE USER store IDENTIFIED BY store;
GRANT connect, resource TO store;
CONNECT store/store;

CREATE TABLE product_types (
  product_type_id INTEGER
    CONSTRAINT product_types_pk PRIMARY KEY,
  name VARCHAR2(10) NOT NULL
);

CREATE TABLE products (
  product_id INTEGER
    CONSTRAINT products_pk PRIMARY KEY,
  product_type_id INTEGER
    CONSTRAINT products_fk_product_types
    REFERENCES product_types(product_type_id),
  name VARCHAR2(30) NOT NULL,
  description VARCHAR2(50),
  price NUMBER(5, 2)
);

INSERT INTO product_types (
  product_type_id, name
) VALUES (
  1, 'Book'
);

INSERT INTO product_types (
  product_type_id, name
) VALUES (
  2, 'DVD'
);

INSERT INTO products (
  product_id, product_type_id, name, description, price
) VALUES (
  1, 1, 'Modern Science', 'A description of modern science', 19.95
);

INSERT INTO products (
  product_id, product_type_id, name, description, price
) VALUES (
  2, 1, 'Chemistry', 'Introduction to Chemistry', 30.00
);

INSERT INTO products (
  product_id, product_type_id, name, description, price
) VALUES (
  3, 2, 'Supernova', 'A star explodes', 25.99
);

INSERT INTO products (
  product_id, product_type_id, name, description, price
) VALUES (
  4, 2, 'Tank War', 'Action movie about a future war', 13.95
);

COMMIT;

"User Id=XXXXXX;Password=XXXXXX;Data Source=Oracle9i"
部分用户名：XXXXXX 密码：XXXXXX Data Source=Oracle9i

1 引用 Oracle.DataAccess.dll
2 新增数据

  Dim myOracleConnection As New OracleConnection( _
       "User Id=;Password=;Data Source=")
        myOracleConnection.Open()
        '    Dim myOracleTransaction As OracleTransaction = _
        '  myOracleConnection.BeginTransaction()
        '    Dim myOracleCommand As OracleCommand = _
        'myOracleConnection.CreateCommand()
        'MessageBox.Show("OK")
        Dim myOracleTransaction As OracleTransaction = _
        myOracleConnection.BeginTransaction()

        Dim myOracleCommand As OracleCommand = _
             myOracleConnection.CreateCommand()

        myOracleCommand.CommandText = _
             "INSERT INTO product_types (" & _
             "  product_type_id, name" & _
             ") VALUES (" & _
             "  3, 'Magazine'" & _
             ")"
        Console.WriteLine("Running first INSERT statement")
        myOracleCommand.ExecuteNonQuery()
        myOracleCommand.CommandText = _
     "INSERT INTO products (" & _
     "  product_id, product_type_id, name, description, price" & _
     ") VALUES (" & _
     "  5, 3, 'Oracle Magazine', 'Magazine about Oracle', 4.99" & _
     ")"

        Console.WriteLine("Running second INSERT statement")
        myOracleCommand.ExecuteNonQuery()

        Console.WriteLine("Committing transaction")
        myOracleTransaction.Commit()

        myOracleConnection.Close()
        MessageBox.Show("OK")

3 新增回滚

Dim myOracleConnection As New OracleConnection( _
            "User Id=;Password=;Data Source=")
        myOracleConnection.Open()
        Dim myOracleTransaction As OracleTransaction = _
     myOracleConnection.BeginTransaction()
        Dim myOracleCommand As OracleCommand = _
     myOracleConnection.CreateCommand()
        Console.WriteLine("Adding a row to the products table " & _
             "with a product_id of 6")
        myOracleCommand.CommandText = _
     "INSERT INTO products (" & _
     "  product_id, product_type_id, name, description, price" & _
     ") VALUES (" & _
     "  6, 2, 'Man from Another World', 'Man from Venus lands on Earth', 24.99" & _
     ")"

        myOracleCommand.ExecuteNonQuery()
        myOracleTransaction.Save("SaveProduct")

        Console.WriteLine("Adding a row to the products table " & _
   "with a product_id of 7")
        myOracleCommand.CommandText = _
          "INSERT INTO products (" & _
          "  product_id, product_type_id, name, description, price" & _
          ") VALUES (" & _
          "  7, 2, 'Z-Files', 'Mysterious stories', 14.99" & _
          ")"
        myOracleCommand.ExecuteNonQuery()
        Console.WriteLine("Performing a rollback to the savepoint")
        myOracleTransaction.Rollback("SaveProduct")

        myOracleCommand.CommandText = _
    "SELECT * " & _
    "FROM products"
        Dim myOracleDataReader As OracleDataReader = _
          myOracleCommand.ExecuteReader()
        'Do While myOracleDataReader.Read()
        '    Console.WriteLine("myOracleDataReader(product_id) = " & _
        '      myOracleDataReader("product_id"))
        '    Console.WriteLine("myOracleDataReader(name) = " & _
        '      myOracleDataReader("name"))
        'Loop
        myOracleDataReader.Close()

        Console.WriteLine("Rolling back entire transaction")
        myOracleTransaction.Rollback()

        myOracleConnection.Close()
        MessageBox.Show("OK")

4 数据显示到dataset里面

  Dim myOracleConnection As New OracleConnection( _
             "User Id=;Password=;Data Source=")
        myOracleConnection.Open()
        Dim myoracleAdapter As OracleDataAdapter = New OracleDataAdapter()

        myoracleAdapter.SelectCommand = New OracleCommand("select * from products", myOracleConnection)
        Dim ds As DataSet = New DataSet("productsDataSet")
        myoracleAdapter.Fill(ds, "Products")
        myOracleConnection.Clone()
        DataGridView1.DataSource = ds.Tables(0).DefaultView

Oracle 连接设置

ORACLE CONFIG
SERVICE_NAME	IBSDB


Link String	MRWDB =
	(DESCRIPTION =
	(ADDRESS = (PROTOCOL = TCP)(HOST = SUZSOFT-EE04A09)(PORT = 1521))
	(CONNECT_DATA =
	(SERVER = DEDICATED)
	(SERVICE_NAME = IBSDB)
	)
	)