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); }