Reading and Writing BLOB Data to MS SQL or Oracle Database
来源:互联网 发布:win7 64位c语言编译器 编辑:程序博客网 时间:2024/05/21 21:57
Introduction
In this article, i will examine how to store and retrieve binary files such as image or PDF into MS SQL or Oracle database.
Using the code
Reading a File into a Byte Array.
byte[] byteArray = null;using (FileStream fs = new FileStream(FileName, FileMode.Open, FileAccess.Read, FileShare.Read)){ byteArray = new byte[fs.Length]; int iBytesRead = fs.Read(byteArray, 0, (int)fs.Length);}
Saving BLOB data from a file to Oracle.
For oracle, you will have to download ODP.NET from Oracle. The following script will create a table that will hold the Blob data in Oracle.
CREATE TABLE BlobStore ( ID number, BLOBFILE BLOB, DESCRIPTION varchar2(100) );
Now, we would like to write Blob in Oracle using c#.
string sql = " INSERT INTO BlobStore(ID,BLOBFILE,DESCRIPTION) VALUES(:ID, :BLOBFILE, :DESCRIPTION) "; string strconn = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; using (OracleConnection conn = new OracleConnection(strconn)) { conn.Open(); using (OracleCommand cmd = new OracleCommand(sql, conn)) { cmd.Parameters.Add("ID", OracleDbType.Int32, 1, ParameterDirection.Input); cmd.Parameters.Add("BLOBFILE", OracleDbType.Blob, byteArray , ParameterDirection.Input); cmd.Parameters.Add("DESCRIPTION", OracleDbType.Varchar2, "any thing here", ParameterDirection.Input); cmd.ExecuteNonQuery(); } }
In next step, we would like to load a data from oracle to file.
string sql = " select * from BlobStore "; string strconn = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; using (OracleConnection conn = new OracleConnection(strconn)) { conn.Open(); using (OracleCommand cmd = new OracleCommand(sql, conn)) { using (IDataReader dataReader = cmd.ExecuteReader()) { while (dataReader.Read()) { byte[] byteArray= (Byte[])dataReader["BLOBFILE"]; using (FileStream fs = new FileStream(strfn, FileMode.CreateNew, FileAccess.Write)) { fs.Write(byteArray, 0, byteArray.Length); } } } } }
Saving BLOB data from a file to MS SQL
Storing and retrieving Blob data in SQL Server is similar to Oracle. Here are code snippets that show saving and loading in SQL server. The following script will create a table that will hold the Blob data in SQL server.
CREATE TABLE TestTable ( ID int, BlobData varbinary(max), DESCRIPTION nvarchar(100) )
The following code shows how to Load from SQL Server to file.
using (SqlConnection connection = new SqlConnection("ConnectionString")) { connection.Open(); using (SqlCommand command = new SqlCommand("select BlobData from TestTable", connection)) { byte[] buffer = (byte[])command.ExecuteScalar(); using (FileStream fs = new FileStream(@"C:/test.pdf", FileMode.Create)) { fs.Write(buffer, 0, buffer.Length); } } }
The following code shows how to save from byte array to SQL Server.
using (SqlConnection connection = new SqlConnection("ConnectionString")) { connection.Open(); using(SqlCommand cmd = new SqlCommand("INSERT INTO TestTable(ID, BlobData, DESCRIPTION) VALUES (@ID, @BlobData, @DESCRIPTION)", conn)) { cmd.Parameters.Add("@ID", SqlDbType.int).Value = 1; cmd.Parameters.Add("@BlobData", SqlDbType.VarBinary).Value = ByteArray; cmd.Parameters.Add("@DESCRIPTION", SqlDbType.NVarchar).Value = "Any text Description"; cmd.ExecuteNonQuery(); } }
Summary
In this article, we examined how to store and retrieve binary files such as image or PDF into Oracle or MS SQL database
- Reading and Writing BLOB Data to MS SQL or Oracle Database
- Reading and writing to Excel 2007 or Excel 2010 from C#
- Reading and Writing to Binary Files
- Reading /(Writing) Data from / (to) a Descriptor (Dispatch Sources)
- Reading and Writing Logs
- Reading and Writing Logs
- Robust Reading and Writing
- Reading and Writing Files
- PHP Reading A File |and| Writing To A File
- 关于Thinking,Reading and Writing
- Reading and writing text files
- 1.7writing and reading files
- Reading and writing text files
- Reading and writing binary files
- Reading and writing Serializable objects
- Reading and writing text files
- reading and writing xml files
- gradle Reading and writing JSON
- 鼠标DarkField技术
- 设计模式读书笔记之中介者模式(mediator pattern)
- 小赟随之一笔
- [转]PHP中判断空变量empty(),is_null(),isset()的区别
- 2的幂问题
- Reading and Writing BLOB Data to MS SQL or Oracle Database
- [转]mysql 的isnull
- 10 things to make your desktop database apps better
- 江民董事长王江民因心脏病突发逝世 享年59岁
- 网页横幅制作
- DOS命令全集(1)
- DOS命令全集(2)
- 空虚
- 查询借出次数超过2次的所有图书的书号和书名;