/// <summary>
/// Creates a new instance of the <see cref="SqlFileStreamReadStream" /> class with the specified connection, table,
/// data field, and where criteria action.
/// </summary>
/// <param name="connection">The <see cref="IDbConnection" /> to use.</param>
/// <param name="table">The table in which the data is stored.</param>
/// <param name="dataField">The field in which the data is stored</param>
/// <param name="criteriaAction">The <see cref="BuildWhereCriteriaAction"/> to use to generate criteria that identifies the record.</param>
public SqlFileStreamReadStream(IDbConnection connection, string table, string dataField, BuildWhereCriteriaAction criteriaAction)
: base(connection, OpenConnectionAndBeginTransaction(connection))
{
// TODO: add buffering
try
{
using (SqlCommand cmd = ((SqlConnection)Connection).CreateCommand())
{
cmd.CommandText = "SELECT " + dataField + ".PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT() FROM " + table + " WHERE " + criteriaAction(cmd);
cmd.Transaction = (SqlTransaction)Transaction;
using (SqlDataReader rd = cmd.ExecuteReader(CommandBehavior.SingleRow))
{
rd.Read();
string path = rd.GetString(0);
byte[] context = (byte[])rd.GetValue(1);
// todo: fileoptions, allocationsize
_innerStream = new SqlFileStream(path, context, FileAccess.Read);
}
}
}
catch
{
if (_innerStream != null)
_innerStream.Dispose();
base.Close();
// TODO: cleanup
throw;
}
}
/// <summary>
/// Creates a new instance of the <see cref="SqlFileStreamWriteStream" /> class with the specified connection, transaction, table,
/// data field, and where criteria action.
/// </summary>
/// <param name="connection">The <see cref="IDbConnection" /> to use.</param>
/// <param name="transaction">The <see cref="IDbTransaction" /> to use.</param>
/// <param name="table">The table in which the data is stored.</param>
/// <param name="dataField">The field in which the data is stored</param>
/// <param name="criteriaAction">The <see cref="BuildWhereCriteriaAction"/> to use to generate criteria that identifies the record.</param>
public SqlFileStreamWriteStream(IDbConnection connection, IDbTransaction transaction, string table, string dataField, BuildWhereCriteriaAction criteriaAction)
: base(connection, transaction)
{
// TODO: throw exception if no transaction
// TODO: add buffering
using (SqlCommand cmd = ((SqlConnection)Connection).CreateCommand())
{
cmd.CommandText = "UPDATE " + table + " SET " + dataField + "=CAST('' AS varbinary(MAX)) " +
"OUTPUT INSERTED." + dataField + ".PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT() WHERE " + criteriaAction(cmd);
cmd.Transaction = (SqlTransaction)Transaction;
try
{
using (SqlDataReader rd = cmd.ExecuteReader(CommandBehavior.SingleRow))
{
if (rd.Read())
{
string path = rd.GetString(0);
byte[] context = (byte[])rd.GetValue(1);
// todo: fileoptions, allocationsize
_innerStream = new SqlFileStream(path, context, FileAccess.Write);
}
else
{
// TODO: throw exception
}
}
}
catch
{
if (_innerStream != null)
_innerStream.Dispose();
base.Close();
// TODO: cleanup
throw;
}
}
}
/// <summary>
/// Creates a new instance of the <see cref="SqlClientWriteStream" /> class with the specified connection, transaction, table,
/// data field, and where criteria action.
/// </summary>
/// <param name="connection">The <see cref="IDbConnection" /> to use.</param>
/// <param name="transaction">The <see cref="IDbTransaction" /> to use, or null for no transaction.</param>
/// <param name="table">The table in which the data is stored.</param>
/// <param name="dataField">The field in which the data is stored</param>
/// <param name="criteriaAction">The <see cref="BuildWhereCriteriaAction"/> to use to generate criteria that identifies the record.</param>
/// <param name="dataType">The data type of file data column.</param>
public SqlClientWriteStream(IDbConnection connection, IDbTransaction transaction, string table, string dataField, BuildWhereCriteriaAction criteriaAction, SqlColumnDataType dataType)
: base(connection, transaction)
{
if (dataType == SqlColumnDataType.FileStream)
throw new ArgumentException("SqlClientWriteStream only supports IMAGE and VARBINARY(MAX). Use SqlFileStreamWriteStream for FILESTREAM.", "dataType");
_dataType = dataType;
// TODO: add buffering
_cmd = (SqlCommand)Connection.CreateCommand();
_cmd.Transaction = transaction as SqlTransaction;
string whereCriteria = criteriaAction(_cmd);
if (_dataType == SqlColumnDataType.VarBinaryMax)
_cmd.CommandText = "UPDATE " + table + " SET " + dataField + "=CAST('' AS varbinary(MAX)) WHERE " + whereCriteria;
else
_cmd.CommandText = "UPDATE " + table + " SET " + dataField + "=NULL WHERE " + whereCriteria;
try
{
if (Connection.State != ConnectionState.Open)
Connection.Open();
_cmd.ExecuteNonQuery();
}
finally
{
if (Transaction == null)
Connection.Close();
}
if (_dataType != SqlColumnDataType.VarBinaryMax)
{
byte[] ptr;
_cmd.CommandText = "SELECT TEXTPTR(" + dataField + ") FROM " + table + " WHERE " + whereCriteria;
try
{
if (Connection.State != ConnectionState.Open)
Connection.Open();
ptr = (byte[])_cmd.ExecuteScalar();
}
finally
{
if (Transaction == null)
Connection.Close();
}
_cmd.CommandText = "UPDATETEXT " + table + "." + dataField + " @ptr @offset NULL @data;";
//_cmd.CommandText = "DECLARE @ptr binary(16);" +
// "SELECT @ptr = TEXTPTR(" + dataField + ")" +
// "FROM " + table + " WHERE " + whereCriteria +
//";UPDATETEXT " + table + "." + dataField + " @ptr @offset NULL @data;";
_cmd.Parameters.Clear();
SqlParameter ptrParam = _cmd.CreateParameter();
ptrParam.DbType = DbType.Binary;
ptrParam.ParameterName = "@ptr";
ptrParam.Size = 16;
ptrParam.Value = ptr;
_cmd.Parameters.Add(ptrParam);
}
else
{
// TODO: add update length
_cmd.CommandText = "UPDATE " + table + " SET " + dataField + " .WRITE(@data, @offset, NULL) WHERE " + whereCriteria;
}
_offsetParam = _cmd.CreateParameter();
_offsetParam.DbType = DbType.Int32;
_offsetParam.ParameterName = "@offset";
_offsetParam.Size = 4;
_cmd.Parameters.Add(_offsetParam);
_dataParam = _cmd.CreateParameter();
_dataParam.SqlDbType = SqlDbType.Image;
_dataParam.ParameterName = "@data";
_dataParam.Size = 8040;
_cmd.Parameters.Add(_dataParam);
/*try
{
_cn.Open();
_cmd.Prepare();
}
catch (Exception ex)
{
ex.ToString();
}
finally
{
_cn.Close();
}*/
}
/// <summary>
/// Creates a new instance of the <see cref="SqlClientWriteStream" /> class with the specified connection, transaction, table,
/// data field, and where criteria action.
/// </summary>
/// <param name="connection">The <see cref="IDbConnection" /> to use.</param>
/// <param name="transaction">The <see cref="IDbTransaction" /> to use, or null for no transaction.</param>
/// <param name="table">The table in which the data is stored.</param>
/// <param name="dataField">The field in which the data is stored</param>
/// <param name="criteriaAction">The <see cref="BuildWhereCriteriaAction"/> to use to generate criteria that identifies the record.</param>
/// <param name="dataType">The data type of file data column.</param>
public SqlClientWriteStream(IDbConnection connection, IDbTransaction transaction, string table, string dataField, BuildWhereCriteriaAction criteriaAction, SqlColumnDataType dataType)
: base(connection, transaction)
{
if (dataType == SqlColumnDataType.FileStream)
{
throw new ArgumentException("SqlClientWriteStream only supports IMAGE and VARBINARY(MAX). Use SqlFileStreamWriteStream for FILESTREAM.", "dataType");
}
_dataType = dataType;
// TODO: add buffering
_cmd = (SqlCommand)Connection.CreateCommand();
_cmd.Transaction = transaction as SqlTransaction;
string whereCriteria = criteriaAction(_cmd);
if (_dataType == SqlColumnDataType.VarBinaryMax)
{
_cmd.CommandText = "UPDATE " + table + " SET " + dataField + "=CAST('' AS varbinary(MAX)) WHERE " + whereCriteria;
}
else
{
_cmd.CommandText = "UPDATE " + table + " SET " + dataField + "=NULL WHERE " + whereCriteria;
}
try
{
if (Connection.State != ConnectionState.Open)
{
Connection.Open();
}
_cmd.ExecuteNonQuery();
}
finally
{
if (Transaction == null)
{
Connection.Close();
}
}
if (_dataType != SqlColumnDataType.VarBinaryMax)
{
byte[] ptr;
_cmd.CommandText = "SELECT TEXTPTR(" + dataField + ") FROM " + table + " WHERE " + whereCriteria;
try
{
if (Connection.State != ConnectionState.Open)
{
Connection.Open();
}
ptr = (byte[])_cmd.ExecuteScalar();
}
finally
{
if (Transaction == null)
{
Connection.Close();
}
}
_cmd.CommandText = "UPDATETEXT " + table + "." + dataField + " @ptr @offset NULL @data;";
//_cmd.CommandText = "DECLARE @ptr binary(16);" +
// "SELECT @ptr = TEXTPTR(" + dataField + ")" +
// "FROM " + table + " WHERE " + whereCriteria +
//";UPDATETEXT " + table + "." + dataField + " @ptr @offset NULL @data;";
_cmd.Parameters.Clear();
SqlParameter ptrParam = _cmd.CreateParameter();
ptrParam.DbType = DbType.Binary;
ptrParam.ParameterName = "@ptr";
ptrParam.Size = 16;
ptrParam.Value = ptr;
_cmd.Parameters.Add(ptrParam);
}
else
{
// TODO: add update length
_cmd.CommandText = "UPDATE " + table + " SET " + dataField + " .WRITE(@data, @offset, NULL) WHERE " + whereCriteria;
}
_offsetParam = _cmd.CreateParameter();
_offsetParam.DbType = DbType.Int32;
_offsetParam.ParameterName = "@offset";
_offsetParam.Size = 4;
_cmd.Parameters.Add(_offsetParam);
_dataParam = _cmd.CreateParameter();
_dataParam.SqlDbType = SqlDbType.Image;
_dataParam.ParameterName = "@data";
_dataParam.Size = 8040;
_cmd.Parameters.Add(_dataParam);
/*try
* {
* _cn.Open();
* _cmd.Prepare();
* }
* catch (Exception ex)
* {
* ex.ToString();
* }
* finally
* {
* _cn.Close();
* }*/
}
/// <summary>
/// Creates a new instance of the <see cref="SqlFileStreamWriteStream" /> class with the specified connection, transaction, table,
/// data field, and where criteria action.
/// </summary>
/// <param name="connection">The <see cref="IDbConnection" /> to use.</param>
/// <param name="transaction">The <see cref="IDbTransaction" /> to use.</param>
/// <param name="table">The table in which the data is stored.</param>
/// <param name="dataField">The field in which the data is stored</param>
/// <param name="criteriaAction">The <see cref="BuildWhereCriteriaAction"/> to use to generate criteria that identifies the record.</param>
public SqlFileStreamWriteStream(IDbConnection connection, IDbTransaction transaction, string table, string dataField, BuildWhereCriteriaAction criteriaAction)
: base(connection, transaction)
{
// TODO: throw exception if no transaction
// TODO: add buffering
using (SqlCommand cmd = ((SqlConnection)Connection).CreateCommand())
{
cmd.CommandText = "UPDATE " + table + " SET " + dataField + "=CAST('' AS varbinary(MAX)) " +
"OUTPUT INSERTED." + dataField + ".PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT() WHERE " + criteriaAction(cmd);
cmd.Transaction = (SqlTransaction)Transaction;
try
{
using (SqlDataReader rd = cmd.ExecuteReader(CommandBehavior.SingleRow))
{
if (rd.Read())
{
string path = rd.GetString(0);
byte[] context = (byte[])rd.GetValue(1);
// todo: fileoptions, allocationsize
_innerStream = new SqlFileStream(path, context, FileAccess.Write);
}
else
{
// TODO: throw exception
}
}
}
catch
{
if (_innerStream != null)
{
_innerStream.Dispose();
}
base.Close();
// TODO: cleanup
throw;
}
}
}
/// <summary>
/// Creates a new instance of the <see cref="SqlClientReadStream" /> class with the specified connection, table,
/// data field, and where criteria action.
/// </summary>
/// <param name="connection">The <see cref="IDbConnection" /> to use.</param>
/// <param name="table">The table in which the data is stored.</param>
/// <param name="dataField">The field in which the data is stored</param>
/// <param name="criteriaAction">The <see cref="BuildWhereCriteriaAction"/> to use to generate criteria that identifies the record.</param>
/// <param name="dataType">The data type of file data column.</param>
public SqlClientReadStream(IDbConnection connection, string table, string dataField, BuildWhereCriteriaAction criteriaAction, SqlColumnDataType dataType)
: base(connection)
{
if (dataType == SqlColumnDataType.FileStream)
{
throw new ArgumentException("SqlClientReadStream only supports IMAGE and VARBINARY(MAX). Use SqlFileStreamReadStream for FILESTREAM.", "dataType");
}
_dataType = dataType;
// TODO: add buffering
_cmd = (SqlCommand)Connection.CreateCommand();
string whereCriteria = criteriaAction(_cmd);
_cmd.CommandText = "SELECT CAST(DATALENGTH(" + dataField + ") AS bigint) FROM " + table + " WHERE " + whereCriteria;
try
{
if (Connection.State != ConnectionState.Open)
{
Connection.Open();
}
_length = (long)_cmd.ExecuteScalar();
}
finally
{
Connection.Close();
}
if (_dataType != SqlColumnDataType.VarBinaryMax)
{
byte[] ptr;
_cmd.CommandText = "SELECT TEXTPTR(" + dataField + ") FROM " + table + " WHERE " + whereCriteria;
try
{
if (Connection.State != ConnectionState.Open)
{
Connection.Open();
}
ptr = (byte[])_cmd.ExecuteScalar();
}
finally
{
Connection.Close();
}
_cmd.CommandText = "READTEXT " + table + "." + dataField + " @ptr @offset @size;";
_cmd.Parameters.Clear();
SqlParameter ptrParam = _cmd.CreateParameter();
ptrParam.DbType = DbType.Binary;
ptrParam.ParameterName = "@ptr";
ptrParam.Size = 16;
ptrParam.Value = ptr;
_cmd.Parameters.Add(ptrParam);
}
else
{
// TODO: test SqlBinary perf vs SUBSTRING
_cmd.CommandText = "SELECT SUBSTRING(" + dataField + ",@offset,@size) FROM " + table + " WHERE " + whereCriteria;
}
_offsetParam = _cmd.CreateParameter();
_offsetParam.DbType = (_dataType == SqlColumnDataType.VarBinaryMax) ? DbType.Int64 : DbType.Int32;
_offsetParam.ParameterName = "@offset";
_offsetParam.Size = (_dataType == SqlColumnDataType.VarBinaryMax) ? 4 : 8;
_cmd.Parameters.Add(_offsetParam);
_sizeParam = _cmd.CreateParameter();
_sizeParam.DbType = (_dataType == SqlColumnDataType.VarBinaryMax) ? DbType.Int64 : DbType.Int32;
_sizeParam.ParameterName = "@size";
_sizeParam.Size = (_dataType == SqlColumnDataType.VarBinaryMax) ? 4 : 8;
_cmd.Parameters.Add(_sizeParam);
}
/// <summary>
/// Creates a new instance of the <see cref="SqlClientReadStream" /> class with the specified connection string, table,
/// data field, and where criteria action.
/// </summary>
/// <param name="connectionString">The connection string of the database to use.</param>
/// <param name="table">The table in which the data is stored.</param>
/// <param name="dataField">The field in which the data is stored</param>
/// <param name="criteriaAction">The <see cref="BuildWhereCriteriaAction"/> to use to generate criteria that identifies the record.</param>
/// <param name="dataType">The data type of file data column.</param>
public SqlClientReadStream(string connectionString, string table, string dataField, BuildWhereCriteriaAction criteriaAction, SqlColumnDataType dataType) :
this(new SqlConnection(connectionString), table, dataField, criteriaAction, dataType)
{
}
/// <summary>
/// Creates a new instance of the <see cref="SqlFileStreamReadStream" /> class with the specified connection string, table,
/// data field, and where criteria action.
/// </summary>
/// <param name="connectionString">The connection string of the database to use.</param>
/// <param name="table">The table in which the data is stored.</param>
/// <param name="dataField">The field in which the data is stored</param>
/// <param name="criteriaAction">The <see cref="BuildWhereCriteriaAction"/> to use to generate criteria that identifies the record.</param>
public SqlFileStreamReadStream(string connectionString, string table, string dataField, BuildWhereCriteriaAction criteriaAction) :
this(new SqlConnection(connectionString), table, dataField, criteriaAction)
{ }
/// <summary>
/// Creates a new instance of the <see cref="SqlFileStreamReadStream" /> class with the specified connection, table,
/// data field, and where criteria action.
/// </summary>
/// <param name="connection">The <see cref="IDbConnection" /> to use.</param>
/// <param name="table">The table in which the data is stored.</param>
/// <param name="dataField">The field in which the data is stored</param>
/// <param name="criteriaAction">The <see cref="BuildWhereCriteriaAction"/> to use to generate criteria that identifies the record.</param>
public SqlFileStreamReadStream(IDbConnection connection, string table, string dataField, BuildWhereCriteriaAction criteriaAction)
: base(connection, OpenConnectionAndBeginTransaction(connection))
{
// TODO: add buffering
try
{
using (SqlCommand cmd = ((SqlConnection)Connection).CreateCommand())
{
cmd.CommandText = "SELECT " + dataField + ".PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT() FROM " + table + " WHERE " + criteriaAction(cmd);
cmd.Transaction = (SqlTransaction)Transaction;
using (SqlDataReader rd = cmd.ExecuteReader(CommandBehavior.SingleRow))
{
rd.Read();
string path = rd.GetString(0);
byte[] context = (byte[])rd.GetValue(1);
// todo: fileoptions, allocationsize
_innerStream = new SqlFileStream(path, context, FileAccess.Read);
}
}
}
catch
{
if (_innerStream != null)
{
_innerStream.Dispose();
}
base.Close();
// TODO: cleanup
throw;
}
}
/// <summary>
/// Creates a new instance of the <see cref="SqlFileStreamReadStream" /> class with the specified connection string, table,
/// data field, and where criteria action.
/// </summary>
/// <param name="connectionString">The connection string of the database to use.</param>
/// <param name="table">The table in which the data is stored.</param>
/// <param name="dataField">The field in which the data is stored</param>
/// <param name="criteriaAction">The <see cref="BuildWhereCriteriaAction"/> to use to generate criteria that identifies the record.</param>
public SqlFileStreamReadStream(string connectionString, string table, string dataField, BuildWhereCriteriaAction criteriaAction) :
this(new SqlConnection(connectionString), table, dataField, criteriaAction)
{
}
/// <summary>
/// Creates a new instance of the <see cref="SqlClientReadStream" /> class with the specified connection, table,
/// data field, and where criteria action.
/// </summary>
/// <param name="connection">The <see cref="IDbConnection" /> to use.</param>
/// <param name="table">The table in which the data is stored.</param>
/// <param name="dataField">The field in which the data is stored</param>
/// <param name="criteriaAction">The <see cref="BuildWhereCriteriaAction"/> to use to generate criteria that identifies the record.</param>
/// <param name="dataType">The data type of file data column.</param>
public SqlClientReadStream(IDbConnection connection, string table, string dataField, BuildWhereCriteriaAction criteriaAction, SqlColumnDataType dataType)
: base(connection)
{
if (dataType == SqlColumnDataType.FileStream)
throw new ArgumentException("SqlClientReadStream only supports IMAGE and VARBINARY(MAX). Use SqlFileStreamReadStream for FILESTREAM.", "dataType");
_dataType = dataType;
// TODO: add buffering
_cmd = (SqlCommand)Connection.CreateCommand();
string whereCriteria = criteriaAction(_cmd);
_cmd.CommandText = "SELECT CAST(DATALENGTH(" + dataField + ") AS bigint) FROM " + table + " WHERE " + whereCriteria;
try
{
if (Connection.State != ConnectionState.Open)
Connection.Open();
_length = (long)_cmd.ExecuteScalar();
}
finally
{
Connection.Close();
}
if (_dataType != SqlColumnDataType.VarBinaryMax)
{
byte[] ptr;
_cmd.CommandText = "SELECT TEXTPTR(" + dataField + ") FROM " + table + " WHERE " + whereCriteria;
try
{
if (Connection.State != ConnectionState.Open)
Connection.Open();
ptr = (byte[])_cmd.ExecuteScalar();
}
finally
{
Connection.Close();
}
_cmd.CommandText = "READTEXT " + table + "." + dataField + " @ptr @offset @size;";
_cmd.Parameters.Clear();
SqlParameter ptrParam = _cmd.CreateParameter();
ptrParam.DbType = DbType.Binary;
ptrParam.ParameterName = "@ptr";
ptrParam.Size = 16;
ptrParam.Value = ptr;
_cmd.Parameters.Add(ptrParam);
}
else
{
// TODO: test SqlBinary perf vs SUBSTRING
_cmd.CommandText = "SELECT SUBSTRING(" + dataField + ",@offset,@size) FROM " + table + " WHERE " + whereCriteria;
}
_offsetParam = _cmd.CreateParameter();
_offsetParam.DbType = (_dataType == SqlColumnDataType.VarBinaryMax) ? DbType.Int64 : DbType.Int32;
_offsetParam.ParameterName = "@offset";
_offsetParam.Size = (_dataType == SqlColumnDataType.VarBinaryMax) ? 4 : 8;
_cmd.Parameters.Add(_offsetParam);
_sizeParam = _cmd.CreateParameter();
_sizeParam.DbType = (_dataType == SqlColumnDataType.VarBinaryMax) ? DbType.Int64 : DbType.Int32;
_sizeParam.ParameterName = "@size";
_sizeParam.Size = (_dataType == SqlColumnDataType.VarBinaryMax) ? 4 : 8;
_cmd.Parameters.Add(_sizeParam);
}
/// <summary>
/// Creates a new instance of the <see cref="SqlClientReadStream" /> class with the specified connection string, table,
/// data field, and where criteria action.
/// </summary>
/// <param name="connectionString">The connection string of the database to use.</param>
/// <param name="table">The table in which the data is stored.</param>
/// <param name="dataField">The field in which the data is stored</param>
/// <param name="criteriaAction">The <see cref="BuildWhereCriteriaAction"/> to use to generate criteria that identifies the record.</param>
/// <param name="dataType">The data type of file data column.</param>
public SqlClientReadStream(string connectionString, string table, string dataField, BuildWhereCriteriaAction criteriaAction, SqlColumnDataType dataType) :
this(new SqlConnection(connectionString), table, dataField, criteriaAction, dataType)
{ }
