Iran Developers Network

CREATE FUNCTION dbo.GetTableVarchar(@Data image)
RETURNS @DataTable
TABLE (RowID int primary key IDENTITY , Value Varchar(8000))
AS
BEGIN
–First Test the data is of type Varchar.
IF(dbo.ValidateExpectedType(103, @Data)<>1) RETURN

–Loop thru the list inserting each
– item into the variable table.
DECLARE @Ptr int, @Length int,
@VarcharLength smallint, @Value Varchar(8000)
SELECT @Length = DataLength(@Data), @Ptr = 2
WHILE(@Ptr<@Length)
BEGIN
–The first 2 bytes of each item is the length of the
–varchar, a negative number designates a null value.
SET @VarcharLength = SUBSTRING(@Data, @ptr, 2)
SET @Ptr = @Ptr + 2
IF(@VarcharLength<0)
SET @Value = NULL
ELSE
BEGIN
SET @Value = SUBSTRING(@Data, @ptr, @VarcharLength)
SET @Ptr = @Ptr + @VarcharLength
END
INSERT INTO @DataTable (Value) VALUES(@Value)
END
RETURN
END

private DataSet GetSelectedSnailDetails()
{
SQLIntListCreator snailIDs = new SQLIntListCreator();
foreach(Snail aSnail in mySelectionCheckedListBox.CheckedItems)
snailIDs.AddValue(aSnail.SnailID);

return SQLHelper.ExecuteDataset(CONN_STRING,
“GetSnailDetails”, snailIDs.GetList());
}

CREATE PROCEDURE GetSnailDetails
@SnailIDs image
AS
SELECT Snail.*
FROM dbo.GetTableInt(@SnailIDs) SIDs
INNER JOIN Snails ON SIDs.Value = Snails.SnailID
GO

CREATE PROCEDURE DeleteSnails
@SnailIDs image
AS
DELETE Snail
FROM dbo.GetTableInt(@SnailIDs) SIDs
INNER JOIN Snails ON SIDs.Value = Snails.SnailID
GO

private void UpdateSnailStatuses(Snail[] snails)
{
SQLIntListCreator snailIDs = new SQLIntListCreator();
SQLVarcharListCreator statuses =
new SQLVarcharListCreator();
SQLRowversionListCreator rowVersions =
new SQLRowversionListCreator();

foreach (Snail aSnail in snails)
{
snailIDs.AddValue(aSnail.SnailID);
statuses.AddValue(aSnail.Status);
rowVersions.AddValue(aSnail.RowVersion);
}

SQLHelper.ExecuteNonquery(CONN_STRING,
“UpdateSnailStatuses”, snailIDs.GetList(),
statuses.GetList(), rowVersions.GetList());
}

CREATE PROCEDURE dbo.UpdateSnailStatuses
@SnailIDs image,
@Statuses image,
@RowVersions image
AS
UPDATE Snail
SET Snail.Status = Statuses.Value
FROM Snail
INNER JOIN GetTableInt(@SnailIDs) SnailIDs
ON Snail.SnailID = SnailIDs.Value
INNER JOIN GetTableVarchar(@Statuses) Statuses
ON SnailIDs.RowID = Statuses.RowID
INNER JOIN GetTableRowversion(@RowVersions) RowVersions
ON Statuses.RowID = RowVersions.RowID
WHERE TSEQUAL(Snail.RowVersion, RowVersions.Value)

RETURN @@ERROR

private void AddSnails(DataTable snails)
{
SQLVarcharListCreator species = new SQLVarcharListCreator();
SQLDatetimeListCreator dOB = new SQLDatetimeListCreator();
SQLNvarcharListCreator location = new SQLNvarcharListCreator();
SQLBitListCreator isMale = new SQLBitListCreator();
SQLMoneyListCreator value = new SQLMoneyListCreator();

foreach(DataRow row in snails.Rows)
{
species.AddValue(row["Species"]);
dOB.AddValue(row["DOB"]);
location.AddValue(row["Location"]);
isMale.AddValue(row["IsMale"]);
value.AddValue(row["Value"]);
}

// A command is explicitly created here
// instead of using SQLHelper as we need to be
// able to set the CommandTimeout property
// big enough to insert 1 000 000 records.
using (SqlConnection cnn = new SqlConnection(CONN_STRING))
{
cnn.Open();
SqlCommand comInst = new SqlCommand(“AddSnails”, cnn);
comInst.CommandType = CommandType.StoredProcedure;
comInst.CommandTimeout = 10000;

comInst.Parameters.Add(“@Species”, species.GetListAndReset());
comInst.Parameters.Add(“@DOB”, dOB.GetListAndReset());
comInst.Parameters.Add(“@Location”, location.GetListAndReset());
comInst.Parameters.Add(“@IsMale”, isMale.GetListAndReset());
comInst.Parameters.Add(“@Value”, value.GetListAndReset());
comInst.ExecuteNonQuery();
}
}

ALTER PROCEDURE dbo.AddSnails
(
@Species image,
@DOB image,
@Location image,
@IsMale image,
@Value image
)
AS
INSERT INTO Snail (Species, DOB, Location, IsMale, Value)
SELECT Species.Value, DOB.Value,
Location.Value, IsMale.Value, Value.Value
FROM GetTableMoney(@value) Value
INNER JOIN GetTableBit(@isMale) IsMale ON Value.RowID = IsMale.RowID
INNER JOIN GetTableVarchar(@species) Species
ON IsMale.RowID = Species.RowID
INNER JOIN GetTableDateTime(@dOB) DOB ON Species.RowID = DOB.RowID
INNER JOIN GetTableNvarchar(@location) Location
ON DOB.RowID = Location.RowID

RETURN @@ERROR