This is a function that I have used in almost every application. ExecuteScalar is fast and for returning a single value, almost unbeatable. The function is extremely simple. Pass your SQL command string and your connection string and it returns an object, a single value. What kind of value? About anything you like that represents a valid SQL field. Lets take a look at the actual function...
public static object GetObject(string connStr, string sqlCmd)
{
object obj = null; // Return Value
SqlConnection m_SqlCn = new SqlConnection(connStr);
SqlCommand m_SqlCommand = new SqlCommand(sqlCmd,m_SqlCn);
try
{ m_SqlCommand.Connection.Open();
obj = m_SqlCommand.ExecuteScalar();
} // end try
catch (Exception e)
{ string Er = "Error in GetObject()-> " + e.ToString();
throw new Exception(Er);
}
finally
{ m_SqlCommand.Dispose();
m_SqlConnection.Close();
}
return obj;
}
Now that we have the function, what's it good for. I have a site on which I have a list of over 900 forms and 30 software downloads. The name, description, etc. is listed in a datagrid. The user selects the desired item and we either load it into Acrobat or start the download. But before we do, we link through a common page and advance a counter based on the database Id. Then using the Id, we grab the filename using the get object function. Looks something like...
if (Request.Params["W"]!=null)
{
Id=Convert.ToInt32(Request.Params["W"]);
sQry="UPDATE Forms SET DloadCount=DloadCount+1 WHERE Id='{0}'";
if(CDb.SqlOperation(string.Format(sQry,Id), CDb.DsnWebDB)==true)
{
sQry="Select FormName from Forms WHERE Id='{0}'";
obj= CDb.GetObject(string.Format(sQry,Id),CDb.DsnWebDB);
if (!Convert.IsDBNull(obj))
{
wPath = @"http://Forms.yyyz.Net/" + Convert.ToString(obj);
Response.Redirect(wPath);
}
}
}
Steps:
1) We start by grabbing the Id of the form from Params
2) SQry is the SQL Command string to update our counter
3) Update the Counter - SqlOperation can be found in our other articles
4) SQry now becomes the string to get our FormName (filename.pdf)
5) Execute GetObject and return the 'obj' variable
6) Make sure that the returned object isn't null and do the link
We also use this function to count records (return value is cast to an int)
and total column values (return value cast to a double) - it all depends on the
SQL string that you send and the value that you expect (and convert to) when
returned.
Top 