public static object ExecuteScalarOnDB(string sSQL, string s_ConnectionString) { System.Data.Odbc.OdbcConnection QConnection = null; System.Data.Odbc.OdbcCommand QCommand = null; try { QConnection = new System.Data.Odbc.OdbcConnection(s_ConnectionString); QCommand = new System.Data.Odbc.OdbcCommand(sSQL, QConnection); QConnection.Open(); return(QCommand.ExecuteScalar()); } finally { if (QCommand != null) { QCommand.Dispose(); } QCommand = null; if (QConnection != null && QConnection.State != System.Data.ConnectionState.Closed) { QConnection.Close(); } if (QConnection != null) { QConnection.Dispose(); } QConnection = null; } }
void AddID(String UserID, String UserName, string IDtype) { string strPath = HttpContext.Current.Request.PhysicalApplicationPath + "LineID.csv";// "C:\\Test.csv"; System.IO.FileInfo file; FileStream fs; StreamWriter sw; //CHECK LineID.csv exist & create lineID.csv if (File.Exists(strPath) == false) { file = new FileInfo(strPath); fs = new FileStream(strPath, FileMode.Append, FileAccess.Write); sw = new StreamWriter(fs, Encoding.Default); sw.Write("LineID,Name,type,CreateTime", true); sw.Write(Environment.NewLine, true); sw.Close( ); } string csvStr = ""; System.Data.Odbc.OdbcConnection csvCn; System.Data.Odbc.OdbcCommand csvCmd; System.Data.Odbc.OdbcDataReader csvRes; csvCn = new System.Data.Odbc.OdbcConnection(@"Driver={Microsoft Text Driver (*.txt; *.csv)};extensions=csv,txt;DBQ=" + HttpContext.Current.Request.PhysicalApplicationPath + ";"); csvCn.Open( ); csvStr = @"SELECT Name FROM LineID.csv WHERE LineID = '" + UserID + "'"; csvCmd = new System.Data.Odbc.OdbcCommand(csvStr, csvCn); var tmp = csvCmd.ExecuteScalar( ); if (tmp == null) { //add id file = new FileInfo(strPath); fs = new FileStream(strPath, FileMode.Append, FileAccess.Write); sw = new StreamWriter(fs, Encoding.Default); sw.Write(UserID + "," + UserName + "," + IDtype + "," + DateTime.Now.ToString("yyyyMMdd_HHmmssfff"), true); sw.Write(Environment.NewLine, true); sw.Close( ); } else if (tmp.ToString( ) != UserName.ToString( ) && IDtype == "UserID") { //add id file = new FileInfo(strPath); fs = new FileStream(strPath, FileMode.Append, FileAccess.Write); sw = new StreamWriter(fs, Encoding.Default); sw.Write(UserID + "," + UserName + "," + IDtype + "," + DateTime.Now.ToString("yyyyMMdd_HHmmssfff"), true); sw.Write(Environment.NewLine, true); sw.Close( ); } else { } csvCn.Close( ); }
public int DesmaterializaAvaliacao(cAvaliacao obj) { string sql; try { sql = "INSERT INTO avaliacao(id_pessoa, data, idade, peso, altura, nivel_atividade, gordura, vo2, "; sql += " desc_atividade, observacoes, fc_max_obtida, fc_min_obtida, fc_media_obtida, fc_basal, tmb) "; sql += " values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; sqlConn = new System.Data.Odbc.OdbcConnection(connString); sqlConn.Open(); sqlCmd = new System.Data.Odbc.OdbcCommand(sql, sqlConn); sqlCmd.Parameters.Add("", System.Data.Odbc.OdbcType.Int).Value = obj.id_pessoa; sqlCmd.Parameters.Add("", System.Data.Odbc.OdbcType.DateTime).Value = obj.data_hora; sqlCmd.Parameters.Add("", System.Data.Odbc.OdbcType.Int).Value = obj.idade; sqlCmd.Parameters.Add("", System.Data.Odbc.OdbcType.Double).Value = obj.peso; sqlCmd.Parameters.Add("", System.Data.Odbc.OdbcType.Double).Value = obj.altura; sqlCmd.Parameters.Add("", System.Data.Odbc.OdbcType.Int).Value = obj.nivel_atividade; sqlCmd.Parameters.Add("", System.Data.Odbc.OdbcType.Double).Value = obj.gordura; sqlCmd.Parameters.Add("", System.Data.Odbc.OdbcType.Double).Value = obj.vo2; sqlCmd.Parameters.Add("", System.Data.Odbc.OdbcType.VarChar, 255).Value = obj.atividade; sqlCmd.Parameters.Add("", System.Data.Odbc.OdbcType.Text).Value = obj.observacoes; sqlCmd.Parameters.Add("", System.Data.Odbc.OdbcType.Int).Value = obj.fc_max; sqlCmd.Parameters.Add("", System.Data.Odbc.OdbcType.Int).Value = obj.fc_min; sqlCmd.Parameters.Add("", System.Data.Odbc.OdbcType.Int).Value = obj.fc_med; sqlCmd.Parameters.Add("", System.Data.Odbc.OdbcType.Int).Value = obj.fc_basal; sqlCmd.Parameters.Add("", System.Data.Odbc.OdbcType.Int).Value = obj.tmb; if (sqlCmd.ExecuteNonQuery() > 0) { sql = "select @@identity"; sqlCmd = new System.Data.Odbc.OdbcCommand(sql, sqlConn); int id = (int)sqlCmd.ExecuteScalar(); sqlConn.Close(); return(id); } else { sqlConn.Close(); return(-1); } } catch (Exception Ex) { return(-1); } }
/// <summary> /// 使用离线数据库连接器,无需使用Open方法,获取数据库当前时间 /// </summary> public override DateTime GetDateTimeNow() { DateTime ret = DateTime.Now; using (System.Data.Odbc.OdbcConnection con = new System.Data.Odbc.OdbcConnection(this.ConnectionText)) { using (System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand("select now()", con)) { con.Open(); ret = Convert.ToDateTime(cmd.ExecuteScalar()); con.Close(); } } return ret; }
/// <summary> /// 使用离线数据库连接器,无需使用Open方法,获取数据库当前时间 /// </summary> public override DateTime GetDateTimeNow() { DateTime ret = DateTime.Now; using (System.Data.Odbc.OdbcConnection con = new System.Data.Odbc.OdbcConnection(this.ConnectionText)) { using (System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand("select now()", con)) { con.Open(); ret = Convert.ToDateTime(cmd.ExecuteScalar()); con.Close(); } } return(ret); }
private static void loadRangeRule() { System.Data.Odbc.OdbcConnection cn = new System.Data.Odbc.OdbcConnection(Comm.DB2.Db2.db2ConnectionStr); System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand(); System.Data.Odbc.OdbcDataReader rd; cmd.Connection = cn; int num = 0; try { cn.Open(); cmd.CommandText = "select count(*) from tblVDValidRange"; num = System.Convert.ToInt32(cmd.ExecuteScalar()); rangeDatas = new RangeData[num]; cmd.CommandText = "select type,maxvalue,minvalue from tblVDValidRange"; rd = cmd.ExecuteReader(); int i = 0; while (rd.Read()) { try { rangeDatas[i++] = new RangeData(System.Convert.ToString(rd[0]).Trim(), System.Convert.ToInt32(rd[1]), System.Convert.ToChar(rd[2])); } catch (Exception ex1) { RemoteInterface.ConsoleServer.WriteLine(ex1.Message + ex1.StackTrace); } //Value1[i] = System.Convert.ToInt32(rd[1]); //type2[i] = System.Convert.ToChar(rd[2]); //Value2[i] = System.Convert.ToInt32(rd[3]); } rd.Close(); } catch (Exception ex) { RemoteInterface.ConsoleServer.WriteLine(ex.Message + ex.StackTrace); } finally { cn.Close(); } }
public static int getAlarmId() { System.Data.Odbc.OdbcConnection cn = new System.Data.Odbc.OdbcConnection(Global.Db2ConnectionString); System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand("SELECT NEXTVAL FOR DB2INST1.SEQ_ALARM FROM SYSIBM.SYSDUMMY1 "); cmd.Connection = cn; try { cn.Open(); return System.Convert.ToInt32( cmd.ExecuteScalar()); } catch (Exception ex) { ConsoleServer.WriteLine(ex.Message+","+ex.StackTrace); throw ex; } finally { try { cn.Close(); } catch { ;} } }
public static Host.Event.EventMode getEventMode(int alarmClass) { System.Data.Odbc.OdbcConnection cn = new System.Data.Odbc.OdbcConnection(Global.Db2ConnectionString); System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand("SELECT login_mode From tblSysAlarmConfig where alarmClass="+alarmClass); cmd.Connection = cn; try { cn.Open(); return ( Host.Event.EventMode)System.Convert.ToInt32(cmd.ExecuteScalar()); } catch (Exception ex) { ConsoleServer.WriteLine(ex.Message + "," + ex.StackTrace); throw ex; } finally { try { cn.Close(); } catch { ;} } }
/// <summary> /// 同步批号 /// </summary> /// <param name="dset"></param> /// <param name="db"></param> /// <returns></returns> public static string SaveKcph(DataSet dset, RelationalDatabase db) { try { DataTable tb = dset.Tables[0]; string ssql = ""; List <System.String[]> listUpdate = new List <System.String[]>(); List <System.String[]> listInsert = new List <System.String[]>(); for (int nrow = 0; nrow <= tb.Rows.Count - 1; nrow++) { #region 变量 DataRow row = tb.Rows[nrow]; ParameterEx[] parameters = new ParameterEx[13]; string _sdeptid = HisFunctions.ConvertNewhisidToOldHisid(HisFunctions.DataMapType.JC_DEPT_PROPERTY, Convertor.IsNull(row["deptid"], ""), db); if (_sdeptid == "") { throw new Exception("没有找到科室匹配信息"); } parameters[0].Text = "yfh"; parameters[0].DataType = System.Data.DbType.String; parameters[0].Value = _sdeptid; string yppc = _sdeptid.ToString() + row["cjid"].ToString(); // yppm=left('0000000000', string temp = "0000000000"; int N = yppc.Length; if (N < 10) { yppc = temp.Substring(0, 10 - N) + yppc; } yppc = "10" + yppc; parameters[1].Text = "yppc"; parameters[1].DataType = System.Data.DbType.String; parameters[1].Value = yppc; string cjid = row["cjid"].ToString(); parameters[2].Text = "ypbm"; parameters[2].Value = row["cjid"]; parameters[3].Text = "ypmc"; parameters[3].DataType = System.Data.DbType.String; parameters[3].Value = row["s_yppm"].ToString().Replace("'", ""); parameters[4].Text = "czgg"; parameters[4].DataType = System.Data.DbType.String; parameters[4].Value = row["s_ypgg"].ToString().Replace("'", ""); parameters[5].Text = "dw"; parameters[5].DataType = System.Data.DbType.String; parameters[5].Value = Convertor.IsNull(row["s_zxdw"], ""); parameters[6].Text = "cjbm"; parameters[6].Value = row["sccj"].ToString(); parameters[7].Text = "grdj"; parameters[7].Value = row["grdj"].ToString(); parameters[8].Text = "pfj"; parameters[8].Value = row["pfj"].ToString(); parameters[9].Text = "lsj"; parameters[9].Value = row["lsj"].ToString(); parameters[10].Text = "ccrq"; parameters[10].DataType = System.Data.DbType.String; parameters[10].Value = Convert.ToDateTime(row["djsj"]).ToString("yyyy-MM-dd HH:mm:ss"); parameters[11].Text = "sxrq"; parameters[11].DataType = System.Data.DbType.String; parameters[11].Value = Convert.ToDateTime(row["ypxq"]).ToString("yyyy-MM-dd HH:mm:ss"); parameters[12].Text = "sl"; parameters[12].Value = Convertor.IsNull(row["kcl"], ""); #endregion #region 插入语句 ssql = "insert into yk_kcb("; for (int i = 0; i <= parameters.Length - 1; i++) { ssql = ssql + parameters[i].Text + ","; } ssql = ssql.Substring(0, ssql.Length - 1) + ")"; ssql = ssql + "values("; for (int i = 0; i <= parameters.Length - 1; i++) { if (parameters[i].DataType != null) { ssql = ssql + "'" + TrasenHIS.DAL.BaseDal.GetEncodingStringToInforMix(parameters[i].Value.ToString()) + "',";//TrasenHIS.DAL.BaseDal.GetEncodingStringToInforMix(parameters[i].Value.ToString()) } else { ssql = ssql + parameters[i].Value + ","; } } ssql = ssql.Substring(0, ssql.Length - 1) + ")"; //用于记录生成的SQL语句,医嘱主键,操作类型。。在一面的事务中一次性处理 System.String[] str_insert = { row["id"].ToString(), _sdeptid, yppc, cjid, ssql, "add" }; listInsert.Add(str_insert); #endregion #region 更新语句 ssql = "update yk_kcb set "; for (int i = 0; i <= parameters.Length - 1; i++) { if (parameters[i].DataType != null) { ssql = ssql + " " + parameters[i].Text + "= '" + TrasenHIS.DAL.BaseDal.GetEncodingStringToInforMix(parameters[i].Value.ToString()) + "',";//TrasenHIS.DAL.BaseDal.GetEncodingStringToInforMix(parameters[i].Value.ToString()) } else { ssql = ssql + " " + parameters[i].Text + "= " + parameters[i].Value + ","; } } ssql = ssql.Substring(0, ssql.Length - 1) + ""; ssql = ssql + " where yfh='" + _sdeptid + "' and yppc='" + yppc + "' and ypbm=" + cjid + ""; //用于记录生成的SQL语句,医嘱主键,操作类型。。在一面的事务中一次性处理 System.String[] str_update = { row["id"].ToString(), _sdeptid, yppc, cjid, ssql, "update" }; listUpdate.Add(str_update); #endregion } #region 老HIS事务处理 System.Data.Odbc.OdbcConnection connection = new System.Data.Odbc.OdbcConnection(DAL.BaseDal.oldhis_constr); System.Data.Odbc.OdbcTransaction tx = null; System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand(); try { connection.Open(); cmd.Connection = connection; tx = connection.BeginTransaction(); cmd.Transaction = tx; for (int i = 0; i <= listInsert.Count - 1; i++) { //插入和更新老HIS ssql = "select * from yk_kcb where yfh='" + listInsert[i][1].ToString() + "' and yppc='" + listInsert[i][2].ToString() + "' and ypbm=" + listInsert[i][3].ToString() + ""; cmd.CommandText = ssql; object o = cmd.ExecuteScalar(); if (o != null) { cmd.CommandText = listUpdate[i][4]; } else { cmd.CommandText = listInsert[i][4]; } cmd.ExecuteNonQuery(); } tx.Commit(); cmd.Dispose(); connection.Close(); } catch (Exception ex) { //事务回滚 tx.Rollback(); cmd.Dispose(); connection.Close(); tx.Dispose(); throw new Exception(ex.Message); } #endregion #region 回填新HIS记录 for (int nrow = 0; nrow <= tb.Rows.Count - 1; nrow++) { ssql = "update EVENTLOG set FINISH=1 ,FINISH_DATE='" + DateTime.Now.ToString() + "' where EVENT='KCBH' AND BIZID='" + tb.Rows[nrow]["ID"].ToString() + "'"; db.DoCommand(ssql); } #endregion System.String[] str = { "0", "保存成功" }; return(HisFunctions.GetResponseString("SaveKcph", str)); } catch (Exception err) { throw err; } }
public void load_vd_travel_mapping_table() { string[][] newTable; System.Data.Odbc.OdbcConnection cn = new System.Data.Odbc.OdbcConnection(Global.Db2ConnectionString); try { System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand(string.Format("select count(*) from tblGroupUnitVD where unitID='{0}' ", unitid)); cmd.Connection = cn; cn.Open(); newTable = new string[System.Convert.ToInt32(cmd.ExecuteScalar())][]; cmd.CommandText = string.Format("select devicename1,DeviceName2,DeviceName3,DeviceName4 from tblGroupUnitVD where unitid='{0}' order by priority", unitid); System.Data.Odbc.OdbcDataReader rd = cmd.ExecuteReader(); int inx = 0; while (rd.Read()) { System.Collections.ArrayList ary = new System.Collections.ArrayList(); string devName1, devName2, devName3, devName4; devName1 = System.Convert.ToString(rd[0]); if (devName1 != "") ary.Add(devName1); devName2 = System.Convert.ToString(rd[1]); if (devName2 != "") ary.Add(devName2); devName3 = System.Convert.ToString(rd[2]); if (devName3 != "") ary.Add(devName3); devName4 = System.Convert.ToString(rd[3]); if (devName4 != "") ary.Add(devName4); newTable[inx] = new string[ary.Count]; for (int i = 0; i < ary.Count; i++) newTable[inx][i] = ary[i].ToString(); inx++; } vd_travel_mapping_table = newTable; } catch (Exception ex) { RemoteInterface.ConsoleServer.WriteLine(ex.Message + ex.StackTrace); } finally { cn.Close(); } }
void getGID() { System.DateTime dt = System.DateTime.Today; string sql = "select gid from tblavitimegroup where month={0}"; System.Data.Odbc.OdbcConnection cn = new System.Data.Odbc.OdbcConnection(Global.Db2ConnectionString); System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand(string.Format(sql,DateTime.Now.Month)); try { cn.Open(); cmd.Connection = cn; _gid = System.Convert.ToInt32(cmd.ExecuteScalar()); } catch (Exception ex) { ConsoleServer.WriteLine(ex.Message + "," + ex.StackTrace); } finally { cn.Close(); } }
void getClassId() { System.DateTime dt = System.DateTime.Today; string sql="select classid from tblAnnualDate where datecode='{0}'"; System.Data.Odbc.OdbcConnection cn = new System.Data.Odbc.OdbcConnection(Global.Db2ConnectionString); System.Data.Odbc.OdbcCommand cmd=new System.Data.Odbc.OdbcCommand(string.Format(sql,DbCmdServer.getTimeStampString(dt))); try { cn.Open(); cmd.Connection = cn; _classid = System.Convert.ToInt32(cmd.ExecuteScalar()); } catch (Exception ex) { ConsoleServer.WriteLine(ex.Message + "," + ex.StackTrace); } finally { cn.Close(); } // System.Data.Odbc.OdbcDataReader }
public void ReloadDeviceLocation() { System.Data.Odbc.OdbcConnection cn = new System.Data.Odbc.OdbcConnection(Global.Db2ConnectionString); System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand("select location from tbldeviceconfig where devicename='"+this.deviceName+"'"); cmd.Connection = cn; try { cn.Open(); this.location= cmd.ExecuteScalar().ToString(); } catch (Exception ex) { throw ex; } finally { cn.Close(); } }
public int AddPerformanceCubeResult(PerformanceCubeResult result) { if (result.FramesPerSecond > 65) return -1; int databaseId = -1; System.Data.Odbc.OdbcConnection conn = createSQLConnection(); System.Data.Odbc.OdbcTransaction transaction = null; try { string mySelectQuery = string.Format(@"SELECT `id` , `FramesPerSecond` FROM `PerformanceGLCubeResults` WHERE `DBDeviceId` ={0} AND `NumberTriangles` ={1} AND `isMonoTouch` ={2}", result.DeviceDatabaseId, result.NumberOfTriangles, result.IsMonoTouch ? 1 : 0); conn.Open(); System.Data.Odbc.OdbcCommand mySelectCommand = new System.Data.Odbc.OdbcCommand(mySelectQuery, conn); System.Data.Odbc.OdbcDataReader reader = mySelectCommand.ExecuteReader(); if (reader.Read()) { System.Data.Odbc.OdbcCommand myUpdateCommand = new System.Data.Odbc.OdbcCommand(mySelectQuery, conn); databaseId = Convert.ToInt32(reader["id"]); string myUpdateQuery = string.Format(@"UPDATE `billholmes54`.`PerformanceGLCubeResults` SET `FramesPerSecond` = '{1}' WHERE `PerformanceGLCubeResults`.`id` ={0} AND `PerformanceGLCubeResults`.`FramesPerSecond` <{1} LIMIT 1 ;", databaseId, result.FramesPerSecond); myUpdateCommand.CommandText = myUpdateQuery; myUpdateCommand.ExecuteNonQuery(); } else { transaction = conn.BeginTransaction(); System.Data.Odbc.OdbcCommand myInsertCommand = new System.Data.Odbc.OdbcCommand(mySelectQuery, conn, transaction); string myInsertQuery = string.Format(@"INSERT INTO `billholmes54`.`PerformanceGLCubeResults` (`id`, `DBDeviceId`, `NumberTriangles`, `FramesPerSecond`, `isMonoTouch`) VALUES (NULL, '{0}', '{1}', '{2}', '{3}');", result.DeviceDatabaseId, result.NumberOfTriangles, result.FramesPerSecond, result.IsMonoTouch ? "1" : "0"); myInsertCommand.CommandText = myInsertQuery; myInsertCommand.ExecuteNonQuery(); myInsertCommand.CommandText = "select last_insert_id();"; databaseId = Convert.ToInt32(myInsertCommand.ExecuteScalar()); transaction.Commit(); } } catch (Exception) { if (transaction != null) transaction.Rollback(); throw; } finally { conn.Close(); } return databaseId; }
public int AddDevice(FullDeviceInfo deviceInfo) { int databaseId = -1; System.Data.Odbc.OdbcConnection conn = createSQLConnection(); System.Data.Odbc.OdbcTransaction transaction = null; try { string selectQuery = string.Format("SELECT `DatabaseId` FROM `PerformanceAppDeviceInfo` WHERE `UniqueId` = '{0}'", deviceInfo.UniqueId); conn.Open(); System.Data.Odbc.OdbcCommand selectCommand = new System.Data.Odbc.OdbcCommand(selectQuery, conn); try { databaseId = (int)selectCommand.ExecuteScalar(); } catch (Exception) { } if (databaseId == -1) { string myInsertQuery = string.Format(@"INSERT INTO `billholmes54`.`PerformanceAppDeviceInfo` (`DatabaseId`, `UniqueId`, `SystemName`, `ModelName`, `UIIdion`, `SpecificHWVersion`, `OSName`, `OSVersion`, `OwnerName`) VALUES (NULL, '{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}');", deviceInfo.UniqueId, deviceInfo.SystemName, deviceInfo.ModelName, deviceInfo.UIIdion, deviceInfo.SpecificHWVersion, deviceInfo.OSName, deviceInfo.OSVersion, deviceInfo.OwnerName); transaction = conn.BeginTransaction(); System.Data.Odbc.OdbcCommand insertCommand = new System.Data.Odbc.OdbcCommand(myInsertQuery, conn, transaction); insertCommand.ExecuteNonQuery(); insertCommand.CommandText = "select last_insert_id();"; databaseId = Convert.ToInt32(insertCommand.ExecuteScalar()); transaction.Commit(); } else { string myInsertQuery = string.Format(@"UPDATE `billholmes54`.`PerformanceAppDeviceInfo` SET `SystemName` = '{0}', `ModelName` = '{1}', `UIIdion` = '{2}', `SpecificHWVersion` = '{3}', `OSName` = '{4}', `OSVersion` = '{5}', `OwnerName` = '{6}' WHERE `PerformanceAppDeviceInfo`.`DatabaseId` = {7} LIMIT 1;", deviceInfo.SystemName, deviceInfo.ModelName, deviceInfo.UIIdion, deviceInfo.SpecificHWVersion, deviceInfo.OSName, deviceInfo.OSVersion, deviceInfo.OwnerName, databaseId); System.Data.Odbc.OdbcCommand updateCommand = new System.Data.Odbc.OdbcCommand(myInsertQuery, conn); updateCommand.ExecuteNonQuery(); } } catch (Exception) { if (transaction != null) transaction.Rollback(); throw; } finally { conn.Close(); } return databaseId; }
public int AddPerformanceMatrixTestResult(MatrixTestResult result) { int databaseId = -1; System.Data.Odbc.OdbcConnection conn = createSQLConnection(); System.Data.Odbc.OdbcTransaction transaction = null; try { string mySelectQuery = string.Format("SELECT `id` FROM `PerformanceMatrixTestResults` WHERE `DBDeviceId` ={0} AND `isMonoTouch` ={1}", result.DeviceDatabaseId, result.IsMonoTouch ? 1 : 0); conn.Open(); System.Data.Odbc.OdbcCommand mySelectCommand = new System.Data.Odbc.OdbcCommand(mySelectQuery, conn); System.Data.Odbc.OdbcDataReader reader = mySelectCommand.ExecuteReader(); if (reader.Read()) { System.Data.Odbc.OdbcCommand myUpdateCommand = new System.Data.Odbc.OdbcCommand(mySelectQuery, conn); databaseId = Convert.ToInt32(reader["id"]); string myUpdateQuery = string.Format(@" update `billholmes54`.`PerformanceMatrixTestResults` set `CSTestResult` = case when `CSTestResult` < {1} then '{1}' else CSTestResult end, `CTestResult` = case when `CTestResult` < {2} then '{2}' else CTestResult end, `BLASTestResult` = case when `BLASTestResult` < {3} then '{3}' else BLASTestResult end where id = {0}", databaseId, result.CSTestResult, result.CTestResult, result.BLASTestResult); myUpdateCommand.CommandText = myUpdateQuery; myUpdateCommand.ExecuteNonQuery(); } else { transaction = conn.BeginTransaction(); System.Data.Odbc.OdbcCommand myInsertCommand = new System.Data.Odbc.OdbcCommand(mySelectQuery, conn, transaction); string myInsertQuery = string.Format(@"INSERT INTO `billholmes54`.`PerformanceMatrixTestResults` (`id`, `DBDeviceId`, `CSTestResult`, `CTestResult`, `BLASTestResult`, `isMonoTouch`) VALUES (NULL, '{0}', '{1}', '{2}', '{3}', '{4}');", result.DeviceDatabaseId, result.CSTestResult, result.CTestResult, result.BLASTestResult, result.IsMonoTouch ? "1" : "0"); myInsertCommand.CommandText = myInsertQuery; myInsertCommand.ExecuteNonQuery(); myInsertCommand.CommandText = "select last_insert_id();"; databaseId = Convert.ToInt32(myInsertCommand.ExecuteScalar()); transaction.Commit(); } } catch (Exception) { if (transaction != null) transaction.Rollback(); throw; } finally { conn.Close(); } return databaseId; }
public static object ExecuteScalarOnDB(string sSQL, string s_ConnectionString) { System.Data.Odbc.OdbcConnection QConnection = null; System.Data.Odbc.OdbcCommand QCommand = null; try { QConnection = new System.Data.Odbc.OdbcConnection(s_ConnectionString); QCommand = new System.Data.Odbc.OdbcCommand(sSQL, QConnection); QConnection.Open(); return QCommand.ExecuteScalar(); } finally { if (QCommand != null) QCommand.Dispose(); QCommand = null; if (QConnection != null && QConnection.State != System.Data.ConnectionState.Closed) QConnection.Close(); if (QConnection != null) QConnection.Dispose(); QConnection = null; } }
public static int getProcessTimeByAlarmClass(int alarmclass) { System.Data.Odbc.OdbcConnection cn = new System.Data.Odbc.OdbcConnection(Global.Db2ConnectionString); System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand("select avgtime from TBLSYSALARMCONFIG where alarmclass="+alarmclass); cmd.Connection = cn; try { cn.Open(); return System.Convert.ToInt32(cmd.ExecuteScalar()); } catch (Exception ex) { ConsoleServer.WriteLine(ex.Message + "," + ex.StackTrace); throw ex; } finally { try { cn.Close(); } catch { ;} } }
private bool m_Commit(object oDeviceData, string strCustomState) { bool result = false; GLPBase report = oDeviceData as GLPBase; string outgoingMessage = string.Empty; //to nie odpowiedz wiec, sprawdzamy połaczenie if (report == null) { byte[] arrToSend = UTF8Encoding.UTF8.GetBytes(".ttc\r\n"); try { SendToDevice(arrToSend, "tcp", true); UpdateProgress(2, 3, "Connection OK", ""); } catch { UpdateProgress(1, 3, "Waiting for connection...", ""); } } else if (report.TerminalStatus != 3) //poleczenie ok brak garmina { UpdateProgress(2, 3, "Connection OK but terminal not connected", ""); } else if (report.TerminalStatus == 3) //polaczenie i garmin ok { outgoingMessage = ".tt " + ChatTextID + ",\"" + ChatText + "\""; if (CannedResponseList.Count > 0) { int newFlag = 1; ChatReader reader = new ChatReader(); foreach (ChatResponseBag response in reader.GetAllResponses()) { uint iResponseID = (uint)response.ID; if (CannedResponseList.Contains(iResponseID)) { outgoingMessage += ",\"" + response.ResponseText + "\""; } newFlag = 0; } if (newFlag == 1) { uint[] CannedResponseID = new uint[CannedResponseList.Count]; for (int i = 0; i < CannedResponseList.Count; i++) { CannedResponseID[i] = CannedResponseList[i]; } string ConnStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString; System.Data.Odbc.OdbcConnection conn = new System.Data.Odbc.OdbcConnection(); conn.ConnectionString = ConnStr; try { conn.Open(); for (int i = 0; i < CannedResponseList.Count; i++) { System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand( @"SELECT body FROM chat_profile_message WHERE chat_profile_message_id = " + CannedResponseID[i] + ";" , conn); outgoingMessage += ",\"" + cmd.ExecuteScalar().ToString() + "\""; } } catch (Exception ex) { } finally { conn.Close(); } } } outgoingMessage += "\r\n"; byte[] arrToSend = UTF8Encoding.UTF8.GetBytes(outgoingMessage); try { SendToDevice(arrToSend, "tcp", true); UpdateProgress(3, 3, "Completed", ""); Thread.Sleep(2000); result = true; } catch { UpdateProgress(1, 3, "Waiting for connection...", ""); } } return(result); }
void initArray() { System.Data.Odbc.OdbcConnection cn = new System.Data.Odbc.OdbcConnection(RemoteInterface.DbCmdServer.getDbConnectStr()); System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand("select count(*) from tbldeviceConfig where location='T' and lineid='N6' and device_type='VD' "); try { cmd.Connection = cn; cn.Open(); int count = System.Convert.ToInt32(cmd.ExecuteScalar()); vd_devNames = new string[count]; vd_dirs = new int[count]; vd_mile_ks = new int[count]; vd_mile_ms = new int[count]; } catch (Exception ex) { Console.WriteLine(ex.Message + ex.StackTrace); } finally { cn.Close(); } }
public void LoadSectionTravelTimeWeight() { System.Data.Odbc.OdbcConnection cn = new System.Data.Odbc.OdbcConnection(Global.Db2ConnectionString); string sql = ""; System.Data.Odbc.OdbcCommand cmd =new System.Data.Odbc.OdbcCommand(); System.Data.Odbc.OdbcDataReader rd; cmd.Connection=cn; try { cn.Open(); lock (aryNormalDayWeight) { int cnt = 0; aryNormalDayWeight.Clear(); System.DateTime dt=DateTime.Now; dt=dt.AddHours(-dt.Hour).AddMinutes(-dt.Minute).AddSeconds(-dt.Second); cmd.CommandText = string.Format("select count(*) from tblSectionTravelTimeWeightHoliday where sectionid='{0}' and date='{1}'",this.sectionId, DbCmdServer.getTimeStampString(dt)); cnt =System.Convert.ToInt32( cmd.ExecuteScalar()); if (cnt == 0) { sql = "select SectionId,Start_Timestamp,End_Timestamp,WeightedId,Vd_Percentage,Avi_Percentage,Etc_Percentage,History_Percentage from tblSectionTravelTimeWeight where sectionid='{0}' and holiday={1} order by Start_Timestamp,WeightedId "; cmd.CommandText = string.Format(sql, this.sectionId, 0); // normalday } else { sql = "select SectionId,Start_Timestamp,End_Timestamp,WeightedId,Vd_Percentage,Avi_Percentage,Etc_Percentage,History_Percentage from tblSectionTravelTimeWeightHoliday where sectionid='{0}' and date='{1}' order by Start_Timestamp,WeightedId "; cmd.CommandText = string.Format(sql, this.sectionId, DbCmdServer.getTimeStampString(dt)); // normalday } rd = cmd.ExecuteReader(); dt=new DateTime(); SectionTravelTimeWeightSegnment seg = null; while (rd.Read()) { DateTime beg, end; int weightid,vd_percent, avi_percent, etc_percent, his_percent; beg = System.Convert.ToDateTime(rd[1]); end = System.Convert.ToDateTime(rd[2]); weightid = System.Convert.ToInt32(rd[3]); vd_percent = System.Convert.ToInt32(rd[4]); avi_percent = System.Convert.ToInt32(rd[5]); etc_percent = System.Convert.ToInt32(rd[6]); his_percent = System.Convert.ToInt32(rd[7]); if (beg != dt) { dt = beg; seg = new SectionTravelTimeWeightSegnment(beg, end); aryNormalDayWeight.Add(seg); } if (weightid < 16) seg.AddWeight(weightid, vd_percent, avi_percent, etc_percent, his_percent); } rd.Close(); aryNormalDayWeight.Sort(); } /* lock (aryHoliDayWeight) { aryHoliDayWeight.Clear(); sql = "select SectionId,Start_Timestamp,End_Timestamp,WeightedId,Vd_Percentage,Avi_Percentage,Etc_Percentage,History_Percentage,Holiday from tblSectionTravelTime where sectionid='{0}' and holiday={1} order by Start_Timestamp,WeightedId "; cmd.CommandText = string.Format(sql, this.sectionId, 1); // holiday rd = cmd.ExecuteReader(); DateTime dt = new DateTime(); SectionTravelTimeWeightSegnment seg = null; while (rd.Read()) { DateTime beg, end; int weightid, vd_percent, avi_percent, etc_percent, his_percent; beg = System.Convert.ToDateTime(rd[1]); end = System.Convert.ToDateTime(rd[2]); weightid = System.Convert.ToInt32(rd[3]); vd_percent = System.Convert.ToInt32(rd[4]); avi_percent = System.Convert.ToInt32(rd[5]); etc_percent = System.Convert.ToInt32(rd[6]); his_percent = System.Convert.ToInt32(rd[7]); if (beg != dt) { dt = beg; seg = new SectionTravelTimeWeightSegnment(beg, end); aryHoliDayWeight.Add(seg); } if(weightid<16) seg.AddWeight(weightid, vd_percent, avi_percent, etc_percent, his_percent); } rd.Close(); aryHoliDayWeight.Sort(); } */ } catch (Exception ex) { ConsoleServer.WriteLine(ex.Message + "," + ex.StackTrace); } finally { cn.Close(); } }
private static void loadSpeedOccupancyRule() { System.Data.Odbc.OdbcConnection cn = new System.Data.Odbc.OdbcConnection(Comm.DB2.Db2.db2ConnectionStr); System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand(); System.Data.Odbc.OdbcDataReader rd; cmd.Connection = cn; int num = 0; try { cn.Open(); cmd.CommandText = "select count(*) from tblVDInvalidPrinciple"; num = System.Convert.ToInt32(cmd.ExecuteScalar()); Value1 = new int[num]; Value2 = new int[num]; type1 = new char[num]; type2 = new char[num]; cmd.CommandText = "select type1,value1,type2,value2 from tblVDInvalidPrinciple"; rd = cmd.ExecuteReader(); int i = 0; while (rd.Read()) { type1[i] = System.Convert.ToChar(rd[0]); Value1[i] = System.Convert.ToInt32(rd[1]); type2[i] = System.Convert.ToChar(rd[2]); Value2[i] = System.Convert.ToInt32(rd[3]); i++; } rd.Close(); } catch (Exception ex) { type1 = new char[1]; type2 = new char[1]; Value1 = new int[1]; Value2 = new int[1]; type1[0] = 'O'; type2[0] = 'S'; Value1[0] = 40; Value2[0] = 40; RemoteInterface.ConsoleServer.WriteLine(ex.Message + ex.StackTrace); } finally { cn.Close(); } }