Copy and Paste Coding: SQL insert/update for C#, PHP, and Perl

The following examples are some dead-simple examples that come up all the time when I’m prototyping.  The following examples are the first ones that come to mind: CGI / DB connectivity and insertion.  Note the code provided does not apply best practices but is intended as a starting off point for creating more robust solutions.  I typically write this kind of code when I’m creating throwaway prototypes and then refactor these into more robust routines if I elect to use the code in production.

Copy Paste this Code!

But don’t ship it.

CGI/Perl/MySQL Copy/Paste Code for insertion into a DB:

use CGI;
use Mysql;
my $q = CGI->new;
print $q->header;
print $q->start_html("Thank you for your input!");

my $helpful = $q->param("helpful");
my $page  = $q->param("page");

# MySQL CONFIG VARIABLES
$host      = "hostname";
$database  = "dbname";
$tablename = "feedback";
$user      = "gguuss";
$pw        = "helpimtrappedinacodefactory";

# PERL MYSQL CONNECT
$connect = Mysql->connect($host, $database, $user, $pw);
if (($page =~ /.*'.*/) || ($helpful =~ /.*'.*/)){
  print "Error! Bad page or feedback value.";
}else{
    $myquery = qq|INSERT INTO $tablename (id, page, feedback, TIME) VALUES (DEFAULT,'$page','$helpful', NOW())|;
    # EXECUTE THE QUERY FUNCTION
    $execute = $connect->query($myquery);
    print "Thank you for your input!";
}
print $q->end_html;

C# SQL

Includes:

// includes for SQL
using System.Data.SqlClient;
using System.Data.Sql;
using System.Data.SqlTypes;

Defining a connection string

const string connString = "Data Source=hostname;Initial Catalog=metricscache;Integrated Security=False;user=sa;password=helpimtrappedinacodefactory"; 

A simple SqlCommand example that will perform an update

DateTime rangeDate = DateTime.Now;
rangeDate.AddMonths(-1 * numMonths);
SqlConnection metricscacheConn = new SqlConnection();
metricscacheConn.ConnectionString = connString;
try{
    metricscacheConn.Open();
    SqlCommand deleteRefreshedData = new SqlCommand("delete from devuemetrics where fromdate > @param1", metricscacheConn);
    SqlParameter p_rangeDate = new SqlParameter("@param1", System.Data.SqlDbType.DateTime);
    p_rangeDate.Value = rangeDate;
    deleteRefreshedData.Parameters.Add(p_rangeDate);
    deleteRefreshedData.ExecuteNonQuery();
} finally {
    metricscacheConn.Close();
}

A more complex select example using a parameterized SqlCommand and a stored procedure:

// Fresh connection
SqlConnection sourceConn = new SqlConnection();
sourceConn.ConnectionString = @"Data Source=devuemetrics01;Initial Catalog=DevUEMetrixMirror;Integrated Security=True; Connection Timeout=6000";

// new source connection for every query... not sure this is right way... 
if (getPageViews){
    sourceConn.Open();    

    try
    {
        SqlCommand DM_ResultsOverviewCommand =
            new SqlCommand("DM_ResultsOverview", sourceConn);

        // Create the SQL sproc
        DM_ResultsOverviewCommand.CommandType = System.Data.CommandType.StoredProcedure;
        DM_ResultsOverviewCommand.Parameters.Add("groupName", System.Data.SqlDbType.VarChar);
        DM_ResultsOverviewCommand.Parameters.Add("productName", System.Data.SqlDbType.VarChar);
        DM_ResultsOverviewCommand.Parameters.Add("detailLevel", System.Data.SqlDbType.VarChar);
        DM_ResultsOverviewCommand.Parameters.Add("startMonth", System.Data.SqlDbType.VarChar,6);
        DM_ResultsOverviewCommand.Parameters.Add("endMonth", System.Data.SqlDbType.VarChar,6);
        DM_ResultsOverviewCommand.Parameters.Add("export", System.Data.SqlDbType.Bit);

        // Set parameters within the sproc
        DM_ResultsOverviewCommand.Parameters[0].Value = productGroup;
        DM_ResultsOverviewCommand.Parameters[1].Value = productName;
        DM_ResultsOverviewCommand.Parameters[2].Value = "page";
        //DM_ResultsOverviewCommand.Parameters[3].Value = lastMonth.ToString("yyyyMM");
        DM_ResultsOverviewCommand.Parameters[3].Value = thisMonth.ToString("yyyyMM");
        DM_ResultsOverviewCommand.Parameters[4].Value = thisMonth.ToString("yyyyMM");
        DM_ResultsOverviewCommand.Parameters[5].Value = (1);

        SqlDataReader reader = null;
        //UtilWriteLine("Reading 30 days data From:  " + lastMonth.ToString("yyyyMM") + " To: " + thisMonth.ToString("yyyyMM"));
        UtilWriteLine("Reading 30 days data From:  " + thisMonth.ToString("yyyyMM") + " To: " + thisMonth.ToString("yyyyMM"), writer);        

        reader = DM_ResultsOverviewCommand.ExecuteReader();

        while (reader.Read())
        {
            Hashtable row = new Hashtable();
            row.Add("p_title", reader[0].ToString());
            row.Add("p_pageviews", reader[1].ToString());
            row.Add("p_rateavg", reader[2].ToString());
            row.Add("p_ratecnt", reader[3].ToString());
            row.Add("p_feedbackcnt", reader[4].ToString());
            row.Add("p_contentid", reader[5].ToString());
            row.Add("p_collection", reader[6].ToString());
            row.Add("p_assetid", reader[7].ToString());
            row.Add("p_writerName", reader[8].ToString());
            row.Add("p_editorName", reader[9].ToString());
            row.Add("p_managerName", reader[10].ToString());
            row.Add("p_UETeamName", reader[11].ToString());   
            row.Add("p_topicType", reader[12].ToString());
            row.Add("p_projectName", reader[13].ToString());
            row.Add("p_fromDate", thisMonth.ToString());
            row.Add("p_toDate", thisMonth.ToString());
            row.Add("p_rGroup", productName);            

            al.Add(row);
        }

        // A custom logger
        UtilWriteLine("done reading from: " + thisMonth + " to: " + thisMonth, writer);        
        System.Threading.Thread.Sleep(10000);
    }
    catch (Exception e)
    {
        UtilWriteLine(e.StackTrace, writer);
        failed = true;
    }
    finally
    {
        sourceConn.Close();
    }
}

A parametrized SELECT query using SqlCommand… bonus – a datetime constructor

// Want less than first day of month but greater than day before at midnight
// this is because the data is aggregated by day, each day set to midnight
// i.e. All Data from 05.24.2010 is recorded with a date of 2010-05-24 00:00:00
static DateTime GetFromDate(int numMonthsago)
{
    DateTime today = DateTime.Now;
    DateTime d = new DateTime(today.Year, today.Month, 1);
    d = d.AddMonths(-1 * numMonthsago);

    return d;
}

...

SqlConnection readConn = null;
try
{
    readConn = new SqlConnection();
    readConn.ConnectionString = isDevCenter ? connString : libraryConnString;
    readConn.Open();

    SqlCommand command = null;
    string strCommand = "select count(id) from referrerdetailswithvisits where [date] >= @param2 and [date] < @param3 and contentID = @param1" + (filterToExtraCIDs ? " and contentid in " + extraContentIds : "") + (specificLocale ? " and locale='" + locale + "'" : "");

    command = new SqlCommand(strCommand, readConn);

    SqlParameter pContentID = new SqlParameter("@param1", System.Data.SqlDbType.VarChar, 8);
    pContentID.Value = contentID;
    command.Parameters.Add(pContentID);

    SqlParameter pDate = new SqlParameter("@param2", System.Data.SqlDbType.DateTime);
    pDate.Value = GetFromDate(1 + monthsAgo);
    command.Parameters.Add(pDate);

    SqlParameter pToDate = new SqlParameter("@param3", System.Data.SqlDbType.DateTime);
    pToDate.Value = GetFromDate(0 + monthsAgo);
    command.Parameters.Add(pToDate);

    SqlDataReader reader = command.ExecuteReader();
    if (reader.Read())
    {
        Console.WriteLine("Page: " + GetWebPageTitleByCID(contentID) + " Views: " + reader[0].ToString());        
        csvWriter.WriteLine(GetWebPageTitleByCID(contentID) + "," + reader[0].ToString());
    }
}
finally
{
    readConn.Close();
}

PHP SQL connect and insert:

if ($s_ok == 1){
  // Connecting, selecting database
  $link = mysql_connect('hostname', 'username', 'helpimtrappedinacodefactory')
    or die('<response><sms>Could not connect: ' . mysql_error() . '<sms></response>');
  
  mysql_select_db('dbname') or die('<response><sms>Could not select database</sms></response>');
  
  // TODO: mysql_real_escape_string(
  mysql_query("insert into images(insertDate, uploadedBy, URL) values (NOW(), '$from', '$body')");
  
  // Closing connection
  mysql_close($link);
}

Happy hacking!