歡迎~ 程式設計 學生 作業 專題製作 程式外包 家教...

如果要使用 ADO.NET 存取 SQL Server 上的 image 欄位，其實只要使用 byte[] 陣列即可:

// 將檔案儲存到資料庫 image 欄位中
string filename = "c:\demo.jpg";
FileStream fs = new FileStream(filename, FileMode.Open);
byte[] buffer = new byte[fs.Length]; // 用來儲存檔案的 byte 陣列，檔案有多大，陣列就有多大
fs.Read(buffer, 0, buffer.Length);
fs.Close();

SqlConnection conn = new SqlConnection();
conn.ConnectionString = "";
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "INSERT Table1(Body) VALUES(@Body)";
cmd.Parameters.Add("@Body", SqlDbType.Image);
cmd.Parameters["@Body"].Value = buffer;

conn.Open();
cmd.ExecuteNonQuery();
conn.Close();

 

// 從資料表 image 欄位取得資料存檔
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "";
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "SELECT Body FROM Table1 WHERE FileID=1";

conn.Open();
object obj = cmd.ExecuteScalar();
conn.Close();

if (obj != null && obj != DBNull.Value)
{
    byte[] buffer = (byte[])obj; // 用來儲存檔案的 byte 陣列，檔案有多大，陣列就有多大
    FileStream fs = new FileStream("c:\new.jpg");
    fs.Write(buffer, 0, buffer.Length);
    fs.Close();
}

可是大家有沒有發現一個問題，那就是，如果檔案非常大，例如 1.5GB，那麼 buffer 陣列就需要 1.5GB ?! 如果是 Web 應用程式，不用幾個使用者連線上來，伺服器大概就掛了吧 ^_^

所以最好將檔案分批次寫入或讀取，例如一次存取 1KB，這樣不管檔案有多大，記憶體的使用量都是固定的。

好了，如果要做到這個效果，我們可以使用 READTEXT 和 WRITETEXT 這兩個 T-SQL 指令，

-- 讀取 Employees 資料表中 Photo 欄位的第 16 個 byte 開始，共 1024 個bytes
DECLARE @ptr varbinary(16)
SELECT @ptr = TEXTPTR(Photo) FROM Employees WHERE EmployeeID = 1
READTEXT Employees.Photo @ptr 15 1024

-- 刪除 Employees 資料表中 Photo 欄位的第 16 個 byte 開始，共 50 個bytes
-- 並且在同一個位置插入二進位資料 0x112233445566778899AABBCCDDEEFF (16 進位字串表示)
DECLARE @ptr varbinary(16)
SELECT @ptr = TEXTPTR(Photo) FROM Employees WHERE EmployeeID = 1
WRITETEXT Employees.Photo @ptr 15 50 0x112233445566778899AABBCCDDEEFF

聖哥寫了兩個副程式，可以直接用來存取 image 欄位:

// 將 byte 陣列轉換為 16 進位字串，例如: 0x112233445566778899AABBCC
public string ByteArrayToHexString(byte[] bytearray, int length)
{
    StringBuilder sb = new StringBuilder("0x");
    for (int i = 0; i < length; i++)
    {
        sb.Append(string.Format("{0:x2}", bytearray[i]));
    }
    return sb.ToString();
}

// 將檔案寫入 image 欄位
public void SaveFileToSqlImage(SqlConnection connection, 
                            string table_name, string column_name,
                            string filename, string where_condition, int buffer_size)
{
    SqlCommand cmdBody = new SqlCommand();
    cmdBody.Connection = connection;
    string sql = "DECLARE @ptrval varbinary(16) " +
        " SELECT @ptrval = TEXTPTR(" + column_name +
        ") FROM " + table_name + " WHERE " + where_condition +
        " UPDATETEXT " + table_name + "." + column_name + " @ptrval {0} 0 {1}";


    FileStream fs = new FileStream(filename, FileMode.Open);
    byte[] buffer = new byte[buffer_size];
    int count = fs.Read(buffer, 0, buffer.Length);
    int loc = 0;
    while (count > 0)
    {
        cmdBody.CommandText = string.Format(sql, loc,
                                                                ByteArrayToHexString(buffer, count));
        connection.Open();
        cmdBody.ExecuteNonQuery();
        connection.Close();
        loc += count;
        count = fs.Read(buffer, 0, buffer.Length);
    }
    fs.Close();
}

// 從 image 欄位讀取資料並存檔
public void SaveFileFromSqlImage(SqlConnection connection,
                        string table_name, string column_name,
                        string filename, string where_condition, int buffer_size)
{
    SqlCommand cmdGetSize = new SqlCommand(
        "SELECT DATALENGTH(" + column_name +
        ") FROM " + table_name + " WHERE " + where_condition, connection);


    connection.Open();
    object obj = cmdGetSize.ExecuteScalar();
    connection.Close();
    if (obj != null && obj != DBNull.Value)
    {
        int filesize = (int)obj;
        string sql = "DECLARE @ptrval varbinary(16) " + 
            "SELECT @ptrval = TEXTPTR(" + column_name +
            ") FROM " + table_name + " WHERE " + where_condition +
            " READTEXT " + table_name + "." + column_name + " @ptrval {0} {1}";


        SqlCommand cmdBody = new SqlCommand();
        cmdBody.Connection = connection;
        int loc = 0;
        FileStream fs = new FileStream(filename, FileMode.Create);
        while (loc < filesize)
        {
            int count = (filesize - loc >= buffer_size) ? buffer_size : filesize - loc;
            cmdBody.CommandText = string.Format(sql, loc, count);
            connection.Open();
            byte[] buffer = (byte[])cmdBody.ExecuteScalar();
            connection.Close();
            fs.Write(buffer, 0, buffer.Length);
            loc += count;
        }
        fs.Close();
    }
}

使用方法:

SaveFileToSqlImage(conn, "MyTable", "MyImageColumn", "c:\filename.ext", "FileID=1", 1024);
SaveFileFromSqlImage(conn, "MyTable", "MyImageColumn", "c:\filename.ext", "FileID=1", 1024);

ps. 附上 VB.NET 版本:

Function ByteArrayToHexString(ByVal bytearray() As Byte, _
                                                  ByVal Length As Integer) As String
    Dim sb As New StringBuilder("0x")
    For I As Integer = 0 To Length - 1
        sb.Append(String.Format("{0:x2}", bytearray(I)))
    Next
    Return sb.ToString()
End Function

' 將檔案寫入 image 欄位
Sub SaveFileToSqlImage(ByVal Connection As SqlConnection, _
            ByVal TableName As String, _
            ByVal ColumnName As String, ByVal FileName As String, _
            ByVal WhereCondition As String, _
            ByVal BufferSize As Integer)

    Dim cmdBody As New SqlCommand()
    cmdBody.Connection = Connection

    Dim sql As String = "DECLARE @ptrval varbinary(16) " +
        "SELECT @ptrval = TEXTPTR(" + _
        ColumnName + ") FROM " + TableName + " WHERE " + _
        WhereCondition + " UPDATETEXT " + TableName + "." + _
        ColumnName + " @ptrval {0} 0 {1}"

    Dim fs As New FileStream(FileName, FileMode.Open)
    Dim buffer(BufferSize - 1) As Byte

    Dim count As Integer = fs.Read(buffer, 0, buffer.Length)
    Dim loc As Integer = 0
    Do While count > 0
        cmdBody.CommandText = String.Format(sql, loc, _
                                                    ByteArrayToHexString(buffer, count))
        Connection.Open()
        cmdBody.ExecuteNonQuery()
        Connection.Close()

        loc += count
        count = fs.Read(buffer, 0, buffer.Length)
    Loop
    fs.Close()
End Sub

' 從 image 欄位讀取資料並存檔
Sub SaveFileFromSqlImage(ByVal Connection As SqlConnection, _
            ByVal TableName As String, _
            ByVal ColumnName As String, ByVal FileName As String, _
            ByVal WhereCondition As String, _
            ByVal BufferSize As Integer)

    Dim cmdGetSize As New SqlCommand("SELECT DATALENGTH(" + _
            ColumnName + ") FROM " + _
            TableName + " WHERE " + WhereCondition, Connection)

    Connection.Open()
    Dim obj As Object = cmdGetSize.ExecuteScalar()
    Connection.Close()

    If obj IsNot Nothing AndAlso obj IsNot DBNull.Value Then
        Dim filesize As Integer = obj

        Dim sql As String = "DECLARE @ptrval varbinary(16) " +
            "SELECT @ptrval = TEXTPTR(" + _
            ColumnName + ") FROM " + TableName + " WHERE " + _
            WhereCondition + " READTEXT " + TableName + "." + _
            ColumnName + " @ptrval {0} {1}"


        Dim cmdBody As New SqlCommand()
        cmdBody.Connection = Connection

        Dim loc As Integer = 0
        Dim fs As New FileStream(FileName, FileMode.Create)

        Do While loc < filesize
            Dim count As Integer = _
                    IIf(filesize - loc >= BufferSize, BufferSize, filesize - loc)
            cmdBody.CommandText = String.Format(sql, loc, count)

            Connection.Open()
            Dim buffer() As Byte = cmdBody.ExecuteScalar()
            Connection.Close()

            fs.Write(buffer, 0, buffer.Length)

            loc += count
        Loop

        fs.Close()
    End If
End Sub

資料來源:http://jimycao.blogspot.com/2008/10/adonet-sql-server-image_30.html