SQL存取image图片.doc

C#在SQl中存取图片image[原]
Posted on 2008-07-30 11:54 桦林 阅读(659) 评论(0)  编辑 收藏 网摘 所属分类: A(C#)
（1）控制台应用程序下演示插入图片
public void InsertIMG()
        {
            //将需要存储的图片读取为数据流
            FileStream fs = new FileStream(@"E:\c.jpg", FileMode.Open,FileAccess.Read);
            Byte[] btye2 = new byte[fs.Length];
            fs.Read(btye2 , 0, Convert.ToInt32(fs.Length));
            fs.Close();

            using (SqlConnection conn = new SqlConnection(sqlconnstr))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "insert into T_Img(imgfile) values(@imgfile)";
                SqlParameter par = new SqlParameter("@imgfile", SqlDbType.Image);
                par.Value = bt;
                cmd.Parameters.Add(par);
                int t=(int)(cmd.ExecuteNonQuery());
                if (t > 0)
                {
                    Console.WriteLine("插入成功");
                }
                conn.Close();
            }
        }
（2）控制台应用程序下读出并生成图片到物理位置
public void Read()
        {
            byte[] MyData = new byte[0];
            using (SqlConnection conn = new SqlConnection(sqlconnstr))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "select * from T_img";
                SqlDataReader sdr = cmd.ExecuteReader();
                sdr.Read();
                MyData = (byte[])sdr["ImgFile"];//读取第一个图片的位流
                int ArraySize= MyData.GetUpperBound(0);//获得数据库中存储的位流数组的维度上限，用作读取流的上限
                FileStream fs = new FileStream(@"c:\00.jpg", FileMode.OpenOrCreate, FileAccess.Write);
                fs.Write(MyData, 0, ArraySize);
                fs.Close();   //-- 写入到c:\00.jpg。
                conn.Close();
                Console.WriteLine("读取成功");//查看硬盘上的文件
            }
        }
（3）Web下picshow.aspx页将图片读取出来并写入到浏览器上呈现
    public void Read()
    {
        byte[] MyData = new byte[0];
        using (SqlConnection conn = new SqlConnection(sqlconnstr))
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = "select * from T_img";
            SqlDataReader sdr = cmd.ExecuteReader();
            sdr.Read();
            MyData = (byte[])sdr["ImgFile"];
            Response.ContentType = "image/gif";
            Response.BinaryWrite(MyData);
            conn.Close();
            Response.Write("读取成功");
        }
（4）在web中可以如上picshow.aspx页面读取并显示图片，而真正引用该图片时如下示例
<img src="picshow.aspx" width="500" height="300" />
 （5）Winform下将图片写入到sql数据库image类型字段中的方法和以上方法基本一致，仅区别于可以利用多个对话框来帮助选取存储图片等，各个属性可以方便的利用上
（6）Winform下读取图片在picturebox控件中显示出来
方法一：利用MemoryStream 和System.Drawing.Image
public void Read()
        {
            byte[] MyData = new byte[0];
            using (SqlConnection conn = new SqlConnection(sqlconnstr))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "select * from T_img";
                SqlDataReader sdr = cmd.ExecuteReader();
                sdr.Read();
                MyData = (byte[])sdr["ImgFile"];
                MemoryStream mystream = new MemoryStream(MyData);
                //用指定的数据流来创建一个image图片
                System.Drawing.Image img = System.Drawing.Image.FromStream(mystream, true);

                System.Windows.Forms.PictureBox picbox = new PictureBox();
                picbox.Image = img;
                picbox.Left = 30;
                picbox.Top = 80;
                picbox.Width = 800;
                picbox.Height = 500;
                this.Controls.Add(picbox);
                mystream.Close();
                conn.Close();
            }
        }
   方法二：将流直接读取成图片并写入到物理位置，然后再行利用该图片呈现
void Read()
        {
            using (SqlConnection conn = new SqlConnection(sqlconnstr))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "select * from T_img";
                SqlDataReader sdr = cmd.ExecuteReader();
                sdr.Read();
                byte[] Image_img = (byte[])sdr["ImgFile"];
                if (Image_img.Length == 0)
                {
                    return;
                }
                int filelength = Image_img.Length;
                string imageName = "1.jpg";
                string myUrl = Environment.CurrentDirectory + "\\" + imageName;
                FileStream fs = new FileStream(myUrl, FileMode.OpenOrCreate,FileAccess.Write);
                BinaryWriter BW = new BinaryWriter(fs);
                BW.BaseStream.Write(Image_img, 0, filelength);
                BW.Flush();
                BW.Close();
                System.Windows.Forms.PictureBox picbox = new PictureBox();

                //为picbox添加图片方法一
                //picbox.ImageLocation = myUrl;
                //picbox.Width = 800;
                //picbox.Height = 300;

                //为picbox添加图片方法二
                Bitmap bitmap = new Bitmap(myUrl);
                picbox.Width = 100;//bitmap.Width;
                picbox.Height = 80;//bitmap.Height;
                picbox.Image = (Image)bitmap;
                picbox.SizeMode = System.Windows.Forms.PictureBoxSizeMode.StretchImage;
                picbox.Left = 20;
                picbox.Top = 30;
                this.Controls.Add(picbox);
                conn.Close();

            }
        }

C#实现SQL Server中存取图片、文件
using System;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Data;
using System.IO;

  //将数据写进数据库
  //参数:
  //FilePath    文件路径
  //ConnectionString  连接字符串
  public void SaveDataIntoDatabase(string FilePath,string ConnectionString)
  {
   if(File.Exists(FilePath)==false)
   {
    MessageBox.Show("无法读取文件！","提示",MessageBoxButtons.OK,MessageBoxIcon.Error);
    return;
   }

   //创建文件对象以打开的形式读取文件
   System.IO.FileStream sFileStream=new System.IO.FileStream(FilePath,System.IO.FileMode.Open);
   //分配数组大小
   byte[] bFile=new byte[sFileStream.Length];
   //将文件内容读进数组
   sFileStream.Read(bFile,0,(int)sFileStream.Length);
   //关闭文件对象
   sFileStream.Close();
   //创建连接
   SqlConnection conn=new SqlConnection(ConnectionString);
   SqlCommand com=conn.CreateCommand();
   com.CommandText="Update 表 Set [图片]=@F Where ID='0001'";
   com.CommandType=CommandType.Text;

   SqlParameter sp=new SqlParameter("@F",SqlDbType.Image,bFile.Length,ParameterDirection.Input,false,0,0,null,DataRowVersion.Current,bFile);

   com.Parameters.Add(sp);
   com.ExecuteNonQuery();
   conn.Close();
  }

  //从数据库中读取数据
  //参数:
  //FilePath    文件路径
  //ConnectionString  连接字符串
  public void LoadDataFromDatabase(string FilePath,string ConnectionString)
  {
   //判断文件是否已存在.
   if(File.Exists(FilePath)!=true)
   {
    //如存在则删除
    File.Delete(FilePath);
   }
   //创建连接
   SqlConnection conn=new SqlConnection(ConnectionString);
   SqlCommand com=conn.CreateCommand();
   com.CommandText="Select [图片] From 表 Where ID='0001'";
   com.CommandType=CommandType.Text;
   //用DataReader读取数据
   SqlDataReader sR=com.ExecuteReader();

   sR.Read();
   //判断是否有记录
   if(sR.HasRows==false)
   {
    sR.Close();
    conn.Close();
    return;
   }

   //分配数组大小
   byte[] bFile=new byte[Convert.ToInt32(sR.GetBytes(0,0,null,0,Int32.MaxValue))];
   //将数据读进数组
   sR.GetBytes(0,0,bFile,0,bFile.Length);
   //关闭DataReader
   sR.Close();
   //创建文件对象以创建文件的形式打开文件
   System.IO.FileStream sFileStream=new System.IO.FileStream(FilePath,System.IO.FileMode.Create);
   //将数组的内容写进文件
   sFileStream.Write(bFile,0,bFile.Length);
   //关闭文件
   sFileStream.Close();
  }

C#存取SQL Server数据库之一：二进制存取图片文件　2008-4-10 　网络　点击:634 　[ 评论 ]
-
-
文章搜索： 【点击打包该文章】
【本站开通在线QQ讨论群】

创建项目

1. 添加一个名为RWTest的表到 SQL Server MYTest 数据库。 表字段设置如下：

a. 唯一标识字段名称为"ID"，类型为Int。

b. 名称为"Description"的VarChar类型的字段，字段长度为50。

c. 名称为"ImgField" 的Image 类型的字段。

2. 启动 Visual Studio .NET， 并创建一个新的 Visual C# Windows 应用程序项目。

3. 从工具栏中拖两个Button 控件到默认窗体， Form1。

4. 在属性窗口中修改Name为buttonFileToDB， Text 属性为从文件保存到数据库， 然后修改Name为buttonDBToFile ，Text 属性为从数据库保存到文件。

5 从工具栏放置2个TextBox和1个PictureBox控件：Name属性分别为：textBoxGetID, textBoxGetDescription, pictureBoxGetImage, 显示从数据库读出的ID，Description，ImgField字段。

源码实例

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Text;

using System.Windows.Forms;

using System.Data.SqlClient;

using System.IO;

using System.Collections;

//数据库说明：MyTest数据库，RWTest表,包含3列：ID(int),Description(varchar(50),ImgField(Image)

namespace RWImgSQL

{

public partial class Form1 : Form

{

public Form1()

{

InitializeComponent();

}

private void buttonFileToDB_Click(object sender, EventArgs e)

{

SqlConnection sqlConnection = new SqlConnection("Data Source = liuxueqin; Initial Catalog=MyTest;Integrated Security=True");

SqlDataAdapter sqlDataAdapter = new SqlDataAdapter("Select * from RWTest", sqlConnection);

SqlCommandBuilder sqlCommandBuilder = new SqlCommandBuilder(sqlDataAdapter);

DataSet dataSet = new DataSet("RWTest");

sqlDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;//确定现有 DataSet 架构与传入数据不匹配时需要执行的操作。

String CurrentExeName = System.Diagnostics.Process.GetCurrentProcess().MainModule.FileName;

string ImageFile = System.IO.Path.GetDirectoryName(CurrentExeName) + "\\F1.jpg";

System.IO.FileStream fileStream = new FileStream(ImageFile, FileMode.OpenOrCreate, FileAccess.ReadWrite);

byte[] myData = new byte[fileStream.Length];

fileStream.Read(myData, 0, System.Convert.ToInt32(fileStream.Length));//从流中读取字节块，并将数据写入到该缓冲区

fileStream.Close();

try

{

sqlDataAdapter.Fill(dataSet, "RWTest");

//DataRow表示DataTable中的一行数据

System.Data.DataRow dataRow;

dataRow = dataSet.Tables["RWTest"].NewRow();

dataRow1["ID"] = 1;

dataRow1["Description"] = "This would be description text";

dataRow1["ImgField"] = myData;

dataSet.Tables["RWTest"].Rows.Add(dataRow);

sqlDataAdapter.Update(dataSet, "RWTest");

sqlConnection.Close();

MessageBox.Show("写入数据库成功！", " 信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);

}

catch (Exception ex)

{

if (sqlConnection.State == ConnectionState.Open)

{

sqlConnection.Close();

}

MessageBox.Show("写入数据库失败"+ex.Message, " 信息提示", MessageBoxButtons.OK, MessageBoxIcon.Error);

}

}

private void buttonDBToFile_Click(object sender, EventArgs e)

{

SqlConnection sqlConnection = new SqlConnection("Data Source=liuxueqin;Initial Catalog=MyTest;Integrated Security=True");

SqlDataAdapter sqlDataAdapter = new SqlDataAdapter("Select * from RWTest", sqlConnection);

SqlCommandBuilder sqlCommandBuilder = new SqlCommandBuilder(sqlDataAdapter);

DataSet dataSet = new DataSet("RWTest");

byte[] MyData = new byte[0];

sqlDataAdapter.Fill(dataSet, "RWTest");

DataRow myRow;

myRow = dataSet.Tables["RWTest"].Rows[0];

MyData = (byte[])myRow["imgField"];

int ArraySize = MyData.GetUpperBound(0);

String CurrentExeName = System.Diagnostics.Process.GetCurrentProcess().MainModule.FileName;

string ImageFile = System.IO.Path.GetDirectoryName(CurrentExeName) + "\\F2.jpg";

FileStream fs = new FileStream(ImageFile, FileMode.OpenOrCreate, FileAccess.Write);

fs.Write(MyData, 0, ArraySize);

fs.Close();

//---在界面上的2个textBox和1个pictureBox，用来显示从数据库中读出的ID，Description，ImageField字段

textBoxGetID.Text = myRow["ID"].ToString();

textBoxGetDescription.Text = myRow["Description"].ToString();

pictureBoxGetImage.Image = Image.FromFile(ImageFile);

if (sqlConnection.State == ConnectionState.Open)

{

sqlConnection.Close();

}

MessageBox.Show(" 从数据库读出数据成功！", " 信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);

}

}

}

文章出处：

C# 版本
DataGridShowImage.aspx

窗体顶端

从数据库中取得照片并显示在DataGrid中

窗体底端

DataGridShowImage.aspx.cs
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

namespace eMeng.Exam.DataGridShowImage
{
///
/// DataGridShowImage 的摘要说明。
///
public class DataGridShowImage : System.Web.UI.Page
{
protected System.Web.UI.WebControls.DataGrid DG_Persons;

private void Page_Load(object sender, System.EventArgs e)
{
// 在此处放置用户代码以初始化页面
if(!this.IsPostBack)
{
BindGrid();
}

}
private void BindGrid()
{
string strCnn = "Data Source=.;Initial Catalog=mxh;User Id=sa;Password=;";
SqlConnection myConnection = new SqlConnection(strCnn);
SqlCommand myCommand = new SqlCommand("SELECT * FROM Person", myConnection);
myCommand.CommandType = CommandType.Text;
try
{
myConnection.Open();
DG_Persons.DataSource = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
DG_Persons.DataBind();
}
catch(SqlException SQLexc)
{
Response.Write("提取数据时出现错误：" + SQLexc.ToString());
}
}
protected string FormatURL(object strArgument)
{
return "ReadImage.aspx?id=" + strArgumen