/// <summary>
/// Creates a new instance of the <see cref="SqlClientUploadStreamProvider" /> class with the specified configuration settings.
/// </summary>
/// <param name="settings">The <see cref="UploadStreamProviderElement" /> object that holds the configuration settings.</param>
public SqlClientUploadStreamProvider(UploadStreamProviderElement settings) :
base(settings)
{
string dataTypeString = Settings.Parameters["dataType"];
if (!string.IsNullOrEmpty(dataTypeString))
_dataType = (SqlColumnDataType)Enum.Parse(typeof(SqlColumnDataType), dataTypeString, true);
else
_dataType = SqlColumnDataType.Image;
if (_dataType == SqlColumnDataType.FileStream)
UseInsertTransaction = true;
}
/// <summary>
/// Creates a new instance of the <see cref="SqlClientUploadStreamProvider" /> class with the specified configuration settings.
/// </summary>
/// <param name="settings">The <see cref="UploadStreamProviderElement" /> object that holds the configuration settings.</param>
public SqlClientUploadStreamProvider(UploadStreamProviderElement settings) :
base(settings)
{
string dataTypeString = Settings.Parameters["dataType"];
if (!string.IsNullOrEmpty(dataTypeString))
{
_dataType = (SqlColumnDataType)Enum.Parse(typeof(SqlColumnDataType), dataTypeString, true);
}
else
{
_dataType = SqlColumnDataType.Image;
}
if (_dataType == SqlColumnDataType.FileStream)
{
UseInsertTransaction = true;
}
}
public SqlFileRepository(string uploadProfile)
{
UploadStreamProviderElement section = SlickUploadContext.Config.UploadProfiles[uploadProfile].UploadStreamProvider;
string connectionStringName = section.Parameters["connectionStringName"];
if (!string.IsNullOrEmpty(connectionStringName))
_cnString = ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;
else
_cnString = section.Parameters["connectionString"];
_table = section.Parameters["table"];
_keyField = section.Parameters["keyField"];
_nameField = section.Parameters["fileNameField"];
_dataField = section.Parameters["dataField"];
string dataTypeString = section.Parameters["dataType"];
if (!string.IsNullOrEmpty(dataTypeString))
_dataType = (SqlColumnDataType)Enum.Parse(typeof(SqlColumnDataType), dataTypeString, true);
else
_dataType = SqlColumnDataType.Image;
}
/// <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 string, table,
/// data field, and where criteria.
/// </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="whereCriteria">The where criteria that identifies the record.</param>
/// <param name="dataType">The data type of file data column.</param>
public SqlClientWriteStream(string connectionString, string table, string dataField, string whereCriteria, SqlColumnDataType dataType) :
this(new SqlConnection(connectionString), null, table, dataField, (IDbCommand cmd) => whereCriteria, dataType)
{ }
/// <summary>
/// Creates a new instance of the <see cref="SqlClientWriteStream" /> class with the specified connection string, table,
/// data field, id field, and id value.
/// </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="idField">The field which identifies the record.</param>
/// <param name="idValue">The value which identifies the record.</param>
/// <param name="dataType">The data type of file data column.</param>
public SqlClientWriteStream(string connectionString, string table, string dataField, string idField, long idValue, SqlColumnDataType dataType) :
this(connectionString, table, dataField, idField + "=" + idValue.ToString(), dataType)
{ }
/// <summary>
/// Creates a new instance of the <see cref="SqlClientWriteStream" /> class with the specified connection string, table,
/// data field, and where criteria.
/// </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="whereCriteria">The where criteria that identifies the record.</param>
/// <param name="dataType">The data type of file data column.</param>
public SqlClientWriteStream(string connectionString, string table, string dataField, string whereCriteria, SqlColumnDataType dataType) :
this(new SqlConnection(connectionString), null, table, dataField, (IDbCommand cmd) => whereCriteria, dataType)
{
}
/// <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 string, table,
/// data field, id field, and id value.
/// </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="idField">The field which identifies the record.</param>
/// <param name="idValue">The value which identifies the record.</param>
/// <param name="dataType">The data type of file data column.</param>
public SqlClientWriteStream(string connectionString, string table, string dataField, string idField, long idValue, SqlColumnDataType dataType) :
this(connectionString, table, dataField, idField + "=" + idValue.ToString(), dataType)
{
}
/// <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="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)
{ }
