手把手教你怎样把文件保存到Oracle数据库

时间：05-30来源：作者：点击数：48

最近给公司开发动态报表程序，通过报表设计器编辑好的模板首先以文件的形式保存，普遍的方法是把模板文件保存到硬盘的某个文件夹下面，然后数据库就保存该模板文件的存放路径即可，可以说这种方法经济实惠，但是要适应灵活多变的报表环境，尤其是可供他人共享别人已经编辑好的报表模板，这就成了问题，有没有更好的解决方案呢？那就是把模板存放到数据库里面，一切就OK了，下面给出具体的程序代码：

第一步：在Oracle数据库新建一张表，SQL语句如下

create table Test
(
ID Number primary key,
TxtFile blob
)

最好通过PL/SQL工具执行SQL语句，建好这张测试表，下面就通过C#具体实现文件的存储了

第二步：VS2008新建一个Windows应用程序，解决方案名：“保存文件到Oracle数据库”，项目名：“SaveFileToOracle”

编写代码之前，首先引入必要的命名空间，本程序要另外引入两个命名空间：using System.Data.OracleClient;

和using System.IO;

先看一下截图：

说明：窗体中央是一个RichTextBox控件，其他不用介绍了，下面是完整的程序代码：

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OracleClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;

namespace 文件保存到数据库
{
    public partial class Form1 : Form
    {
        private string connectionstring = @"server=CW;user id=ls0019999;password=aaaaaa;";
        public Form1()
        {
            InitializeComponent();
            this.button1.Click += new EventHandler(button1_Click);
            this.button2.Click += new EventHandler(button2_Click);
            this.button3.Click += new EventHandler(button3_Click);
            this.button4.Click += new EventHandler(button4_Click);
            this.button5.Click += new EventHandler(button5_Click);
            this.button6.Click += new EventHandler(button6_Click);
            this.button7.Click += new EventHandler(button7_Click);
        }

        void button7_Click(object sender, EventArgs e)
        {
            StringWriteDB(this.textBox1.Text.Trim());
        }

        void button6_Click(object sender, EventArgs e)
        {
            StringAndWrite(this.textBox1.Text.Trim(), this.textBox2.Text.Trim());

        }

        void button5_Click(object sender, EventArgs e)
        {
            StringAndRead(this.textBox1.Text.Trim());
        }

        void button1_Click(object sender, EventArgs e)
        {
            newFile();
        }

        void button4_Click(object sender, EventArgs e)
        {
            deleteFile(this.textBox1.Text.Trim());
        }

        void button3_Click(object sender, EventArgs e)
        {
            CreateNewFile();
        }

        void button2_Click(object sender, EventArgs e)
        {
            SaveFile(this.textBox2.Text.Trim());
        }
        private void SaveFile(string fileIndex)
        {
            FileStream fileStream = new FileStream(Application.StartupPath + "\\" + fileIndex, FileMode.Open, FileAccess.Read);
            BinaryReader filerd = new BinaryReader(fileStream, Encoding.Default);
            byte[] fileByte = new byte[fileStream.Length];
            filerd.Read(fileByte, 0, (int)fileStream.Length);
            OracleConnection con = new OracleConnection(connectionstring);
            try
            {
                con.Open();
                OracleCommand cmd = con.CreateCommand();
                cmd.CommandText = "insert into test (ID,TXTFILE) values(:ID,:TXTFILE)";
                cmd.Parameters.AddWithValue(":ID", this.textBox1.Text.Trim());
                cmd.Parameters.AddWithValue(":TXTFILE", DBNull.Value);
                cmd.Parameters[":TXTFILE"].Value = fileByte;
                cmd.ExecuteNonQuery();
                MessageBox.Show("把文件写入数据库成功！");
            }
            catch (Exception ex)
            {
                MessageBox.Show("错误提示：" + ex.StackTrace);
            }

        }
        private void ReadFile()
        {
            OracleConnection con = new OracleConnection(connectionstring);
            try
            {
                con.Open();
                OracleCommand cmd = con.CreateCommand();
                cmd.CommandText = "select * from test";
                OracleDataAdapter da = new OracleDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        byte[] fileByte = (byte[])ds.Tables[0].Rows[i]["TXTFILE"];
                        this.richTextBox1.AppendText(Encoding.Default.GetString(fileByte));
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("错误提示：" + ex.StackTrace);
                MessageBox.Show("错误原因：" + ex.Message.ToString());
            }
        }
        private void CreateNewFile()
        {
            this.richTextBox1.Clear();
            using (OracleConnection con = new OracleConnection(connectionstring))
            {
                con.Open();
                OracleCommand cmd = con.CreateCommand();
                cmd.CommandText = "select * from test";
                OracleDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    byte[] fileByte = (byte[])reader.GetValue(1);
                    this.richTextBox1.AppendText("[" + reader.GetInt32(0).ToString() + "]" + "：" + Encoding.Default.GetString(fileByte));
                    this.richTextBox1.AppendText("\n");
                }
            }
        }
        private void deleteFile(string index)
        {
            using (OracleConnection con = new OracleConnection(connectionstring))
            {
                con.Open();
                OracleCommand cmd = con.CreateCommand();
                cmd.CommandText = "delete from test where ID=" + int.Parse(index);
                cmd.ExecuteNonQuery();
                MessageBox.Show("删除成功！");
            }
        }
        private void newFile()
        {
            this.richTextBox1.Clear();
            using (OracleConnection con = new OracleConnection(connectionstring))
            {
                con.Open();
                OracleCommand cmd = con.CreateCommand();
                cmd.CommandText = "select * from test";
                OracleDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    byte[] fileByte = (byte[])reader.GetValue(1);
                    FileStream fileStream = new FileStream(Application.StartupPath + "\\" + "QQ.grf", FileMode.Create);
                    fileStream.Write(fileByte, 0, (int)fileStream.Length);
                    this.richTextBox1.AppendText("[" + reader.GetInt32(0).ToString() + "]" + "：" + Encoding.Default.GetString(fileByte));
                    this.richTextBox1.AppendText("\n");
                }
            }
        }
        private void StringAndRead(string fileName)
        {
            //FileInfo myFile = new FileInfo(Application.StartupPath + "\\" + fileName);
            //StreamReader sr = myFile.OpenText();
            StreamReader sr = new StreamReader(Application.StartupPath + "\\" + fileName, Encoding.Default);
            MessageBox.Show(sr.CurrentEncoding.EncodingName);
            this.richTextBox1.Text = sr.ReadToEnd();
            sr.Close();
        }

        private void StringAndWrite(string fromName, string toName)
        {
            StreamReader sr = new StreamReader(Application.StartupPath + "\\" + fromName, Encoding.Default);
            StreamWriter sw = new StreamWriter(Application.StartupPath + "\\" + toName, true, Encoding.Default);
            sw.Write(sr.ReadToEnd());
            sr.Close();
            sw.Close();
        }
        private void StringWriteDB(string fileName)
        {
            this.richTextBox1.Clear();
            using (OracleConnection con = new OracleConnection(connectionstring))
            {
                con.Open();
                OracleCommand cmd = con.CreateCommand();
                cmd.CommandText = "select * from test";
                OracleDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    byte[] fileByte = (byte[])reader.GetValue(1);
                    StreamWriter sw = new StreamWriter(Application.StartupPath + "\\" + fileName, true, Encoding.Default);
                    sw.Write(fileByte);
                    sw.Close();
                    StreamReader sr = new StreamReader(Application.StartupPath + "\\" + fileName, Encoding.Default);
                    this.richTextBox1.Text = sr.ReadToEnd();
                    sr.Close();
                }
            }
        }
    }
}