如何将图像保存为sql server 2008r2中的varbinay
本文关键字:server 2008r2 中的 varbinay sql 图像 保存 | 更新日期: 2023-09-27 18:05:28
我正在创建visual studio10和sql server 2008r2的windows应用程序。我想保存图像在数据库和检索更新。当我使用图像数据类型时,我的查询正确执行,但对于varbinary数据类型,它不起作用。
这是一段时间以前的东西,所以可能需要对路径和其他东西进行一些修改(来自旧项目)
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