locked
memory stream to DB RRS feed

  • Question

  • hi

    I am trying to store a memoryStream to db and again retrive it. every thing works Ok but the problem is when I retrive the file I have extra blanks between each character. for example if I store "Hello" I will retrive it as "H e l l o" and I can not undrestand why.

    I use the folowing methods to store and then retrive as a txt file:

    public void memorystreamToDb()

    {

    int count;

    byte[] byteArray;

    char[] charArray;

    UnicodeEncoding uniEncoding = new UnicodeEncoding();

    // Create the data to write to the stream.

    byte[] secondString = uniEncoding.GetBytes(

    "Hello World");

    using (MemoryStream memStream = new MemoryStream(100))

    {

    // Write the second string to the stream, byte by byte.

    count = 0;

    while (count < secondString.Length)

    {

    memStream.WriteByte(secondString[count++]);

    }

    // Set the position to the beginning of the stream.

    memStream.Seek(0, SeekOrigin.Begin);

    // Read bytes, byte by byte.

    byteArray = new byte[memStream.Length];

    count = 0;

    while (count < memStream.Length)

    {

    byteArray[count++] =

    Convert.ToByte(memStream.ReadByte());

    }

    #region add to db

    CommonMethods_class k = new CommonMethods_class();

    using (SqlConnection connection = new SqlConnection(k.Get_connection_string()))

    {

    SqlCommand command = new SqlCommand("INSERT INTO temp (examplefile) Values(@File)", connection);

    command.Parameters.Add("@File", SqlDbType.Binary, byteArray.Length).Value = byteArray;

    connection.Open();

    command.ExecuteNonQuery();

    }

    #endregion

    }

     

    public void retrive_file_partly()

    {

    CommonMethods_class c = new CommonMethods_class();

    SqlConnection connection = new SqlConnection(c.Get_connection_string());

    SqlCommand command = new SqlCommand("Select * from temp", connection);

    // Writes the BLOB to a file (*.bmp).

    FileStream stream;

    // Streams the BLOB to the FileStream object.

    BinaryWriter writer;

    // Size of the BLOB buffer.

    int bufferSize = 50;

    // The BLOB byte[] buffer to be filled by GetBytes.

    byte[] outByte = new byte[bufferSize];

    // The bytes returned from GetBytes.

    long retval;

    // The starting position in the BLOB output.

    long startIndex = 0;

    // Open the connection and read data into the DataReader.

    connection.Open();

    SqlDataReader reader = command.ExecuteReader(CommandBehavior.SequentialAccess);

    while (reader.Read())

    {

    // Create a file to hold the output.

    stream = new FileStream(

    "C:\\sn2.txt", FileMode.OpenOrCreate, FileAccess.Write);

    writer = new BinaryWriter(stream);

    // Reset the starting byte for the new BLOB.

    startIndex = 0;

    // Read bytes into outByte[] and retain the number of bytes returned.

    retval = reader.GetBytes(0, startIndex, outByte, 0, bufferSize);

    // Continue while there are bytes beyond the size of the buffer.

    while (retval == bufferSize)

    {

    writer.Write(outByte);

    writer.Flush();

    // Reposition start index to end of last buffer and fill buffer.

    startIndex += bufferSize;

    retval = reader.GetBytes(0, startIndex, outByte, 0, bufferSize);

    }

    // Write the remaining buffer.

    if (retval != 0)

    writer.Write(outByte, 0, (int)retval - 1);

    writer.Flush();

    // Close the output file.

    writer.Close();

    stream.Close();

    }

    // Close the reader and the connection.

    reader.Close();

    connection.Close();

    }

    Wednesday, December 20, 2006 8:47 AM

Answers

  • You have an encoding problem, fixing that problem and tidying up your memoryStreamtoDB Method:

    public void memorystreamToDb()
    {
    // Create the data to write to the stream.
    byte[] secondString = System.Text.Encoding.UTF8.GetBytes("Hello World");

    using (MemoryStream memStream = new MemoryStream(secondString))
    {
    byte[] bytes = new byte[memStream.Length];
    memStream.Read(bytes,0,(int)memStream.Length);

    using (SqlConnection connection = new SqlConnection(sConnection))
    {
    SqlCommand command = new SqlCommand("INSERT INTO temp Values(@File)", connection);
    command.Parameters.Add("@File", SqlDbType.Binary, (int)memStream.Length).Value = bytes;
    connection.Open();
    command.ExecuteNonQuery();
    }
    }
    }


    and in your output method, you are not reading the last byte of the file:

    writer.Write(outByte, 0, (int)retval - 1);

    should better read

    writer.Write(outByte, 0, (int)retval );

    Regards

    Wednesday, December 20, 2006 10:22 AM

All replies

  • You have an encoding problem, fixing that problem and tidying up your memoryStreamtoDB Method:

    public void memorystreamToDb()
    {
    // Create the data to write to the stream.
    byte[] secondString = System.Text.Encoding.UTF8.GetBytes("Hello World");

    using (MemoryStream memStream = new MemoryStream(secondString))
    {
    byte[] bytes = new byte[memStream.Length];
    memStream.Read(bytes,0,(int)memStream.Length);

    using (SqlConnection connection = new SqlConnection(sConnection))
    {
    SqlCommand command = new SqlCommand("INSERT INTO temp Values(@File)", connection);
    command.Parameters.Add("@File", SqlDbType.Binary, (int)memStream.Length).Value = bytes;
    connection.Open();
    command.ExecuteNonQuery();
    }
    }
    }


    and in your output method, you are not reading the last byte of the file:

    writer.Write(outByte, 0, (int)retval - 1);

    should better read

    writer.Write(outByte, 0, (int)retval );

    Regards

    Wednesday, December 20, 2006 10:22 AM
  • yes, now it works nice

    thank you very much

    regards

    Wednesday, December 20, 2006 12:34 PM
  • No Problem,

    There is also a solution to your 'store a file in sql image' post, for some reason it appears half way down.

    Regards

    Wednesday, December 20, 2006 2:31 PM