I am trying to select rows from a SQL2000 database table and then write a random number back into each row. I have already looked into do it all in a SP but there are well documented limitations for the SQL RAND function when called in the same batch, so I need to somehow do in .Net what I already have working in classic ASP.
I can't get the UPDATE (part two section) to compile. I don't know how to call the stored procedure inside the 'foreach' loop or extract the SP parameters. I have it working in classic asp but am having a lot of trouble converting to .Net 2.0. Is the below even close to working?
// stored procedure to write externally generated random number value into database
PROCEDURE RandomizeLinks
@.L_ID int,
@.L_Rank int
AS
UPDATE Links SET L_Rank = @.L_Rank
WHERE (L_ID = @.L_ID)
// Part One select links that need random number inserted.
public DataTable GetRandLinks()
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString1A"].ConnectionString);
SqlCommand cmd = new SqlCommand("RandomizerSelect001", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
DataSet ds = new DataSet();
try
{
da.Fill(ds, "Random001");
return ds.Tables["Random001"];
}
catch
{ throw new ApplicationException("Data error"); }
}
// Part Two I need two write a random number back into each row
protected void Button1_Click(object sender, EventArgs e)
{
GetRandLinks();
int LinkID; // this generates unassigned local variable "LinkID' error
int LRank; // this generates unassigned local variable "LRank' error
SqlConnection con2 = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString1A"].ConnectionString);
SqlCommand cmd2 = new SqlCommand("RandomizeLinks", con2);
cmd2.CommandType = CommandType.StoredProcedure;
cmd2.Parameters.AddWithValue("@.L_ID", LinkID);
cmd2.Parameters.AddWithValue("@.L_Rank", LRank);
SqlDataAdapter da2 = new SqlDataAdapter();
int RowIncrement;
RowIncrement = 0;
DataTable dt = GetRandLinks();
foreach (DataRow row in dt.Rows)
{
System.Random myRandom = new System.Random();
int LinkRank = myRandom.Next(25, 250);
LRank = LinkRank;
da2.UpdateCommand = cmd2;
RowIncrement++;
}
}
Question - instead of looping back to the database server for every row, can I pull the data into a DataTable, and then on the .Net web server update each row with it random number using system.Random and then finally read the updated DataTable back to update the SQL Database table? And if so is any of the code I have written useful to that end?
|||
Most of the T-SQL limitations can be gotten around by feeding the rand function the right 4 digits of the newid function.
|||
How would I incorporate that into this stored procedure? I am working on a SQL 2000 server, company hasn't got the money for SQL2007. And is there a way to limit the random number range to > 25 and < 250?
PROCEDURE spA_Random001
AS
DECLARE @.L_ID int
DECLARE @.L_Rank int
DECLARE cur CURSOR FOR
SELECT L_ID , L_Rank
FROM tblLinkInfo_OLD2
OPEN cur;
FETCH NEXT FROM cur
INTO @.L_ID, @.L_Rank
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF (@.L_Rank > 10 AND @.L_Rank < 300)
UPDATE tblLinkInfo_OLD2 SET L_Rank = Convert(int, (L_ID)*RAND()) WHERE (L_ID = @.L_ID)
FETCH NEXT FROM cur
INTO @.L_ID, @.L_Rank
END
CLOSE cur;
DEALLOCATE cur;
|||
Thank you for the suggestion, it is an excellent solution for most applications however in my cash I need a strictly limit the random value to integers between 25 and 250. So far - pulling the table data into a DataSet, using the webserver resident System.Random to generate the random number and then writing the changes up to the SQL server seems to be the solution. I have been spoiled by the drag and drop GridViews and DataLists no I have to learn what is really going one. I am going to try to get th above solution working but I am beginning to think I am way of track.
