Detailed steps of connecting VS2015 to Oracle database

Time:2019-9-6

development environment

Host: Win10 + VS2015 + ODP.Net for VS2015 Virtual Machine: Win7 + Oracle 11g + Bridging

Configure ODP.Net

First download Oracle Developer Tools for Visual Studio 2015. To download this file, you need to register Oracle Community Account and accept the relevant protocols. 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 the MSI installer to install. After installing, the relevant plug-ins of VS2015 will be registered automatically. After restarting VS2015, Oracle commands, such as SQL * PLUS support, will be seen. At the same time, you can see the corresponding options when you add the database.

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

Configure tnsnames.ora

ODP.Net defaults to tnsnames.ora in the installation directory. If the installation directory is under Program Files, it may encounter problems such as no privileges. 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 tnsnames. ora file on the server side, or edit it manually, in the following format:

Data Source Alias >=
 (DESCRIPTION =
  (ADDRESS = PROTOCOL = TCP) (HOST = host name or IP >) (PORT = port number >)
  (CONNECT_DATA =
   (SERVER = DEDICATED)
   (SERVICE_NAME= <Database Service Name>)
  )
 )

Add database

Open Tool – Connect to the database, modify the data source to ODP. NET under Oracle database, host the driver, and then click OK to open the Add Connection Window.

Fill in the username, password and select the data source, and then test the connection. If successful, it shows that it has been connected, click OK.

According to a netizen’s analysis, Oracle’s monitor will open another random port for data communication after connecting through 1521 port, so using NAT virtual network card may lead to connection failure. In this case, use the bridge virtual network card and change loaclhost to the current IP of the virtual machine in the net manager. After restarting the monitoring service, try again.

Connect to the database and use it

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 OracleCommand
sqlCom.CommandText = sql
sqlCom.Connection = conn
sqlCom.ExecuteNonQuery()

Query data

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

The correct posture for inserting pictures

Pictures as binary data can not directly compile SQL commands, we need to use Oracle Command’s own Parameters function. In the SQL command, use: photo to represent a parameter, and then use


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

To specify the type and size of the parameter, respectively

Final use

sqlCom.Parameters(0).Value = imgData

To specify the value of this parameter.

The code for the entire insertion process is as follows:


Dim conn As New OracleConnection(oradb)
Dim imgData(0) As Byte
Dim ms As New System.IO.MemoryStream
PictureBox1.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 OracleCommand
sqlCom.CommandText = sql
sqlCom.Connection = conn
sqlCom.Parameters.Add("photo", OracleDbType.Blob, imgData.Length)
sqlCom.Parameters(0).Value = imgData
sqlCom.ExecuteNonQuery()

Common errors

column not allowed here

The data type does not match. Check whether the data type of the corresponding item is correct.

missing comma
The format of the command is incorrect. Check if your SQL command is incorrect. Especially when you have a string, you need to use “” to represent the “.

identifier is too long
The identifier is too long (no more than 30 characters) and it’s not very clear why, but after I cancelled the location specified in the insert command, the error disappeared.

missing INTO keyword
Lack of into keyword (hand sliding into inte), check your SQL command for errors.

cannot insert NULL into (“SYSTEM”.”HERO”.”HEROCATEGORYID”)
These items all specify non-zero values, so it is necessary to assign values to the corresponding items.

The following is a supplement to other netizens:

1. Download Oracle Developer Tools for Visual Studio 2015 at the following address.

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 (direct copy). 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 more roacles such as sqlplus in the tool options. Right-click on the project reference to add reference-extension-select Oracle. Manage Data Access
Previous Data Access pens for Manage Data Access were better to use without considering 64-bit 32-bit issues.
4. Test the connection success by following code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
Using Oracle.ManagedDataAccess.Client;//dll reference


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 information is open, the connection is successful

Recommended Today

External part – Visual Studio code and vue.js

Fan waipian Seaconch is so tired that he can’t help writing this article I can’t help but recommend new editors. I can’t help but use them everywherevs codeThis editor To whom? I don’t know, when the same person is like meFrom vsThen maybe you will feel the same as me~ Start learning in Mayvue.jsUp to […]