none
Managing music collection Using Visual Basic Express and SQL Server Express

    Domanda

  • Hello from Russia.

    I'm glad i found this article on MSDN (http://msdn.microsoft.com/ru-ru/library/ms345151(en-us,SQL.90).aspx) - it's really helpful for my course-work in University. But this article lacks of Sample code of VB-application and database example. It was there, though, and it was in Spanish article (as it said here: http://social.msdn.microsoft.com/forums/en-US/Vsexpressvb/thread/a43b05af-86b3-4c0d-8084-4900a702ec31/)

    But now there is no spanish article and no sample code in the main article. I really need it and searched for it in The Internet. But there are no results for my searches. Can anybody help me finding theese samples, or maybe someone has got them?

    Looking forward good news 'bout my problem...

     

    Cincerely, 

    Damir.


    domenica 15 maggio 2011 14:08

Tutte le risposte

  • Create a column with "varbinary(max)" to store the songs and use the following code to save in the database

    private void SaveBlobToDatabase()

    {

    // This call lets you select the

    // binary file to save As a BLOB

    // in the database.

    // The BLOB holds the byte array to save.

    byte[] BLOB;

    // The FileStream is the stream of bytes

    // that represent the binary file.

    System.IO.FileStream FileStream = new System.IO.FileStream(

    CompleteFilePath, System.IO.FileMode.Open, System.IO.FileAccess.Read);

    // The reader reads the binary data from the FileStream.

    System.IO.BinaryReader reader =

    new System.IO.BinaryReader(FileStream);

    // The BLOB is assigned the bytes from the reader.

    // The file length is passed to the ReadBytes method

    // telling it how many bytes to read.

    System.IO.FileInfo file = new FileInfo(CompleteFilePath);

    BLOB = reader.ReadBytes((int)(file.Length));

    FileStream.Close();

    reader.Close();

    // Create a command object to save

    // the BLOB value.

    SqlCommand SaveDocCommand = new SqlCommand();

    SaveDocCommand.Connection = NorthwindConnection;

    SaveDocCommand.CommandText = "INSERT INTO DocumentStorage" +

    "(FileName, DocumentFile)" +

    "VALUES (@FileName, @DocumentFile)";

    // Create parameters to store the filename and BLOB data.

    SqlParameter FileNameParameter = new SqlParameter("@FileName",

    SqlDbType.NChar);

    SqlParameter DocumentFileParameter = new SqlParameter(

    "@DocumentFile", SqlDbType.Binary);

    SaveDocCommand.Parameters.Add(FileNameParameter);

    SaveDocCommand.Parameters.Add(DocumentFileParameter);

    // Parse the filename out of the complete path

    // and assign it to the parameter.

    FileNameParameter.Value = CompleteFilePath.Substring(

    CompleteFilePath.LastIndexOf("\\")+ 1);

    // Set the DocumentFile parameter to the BLOB Value.

    DocumentFileParameter.Value = BLOB;

    // Execute the command and save the BLOB to the database.

    try

    {

    SaveDocCommand.Connection.Open();

    SaveDocCommand.ExecuteNonQuery();

    MessageBox.Show(FileNameParameter.Value.ToString() + " saved to

    database.",

    "BLOB Saved!", MessageBoxButtons.OK, MessageBoxIcon.Information);

    }

    catch (Exception ex)

    {

    MessageBox.Show(ex.Message, "Save Failed", MessageBoxButtons.OK,

    MessageBoxIcon.Error);

    }

    finally

    {

    SaveDocCommand.Connection.Close();

    }

    }

    Regards,

    Narendran Ponpandiyan

    martedì 17 maggio 2011 07:12
  • for fetching data from database

    private void FetchBlobFromDatabase()

    {

    // Verify there is a BLOB selected to retrieve.

    if (BlobList.Text == "")

    {

    MessageBox.Show("Select a BLOB to fetch from the ComboBox");

    return;

    }

    // Get the path to save the BLOB to.

    GetSavePath();

    // Create the Command object to fetch the selected BLOB.

    SqlCommand GetBlobCommand = new SqlCommand(

    "SELECT FileName, DocumentFile " +

    "FROM DocumentStorage " + "WHERE FileName = @DocName",

    NorthwindConnection);

    GetBlobCommand.Parameters.Add(

    "@DocName", SqlDbType.NVarChar).Value = BlobList.Text;

    // Current index to write the bytes to.

    long CurrentIndex = 0;

    // number of bytes to store in the BLOB.

    int BufferSize = 100;

    // Actual number of bytes returned when calling GetBytes.

    long BytesReturned ;

    // The Byte array used to hold the buffer.

    byte[] Blob = new byte[BufferSize];

    GetBlobCommand.Connection.Open();

    SqlDataReader reader =

    GetBlobCommand.ExecuteReader(CommandBehavior.SequentialAccess);

    while(reader.Read())

    {

    // Create or open the selected file.

    System.IO.FileStream FileStream = new System.IO.FileStream(SavePath + "\\"

    reader["FileName"].ToString(), System.IO.FileMode.OpenOrCreate,

    System.IO.FileAccess.Write);

    // Set the writer to write the BLOB to the file.

    System.IO.BinaryWriter writer = new System.IO.BinaryWriter(FileStream);

    // Reset the index to the beginning of the file.

    CurrentIndex = 0;

    // Set the BytesReturned to the actual number

    // of bytes returned by the GetBytes call.

    BytesReturned = reader.GetBytes(1, CurrentIndex, Blob, 0, BufferSize);

    // If the BytesReturned fills the buffer keep appending to the file.

    while (BytesReturned == BufferSize)

    {

    writer.Write(Blob);

    writer.Flush();

    CurrentIndex += BufferSize;

    BytesReturned = reader.GetBytes(1, CurrentIndex, Blob, 0, BufferSize);

    }

    // When the BytesReturned no longer fills the buffer, write the remaining

    bytes.

    writer.Write(Blob, 0, (int)(BytesReturned));

    writer.Flush();

    writer.Close();

    FileStream.Close();

    }

    reader.Close();

    GetBlobCommand.Connection.Close();

    }

    Regards,

    Narendran Ponpandiyan

    martedì 17 maggio 2011 07:40