C# ODBC将一个文件夹的文件装载到ORACLE数据库BLOB列,并将BLOB列下载到另一个文件夹

下载

instantclient-basic-windows.x64-11.2.0.4.0.zip

instantclient-odbc-windows.x64-11.2.0.4.0.zip

Instant Client for Microsoft Windows (x64) 64-bit | Oracle 中国

解压后将目录复制到C:\ oracle下

  双击执行odbc_install.exe

配置环境变量，在“环境变量”的“系统变量”中增加：

ORACLE_HOME = C:\oracle\instantclient_11_2

TNS_ADMIN = C:\oracle\instantclient_11_2\network\admin

NLS_LANG = SIMPLIFIED CHINESE_CHINA.ZHS16GBK

新建tnsnames.ora文件

在C:\oracle\instantclient_11_2\network\admin新建一个tnsnames.ora文件，增加自己的别名配置。

示例如下：

# tnsnames.ora Network Configuration File: C:\oracle\product\11.2.0\client_1\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.

ORCL185 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

创建ODBC数据源

         

          

        配置完相关属性之后 点击“Test Connection”测试连接是否成功

using System.Data.Odbc;




string SQL;
long FileSize;
byte[] rawData;
FileStream fs;

//Connection string for Connector/ODBC 3.51
string MyConString = "DSN=orcl185;" +
                           "UID=scott;" +
                           "PWD=tiger;";

//Connect to MySQL using Connector/ODBC
OdbcConnection conn = new OdbcConnection(MyConString);


OdbcCommand cmd = new OdbcCommand();


try
{
    // Get the directory
    DirectoryInfo place = new DirectoryInfo(@"InputFiles\");

    // Using GetFiles() method to get list of all
    // the files present in the Train directory
    FileInfo[] Files = place.GetFiles();
    Console.WriteLine("Files are:");
    Console.WriteLine();

    // Display the file names
    foreach (FileInfo i in Files)
    {
        Console.WriteLine("File Name - {0}", i.Name);
        {
            fs = new FileStream(i.FullName, FileMode.Open, FileAccess.Read);
            FileSize = fs.Length;

            rawData = new byte[FileSize];
            fs.Read(rawData, 0, (int)FileSize);
            fs.Close();

            conn.Open();

            SQL = "INSERT INTO bindata(name,data) VALUES( ?,  ?)";

            cmd.Connection = conn;
            cmd.CommandText = SQL;
            cmd.Parameters.Clear();



            cmd.Parameters.Add("name", OdbcType.NVarChar).Value = i.Name;
            cmd.Parameters.Add("data", OdbcType.VarBinary).Value = rawData;

            cmd.ExecuteNonQuery();

            Console.WriteLine("File Inserted into database successfully!",
                "Success!");

            conn.Close();
        }
    }
}
catch (OdbcException MyOdbcException) //Catch any ODBC exception ..
{
    for (int i = 0; i < MyOdbcException.Errors.Count; i++)
    {
        Console.Write("ERROR #" + i + "\n" +
                      "Message: " +
                      MyOdbcException.Errors[i].Message + "\n" +
                      "Native: " +
                      MyOdbcException.Errors[i].NativeError.ToString() + "\n" +
                      "Source: " +
                      MyOdbcException.Errors[i].Source + "\n" +
                      "SQL: " +
                      MyOdbcException.Errors[i].SQLState + "\n");
    }
}

 

using System.Data.Odbc;




string SQL;
long FileSize;
byte[] rawData;
FileStream fs;

//Connection string for Connector/ODBC 3.51
string MyConString = "DSN=orcl185;" +
                           "UID=scott;" +
                           "PWD=tiger;";

//Connect to MySQL using Connector/ODBC
OdbcConnection conn = new OdbcConnection(MyConString);


OdbcCommand cmd = new OdbcCommand();

OdbcDataReader myData;


SQL = "SELECT name, data FROM bindata";

try
{
    conn.Open();

    cmd.Connection = conn;
    cmd.CommandText = SQL;

    myData = cmd.ExecuteReader();

    while (myData.Read())
    {

        FileSize = myData.GetBytes(myData.GetOrdinal("data"), 0, null, 0, 0);


        rawData = new byte[FileSize];

        myData.GetBytes(myData.GetOrdinal("data"), 0, rawData, 0, (int)FileSize);

        fs = new FileStream(@"DownLoadFiles\" + myData.GetString(0), FileMode.OpenOrCreate, FileAccess.Write);
        fs.Write(rawData, 0, (int)FileSize);
        fs.Close();

        Console.WriteLine(myData.GetString(0), "File successfully written to disk!", "Success!");

    }

    myData.Close();
    conn.Close();
}
catch (OdbcException MyOdbcException) //Catch any ODBC exception ..
{
    for (int i = 0; i < MyOdbcException.Errors.Count; i++)
    {
        Console.Write("ERROR #" + i + "\n" +
                      "Message: " +
                      MyOdbcException.Errors[i].Message + "\n" +
                      "Native: " +
                      MyOdbcException.Errors[i].NativeError.ToString() + "\n" +
                      "Source: " +
                      MyOdbcException.Errors[i].Source + "\n" +
                      "SQL: " +
                      MyOdbcException.Errors[i].SQLState + "\n");
    }
}

CREATE TABLE "SCOTT"."BINDATA" (
    "ID"   NUMBER(10, 0),
    "NAME" VARCHAR2(512 BYTE),
    "DATA" BLOB
)
 
ALTER TABLE bindata ADD (
    CONSTRAINT bindata_pk PRIMARY KEY ( id )
);
 
CREATE SEQUENCE bindata_sequence;
 
CREATE OR REPLACE TRIGGER bindata_on_insert BEFORE
    INSERT ON bindata
    FOR EACH ROW
BEGIN
    SELECT
        bindata_sequence.NEXTVAL
    INTO :new.id
    FROM
        dual;
 
END;