New Topic/Question
Reply
I've written a little utility that runs a CMD.EXE + command that writes data to a log file, then my program reads this file, splits the data into variables and inserts a record into a database, finally I truncate the log file so that repeated data isn't inserted into my database (is there another way to achieve this?). At the moment it is very hit and miss, I can get 10 - 15 records added then I receive an error: 'The process cannot access the file as it is in use by another process'
This is my timer elapsed event method that runs the command line logger
static void myTimer_Elapsed(object sender, ElapsedEventArgs e)
{
// build a command string to change directory to and execute log2300 utility
string command =
string.Format(@"/c cd C:\msys\1.0\home\Administrator\open2300\ & log2300 C:\WeatherLog.txt open2300-dist.conf");
// launch CMD.EXE and pass the command argument
try
{
ProcessStartInfo cmdRun = new ProcessStartInfo();
cmdRun.FileName = "CMD.EXE";
cmdRun.Arguments = command;
Process.Start(cmdRun);
cmdRun.CreateNoWindow = true;
}
catch(Exception Ex)
{
Console.WriteLine(Ex.Message);
}
Next I pass the log file to a class that parses the data and adds to database:
static void convertData()
/* this method creates a ParseLogFile object
and runs its methods to parse and insert
data to a database */
{
ParseLogFile parseData = new ParseLogFile();
string logLocation = @"C:\WeatherLog.txt";
try
{
parseData.ParseLog(logLocation);
parseData.AddtoDatabase();
}
catch (Exception Ex)
{
Console.WriteLine(Ex.Message);
}
}
Next are the class methods for the parseData object
public void ParseLog(string logFile)
{
this.logFile = logFile;
// use a stream reader to open and read log file
using (StreamReader readLog = new StreamReader(this.logFile))
{
string line;
while ((line = readLog.ReadLine()) != null)
{
// split log string into individual data/columns
string[] splitData = line.Split(' ');
// convert split string into variables with suitable data types
DateTime.TryParse(splitData[1] + " " + splitData[2], out date);
double.TryParse(splitData[3], out indoorTemp);
double.TryParse(splitData[4], out outdoorTemp);
double.TryParse(splitData[5], out dewPoint);
double.TryParse(splitData[6], out rel_humidity_indoor);
double.TryParse(splitData[7], out rel_humidity_outdoor);
double.TryParse(splitData[8], out windSpeed);
double.TryParse(splitData[9], out windDirectionDegrees);
windDirectionText = splitData[10];
double.TryParse(splitData[11], out windChill);
double.TryParse(splitData[12], out rain1h);
double.TryParse(splitData[13], out rain24h);
double.TryParse(splitData[14], out totalRain);
double.TryParse(splitData[15], out relative_pressure);
tendency = splitData[16];
forecast = splitData[17];
}
}
try
{
using (FileStream f = File.Open(this.logFile, FileMode.Truncate))
{
Console.WriteLine("Logfile reset");
}
}
catch (IOException iEx)
{
Console.WriteLine("Problem accessing file: {0}", iEx.Message);
}
}
public void AddtoDatabase()
{
// create an SQL Server connection
using (SqlConnection dataConnection = new SqlConnection())
{
// build a connection string using a string builder object
// open a database connection
try
{
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.DataSource = "WEATHERSTATION";
builder.InitialCatalog = "Weather";
builder.IntegratedSecurity = true;
dataConnection.ConnectionString = builder.ConnectionString;
dataConnection.Open();
// insert record into database table
SqlCommand dataCommand = new SqlCommand();
dataCommand.Connection = dataConnection;
dataCommand.Parameters.AddWithValue("@date", date);
dataCommand.Parameters.AddWithValue("@indoor_temp", indoorTemp);
dataCommand.Parameters.AddWithValue("@outdoor_temp", outdoorTemp);
dataCommand.Parameters.AddWithValue("@dewpoint", dewPoint);
dataCommand.Parameters.AddWithValue("@rel_humidity_indoor", rel_humidity_indoor);
dataCommand.Parameters.AddWithValue("@rel_humidity_outdoor", rel_humidity_outdoor);
dataCommand.Parameters.AddWithValue("@wind_speed", windSpeed);
dataCommand.Parameters.AddWithValue("@wind_direction_degrees", windDirectionDegrees);
dataCommand.Parameters.AddWithValue("@wind_direction_text", windDirectionText);
dataCommand.Parameters.AddWithValue("@wind_chill", windChill);
dataCommand.Parameters.AddWithValue("@rain1h", rain1h);
dataCommand.Parameters.AddWithValue("@rain24h", rain24h);
dataCommand.Parameters.AddWithValue("@rain_total", totalRain);
dataCommand.Parameters.AddWithValue("@relative_pressure", relative_pressure);
dataCommand.Parameters.AddWithValue("@tendency", tendency);
dataCommand.Parameters.AddWithValue("@forecast", forecast);
dataCommand.CommandType = CommandType.Text;
dataCommand.CommandText =
"INSERT INTO"
+ " dbo.WeatherData(date, indoor_temp, outdoor_temp, dewpoint," +
"rel_humidity_indoor, rel_humidity_outdoor, wind_speed, wind_direction_degrees," +
"wind_direction_text, wind_chill, rain1h, rain24h, rain_total, relative_pressure," +
"tendency, forecast)"
+ "VALUES " +
"(@date, @indoor_temp, @outdoor_temp, @dewpoint, @rel_humidity_indoor," +
"@rel_humidity_outdoor, @wind_speed, @wind_direction_degrees," +
"@wind_direction_text, @wind_chill, @rain1h, @rain24h, @rain_total," +
"@relative_pressure, @tendency, @forecast)";
;
dataCommand.ExecuteNonQuery();
}
// handle any database exceptions
catch (SqlException dBeX)
{
Console.WriteLine(dBeX.Message);
}
}
}
}
If the file access problem is down to the command line utility and the File.Truncate clashing, is there a way to only insert new records to the database instead of the entire log file each time?
Many thanks,
Mike
MultiQuote
| 