inline SQL to stored procedure

[Mike P]

I'm going about converting all of my inline SQL in my C# application
into stored procedures. I have this method below that is running an SQL
statement until a field changes. Is it a good idea to convert this to a
stored procedure, and if so should it be easy to do?

DBResult dbrChangeCCState;
string strViewAttemptedPurchase;

strViewAttemptedPurchase = "SELECT STATUS FROM ATTEMPTEDPURCHASE
WHERE SESSIONID = " + Convert.ToInt64(strSessionID) + " AND PURCHASEID =
" + Convert.ToInt64(strPurchaseID);

SqlConnection objConnection = new
SqlConnection(ConfigurationSettings.AppSettings["strConnectMcCallumTest"
]);
//SqlConnection objConnection = new
SqlConnection(ConfigurationSettings.AppSettings["strConnectMcCallumTest"
]);
SqlCommand objCommand = new SqlCommand(strViewAttemptedPurchase,
objConnection);

try
{
objConnection.Open();
SqlDataReader objDataReader = null;
objDataReader = objCommand.ExecuteReader();

if (objDataReader.Read() == true)
{
intNewStatus = Convert.ToInt32(objDataReader["STATUS"]);
}

DateTime dtmStart = DateTime.Now;
TimeSpan tsTimeout = new TimeSpan (0, 1, 0); // One minute

while ((intNewStatus == 0) || (intNewStatus == 1))
{
objDataReader.Close();
objDataReader = objCommand.ExecuteReader();

if (objDataReader.Read() == true)
{
intNewStatus = Convert.ToInt32(objDataReader["STATUS"]);
}

if (DateTime.Now-dtmStart > tsTimeout)
{
break;
}
}

dbrChangeCCState = DBResult.Valid;
}


Thanks,

Mike

 

[Nicholas Paldino [.NET/C# MVP]]

Mike,

It would be incredibly easy, it would seem. You would just need two
parameters, one for the session id and one for the purchase id. They would
be of type bigint. Other than that, you wouldn't have to do much to change
your code.

Also, you dont have to do this:

intNewStatus = Convert.ToInt32(objDataReader["STATUS"]);

Rather, you can do this:

intNewStatus = (int) objDataReader["STATUS"];

Assuming that the STATUS field is of type int. It's not necessary, and
makes for more work.

Hope this helps.

 

[Guest]

Write the whole lot in SQL ... you don't look like you've got any code that
updates UI there... could the whole lot be done as one SP, including the
loop..?

 

[James Curran]

Actually, writing the whole thing as a stored proc turned out easier
than I thought (and I learned a couple things about SQL along the way....)

Create PROCEDURE TestStatus
@SessionID bigint,
@PurchaseID bigint
AS

SET NOCOUNT ON

DECLARE @dtStop datetime
DECLARE @intNewStatus integer

Set @intNewStatus = 0 --- initialize to a "keep looping value"
SET @dtStop = DateAdd("mi", 1, GetDate()) -- set to one minute from now.

while (@dtStop > GetDate()) and (@intNewStatus = 0 or @intNewStatus =1)
begin

SELECT @intNewStatus = STATUS FROM ATTEMPTEDPURCHASE
WHERE SESSIONID = @SessionID AND PURCHASEID = @PurchaseID

waitfor delay '00:00:01'
end

return @intNewStatus
GO


The "waitfor delay" line isn't necessary, but it limits the select to being
run only once a second during the minute you are waiting for the status to
change. Otherwise it will query the table several thousand times during
that minute , (1,600,000+ times on my 2.6Ghz P4) while using all available
CPU cycles.

--
Truth,
James Curran
Home: www.noveltheory.com Work: www.njtheater.com
Blog: www.honestillusion.com Day Job: www.partsearch.com
(note new day job!)

I'm going about converting all of my inline SQL in my C# application
into stored procedures. I have this method below that is running an SQL
statement until a field changes. Is it a good idea to convert this to a
stored procedure, and if so should it be easy to do?

DBResult dbrChangeCCState;
string strViewAttemptedPurchase;

strViewAttemptedPurchase = "SELECT STATUS FROM ATTEMPTEDPURCHASE
WHERE SESSIONID = " + Convert.ToInt64(strSessionID) + " AND PURCHASEID =
" + Convert.ToInt64(strPurchaseID);

SqlConnection objConnection = new
SqlConnection(ConfigurationSettings.AppSettings["strConnectMcCallumTest"
]);
//SqlConnection objConnection = new
SqlConnection(ConfigurationSettings.AppSettings["strConnectMcCallumTest"
]);
SqlCommand objCommand = new SqlCommand(strViewAttemptedPurchase,
objConnection);

try
{
objConnection.Open();
SqlDataReader objDataReader = null;
objDataReader = objCommand.ExecuteReader();

if (objDataReader.Read() == true)
{
intNewStatus = Convert.ToInt32(objDataReader["STATUS"]);
}

DateTime dtmStart = DateTime.Now;
TimeSpan tsTimeout = new TimeSpan (0, 1, 0); // One minute

while ((intNewStatus == 0) || (intNewStatus == 1))
{
objDataReader.Close();
objDataReader = objCommand.ExecuteReader();

if (objDataReader.Read() == true)
{
intNewStatus = Convert.ToInt32(objDataReader["STATUS"]);
}

if (DateTime.Now-dtmStart > tsTimeout)
{
break;
}
}

dbrChangeCCState = DBResult.Valid;
}


Thanks,

Mike

 

[Bob Grommes]

Better to have used parameters in the first place (see below). Once you've
done that it's easy to turn it in to an SP. Just put the SQL in the SP and
change the command string to the name of the SP, and set the CommandType to
StoredProcedure.

Since you're just returning one value, I also changed the code to
ExecuteScalar(), which gets rid of a lot of code. If you stay with the
DataReader you can also consider:

intNewStatus = (int)objDataReader["STATUS"];
// or
intNewStatus = objDataReader.GetInt32(0);

--Bob

DBResult dbrChangeCCState;
SqlConnection objConnection = new
SqlConnection(ConfigurationSettings.AppSettings["strConnectMcCallumTest"]);
SqlCommand objCommand = new SqlCommand("SELECT Status FROM AttemptedPurchase
WHERE SessionID = @SessionID AND PurchaseID = @PurchaseID",objConnection);

// Following assumes the fields are really BigInts.

objCommand.Parameters.Add("@SessionId",SqlDbType.BigInt).Value =
Convert.ToInt64(strSessionID);
objCommand.Parameters.Add("@PurchaseId",SqlDbType.BigInt).Value =
Convert.ToInt64(strPurchaseID);

try
{
objConnection.Open();
intNewStatus = (int)objCommand.ExecuteScalar();
DateTime dtmStart = DateTime.Now;
TimeSpan tsTimeout = new TimeSpan (0, 1, 0); // One minute

while ((intNewStatus == 0) || (intNewStatus == 1))
{
intNewStatus = (int)objCommand.ExecuteScalar();

if (DateTime.Now-dtmStart > tsTimeout)
{
break;
}

// where's your catch and/or finally block??
}

dbrChangeCCState = DBResult.Valid;
}