Using FileUp with ADO.NET

FileUp fully supports the ADO.NET DataReader and DataTable objects. You can pass ADO.NET data types to FileUp's SaveAsBloband TransferBlobmethods. Note that you can still use ADO with FileUp in ASP.NET.

ASP.NET Samples
FileUp includes many sample ASP.NET applications in both C# and VB.NET. To run the samples, open a browser window and enter the URL http://localhost/safileupsamples. The sample code is in the folder FileUp\Samples.
ADO.NET and TransferBlob

FileUp's TransferBlob method downloads a file from a database. Using ADO.NET, a reference to the database BLOB that contains the file can pass the file to TransferBlob:

Using DataReader requires less memory and is almost always faster than using a byte array. In the following example, the file to download from the database is assigned to a DataReader object and the DataReader object is passed to TransferBlob.

C#
<%@ Page Language="C#" %>
<%@ Import namespace="System.Data" %>
<%@ Import namespace="System.Data.OleDb" %>
<%@ Import namespace="SoftArtisans.Net" %>

...

//--- Create the FileUp object
FileUp fileUpload = new FileUp(Context);

//--- Open an ADO.NET OleDbConnection
OleDbParameter param = new OleDbParameter();
OleDbConnection conn = new OleDbConnection(Application["connString"].ToString());
conn.Open();

//--- Set a new OleDbCommand object to query the database.
OleDbCommand cmdReadBinary = new OleDbCommand("SELECT FileBinary " &
" FROM UploadTable WHERE ID=1", conn);

//--- Use the DataReader object to get the query result (the
//--- file to download).
OleDbDataReader dr;
dr = cmdReadBinary.ExecuteReader(CommandBehavior.SequentialAccess);

...

//--- Pass the DataReader object to the TransferBlob method
//--- and download the file.
fileUpload.TransferBlob(dr, 0, 256);

...

Top

ADO.NET and SaveAsBlob

FileUp's SaveAsBlob method saves an uploaded file in a database. SaveAsBlob can write the uploaded file to an ADO.NET DataColumn, as demonstrated in the following example.

VB.NET
<%@ Page Language="VB"% >
<%@ Import namespace="SoftArtisans.Net" %>
<%@ Import namespace="System.Data.OleDb" %>
<%@ Import namespace="System.Data" %>

'--- Create an instance of FileUp.
Dim fileUpload As FileUp = New FileUp(Context)

...

'--- Set a new DataAdapter object to query the database.
Dim da As OleDbDataAdapter = New OleDbDataAdapter("SELECT FileName, " + _
"FileBinary FROM UploadTable", conn)

...

'--- Create a new DataSet and execute the SELECT statement
'--- to fill the DataSet with data.
Dim ds As DataSet = New DataSet()
da.MissingSchemaAction = MissingSchemaAction.AddWithKey
da.Fill(ds, "UploadTable")

'--- Create a new row in the DataSet.
Dim row As DataRow
row = ds.Tables("UploadTable").NewRow()

'--- Insert the file name and file into fields in the new row.
row(0) = fileUpload.ShortFilename
row(1) = fileUpload.SaveAsBlob()

'--- Update the DataSet
ds.Tables("UploadTable").Rows.Add(row)
da.Update(ds, "UploadTable")
conn.Close()

...

Top

Chunked Inserts and Updates in ADO.NET

FileUp supports chunked database inserts and updates with ADO.NET in SQL Server. This is done using SQL Server's UPDATETEXT command and FileUp's SaveAsBlob override method to write chunks of data to a BLOB column.

If UseMemoryis set to True, you cannot use SaveAsBlob to chunk uploaded data to the database.

Use the following SaveAsBlob syntax when creating a chunked update:

SaveAsBlob(SqlCommand SqlCmd, SqlParameter inParam, SqlParameter outParam)

SaveAsBlob is a method of both the FileUpobject and the Fileobject. If FileUp.SaveAsBlob is called in an upload of more than one file, the first file will be saved.

The following example shows you how to use the new SaveAsBlob method.

With UPDATETEXT the BLOB column - the column to which the file is uploaded - must not be null. Therefore, a single byte is written to the column. FileUp deletes the byte when the file is inserted.

C#
<%@ Page Language=C# debug=true trace=false %>
<%@ Import namespace="System.Data" %>
<%@ Import namespace="System.Data.OleDb" %>
<%@ Import namespace="System.Data.SqlClient" %>
<%@ Import Namespace="SoftArtisans.Net" %>
<script runat=server>

int Insert(SqlConnection cn)
{
// "SELECT @@IDENTITY;" is necessary only if you are retrieving an identity
// column value
String strSql = "INSERT INTO Categories(CategoryName, Description, Picture) " &
VALUES(@CategoryName, @Description, @Picture);SELECT @@IDENTITY;";

SqlCommand cmd = new SqlCommand(strSql, cn);

SqlParameter categoryNameParam =
cmd.Parameters.Add("@CategoryName", SqlDbType.NChar, 15);
SqlParameter descriptionParam =
cmd.Parameters.Add("@Description", SqlDbType.NText, 16);
SqlParameter pictureParam =
cmd.Parameters.Add("@Picture", SqlDbType.Binary, 16);

categoryNameParam.Value = "TestFile";
descriptionParam.Value = "My Test Image";

// The BLOB column (to which the file is uploaded) must not be null,
// so insert one byte. FileUp will delete this byte when the file is inserted.
pictureParam.Value = new System.Byte[1];

int identity= 0;
SqlDataReader reader= cmd.ExecuteReader(CommandBehavior.SequentialAccess);

// Retrieve the identity column value for the new row. This value
// will be used to open the new row.
reader.Read();
identity = (int)reader.GetDecimal(0);
reader.Close();

return nIdentity;
}

void Page_Load(object sender, EventArgs args)
{
try
{
FileUp fileUpload = new FileUp(Context);
SqlConnection cn = new SqlConnection("Data Source=localhost;" &
"Persist Security Info=False;User ID=sa;" &
"Initial Catalog=Northwind;");
cn.Open();
int identity= 0;

// Insert a new row into the database.
identity= Insert(cn);

String strSql = "SET NOCOUNT ON;UPDATE Categories SET Picture = 0x0 " &
"WHERE CategoryName='Seafood';SELECT @Pointer=TEXTPTR(Picture) " &
"FROM Categories WHERE CategoryID=@CategoryID";

SqlCommand cmdGetPointer = new SqlCommand(strSql, cn);
SqlParameter categoryIDParam =
cmdGetPointer.Parameters.Add("@CategoryID", SqlDbType.Int);
categoryIDParam.Value = nIdentity;

SqlParameter outParam =
cmdGetPointer.Parameters.Add("@Pointer", SqlDbType.VarBinary, 100);
outParam.Direction = ParameterDirection.Output;

// Open the row with the 'UPDATE' sql command and get a 'TEXTPTR'
// pointer to the blob column.
int nRows = cmdGetPointer.ExecuteNonQuery();

// Verify that the row was opened.
if(outParam.Value == System.DBNull.Value)
{
Response.Write("Failed to open row");
cn.Close();
return;
}

// Create an System.Data.SqlClient.SqlCommand reference using the
// 'UPDATETEXT' sql command, creating 'Pointer', 'Offset', and 'Delete'
// parameters so that FileUp can properly chunk data to the database column.
SqlCommand sqlCmd = new SqlCommand("UPDATETEXT Categories.Picture " &
"@Pointer @Offset @Delete WITH LOG @Bytes", cn);
SqlParameter inParam =
sqlCmd.Parameters.Add("@Pointer", SqlDbType.Binary, 16);

//call SaveAsBlob
fileUpload.SaveAsBlob(sqlCmd, inParam, outParam);

//Close the database connection.
cn.Close();
}
catch(Exception e)
{
Response.Write(e.ToString());
}
}
</script>

Top

Copyright © 2010 SoftArtisans, Inc. All rights reserved.