Database Actions that Drive Me Nuts

There are so many actions while writing software that you have to do over and over and over... again.

Things like opening database connections, filling a dataset or data table with data from the database, executing a SQL command and retrieving a scalar value from a database. 99% of the time I write database driven apps, and I have to do these things all the time. They are so boring and repetitive!!!

These small functions help me make these action tolerable, and quite simple to do:

There is a small function that these functions call, called GetConnectionString. This basically gets the connection string that you set in your Web.Config file.

public static string GetConnectionString()

{

return System.Configuration.ConfigurationManager.AppSettings["sqlConnectionString"];

}

Simplifying the ExecuteScalar Method...

public static String ExecuteScalar(String lvCommand)

{

// If a record is found, this will return the first column of the first row of the found data

String lvReturn = "";

// Setup your connection to your database

SqlConnection cn = new SqlConnection(GetConnectionString());

// Create a SQLCommand object

SqlCommand lvSQL = new SqlCommand(lvCommand, cn);

try

{

// Open a connection to your database

cn.Open();

// Execute your query

lvReturn = lvSQL.ExecuteScalar().ToString();

}

catch (Exception ex)

{

// If there is an error set lvReturn to ""

lvReturn = "";

}

finally

{

// Whatever happens, check to see if the connection is still open and if it is, close it

if (cn.State != ConnectionState.Closed)

{

cn.Close();

}

}

// Return the data that was found or the empty string is none

return lvReturn;

}

To use this function call it like this...

String lvCommand = "Select AdminStatus From YourTable Where UserName = 'SomeName'";

String lvAdminStatus = ExecuteScalar(lvCommand);

It’s that simple. No having to create your connection, create your sqlcommand object, open your connection, execute your command and close your connection every time you want to use the ExecuteScalar method.