手把手教你怎样把文件保存到Oracle数据库
最近给公司开发动态报表程序，通过报表设计器编辑好的模板首先以文件的形式保存，普遍的方法是把模板文件保存到硬盘的某个文件夹下面，然后数据库就保存该模板文件的存放路径即可，可以说这种方法经济实惠，但是要适应灵活多变的报表环境，尤其是可供他人共享别人已经编辑好的报表模板，这就成了问题，有没有更好的解决方案呢？那就是把模板存放到数据库里面，一切就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();
}
}
}
}
}
最后说明：本小程序Demo在VS2008集成环境下编译通过，最好能完整的自己去编写一边，另外补充一下“流”的概念，就可以很简单的实现文件保存数据库，以及怎样从数据库读和写文件，欢迎大家共同探讨，本人QQ:569339578
