스크랩 SQL Server 2008 의 File Stream 을 이용하여 Grid View 에 이미지 보여주기

[獨先生]

FileStream data type is a very important feature of SQL Server 2008 and gradually getting popular amongst developer for it’s feasibility. And in the past few days specially after “Configure SQL Server 2008 for File Stream” post. I received several feedbacks regarding the usage of FileStream with Ado.net and Frankly there is not much stuff available on Google for this topic.

In this post, I will guide you to use FileStream Data type in Ado.net. But before we start make sure you have configure your SQL Server 2008 instance to use File Stream Data type and for this you can read this post.

Once you finish with the Configuration execute the following script

SQL Server 2008 에서 아래와 같이 테이블을 생성합니다. 제일 주의해서 볼 부분은 [SystemFile] [varbinary](max) FILESTREAM  NULL, 입니다. FILESTREAM 데이터 타입이라고 명시해줘야 합니다. size 는 최대값으로 max 했습니다.

1: CREATE TABLE [dbo].[tbl_Files](

   2:     [Id] [uniqueidentifier] ROWGUIDCOL  NOT NULL,

   3:     [SystemNumber] [int] NOT NULL,

   4:     [SystemFile] [varbinary](max) FILESTREAM  NULL,

   5:     [FileType] [varchar](5) NULL,

   6: UNIQUE NONCLUSTERED 

   7: (

   8:     [Id] ASC

   9: )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
         ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

  10: ) ON [PRIMARY] FILESTREAM_ON [FileStreamGroup1]

  11:  

  12: GO

  13:  

  14: ALTER TABLE [dbo].[tbl_Files] ADD  CONSTRAINT [DF_tbl_Files_Id]  DEFAULT (newid()) FOR [Id]

This will create a table with FileStream Data type. Notice the FileType field I have used here to determine the type of file which we will use when we were downloading the file.

Now that we have created a table, lets now move on to the Stored Procedures by which we will access this newly created table.

보안설정 (Security Setting:)  

: 데이터베이스 연결자 구문에 “Integrated Security = true” 명시하거나, ASP.NET 의 Impersonation(가장) 을 사용하라고 하네요.

Don’t get confused with the heading, there is no additional setting required. You need to do one of the two available options. Either you need to specify “Integrated Security = true” in Connection String or you need to implement Asp.net Impersonation. It is there because SQL Server 2008 will not allow un authenticated user or instance to read/modify the file.

Most of the developers usually aware of Integrated Security stuff but let me give a little detail about asp.net impersonation. Actually, it is a way to Authorize the Instance of your asp.net application on SQL Server by using Credential Information.

Following links will help you to understand or implement impersonation.

impersonation (가장) 에 대해 자세히 알고자 하시는 분은 아래의 URL 을 참고하시기 바랍니다.

http://blogs.msdn.com/shawnfa/archive/2005/03/21/400088.aspx

http://blogs.msdn.com/saurabhkv/archive/2008/05/29/windowsidentity-impersonation-using-c-code.aspx

http://www.west-wind.com/WebLog/posts/1572.aspx

INSERT 저장프로시저 구현 (add Procedure:)

Lets create a procedure call it “”FileAdd” and past the following script.

1: Create PROCEDURE [dbo].[FileAdd]

   2: @SystemNumber int,

   3: @FileType varchar(5),

   4: @filepath varchar(max) output

   5: AS

   6: BEGIN

   7:     -- SET NOCOUNT ON added to prevent extra result sets from

   8:     -- interfering with SELECT statements.

   9:     SET NOCOUNT ON;

  10:  

  11:     DECLARE @ID UNIQUEIDENTIFIER

  12:     SET @ID = NEWID()

  13:  

  14:     INSERT INTO [dbo].[tbl_Files]

  15:     ([Id],[SystemNumber],SystemFile,FileType)

  16:     VALUES (@ID ,@SystemNumber,CAST('' AS VARBINARY(MAX)),@FileType)

  17:     

  18:     select @filepath = SystemFile.PathName() from tbl_Files where Id = @ID

  19:  

  20:     

  21:  

  22: END

In the above procedure, we add new records in our table and just pass empty (null) to the FileStream field because we first want our SQL Server to create an empty file on NTFS location which we can access from our code behind by using the path which we have taken as Output Parameter here.

notice the SystemFile.PathName(), it is a new function introduced in SQL Server 2008 which will return the NTFS location of the file.

SELECT 저장프로시저 (Get Procedure:)

Create a procedure and call it “FileGet”

1: CREATE PROCEDURE [dbo].[FileGet]

   2: @Id varchar(50)

   3: AS

   4: BEGIN

   5:      select  SystemFile.PathName(),FileType from tbl_Files where Id = @ID

   6: END

This is a simple stuff, we are returning PathName and FileType by specifying ID. Just to read the record.

저장 및 업로드 (Upload and Store:) : FileUpload control 컨트롤에 아래와 같이 기술하세요.

To save the file in the file stream, we will use FileUpload control to upload the file and then save it to FileStream field. For that we have created a page and drag FileUpload control with an Upload button.

Now on the click event of the button write the following code.

1: byte[] buffer = new byte[(int)FileUpload1.FileContent.Length];

   2: FileUpload1.FileContent.Read(buffer, 0, buffer.Length);

   3:  

   4:  

   5: if (FileUpload1.FileContent.Length > 0)

   6: {

   7:     SqlConnection objSqlCon = new SqlConnection(ConfigurationManager.
            ConnectionStrings["ConnectionString"].ConnectionString);

   8:     objSqlCon.Open();

   9:     SqlTransaction objSqlTran = objSqlCon.BeginTransaction();

  10:  

  11:     SqlCommand objSqlCmd = new SqlCommand("FileAdd",objSqlCon,objSqlTran);

  12:     objSqlCmd.CommandType = CommandType.StoredProcedure;

  13:  

  14:     SqlParameter objSqlParam1 = new SqlParameter("@SystemNumber", SqlDbType.Int);

  15:     objSqlParam1.Value = "1";

  16:  

  17:     SqlParameter objSqlParam2 = new SqlParameter("@FileType", SqlDbType.VarChar,4);

  18:     objSqlParam2.Value = System.IO.Path.GetExtension(FileUpload1.FileName);

  19:  

  20:     SqlParameter objSqlParamOutput = new SqlParameter("@filepath", SqlDbType.VarChar, -1);

  21:     objSqlParamOutput.Direction = ParameterDirection.Output;

  22:  

  23:     objSqlCmd.Parameters.Add(objSqlParam2);

  24:     objSqlCmd.Parameters.Add(objSqlParam1);

  25:     objSqlCmd.Parameters.Add(objSqlParamOutput);

  26:  

  27:  

  28:     objSqlCmd.ExecuteNonQuery();

  29:  

  30:     string Path = objSqlCmd.Parameters["@filepath"].Value.ToString();

  31:  

  32:     objSqlCmd = new SqlCommand("SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()", objSqlCon, objSqlTran);

  33:  

  34:     byte[] objContext = (byte[])objSqlCmd.ExecuteScalar();

  35:     

  36:  

  37:     SqlFileStream objSqlFileStream = new SqlFileStream(Path, objContext, FileAccess.Write);

  38:     

  39:     objSqlFileStream.Write(buffer, 0, buffer.Length);

  40:     objSqlFileStream.Close();

  41:  

  42:     objSqlTran.Commit();

위의 코드에 대한 설명입니다. 37 라인인에 SqlFileStream 객체를 사용하여 이를 WRITE 하는 부분이 제일 중요할 듯 싶네요.

Well, in the first two lines we have saved the uploaded file in byte and call this variable “buffer”.

As we are simply using ADO.net, that is why in line 7 and 8 we have created and open a connection. Where as it is worth to mention here, we need to use transaction when we want to do any operation on FileStream field that is why we have begin a new transaction in line no 9.

On line number 11 to 30, we have a simply setup command object and parameter stuff and then execute the procedure and save the output parameter in a variable called “Path”.

This new variable will contain the NTFS location of the file which is stored on SQL Server FileStream. It should be clear that, this file is empty yet as we have not stored any thing in it.

Now on line number 32 we have reused command object and this time we are executing a simple statement “GET_FILESTREAM_TRANSACTION_CONTEXT”. It is also a newly added feature in SQL Server 2008 which will return current transaction context to be used in the next few lines. Now, on line number 34 we have stored the output of the above statement in byte.

In line number 37, here is some thing new which is called “SqlFileStream”. It is a new class which you can find under “System.Data.SqlTypes”. It seems more like FileStream of “System.IO” but it should be cleared here that the file stored in FileStream field cannot be access using regular file stream object of “System.IO” we need to use SqlFileStream to access those files which are stored in FileStream field.

In line no 39 and on, we are writing the file with the content of uploaded file (Remember we have stored our uploaded file in bytes and call it “buffer”). and that’s it.

저장된 파일을 읽어오기(Read The Stored File:)

We have finished with storing the file, now lets see how can we read this file back. To do this, Drag a Grid View and make it similar to the following

1: <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"

   2:        onrowcommand="GridView1_RowCommand">

   3:        <Columns>

   4:            <asp:BoundField DataField="ID" HeaderText="ID" />

   5:            <asp:BoundField DataField="SystemNumber" HeaderText="System Id" />

   6:            <asp:TemplateField>

   7:                <ItemTemplate>

   8:                    <asp:LinkButton ID="lbGetFile" runat="server" CommandName="GetFile" 
                    CommandArgument='<%#Eval‎("ID") %>' Text="Get File"></asp:LinkButton>

   9:                </ItemTemplate>

  10:            </asp:TemplateField>

  11:        </Columns>

  12:    

  13:    </asp:GridView>

And bind the GridView using the following code.

아래와 같이 GridView 바인딩 컨트롤에 바인딩 합니다.

1: protected void bindData()

   2: {

   3:     SqlConnection objSqlCon = new SqlConnection(ConfigurationManager.
              ConnectionStrings["ConnectionString"].ConnectionString);

   4:     objSqlCon.Open();

   5:  

   6:     SqlCommand objSqlCmd = new SqlCommand("Select * from tbl_Files", objSqlCon);

   7:     SqlDataAdapter objSqlDat = new SqlDataAdapter(objSqlCmd);

   8:     DataTable objdt = new DataTable();

   9:     objSqlDat.Fill(objdt);

  10:  

  11:     GridView1.DataSource = objdt;

  12:     GridView1.DataBind();

  13: }

Well, the above markup and the code is enough self explaining but the little important stuff to mention here is the link button. We will use the same link button to download the stored file. Lets quickly move on to the RowCommand implementation of the GridView.

GridView 의 RowCommand 이벤트에 아래와 같이 기술합니다.

1: protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)

   2: {

   3:     if (e.CommandName == "GetFile")

   4:     {

   5:  

   6:         SqlConnection objSqlCon = new SqlConnection(ConfigurationManager.
                 ConnectionStrings["ConnectionString"].ConnectionString);

   7:         objSqlCon.Open();

   8:         SqlTransaction objSqlTran = objSqlCon.BeginTransaction();

   9:  

  10:         SqlCommand objSqlCmd = new SqlCommand("FileGet", objSqlCon, objSqlTran);

  11:         objSqlCmd.CommandType = CommandType.StoredProcedure;

  12:  

  13:         SqlParameter objSqlParam1 = new SqlParameter("@ID", SqlDbType.VarChar);

  14:         objSqlParam1.Value = e.CommandArgument;

  15:  

  16:         objSqlCmd.Parameters.Add(objSqlParam1);

  17:         string path = string.Empty;

  18:         string fileType = string.Empty;

  19:  

  20:         using (SqlDataReader sdr = objSqlCmd.ExecuteReader())

  21:         {

  22:             while (sdr.Read())

  23:             {

  24:                 path = sdr[0].ToString();

  25:                 fileType = sdr[1].ToString();

  26:             }

  27:             

  28:         }

  29:         

  30:         objSqlCmd = new SqlCommand("SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()", objSqlCon, objSqlTran);

  31:  

  32:         byte[] objContext = (byte[])objSqlCmd.ExecuteScalar();

  33:  

  34:  

  35:         SqlFileStream objSqlFileStream = new SqlFileStream(path, objContext, FileAccess.Read);

  36:  

  37:         byte[] buffer = new byte[(int)objSqlFileStream.Length];

  38:         objSqlFileStream.Read(buffer, 0, buffer.Length);

  39:         objSqlFileStream.Close();

  40:  

  41:         objSqlTran.Commit();

  42:         Response.AddHeader("Content-disposition", "attachment; filename=" + 
                   Path.GetFileName(path) + fileType);

  43:         // Here you need to manage the download file stuff according to your need

  44:         Response.ContentType = "application/octet-stream";

  45:  

  46:         Response.BinaryWrite(buffer);

  47:  

  48:  

  49:         

  50:     }

  51: }

아래는 위의 CS 코드에 대한 설명을 하고 있습니다.

Well, in the first 8 lines, we have created and opened a connection and then begin the transaction. from line no 10 to 28, we are setting the parameter stuff, executing the procedure and save the output in the two variable called “path” and “fileType”.

In line no 30 to 32, we are executing the the transaction context statement and then save the output the bytes. (Same we have done when we were writing the file)

In line no 35 to 40, we have used the same SqlFileStream and instead of writing, we are reading the file this time(notice line no 38) and save the content of the file to the bytes. Now we have file content in bytes, So we have now commit the transaction in line no 41.

In line no 42 and 44, we are setting the content type and specifying the file name with the extension. That is why, we have also saved file type in the database so that at the time of downloading we can make it available in its original state.

And in line no 46, we are simply writing the binary of the file to the browser so that it can be downloaded.

결론 (Conclusion:)

I have tried my best to explain the integration of FileStream field with ADO.net, and I found this is an easy way to accomplish the task. You can download the VS 2008 solution which contain the complete source code along with procedures and table SQL.

Since, it is a new featured in SQL Server 2008 which is still in CTP by the time I am posting this stuff that is why we can expect some modifications in the method of reading and saving files using FileStream. If somebody face any challenge in the above code. Please feel to contact me.

ADO.NET 에서 FileStream field 의 통합에 대해 자세히 설명할려고 노력하였다고 하며, 해당 작업에 대해 쉽게 처리하는 방법을 발견했고, 소스는 http://cid-cdbfe38dc780f729.skydrive.live.com/self.aspx/.Public/FileStreamSQL2008.zip 에서 다운로드 하시기 바랍니다.

원문 : http://www.aghausman.net/dotnet/saving-and-retrieving-file-using-filestream-sql-server-2008.html

** 자세히 모르는 부분에 대해서는 질문으로 받겠습니다. 요즘 바쁜관계로 일일이 번역해 드리지 못한 점 죄송합니다.

감사합니다. POSTED BY 심재운