如何将图像保存为sql server 2008r2中的varbinay

本文关键字:server 2008r2 中的 varbinay sql 图像 保存 | 更新日期: 2023-09-27 18:05:28

我正在创建visual studio10和sql server 2008r2的windows应用程序。我想保存图像在数据库和检索更新。当我使用图像数据类型时,我的查询正确执行,但对于varbinary数据类型,它不起作用。

如何将图像保存为sql server 2008r2中的varbinay

这是一段时间以前的东西,所以可能需要对路径和其他东西进行一些修改(来自旧项目)

    protected void btn_upload_file_server_Click(object sender, EventArgs e)
    {
        if (this.ddl_determinationBit.SelectedIndex != 2)
        {
          btn_upload_file_server.Enabled = false;
          string fileDirectory = @"Y:'Files'"; // Change all reference locations for this: pages Autism.cs & SpecUserAdmin.cs
          string FilePath = Request.PhysicalApplicationPath;
          if (FileUpload1.HasFile)
          {
              string fileExt = ".html";
              string fileName = this.txt_fileName.Text + fileExt;
              string SaveFilePath = fileDirectory + Server.HtmlEncode(fileName);
              FileUpload1.SaveAs(SaveFilePath);

              string connstring = WebConfigurationManager.ConnectionStrings["SomeConnectionString"].ConnectionString;
              SqlConnection conn = new SqlConnection(connstring);
              SqlCommand SaveMyFile = new SqlCommand("usp_store_document", conn);
              SaveMyFile.CommandType = CommandType.StoredProcedure;
              string SQLFileDir = fileDirectory + fileName;
              SaveMyFile.Parameters.Add(new SqlParameter("@fullpath", SQLFileDir));
              SaveMyFile.Parameters.Add(new SqlParameter("@filename", this.txt_fileName.Text));
              SaveMyFile.Parameters.Add(new SqlParameter("@ext", fileExt));
              int detBit = this.ddl_determinationBit.SelectedIndex;
              SaveMyFile.Parameters.Add(new SqlParameter("@document_destination_bit", detBit));
              conn.Open();
              SaveMyFile.Connection = conn;
              SaveMyFile.ExecuteNonQuery();
              conn.Close();
              // delete all files from the file folder
              string[] filepaths = Directory.GetFiles(@"Y:'Files'");
              foreach (string filepath in filepaths)
                  File.Delete(filepath);
              ddl_determinationBit.SelectedIndex = 2;
              txt_fileName.Text = "";
              btn_upload_file_server.Enabled = true;
          }
          else if (ddl_determinationBit.SelectedIndex == 2) lbl_error.Text = "Please where this Document will go";
        }

你还需要这个SQL代码

    --set up database for filestream
    ALTER DATABASE SOAR ADD
    FILEGROUP FileStreamLibrary CONTAINS FILESTREAM;
    GO
    --set file folder location of files
    ALTER DATABASE SOAR ADD FILE (
           NAME = FSGroup1File,
           FILENAME = 'D:'SOAR'FSDATA')
    TO FILEGROUP FileStreamLibrary;
    GO
    /****** Object:  Table [dbo].[tbl_document]    Script Date: 02/01/2012 11:15:15 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[tbl_document](
        [document_ID] [int] IDENTITY(1,1) NOT NULL,
        [documents_10k] [varbinary](max) FILESTREAM  NULL,
        [document_name] [varchar](300) NULL,
        [document_ext] [char](4) NULL,
        [document_path] [varchar](500) NULL,
        [document_destination_bit] [int] NULL,
        [document_GUID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
        [document_date] [datetime] NULL,
     CONSTRAINT [PK__tbl_docu__9679EC941CBC4616] PRIMARY KEY CLUSTERED 
    (
        [document_ID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] FILESTREAM_ON [FileStreamLibrary],
     CONSTRAINT [UQ__tbl_docu__09BF7B501F98B2C1] UNIQUE NONCLUSTERED 
    (
        [document_GUID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY] FILESTREAM_ON [FileStreamLibrary]
    GO
    SET ANSI_PADDING OFF
    GO

    ALTER TABLE [dbo].[tbl_document] ADD  CONSTRAINT [DF__tbl_docum__docum__2180FB33]  DEFAULT ((0)) FOR [document_destination_bit]
    GO
    ALTER TABLE [dbo].[tbl_document] ADD  CONSTRAINT [DF__tbl_docum__docum__22751F6C]  DEFAULT (newid()) FOR [document_GUID]
    GO