Here's the table declaration:
CREATE TABLE version
(
version_id INT NOT NULL IDENTITY(1,1),
device_type_id INT NOT NULL,
version_major INT NOT NULL,
version_minor INT NOT NULL,
version_build INT NULL,
version_variant VARCHAR(5) NULL,
version_pre_release VARCHAR(5) NULL,
release_date datetime NOT NULL DEFAULT(GETDATE()),
download_url varchar(150) NOT NULL,
firmware VARBINARY(500000) NULL,
deleted datetime NULL,
CONSTRAINT pk_version PRIMARY KEY (version_id),
CONSTRAINT fk_version_device_type FOREIGN KEY (device_type_id)
REFERENCES z_device_type (device_type_id),
CONSTRAINT uq_version UNIQUE (device_type_id, version_major,
version_minor, version_build, version_variant, version_pre_release,
deleted)
)
GO
Then, the proc that is being called:
CREATE PROCEDURE [dbo].[up_version_Insert_AllRowData]
(
@device_type_id AS [int]
,@version_major AS [int]
,@version_minor AS [int]
,@version_build AS [int]
,@version_variant AS [varchar] (5) = NULL
,@version_pre_release AS [varchar] (5) = NULL
,@release_date AS [datetime]
,@download_url AS [varchar] (150)
,@firmware AS [varbinary]
,@deleted AS [datetime]
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @id int
SET @id = 0
INSERT INTO [dbo].[version]
(
[device_type_id]
,[version_major]
,[version_minor]
,[version_build]
,[version_variant]
,[version_pre_release]
,[release_date]
,[download_url]
,[firmware]
,[deleted]
)
VALUES
(
@device_type_id
,@version_major
,@version_minor
,@version_build
,@version_variant
,@version_pre_release
,@release_date
,@download_url
,@firmware
,@deleted
)
IF @@ERROR = 0
BEGIN
-- [Identity] Return the ID of the new Row
SET @id = SCOPE_IDENTITY()
END
RETURN @id
END
Hope you can spot an issue...
The method in my Data Accessor to add the object to the database is:
private static SqlCommand PrepareAdd(Database db, VersionObject
versionObject)
{
if(db == null)
throw new ArgumentNullException("db", "Database object can not be
null.");
if(versionObject == null)
throw new ArgumentNullException("versionObject", "Add parameter
can not be null.");
const string sqlCommand = "up_version_Insert_AllRowData";
SqlCommand cmd = new SqlCommand(sqlCommand);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@device_type_id",
DbFunctions.SetInt32(versionObject.DeviceTypeId)));
cmd.Parameters.Add(new SqlParameter("@version_major",
DbFunctions.SetInt32(versionObject.VersionMajor)));
cmd.Parameters.Add(new SqlParameter("@version_minor",
DbFunctions.SetInt32(versionObject.VersionMinor)));
cmd.Parameters.Add(new SqlParameter("@version_build",
DbFunctions.SetNullableInt32(versionObject.VersionBuild)));
cmd.Parameters.Add(new SqlParameter("@version_variant",
DbFunctions.SetString(versionObject.VersionVariant)));
cmd.Parameters.Add(new SqlParameter("@version_pre_release",
DbFunctions.SetString(versionObject.VersionPreRelease)));
cmd.Parameters.Add(new SqlParameter("@release_date",
DbFunctions.SetDateTime(versionObject.ReleaseDate)));
cmd.Parameters.Add(new SqlParameter("@download_url",
versionObject.Firmware.Length));//
DbFunctions.SetString(versionObject.DownloadUrl)));
cmd.Parameters.Add(new SqlParameter("@firmware",
versionObject.Firmware));
cmd.Parameters.Add(new SqlParameter("@deleted",
DbFunctions.SetNullableDateTime(versionObject.Deleted)));
SqlParameter returnParameter = new SqlParameter("@RETURN_VALUE",
SqlDbType.Int);
returnParameter.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(returnParameter);
return cmd;
}
Note that I set @download_url to the size of the byte[] I am trying to
store fort test reasons, and it does report the correct size.
And finally, this is my versionObject:
public partial class VersionObject
{
#region Public Variable Declarations
/// <summary>
/// version_id Holds data for the Column version_id in table version
/// </summary>
public int VersionId;
/// <summary>
/// device_type_id Holds data for the Column device_type_id in table
version
/// </summary>
public int DeviceTypeId;
/// <summary>
/// version_major Holds data for the Column version_major in table
version
/// </summary>
public int VersionMajor;
/// <summary>
/// version_minor Holds data for the Column version_minor in table
version
/// </summary>
public int VersionMinor;
/// <summary>
/// version_build Holds data for the Column version_build in table
version
/// </summary>
public int? VersionBuild;
/// <summary>
/// version_variant Holds data for the Column version_variant in
table version
/// </summary>
public string VersionVariant;
/// <summary>
/// version_pre_release Holds data for the Column
version_pre_release in table version
/// </summary>
public string VersionPreRelease;
/// <summary>
/// release_date Holds data for the Column release_date in table
version
/// </summary>
public DateTime ReleaseDate;
/// <summary>
/// download_url Holds data for the Column download_url in table
version
/// </summary>
public string DownloadUrl;
/// <summary>
/// firmware Holds data for the Column firmware in table version
/// </summary>
public byte[] Firmware;
/// <summary>
/// deleted Holds data for the Column deleted in table version
/// </summary>
public DateTime? Deleted;
#endregion
}
