public static void ConvertNamedParametersToPositionalParameters(this OdbcCommand command)
{
//1. Find all occurrences parameters references in the SQL statement (such as @MyParameter).
//2. Find the corresponding parameter in the command's parameters list.
//3. Add the found parameter to the newParameters list and replace the parameter reference in the SQL with a question mark (?).
//4. Replace the command's parameters list with the newParameters list.
var newParameters = new List <OdbcParameter>();
command.CommandText = Regex.Replace(command.CommandText, "(@\\w*)", match =>
{
var parameter = command.Parameters.OfType <OdbcParameter>().FirstOrDefault(a => a.ParameterName == match.Groups[1].Value);
if (parameter != null)
{
var parameterIndex = newParameters.Count;
var newParameter = command.CreateParameter();
newParameter.OdbcType = parameter.OdbcType;
newParameter.ParameterName = "@parameter" + parameterIndex.ToString();
newParameter.Value = parameter.Value;
newParameters.Add(newParameter);
}
return("?");
});
command.Parameters.Clear();
command.Parameters.AddRange(newParameters.ToArray());
}
public OdbcParameter makeStringParam(OdbcCommand cmd, string fieldname, string value)
{
OdbcParameter param = cmd.CreateParameter();
param.ParameterName = fieldname;
param.DbType = System.Data.DbType.String;
param.Value = value;
return(param);
}
public void AddReturnParameter(OdbcCommand cmd, string parameterName, DbType dbType)
{
OdbcParameter dbParameter = cmd.CreateParameter();
dbParameter.DbType = dbType;
dbParameter.ParameterName = parameterName;
dbParameter.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(dbParameter);
}
private static OdbcParameter CreateParameter(DbParam parameter, OdbcCommand com)
{
var p = com.CreateParameter();
p.ParameterName = parameter.ParameterName;
p.DbType = parameter.DbType;
p.Value = parameter.Value;
return(p);
}
public void AddInParameter(OdbcCommand cmd, string parameterName, DbType dbType, object value)
{
OdbcParameter dbParameter = cmd.CreateParameter();
dbParameter.DbType = dbType;
dbParameter.ParameterName = parameterName;
dbParameter.Value = value;
dbParameter.Direction = ParameterDirection.Input;
cmd.Parameters.Add(dbParameter);
}
public void AddOutParameter(OdbcCommand cmd, string parameterName, DbType dbType, int size)
{
OdbcParameter dbParameter = cmd.CreateParameter();
dbParameter.DbType = dbType;
dbParameter.ParameterName = parameterName;
dbParameter.Size = size;
dbParameter.Direction = ParameterDirection.Output;
cmd.Parameters.Add(dbParameter);
}
public static int ExecuteProcedure(OdbcConnection connection, string procedureName, string[] paramNames, OdbcType[] types, object[] values, List <object> outParams = null)
{
int retcode = -1;
StringBuilder sb = new StringBuilder();
for (int i = 0; i < paramNames.Length; i++)
{
sb.Append("?");
if (i < paramNames.Length - 1)
{
sb.Append(",");
}
}
string sql = "{? = CALL " + procedureName + " (" + sb.ToString() + ")}";
using (OdbcCommand command = new OdbcCommand(sql, connection))
{
command.CommandType = CommandType.StoredProcedure;
OdbcParameter returnValue = new OdbcParameter("@return_value", OdbcType.Int);
returnValue.Direction = ParameterDirection.ReturnValue;
command.Parameters.Add(returnValue);
for (int i = 0; i < paramNames.Length; i++)
{
OdbcParameter param = command.CreateParameter();
param.ParameterName = "@" + paramNames[i];
param.OdbcType = types[i];
if (values[i] == null)
{
param.Direction = ParameterDirection.Output;
}
else
{
param.Value = values[i];
}
command.Parameters.Add(param);
}
command.ExecuteNonQuery();
retcode = (int)command.Parameters["@return_value"].Value;
for (int i = 0; i < paramNames.Length; i++)
{
if (values[i] == null && outParams != null)
{
outParams.Add(command.Parameters["@" + paramNames[i]].Value);
}
}
}
return(retcode);
}
public IDbDataParameter CreateParameter(IDbCommand command)
{
OdbcCommand SQLcommand = (OdbcCommand)command;
return(SQLcommand.CreateParameter());
}
private bool CreateODBC(string DataDir, string DeviceName, string ConnectString)
{
OdbcCommand sql_cmd = new OdbcCommand();
OdbcTransaction trans;
OdbcConnection con = new OdbcConnection(ConnectString);
DataSet dds = new DataSet();
List <string> UploadList = new List <string>();
List <string> SendCmd = new List <string>();
int count = 0;
string table = "";
string LastDate = "";
string DBLocation = SQLiteDB.Text.Replace(@"\\", @"\\\");
System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
if (bDbInitialized == false)
{
//Create db file when SQLite enable
if (Mode == (int)DB.SQLite) //SQLite DB not define
{
if (!File.Exists(DBLocation + @"\" + mInfo.DBName))
{
SQLiteConnection.CreateFile(DBLocation + @"\" + mInfo.DBName);
}
}
//Create db table
for (int i = 0; i < TitleArray.Length; i++)
{
string tmptable = "";
if (i == 0)
{
tmptable = string.Format("CREATE TABLE {0}([{1}][{2}]({3}) NULL,", mInfo.TableName, TitleArray[i], Dataformat[i], MAX_SIZE);
}
else if (i == TitleArray.Length - 1)
{
if (Dataformat[i].IndexOf("char") >= 0) //Format is char
{
tmptable = string.Format("[{0}][{1}]({2}) NULL);", TitleArray[i], Dataformat[i], MAX_SIZE, mInfo.TableName);
}
else
{
tmptable = string.Format("[{0}][{1}] NULL);", TitleArray[i], Dataformat[i]);
}
}
else
{
if (Dataformat[i].IndexOf("char") >= 0) //Format is char
{
tmptable = string.Format("[{0}][{1}]({2}) NULL,", TitleArray[i], Dataformat[i], MAX_SIZE);
}
else
{
tmptable = string.Format("[{0}][{1}] NULL,", TitleArray[i], Dataformat[i]);
}
}
table += tmptable;
}
}
try
{
con.Open();
if (bDbInitialized == false)
{
sql_cmd.CommandText = table;
sql_cmd.Connection = con;
sql_cmd.ExecuteNonQuery();
sql_cmd.Parameters.Add(sql_cmd.CreateParameter());
}
}
catch (Exception eee)
{
Console.Write(eee.Message);
if (eee.Message.ToString().IndexOf("already") < 0) //table or object already exists, ignore
{
MessageBox.Show(eee.Message, "SQL Server Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
}
sw.Stop();
sw.Reset();
if (bDbInitialized == false)
{
Console.Write("Done, Create data table, spend: " + sw.Elapsed.TotalMilliseconds.ToString() + "ms\n\r");
}
//DB table is created
bDbInitialized = true;
sw.Start();
//Read ini file to get the last upload time
string IniName = DataDir + @"\" + DeviceName + @".ini";
try
{
if (!File.Exists(IniName))
{ //Create ini file and set the last upload time
var mfile = File.Create(IniName);
LastDate = default(DateTime).ToString();
SQL_Data = 0;
mfile.Close();
}
else
{ //Read the record from the previous log collection
string[] Content = File.ReadAllLines(IniName);
if (Content.Count() == 0) //No data
{
LastDate = default(DateTime).ToString();
}
else
{
LastDate = Content[0];
if (this.InvokeRequired)
{
this.BeginInvoke(new DelegatePrintToList(this.PrintToList), DeviceName, LogType, Content[0], Col.LastTime, (int)PrintType.re_write);
this.BeginInvoke(new DelegatePrintToList(this.PrintToList), DeviceName, LogType, Content[1], Col.TotalData, (int)PrintType.re_write);
}
else
{
PrintToList(DeviceName, LogType, Content[0], (int)Col.LastTime, (int)PrintType.re_write);
PrintToList(DeviceName, LogType, Content[1], (int)Col.TotalData, (int)PrintType.re_write);
}
if (int.Parse(Content[1]) >= 0)
{
SQL_Data = int.Parse(Content[1]);
}
}
}
}
catch (OdbcException e)
{
Console.Write(e.Message);
MessageBox.Show(e.Message, "ODBC Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
//Collect all the data rows of csv file in the same path
var di = new DirectoryInfo(DataDir);
var directories = di.GetDirectories().OrderBy(d => d.CreationTime).Select(d => d.FullName).ToList(); //Sorting dir according to dir create time
foreach (string FileDir in directories)
{
di = new DirectoryInfo(FileDir);
var files = di.GetFiles().OrderBy(f => f.LastWriteTime).Select(f => f.FullName).ToList(); //Sorting file according to the last write time
foreach (string Csvfile in files)
{ //Check file format, if format is .csv, parsing the content of csv file
if (Csvfile.ToLower().IndexOf(".csv") > 0 && (DateTime.Compare(DateTime.Parse(LastDate), File.GetLastWriteTime(Csvfile)) < 0) &&
LastDate.IndexOf(File.GetLastWriteTime(Csvfile).ToString("yyyy/MM/dd HH:mm:ss")) < 0)
{
DateTime test1 = File.GetLastWriteTime(Csvfile);
bool test = DateTime.Equals(DateTime.Parse(LastDate), File.GetLastWriteTime(Csvfile));
//Process csv file then generate SQL command
SendCmd = TxtConvertToSQLite(Csvfile, mInfo.TableName, Path.GetFileNameWithoutExtension(DataDir).ToString(), ",", count, DeviceName);
UploadList.AddRange(SendCmd);
count++;
LastDate = File.GetLastWriteTime(Csvfile).ToString("yyyy/MM/dd HH:mm:ss");
}
}
}
sw.Stop();
Console.Write("Done, Marge data, spend: " + sw.Elapsed.TotalMilliseconds.ToString() + "ms\n\r");
sw.Reset();
sw.Start();
//Upload data to db
if (SQL_Data > 0)
{
try
{
count = 0;
trans = con.BeginTransaction();
foreach (string Upload in UploadList)
{
sql_cmd.CommandType = CommandType.Text;
sql_cmd.CommandText = Upload;
sql_cmd.Connection = con;
sql_cmd.Transaction = trans;
sql_cmd.ExecuteNonQuery();
count++;
}
trans.Commit();
//Update the last upload time
if (this.InvokeRequired)
{
//Console.Write(Path.GetFileNameWithoutExtension(DataDir).ToString());
this.BeginInvoke(new DelegatePrintToList(this.PrintToList), DeviceName, Path.GetFileNameWithoutExtension(DataDir).ToString(), SQL_Data.ToString(), Col.TotalData, (int)PrintType.re_write);
this.BeginInvoke(new DelegatePrintToList(this.PrintToList), DeviceName, Path.GetFileNameWithoutExtension(DataDir).ToString(), LastDate, Col.LastTime, (int)PrintType.re_write);
}
}
catch (Exception SQLEX)
{
//trans.Rollback(); // <-------------------
//throw; // <-------------------
Console.Write(SQLEX.Message);
MessageBox.Show(SQLEX.Message, "SQL Server Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
UploadList.Clear();
}
sw.Stop();
Console.Write("Done, Upload " + SQL_Data + " data, spend: " + sw.Elapsed.TotalMilliseconds.ToString() + "ms\r\n");
Console.Write("Done\r\n");
//Write the last upload time and data count into ini file
try
{
if (!File.Exists(IniName))
{
File.Create(IniName);
}
string[] Result = { LastDate, SQL_Data.ToString() };
File.WriteAllLines(IniName, Result);
//close connection
con.Close();
}
catch (Exception e)
{
Console.Write(e.Message);
MessageBox.Show(e.Message, "ODBC Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
return(true);
}
