//Insert statement
public void Insert(LogRecord log)
{
string query = "INSERT `log_collection` SET `DateStamp`=@dateStamp, `Duration`=@duration, `ClientAddress`=@clientAddress, `SquidReturnCode`=@squidReturnCode, `HTTPReturnCode`=@httpReturnCode, `Bytes`=@bytes, `RequestMethod`=@requestMethod, `URL`=@url, `RFC931`=@rfc931, `HierarchyCode`=@hierarchyCode, `Type`=@type";
//Open connection
if (this.OpenConnection() == true)
{
//create mysql command
MySqlCommand cmd = new MySqlCommand();
//Assign the query using CommandText
cmd.CommandText = query;
//Assign the connection using Connection
cmd.Connection = connection;
// Parametized queries
cmd.Parameters.AddWithValue("@dateStamp", log.dateStamp);
cmd.Parameters.AddWithValue("@duration", log.duration);
cmd.Parameters.AddWithValue("@clientAddress", log.clientAddress);
cmd.Parameters.AddWithValue("@squidReturnCode", log.squidReturnCode);
cmd.Parameters.AddWithValue("@httpReturnCode", log.httpReturnCode);
cmd.Parameters.AddWithValue("@bytes", log.bytes);
cmd.Parameters.AddWithValue("@requestMethod", log.requestMethod);
cmd.Parameters.AddWithValue("@url", log.url);
cmd.Parameters.AddWithValue("@rfc931", log.rfc931);
cmd.Parameters.AddWithValue("@hierarchyCode", log.hierarchyCode);
cmd.Parameters.AddWithValue("@type", log.type);
//Execute command
cmd.ExecuteNonQuery();
//close connection
this.CloseConnection();
}
}
public static void addLog(string[] logs)
{
// Create the object here to save processing time!
MysqlFactory dbCon = new MysqlFactory();
int line = 1;
foreach (String str in logs)
{
// Sanitize the data firstly - squid inserts multiple spaces in the first column *ack*
var temp = Regex.Replace(str, @"\s+", " ").Split(' ');
if (temp.Count() != 10)
{
Console.WriteLine("ERROR: Malformed line in the proxy log - please see http://wiki.squid-cache.org/Features/LogFormat‎ for proper format on line " + line);
break;
}
else
{
LogRecord record = new LogRecord();
// convert unix timestamp to native DateTime type and then convert to string in format appropriate for mysql server DateTime data type.
record.dateStamp = HelperMethods.UnixTimeStampToDateTime(Convert.ToDouble(temp[0])).ToString("yyyy-MM-dd hh:mm:ss");
record.duration = Int32.Parse(temp[1]);
record.clientAddress = temp[2];
record.squidReturnCode = temp[3].Split('/')[0];
record.httpReturnCode = temp[3].Split('/')[1];
record.bytes = Int32.Parse(temp[4]);
record.requestMethod = temp[5];
record.url = temp[6];
record.rfc931 = temp[7];
record.hierarchyCode = temp[8];
record.type = temp[9];
// http://www.bytechaser.com/en/articles/ckcwh8nsyt/display-progress-bar-in-console-application-in-c.aspx
dbCon.Insert(record);
}
line++;
}
}