Bilder in einer MS SQL Tabelle ablegen

Question

Hallo Forum,

ich versuche gerade ein Bild als FileStream in einer MS SQL Tabelle zu speichern. Die neue Funktion im MS SQL Server 2008 bei der man Files "ausserhalb" der Tabelle speichert, möchte / kann ich dabei nicht verwenden.

Was ich mache ist ein Bild oder Dokument in einen FileStream "zerlegen". Diesen möchte ich dann in ein Tabellenfeld speichern. Von dort wieder lesen und auch überschreiben können. Folgendes habe ich versucht.

Einfach mal ein File erstellen.

// File erzeugen und abspeichern
System.IO.FileStream writeFile; 
byte[] byteData = null; 
byteData = Encoding.ASCII.GetBytes("Test auf Funktion"); 
writeFile = new FileStream("c:\\funktion_test.txt", FileMode.Create); 
writeFile.Write(byteData, 0, byteData.Length);
writeFile.Close();
// File in DB schreiben
SqlConnection myConnection = new SqlConnection("Server=.\\SQLEXPRESS;Initial Catalog=xx;user id=yy;pwd=yyy");
myConnection.Open();
SqlCommand myCommand = new SqlCommand("INSERT INTO Bilder (dokument) Values "+ ?????? byteData ????? , myConnection); 
myCommand.ExecuteNonQuery();
myConnection.Close();

Wie muss mein Aufruf  "?????? byteData ?????"  sein, damit die Daten in das Feld geschrieben werden ? Geht das so überhaupt ?

Gruß Roland

Answer 1

Hallo Roland,

prinzipiell würde das so gehen, wenn Du einen SqlParameter mit SqlDbType.Image (für SQL Server 2000)
oder auch als SqlDbType.Varbinary (mit Länge -1) für SQL Server 2005 und höher erstellst.
Inline würde es bedeuten, dass man eine Hexadezimale Zeichenkette erzeugen muß,
was weder besonders performant noch anderweitig zu empfehlen ist.
Bei größeren Dateien werden dabei allerdings viele Ressourcen verbraucht (Client- wie Serverseitig)
und empfiehlt sich ein Vorgehen wie in:
Sparsames Verwenden von Ressourcen beim Schreiben von BLOB-Werten in SQL Server

Leider ist der Code bis heute nicht auf die neueren SQL Server Versionen angepaßt worden
und zeigt nicht die Verwendung von VARBINARY(MAX) und Co.
Deswegen eine Beispielimplementation, die die verschiedenen Varianten zeigt.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.IO;

namespace ElmarBoye.Samples.Data
{
  public class SqlServerBlob
  {
    private const int BUFFER_SIZE = 32768;
    private string _connectionString;

    public SqlServerBlob(string connectionString)
    {
      this._connectionString = connectionString;
    }

    /// <summary>Test auf die SQL Server Version.</summary>
    /// <param name="versionString">SqlConnection.ServerVersion.</param>
    private static bool SupportVarBinayMax(string versionString)
    {
      var version = new Version(versionString);
      return (version.Major >= 9);
    }

    #region Write
    /// <summary>
    /// Schreibt eine Datei in einem Block. Für größere Dateien nicht zu empfehlen.
    /// </summary>
    /// <param name="fileName">Der Name der Datei.</param>
    public int WriteAll(string fileName)
    {
      using (var connection = new SqlConnection(this._connectionString))
      {
        connection.Open();

        var dataBuffer = File.ReadAllBytes(fileName);

        var insertCommand = new SqlCommand(
          "INSERT INTO dbo.Blobs (Dateiname, Daten) VALUES (@Dateiname, @Daten); "
          + "SELECT Id FROM dbo.Blobs WHERE ID = SCOPE_IDENTITY();",
          connection);
        insertCommand.Parameters.Add("@Dateiname", SqlDbType.NVarChar, 260).Value = fileName;
        insertCommand.Parameters.Add("@Daten", SqlDbType.Image, dataBuffer.Length).Value = dataBuffer;

        return (int)insertCommand.ExecuteScalar();
      }
    }

    /// <summary>
    /// Schreibt eine Datei in mehreren Blöcken.
    /// </summary>
    /// <param name="fileName">Der Name der Datei.</param>
    /// <remarks>
    /// Nur für SQL Server 2005 und höher und varbinary(max)
    /// Für speicherschonende SQL Server 2000 Version
    /// siehe: http://msdn.microsoft.com/de-de/library/3517w44b%28VS.80%29.aspx
    /// </remarks>
    /// <returns>Die Id.</returns>
    public int WriteChunk(string fileName)
    {
      int dataId = -1;

      using (var connection = new SqlConnection(this._connectionString))
      {
        connection.Open();

        if (!SupportVarBinayMax(connection.ServerVersion))
          throw new NotSupportedException("Von dieser SQL Server Version nicht unterstützt");

        using (var inputStream = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.Read, BUFFER_SIZE))
        {
          var dataBuffer = new byte[BUFFER_SIZE];

          long dataLength = inputStream.Read(dataBuffer, 0, dataBuffer.Length);
          if (dataLength <= 0)
            throw new IOException(string.Format("Die Eingabedatei '{0}' ist leer.", fileName));

          // Anlage via Insert
          if (dataLength < BUFFER_SIZE)
            Array.Resize(ref dataBuffer, (int)dataLength);

          using (var insertCommand = new SqlCommand(
            "INSERT INTO dbo.Blobs (Dateiname, Daten) OUTPUT inserted.Id VALUES (@Dateiname, @Daten);",
            connection))
          {
            insertCommand.Parameters.Add("@Dateiname", SqlDbType.NVarChar, 260).Value = fileName;
            insertCommand.Parameters.Add("@Daten", SqlDbType.VarBinary, -1).Value = dataBuffer;
            dataId = (int)insertCommand.ExecuteScalar();
          }

          // Weitere Blöcke via UPDATE
          using (var updateCommand = new SqlCommand(
            "UPDATE dbo.Blobs SET Daten.Write(@Daten, NULL, NULL) WHERE Id = @ID;",
            connection))
          {
            updateCommand.Parameters.Add("@ID", SqlDbType.Int).Value = dataId;
            updateCommand.Parameters.Add("@Daten", SqlDbType.VarBinary, BUFFER_SIZE);

            while ((dataLength = inputStream.Read(dataBuffer, 0, dataBuffer.Length)) > 0)
            {
              if (dataLength < BUFFER_SIZE)
                Array.Resize(ref dataBuffer, (int)dataLength);

              updateCommand.Parameters["@Daten"].Value = dataBuffer;
              updateCommand.ExecuteNonQuery();
            }
          }
        }
      }
      return dataId;
    }

    /// <summary>
    /// Schreibt eine Datei in Abschnitten für SQL Server 2000/IMAGE.
    /// </summary>
    /// <param name="fileName">Der Name der Datei.</param>
    /// <remarks>
    /// Für IMAGE Spalten (bei SQL Server 2000 oder aus Kompatiblität)
    /// siehe auch: http://msdn.microsoft.com/de-de/library/3517w44b%28VS.80%29.aspx
    /// </remarks>
    /// <returns>Die Id.</returns>
    public int WriteImageChunk(string fileName)
    {
      int dataId = -1;

      using (var connection = new SqlConnection(this._connectionString))
      {
        connection.Open();

        using (var inputStream = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.Read, BUFFER_SIZE))
        {
          byte[] dataPtr = null;
          var dataBuffer = new byte[BUFFER_SIZE];

          long dataLength = inputStream.Read(dataBuffer, 0, dataBuffer.Length);
          if (dataLength <= 0)
            throw new IOException(string.Format("Die Eingabedatei '{0}' ist leer.", fileName));

          using (var insertCommand = new SqlCommand(
            // Anlage mit 0x00 um einen TEXTPTR zu erhalten
            "INSERT INTO dbo.Blobs (Dateiname, Daten) VALUES (@Dateiname, 0x00);\r\n"
            + "SELECT @Id = Id, @DatenPtr = TEXTPTR(Daten) FROM dbo.Blobs WHERE Id = SCOPE_IDENTITY();",
            connection))
          {
            insertCommand.Parameters.Add("@Dateiname", SqlDbType.NVarChar, 260).Value = fileName;

            var idParameter = insertCommand.Parameters.Add("@Id", SqlDbType.Int);
            idParameter.Direction = ParameterDirection.Output;

            var dataPtrParameter = insertCommand.Parameters.Add("@DatenPtr", SqlDbType.Binary, 16);
            dataPtrParameter.Direction = ParameterDirection.Output;

            insertCommand.ExecuteNonQuery();

            dataId = (int)idParameter.Value;
            dataPtr = (byte[])dataPtrParameter.Value;
          }

          // Weitere Blöcke via UPDATE
          using (var updateCommand = new SqlCommand(
            "UPDATETEXT dbo.Blobs.Daten @DataPtr @Offset 0 @Data",
            connection))
          {
            updateCommand.Parameters.Add("@DataPtr", SqlDbType.Binary, 16).Value = dataPtr;
            var offsetParameter = updateCommand.Parameters.Add("@Offset", SqlDbType.Int);
            var dataParameter = updateCommand.Parameters.Add("@Data", SqlDbType.Image, BUFFER_SIZE);

            int dataOffset = 0;
            do
            {
              if (dataLength < BUFFER_SIZE)
                Array.Resize(ref dataBuffer, (int)dataLength);

              offsetParameter.Value = dataOffset;
              dataParameter.Value = dataBuffer;
              updateCommand.ExecuteNonQuery();

              dataOffset += (int)dataLength;
            }
            while ((dataLength = inputStream.Read(dataBuffer, 0, dataBuffer.Length)) > 0);
          }
        }
      }
      return dataId;
    }
    #endregion Write

    #region Read
    /// <summary>
    /// Liest die Daten aus einer Varbinary oder Image Spalte in die angegebene Datei.
    /// </summary>
    /// <param name="dataId">Die Id.</param>
    /// <param name="fileName">Der Ausgabedateiname.</param>
    /// <returns>Die Anzahl der verarbeiteten Bytes.</returns>
    public int ReadAll(int dataId, string fileName)
    {
      long dataOffset = 0;
      using (var connection = new SqlConnection(this._connectionString))
      {
        connection.Open();

        var selectCommand = new SqlCommand("SELECT Daten FROM dbo.Blobs WHERE Id = @ID", connection);
        selectCommand.Parameters.Add("@ID", SqlDbType.Int).Value = dataId;

        using (var reader = selectCommand.ExecuteReader(CommandBehavior.SequentialAccess))
        {
          if (reader.Read())
          {
            using (var outputStream = new FileStream(fileName, FileMode.Create, FileAccess.Write, FileShare.None, BUFFER_SIZE))
            {
              var dataBuffer = new byte[BUFFER_SIZE];
              long dataLength;

              while ((dataLength = reader.GetBytes(0, dataOffset, dataBuffer, 0, BUFFER_SIZE)) > 0)
              {
                outputStream.Write(dataBuffer, 0, (int)dataLength);
                dataOffset += dataLength;
              }
            }
          }
        }
      }
      return (int)dataOffset;
    }
    #endregion Read

    #region CREATE / DROP TABLE
    /// <summary>
    /// Legt die Beispiel Tabelle an.
    /// </summary>
    public void CreateTable()
    {
      using (var connection = new SqlConnection(this._connectionString))
      {
        connection.Open();

        // Bei SQL Serer 2000 image, sonst varbinary(max)
        string blobDataType = "image";
        if (SupportVarBinayMax(connection.ServerVersion))
          blobDataType = "varbinary(max)";

        var createTableCommand = new SqlCommand(
        "IF OBJECT_ID(N'dbo.Blobs', 'U') IS NULL\r\n"
         + "CREATE TABLE dbo.Blobs (\r\n"
         + "Id int IDENTITY(1,1) NOT NULL,\r\n"
         + "Dateiname nvarchar(260) NULL,\r\n"
         + "Daten " + blobDataType + " NULL,\r\n"
         + "CONSTRAINT PK_Blobs PRIMARY KEY CLUSTERED (Id) )",
        connection);

        createTableCommand.ExecuteNonQuery();
      }
    }

    /// <summary>
    /// Löscht die Beispiel Tabelle.
    /// </summary>
    public void DropTable()
    {
      using (var connection = new SqlConnection(this._connectionString))
      {
        connection.Open();

        var dropTableCommand = new SqlCommand(
        "IF OBJECT_ID(N'dbo.Blobs', 'U') IS NOT NULL\r\n"
         + "DROP TABLE dbo.Blobs;",
        connection);

        dropTableCommand.ExecuteNonQuery();
      }
    }
    #endregion CREATE / DROP TABLE

    /// <summary>Ein kleiner Test.</summary>
    internal static void TestSqlBlob()
    {
      const string PATH = @"E:\MEDIA\Bilder";
      var sqlBlob = new SqlServerBlob(Properties.Settings.Default.NorthwindConnectionString);

      sqlBlob.CreateTable();

      var fileIds = new List<int>();

      var watch = Stopwatch.StartNew();
      foreach (var filename in Directory.GetFiles(PATH, "*.jpg"))
      {
        // int id = sqlBlob.WriteAll(filename);  // alles auf einmal
        int id = sqlBlob.WriteChunk(filename);
        //int id = sqlBlob.WriteImageChunk(filename); // IMAGE SQL Server 2000
        fileIds.Add(id);
        Console.WriteLine("Write: {0} = {1}", id, filename);
      }
      watch.Stop();
      Console.WriteLine("Write {0} ms", watch.ElapsedMilliseconds);


      string outputFileName = Path.GetTempFileName();
      watch = Stopwatch.StartNew();
      foreach (var id in fileIds)
      {
        long length = sqlBlob.ReadAll(id, outputFileName);

        Debug.Assert(length == new FileInfo(outputFileName).Length, 
          String.Format("Abweichende Länge bei {0}", id));
      }
      Console.WriteLine("Read {0} ms", watch.ElapsedMilliseconds);

      File.Delete(outputFileName);

      sqlBlob.DropTable();
    }
  }
}

Daraus kannst Du Dir den Abschnitt raussuchen, der für Dich am besten passt.

Gruß Elmar

 

Answer 2

Hier ist ein vollständiges Beispiel:

[Read / Write BLOBs from / to SQL Server using C# .NET DataReader]
http://www.akadia.com/services/dotnet_read_write_blob.html

ergänzend .. beim SQL Server 2008 R2 gibt es ggf. verbesserte Möglichkeiten:

[Webcast: SQL Server 2008 R2 Filestream - BLOBs in der Datenbank speichern | MSDN Online]
http://www.microsoft.com/germany/msdn/webcasts/library.aspx?id=1032455029

[Übersicht über FILESTREAM]
http://msdn.microsoft.com/de-de/library/bb933993.aspx

 

---

ciao Frank

Answer 3

Hallo Elmar, Hallo Frank,

Danke für die Info´s . Da hab ich einiges zum Testen

Gruß Roland

 

Answer 4

Hallo Elmar, Hallo Frank,

das speichern hab ich anscheinend hinbekommen ( sehe ich an der Größe der DB ), aber was ich nicht schaffe ist

der "download" aus der DB und das Bild wieder als File zu spechern. Wie kann ich das Feld in der DB ansprechen irgendwie hab ich da ein Problem.

In der Tabelle gibt es 2 Bilder :  Bild 1  das Original    Bild 2  eine kleine Vorschau

Gruß Roland

Answer 5

Hallo Roland,

So könnte man ein Bild aus der Datenbank auslesen, funktioniert genauso auch aus einer CE DB:

public Image getImageFromDB(int id) {
  Image img;
  byte[] imgData = null;

  using (SqlConnection con = new SqlConnection(conString)) {
    con.Open();
    using (SqlCommand com = new SqlCommand
      ("SELECT image FROM cp_image WHERE id = '" + id.ToString() + "'", con)) {

      using (SqlDataReader dr = com.ExecuteReader()) {
        while (dr.Read()) {
          imgData = (byte[])dr["image"];
        }
      }
    }
  }

  if (imgData != null) {
    //Es wurde ein Bild geladen
    MemoryStream ms = new MemoryStream(imgData);
    img = Image.FromStream(ms);
    return img;
  }
  return null;
}

 

Zum schluss wird der ausgelesene BLOB noch in ein Image Objekt umgewandelt, das du auch bequem in eine PictureBox einfügen kannst.

Das Bild speichern kannst du dann ganz normal über Syste.IO.File.WriteAllBytes. Übergib hier einfach das Bytearray, dass man mit der oberen Funktion ausließt.

 

---

MfG, Sebastian Gross

Answer 6

Hallo Roland,

die im ersten Beispiel gezeigte Methode ReadAll tut das geforderte.
Wenn Du mehrere Spalten hast, müsstest Du Methode erweitern.

Quick 'n Dirty (ohne Fehlerprüfung) mit Übergabe des jeweiligen Spaltennamens (Bild und Vorschau):

  public int ReadVorschau(int dataId, string fileName)
  {
    // Verwendet den Spaltennamen Vorschau
    return ReadAll(int dataId, string fileName, "Vorschau");
  }

  public int ReadBild(int dataId, string fileName)
  {
    // Verwendet den Spaltennamen Bild
    return ReadAll(int dataId, string fileName, "Bild");
  }

  private int ReadAll(int dataId, string fileName, string spalte)
  {
   long dataOffset = 0;
   using (var connection = new SqlConnection(this._connectionString))
   {
    connection.Open();

    var selectCommand = new SqlCommand("SELECT [" + Spalte + "] FROM dbo.Blobs WHERE Id = @ID", connection);
    selectCommand.Parameters.Add("@ID", SqlDbType.Int).Value = dataId;

    using (var reader = selectCommand.ExecuteReader(CommandBehavior.SequentialAccess))
    {
     if (reader.Read())
     {
      using (var outputStream = new FileStream(fileName, FileMode.Create, FileAccess.Write, FileShare.None, BUFFER_SIZE))
      {
       var dataBuffer = new byte[BUFFER_SIZE];
       long dataLength;

       while ((dataLength = reader.GetBytes(0, dataOffset, dataBuffer, 0, BUFFER_SIZE)) > 0)
       {
        outputStream.Write(dataBuffer, 0, (int)dataLength);
        dataOffset += dataLength;
       }
      }
     }
    }
   }
   return (int)dataOffset;
  }

Gruß Elmar