- 下面的代码实现向SQL Server数据库添加图片和文字的功能。
- 首先,在SQL查询分析器中执行下面的语句,以创建表和存储过程。
- Drop Table Person
- Go
- Create Table Person
- (
- PersonID Int Identity,
- PersonEmail Varchar(255),
- PersonName Varchar(255),
- PersonSex Char(1),
- PersonDOB DateTime,
- PersonImage Image,
- PersonImageType Varchar(255)
- )
- Drop Proc sp_person_isp
- Go
- Create Proc sp_person_isp
- @PersonEmail Varchar(255),
- @PersonName Varchar(255),
- @PersonSex Char(1),
- @PersonDOB DateTime,
- @PersonImage Image,
- @PersonImageType Varchar(255)
- As
- Begin
- Insert into Person
- (PersonEmail, PersonName, PersonSex,
- PersonDOB, PersonImage, PersonImageType)
- Values
- (@PersonEmail, @PersonName, @PersonSex,
- @PersonDOB, @PersonImage, @PersonImageType)
- End
- Go
- 下面就是完整的代码,拷贝即可运行:
- <%@ Import Namespace="System.IO" %>
- <%@ Import Namespace="System.Data.SqlClient" %>
- <%@ Import Namespace="System.Data" %>
- <%@ Page Language="vb" %>
- <HTML>
- <HEAD>
- <title>向SQL Server插入图片</title>
- <script runat="server">
- Public Sub AddPerson(sender As Object, e As EventArgs)
- Dim intImageSize As Int64
- Dim strImageType As String
- Dim ImageStream As Stream
- ' 获得图片的大小
- intImageSize = PersonImage.PostedFile.ContentLength
- ' 获得图片类型
- strImageType = PersonImage.PostedFile.ContentType
- '读取图片
- ImageStream = PersonImage.PostedFile.InputStream
- Dim ImageContent(intImageSize) As Byte
- Dim intStatus As Integer
- intStatus = ImageStream.Read(ImageContent, 0, intImageSize)
- ' 创建Connection和Command对象
- Dim strCnn As String = "Data Source=.;Initial Catalog=mxh;User Id=sa;Password=;"
- Dim myConnection As New SqlConnection(strCnn)
- Dim myCommand As New SqlCommand("sp_person_isp", myConnection)
- ' 使用存储过程
- myCommand.CommandType = CommandType.StoredProcedure
- ' 向存储过程添加参数
- Dim prmEmail As New SqlParameter("@PersonEmail", SqlDbType.VarChar, 255)
- prmEmail.Value = txtPersonEmail.Text
- myCommand.Parameters.Add(prmEmail)
- Dim prmName As New SqlParameter("@PersonName", SqlDbType.VarChar, 255)
- prmName.Value = txtPersonName.Text
- myCommand.Parameters.Add(prmName)
- Dim prmSex As New SqlParameter("@PersonSex", SqlDbType.Char, 1)
- If sexMale.Checked Then
- prmSex.Value = "M"
- Else
- prmSex.Value = "F"
- End If
- myCommand.Parameters.Add(prmSex)
- Dim prmPersonDOB As New SqlParameter("@PersonDOB", SqlDbType.DateTime)
- prmPersonDOB.Value = txtPersonDob.Text
- myCommand.Parameters.Add(prmPersonDOB)
- Dim prmPersonImage As New SqlParameter("@PersonImage", SqlDbType.Image)
- prmPersonImage.Value = ImageContent
- myCommand.Parameters.Add(prmPersonImage)
- Dim prmPersonImageType As New SqlParameter("@PersonImageType", SqlDbType.VarChar, 255)
- prmPersonImageType.Value = strImageType
- myCommand.Parameters.Add(prmPersonImageType)
- Try
- myConnection.Open()
- myCommand.ExecuteNonQuery()
- myConnection.Close()
- Response.Write("添加成功!")
- Catch SQLexc As SqlException
- Response.Write("添加失败,原因:" & SQLexc.ToString())
- End Try
- End Sub
- </script>
- </HEAD>
- <body style="FONT: 9pt 宋体">
- <form enctype="multipart/form-data" runat="server" ID="Form1">
- <asp:Table Runat="server" Width="50%" BorderWidth="1" BackColor="Beige" ID="Table1"
- Font-Name="宋体" Font-Size="9pt">
- <asp:TableRow>
- <asp:TableCell ColumnSpan="2" BackColor="#ff0000">
- <asp:Label ForeColor="#ffffff" font-bold="True" Runat="server" Text="添加新用户" ID="Label1" />
- </asp:TableCell>
- </asp:TableRow>
- <asp:TableRow>
- <asp:TableCell HorizontalAlign="Right">
- <asp:Label Runat="server" Text="姓名" ID="Label2" />
- </asp:TableCell>
- <asp:TableCell>
- <asp:TextBox id="txtPersonName" Runat="server" />
- </asp:TableCell>
- </asp:TableRow>
- <asp:TableRow>
- <asp:TableCell HorizontalAlign="Right">
- <asp:Label Runat="server" Text="电子邮件" ID="Label3" />
- </asp:TableCell>
- <asp:TableCell>
- <asp:TextBox id="txtPersonEmail" Runat="server" />
- </asp:TableCell>
- </asp:TableRow>
- <asp:TableRow>
- <asp:TableCell HorizontalAlign="Right">
- <asp:Label Runat="server" Text="性别" ID="Label4"/>
- </asp:TableCell>
- <asp:TableCell>
- <asp:RadioButton GroupName="sex" Text="男" ID="sexMale" Runat="server" />
- <asp:RadioButton GroupName="sex" Text="女" ID="sexFeMale" Runat="server" />
- </asp:TableCell>
- </asp:TableRow>
- <asp:TableRow>
- <asp:TableCell HorizontalAlign="Right">
- <asp:Label Runat="server" Text="出生日期" ID="Label5"/>
- </asp:TableCell>
- <asp:TableCell>
- <asp:TextBox id="txtPersonDOB" Runat="server" />
- </asp:TableCell>
- </asp:TableRow>
- <asp:TableRow>
- <asp:TableCell HorizontalAlign="Right">
- <asp:Label Runat="server" Text="照片" ID="Label6"/>
- </asp:TableCell>
- <asp:TableCell>
- <input type="file" id="PersonImage" runat="server" NAME="PersonImage" /></asp:TableCell>
- </asp:TableRow>
- <asp:TableRow>
- <asp:TableCell ColumnSpan="2" HorizontalAlign="Center">
- <asp:Button Text=" 添 加 " OnClick="AddPerson" Runat="server" ID="Button1"/>
- </asp:TableCell>
- </asp:TableRow>
- </asp:Table>
- </form>
- </body>
- </HTML>
================================================================================
- 下面的代码实现从SQL Server数据库提取图片并显示在DataGrid的功能。
- 下面就是完整的代码,拷贝即可运行:
- <%@ Page Language="vb" %>
- <%@ Import Namespace="System.Data" %>
- <%@ Import Namespace="System.Data.SqlClient" %>
- <html>
- <head>
- <title>用户列表</title>
- <script runat=server>
- Sub Page_Load(sender As Object, e As EventArgs)
- If Not Page.IsPostBack Then
- BindGrid()
- End If
- End Sub
- Private Sub BindGrid()
- Dim strCnn As String = "Data Source=.;Initial Catalog=mxh;User Id=sa;Password=;"
- Dim myConnection As SqlConnection = New SqlConnection(strCnn)
- Dim myCommand As SqlCommand = New SqlCommand("SELECT * FROM Person", myConnection)
- myCommand.CommandType = CommandType.Text
- Try
- myConnection.Open()
- DG_Persons.DataSource = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
- DG_Persons.DataBind()
- Catch SQLexc As SqlException
- Response.Write("Error occured while Generating Data. Error is " & SQLexc.ToString())
- End Try
- End Sub
- Function FormatURL(strArgument) as String
- Return ("ReadImage.aspx?id=" & strArgument)
- End Function
- </script>
- </head>
- <body style="font: 9pt 宋体">
- <h3 align=center>从数据库中取得照片并显示在DataGrid中</h3>
- <form id="Form1" method="post" runat="server">
- <asp:DataGrid ID="DG_Persons" AutoGenerateColumns=False Width="99%"
- HeaderStyle-BackColor="#ff0000" HeaderStyle-Font-Bold="True" HeaderStyle-ForeColor="#ffffff"
- ItemStyle-BackColor=Beige BorderColor="#000000" Runat=server HeaderStyle-HorizontalAlign=Center>
- <Columns>
- <asp:TemplateColumn HeaderText="姓名">
- <ItemTemplate>
- <asp:Label Runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "PersonName") %>' ID="Label1"/>
- </ItemTemplate>
- </asp:TemplateColumn>
- <asp:TemplateColumn HeaderText="电子邮件">
- <ItemTemplate>
- <asp:Label Runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "PersonEmail") %>' ID="Label2"/>
- </ItemTemplate>
- </asp:TemplateColumn>
- <asp:TemplateColumn HeaderText="性别">
- <ItemTemplate>
- <asp:Label Runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "PersonSex") %>' ID="Label3"/>
- </ItemTemplate>
- </asp:TemplateColumn>
- <asp:TemplateColumn HeaderText="出生日期">
- <ItemTemplate>
- <asp:Label Runat="server"
- Text='<%# DataBinder.Eval(Container.DataItem, "PersonDOB") %>' ID="Label4"/>
- </ItemTemplate>
- </asp:TemplateColumn>
- <asp:TemplateColumn HeaderText="照片">
- <ItemTemplate>
- <asp:Image Runat=server ID="Image1" Width="150" Height="125"
- ImageUrl='<%# FormatURL(DataBinder.Eval(Container.DataItem, "PersonID")) %>' />
- </ItemTemplate>
- </asp:TemplateColumn>
- </Columns>
- </asp:DataGrid>
- </form>
- </body>
- </html>
- ReadImage.aspx
- <%@ Page Language="vb" %>
- <%@ Import Namespace="System.Data" %>
- <%@ Import Namespace="System.Data.SqlClient" %>
- <HTML>
- <HEAD>
- <script runat=server>
- Public Sub Page_Load(sender As Object, e As EventArgs)
- Dim strImageID as String = Request.QueryString("id")
- Dim myConnection As New SqlConnection("Data Source=.;Initial Catalog=mxh;User Id=sa;Password=;")
- Dim myCommand As New SqlCommand("Select PersonImageType, PersonImage from Person Where PersonID=" _
- + strImageID, myConnection)
- Try
- myConnection.Open()
- Dim myDataReader as SqlDataReader
- myDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
- Do While (myDataReader.Read())
- Response.ContentType = myDataReader.Item("PersonImageType")
- Response.BinaryWrite(myDataReader.Item("PersonImage"))
- Loop
- myConnection.Close()
- Catch SQLexc As SqlException
- End Try
- End Sub
- </script>
- </HEAD>
- <body>
- <form runat="server" ID="Form1"></form>
- </body>
- </HTML>
- C# 版本
- DataGridShowImage.aspx
- <%@ Page language="c#" debug="true" Codebehind="DataGridShowImage.aspx.cs" AutoEventWireup="false"
- Inherits="eMeng.Exam.DataGridShowImage.DataGridShowImage" %>
- <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
- <HTML>
- <HEAD>
- <title>从数据库中取得照片并显示在DataGrid中</title>
- <meta name="GENERATOR" Content="Microsoft Visual Studio 7.0">
- <meta name="CODE_LANGUAGE" Content="C#">
- <meta name="vs_defaultClientScript" content="JavaScript">
- <meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5">
- </HEAD>
- <body MS_POSITIONING="GridLayout">
- <form id="DataGridShowImage" method="post" runat="server">
- <h3 align="center">从数据库中取得照片并显示在DataGrid中</h3>
- <asp:DataGrid ID="DG_Persons" AutoGenerateColumns="False" Width="99%" HeaderStyle-BackColor="#ff0000"
- HeaderStyle-Font-Bold="True" HeaderStyle-ForeColor="#ffffff" ItemStyle-BackColor="Beige"
- BorderColor="#000000" Runat="server" HeaderStyle-HorizontalAlign="Center">
- <Columns>
- <asp:TemplateColumn HeaderText="姓名">
- <ItemTemplate>
- <asp:Label Runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "PersonName") %>' ID="Label1"/>
- </ItemTemplate>
- </asp:TemplateColumn>
- <asp:TemplateColumn HeaderText="电子邮件">
- <ItemTemplate>
- <asp:Label Runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "PersonEmail") %>' ID="Label2"/>
- </ItemTemplate>
- </asp:TemplateColumn>
- <asp:TemplateColumn HeaderText="性别">
- <ItemTemplate>
- <asp:Label Runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "PersonSex") %>' ID="Label3"/>
- </ItemTemplate>
- </asp:TemplateColumn>
- <asp:TemplateColumn HeaderText="出生日期">
- <ItemTemplate>
- <asp:Label Runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "PersonDOB") %>' ID="Label4"/>
- </ItemTemplate>
- </asp:TemplateColumn>
- <asp:TemplateColumn HeaderText="照片">
- <ItemTemplate>
- <asp:Image Runat=server ID="Image1"
- ImageUrl='<%# FormatURL(DataBinder.Eval(Container.DataItem, "PersonID")) %>' />
- </ItemTemplate>
- </asp:TemplateColumn>
- </Columns>
- </asp:DataGrid>
- </form>
- </body>
- </HTML>
- 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
- {
- /// <summary>
- /// DataGridShowImage 的摘要说明。
- /// </summary>
- 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=" + strArgument.ToString();
- }
- #region Web Form Designer generated code
- override protected void OnInit(EventArgs e)
- {
- //
- // CODEGEN:该调用是 ASP.NET Web 窗体设计器所必需的。
- //
- InitializeComponent();
- base.OnInit(e);
- }
- /// <summary>
- /// 设计器支持所需的方法 - 不要使用代码编辑器修改
- /// 此方法的内容。
- /// </summary>
- private void InitializeComponent()
- {
- this.Load += new System.EventHandler(this.Page_Load);
- }
- #endregion
- }
- }
- ReadImage.aspx
- <%@ Page language="c#" Codebehind="ReadImage.aspx.cs" AutoEventWireup="false"
- Inherits="eMeng.Exam.DataGridShowImage.ReadImage" %>
- ReadImage.aspx.cs
- using System;
- using System.Collections;
- using System.ComponentModel;
- using System.Data;
- using System.Drawing;
- using System.Web;
- using System.Data.SqlClient;
- using System.Web.SessionState;
- using System.Web.UI;
- using System.Web.UI.WebControls;
- using System.Web.UI.HtmlControls;
- namespace eMeng.Exam.DataGridShowImage
- {
- /// <summary>
- /// ReadImage 的摘要说明。
- /// </summary>
- public class ReadImage : System.Web.UI.Page
- {
- private void Page_Load(object sender, System.EventArgs e)
- {
- // 在此处放置用户代码以初始化页面
- string strImageID = Request.QueryString["id"];
- SqlConnection myConnection = new SqlConnection("Data Source=.;Initial Catalog=mxh;User Id=sa;Password=;");
- SqlCommand myCommand = new SqlCommand("Select PersonImageType, PersonImage from Person Where PersonID="
- + strImageID, myConnection);
- try
- {
- myConnection.Open();
- SqlDataReader myDataReader;
- myDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
- if(myDataReader.Read())
- {
- Response.Clear();
- Response.ContentType = myDataReader["PersonImageType"].ToString();
- Response.BinaryWrite((byte[])myDataReader["PersonImage"]);
- }
- myConnection.Close();
- }
- catch (SqlException SQLexc)
- {
- }
- Response.End();
- }
- #region Web Form Designer generated code
- override protected void OnInit(EventArgs e)
- {
- //
- // CODEGEN:该调用是 ASP.NET Web 窗体设计器所必需的。
- //
- InitializeComponent();
- base.OnInit(e);
- }
- /// <summary>
- /// 设计器支持所需的方法 - 不要使用代码编辑器修改
- /// 此方法的内容。
- /// </summary>
- private void InitializeComponent()
- {
- this.Load += new System.EventHandler(this.Page_Load);
- }
- #endregion
- }
- }
=======================================================================
转贴地址:
向数据库中保存图片:
http://dotnet.aspx.cc/article/2a5dd7c6-a45a-48ab-a2e8-342a29f17506/read.aspx
从SQL Server数据库提取图片并显示在DataGrid:
http://dotnet.aspx.cc/article/ecd9ae16-8ff0-4a1c-9b9f-5e8b641cb1b1/read.aspx