Monday, December 29, 2008

How to insert large file into MSSQL field

I was to get rid of a agreement storing application. I'd needed to save Agreement file as attachment to agreement storing table. So i added a field c_file, type of Varbinary.
And when executed it this error was occurred.
System.Data.SqlClient.SQLException: String or binary date would be truncated.
The statement has been terminated.

I knew that this caused of 8K maximum size of Varbinary type. Then i changed the type to Image, it was OK. Following piece of code, i used.



private void simpleButton1_Click(object sender, EventArgs e)
{
CdsTraining.contractDataTable dtContract = new CdsTraining.contractDataTable();
CdsTrainingTableAdapters.contractTableAdapter taContract = new training.CdsTrainingTableAdapters.contractTableAdapter();
byte[] contract= ReadFully(btnedtAttachment.Text, 0);
try
{
taContract.Insert(Convert.ToInt32(cbbEmployee.EditValue), Convert.ToInt32(cbbTraining.EditValue), dateStart.DateTime, dateEnd.DateTime, txtContractNum.Text, mmDescr.Text, contract, btnedtAttachment.Text);
MessageBox.Show("Saved");
}
catch (Exception ex)
{
MessageBox.Show("Error: "+ ex.ToString());
}
//MessageBox.Show(Convert.ToString(cbbEmployee.EditValue));
}

public static byte[] ReadFully(string fileName, int initialLength)
{
// If we've been passed an unhelpful initial length, just
// use 32K.
if (initialLength < 1)
{
initialLength = 32768;
}
Stream stream = File.OpenRead(fileName);

byte[] buffer = new byte[initialLength];
int read = 0;

int chunk;
while ((chunk = stream.Read(buffer, read, buffer.Length - read)) > 0)
{
read += chunk;

// If we've reached the end of our buffer, check to see if there's
// any more information
if (read == buffer.Length)
{
int nextByte = stream.ReadByte();

// End of stream? If so, we're done
if (nextByte == -1)
{
return buffer;
}

// Nope. Resize the buffer, put in the byte we've just
// read, and continue
byte[] newBuffer = new byte[buffer.Length * 2];
Array.Copy(buffer, newBuffer, buffer.Length);
newBuffer[read] = (byte)nextByte;
buffer = newBuffer;
read++;
}
}
// Buffer is now too big. Shrink it.
byte[] ret = new byte[read];
Array.Copy(buffer, ret, read);
return ret;
}

No comments: