/// <summary> /// 取消门诊发药状态 /// </summary> /// <param name="dset"></param> /// <param name="db"></param> /// <returns></returns> public static string SaveMzQxFyzt(DataSet dset, RelationalDatabase db) { try { DataTable tb = dset.Tables[0]; string ssql = ""; DataRow row = tb.Rows[0]; string BIZID = row["BIZID"].ToString(); string DH = row["DH"].ToString(); #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; cmd.CommandText = "update MZ_CFD_ZB set sfty='N',qrty='',tyr='', tyrq=null where dh='" + DH + "' and sftf='N'"; cmd.ExecuteNonQuery(); cmd.CommandText = "update MZ_CFD_CB set sfty='N',qrty='',tyr='' where dh='" + DH + "'"; 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='MZQXFYZT' AND BIZID='" + BIZID + "'"; db.DoCommand(ssql); } #endregion System.String[] str = { "0", "保存成功" }; return(HisFunctions.GetResponseString("SaveKcph", str)); } catch (Exception err) { throw err; } }
public bool DesmaterializaDado(cDados dado) { string sql; try { sql = "INSERT INTO dados(id_avaliacao, data_hora, freq_cardiaca) 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 = dado.id_avaliacao; sqlCmd.Parameters.Add("", System.Data.Odbc.OdbcType.DateTime).Value = dado.data; sqlCmd.Parameters.Add("", System.Data.Odbc.OdbcType.Int).Value = dado.fc; if (sqlCmd.ExecuteNonQuery() > 0) { sqlConn.Close(); return(true); } else { sqlConn.Close(); return(false); } } catch (Exception Ex) { return(false); } }
public bool DesmaterializaPessoa(cPessoa objPessoa) { string sql; try { sql = "INSERT INTO pessoa(nome, sexo, dt_nascimento) 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.VarChar, 255).Value = objPessoa.sNome; sqlCmd.Parameters.Add("", System.Data.Odbc.OdbcType.VarChar, 1).Value = objPessoa.sSexo; sqlCmd.Parameters.Add("", System.Data.Odbc.OdbcType.Date).Value = objPessoa.dtDataNasc; if (sqlCmd.ExecuteNonQuery() > 0) { sqlConn.Close(); return(true); } else { sqlConn.Close(); return(false); } } catch (Exception Ex) { return(false); } }
public string ExecuteNonQuery(string sQuery) { string sFuncName = "ExecuteNonQuery()"; string sConstr = ConfigurationManager.ConnectionStrings["adapterConnection"].ToString(); System.Data.Odbc.OdbcConnection oCon = new System.Data.Odbc.OdbcConnection(sConstr); System.Data.Odbc.OdbcCommand oCmd = new System.Data.Odbc.OdbcCommand(); try { oCon.Open(); oCmd.CommandType = CommandType.Text; oCmd.CommandText = sQuery; oCmd.Connection = oCon; oCmd.CommandTimeout = 120; oCmd.ExecuteNonQuery(); } catch (Exception ex) { if (p_iDebugMode == DEBUG_ON) { oLog.WriteToLogFile_Debug("Completed with ERROR", sFuncName); } oCon.Dispose(); throw new Exception(ex.Message); } return("SUCCESS"); }
protected void Button5_Click(object sender, EventArgs e) { string SqlStr = ""; System.Data.Odbc.OdbcConnection Cn; System.Data.Odbc.OdbcCommand Cmd; System.Data.Odbc.OdbcDataReader Res; string insertTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); Cn = new System.Data.Odbc.OdbcConnection(@"Driver={ODBC Driver 13 for SQL Server};Server=tcp:monkdb.database.windows.net,1433;Database=monkdb;Uid=monk;Pwd=!@#$qwer19;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;"); //SqlStr = @"INSERT INTO lineid (lineid,name,type,ENABLE,createTime,UPDATETime) VALUES (N'abcdefedfsdfef',N'蘇永昇',N'userid',1,N'" + insertTime + "',N'" + insertTime + "');"; //SqlStr = @"insert into Replymsg (keyValue,returnMSG,ENABLE,CreateTime,UPDATETime) values //(N'你是誰',N'我?你竟然不知道我是誰?其實我也不知道我是誰?要問邀我進來的那個白目仔。',1,'20200512_100000','20200512_100000' );"; //SqlStr = @"insert into Receivemsg ( MSG, fromUser, fromRoom ) values ( N'msgmsgmsg', 'userid', 'roomid' ); "; 接收訊息db // SqlStr = @"insert into Replymsg (keyValue,returnMSG,ENABLE,CreateTime,UPDATETime) values (N'白鳥',N'據傳他的奶茶是瀉藥中的勞斯萊斯!',1,'20200512_100000','20200512_100000' ); //insert into Replymsg (keyValue,returnMSG,ENABLE,CreateTime,UPDATETime) values (N'小號',N'其實sax比較帥,不然你問monk',1,'20200512_100000','20200512_100000' ); //insert into Replymsg (keyValue,returnMSG,ENABLE,CreateTime,UPDATETime) values (N'長號',N'老話一句,其實sax比較帥',1,'20200512_100000','20200512_100000' );"; Cmd = new System.Data.Odbc.OdbcCommand(SqlStr, Cn); try { Cn.Open( ); Cmd.ExecuteNonQuery( ); lbinsertMSG.Text = "insert ok"; } catch (Exception ex) { lbinsertMSG.Text = ex.ToString( ); } }
public static void ExecuteNonQueryOnDB(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(); QCommand.ExecuteNonQuery(); } 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 string ExecuteQuery(string sQuery, string sCompanyCode, System.Data.Odbc.OdbcParameter[] param) { string sFuncName = "ExecuteQuery()"; string sConstr = ConfigurationManager.ConnectionStrings["DBSSG"].ToString(); string[] sArray = sConstr.Split(';'); string sSplitCompany = sConstr.Split(';').Last(); string sSplit1 = sSplitCompany.Split('=').First(); string sCompanyGenerate = sSplit1 + "=" + sCompanyCode; sConstr = sArray[0] + ";" + sArray[1] + ";" + sArray[2] + ";" + sArray[3] + ";" + sCompanyGenerate; if (p_iDebugMode == DEBUG_ON) { oLog.WriteToLogFile_Debug("Connection String : " + sConstr, sFuncName); } System.Data.Odbc.OdbcConnection oCon = new System.Data.Odbc.OdbcConnection(sConstr); System.Data.Odbc.OdbcCommand oCmd = new System.Data.Odbc.OdbcCommand(); try { oCon.Open(); oCmd.CommandType = CommandType.Text; oCmd.CommandText = sQuery; if (p_iDebugMode == DEBUG_ON) { oLog.WriteToLogFile_Debug("SQL Query : " + sQuery, sFuncName); } if (p_iDebugMode == DEBUG_ON) { oLog.WriteToLogFile_Debug("Before adding Parameters", sFuncName); } foreach (var item in param) { oCmd.Parameters.Add(item); } if (p_iDebugMode == DEBUG_ON) { oLog.WriteToLogFile_Debug("After adding parameters", sFuncName); } oCmd.Connection = oCon; oCmd.CommandTimeout = 120; oCmd.ExecuteNonQuery(); } catch (Exception ex) { if (p_iDebugMode == DEBUG_ON) { oLog.WriteToLogFile_Debug("Completed with ERROR", sFuncName); } oCon.Dispose(); throw new Exception(ex.Message); } return("SUCCESS"); }
public void SendCmdImmediately(string sqlCmd) { System.Data.Odbc.OdbcConnection cn = new System.Data.Odbc.OdbcConnection(DbCmdServer.getDbConnectStr()); System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand(sqlCmd); cmd.Connection = cn; cn.Open(); cmd.ExecuteNonQuery(); cn.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); } }
private void button1_Click(object sender, EventArgs e) { if (comboBox1.SelectedItem != null) { if (textBox1.Text != "") { if (textBox2.Text != "") { connDsn = comboBox1.SelectedItem.ToString(); connUser = textBox1.Text; connPassword = textBox2.Text; connDsnName = "DSN=" + comboBox1.SelectedItem.ToString() + "; Uid=" + textBox1.Text + "; Pwd=" + textBox2.Text + ";"; using (System.Data.Odbc.OdbcConnection conn = new System.Data.Odbc.OdbcConnection(connDsnName)) { try { conn.Open(); System.Data.Odbc.OdbcCommand command = new System.Data.Odbc.OdbcCommand("select getDate()", conn); command.ExecuteNonQuery(); } catch (Exception ex) { MessageBox.Show("ODBC Conexión Fallida revise los datos"); Console.WriteLine("ODBC Connection test failed!!!!"); Console.WriteLine(ex.Message); conn.Close(); return; } MessageBox.Show("ODBC Conexión Satisfatoria"); Console.WriteLine("ODBC Connection test PASSED!"); conn.Close(); button3.Enabled = true; } } else { MessageBox.Show("Es necesario rellenar el campo de Password"); } } else { MessageBox.Show("Es necesario rellenar el campo de Usuario"); } } else { MessageBox.Show("Seleccione un OBDC"); } }
public bool Execute(System.Data.Odbc.OdbcConnection oConn, string SqlText, object[] SqlParams) { try { using (System.Data.Odbc.OdbcCommand oCmd = new System.Data.Odbc.OdbcCommand(SqlText, oConn)) { CreateSqlParam(oCmd, SqlParams); return(oCmd.ExecuteNonQuery() > 0 ? true : false); } } catch { return(false); } }
void AddMsg(String UserID, String RoomID, string msg) { string SqlStr = ""; System.Data.Odbc.OdbcConnection Cn; System.Data.Odbc.OdbcCommand Cmd; System.Data.Odbc.OdbcDataReader Res; string Time = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss.fff"); Cn = new System.Data.Odbc.OdbcConnection(@"Driver={ODBC Driver 13 for SQL Server};Server=tcp:monkdb.database.windows.net,1433;Database=monkdb;Uid=monk;Pwd=!@#$qwer19;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;"); Cn.Open( ); SqlStr = @"insert into Receivemsg ( MSG, fromUser, fromRoom ) values ( N'" + msg + "', '" + UserID + "', '" + RoomID + "' ); "; Cmd = new System.Data.Odbc.OdbcCommand(SqlStr, Cn); Cmd.ExecuteNonQuery( ); Cn.Close( ); }
/// <summary> /// Finalizar la aplicación. /// </summary> /// /// <returns>Resultado (S/N)</returns> public char FinConexion() { // Sólo a efectos de Test y formación. using (System.Data.Odbc.OdbcConnection cn = new System.Data.Odbc.OdbcConnection(Iptr.cadenadeconexion.ToString())) { // TEST acceso a BBDD. cn.Open(); string strSQL = "Update F05C001 Set F05cCodTer='CONN' Where F05cCodOpe='0000'"; using (System.Data.Odbc.OdbcCommand cb = new System.Data.Odbc.OdbcCommand(strSQL, cn)) { int aaa = cb.ExecuteNonQuery(); } } // Fin Test acceso a BBDD. return(System.Convert.ToChar(Iptr.finconexion())); }
protected void Button4_Click(object sender, EventArgs e) { string SqlStr = ""; System.Data.Odbc.OdbcConnection Cn; System.Data.Odbc.OdbcCommand Cmd; System.Data.Odbc.OdbcDataReader Res; Cn = new System.Data.Odbc.OdbcConnection(@"Driver={ODBC Driver 13 for SQL Server};Server=tcp:monkdb.database.windows.net,1433;Database=monkdb;Uid=monk;Pwd=!@#$qwer19;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;"); //SqlStr = @"CREATE TABLE lineID (SN INT IDENTITY PRIMARY KEY ,lineid nvarchar(50),Name nvarchar(50),type nvarchar(50),CreateTime nvarchar(50),UPDATETime nvarchar(50));"; //SqlStr = @"ALTER TABLE lineID ALTER COLUMN Name nvarchar(50)"; //drop table SqlStr = @"DROP TABLE Receivemsg;"; Cmd = new System.Data.Odbc.OdbcCommand(SqlStr, Cn); try { Cn.Open( ); Cmd.ExecuteNonQuery( ); Cn.Close( ); lbCreateMSG.Text = "刪除 LineID成功"; } catch (Exception ex) { lbCreateMSG.Text = ex.ToString( ); } //create table //SqlStr = @"CREATE TABLE lineID (SN INT IDENTITY PRIMARY KEY ,lineid nvarchar(50),Name nvarchar(50),type nvarchar(50),ENABLE int,CreateTime nvarchar(50),UPDATETime nvarchar(50));"; //SqlStr = @"CREATE TABLE Replymsg (SN INT IDENTITY PRIMARY KEY ,keyValue nvarchar(50),returnMSG nvarchar(200),ENABLE int,CreateTime nvarchar(50),UPDATETime nvarchar(50));"; SqlStr = @"CREATE TABLE Receivemsg (SN INT IDENTITY PRIMARY KEY ,addTime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,msg nvarchar(200),fromUser nvarchar(50) ,fromRoom nvarchar(50));"; Cmd = new System.Data.Odbc.OdbcCommand(SqlStr, Cn); try { Cn.Open( ); Cmd.ExecuteNonQuery( ); Cn.Close( ); lbCreateMSG.Text = "建立table成功"; } catch (Exception ex) { lbCreateMSG.Text = ex.ToString( ); } }
public override void guardarMensajes(List <DOM.Mensaje> Mensajes) { var sqlConexion = new System.Data.Odbc.OdbcConnection("Dsn=prova"); var sqlComando = new System.Data.Odbc.OdbcCommand(); sqlComando.CommandText = "INSERT INTO mensajeria.Mensaje (IDMensaje, Mensaje) VALUES ('@IdMensaje','@Descripcion');"; sqlComando.CommandType = System.Data.CommandType.Text; sqlComando.Connection = sqlConexion; System.Data.Odbc.OdbcTransaction Transaccion = null; try { sqlConexion.Open(); Transaccion = sqlConexion.BeginTransaction(); sqlComando.Transaction = Transaccion; foreach (DDD_MSJ.DOM.Mensaje MensajeAInserir in Mensajes) { sqlComando.Parameters.AddWithValue("@IdMensaje", MensajeAInserir.IdMensaje); sqlComando.Parameters.AddWithValue("@Descripcion", MensajeAInserir.Descripcion); sqlComando.ExecuteNonQuery(); } } catch { if (Transaccion != null) { Transaccion.Rollback(); } } finally { if (Transaccion != null) { Transaccion.Commit(); } sqlConexion.Close(); } }
public static void ExecuteNonQueryOnDB(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(); QCommand.ExecuteNonQuery(); } 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; } }
static public bool KillODBCConnection(System.Data.Odbc.OdbcConnection myConn) { if (myConn != null) { if (myConn.State == System.Data.ConnectionState.Closed) { return(false); } try { string strSQL = "kill connection_id()"; System.Data.Odbc.OdbcCommand myCmd = new System.Data.Odbc.OdbcCommand(strSQL, myConn); myCmd.CommandText = strSQL; myCmd.ExecuteNonQuery(); } catch (Exception ex) { } } return(true); }
void DbSQLExecute_Task(object args) { // bool isInLock = false; #if DEBUG return; #endif int inx = System.Convert.ToInt32(args); System.Data.Odbc.OdbcConnection cn; System.DateTime dt = System.DateTime.Now; ; System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand(); cmd.CommandTimeout = 120; cn = new System.Data.Odbc.OdbcConnection(DbCmdServer.getDbConnectStr()); cmd.Connection = cn; cn.Open(); ConsoleServer.WriteLine("Db task started!"); state[inx] = 0; while (true) { try { state[inx] = 1; // lock (sqlLockObj) // { state[inx] = 2; if (sqlQueue.Count == 0) { if(isPrint) ConsoleServer.WriteLine("process cnt:" + processcnt); cn.Close(); lock(this.sqlLockObj) { state[inx]=10; System.Threading.Monitor.Wait(sqlLockObj); state[inx] = 11; } cn.Open(); processcnt = 0; } state[inx] = 3; //} while( sqlQueue.Count > 0) { try { dt = System.DateTime.Now; //lock (sqlLockObj) //{ state[inx] = 4; try { cmd.CommandText = System.Convert.ToString(sqlQueue.Dequeue()); } catch { ;} //} lastcmd[inx] = cmd.CommandText; state[inx] = 9; cmd.ExecuteNonQuery(); ProcessCntPerMin++; processcnt++; state[inx] = 5; if (System.DateTime.Now - dt > new TimeSpan(0, 0, 30)) ConsoleServer.WriteLine("db Executeion time longer than 30 sec:" + cmd.CommandText); state[inx] = 6; errcnt = 0; if (dbqmode == DBQueueMode.Slow) //2013/2/25 System.Threading.Thread.Sleep(1000); // ConsoleServer.WriteLine("finish!"); } //catch (System.Data.Odbc.OdbcException odbcex) //{ // if(odbcex.ErrorCode!=-2147467259) // repeat unixodbc32 // { // ConsoleServer.WriteLine(odbcex.ErrorCode+","+odbcex.Message+cmd.CommandText); // } // else // Console.WriteLine(odbcex.ErrorCode + "," + odbcex.Message); // try { cn.Close(); } // catch { ;} // try // { // cn = new System.Data.Odbc.OdbcConnection(Comm.DB2.Db2.db2ConnectionStr); // cmd.Connection = cn; // cn.Open(); // } // catch { ;} //} catch (Exception ex1) { errcnt++; //if(ex1 is System.Data.Odbc.OdbcException) // ConsoleServer.WriteLine("db exception:"+(ex1 as System.Data.Odbc.OdbcException).Message + cmd.CommandText); //else ConsoleServer.WriteLine("db exception:"+ex1.Message + cmd.CommandText); RemoteInterface.Util.SysLog("dberr.log",System.DateTime.Now+","+ex1.Message+","+cmd.CommandText); try { // if ( cn.State == System.Data.ConnectionState.Broken || cn.State == System.Data.ConnectionState.Closed ) // { try { cn.Close(); } catch { ;} try { cn.Open(); cmd.Connection = cn; } catch { ;} // } } catch { ;} //try //{ // // state[inx] = 7; // //// cn = new System.Data.Odbc.OdbcConnection(Comm.DB2.Db2.db2ConnectionStr); // // cmd.Connection = cn; // // cn.Open(); // // state[inx] = 8; //} //catch (Exception ex){ // ConsoleServer.WriteLine(" In DbSQLExecute_Task " + "," + ex.Message + "," + ex.StackTrace); //} } } } catch (Exception ex) { ConsoleServer.WriteLine(ex.Message + ex.StackTrace); } } }
public void LogMfccStart(string ProcessName, bool bPlay) { System.Data.Odbc.OdbcConnection cn = new System.Data.Odbc.OdbcConnection("dsn=TCS;uid=db2inst1;pwd=db2inst1"); string sql= "insert into tblMFCCStateLog (TIMESTAMP,MFCCId,CONN_State) values('{0}','{1}',{2})" ; System.Data.Odbc.OdbcCommand cmd=new System.Data.Odbc.OdbcCommand(string.Format(sql, getTimeStampString(DateTime.Now), ProcessName, bPlay ? 1 : 3)); try { cn.Open(); cmd.Connection = cn; cmd.ExecuteNonQuery(); } finally { cn.Close(); } }
public static bool KillODBCConnection(System.Data.Odbc.OdbcConnection myConn) { if (myConn != null) { if (myConn.State == System.Data.ConnectionState.Closed) return false; try { string strSQL = "kill connection_id()"; System.Data.Odbc.OdbcCommand myCmd = new System.Data.Odbc.OdbcCommand(strSQL, myConn); myCmd.CommandText = strSQL; myCmd.ExecuteNonQuery(); } catch (Exception ex) { } } return true; }
// <summary> /// recompute indexes for selected table /// to speed up queries /// </summary> /// <returns></returns> public static void UpdateIndexes(string tableType, DbConnect dbConnect) { ProtCidSettings.progressInfo.ResetCurrentProgressInfo(); DbQuery dbQuery = new DbQuery(); try { System.Data.Odbc.OdbcCommand updateIndexCommand = dbConnect.CreateCommand(); // retrieve user-defined indexes string showIndexStr = @"select RDB$INDEX_NAME, RDB$RELATION_NAME from RDB$INDICES WHERE RDB$SYSTEM_FLAG = 0;"; //string showIndexStr = @"select RDB$INDEX_NAME from RDB$INDICES;"; updateIndexCommand.CommandText = showIndexStr; System.Data.Odbc.OdbcDataReader indexReader = updateIndexCommand.ExecuteReader(); // ArrayList indexList = new ArrayList (); Dictionary <string, List <string> > relationIndexHash = new Dictionary <string, List <string> > (); string indexName = ""; string relationName = ""; if (indexReader.HasRows) { while (indexReader.Read()) { relationName = indexReader.GetString(1).Trim().ToUpper(); if (relationName.IndexOf(tableType.ToUpper()) > -1) { indexName = indexReader.GetString(0).Trim().ToUpper(); if (relationIndexHash.ContainsKey(relationName)) { relationIndexHash[relationName].Add(indexName); } else { List <string> relationIndexList = new List <string> (); relationIndexList.Add(indexName); relationIndexHash.Add(relationName, relationIndexList); } } } indexReader.Close(); } foreach (string relationTable in relationIndexHash.Keys) { foreach (string index in relationIndexHash[relationTable]) { // rebuild this index for cryst and interface if (indexName.ToString().ToUpper().IndexOf("RDB$PRIMARY") == -1) { string inactiveIndexStr = string.Format("ALTER INDEX {0} INACTIVE;", index); updateIndexCommand.CommandText = inactiveIndexStr; updateIndexCommand.ExecuteNonQuery(); string activeIndexStr = string.Format("ALTER INDEX {0} ACTIVE;", index); updateIndexCommand.CommandText = activeIndexStr; updateIndexCommand.ExecuteNonQuery(); } // recompute selectivity of this index string updateSelectivityStr = string.Format("SET STATISTICS INDEX {0};", index); updateIndexCommand.CommandText = updateSelectivityStr; updateIndexCommand.ExecuteNonQuery(); } } } catch (Exception ex) { // Displays the Error Message in the progress label. ProtCidSettings.progressInfo.progStrQueue.Enqueue("Update Indexes Errors: " + ex.Message); } }
/// <summary> /// 检查并修正病人信息 /// </summary> /// <param name="zyh"></param> /// <param name="db"></param> /// <returns></returns> public static bool Check(string zyh, RelationalDatabase db) { //Modify By Tany 2015-01-30 如果连接不是正式库,则不验证 string conn = db.ConnectionString; string[] s = conn.Split(';'); if (s.Length > 0) { for (int i = 0; i < s.Length; i++) { if (s[i].IndexOf("initial catalog=") >= 0) { if (s[i].Replace("initial catalog=", "").ToLower() != "trasen") { return(true); } } } } InstanceOldHISDb(); string oldzyh = ""; string sql = ""; try { if (zyh.Trim() == "") { throw new Exception("住院号为空,请检查!"); } oldzyh = Convert.ToInt64(zyh).ToString(); sql = "select * from vi_zy_vinpatient_all where flag<>10 and inpatient_no='" + zyh + "' and dept_id in (select deptid from vi_zy_newhishsz)"; DataTable newPatTb = db.GetDataTable(sql); //上线新护士站的病人才进行验证 if (newPatTb != null && newPatTb.Rows.Count > 0) { sql = "select * from zy_zybrxx where zyh='" + oldzyh + "'"; DataTable oldPatTb = InFomixDb.GetDataTable(sql); if (oldPatTb == null || oldPatTb.Rows.Count == 0) { throw new Exception("在老系统未找到住院号为【" + oldzyh + "】的病人!"); } string oldKs = HisFunctions.ConvertOldhisidToNewHisid(HisFunctions.DataMapType.JC_DEPT_PROPERTY, oldPatTb.Rows[0]["ks"].ToString().Trim(), db); if (oldKs == "") { throw new Exception("未找到该科室[" + oldPatTb.Rows[0]["ks"].ToString().Trim() + "]对应的科室信息!"); } string oldCwh = oldPatTb.Rows[0]["cwh"].ToString().Trim(); bool isTs = false; //如果老系统科室和新系统不一样,则调用新系统到老系统的转科事件 if (oldKs != newPatTb.Rows[0]["dept_id"].ToString()) { isTs = true; MessageBox.Show("该病人在新系统中的科室与老系统的科室不符,系统将尝试修复老系统数据!\r\n\r\n点击确定后将继续操作......", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); //不能调用转科WS,只能直接更新数据 string _ks = HisFunctions.ConvertNewhisidToOldHisid(HisFunctions.DataMapType.JC_DEPT_PROPERTY, newPatTb.Rows[0]["dept_id"].ToString(), db);; sql = "SELECT BQ FROM zy_bqksdzb Where KS = '" + _ks + "'"; string _bq = Convertor.IsNull(InFomixDb.GetDataResult(sql), ""); 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; //清空老床位信息 sql = "UPDATE ZY_CWXX Set zyh = '',xm = '',APZ = '',ZT = '空闲' Where zyh = '" + oldzyh + "'"; cmd.CommandText = sql; cmd.ExecuteNonQuery(); //更新病人信息 sql = "UPDATE ZY_ZYBRXX SET CWH='',BQ='" + _bq + "',ks='" + _ks + "',bf='',sfapcw='N' WHERE ZYH='" + oldzyh + "'"; cmd.CommandText = sql; cmd.ExecuteNonQuery(); tx.Commit(); } catch (Exception err) { tx.Rollback(); throw err; } finally { cmd.Dispose(); connection.Close(); } } //这里需要在检查一下这个病人的床位号如果不为空的情况下,在老系统床位表上是不是有信息 Modify By Tany 2015-01-22 string cwZyh = ""; if (oldCwh != "") { sql = "select zyh from zy_cwxx where ks='" + oldPatTb.Rows[0]["ks"].ToString() + "' and cwh='" + oldCwh + "'"; cwZyh = Convertor.IsNull(InFomixDb.GetDataResult(sql), ""); } //如果老系统科室和新系统或者床位不一样,则调用新系统到老系统的转床事件 //if (oldKs != newPatTb.Rows[0]["dept_id"].ToString() || oldCwh != newPatTb.Rows[0]["bed_no"].ToString() || cwZyh != oldzyh) //Modify By Tany 2015-05-05 修正这里的判断,因为新老系统床位名称可能不一致,判断床位的时候,需要通过对应关系去验证床号是否正确 string mapCwh = HisFunctions.ConvertNewhisidToOldHisid(HisFunctions.DataMapType.ZY_BEDDICTION, newPatTb.Rows[0]["bed_id"].ToString(), db); string[] ss = mapCwh.Replace("||", "|").Split("|".ToCharArray()); if (ss.Length > 1) { mapCwh = ss[1]; } if (oldKs != newPatTb.Rows[0]["dept_id"].ToString() || oldCwh != mapCwh || cwZyh != oldzyh) { if (!isTs) { isTs = true; MessageBox.Show("该病人在新系统中的床位与老系统的床位不符,系统将尝试修复老系统数据!\r\n\r\n点击确定后将继续操作......", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } TrasenWS.TrasenWS ws = new TrasenHIS.TrasenWS.TrasenWS(); string strXML = ""; strXML = ws.GetXml("n2oZc.HIS", newPatTb.Rows[0]["inpatient_id"].ToString()); strXML = ws.ExeWebService("n2oZc.HIS", strXML); DataSet dset = HisFunctions.ConvertXmlToDataSet(strXML); if (dset.Tables["HEAD"].Rows.Count > 0) { if (dset.Tables["HEAD"].Rows[0]["ERRCODE"].ToString() == "-1") { throw new Exception("调用WS转床时出现错误:" + dset.Tables["HEAD"].Rows[0]["ERRTEXT"].ToString()); } } } if (isTs) { MessageBox.Show("老系统数据修复完成,将继续完成您的操作!"); } } return(true); } catch (Exception ex) { MessageBox.Show("检查新老系统病人状态时出错,您的操作将不能继续!\r\n\r\n" + ex.Message); return(false); } }
/// <summary> /// 发药状态更新到老HIS 门诊状态 /// </summary> /// <param name="dset"></param> /// <param name="db"></param> /// <returns></returns> public static string SaveMzFyzt(DataSet dset, RelationalDatabase db) { try { DataTable tb = dset.Tables[0]; string ssql = ""; DataRow row = tb.Rows[0]; string BIZID = row["BIZID"].ToString(); string FY_DATE = Convert.ToDateTime(row["FY_DATE"]).ToString("yyyy-MM-dd HH:mm:ss"); string FY_USER = row["FY_USER"].ToString(); string PY_USER = row["PY_USER"].ToString(); string DH = row["DH"].ToString(); decimal ZJE = Convert.ToDecimal(row["zje"]); FY_USER = HisFunctions.ConvertNewhisidToOldHisid(HisFunctions.DataMapType.JC_EMPLOYEE_PROPERTY, FY_USER, db); PY_USER = HisFunctions.ConvertNewhisidToOldHisid(HisFunctions.DataMapType.JC_EMPLOYEE_PROPERTY, PY_USER, db); #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; if (ZJE >= 0) { cmd.CommandText = "update MZ_CFD_ZB set sffy='Y',fyy='" + FY_USER + "', fysj='" + FY_DATE + "',pzr='" + PY_USER + "' where dh='" + DH + "'"; cmd.ExecuteNonQuery(); cmd.CommandText = "update MZ_CFD_CB set sffy='Y',fyy='" + FY_USER + "' where dh='" + DH + "'"; cmd.ExecuteNonQuery(); } else { cmd.CommandText = "update MZ_CFD_ZB set sfty='Y',qrty='" + FY_USER + "',tyr='" + FY_USER + "', tyrq='" + FY_DATE + "' where dh='" + DH + "'"; cmd.ExecuteNonQuery(); cmd.CommandText = "update MZ_CFD_CB set sfty='Y',qrty='" + FY_USER + "',tyr='" + FY_USER + "' where dh='" + DH + "'"; 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记录 if (ZJE > 0) { ssql = "update EVENTLOG set FINISH=1 ,FINISH_DATE='" + DateTime.Now.ToString() + "' where EVENT='MZFYZT' AND BIZID='" + BIZID + "'"; } else { ssql = "update EVENTLOG set FINISH=1 ,FINISH_DATE='" + DateTime.Now.ToString() + "' where EVENT='MZFYZTTY' AND BIZID='" + BIZID + "'"; } db.DoCommand(ssql); #endregion System.String[] str = { "0", "保存成功" }; return(HisFunctions.GetResponseString("SaveKcph", str)); } catch (Exception err) { throw err; } }
/// <summary> /// 导出患者费用信息 /// </summary> /// <param name="path">路径</param> /// <param name="tablename">表名,同文件名</param> /// <param name="p">患者信息</param> /// <param name="alFeeDetail">费用信息</param> /// <param name="errTxt">错误信息</param> /// <returns>1成功 -1失败</returns> public static int ExportInpatientFeedetail(string path, string tablename, ref string errTxt) { if (!System.IO.Directory.Exists(path)) { System.IO.Directory.CreateDirectory(path); } if (tablename.Substring(0, 1).ToUpper() != "Y") { tablename = "Y" + tablename; } string connect = @"Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277; Dbq=" + path; System.Data.Odbc.OdbcConnection myconn = new System.Data.Odbc.OdbcConnection(connect); string drop = "drop table " + tablename; string create = "create table " + tablename + @"(GMSFHM CHAR(20) , ZYH CHAR(14) , XMXH NUMERIC , XMBH CHAR(20) , XMMC CHAR(50) , FLDM CHAR(10), YPGG CHAR(30),YPJX CHAR(10), JG NUMERIC , MCYL NUMERIC , JE NUMERIC , ZFBL NUMERIC , ZFJE NUMERIC , BZ1 CHAR(20) , BZ2 CHAR(20) , BZ3 CHAR(20), FYRQ CHAR(20))"; System.Data.Odbc.OdbcCommand cmDrop = new System.Data.Odbc.OdbcCommand(drop, myconn); System.Data.Odbc.OdbcCommand cmCreate = new System.Data.Odbc.OdbcCommand(create, myconn); myconn.Open(); try { cmDrop.ExecuteNonQuery(); } catch (Exception e) {} try { cmCreate.ExecuteNonQuery(); } catch (Exception ex) { errTxt = "导出文件出错" + ex.Message; return(-1); } System.Data.Odbc.OdbcCommand cmInsert = new System.Data.Odbc.OdbcCommand(); cmInsert.Connection = myconn; int i = 1; //个人身份号码 住院号 项目序号 项目编号 项目名称 分类代码 规范 药品剂型 价格 //每次用量 金额 自费比例 自费金额 处方号 费用日期 标志3 费用日期 string insert = "insert into " + tablename + @"(GMSFHM, ZYH, XMXH, XMBH , XMMC , FLDM ,YPGG ,YPJX ,JG , MCYL, JE, ZFBL,ZFJE, BZ1, BZ2, BZ3, FYRQ ) values ( '{0}','{1}',{2},'{3}', '{4}', '{5}','{6}','{7}',{8},{9},{10},{11},{12},'{13}','{14}','{15}','{16}' )"; try { insert = string.Format(insert, "50023919851113319X", "0000001", "1", "001", "阿莫西林颗粒", "sys", "specs", "dosecode", 10, 2, 20, 0.8, 16, "F12345678901", System.DateTime.Now.ToString("yyyy.MM.dd"), "", System.DateTime.Now.ToString("yyyyMMdd")); } catch (Exception ex) { errTxt = "导出文件出错" + ex.Message; return(0); } i++; cmInsert.CommandText = insert; try { cmInsert.ExecuteNonQuery(); } catch (Exception ex) { errTxt = "导出文件出错" + ex.Message; return(-1); } cmInsert.Dispose(); cmCreate.Dispose(); cmDrop.Dispose(); myconn.Close(); try { string file = path + "\\" + tablename + ".dbf"; System.IO.FileInfo fileInfo = new System.IO.FileInfo(file); if (!System.IO.Directory.Exists(path + "\\Backup")) { System.IO.Directory.CreateDirectory(path + "\\Backup"); } fileInfo.MoveTo(path + "\\Backup\\" + System.DateTime.Now.ToString("yyyyMMddHHmmss") + tablename + ".dbf"); } catch { } return(1); }
/// <summary> /// 导出患者费用信息 /// </summary> /// <param name="path">路径</param> /// <param name="tablename">表名,同文件名</param> /// <param name="idCard">身份号码</param> /// <param name="regNO">医保端流水号</param> /// <param name="alFeeDetail">费用信息</param> /// <param name="errTxt">错误信息</param> /// <returns>1成功 -1失败</returns> public static int ExportFeedetails(string path, string tablename, string idCard, string regNO, ArrayList alFeeDetail, ref string errTxt) { if (!System.IO.Directory.Exists(path)) { System.IO.Directory.CreateDirectory(path); } if (tablename.Substring(0, 1).ToUpper() != "Y") { tablename = "Y" + tablename; } string connect = @"Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277; Dbq=" + path; System.Data.Odbc.OdbcConnection myconn = new System.Data.Odbc.OdbcConnection(connect); string drop = "drop table " + tablename; string create = "create table " + tablename + @"(GMSFHM CHAR(20) , ZYH CHAR(14) , XMXH NUMERIC , XMBH CHAR(20) , XMMC CHAR(50) , FLDM CHAR(10), YPGG CHAR(30),YPJX CHAR(10), JG NUMERIC , MCYL NUMERIC , JE NUMERIC , ZFBL NUMERIC , ZFJE NUMERIC , BZ1 CHAR(20) , BZ2 CHAR(20) , BZ3 CHAR(20), FYRQ CHAR(20))"; System.Data.Odbc.OdbcCommand cmDrop = new System.Data.Odbc.OdbcCommand(drop, myconn); System.Data.Odbc.OdbcCommand cmCreate = new System.Data.Odbc.OdbcCommand(create, myconn); System.Data.Odbc.OdbcTransaction trans = null; myconn.Open(); try { cmDrop.ExecuteNonQuery(); } catch (Exception e) { } try { cmCreate.ExecuteNonQuery(); } catch (Exception ex) { errTxt = "导出文件出错" + ex.Message; return(-1); } trans = myconn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted); System.Data.Odbc.OdbcCommand cmInsert = new System.Data.Odbc.OdbcCommand(); cmInsert.Connection = myconn; cmInsert.Transaction = trans; int i = 1; foreach (Neusoft.HISFC.Models.Fee.Inpatient.FeeItemList f in alFeeDetail) { //个人身份号码 住院号 项目序号 项目编号 项目名称 分类代码 规范 药品剂型 价格 //每次用量 金额 自费比例 自费金额 处方号 费用日期 标志3 费用日期 string insert = "insert into " + tablename + @"(GMSFHM, ZYH, XMXH, XMBH , XMMC , FLDM ,YPGG ,YPJX ,JG , MCYL, JE, ZFBL,ZFJE, BZ1, BZ2, BZ3, FYRQ ) values ( '{0}','{1}',{2},'{3}', '{4}', '{5}','{6}','{7}',{8},{9},{10},{11},{12},'{13}','{14}','{15}','{16}' )"; try { insert = string.Format(insert, idCard, regNO, i.ToString(), f.Compare.CenterItem.ID, f.Compare.CenterItem.Name, f.Compare.CenterItem.SysClass, f.Compare.CenterItem.Specs, f.Compare.CenterItem.DoseCode, f.Item.Price, f.Item.Qty, f.Item.Price * f.Item.Qty, f.Compare.CenterItem.Rate, f.Item.Price * f.Item.Qty * f.Compare.CenterItem.Rate, f.RecipeNO + f.SequenceNO.ToString().PadLeft(2, '0'), f.FeeOper.OperTime.ToString("yyyy.MM.dd"), "", f.FeeOper.OperTime.ToString("yyyyMMdd")); } catch (Exception ex) { trans.Rollback(); errTxt = "导出文件出错" + ex.Message; return(0); } i++; cmInsert.CommandText = insert; try { cmInsert.ExecuteNonQuery(); } catch (Exception ex) { trans.Rollback(); errTxt = "导出文件出错" + ex.Message; return(-1); } } trans.Commit(); cmInsert.Dispose(); cmCreate.Dispose(); cmDrop.Dispose(); myconn.Close(); try { string file = path + "\\" + tablename + ".dbf"; System.IO.FileInfo fileInfo = new System.IO.FileInfo(file); if (!System.IO.Directory.Exists(path + "\\Backup")) { System.IO.Directory.CreateDirectory(path + "\\Backup"); } fileInfo.CopyTo(path + "\\Backup\\" + tablename + ".dbf"); } catch { } return(1); }
// used to reset all the tables for this service private void clearAllTables() { //DELETE FROM `PerformanceGLCubeResults`; //ALTER TABLE PerformanceGLCubeResults AUTO_INCREMENT = 0; //DELETE FROM `PerformanceAppDeviceInfo`; //ALTER TABLE PerformanceAppDeviceInfo AUTO_INCREMENT = 0; //DELETE FROM `PerformanceMatrixTestResults`; //ALTER TABLE PerformanceMatrixTestResults AUTO_INCREMENT = 0; System.Data.Odbc.OdbcConnection conn = createSQLConnection(); System.Data.Odbc.OdbcTransaction transaction = null; try { string myInsertQuery = "DELETE FROM `PerformanceGLCubeResults`;"; conn.Open(); transaction = conn.BeginTransaction(); System.Data.Odbc.OdbcCommand myCommand = new System.Data.Odbc.OdbcCommand(myInsertQuery, conn, transaction); myCommand.ExecuteNonQuery(); myCommand.CommandText = "ALTER TABLE PerformanceGLCubeResults AUTO_INCREMENT = 0;"; myCommand.ExecuteNonQuery(); myCommand.CommandText = "DELETE FROM `PerformanceAppDeviceInfo`;"; myCommand.ExecuteNonQuery(); myCommand.CommandText = "ALTER TABLE PerformanceAppDeviceInfo AUTO_INCREMENT = 0;"; myCommand.ExecuteNonQuery(); myCommand.CommandText = "DELETE FROM `PerformanceMatrixTestResults`;"; myCommand.ExecuteNonQuery(); myCommand.CommandText = "ALTER TABLE PerformanceMatrixTestResults AUTO_INCREMENT = 0;"; myCommand.ExecuteNonQuery(); transaction.Commit(); } catch (Exception) { if (transaction != null) transaction.Rollback(); throw; } finally { conn.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; }
private void btnOKnp_Click(object sender, EventArgs e) { if (txtBxnewuser.Text == "") { MessageBox.Show(" Username not specified "); } else if (txtBxnewuser.Text != "") { if (AuthCon.State == ConnectionState.Open) { AuthCom = new System.Data.Odbc.OdbcCommand("SELECT username FROM mis_pass", AuthCon); AuthRead = AuthCom.ExecuteReader(); string usrnm = ""; bool usrchek = false;//indicate that user does not exist while (AuthRead.Read()) { usrnm = (string)AuthRead[0]; if (usrnm.CompareTo(txtBxnewuser.Text) == 0) { usrchek = true; // user exists break; } } if (usrchek == false) { MessageBox.Show(" Username name does not exist. Please re-check "); } else if (usrchek == true) { AuthCom = new System.Data.Odbc.OdbcCommand("SELECT passwd FROM mis_pass WHERE username='******'", AuthCon); AuthRead = AuthCom.ExecuteReader(); string passwd = ""; bool passchek = false;//indicate that password is invalid while (AuthRead.Read()) { passwd = (string)AuthRead[0]; passwd = DecodePass(passwd, passwd.Length); if (passwd.CompareTo(txtBxoldpass.Text) == 0) { passchek = true; // password valid break; } else if (passwd.CompareTo(txtBxoldpass.Text) != 0) { MessageBox.Show(" Invalid Password "); } } if (passchek == true) { if (txtBxnewpass.Text.CompareTo(txtBxconfirm.Text) != 0) { MessageBox.Show("The Passwords do not match. Please Retype"); txtBxconfirm.Text = txtBxnewpass.Text = ""; } else if (txtBxnewpass.Text.CompareTo(txtBxconfirm.Text) == 0) { string newpass = txtBxconfirm.Text; newpass = EncodePass(newpass, newpass.Length); AuthCom = new System.Data.Odbc.OdbcCommand("UPDATE mis_pass SET passwd='" + newpass + "' WHERE username='******'", AuthCon); AuthCom.ExecuteNonQuery(); MessageBox.Show(" Password changed successfully "); } } else if (passchek == false) { MessageBox.Show(" Invalid Password "); } } } } else if (AuthCon.State == ConnectionState.Closed) { MessageBox.Show(" Connection closed "); } }
void InsertAlarmTable(Event evt) { string sql; // if(! evt.IsReNew) if (evt is Host.Event.Jam.JamRange ) { sql = "insert into tblSysAlarmLog (EVENTID,ALARMCLASS,TIMESTAMP,LINEID,DIRECTION,START_MILEAGE,END_MILEAGE,DEGREE,OriginalEventID,DeviceName) values({0},{1},'{2}','{3}','{4}',{5},{6},{7},{8},'{9}')"; sql = string.Format(sql, evt.EventId, evt.EventClass, RemoteInterface.DbCmdServer.getTimeStampString(DateTime.Now), evt.getLineId(), evt.getDir(), evt.getStartMileM(), evt.getEndMileM(), evt.getDegree(), evt.OrgEventId,(evt as Jam.JamRange).DeviceName); } else if(evt is Host.Event.Jam.RampJamRange) { sql = "insert into tblSysAlarmLog (EVENTID,ALARMCLASS,TIMESTAMP,LINEID,DIRECTION,START_MILEAGE,END_MILEAGE,DEGREE,OriginalEventID,DivisionID,DeviceName) values({0},{1},'{2}','{3}','{4}',{5},{6},{7},{8},'{9}','{10}')"; sql =string.Format(sql, evt.EventId, evt.EventClass, RemoteInterface.DbCmdServer.getTimeStampString(DateTime.Now), evt.getLineId(), evt.getDir(), evt.getStartMileM(), evt.getEndMileM(), evt.getDegree(), evt.OrgEventId, (evt as Jam.RampJamRange).rampVDData.divisionId, (evt as Jam.RampJamRange).rampVDData.deviceName); } else if(evt is Host.Event.MovingConstruct.MovingConstructRange) { sql = "insert into tblSysAlarmLog (EVENTID,ALARMCLASS,TIMESTAMP,LINEID,DIRECTION,START_MILEAGE,END_MILEAGE,OriginalEventID,mc_id,mc_notifier,mc_blocktypeid,mc_blocklane,mc_memo) values({0},{1},'{2}','{3}','{4}',{5},{6},{7},{8},'{9}',{10},'{11}','{12}')"; sql =string.Format(sql, evt.EventId, evt.EventClass, RemoteInterface.DbCmdServer.getTimeStampString(DateTime.Now), evt.getLineId(), evt.getDir(), evt.getStartMileM(), evt.getEndMileM(), evt.OrgEventId, (evt as MovingConstruct.MovingConstructRange).id, (evt as MovingConstruct.MovingConstructRange).notifier, (evt as MovingConstruct.MovingConstructRange).blockTypeId, (evt as MovingConstruct.MovingConstructRange).blocklane, (evt as MovingConstruct.MovingConstructRange).description); } else if(evt is Host.Event.Weather.WeatherRange) { sql = "insert into tblSysAlarmLog (EVENTID,ALARMCLASS,TIMESTAMP,LINEID,DIRECTION,START_MILEAGE,END_MILEAGE,DEGREE,OriginalEventID,DeviceName) values({0},{1},'{2}','{3}','{4}',{5},{6},{7},{8},'{9}')"; sql=string.Format(sql,evt.EventId, evt.EventClass, RemoteInterface.DbCmdServer.getTimeStampString(DateTime.Now), evt.getLineId(), evt.getDir(), evt.getStartMileM(), evt.getEndMileM(), evt.getDegree(), evt.OrgEventId,((Host.Event.Weather.WeatherRange)evt).DeviceName); } else if (evt is Host.Event.TEM.TemRangeData) { sql = (evt as Host.Event.TEM.TemRangeData).getSQL_tInsertAlarmData(); } else if(evt is Host.Event.IID.IIDRange) { Host.Event.IID.IIDRange iidevt = evt as Host.Event.IID.IIDRange; sql = "insert into tblSysAlarmLog (EVENTID,ALARMCLASS,TIMESTAMP,LINEID,DIRECTION,START_MILEAGE,END_MILEAGE,DEGREE,OriginalEventID,CAM_ID,LANE_ID) values({0},{1},'{2}','{3}','{4}',{5},{6},{7},{8},{9},{10})"; sql = string.Format(sql, iidevt.EventId, iidevt.EventClass, RemoteInterface.DbCmdServer.getTimeStampString(DateTime.Now), iidevt.getLineId(), iidevt.getDir(), iidevt.getStartMileM(), iidevt.getEndMileM(), iidevt.getDegree(), iidevt.OrgEventId,iidevt.Cam_ID,iidevt.Lane_Id); } else { sql = "insert into tblSysAlarmLog (EVENTID,ALARMCLASS,TIMESTAMP,LINEID,DIRECTION,START_MILEAGE,END_MILEAGE,DEGREE,OriginalEventID) values({0},{1},'{2}','{3}','{4}',{5},{6},{7},{8})"; sql = string.Format(sql, evt.EventId, evt.EventClass, RemoteInterface.DbCmdServer.getTimeStampString(DateTime.Now), evt.getLineId(), evt.getDir(), evt.getStartMileM(), evt.getEndMileM(), evt.getDegree(), evt.OrgEventId); } try { System.Data.Odbc.OdbcConnection cn = new System.Data.Odbc.OdbcConnection(Global.Db2ConnectionString); System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand(sql); cmd.Connection = cn; try { cn.Open(); cmd.ExecuteNonQuery(); } catch(Exception ex) { Util.SysLog("evtlerr.log",ex.Message+","+ex.StackTrace); } finally { cn.Close(); } // Program.matrix.dbServer.SendSqlCmd(sql); } catch (Exception ex) { ConsoleServer.WriteLine(ex.Message + "," + ex.StackTrace); } }
/// <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 override int FSFileUpdate(int? id, byte[] databuffer) { System.Data.Odbc.OdbcConnection rawconn = this._dbObj.GetDatabaseConnector().GetRawConnectionObject(); System.Data.Odbc.OdbcCommand ocomm = new System.Data.Odbc.OdbcCommand(); string sqlStr = "update #__filesys_filenode set data=? where id=?"; sqlStr = this._dbObj.GetDatabaseConnector().DoTopLevelSqlTranslations(ref sqlStr); ocomm.CommandText = sqlStr; ocomm.Connection = rawconn; System.Data.Odbc.OdbcParameter idParam = new System.Data.Odbc.OdbcParameter("@dataid", System.Data.Odbc.OdbcType.Int); idParam.Value = id; System.Data.Odbc.OdbcParameter dataParam = new System.Data.Odbc.OdbcParameter("@databit", System.Data.Odbc.OdbcType.Image); dataParam.Value = databuffer; ocomm.Parameters.Add(dataParam); ocomm.Parameters.Add(idParam); try { ocomm.ExecuteNonQuery(); } catch (Exception e) { System.Diagnostics.Debug.Print(e.Message); return -1; } return 1; }
/// <summary> /// 发药状态更新到老HIS 住院状态 /// </summary> /// <param name="dset"></param> /// <param name="db"></param> /// <returns></returns> public static string SaveFyzt(DataSet dset, RelationalDatabase db) { try { DataTable tb = dset.Tables[0]; string ssql = ""; DataRow row = tb.Rows[0]; string BIZID = row["BIZID"].ToString(); string FY_DATE = Convert.ToDateTime(row["FY_DATE"]).ToString("yyyy-MM-dd HH:mm:ss"); string FY_USER = row["FY_USER"].ToString(); string PY_USER = row["PY_USER"].ToString(); string ZYH = row["ZYH"].ToString(); string YZXH = row["YZXH"].ToString(); string YZZXH = row["YZZXH"].ToString(); decimal num = Convert.ToDecimal(row["NUM"].ToString()); FY_USER = HisFunctions.ConvertNewhisidToOldHisid(HisFunctions.DataMapType.JC_EMPLOYEE_PROPERTY, FY_USER, db); PY_USER = HisFunctions.ConvertNewhisidToOldHisid(HisFunctions.DataMapType.JC_EMPLOYEE_PROPERTY, PY_USER, db); #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; if (num > 0) { cmd.CommandText = "update zy_yzlxd set zjfysj='" + FY_DATE + "' where zyh='" + ZYH + "' and xh=" + YZXH + " and zxh=" + YZZXH + ""; cmd.ExecuteNonQuery(); cmd.CommandText = "update zy_cqlsyzd set sffy='Y' where zyh='" + ZYH + "' and xh=" + YZXH + " and zxh=" + YZZXH + ""; cmd.ExecuteNonQuery(); } else { cmd.CommandText = "update zy_yzlxd set sfty='Y' where zyh='" + ZYH + "' and xh=" + YZXH + " and zxh=" + YZZXH + ""; cmd.ExecuteNonQuery(); cmd.CommandText = "update zy_cqlsyzd set sfty='Y' where zyh='" + ZYH + "' and xh=" + YZXH + " and zxh=" + YZZXH + ""; 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='FYZT' AND BIZID='" + BIZID + "'"; db.DoCommand(ssql); } #endregion System.String[] str = { "0", "保存成功" }; return(HisFunctions.GetResponseString("SaveKcph", str)); } catch (Exception err) { throw err; } }
/// <summary> /// 导出患者费用信息 /// </summary> /// <param name="alFeeDetail">费用信息</param> /// <param name="p">患者信息</param> /// <param name="errTxt">错误信息</param> /// <returns>1成功 -1失败</returns> public int ExportInpatientFeedetail(string path, string tablename, Neusoft.HISFC.Models.RADT.Patient p, ArrayList alFeeDetail, ref string errTxt) { if (!System.IO.Directory.Exists(path)) { System.IO.Directory.CreateDirectory(path); } //try //{ // foreach (string file in System.IO.Directory.GetFiles(path)) // { // System.IO.File.Delete(file); // } //} //catch { } string connect = @"Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277; Dbq=" + path; System.Data.Odbc.OdbcConnection myconn = new System.Data.Odbc.OdbcConnection(connect); string drop = "drop table " + tablename; string create = "create table " + tablename + @"(GRSHBZH CHAR(20) , ZYH CHAR(12) , XMXH NUMERIC , XMBH CHAR(15) , XMMC CHAR(40) , FLDM CHAR(3), YPGG CHAR(15),YPJX CHAR(8), JG NUMERIC , MCYL NUMERIC , JE NUMERIC , ZFBL NUMERIC , ZFJE NUMERIC , CZFBZ CHAR(3) , BZ1 CHAR(20) , BZ2 CHAR(20) , BZ3 CHAR(20))"; System.Data.Odbc.OdbcCommand cmDrop = new System.Data.Odbc.OdbcCommand(drop, myconn); System.Data.Odbc.OdbcCommand cmCreate = new System.Data.Odbc.OdbcCommand(create, myconn); myconn.Open(); //try //{ // cmDrop.ExecuteNonQuery(); //} //catch { } try { cmCreate.ExecuteNonQuery(); } catch (Exception ex) { errTxt = "导出文件出错" + ex.Message; return(-1); } System.Data.Odbc.OdbcCommand cmInsert = new System.Data.Odbc.OdbcCommand(); cmInsert.Connection = myconn; int i = 1; //System.Data.Odbc.OdbcTransaction trans = myconn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted); foreach (Neusoft.HISFC.Models.Fee.Inpatient.FeeItemList f in alFeeDetail) { string insert = "insert into " + tablename + @"(GRSHBZH, ZYH, XMXH, XMBH , XMMC , FLDM ,YPGG ,YPJX ,JG , MCYL, JE, ZFBL,ZFJE, CZFBZ, BZ1, BZ2, BZ3 ) values ( '{0}','{1}',{2},'{3}', '{4}', '{5}','{6}','{7}',{8},{9},{10},{11},{12},'{13}','{14}','{15}','{16}' )"; //传过去的价格应该是优惠之后的价格 try { insert = string.Format(insert, p.IDCard, p.PID.PatientNO, f.Item.User02, f.Item.UserCode, f.Item.Name, f.Item.SysClass.Name, f.Item.Specs, f.Item.User01, f.Item.Price, f.NoBackQty, f.FT.OwnCost, 0, 0, 0, "", "", ""); } catch (Exception ex) { //trans.Rollback(); errTxt = "导出文件出错" + ex.Message; return(0); } i++; cmInsert.CommandText = insert; //cmInsert.Transaction = trans; try { cmInsert.ExecuteNonQuery(); } catch (Exception ex) { //trans.Rollback(); errTxt = "导出文件出错" + ex.Message; return(-1); } } //trans.Commit(); cmInsert.Dispose(); cmCreate.Dispose(); cmDrop.Dispose(); myconn.Close(); try { string file = System.IO.Directory.GetFiles(path)[0]; System.IO.FileInfo fileInfo = new System.IO.FileInfo(file); fileInfo.MoveTo(path + @"\" + tablename); } catch { } return(1); }
private void btnok_Click(object sender, EventArgs e) { if (initialset == true) { string newpass = txtbxpassword.Text; newpass = EncodePass(newpass, newpass.Length); //MessageBox.Show(newpass); AuthCom = new System.Data.Odbc.OdbcCommand("UPDATE mis_pass SET passwd='" + newpass + "' WHERE username='******'", AuthCon); AuthCom.ExecuteNonQuery(); MessageBox.Show(" New password updated. please restart application"); validity.cancelled = true; AuthCon.Close(); this.Close(); } else if (initialset == false) { if (txtbxusername.Text == "") { MessageBox.Show(" Please Specify Username "); } else if (AuthCon.State == ConnectionState.Open) { string passwd = ""; AuthCom = new System.Data.Odbc.OdbcCommand("SELECT passwd FROM mis_pass WHERE username='******'", AuthCon); AuthRead = AuthCom.ExecuteReader(); while (AuthRead.Read()) { passwd = (String)AuthRead[0]; } //MessageBox.Show(passwd); if (passwd.CompareTo("") == 0) { MessageBox.Show(" Username invalid "); } else if (passwd.CompareTo("") != 0) { passwd = DecodePass(passwd, passwd.Length); //MessageBox.Show(passwd); if (passwd.CompareTo(txtbxpassword.Text) == 0) { validity.authenticated = true; if (txtbxusername.Text == "admin") { validity.uservalue = true; } else { validity.uservalue = false; } } else { validity.authenticated = false; } AuthCon.Close(); this.Close(); } } } }