Shulou(Shulou.com)06/01 Report--

development environment

Host: Win10 + VS2015 + ODP. Net for VS2015 VM: Win7 + Oracle 11g + Bridge

Configure ODP. Net

First download Oracle Developer Tools for Visual Studio 2015. To download this file, you need to register an Oracle community account and accept the relevant agreement. This file provides the following components:

Oracle Developer Tools for Visual Studio 12.1.0.2.4

Oracle Data Provider for .NET 4 12.1.0.2.0

Oracle Providers for ASP.NET 4 12.1.0.2.0

After downloading, run MSI installer to install. After installation, relevant plug-ins of VS2015 will be automatically registered. After restarting VS2015, you will see relevant commands of Oracle, such as SQL *PLUS support. You can also see the corresponding options when adding databases.

ODP. Net supports all Oracle versions, so you only need to pay attention to the VS version when downloading.

Configure tnsname.ora

ODP. Net uses tnsnames.ora in the installation directory by default. If the installation directory is under Program Files, you may encounter problems such as no permissions. At this time, use administrator privileges to open the command line, switch to the corresponding directory and use notepad to edit.

Copy the contents of the server-side tnsnames.ora file, or edit it manually, in the following format:

= (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = )) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ) ) )

add database

Open Tools-Connect to Database, modify the data source to ODP. NET, Managed Driver under Oracle Database, and click OK to open the Add Connection window.

Fill in the user name, password and select the data source, and then test the connection, if successful, it has been connected, click OK.

Additional Tips for Using Virtual Machines to Build Databases According to a netizen's analysis, Oracle's listener will open another new random port for data communication after connecting through port 1521, so using NAT virtual network cards may lead to connection failure. In this case, use the bridge virtual NIC and modify loaclhost to the current IP of the virtual machine in net manager. Restart the monitoring service and try again.

Connect databases and use

connect to the database

Dim oradb As String = "User ID=system;Password=123456;Data Source=lol"Dim conn As New OracleConnection(oradb)conn.Open()Dim sql As String = "create table xxx"Dim sqlCom As New OracleCommandsqlCom.CommandText = sqlsqlCom.Connection = connsqlCom.ExecuteNonQuery()

query data

After successfully configuring the data source, you only need to drag DataGridView to the interface, configure it, and select the table you need.

Insert the correct posture of the picture

As binary data, images cannot be directly pieced together into SQL commands. We need to use the Parameters function that comes with OracleCommand. Use:photo to represent a parameter in SQL commands, and then use

sqlCom.Parameters.Add("photo", OracleDbType.Blob, imgData.Length)

to specify the type and size of the parameter.

last used

sqlCom.Parameters(0).Value = imgData

to specify the value of this parameter.

The code for the entire image insertion process is as follows:

Dim conn As New OracleConnection(oradb)Dim imgData(0) As ByteDim ms As New System.IO.MemoryStreamPictureBox1.BackgroundImage.Save(ms, PictureBox1.BackgroundImage.RawFormat)ReDim imgData(ms.Length - 1)ms.Read(imgData, 0, ms.Length)ms.Close()conn.Open()Dim sql As String = "insert into hero values" & "(" & TextBox1.Text & ":photo" & ")"Dim sqlCom As New OracleCommandsqlCom.CommandText = sqlsqlCom.Connection = connsqlCom.Parameters.Add("photo", OracleDbType.Blob, imgData.Length)sqlCom.Parameters(0).Value = imgDatasqlCom.ExecuteNonQuery()

common mistakes

column not allowed here

Data types do not match. Check whether the corresponding item data type is correct.

missing comma

Command format is not right, check whether your SQL command has errors, especially when there is a string, you need to use "" to represent a string of ".

identifier is too long

The identifier is too long (no more than 30 characters). It's not obvious why, but after I removed the part of the insert command that specifies the position, the error disappeared.

missing INTO keyword

Missing into keyword (hand slide typed as inte), check your SQL command for errors.

cannot insert NULL into ("SYSTEM". "HERO". "HEROCATEGORYID")

These items are specified with non-zero values, so they cannot be assigned values, and the corresponding items can be assigned values.

Here are some comments from other netizens:

1. Download Oracle Developer Tools for Visual Studio 2015 at:

After installation, refer to the last paragraph of the tnsnames.ora file in the oracle installation directory to modify the last paragraph of the tnsnames.ora file in the Oracle Developer Tools for Visual Studio 2015 installation directory (copy directly). My ORCL is as follows:

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

) F:\app\sky\product\11.2.0\dbhome_1\NETWORK\ADMIN

3. Open vs and you will find that there are many sqlplus and other objects in the tool options. Right-click on the project reference to add reference-expand-select Oracle.ManageDataAccess

ManageDataAccess pen Previous DataAccess is better to use, do not consider the 64-bit 32-bit problem

4. Test for successful connection by following code

using System; using System.Collections.Generic; using System.Linq; using System.Text; using Oracle.ManagedDataAccess.Client;//dll invokes namespace test{ class Program { static void Main(string[] args) { string connString = "Data Source=orcl;User Id=zzw;Password=123456"; OracleConnection conn = new OracleConnection(); conn.ConnectionString = connString; conn.Open(); Console.WriteLine("Connection State:" + conn.State); conn.Close(); Console.ReadLine(); } }}

If the console output message is open, the connection is successful

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.