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!