What Datatype to use for this so that the image can upload to the SQL Server?

Question

Visual Studio, c#, SQL 2005 server. I"m trying to match the .dbml table data type with my .cs file. The goal is to allow an image to load to the database. So far it is not working. The issue seems to be related to the file type for the FileContent column. I have tried several different variations, but none have worked.

<Column Name="FileName" Type="System.String" DbType="NVarChar(100)" CanBeNull="true" />
<Column Name="FileType" Type="System.String" DbType="NVarChar(100)" CanBeNull="true" />
<Column Name="FileSize" Type="System.int32" DbType="int" CanBeNull="true" />
<Column Name="FileContent" Type="System.Data.Linq.Binary" DbType="varbinary(MAX)" CanBeNull="true" />

SQL Server Columns
Applicant_PK(PK,int,notnull)
FileName(nvarchar(100),null)
FileType(nvarchar(100),null)
FileSize(int,null)
FileContent(varbinary(max),null)

 void CreatePreApplication()
{
    Pre_Application = new PreApplication();
    Pre_Application.FileName = Path.GetFileName(ctrFile.PostedFile.FileName);
    Pre_Application.FileType = ctrFile.PostedFile.ContentType;
    Pre_Application.FileSize = ctrFile.PostedFile.ContentLength;
    byte[] fileContent = new byte[ctrFile.PostedFile.ContentLength];
    ctrFile.PostedFile.InputStream.Read(fileContent, 0, ctrFile.PostedFile.ContentLength);
    Pre_Application.FileContent = fileContent;     

public class PreApplication

{ public int DatabaseId { get; set; } public String FileName { get; set; } public String FileType { get; set; } public int FileSize { get; set; } public byte[] FileContent { get; set; } public PreApplication()

 {
    PreApplicationsDataContext db =
        new PreApplicationsDataContext(
            "Data Source=THESQLSERVER;Initial Catalog=THECONNECTIONSTRING;Integrated Security=True");
    tblPreApplication preApp = new tblPreApplication();
    preApp.FileName = FileName;
    preApp.FileType = FileType;
    preApp.FileSize = FileSize;
    preApp.FileContent = (byte[])FileContent;
    try

    {
        db.tblPreApplications.InsertOnSubmit(preApp);
        db.SubmitChanges();
        DatabaseId = preApp.Applicant_PK;
        return preApp.Applicant_PK;
    }
    catch
    {
        DatabaseId = 0;
        return 0;   
    }        
}

Thanks for looking at this. I am a novice at programming, so if you ask me a question, please keep this in mind.

Answer 1

I see the problem... you are creating the db connection and trying to insert in the constructor.

your should be class is defined like so

public PreApplication() {
}

public DoInsert {
  PreApplicationsDataContext db =
    new PreApplicationsDataContext("Data Source=THESQLSERVER;Initial Catalog=THECONNECTIONSTRING;Integrated Security=True");
  tblPreApplication preApp = new tblPreApplication();
  preApp.FileName = FileName;
  preApp.FileType = FileType;
  preApp.FileSize = FileSize;
  preApp.FileContent = (byte[])FileContent;
  try {
    db.tblPreApplications.InsertOnSubmit(preApp);
    db.SubmitChanges();
    DatabaseId = preApp.Applicant_PK;
    return preApp.Applicant_PK;
  } catch {
    DatabaseId = 0;
    return 0;   
  }  
}

and then your execute function

void CreatePreApplication() {
    Pre_Application p = new PreApplication();
    p.FileName = Path.GetFileName(ctrFile.PostedFile.FileName);
    p.FileType = ctrFile.PostedFile.ContentType;
    p.FileSize = ctrFile.PostedFile.ContentLength;
    byte[] fileContent = new byte[ctrFile.PostedFile.ContentLength];
    ctrFile.PostedFile.InputStream.Read(fileContent, 0, ctrFile.PostedFile.ContentLength);
    p.FileContent = fileContent;

    //do the insert after you have assigned all the variables
    p.DoInsert();
}

Answer 2

I would double check your connection string, if you're working locally use "." for THESQLSERVER, and make sure the Initial Catalog = NameOfDataBase. If you're not sure what object is null, try stepping thru it in the debugger and hover over the db variable. Make sure that is not null.

You may also find it useful to have a try catch to better handle when things go bad. So you may want to read up on exception handling.

Finally, it's not a bad idea to make sure the steam is at the beginning before reading or you can always reset it as such:

// Ensure stream is at 0 index.
if (stream.Position != 0)
{
   stream.Seek(0, SeekOrigin.Begin);
}

Hope that helps a bit,

Eddy