internal LsPerfilFuncao Listar() { try { var m_oPerfilFuncao = new LsPerfilFuncao(); sbSQL.Length = 0; sbSQL.Append("SELECT IDDOR033, IDPRO016, IDPRO031"); sbSQL.Append(" FROM TBPRO033"); oAdapter = ObterDataAdapter(sbSQL.ToString()); ITableMapping objMapping = oAdapter.TableMappings.Add("Table", LsPerfilFuncao.tbPerfilFuncao); objMapping.ColumnMappings.Add("IDDOR033", "Codigo"); objMapping.ColumnMappings.Add("IDPRO016", "CodigoPerfil"); objMapping.ColumnMappings.Add("IDPRO031", "CodigoFuncao"); oAdapter.Fill(m_oPerfilFuncao); return m_oPerfilFuncao; } catch (Exception ex) { throw new Dor.Util.OperacaoInvalidaBD(ex); } }
internal LsOperacao Listar() { try { var m_oOperacao = new LsOperacao(); sbSQL.Length = 0; sbSQL.Append("SELECT DSAPLDOPRC, IDPRO020, NMOPRC"); sbSQL.Append(" FROM TBPRO020"); oAdapter = ObterDataAdapter(sbSQL.ToString()); ITableMapping objMapping = oAdapter.TableMappings.Add("Table" , LsOperacao.tbOperacao); objMapping.ColumnMappings.Add("DSAPLDOPRC","Apelido"); objMapping.ColumnMappings.Add("IDPRO020","Codigo"); objMapping.ColumnMappings.Add("NMOPRC","Nome"); oAdapter.Fill(m_oOperacao); return m_oOperacao; } catch (Exception ex) { throw new Dor.Util.OperacaoInvalidaBD(ex); } }
internal LsUf Listar() { try { var m_oUf = new LsUf(); sbSQL.Length = 0; sbSQL.Append("SELECT CDUNDDFEDR, IDPRO021, NMUNDDFEDR"); sbSQL.Append(" FROM TBPRO021"); oAdapter = ObterDataAdapter(sbSQL.ToString()); ITableMapping objMapping = oAdapter.TableMappings.Add("Table", LsUf.tbUf); objMapping.ColumnMappings.Add("CDUNDDFEDR", LsUf.cpSigla); objMapping.ColumnMappings.Add("IDPRO021", LsUf.cpCodigo); objMapping.ColumnMappings.Add("NMUNDDFEDR", LsUf.cpNome); oAdapter.Fill(m_oUf); return m_oUf; } catch (Exception ex) { throw new Dor.Util.OperacaoInvalidaBD(ex); } }
internal LsDireito Listar() { try { var m_oDireito = new LsDireito(); sbSQL.Length = 0; sbSQL.Append("SELECT DSAPLDDIRT, IDPRO017, NMDIRT"); sbSQL.Append(" FROM TBPRO017"); oAdapter = ObterDataAdapter(sbSQL.ToString()); ITableMapping objMapping = oAdapter.TableMappings.Add("Table", LsDireito.tbDireito); objMapping.ColumnMappings.Add("DSAPLDDIRT", LsDireito.cpApelido); objMapping.ColumnMappings.Add("IDPRO017", LsDireito.cpCodigo); objMapping.ColumnMappings.Add("NMDIRT", LsDireito.cpNome); oAdapter.Fill(m_oDireito); return m_oDireito; } catch (Exception ex) { throw new Dor.Util.OperacaoInvalidaBD(ex); } }
public SQLCommandBuilderExample () { var connectionDetails = ConfigurationManager.ConnectionStrings ["MyDatabase"]; var providerName = connectionDetails.ProviderName; var connectionString = connectionDetails.ConnectionString; var dbFactory = DbProviderFactories.GetFactory (providerName); Adapter = dbFactory.CreateDataAdapter (); // Create and configure a connection using (var connection = dbFactory.CreateConnection ()) { connection.ConnectionString = connectionString; // Create Select Command var selCmd = dbFactory.CreateCommand (); selCmd.CommandText = "SELECT idMyTable, FieldA FROM MyTable"; selCmd.Connection = connection; Adapter.SelectCommand = selCmd; // Create and configure DbCommandBuilder var builder = dbFactory.CreateCommandBuilder (); builder.DataAdapter = (DbDataAdapter)Adapter; // Create and fill a DataTable Adapter.Fill (DS); } }
public void Render(TextWriter writer) { writer.WriteLine("Rendering data:"); var dataSet = new DataSet(); dataAdapter.Fill(dataSet); foreach (DataTable table in dataSet.Tables) { foreach (DataColumn column in table.Columns) { writer.Write(column.ColumnName.PadRight(20) + " "); } writer.WriteLine(); foreach (DataRow row in table.Rows) { for (int i = 0; i < table.Columns.Count; i++) { writer.Write(row[i].ToString().PadRight(20) + " "); } writer.WriteLine(); } } }
/// <summary> /// method to execute the dataset property of the command object /// </summary> /// <param name="commandType"></param> /// <param name="commandText"></param> /// <returns></returns> public DataSet ExecuteDataSet(CommandType commandType, string commandText) { try { // get the command object from the DBManagerFactory this.idbCommand = DBManagerFactory.GetCommand(this.ProviderType); // method to prepare the oommand object PrepareCommand(idbCommand, this.Connection, this.Transaction, commandType, commandText, this.Parameters); // get the data adapter from DBManagerFactory IDbDataAdapter dataAdapter = DBManagerFactory.GetDataAdapter(this.ProviderType); // assign the selected command property dataAdapter.SelectCommand = idbCommand; DataSet dataSet = new DataSet(); // fill the dataset dataAdapter.Fill(dataSet); // clear the command parameters idbCommand.Parameters.Clear(); return(dataSet); } catch (Exception ex) { throw ex; } }
public DataSet Execute(string SQL) { try { if (_con == null || df == null || _cmd != null) { return(null); } _cmd = df.CreateCommand(_con, SQL); IDbDataAdapter da = df.CreateDbAdapter(_cmd); if (da == null) { return(null); } DataSet ds = new DataSet(); da.Fill(ds); return(ds); } catch (Exception e) { e.ToString(); return(null); } }
public void Render(TextWriter writer) { writer.WriteLine("Render Started"); var dataSet = new DataSet(); _dataAdapter.Fill(dataSet); foreach (DataTable table in dataSet.Tables) { foreach (DataColumn column in table.Columns) { writer.Write(column.ColumnName.PadRight(20) + " "); } writer.WriteLine(); foreach (DataRow row in table.Rows) { foreach (DataColumn column in table.Columns) { writer.Write(row[column].ToString().PadRight(20) + " "); } writer.WriteLine(); } } }
internal LsMenu Listar() { try { var m_oMenu = new LsMenu(); sbSQL.Length = 0; sbSQL.Append("SELECT IDPRO018, IDPRO039, IDSUBMENU, NMMENU, NMPAGN"); sbSQL.Append(" FROM TBPRO039 "); oAdapter = ObterDataAdapter(sbSQL.ToString()); ITableMapping objMapping = oAdapter.TableMappings.Add("Table", LsMenu.tbMenu); objMapping.ColumnMappings.Add("IDPRO018", LsMenu.cpCodigoProjeto); objMapping.ColumnMappings.Add("IDPRO039", LsMenu.cpCodigo); objMapping.ColumnMappings.Add("IDSUBMENU", LsMenu.cpSubMenu); objMapping.ColumnMappings.Add("NMMENU", LsMenu.cpNome); objMapping.ColumnMappings.Add("NMPAGN", LsMenu.cpPagina); oAdapter.Fill(m_oMenu); return m_oMenu; } catch (Exception ex) { throw new Dor.Util.OperacaoInvalidaBD(ex); } }
public DataSet ListaExames() { DataSet Ds = new DataSet("DsMedicamentos"); try { //Listar todos os Exames do DW de acordo com a Unidade do usuário Logado. sbSQL.Length = 0; sbSQL.Append("SELECT "); sbSQL.Append("PRODUTOS.IDINT106 AS IDDWD016, "); sbSQL.Append("' ' AS CODIGO, "); sbSQL.Append("TRIM(PRODUTOS.DSGRUPO) || ' - ' || TRIM(PRODUTOS.DSPROCED) AS DESCRICAO "); sbSQL.Append("FROM "); sbSQL.Append("TBINT106 PRODUTOS "); sbSQL.Append("WHERE "); sbSQL.Append("TRIM(PRODUTOS.DSGRUPO) IN("); sbSQL.Append("'METODOS DIAGNOSTICOS POR IMAGEM',"); sbSQL.Append("'MEDICINA NUCLEAR',"); sbSQL.Append("'TOMOGRAFIA COMPUTADORIZADA',"); sbSQL.Append("'MEDICINA LABORATORIAL',"); sbSQL.Append("'ELETROFISIOLOGICOS / MECANICOS E FUNCIONAIS',"); sbSQL.Append("'RESSONANCIA MAGNETICA') "); sbSQL.Append("AND "); sbSQL.Append("NOT PRODUTOS.DSSUBGRP LIKE '%PROCEDIMENT%' "); sbSQL.Append("ORDER BY PRODUTOS.DSSUBGRP,PRODUTOS.DSPROCED "); //sbSQL.Append("SELECT "); //sbSQL.Append("PRODUTOS.IDDWD016, "); //sbSQL.Append("Substr(PRODUTOS.CLPRCD,10,6) AS CODIGO, "); //sbSQL.Append("PRODUTOS.DSPRCD AS DESCRICAO "); //sbSQL.Append("FROM "); //sbSQL.Append("TBDWD016 PRODUTOS "); //sbSQL.Append(" INNER JOIN TBDWD001 UNIDADES ON Substr(PRODUTOS.CLPRCD,1,3) = UNIDADES.NMMNMNUNDD "); //sbSQL.Append(" INNER JOIN TBPRO012 UNIPROCYON ON UNIDADES.IDDWD001 = UNIPROCYON.IDDWD001 "); //sbSQL.Append("WHERE "); //sbSQL.Append("PRODUTOS.FLBLQD = '0' "); //sbSQL.Append("AND "); //sbSQL.Append("PRODUTOS.IDDWD030 = 2 "); //sbSQL.Append("AND "); //sbSQL.Append("UNIPROCYON.IDPRO012 = :UNI "); //sbSQL.Append("ORDER BY "); //sbSQL.Append("PRODUTOS.DSPRCD "); Conectar(); oAdapter = ObterDataAdapter(sbSQL.ToString()); UtDbNet.AdicionarParametro(oAdapter, TratarSQLParametroBanco("UNI").ToString(), UtSessao.Empresa); ITableMapping objMapping = oAdapter.TableMappings.Add("Table", "DsMedicamentos"); objMapping.ColumnMappings.Add("IDDWD016", "IDDWD016"); objMapping.ColumnMappings.Add("CODIGO", "CODIGO"); objMapping.ColumnMappings.Add("DESCRICAO", "DESCRICAO"); oAdapter.Fill(Ds); Desconectar(); return Ds; } catch (Exception en) { m_oLog.CriarArquivoLog(en.Message, UtSessao.Sessao["UNIDADE"].ToString()); throw new Dor.Util.OperacaoInvalidaBD(en); } }
/// <summary> /// Preenche DataTable com as Unidades... /// </summary> /// <returns></returns> public DataSet PreecherDsUnidades() { DataSet Ds = new DataSet("DsUnidades"); try { sbSQL.Length = 0; sbSQL.Append(" SELECT "); sbSQL.Append(" IDDWD001, "); sbSQL.Append(" DSUNDD "); sbSQL.Append(" FROM "); sbSQL.Append(" TBDWD001 "); sbSQL.Append(" ORDER BY IDDWD001 "); Conectar(); oAdapter = ObterDataAdapter(sbSQL.ToString()); ITableMapping objMapping = oAdapter.TableMappings.Add("DtUnidades", "DsUnidades"); objMapping.ColumnMappings.Add("IDDWD001", "IDDWD001"); objMapping.ColumnMappings.Add("DSUNDD", "DSUNDD"); oAdapter.Fill(Ds); Desconectar(); return Ds; } catch (Exception ex) { m_oLog.CriarArquivoLog(ex.Message, UtSessao.Sessao["UNIDADE"].ToString()); throw new Dor.Util.OperacaoInvalidaBD(ex); } }
/// <summary> /// Executa o PS de selectie si returneaza intr-un DataSet inregistrarile ce corespund acestei executii /// </summary> /// <param name="sNumeProcedura">numele procedurii stocate pe care dorim sa o executam</param> /// <param name="lstParametri">lista de parametri pasati PS</param> /// <param name="xTranzactieSQL">Tranzactia in cadrul careia executam PS</param> /// <param name="sConnexionString">poate fi utilizat doar daca nu transmitem tranzactie in parametru deoarece acest connection string este atasat noii tranzactii</param> /// <returns>Un DataSet ce contine rezultatul executiei procedurii stocate</returns> public static DataSet GetDataSetByStoredProc(string sNumeProcedura, List <IDataParameter> lstParametri, IDbTransaction xTranzactieSQL, string sConnexionString) { IDbCommand cmdSqlCommand = CInterfataSQLServer.getNewDataCommand(); IDbDataAdapter adpSqlDataAdapter = CInterfataSQLServer.getNewDataAdaptater(); DataSet dsDataSet = new DataSet(); try { //Configuram comanda using (cmdSqlCommand) { cmdSqlCommand.CommandType = CommandType.StoredProcedure; //precizam ca executam o procedura stocata cmdSqlCommand.CommandText = sNumeProcedura; //numele procedurii stocate //Conexiunea la BDD pe care o vom utiliza if (xTranzactieSQL == null) { IDbTransaction myTrans = GetTransactionOnConnection(sConnexionString); cmdSqlCommand.Connection = myTrans.Connection; cmdSqlCommand.Transaction = myTrans; } else { //Atasam conexiunea si tranzactia comenzii cmdSqlCommand.Connection = xTranzactieSQL.Connection; cmdSqlCommand.Transaction = xTranzactieSQL; } //Stergem parametrii precedenti ai comenzii if (cmdSqlCommand.Parameters != null) { cmdSqlCommand.Parameters.Clear(); } //Adaugam noii parametri la comanda if (lstParametri != null) { foreach (IDataParameter sqlParam in lstParametri) { cmdSqlCommand.Parameters.Add(sqlParam); } } } //Configuram un SqlDataAdapter pentru a folosi SqlCommand si a incarca DataSet-ul adpSqlDataAdapter.SelectCommand = cmdSqlCommand; try { adpSqlDataAdapter.Fill(dsDataSet); } catch (Exception) { //in caz de eroare mai incercam o data adpSqlDataAdapter.Fill(dsDataSet); } //Daca nu am pasat o tranzactie atunci inchidem si facem comit tranzactiei create si utilizate if (xTranzactieSQL == null) { CloseTransactionOnConnection(cmdSqlCommand.Transaction, true); cmdSqlCommand.Connection.Close(); cmdSqlCommand.Connection.Dispose(); } return(dsDataSet); } catch (Exception ex) { if (cmdSqlCommand.Connection != null && cmdSqlCommand.Connection.State == ConnectionState.Open) { //daca nu am transmis tranzactie if (xTranzactieSQL == null) { //Daca exista o tranzactie creata in interiorul acestei metode if (cmdSqlCommand.Transaction != null) { CloseTransactionOnConnection(cmdSqlCommand.Transaction, false); } } cmdSqlCommand.Connection.Close(); cmdSqlCommand.Connection.Dispose(); } throw ex; } finally { cmdSqlCommand.Dispose(); cmdSqlCommand = null; adpSqlDataAdapter = null; dsDataSet = null; } }
//执行最后的sql private string ExecSql(InterfaceModel response, IDbConnection connection, IDbDataAdapter sqlDA, IDbCommand cmd, List <FilterCondition> filters) { XmlHelper xmlHelp = new XmlHelper(); string json = ""; object dataResult = new object(); object result = new object(); switch (response.ExecuteType) { case "Scalar": DataResult dataResult1 = new DataResult(); dataResult1.DataType = 200; dataResult1.Result = cmd.ExecuteScalar(); dataResult = dataResult1; result = dataResult1.Result; break; case "Int": IntDataResult intDataResult = new IntDataResult(); intDataResult.Result = cmd.ExecuteNonQuery(); intDataResult.DataType = 200; dataResult = intDataResult; result = intDataResult.Result; break; case "DataSet": DataSet ds = new DataSet(); DSDataResult dSDataResult = new DSDataResult(); sqlDA.Fill(ds); int lastIndex = ds.Tables.Count - 1; if (filters != null) { //复杂查询需要再过滤一下最后一个表 string msg; string filter = this.CreateFileterString(filters, out msg); if (msg != "") { throw new Exception(msg); } if (ds.Tables.Count > 0 && ds.Tables[lastIndex].Rows.Count > 0) { //只过滤最后一个表 DataTable table = ds.Tables[lastIndex]; table.DefaultView.RowFilter = filter; DataTable dtNew = table.DefaultView.ToTable(); ds.Tables.RemoveAt(lastIndex); ds.Tables.Add(dtNew); } dSDataResult.Result = ds; } else { int nCount = 0; if (this.context.Request.Headers.ContainsKey("NCS")) { bool bOK = context.Request.Headers.TryGetValue("NCS", out StringValues NCS); if (bOK) { nCount = int.Parse(NCS.ToString()); } } if (nCount > 0 && lastIndex >= 0) { DataTable dtNew = DtSelectTop(nCount, ds.Tables[lastIndex]); ds.Tables.RemoveAt(lastIndex); ds.Tables.Add(dtNew); } dSDataResult.Result = ds; } dataResult = dSDataResult; dSDataResult.DataType = 200; break; } if (response.SerializeType == "Xml") { switch (response.ExecuteType) { case "Scalar": json = xmlHelp.SerializeXML <DataResult>(dataResult); break; case "Int": json = xmlHelp.SerializeXML <IntDataResult>(dataResult); break; case "DataSet": json = xmlHelp.SerializeXML <DSDataResult>(dataResult); break; } } else if (response.SerializeType == "Json") { json = JsonConvert.SerializeObject(dataResult); } else { json = result.ToString(); } return(json); }
public DataAdapterExample() { var connectionDetails = ConfigurationManager.ConnectionStrings ["MyDatabase"]; var dbFactory = DbProviderFactories.GetFactory(connectionDetails.ProviderName); Adapter = dbFactory.CreateDataAdapter(); // Create and configure a connection using (var connection = dbFactory.CreateConnection()) { connection.ConnectionString = connectionDetails.ConnectionString; // Create Select Command DbCommand selCmd = dbFactory.CreateCommand(); selCmd.CommandText = "SELECT idMyTable,FieldA FROM MyTable"; selCmd.Connection = connection; Adapter.SelectCommand = selCmd; // Parameters var fieldAParam = dbFactory.CreateParameter(); fieldAParam.DbType = DbType.String; fieldAParam.ParameterName = "@FieldA"; fieldAParam.SourceColumn = "FieldA"; fieldAParam.DbType = DbType.String; var idParam = dbFactory.CreateParameter(); idParam.DbType = DbType.Int32; idParam.ParameterName = "@idMyTable"; idParam.SourceColumn = "idMyTable"; idParam.DbType = DbType.Int32; // Configure Insert Command var insCmd = dbFactory.CreateCommand(); insCmd.CommandText = "INSERT INTO MyTable (FieldA) VALUES (@FieldA)"; insCmd.Parameters.Add(fieldAParam); insCmd.Connection = connection; insCmd.CommandType = CommandType.Text; Adapter.InsertCommand = insCmd; // Delete Command var delCmd = dbFactory.CreateCommand(); delCmd.CommandText = "DELETE FROM MyTable WHERE idMyTable = @idMyTable"; delCmd.Parameters.Add(idParam); delCmd.Connection = connection; delCmd.CommandType = CommandType.Text; Adapter.DeleteCommand = delCmd; // Configure Update Command. var upCmd = dbFactory.CreateCommand(); upCmd.CommandText = "UPDATE MyTable SET FieldA = @FieldA WHERE idMyTable = @idMyTable"; upCmd.Parameters.Add(idParam); upCmd.Parameters.Add(fieldAParam); upCmd.Connection = connection; upCmd.CommandType = CommandType.Text; Adapter.UpdateCommand = upCmd; } Adapter.Fill(DS); }
public DataSet ExecuteDataSet(IDbDataAdapter adapter) { CheckReaders(); Prepare(adapter); DataSet result; try { result = new DataSet(); adapter.Fill(result); } catch { log.Error("Could not execute command: " + adapter.SelectCommand.CommandText); throw new Exception("Could not execute command: " + adapter.SelectCommand.CommandText); } return result; }
private static T Execute <T>(string procedureName, IDictionary <string, object> parameters, IDbTransaction transaction) { IDbConnection cn = transaction == null?GetConnection() : transaction.Connection; #region DELETE //int max = 1; //int initial = 0; //int increment = 0; //if (Configuration.RetryPolicy.Enabled) //{ // max = Configuration.RetryPolicy.Retries; // initial = Configuration.RetryPolicy.InitialWait * 1000; // increment = Configuration.RetryPolicy.Increment * 1000; //} #endregion try { if (transaction == null) { ExecuteWithRetry <object>(() => { cn.Open(); return(null); }); #region DELETE //for (int i = 0; i < max; i++) //{ // try // { // cn.Open(); // break; // } // catch(Exception) // { // Thread.Sleep(initial + (i * increment)); // } //} #endregion } using (IDbCommand cmd = cn.CreateCommand()) { cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = procedureName; if (transaction != null) { cmd.Transaction = transaction; } if (parameters != null && parameters.Keys.Count > 0) { foreach (string key in parameters.Keys) { IDbDataParameter parameter = GetParameter(); parameter.ParameterName = key; parameter.Value = parameters[key] ?? DBNull.Value; cmd.Parameters.Add(parameter); } } T retVal = default(T); #region DELETE //for (int i = 0; i < max; i++) //{ // try // { // ExecuteScalar #endregion if (typeof(T) == typeof(int)) { #region DELETE //retVal = (T)(object)cmd.ExecuteNonQuery(); //break; #endregion retVal = ExecuteWithRetry <T>(() => { return((T)(object)cmd.ExecuteNonQuery()); }); } // ExecuteReader else if (typeof(T) == typeof(IDataReader)) { #region DELETE //retVal = (T)cmd.ExecuteReader(CommandBehavior.CloseConnection); //break; #endregion retVal = ExecuteWithRetry <T>(() => { return((T)cmd.ExecuteReader(CommandBehavior.CloseConnection)); }); } // DataSet else if (typeof(T) == typeof(DataSet)) { DataSet ds = new DataSet(); IDbDataAdapter da = GetAdapter(); da.SelectCommand = cmd; //da.Fill(ds); ExecuteWithRetry <object>(() => { da.Fill(ds); return(null); }); retVal = (T)(object)ds; //break; } else { //retVal = (T)cmd.ExecuteScalar(); retVal = ExecuteWithRetry <T>(() => { return((T)cmd.ExecuteScalar()); }); } #region DELETE //break; // } // catch (Exception) // { // if (i == max - 1) // throw; // Thread.Sleep(initial + (i * increment)); // } //} #endregion return(retVal); } } finally { if (cn != null && transaction == null && typeof(T) != typeof(IDataReader)) { ExecuteWithRetry <object>(() => { cn.Close(); return(null); }); #region DELETE //for (int i = 0; i < max; i++) //{ // try // { // cn.Close(); // break; // } // catch // { // if (i == max - 1) // throw; // Thread.Sleep(initial + (i * increment)); // } //} #endregion cn.Dispose(); } } }
/// <summary> /// 获取表 /// </summary> /// <param name="sql"></param> /// <returns></returns> public DataTable GetDataTable(string sql, List <IDataParameter> paras = null) { IDbConnection conn = null; try { DataSet ds = new DataSet(); conn = GetConnection(); if (conn.State == ConnectionState.Closed) { conn.Open(); } if (conn.State == ConnectionState.Broken) { conn.Close(); conn.Open(); } using (IDbCommand com = GetCommand()) { com.Connection = conn; com.CommandText = sql; com.CommandType = CommandType.Text; if (paras != null && paras.Count > 0) { foreach (var item in paras) { com.Parameters.Add(item); } } IDbDataAdapter da = GetDataAdapter(); da.SelectCommand = com; da.Fill(ds); } return(ds.Tables[0]); } catch (Exception ex) { // LoggerManager.Instance.Error(ex); //日志记录 throw ex;//return null; } finally { if (conn != null)//脏链接废弃,WWJ20180412 { if (conn.State == ConnectionState.Open) { EnqueueConn(conn); } else { try { conn.Close(); } catch (Exception ex) { } finally { conn.Dispose(); conn = null; } } } } }
private DataSet GetTestDataSet(ref Audit auditToRun, int testIndex) { IAuditDbProvider currDbProvider = _providers.Providers[_colAudits.DatabaseProvider]; currDbProvider.ConnectionString = _colAudits.ConnectionString.ToString(); if (_colAudits.ConnectionString.ConnectionTimeout != null) { currDbProvider.ConnectionTimeout = _colAudits.ConnectionString.ConnectionTimeout; } else { currDbProvider.CommandTimeout = 15.ToString(); _colAudits.ConnectionString.ConnectionTimeout = 15.ToString(); } if (_colAudits.ConnectionString.CommandTimeout != null) { currDbProvider.CommandTimeout = _colAudits.ConnectionString.CommandTimeout; } else { currDbProvider.CommandTimeout = 30.ToString(); _colAudits.ConnectionString.CommandTimeout = 30.ToString(); } currDbProvider.CreateDatabaseSession(); var dsAudit = new DataSet(); string sql = BuildSqlStatement(auditToRun, testIndex); CommandType commandType = (CommandType)0; if (auditToRun.Test.SqlType == Audit.SqlStatementTypeEnum.SqlText) { commandType = CommandType.Text; } else if (auditToRun.Test.SqlType == Audit.SqlStatementTypeEnum.StoredProcedure) { commandType = CommandType.StoredProcedure; } IDbCommand cmdAudit = currDbProvider.CreateDbCommand(sql, commandType, int.Parse(_colAudits.ConnectionString.CommandTimeout)); IDbDataAdapter daAudit = currDbProvider.CreateDbDataAdapter(cmdAudit); string intConnectionTimeout = _colAudits.ConnectionString.ConnectionTimeout; string intCommandTimeout = _colAudits.ConnectionString.CommandTimeout; try { daAudit.Fill(dsAudit); } catch (Exception ex) { int intFound = 0; string strMsg = null; strMsg = ex.Message; intFound = (strMsg.IndexOf("Timeout expired.", 0, StringComparison.Ordinal) + 1); if (intFound == 1) { auditToRun.Test.FailedMessage = "Timeout expired while running this audit. The connection timeout was " + intConnectionTimeout.ToString(CultureInfo.InvariantCulture) + " seconds. The command timeout was " + intCommandTimeout + " seconds."; auditToRun.ErrorMessages.Add(auditToRun.Test.FailedMessage); } else { auditToRun.Test.FailedMessage = strMsg; auditToRun.ErrorMessages.Add(strMsg); } auditToRun.WasSuccessful = false; } finally { cmdAudit.Dispose(); } return(dsAudit); }
public static IDbDataAdapter CarregaDados(DataSet dsDados, BindingSource bdsDados, string nomeTabela, string filtro, string join, List <FieldDataGrid> colunasDataGridView, DataGridView dgvDados, bool geraCamposSQL) { try { string sql = geraCamposSQL ? "SELECT " : "SELECT *"; if (colunasDataGridView == null) { sql += "*"; } else { bool first = true; if (geraCamposSQL) { foreach (FieldDataGrid campoBD in colunasDataGridView) { if (first) { sql += campoBD.FieldName; first = false; } else { sql += ", " + campoBD.FieldName; } } } } sql += " FROM " + nomeTabela + " " + join + " " + filtro; IDbDataAdapter adapter = GetDataAdapter(sql); DbCommandBuilder commandBuilder = GetCommandBuilder(); commandBuilder.DataAdapter = (DbDataAdapter)adapter; adapter.Fill(dsDados); bdsDados.DataSource = dsDados; bdsDados.DataMember = dsDados.Tables[0].TableName; dgvDados.DataSource = bdsDados; if (colunasDataGridView != null) { dgvDados.Columns.Clear(); dgvDados.Update(); foreach (FieldDataGrid campoBD in colunasDataGridView) { dgvDados.Columns.Add(new DataGridViewTextBoxColumn { DataPropertyName = campoBD.FieldName, HeaderText = campoBD.FieldAlias, Name = campoBD.FieldName, ReadOnly = true, Width = campoBD.Visible ? campoBD.FieldWidth : -1, Visible = campoBD.Visible }); } dgvDados.Update(); } return(adapter); } catch (Exception ex) { throw new Exception("Erro ao carregar os dados.\nDetalhes: " + ex.Message); } }
private OperationReturn ModifyOrgInfo(SessionInfo session, List <string> listParams) { OperationReturn optReturn = new OperationReturn(); optReturn.Result = true; optReturn.Code = 0; try { if (listParams.Count != 6) { optReturn.Result = false; optReturn.Code = Defines.RET_PARAM_INVALID; optReturn.Message = string.Format("Param count invalid"); return(optReturn); } //0 OrgID //1 OrgName //2 OrgType //3 OrgDesc //4 IsActive //5 ObjParentID string orgID = listParams[0]; string orgName = EncryptToDB(listParams[1]); string orgType = listParams[2]; string orgDesc = listParams[3]; string IsActive = listParams[4]; string rentToken = session.RentInfo.Token; string orgParentId = listParams[5]; string strSql; //判断同级父机构下是否有同名的机构 IDbConnection objConn = null; IDbDataAdapter objAdapter = null; DataSet objDataSet = new DataSet(); switch (session.DBType) { //MSSQL case 2: strSql = string.Format("SELECT * FROM T_11_006_{0} WHERE C002='{1}' AND C004='{2}' AND C001<>{3} ", rentToken, orgName, orgParentId, orgID); objConn = MssqlOperation.GetConnection(session.DBConnectionString); objAdapter = MssqlOperation.GetDataAdapter(objConn, strSql); break; //ORCL case 3: strSql = string.Format("SELECT * FROM T_11_006_{0} WHERE C002='{1}' AND C004='{2}' AND C001 <>{3} ", rentToken, orgName, orgParentId, orgID); objConn = OracleOperation.GetConnection(session.DBConnectionString); objAdapter = OracleOperation.GetDataAdapter(objConn, strSql); break; } if (objConn == null || objAdapter == null) { optReturn.Result = false; optReturn.Code = Defines.RET_OBJECT_NULL; optReturn.Message = string.Format("DBConnection or DBDataAdapter is null"); return(optReturn); } objAdapter.Fill(objDataSet); if (objDataSet != null && objDataSet.Tables[0].Rows.Count > 0) { //同名机构名称 optReturn.Result = false; optReturn.Code = Defines.RET_DBACCESS_EXIST; optReturn.Message = string.Format("EXIST"); return(optReturn); } switch (session.DBType) { //MSSQL case 2: strSql = string.Format( "UPDATE T_11_006_{0} SET C002 = '{1}', C003 = {2}, C012 = '{3}',C005='{5}' WHERE C001 = {4}", rentToken, orgName, orgType, orgDesc, orgID, IsActive); optReturn = MssqlOperation.ExecuteSql(session.DBConnectionString, strSql); if (!optReturn.Result) { return(optReturn); } break; //ORCL case 3: strSql = string.Format( "UPDATE T_11_006_{0} SET C002 = '{1}', C003 = {2}, C012 = '{3}' ,C005='{5}' WHERE C001 = {4}", rentToken, orgName, orgType, orgDesc, orgID, IsActive ); optReturn = OracleOperation.ExecuteSql(session.DBConnectionString, strSql); if (!optReturn.Result) { return(optReturn); } break; } } catch (Exception ex) { optReturn.Result = false; optReturn.Code = Defines.RET_FAIL; optReturn.Message = ex.Message; } return(optReturn); }
private string ExportMdb(string sql, DataTable exportTable, int incidOrdinal, List <int> fieldCountList, out int exportRowCount) { exportRowCount = -1; DbOleDb dbOut = null; string tempPath = String.Empty; try { tempPath = Path.GetTempPath(); } catch { tempPath = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData); } tempPath = Path.Combine(tempPath, Path.GetFileNameWithoutExtension(Path.GetRandomFileName()) + ".mdb"); try { if (File.Exists(tempPath)) { File.Delete(tempPath); } OdbcCP32 odbc = new OdbcCP32(); odbc.CreateDatabase(tempPath); string connString = String.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};", tempPath); string defaultSchema = ""; bool promptPwd = false; dbOut = new DbOleDb(ref connString, ref defaultSchema, ref promptPwd, Properties.Resources.PasswordMaskString, Settings.Default.UseAutomaticCommandBuilders, true, Settings.Default.DbIsUnicode, Settings.Default.DbUseTimeZone, 255, Settings.Default.DbBinaryLength, Settings.Default.DbTimePrecision, Settings.Default.DbNumericPrecision, Settings.Default.DbNumericScale); dbOut.CreateTable(exportTable); DataSet datasetOut = new DataSet("Export"); IDbDataAdapter adapterOut = dbOut.CreateAdapter(exportTable); adapterOut.Fill(datasetOut); int[] pkOrdinals = exportTable.PrimaryKey.Select(c => c.Ordinal).ToArray(); exportTable.PrimaryKey = pkOrdinals.Select(o => exportTable.Columns[o]).ToArray(); adapterOut.TableMappings.Clear(); adapterOut.TableMappings.Add(exportTable.TableName, datasetOut.Tables[0].TableName); exportTable = datasetOut.Tables[0]; DataRow exportRow = null; bool rowAdded = false; using (IDataReader reader = _viewModelMain.DataBase.ExecuteReader(sql, _viewModelMain.DataBase.Connection.ConnectionTimeout, CommandType.Text)) { int runTotal = 0; int[][] fieldMapTemplate = fieldCountList.Select((i, index) => new int[] { index, index + runTotal, i, (runTotal += i - (i > 0 ? 1 : 0)) }) .Select(e => new int[] { e[0], e[1], e[1] + e[2] }).ToArray(); int[] dupsAllowed = (from e in exportTable.Columns.Cast <DataColumn>() let q = from c in _viewModelMain.HluDataset.incid_sources.Columns.Cast <DataColumn>() where !Regex.IsMatch(c.ColumnName, @"(\Aincid\z|_(importance|id)\z)", RegexOptions.IgnoreCase) select c.ColumnName where q.Count(n => Regex.IsMatch(e.ColumnName, n + @"(_[0-9]+)*\z", RegexOptions.IgnoreCase)) == 1 select e.Ordinal).ToArray(); int[][] fieldMap = new int[fieldMapTemplate.Length][]; string currIncid = String.Empty; string prevIncid = String.Empty; while (reader.Read()) { currIncid = reader.GetString(incidOrdinal); if (currIncid != prevIncid) { prevIncid = currIncid; fieldMap = fieldMapTemplate.Select(a => new int[] { a[0], a[1], a[2] }).ToArray(); if (exportRow != null) { exportTable.Rows.Add(exportRow); rowAdded = true; } exportRow = exportTable.NewRow(); rowAdded = false; for (int i = 0; i < fieldMap.GetLength(0); i++) { object item = reader.GetValue(fieldMap[i][0]); if (item != DBNull.Value) { exportRow[fieldMap[i][1]] = reader.GetValue(fieldMap[i][0]); } fieldMap[i][1]++; } } else { for (int i = 0; i < fieldMap.GetLength(0); i++) { if (fieldMap[i][1] < fieldMap[i][2]) { object item = reader.GetValue(fieldMap[i][0]); if ((item != DBNull.Value) && (!item.Equals(exportRow[fieldMap[i][1] - 1]) || (Array.IndexOf(dupsAllowed, fieldMap[i][1]) != -1))) { exportRow[fieldMap[i][1]++] = item; } } } } } } if (!rowAdded && (exportRow != null)) { exportTable.Rows.Add(exportRow); } exportRowCount = adapterOut.Update(datasetOut); return(exportRowCount != -1 ? tempPath : null); } catch { if (File.Exists(tempPath)) { try { File.Delete(tempPath); } catch { _viewModelMain.ExportMdbs.Add(tempPath); } } return(null); } finally { if ((dbOut != null) && (dbOut.Connection.State != ConnectionState.Closed)) { try { dbOut.Connection.Close(); } catch { } } } }
public static DataSet GetDataSetByComandaDirecta(string sComandaDirecta, BColectieParametriSQL pListaParametri, IDbTransaction pTranzactieSQL, string pConnexionString) { IDbCommand cmdSqlCommand = CInterfataSQLServer.getNewDataCommand(); IDbDataAdapter adpSqlDataAdapter = CInterfataSQLServer.getNewDataAdaptater(); DataSet dsDataSet = new DataSet(); try { //Configuram comanda using (cmdSqlCommand) { cmdSqlCommand.CommandType = CommandType.Text; //precizam ca executam o comanda directa cmdSqlCommand.CommandText = sComandaDirecta; //textul comenzii directe //Conexiunea la BDD pe care o vom utiliza if (pTranzactieSQL == null) { IDbTransaction myTrans = GetTransactionOnConnection(pConnexionString); cmdSqlCommand.Connection = myTrans.Connection; cmdSqlCommand.Transaction = myTrans; } else { //Atasam conexiunea si tranzactia comenzii cmdSqlCommand.Connection = pTranzactieSQL.Connection; cmdSqlCommand.Transaction = pTranzactieSQL; } //Adaugam noii parametri la comanda if (pListaParametri != null) { pListaParametri.AdaugaParametriiLaComanda(cmdSqlCommand); } } //Configuram un SqlDataAdapter pentru a folosi SqlCommand si a incarca DataSet-ul adpSqlDataAdapter.SelectCommand = cmdSqlCommand; adpSqlDataAdapter.Fill(dsDataSet); //Daca nu am pasat o tranzactie atunci inchidem si facem comit tranzactiei create si utilizate if (pTranzactieSQL == null) { CloseTransactionOnConnection(cmdSqlCommand.Transaction, true); cmdSqlCommand.Connection.Close(); cmdSqlCommand.Connection.Dispose(); } return(dsDataSet); } catch (Exception ex) { if (cmdSqlCommand.Connection != null && cmdSqlCommand.Connection.State == ConnectionState.Open) { //daca nu am transmis tranzactie if (pTranzactieSQL == null) { //Daca exista o tranzactie creata in interiorul acestei metode if (cmdSqlCommand.Transaction != null) { CloseTransactionOnConnection(cmdSqlCommand.Transaction, false); } } cmdSqlCommand.Connection.Close(); cmdSqlCommand.Connection.Dispose(); } throw ex; } finally { cmdSqlCommand.Dispose(); cmdSqlCommand = null; adpSqlDataAdapter = null; dsDataSet = null; } }
internal LsPerfil Listar(long v_iCodigotema) { try { var m_oPerfil = new LsPerfil(); sbSQL.Length = 0; sbSQL.Append("SELECT DSAPLDPERF, perfil.IDPRO016, NMPERF"); sbSQL.Append(" FROM TBPRO016 perfil, TBPRO027 temaperfil"); sbSQL.Append(" WHERE perfil.IDPRO016 = temaperfil.IDPRO016"); sbSQL.Append(" AND temaperfil.IDPRO002 = :IDPRO002"); oAdapter = ObterDataAdapter(sbSQL.ToString()); UtDbNet.AdicionarParametro(oAdapter, TratarSQLParametroBanco("IDPRO002").ToString(), v_iCodigotema); ITableMapping objMapping = oAdapter.TableMappings.Add("Table", LsPerfil.tbPerfil); objMapping.ColumnMappings.Add("DSAPLDPERF", LsPerfil.cpApelido); objMapping.ColumnMappings.Add("IDPRO016", LsPerfil.cpCodigo); objMapping.ColumnMappings.Add("NMPERF", LsPerfil.cpNome); oAdapter.Fill(m_oPerfil); return m_oPerfil; } catch (Exception ex) { throw new Dor.Util.OperacaoInvalidaBD(ex); } }
public object DoInDataAdapter(IDbDataAdapter dataAdapter) { dataAdapter.SelectCommand.CommandType = commandType; dataAdapter.SelectCommand.CommandText = sql; //TODO investigate performance of cloning....would need to change signature to // DataTableMapping[] otherwise... foreach (DataTableMapping dataTableMapping in mappingCollection) { dataAdapter.TableMappings.Add(((ICloneable)dataTableMapping).Clone()); } ParameterUtils.CopyParameters(dataAdapter.SelectCommand, parameters); //TODO Review these lifecycle hooks... if (dataAdapterSetter != null) { dataAdapterSetter.SetValues(dataAdapter); } if (fillLifecycleProcessor != null) { fillLifecycleProcessor.BeforeFill(dataSet, dataAdapter.TableMappings); } int returnVal; if (containsDataSet) { returnVal = dataAdapter.Fill(dataSet); } else { //TODO should query metadata to see if supports filling dataTable directly. if (dataAdapter is DbDataAdapter) { returnVal = ((DbDataAdapter)dataAdapter).Fill(dataTable); } else { //TODO could create DataSet and extract DataTable... for now just throw throw new DataException("Provider does not support filling DataTable directly"); } } ParameterUtils.CopyParameters(parameters, dataAdapter.SelectCommand); if (fillLifecycleProcessor != null) { fillLifecycleProcessor.AfterFill(dataSet, dataAdapter.TableMappings); } return returnVal; }
internal static DataSet ExecuteSelect(EDtxReaderType connType, int timeout, IDbConnection conn, string cmdText, ParamHelper[] paramList, ref TimeSpan executionTime) { DateTime started = DateTime.Now; IDataReader reader = null; IDbCommand cmd = null; bool bConnOpenedHere = EnsureConnectionOpen(conn); IDbTransaction transaction = conn.BeginTransaction(); DataSet retv = new DataSet(); try { cmd = conn.CreateCommand(); cmd.CommandTimeout = timeout; cmd.Transaction = transaction; cmd.CommandText = cmdText; DbType[] reqLenTypes = new DbType[] { DbType.AnsiString, DbType.AnsiStringFixedLength, DbType.String, DbType.StringFixedLength, DbType.Binary, DbType.Object, DbType.Xml }; for (int i = 0; i < paramList.Length; i++) { IDbDataParameter idb = cmd.CreateParameter(); cmd.Parameters.Add(BuildParameter(idb, paramList[i])); if (reqLenTypes.Contains(idb.DbType)) { if (idb is OdbcParameter) { (idb as OdbcParameter).Size = paramList[i].Size; } else if (idb is CtreeSqlParameter) { (idb as CtreeSqlParameter).Size = paramList[i].Size; } } } switch (connType) { case EDtxReaderType.Adapter: case EDtxReaderType.FaircomAdapter: { IDbDataAdapter adap = GetAdapter(connType, cmd); retv.BeginInit(); //Since the FillSchema and Fill functions return the same instance of 'DataTable' //There is probably a better way of doing this, but for sake of explination //Read the db schema bool bSchemaFound = false; DataTable[] dta = adap.FillSchema(retv, SchemaType.Source); DataTable clone = null; if (dta.Length > 0) { bSchemaFound = true; dta[0].TableName = "SchemaTable"; //Ensure the table is named 'SchemaTable' retv.Tables.Remove(dta[0]); //Drop the table from the dataset clone = dta[0].Clone(); //Clone the results dta[0].TableName = "Table"; //Rename the datatable instance back to table } adap.Fill(retv); //Fill 'Table' with the actual results if (bSchemaFound && clone != null) { retv.Tables.Add(clone); //Now add the 'schematable' back to the results } retv.EndInit(); break; } default: { DataTable dt; reader = cmd.ExecuteReader(); if (reader.FieldCount > 0) { retv.Tables.Add(dt = new DataTable("Table")); retv.Tables.Add(reader.GetSchemaTable()); switch (connType) { case EDtxReaderType.FaircomReader: case EDtxReaderType.Reader: { dt.Load(reader, LoadOption.OverwriteChanges); } break; case EDtxReaderType.FaircomReaderSafe: case EDtxReaderType.ReaderSafe: default: { bool columnsBuilt = false; while (reader.Read()) { if (columnsBuilt == false) { BuildColumnData(dt, reader); columnsBuilt = true; } AddDataRow(dt, reader); } } break; } } break; } } transaction.Commit(); executionTime = DateTime.Now - started; //Now update parameter inputs with their respective values for (int i = 0; i < paramList.Length; i++) { IDbDataParameter p = (IDbDataParameter)cmd.Parameters[i]; if (p.Direction != ParameterDirection.Input) { paramList[i].OutValue = p.Value; } } } catch (Exception e) { transaction.Rollback(); executionTime = DateTime.Now - started; HelperFunctions.LogException(e, false); throw e; } finally { if (reader != null) { if (!reader.IsClosed) { reader.Close(); } reader.Dispose(); } if (cmd != null) { cmd.Dispose(); } if (bConnOpenedHere) { conn.Close(); } } return(retv); }
private void Test_simpleButton_Click(object sender, EventArgs e) { string query = this.Query_memoEdit.Text.Replace(Environment.NewLine, " ").Replace("\t", " "); if (query.Length == 0) { return; } if (!query.ToUpper().Contains("BEGIN") && !query.ToUpper().StartsWith("SELECT TOP ")) { //REPLACES ALL SELECTS //query = query.ToUpper().Replace("SELECT ", "SELECT TOP 20 "); var regex = new System.Text.RegularExpressions.Regex(System.Text.RegularExpressions.Regex.Escape("SELECT")); query = regex.Replace(query, "SELECT TOP 20 ", 1); } if (da != null) { da.SelectCommand.Parameters.Clear(); } da = CreateDataAdapter(((NewStandardReportWizard)this.standardWizard).ConnectionString, query); foreach (SqlParameter param in Parameters) { da.SelectCommand.Parameters.Add(param); } ds.Reset(); try { da.Fill(ds); if (da != null) { da.SelectCommand.Parameters.Clear(); } da = CreateDataAdapter(((NewStandardReportWizard)this.standardWizard).ConnectionString, this.Query_memoEdit.Text.Replace(Environment.NewLine, " ").Replace("\t", " ")); foreach (SqlParameter param in Parameters) { da.SelectCommand.Parameters.Add(param); } } catch (Exception ex) { CDS.Client.Desktop.Essential.BaseAlert.ShowAlert("Invalid Query", ex.Message, CDS.Client.Desktop.Essential.BaseAlert.Buttons.Ok, CDS.Client.Desktop.Essential.BaseAlert.Icons.Error); return; //if (CDS.Shared.Exception.UserInterfaceExceptionHandler.HandleException(ref ex)) { throw ex; } //MessageBox.Show(ex.Message); //return; } this.dataGridView1.Columns.Clear(); this.dataGridView1.DataSource = ds.Tables[0]; this.dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells; this.dataGridView1.Refresh(); // this.dataGridView1.Columns[this.dataGridView1.ColumnCount - 1].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill; }
/// <summary> /// 填充dataset,并生成一个名为Table的DataTable /// </summary> /// <param name="sql">查询SQL</param> /// <param name="dataset">dataset</param> /// <param name="param">参数</param> /// <param name="isStoredProcedure">是否是存储过程</param> /// <returns></returns> public int Fill(string sql, DataSet dataset, IDataParameter[] param, bool isStoredProcedure) { try { #if DEBUG sw.Reset(); sw.Start(); #endif if (DBConnector.Connection.State == ConnectionState.Closed) { DBConnector.Connection.Open(); } IDbCommand cmd = DBConnector.GetCommand(sql, param); //是否是事务 if (isTranscation) { cmd.Transaction = transaction; } //是否是存储过程 if (isStoredProcedure) { cmd.CommandType = CommandType.StoredProcedure; } else { cmd.CommandType = CommandType.Text; } IDbDataAdapter adapter = GetAdapter(cmd); return(adapter.Fill(dataset)); } catch (Exception e) { throw e; } finally { #if DEBUG sw.Stop(); if (isStoredProcedure) { StringBuilder b = new StringBuilder(); foreach (var item in param) { b.Append(item.ParameterName + ":" + item.Value.ToString() + ";"); } LogD(string.Format("EXEC:StoredProcedure[{0}],Parameter[{1}], Elapsed:[{2}]", sql, b.ToString(), sw.ElapsedMilliseconds)); } else { LogD(string.Format("EXEC:Sql[{0}], Elapsed:[{1}]", sql, sw.ElapsedMilliseconds)); } #endif if (!isTranscation) { DBConnector.Connection.Close(); } } }
//返因LayUI的结构 private string ExecSqlLayui(InterfaceModel response, IDbConnection connection, IDbDataAdapter sqlDA, IDbCommand cmd, List <FilterCondition> filters) { XmlHelper xmlHelp = new XmlHelper(); string json = ""; LayuiResult dataResult = new LayuiResult(); dataResult.code = 0; dataResult.count = 0; dataResult.msg = ""; dataResult.data = "{0}"; object result = new object(); switch (response.ExecuteType) { case "Scalar": result = cmd.ExecuteScalar(); break; case "Int": result = cmd.ExecuteNonQuery(); break; case "DataSet": DataSet ds = new DataSet(); sqlDA.Fill(ds); DataTable table = new DataTable(); if (ds.Tables.Count > 0) { if (filters != null) { //复杂查询需要再过滤一下最后一个表 string msg; string filter = this.CreateFileterString(filters, out msg); if (msg != "") { throw new Exception(msg); } //只过滤最后一个表 DataTable table0 = ds.Tables[ds.Tables.Count - 1]; table0.DefaultView.RowFilter = filter; DataTable dtNew = table0.DefaultView.ToTable(); table = dtNew; } else { table = ds.Tables[0]; } //测试数据最返最后10行 int nCount = 0; if (this.context.Request.Headers.ContainsKey("NCS")) { bool bOK = context.Request.Headers.TryGetValue("NCS", out StringValues NCS); if (bOK) { nCount = int.Parse(NCS.ToString()); } } if (nCount > 0) { table = DtSelectTop(nCount, table); } } dataResult.count = table.Rows.Count; result = table; break; } dataResult.data = result; if (response.SerializeType == "Xml") { } else if (response.SerializeType == "Json") { json = JsonConvert.SerializeObject(dataResult); } else { json = result.ToString(); } return(json); }
/// <summary> /// 添加或更新邮件通知参数 /// </summary> /// <param name="session"></param> /// <param name="lstParams"></param> /// lstParams[0] : 是否启用邮件通知 /// lstParams[1] : SMTP /// lstParams[2] : SMTP Port /// lstParams[3] : 需要SSL /// lstParams[4] : 账号 /// lstParams[5] : 密码 /// lstParams[6] : 需要身份认证 /// lstParams[7] : Email Address /// <returns></returns> private OperationReturn UpdateEmailInfo(SessionInfo session, List <string> lstParams) { OperationReturn optReturn = new OperationReturn(); try { string rentToken = session.RentInfo.Token; string strSql = string.Empty; lstParams[5] = S2400EncryptOperation.EncryptWithM002(lstParams[5]); IDbConnection objConn = null; IDbDataAdapter objAdapter = null; DbCommandBuilder objCmdBuilder = null; switch (session.DBType) { case 2: strSql = string.Format("SELECT * FROM T_11_001_{0} WHERE C001 = '{1}' and C002 =24 and C004 = 24041 ORDER BY C005", rentToken, session.RentID); optReturn = MssqlOperation.GetDataSet(session.DBConnectionString, strSql); if (!optReturn.Result) { return(optReturn); } objConn = MssqlOperation.GetConnection(session.DBConnectionString); objAdapter = MssqlOperation.GetDataAdapter(objConn, strSql); objCmdBuilder = MssqlOperation.GetCommandBuilder(objAdapter); break; case 3: strSql = string.Format("SELECT * FROM T_11_001_{0} WHERE C001 = '{1}' AND C002 =24 AND C004 = 24041 ORDER BY C005", rentToken, session.RentID); optReturn = OracleOperation.GetDataSet(session.DBConnectionString, strSql); if (!optReturn.Result) { return(optReturn); } objConn = OracleOperation.GetConnection(session.DBConnectionString); objAdapter = OracleOperation.GetDataAdapter(objConn, strSql); objCmdBuilder = OracleOperation.GetCommandBuilder(objAdapter); break; } if (objConn == null || objAdapter == null || objCmdBuilder == null) { optReturn.Result = false; optReturn.Code = Defines.RET_OBJECT_NULL; optReturn.Message = string.Format("Db object is null"); return(optReturn); } objCmdBuilder.ConflictOption = ConflictOption.OverwriteChanges; objCmdBuilder.SetAllValues = false; DataSet objDataSet = new DataSet(); objAdapter.Fill(objDataSet); List <string> listMsg = new List <string>(); if (lstParams[0] == "0") { DataRow[] drs = objDataSet.Tables[0].Select("c003 = 240401"); if (drs.Count() > 0) { drs[0]["C006"] = lstParams[0]; drs[0]["C018"] = DateTime.Now.ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss"); drs[0]["C019"] = session.UserID; listMsg.Add("Update Email Enable : 0"); } } else { for (int i = 0; i < lstParams.Count; i++) { string strParamID = "24040" + (i + 1); DataRow[] drs = objDataSet.Tables[0].Select(string.Format("c003 = {0}", strParamID)); //如果存在 则修改 if (drs.Count() > 0) { drs[0]["C006"] = lstParams[i]; drs[0]["C018"] = DateTime.Now.ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss"); drs[0]["C019"] = session.UserID; listMsg.Add(string.Format("Update {0}", lstParams[i])); } //如果不存在 则插入 else { DataRow row = objDataSet.Tables[0].NewRow(); row["C001"] = session.RentID; row["C002"] = 24; row["C003"] = "24040" + (i + 1); row["C004"] = "24041"; row["C005"] = i; row["C006"] = lstParams[i]; row["C007"] = 13; row["C009"] = 0; row["C010"] = 0; row["C011"] = 0; row["C015"] = 0; row["C018"] = DateTime.Now.ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss"); row["C019"] = session.UserID; row["C020"] = -1; row["C021"] = DateTime.Now.ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss"); objDataSet.Tables[0].Rows.Add(row); listMsg.Add(string.Format("Add {0}", lstParams[i])); } } } objAdapter.Update(objDataSet); objDataSet.AcceptChanges(); optReturn.Data = listMsg; optReturn.Result = true; optReturn.Code = Defines.RET_SUCCESS; } catch (Exception ex) { optReturn.Result = false; optReturn.Code = Defines.RET_FAIL; optReturn.Message = ex.Message; optReturn.Exception = ex; } return(optReturn); }
public static DataTable GetDataTable(IDbConnection conn, IDbDataAdapter dataAdapter, string sqlTemplate, params string[] parameterValues) { var data = new DataSet(); IDbCommand cmd = conn.CreateCommand(); cmd.CommandText = parameterValues == null ? sqlTemplate : string.Format(sqlTemplate, parameterValues); dataAdapter.SelectCommand = cmd; dataAdapter.Fill(data); return data.Tables[0]; }
/// <summary> /// 数据导入. /// </summary> /// <param name="tableName"></param> public void DoImp(string tableName) { Console.WriteLine("从 XML 文件中,读取数据到 DataTable 里面。"); DataTable dtXml = new DataTable(); dtXml.ReadXmlSchema(GetXmlSchema(tableName)); dtXml.ReadXml(GetXml(tableName)); using (IDbConnection conn = GetDbConnection(ConnString)) { // 创建一个适配器 IDbDataAdapter adapter = GetDbDataAdapter(GetExpSql(tableName), conn); // 创建DataSet,用于存储数据. DataSet resultDataSet = new DataSet(); // 表结构定义导入DataSet. adapter.FillSchema(resultDataSet, SchemaType.Source); // 数据导入DataSet. adapter.Fill(resultDataSet); // 这些数据此时作为 DataSet 的 Tables 集合内独立的 DataTable 对象来提供。 // 如果在对 FillSchema 和 Fill 的调用中指定了一个表名, // 则可以使用该名称访问您需要的特定表。 DataTable dtDatabase = resultDataSet.Tables[0]; Console.WriteLine("将数据写入到数据库中..."); // 遍历 xml 文件中的每一行. foreach (DataRow xmlRow in dtXml.Rows) { // 从 DataTable 获取新的 DataRow 对象。 DataRow drCurrent = dtDatabase.NewRow(); // 每一列依次赋值. for (int i = 0; i < dtDatabase.Columns.Count; i++) { // 取得列名. string colName = dtDatabase.Columns[i].ColumnName; // 赋值. drCurrent[colName] = xmlRow[colName]; } // 将新的对象传递给 DataTable.Rows 集合的 Add 方法。 dtDatabase.Rows.Add(drCurrent); } // 通过 SqlCommandBuilder 设置 DataAdapter 对象的 InsertCommand、UpdateCommand 和 DeleteCommand 属性。 // 注意:表必须有主键信息 DoSqlCommandBuilder(adapter); // 更新原始数据库,可将 DataSet 传递到 DataAdapter 对象的 Update 方法。 adapter.Update(resultDataSet); } Console.WriteLine("处理完毕!"); }
public DataSet PreecherDsCID() { DataSet Ds = new DataSet("DsCid"); try { sbSQL.Length = 0; sbSQL.Append(" SELECT "); sbSQL.Append(" IDDWD057, "); sbSQL.Append(" CDCID, "); sbSQL.Append(" DSCID, "); sbSQL.Append(" TO_CHAR(IDDWD057) || ';' || CDCID As Valor"); sbSQL.Append(" FROM "); sbSQL.Append(" TBDWD057 "); // sbSQL.Append(" ORDER BY CDCID,DSCID "); Conectar(); oAdapter = ObterDataAdapter(sbSQL.ToString()); ITableMapping objMapping = oAdapter.TableMappings.Add("DtCid", "DsCid"); objMapping.ColumnMappings.Add("IDDWD057", "IDDWD057"); objMapping.ColumnMappings.Add("CDCID", "CDCID"); objMapping.ColumnMappings.Add("DSCID", "DSCID"); objMapping.ColumnMappings.Add("Valor", "Valor"); oAdapter.Fill(Ds); Desconectar(); return Ds; } catch (Exception ex) { m_oLog.CriarArquivoLog(ex.Message, UtSessao.Sessao["UNIDADE"].ToString()); throw new Dor.Util.OperacaoInvalidaBD(ex); } }
private DataTable GetAppMenuList(IDbDataAdapter adapter, string UserID, string countryCODE, ClientType ClientType) { try { //String Org_Codes = ""; //for (int i = 0; i < Org_ID.Count; i++) //{ // Org_Codes += "'" + Org_ID[i].ToString() + "'"; // if (i != Org_ID.Count - 1) // Org_Codes += ","; //} String client_type = String.Empty; if (ClientType == ClientType.DeskTop) { client_type = "D"; } else if (ClientType == ClientType.Browser) { client_type = "B"; } DataSet ds = new DataSet(); // adapter.SelectCommand.CommandText = @"SELECT menu_id, parent_menu_id, menu_display_name, menu_page_heading, menu_sort_id,menu_type,transaction_label,is_active, // transaction_page_name,menu_color,layout_id,is_default, menu_link, href_id FROM menu_master WHERE (menu_id IN (SELECT DISTINCT menu_id // FROM group_rights WHERE (group_id IN (SELECT group_id FROM user_group_rights WHERE (user_id = @user_id) ) )) ) "; adapter.SelectCommand.CommandText = @"SELECT menu_id, parent_menu_id, menu_display_name, menu_page_heading, menu_sort_id,menu_type,transaction_label,is_active, transaction_page_name,menu_color,layout_id,is_default, menu_link, href_id FROM menu_master WHERE (menu_id IN (SELECT DISTINCT menu_id FROM group_rights WHERE (group_id IN (SELECT group_id FROM user_group_rights WHERE (user_id = @user_id AND country_code = '" + countryCODE + "') ) )) ) "; //adapter.SelectCommand.CommandText = @"SELECT menu_id, pmenu_id, parameter, sort_id, transaction_type, menu_desc, window_name FROM menu_mst WHERE (menu_id IN (SELECT DISTINCT menu_id FROM group_rights WHERE (group_id IN (SELECT group_id FROM user_group_rights WHERE (user_id = @user_id) AND (country_code IN ()) AND (menu_mst.client_type = 'B') AND (start_date <= @today) AND (end_date > @today OR end_date IS NULL))) AND (start_date <= @today) AND (end_date > @today OR end_date IS NULL)))"; adapter.SelectCommand.Parameters.Clear(); IDataParameter parma1 = DBObjectFactory.GetParameterObject(); parma1.ParameterName = "@today"; parma1.DbType = DbType.DateTime; parma1.Value = System.DateTime.UtcNow; IDataParameter parma2 = DBObjectFactory.GetParameterObject(); parma2.ParameterName = "@user_id"; parma2.DbType = DbType.String; parma2.Value = UserID; adapter.SelectCommand.Parameters.Add(parma1); adapter.SelectCommand.Parameters.Add(parma2); adapter.Fill(ds); //ds.Tables[0].TableName = "dt_menu_info"; //MessageBox.Show("fill",ds.Tables[0].Rows.Count.ToString()); if (ds.Tables[0].Rows.Count > 0) { //MessageBox.Show("return"); return(ds.Tables[0]); } else { return(null); } } catch (Exception ex) { ServerLog.Log(ex.Message + " " + ex.StackTrace); return(null); //throw ExceptionManager.Help(ex, null); } }
public DataSet ListaEspecialidades() { DataSet Ds = new DataSet("DsMedicamentos"); try { sbSQL.Length = 0; sbSQL.Append(" Select DISTINCT "); sbSQL.Append(" dw10.dsespc, "); sbSQL.Append(" dw10.IDDWD010 "); sbSQL.Append(" From "); sbSQL.Append(" TBDWD010 dw10 INNER JOIN TBDWD001 dw01 ON Substr(dw10.CLESPC,1,3) = dw01.NMMNMNUNDD "); sbSQL.Append(" INNER JOIN TBPRO012 pr12 ON pr12.IDDWD001 = dw01.IDDWD001 "); sbSQL.Append(" Where pr12.idpro012 = :UNI "); sbSQL.Append(" ORDER BY "); sbSQL.Append(" dw10.DSESPC"); Conectar(); oAdapter = ObterDataAdapter(sbSQL.ToString()); UtDbNet.AdicionarParametro(oAdapter, TratarSQLParametroBanco("UNI").ToString(), UtSessao.Empresa); ITableMapping objMapping = oAdapter.TableMappings.Add("Table", "DsMedicamentos"); objMapping.ColumnMappings.Add("dsespc", "dsespc"); objMapping.ColumnMappings.Add("IDDWD010", "IDDWD010"); oAdapter.Fill(Ds); Desconectar(); return Ds; } catch (Exception en) { m_oLog.CriarArquivoLog(en.Message, UtSessao.Sessao["UNIDADE"].ToString()); throw new Dor.Util.OperacaoInvalidaBD(en); } }
public DataSet ListaMedicamentos() { DataSet Ds = new DataSet("DsMedicamentos"); try { //Listar todos os medicamentos padrões e não padrões do DW de acordo com a Unidade do usuário Logado. sbSQL.Length = 0; sbSQL.Append("SELECT "); sbSQL.Append("PRODUTOS.SEQ_MED AS IDDWD016, "); sbSQL.Append("' 'AS CODIGO, "); sbSQL.Append("PRODUTOS.PRODUTO AS DESCRICAO, "); sbSQL.Append("' ' AS TIPO_PRODUTO "); sbSQL.Append("FROM "); sbSQL.Append("TBDEF PRODUTOS "); sbSQL.Append("ORDER BY "); sbSQL.Append("PRODUTOS.PRODUTO "); //sbSQL.Append("SELECT "); //sbSQL.Append("PRODUTOS.IDDWD016, "); //sbSQL.Append("Substr(PRODUTOS.CLPRCD,10,6) AS CODIGO, "); //sbSQL.Append("PRODUTOS.DSPRCD AS DESCRICAO, "); //sbSQL.Append("TPPRODUTO.TPPRDT AS TIPO_PRODUTO "); //sbSQL.Append("FROM "); //sbSQL.Append("TBDWD016 PRODUTOS "); //sbSQL.Append(" INNER JOIN TBDWD031 TPPRODUTO ON PRODUTOS.IDDWD031 = TPPRODUTO.IDDWD031 "); //sbSQL.Append(" INNER JOIN TBDWD001 UNIDADES ON Substr(PRODUTOS.CLPRCD,1,3) = UNIDADES.NMMNMNUNDD "); //sbSQL.Append(" INNER JOIN TBPRO012 UNIPROCYON ON UNIDADES.IDDWD001 = UNIPROCYON.IDDWD001 "); //sbSQL.Append("WHERE "); //sbSQL.Append("PRODUTOS.FLBLQD = '0' "); //sbSQL.Append("AND "); //sbSQL.Append("TPPRODUTO.TPPRDT = 'MED' "); //sbSQL.Append("AND "); //sbSQL.Append("UNIPROCYON.IDPRO012 = :UNI "); //sbSQL.Append("ORDER BY "); //sbSQL.Append("PRODUTOS.DSPRCD "); Conectar(); oAdapter = ObterDataAdapter(sbSQL.ToString()); UtDbNet.AdicionarParametro(oAdapter, TratarSQLParametroBanco("UNI").ToString(), UtSessao.Empresa); ITableMapping objMapping = oAdapter.TableMappings.Add("Table", "DsMedicamentos"); objMapping.ColumnMappings.Add("IDDWD016", "IDDWD016"); objMapping.ColumnMappings.Add("CODIGO", "CODIGO"); objMapping.ColumnMappings.Add("DESCRICAO", "DESCRICAO"); objMapping.ColumnMappings.Add("TIPO_PRODUTO", "TIPO_PRODUTO"); oAdapter.Fill(Ds); Desconectar(); return Ds; } catch (Exception en) { m_oLog.CriarArquivoLog(en.Message, UtSessao.Sessao["UNIDADE"].ToString()); throw new Dor.Util.OperacaoInvalidaBD(en); } }
internal LsMenu Listar(long v_iCodigoProjeto) { try { var m_oMenu = new LsMenu(); sbSQL.Length = 0; sbSQL.Append("SELECT projeto.IDPRO018, IDPRO039, IDSUBMENU, NMMENU, NMPAGN"); sbSQL.Append(" FROM TBPRO039 menu INNER JOIN TBPRO018 projeto ON menu.IDPRO018 = projeto.IDPRO018"); sbSQL.Append(" WHERE projeto.IDPRO018 =:IDPRO018"); sbSQL.Append(" ORDER BY NMMENU"); oAdapter = ObterDataAdapter(sbSQL.ToString()); UtDbNet.AdicionarParametro(oAdapter, "IDPRO018", v_iCodigoProjeto); ITableMapping objMapping = oAdapter.TableMappings.Add("Table", LsMenu.tbMenu); objMapping.ColumnMappings.Add("IDPRO018", LsMenu.cpCodigoProjeto); objMapping.ColumnMappings.Add("IDPRO039", LsMenu.cpCodigo); objMapping.ColumnMappings.Add("IDSUBMENU", LsMenu.cpSubMenu); objMapping.ColumnMappings.Add("NMMENU", LsMenu.cpNome); objMapping.ColumnMappings.Add("NMPAGN", LsMenu.cpPagina); oAdapter.Fill(m_oMenu); return m_oMenu; } catch (Exception ex) { throw new Dor.Util.OperacaoInvalidaBD(ex); } }
public DataSet ListaMedicos() { DataSet Ds = new DataSet("DsPesquisar"); try { sbSQL.Length = 0; sbSQL.Append(" Select "); sbSQL.Append(" Substr(dw12.CLMEDC,10) As Codigo, "); sbSQL.Append(" dw12.NMMEDC As Medico, "); sbSQL.Append(" dw12.NRCONSMEDC As CRM "); sbSQL.Append(" From "); sbSQL.Append(" VWDWD012 dw12 INNER JOIN TBDWD001 dw01 ON Substr(dw12.CLMEDC,1,3) = dw01.NMMNMNUNDD "); sbSQL.Append(" INNER JOIN TBPRO012 pr12 ON pr12.IDDWD001 = dw01.IDDWD001 "); sbSQL.Append(" Where pr12.idpro012 = :UNI "); sbSQL.Append(" Order By dw12.NMMEDC "); Conectar(); oAdapter = ObterDataAdapter(sbSQL.ToString()); UtDbNet.AdicionarParametro(oAdapter, TratarSQLParametroBanco("UNI").ToString(), UtSessao.Empresa); ITableMapping objMapping = oAdapter.TableMappings.Add("Table", "DsPesquisar"); objMapping.ColumnMappings.Add("Codigo", "Codigo"); objMapping.ColumnMappings.Add("Medico", "Medico"); objMapping.ColumnMappings.Add("CRM", "CRM"); oAdapter.Fill(Ds); Desconectar(); return Ds; } catch (Exception en) { m_oLog.CriarArquivoLog(en.Message, UtSessao.Sessao["UNIDADE"].ToString()); throw new Dor.Util.OperacaoInvalidaBD(en); } }
internal LsPerfil Listar() { try { var m_oPerfil = new LsPerfil(); sbSQL.Length = 0; sbSQL.Append("SELECT DSAPLDPERF, IDPRO016, NMPERF"); sbSQL.Append(" FROM TBPRO016"); sbSQL.Append(" ORDER BY NMPERF"); oAdapter = ObterDataAdapter(sbSQL.ToString()); ITableMapping objMapping = oAdapter.TableMappings.Add("Table", LsPerfil.tbPerfil); objMapping.ColumnMappings.Add("DSAPLDPERF", LsPerfil.cpApelido); objMapping.ColumnMappings.Add("IDPRO016", LsPerfil.cpCodigo); objMapping.ColumnMappings.Add("NMPERF", LsPerfil.cpNome); oAdapter.Fill(m_oPerfil); return m_oPerfil; } catch (Exception ex) { throw new Dor.Util.OperacaoInvalidaBD(ex); } }
public DataSet ListaPacientes() { DataSet Ds = new DataSet("DsPesquisar"); try { sbSQL.Length = 0; sbSQL.Append(" Select "); sbSQL.Append(" Substr(dw18.CLATND,10) As Registro, "); sbSQL.Append(" dw04.NMPESS As Paciente, "); sbSQL.Append(" dw04.NRCPFCNPJ As CPFCNPJ, "); sbSQL.Append(" dw05.DTNASC As Nascimento, "); sbSQL.Append(" dw18.DTATND As Atendimento, "); sbSQL.Append(" dw18.DTALTA As Alta "); sbSQL.Append(" From "); sbSQL.Append(" TBDWD007 dw07 INNER JOIN TBDWD005 dw05 ON dw07.CLPACN = dw05.CLPESSFISC "); sbSQL.Append(" INNER JOIN TBDWD004 dw04 ON dw05.CLPESSFISC = dw04.CLPESS "); sbSQL.Append(" INNER JOIN TBDWD018 dw18 ON dw04.CLPESS = dw18.CLPACN "); //sbSQL.Append(" INNER JOIN TBDWD019 dw19 ON dw19.CLATND = dw18.CLATND "); //sbSQL.Append(" INNER JOIN TBDWD015 dw15 ON dw19.CLPLNO = dw15.CLPLNO "); //sbSQL.Append(" INNER JOIN TBDWD004 dw04_1 ON dw04_1.CLPESS = dw15.CLCONV "); sbSQL.Append(" INNER JOIN TBDWD001 dw01 ON Substr(dw07.CLPACN,1,3) = dw01.NMMNMNUNDD "); sbSQL.Append(" INNER JOIN TBPRO012 pr12 ON pr12.IDDWD001 = dw01.IDDWD001 "); sbSQL.Append(" Where pr12.idpro012 = :UNI "); sbSQL.Append(" AND ROWNUM <= 501 "); Conectar(); oAdapter = ObterDataAdapter(sbSQL.ToString()); UtDbNet.AdicionarParametro(oAdapter, TratarSQLParametroBanco("UNI").ToString(), UtSessao.Empresa); ITableMapping objMapping = oAdapter.TableMappings.Add("Table", "DsPesquisar"); objMapping.ColumnMappings.Add("Registro", "Registro"); objMapping.ColumnMappings.Add("Paciente", "Paciente"); objMapping.ColumnMappings.Add("CPFCNPJ", "CPFCNPJ"); objMapping.ColumnMappings.Add("Nascimento", "Nascimento"); objMapping.ColumnMappings.Add("Atendimento", "Atendimento"); objMapping.ColumnMappings.Add("Alta", "Alta"); oAdapter.Fill(Ds); Desconectar(); return Ds; } catch (Exception en) { m_oLog.CriarArquivoLog(en.Message, UtSessao.Sessao["UNIDADE"].ToString()); throw new Dor.Util.OperacaoInvalidaBD(en); } }
public virtual DataSet GetResultSet( string sql, CommandType cmdType = CommandType.Text, Dictionary <string, object> inputArgs = null, Dictionary <string, object> outputArgs = null) { DataSet ds = null; try { Type adapterTyp = mConn.GetType().Assembly.GetExportedTypes().Where( typ => typ.IsClass && typ.GetInterfaces().Contains(typeof(IDbDataAdapter)) && typ.IsAbstract == false && typeof(IDbDataAdapter).IsAssignableFrom(typ)).First(); if (adapterTyp != null) { //IDataAdapter dd; IDbDataAdapter d = null; d = Activator.CreateInstance(adapterTyp) as IDbDataAdapter; using (IDbCommand cmd = mConn.CreateCommand()) { cmd.CommandText = sql; cmd.CommandType = cmdType; if (mTrans != null) { cmd.Transaction = mTrans; } Dictionary <string, object> inputs = inputArgs ?? new Dictionary <string, object>(); Dictionary <string, object> outputs = outputArgs ?? new Dictionary <string, object>(); IDbDataParameter parameter; foreach (var key in inputs) { parameter = cmd.CreateParameter(); parameter.ParameterName = key.Key; parameter.Value = key.Value; parameter.Direction = outputs.ContainsKey(key.Key) ? ParameterDirection.InputOutput : ParameterDirection.Input; cmd.Parameters.Add(parameter); } foreach (var key in outputs) { if (!inputs.ContainsKey(key.Key)) { parameter = cmd.CreateParameter(); parameter.ParameterName = key.Key; parameter.Direction = ParameterDirection.Output; cmd.Parameters.Add(parameter); } } d.SelectCommand = cmd; ds = new DataSet(); var result = d.Fill(ds); if (outputArgs != null) { foreach (var key in outputArgs) { outputArgs[key.Key] = (cmd.Parameters[key.Key] as IDbDataParameter)?.Value; } } } } } catch (Exception e) { throw; } return(ds); }
public DataSet ListaProcedimentos() { DataSet Ds = new DataSet("DsMedicamentos"); try { //Listar todos os procedimentos do DW de acordo com a Unidade do usuário Logado. sbSQL.Length = 0; //ORIGINAL //sbSQL.Append("SELECT "); //sbSQL.Append("IDINT106 AS IDDWD016,"); //sbSQL.Append("' ' AS CODIGO,"); //sbSQL.Append("DSPROCED AS DESCRICAO,"); //sbSQL.Append("' ' AS TIPO_PRODUTO "); //sbSQL.Append("FROM TBINT106 WHERE DSGRUPO LIKE '%PROCEDIM%' OR DSSUBGRP LIKE '%PROCEDIM%' "); //sbSQL.Append("ORDER BY DSPROCED"); sbSQL.Append("SELECT "); sbSQL.Append("IDINT106 AS IDDWD016,"); sbSQL.Append("' ' AS CODIGO,"); sbSQL.Append("DSPROCED AS DESCRICAO,"); sbSQL.Append("' ' AS TIPO_PRODUTO "); sbSQL.Append("FROM TBINT106 "); sbSQL.Append("ORDER BY DSPROCED"); Conectar(); oAdapter = ObterDataAdapter(sbSQL.ToString()); UtDbNet.AdicionarParametro(oAdapter, TratarSQLParametroBanco("UNI").ToString(), UtSessao.Empresa); ITableMapping objMapping = oAdapter.TableMappings.Add("Table", "DsMedicamentos"); objMapping.ColumnMappings.Add("IDDWD016", "IDDWD016"); objMapping.ColumnMappings.Add("CODIGO", "CODIGO"); objMapping.ColumnMappings.Add("DESCRICAO", "DESCRICAO"); objMapping.ColumnMappings.Add("TIPO_PRODUTO", "TIPO_PRODUTO"); oAdapter.Fill(Ds); Desconectar(); return Ds; } catch (Exception en) { m_oLog.CriarArquivoLog(en.Message, UtSessao.Sessao["UNIDADE"].ToString()); throw new Dor.Util.OperacaoInvalidaBD(en); } }
/// <summary> /// 通过SQL语句查询数据库内容 /// </summary> /// <param name="sql">SQL语句</param> /// <param name="parameters">参数列表</param> /// <returns>查询的数据结果列表</returns> public TPCResult <DataTable> Query(string sql, List <DbParameter> parameters) { TPCResult <DataTable> result = new TPCResult <DataTable>(); //打开连接 if (!Open()) { TPCLogger.Instance.Write(LOGGER_TYPE.LOGGER_ERROR, "Open Database Failed."); result.State = RESULT_STATE.NG; result.Message = "Open Database Failed."; return(null); } try { TPCLogger.Instance.Write(LOGGER_TYPE.LOGGER_INFORMATION, string.Format("Execute SQL:{0}", sql)); //创建SQL指令 IDbCommand cmd = CreateCommand(sql); //构建数据库指令参数列表 StringBuilder param_logger = new StringBuilder(); foreach (DbParameter param in parameters) { IDbDataParameter dp = cmd.CreateParameter(); dp.ParameterName = param.ParamName; dp.DbType = param.DbType; dp.Value = param.Value; cmd.Parameters.Add(dp); if (param_logger.Length > 0) { param_logger.Append(","); } param_logger.Append(string.Format("{{ParamName:{0}, DbType:{1}, Value:{2}. }}", param.ParamName, param.DbType.ToString(), (param.Value == null) ? "NULL" : param.Value.ToString())); } TPCLogger.Instance.Write(LOGGER_TYPE.LOGGER_INFORMATION, string.Format("\t\tParameters:{0}", param_logger.ToString())); //创建数据适配器 IDbDataAdapter da = m_Creator.CreateDataAdapter(); da.SelectCommand = cmd; //执行查询并将结果填入DataSet DataSet ds = new DataSet(); da.Fill(ds); if (ds.Tables.Count == 0) { TPCLogger.Instance.Write(LOGGER_TYPE.LOGGER_ERROR, "Execute query failed."); result.State = RESULT_STATE.NG; result.Message = "Execute query failed."; return(result); } result.Value = ds.Tables[0]; return(result); } catch (Exception e) { TPCLogger.Instance.Write(LOGGER_TYPE.LOGGER_ERROR, e.Message); result.State = RESULT_STATE.NG; result.Message = e.Message; return(result); } finally { Close(); } }
public DataSet ListaReacaoHemo_Sumario(long m_iId058) { DataSet Ds = new DataSet("DsHemo"); try { sbSQL.Length = 0; sbSQL.Append("SELECT "); sbSQL.Append("R.IDINT099,"); sbSQL.Append("R.DSREACAO "); sbSQL.Append("FROM "); sbSQL.Append("TBINT100 T INNER JOIN TBINT099 R ON T.IDINT099 = R.IDINT099 "); sbSQL.Append("WHERE "); sbSQL.Append("IDINT058 = " + m_iId058); Conectar(); oAdapter = ObterDataAdapter(sbSQL.ToString()); ITableMapping objMapping = oAdapter.TableMappings.Add("Table", "DsHemo"); objMapping.ColumnMappings.Add("IDINT099", "IDINT099"); objMapping.ColumnMappings.Add("DSREACAO", "DSREACAO"); oAdapter.Fill(Ds); Desconectar(); return Ds; } catch (Exception en) { m_oLog.CriarArquivoLog(en.Message, UtSessao.Sessao["UNIDADE"].ToString()); throw new Dor.Util.OperacaoInvalidaBD(en); } }
internal LsSumario_Antimicrobiano Listar() { try { var m_oSumario_Antimicrobiano = new LsSumario_Antimicrobiano(); sbSQL.Length = 0; sbSQL.Append("SELECT IDINT064, IDINT058, DSANTI, DSPERI"); sbSQL.Append(" FROM TBINT064 WHERE IDINT058 = " + Convert.ToInt32(UtSessao.Sessao["Id058"])); oAdapter = ObterDataAdapter(sbSQL.ToString()); ITableMapping objMapping = oAdapter.TableMappings.Add("Table" , LsSumario_Antimicrobiano.tbSumario_Antimicrobiano); objMapping.ColumnMappings.Add("IDINT064",LsSumario_Antimicrobiano.cpCodigo); objMapping.ColumnMappings.Add("IDINT058",LsSumario_Antimicrobiano.cpidint058); objMapping.ColumnMappings.Add("DSANTI",LsSumario_Antimicrobiano.cpnome); objMapping.ColumnMappings.Add("DSPERI",LsSumario_Antimicrobiano.cpdsperi); oAdapter.Fill(m_oSumario_Antimicrobiano); return m_oSumario_Antimicrobiano; } catch (Exception en) { throw new Dor.Util.OperacaoInvalidaBD(en); } }
public DataSet ListarHemoReacao() { DataSet Ds = new DataSet("DsHemo"); try { //Listar todos os Exames do DW de acordo com a Unidade do usuário Logado. sbSQL.Length = 0; sbSQL.Append(" SELECT "); sbSQL.Append(" IDINT099, "); sbSQL.Append(" DSREACAO "); sbSQL.Append(" FROM "); sbSQL.Append(" TBINT099 "); sbSQL.Append(" ORDER BY "); sbSQL.Append(" DSREACAO "); Conectar(); oAdapter = ObterDataAdapter(sbSQL.ToString()); ITableMapping objMapping = oAdapter.TableMappings.Add("Table", "DsHemo"); objMapping.ColumnMappings.Add("IDINT099", "IDINT099"); objMapping.ColumnMappings.Add("DEREACAO", "DSREACAO"); oAdapter.Fill(Ds); Desconectar(); return Ds; } catch (Exception en) { m_oLog.CriarArquivoLog(en.Message, UtSessao.Sessao["UNIDADE"].ToString()); throw new Dor.Util.OperacaoInvalidaBD(en); } }
public static void GenericConnectionTest(IDbConnection cn, IDbDataAdapter da) // exceptions handled in the caller { IDbCommand cmd = null; IDataReader dr = null; DataSet ds = null; DataTable dt = null; DataRow row = null; DateTime startTime; int iCol = 0; long ms = 0, rowsRead = 0; long initialMemory = 0, finalMemory = 0; startTime = DateTime.Now; cn.Open(); ms = (long)DateTime.Now.Subtract(startTime).TotalMilliseconds; LogMessage($"Connected successfully in {ms} milliseconds."); if (CommandString != "") { cmd = cn.CreateCommand(); cmd.CommandText = CommandString; cmd.CommandTimeout = CommandTimeout; switch (CursorType) { case "NONE": startTime = DateTime.Now; cmd.ExecuteNonQuery(); ms = (long)DateTime.Now.Subtract(startTime).TotalMilliseconds; LogMessage($"Executed command successfully in {ms} milliseconds. No rows read."); break; case "DATASET": ds = new DataSet(); GC.Collect(); initialMemory = GC.GetTotalMemory(true); startTime = DateTime.Now; da.Fill(ds); ms = (long)DateTime.Now.Subtract(startTime).TotalMilliseconds; if (ds.Tables.Count > 0) { rowsRead = ds.Tables[0].Rows.Count; } LogMessage($"Executed command and filled DataSet successfully in {ms} milliseconds. {rowsRead} rows read."); // memory used - have to reverse-engineer DiagClient43.exe GC.Collect(); finalMemory = GC.GetTotalMemory(true); LogMessage($"Approximate memory usage: {finalMemory.ToString("#,##0")} bytes"); // display rows if (RowsToDisplay != 0) { dt = ds.Tables[0]; int colCount = dt.Columns.Count; string[] colNames = new string[colCount]; string[] colValues = null; row = null; iCol = 0; foreach (DataColumn dc in dt.Columns) { colNames[iCol] = dc.ColumnName + ":L"; iCol++; } ReportFormatter rf = new ReportFormatter(); rf.SetColumnNames(colNames); int rows2disp = RowsToDisplay == -1 ? dt.Rows.Count : RowsToDisplay; for (int r = 0; r < rows2disp; r++) { row = dt.Rows[r]; iCol = 0; colValues = new string[colCount]; foreach (DataColumn dc in dt.Columns) { colValues[iCol] = row[iCol] == DBNull.Value ? "" : row[iCol].ToString(); iCol++; } rf.SetcolumnData(colValues); } Console.WriteLine(); Console.WriteLine(rf.GetHeaderText()); Console.WriteLine(rf.GetSeparatorText()); for (int i = 0; i < rf.GetRowCount(); i++) { Console.WriteLine(rf.GetDataText(i)); } Console.WriteLine(); } break; case "FIREHOSE": int iRows = 0; startTime = DateTime.Now; dr = cmd.ExecuteReader(); while (dr.Read()) { iRows++; // do nothing - throw the rows away } dr.Close(); ms = (long)DateTime.Now.Subtract(startTime).TotalMilliseconds; LogMessage($"Executed command and streamed the rows in {ms} milliseconds. {iRows} rows read."); break; } } }
internal LsEmpresa Listar(string v_sLogin) { try { var m_oEmpresa = new LsEmpresa(); sbSQL.Length = 0; sbSQL.Append(" SELECT DISTINCT CDCNPJ, DSURL, IDPRO010, IDPRO011, empresa.IDPRO012, NMFANT, NMRAZASOCL,IDSUBEMPR"); sbSQL.Append(" FROM TBPRO015 usuario, TBPRO013 usuario_perfil_empresa, TBPRO012 empresa"); sbSQL.Append(" WHERE usuario.IDPRO015 = usuario_perfil_empresa.IDPRO015"); sbSQL.Append(" AND usuario_perfil_empresa.IDPRO012 = empresa.IDPRO012"); sbSQL.Append(" AND usuario.DSEMAL = :DSEMAL"); oAdapter = ObterDataAdapter(sbSQL.ToString()); ITableMapping objMapping = oAdapter.TableMappings.Add("Table", LsEmpresa.tbEmpresa); UtDbNet.AdicionarParametro(oAdapter, TratarSQLParametroBanco("DSEMAL").ToString(), v_sLogin); objMapping.ColumnMappings.Add("CDCNPJ", LsEmpresa.cpCNPJ); objMapping.ColumnMappings.Add("DSURL", LsEmpresa.cpUrl); objMapping.ColumnMappings.Add("IDPRO010", LsEmpresa.cpEndereco); objMapping.ColumnMappings.Add("IDPRO011", LsEmpresa.cpTelefone); objMapping.ColumnMappings.Add("IDPRO012", LsEmpresa.cpCodigo); objMapping.ColumnMappings.Add("NMFANT", LsEmpresa.cpNome); objMapping.ColumnMappings.Add("NMRAZASOCL", LsEmpresa.cpRazaoSocial); objMapping.ColumnMappings.Add("IDSUBEMPR", LsEmpresa.cpSubEmpresa); oAdapter.Fill(m_oEmpresa); return m_oEmpresa; } catch (Exception ex) { throw new Dor.Util.OperacaoInvalidaBD(ex); } }
public void Evaluate(int SpreadMax) { double dblsend; this.FPinInSendQuery.GetValue(0, out dblsend); bool ondata = false; if (this.FPinInFields.PinIsChanged || this.FPinInTables.PinIsChanged || this.FPinInWhere.PinIsChanged || this.FPinInGroupBy.PinIsChanged || this.FPinInHaving.PinIsChanged || this.FPinInOrderBy.PinIsChanged ) { string fields, tables, where, groupby, orderby, having; this.FPinInFields.GetString(0, out fields); this.FPinInTables.GetString(0, out tables); this.FPinInWhere.GetString(0, out where); this.FPinInGroupBy.GetString(0, out groupby); this.FPinInHaving.GetString(0, out having); this.FPinInOrderBy.GetString(0, out orderby); tables = tables == null ? "" : tables; fields = fields == null ? "" : fields; where = where == null ? "" : where; groupby = groupby == null ? "" : groupby; having = having == null ? "" : having; orderby = orderby == null ? "" : orderby; this.FQuery = "SELECT "; this.FQuery += fields; if (tables.Trim().Length > 0) { this.FQuery += " FROM "; this.FQuery += tables; } if (where.Trim().Length > 0) { this.FQuery += " WHERE "; this.FQuery += where; } if (groupby.Trim().Length > 0) { this.FQuery += " GROUP BY "; this.FQuery += groupby; } if (having.Trim().Length > 0) { this.FQuery += " HAVING "; this.FQuery += having; } if (orderby.Trim().Length > 0) { this.FQuery += " ORDER BY "; this.FQuery += orderby; } this.FPinOutStatement.SetString(0, this.FQuery); } if (this.FPinInConnection.IsConnected && dblsend >= 0.5) { if (this.FConnectionObject.Connection.State == ConnectionState.Open) { try { IDbDataAdapter da = this.FConnectionObject.GetDataAdapter(this.FQuery); DataSet dst = new DataSet(); da.Fill(dst); List <string> matchingfields = new List <string>(); this.FPinOutFieldNames.SliceCount = dst.Tables[0].Columns.Count; //Browse column list int colcount = dst.Tables[0].Columns.Count; int rowcount = dst.Tables[0].Rows.Count; for (int i = 0; i < colcount; i++) { string colname = dst.Tables[0].Columns[i].ColumnName; this.FPinOutFieldNames.SetString(i, colname); if (this.FPinOutFields.ContainsKey(colname)) { matchingfields.Add(colname); this.FPinOutFields[colname].SliceCount = rowcount; } } foreach (string fname in this.FPinOutFields.Keys) { if (!matchingfields.Contains(fname)) { this.FPinOutFields[fname].SliceCount = 0; } } for (int i = 0; i < rowcount; i++) { DataRow row = dst.Tables[0].Rows[i]; foreach (string matchedfield in matchingfields) { this.FPinOutFields[matchedfield].SetString(i, Convert.ToString(row[matchedfield])); } } ondata = true; this.FPinOutStatus.SetString(0, "OK"); } catch (Exception ex) { this.FPinOutFieldNames.SliceCount = 0; this.FPinOutStatus.SetString(0, ex.Message); foreach (IStringOut so in this.FPinOutFields.Values) { so.SliceCount = 0; } } } else { this.FPinOutFieldNames.SliceCount = 0; this.FPinOutStatus.SetString(0, "Not Connected"); foreach (IStringOut so in this.FPinOutFields.Values) { so.SliceCount = 0; } } } if (ondata) { this.FPinOutOnData.SetValue(0, 1); } else { this.FPinOutOnData.SetValue(0, 0); } }
public override object Execute(Expression expression) { try { Type elementType = TypeSystem.GetElementType(expression.Type); string methodName = ((MethodCallExpression)expression).Method.Name; bool isIQueryable = (expression.Type.FullName.StartsWith("System.Linq.IQueryable") || expression.Type.FullName.StartsWith("System.Linq.IOrderedQueryable")); string queryString = String.Format(this.Translate(expression), m_tableName); if (m_tableName == "sipaccounts") { Console.WriteLine(); } if (!OrderBy.IsNullOrBlank()) { queryString += " order by " + OrderBy; } if (Count != Int32.MaxValue) { queryString += " limit " + Count; } if (Offset != 0) { queryString += " offset " + Offset; } //logger.Debug(queryString); if (!queryString.IsNullOrBlank()) { using (TransactionScope transactionScope = new TransactionScope(TransactionScopeOption.Suppress)) { using (IDbConnection connection = m_dbFactory.CreateConnection()) { connection.ConnectionString = m_dbConnStr; connection.Open(); if (elementType == typeof(Int32)) { // This is a count. IDbCommand command = connection.CreateCommand(); command.CommandText = queryString; return(Convert.ToInt32(command.ExecuteScalar())); } else { //logger.Debug("SimpleDB select: " + queryString + "."); IDbCommand command = connection.CreateCommand(); command.CommandText = queryString; IDbDataAdapter adapter = m_dbFactory.CreateDataAdapter(); adapter.SelectCommand = command; DataSet resultSet = new DataSet(); adapter.Fill(resultSet); if (resultSet != null && resultSet.Tables[0] != null) { object result = Activator.CreateInstance( typeof(SQLObjectReader <>).MakeGenericType(elementType), BindingFlags.Instance | BindingFlags.Public, null, new object[] { resultSet, m_setter }, null); if (isIQueryable) { return(result); } else { IEnumerator enumerator = ((IEnumerable)result).GetEnumerator(); if (enumerator.MoveNext()) { return(enumerator.Current); } else { return(null); } } } } } throw new ApplicationException("No results for SQL query."); } } else { throw new ApplicationException("The expression translation by the SQLQueryProvider resulted in an empty select string."); } } catch (Exception excp) { logger.Error("Exception SQLQueryProvider Execute. " + expression.ToString() + ". " + excp.Message); throw; } }
private string InvokeStep(InterfaceModel response, IDbConnection connection, IDbTransaction tran, Dictionary <string, object> dict, string version, out string exceMsg) { exceMsg = ""; DataRow dataPre = null; string result = ""; int rowIndex = 0; int count = response.Steps.Count; foreach (FlowStepModel step in response.Steps) { if (step.CommandText == "") { exceMsg = step.StepName + " 没有数据库语句,请维护"; return(""); } connection.ChangeDatabase(step.DataBase); IDbCommand cmd = connection.CreateCommand(); cmd.CommandTimeout = 0; cmd.Connection = connection; if (response.IsTransaction) { cmd.Transaction = tran; } IDbDataAdapter sqlDA = this.componentContext.ResolveNamed <IDbDataAdapter>(response.DataType); sqlDA.SelectCommand = cmd; //初始化参数 switch (step.CommandType) { case "Fun": case "Text": cmd.CommandType = CommandType.Text; break; case "StoredProcedure": cmd.CommandType = CommandType.StoredProcedure; break; } cmd.CommandText = step.CommandText; cmd.Parameters.Clear(); bool bOK = CreateParam(response, cmd, dataPre, step, dict, out exceMsg); if (!bOK) { return(""); } try { //执行Sql if (rowIndex == count - 1) //最后一步 { List <FilterCondition> filters = null; if (dict.ContainsKey(_ReqFilter)) { JArray ja = (JArray)dict[_ReqFilter]; filters = ja.ToObject <List <FilterCondition> >(); } if (version == "layui") { result = this.ExecSqlLayui(response, connection, sqlDA, cmd, filters); } else { result = this.ExecSql(response, connection, sqlDA, cmd, filters); } } else { //如果不是最后一步,存储当前步结果 DataSet ds = new DataSet(); sqlDA.Fill(ds); if (ds.Tables.Count > 0 && ds.Tables[ds.Tables.Count - 1].Rows.Count > 0) { DataTable dt = ds.Tables[ds.Tables.Count - 1]; dataPre = dt.Rows[0]; } } } catch (Exception ex) { exceMsg = response.Url + ">>" + step.StepName + ">>" + ex.Message; this.logger.LogError(exceMsg); return(""); } rowIndex++; } return(result); }
public DataSet ObterReceituario(long m_iId058) { DataSet Ds = new DataSet("DsProcedimento"); try { sbSQL.Length = 0; sbSQL.Append(" SELECT "); sbSQL.Append(" rece.IDINT067, "); sbSQL.Append(" prod.PRODUTO || ' ' || rece.OUTITM As Medicamento, "); sbSQL.Append(" rece.DSEMED || ' ' || rece.DSEVAL As Dose, "); sbSQL.Append(" rece.APREMED || ' ' || rece.APREVAL As Apresentacao, "); sbSQL.Append(" rece.VIAMED || ' ' || rece.VIALOCAL As Via, "); sbSQL.Append(" rece.FREQMED || ' ' || rece.COMPMED As Frequencia, "); sbSQL.Append(" rece.DURMED || ' ' || rece.DURVAL As Duracao, "); sbSQL.Append(" rece.DTULTDSE As UltimaDose "); sbSQL.Append(" FROM "); sbSQL.Append(" TBINT067 rece LEFT JOIN TBDEF prod ON rece.IDDWD016 = prod.SEQ_MED "); sbSQL.Append(" WHERE "); sbSQL.Append(" RECE.IDINT058 = " + m_iId058); //sbSQL.Append(" SELECT "); //sbSQL.Append(" rece.IDINT067, "); //sbSQL.Append(" prod.DSPRCD As Medicamento, "); //sbSQL.Append(" rece.DSEMED || ' ' || rece.DSEVAL As Dose, "); //sbSQL.Append(" rece.APREMED || ' ' || rece.APREVAL As Apresentacao, "); //sbSQL.Append(" rece.VIAMED || ' ' || rece.VIALOCAL As Via, "); //sbSQL.Append(" rece.FREQMED || ' ' || rece.COMPMED As Frequencia, "); //sbSQL.Append(" rece.DURMED || ' ' || rece.DURVAL As Duracao, "); //sbSQL.Append(" rece.DTULTDSE As UltimaDose "); //sbSQL.Append(" FROM "); //sbSQL.Append(" TBINT067 rece INNER JOIN TBDWD016 prod ON rece.IDDWD016 = prod.IDDWD016 "); //sbSQL.Append(" WHERE "); //sbSQL.Append(" RECE.IDINT058 = " + m_iId058); Conectar(); oAdapter = ObterDataAdapter(sbSQL.ToString()); ITableMapping objMapping = oAdapter.TableMappings.Add("Table", "DsProcedimento"); objMapping.ColumnMappings.Add("IDINT067", "IDINT067"); objMapping.ColumnMappings.Add("Medicamento", "Medicamento"); objMapping.ColumnMappings.Add("Dose", "Dose"); objMapping.ColumnMappings.Add("Apresentacao", "Apresentacao"); objMapping.ColumnMappings.Add("Via", "Via"); objMapping.ColumnMappings.Add("Frequencia", "Frequencia"); objMapping.ColumnMappings.Add("Duracao", "Duracao"); objMapping.ColumnMappings.Add("UltimaDose", "UltimaDose"); oAdapter.Fill(Ds); Desconectar(); return Ds; } catch (Exception en) { m_oLog.CriarArquivoLog(en.Message, UtSessao.Sessao["UNIDADE"].ToString()); throw new Dor.Util.OperacaoInvalidaBD(en); } }
/// <summary> /// call mysql stored proc or stored function, /// optionally return DataSet, and collect return params. /// </summary> public override System.Data.Linq.IExecuteResult ExecuteMethodCall(DataContext context, MethodInfo method , params object[] inputValues) { if (method == null) { throw new ArgumentNullException("L56 Null 'method' parameter"); } //check to make sure there is exactly one [FunctionEx]? that's below. //FunctionAttribute functionAttrib = GetFunctionAttribute(method); var functionAttrib = context.Mapping.GetFunction(method); ParameterInfo[] paramInfos = method.GetParameters(); //int numRequiredParams = paramInfos.Count(p => p.IsIn || p.IsRetval); //if (numRequiredParams != inputValues.Length) // throw new ArgumentException("L161 Argument count mismatch"); string sp_name = functionAttrib.MappedName; using (IDbCommand command = context.Connection.CreateCommand()) { command.CommandText = sp_name; //MySqlCommand command = new MySqlCommand("select hello0()"); int currInputIndex = 0; List <string> paramNames = new List <string>(); for (int i = 0; i < paramInfos.Length; i++) { ParameterInfo paramInfo = paramInfos[i]; //TODO: check to make sure there is exactly one [Parameter]? ParameterAttribute paramAttrib = paramInfo.GetCustomAttributes(false).OfType <ParameterAttribute>().Single(); //string paramName = "?" + paramAttrib.Name; //eg. '?param1' MYSQL string paramName = ":" + paramAttrib.Name; //eg. '?param1' PostgreSQL paramNames.Add(paramName); System.Data.ParameterDirection direction = GetDirection(paramInfo, paramAttrib); //MySqlDbType dbType = MySqlTypeConversions.ParseType(paramAttrib.DbType); IDbDataParameter cmdParam = command.CreateParameter(); cmdParam.ParameterName = paramName; //cmdParam.Direction = System.Data.ParameterDirection.Input; if (direction == ParameterDirection.Input || direction == ParameterDirection.InputOutput) { object inputValue = inputValues[currInputIndex++]; cmdParam.Value = inputValue; } else { cmdParam.Value = null; } cmdParam.Direction = direction; command.Parameters.Add(cmdParam); } if (!functionAttrib.IsComposable) { //procedures: under the hood, this seems to prepend 'CALL ' command.CommandType = System.Data.CommandType.StoredProcedure; } else { //functions: 'SELECT myFunction()' or 'SELECT hello(?s)' string cmdText = "SELECT " + command.CommandText + "($args)"; cmdText = cmdText.Replace("$args", string.Join(",", paramNames.ToArray())); command.CommandText = cmdText; } if (method.ReturnType == typeof(DataSet)) { //unknown shape of resultset: System.Data.DataSet dataSet = new DataSet(); IDbDataAdapter adapter = CreateDataAdapter(context); adapter.SelectCommand = command; adapter.Fill(dataSet); List <object> outParamValues = CopyOutParams(paramInfos, command.Parameters); return(new ProcedureResult(dataSet, outParamValues.ToArray())); } else { object obj = command.ExecuteScalar(); List <object> outParamValues = CopyOutParams(paramInfos, command.Parameters); return(new ProcedureResult(obj, outParamValues.ToArray())); } } }
/// <summary> /// Obter Dados Resultados... /// </summary> /// <param name="m_iId058"></param> /// <returns></returns> public DataSet ObterResultados(long m_iId058) { DataSet Ds = new DataSet("DsResultado"); try { sbSQL.Length = 0; sbSQL.Append(" SELECT "); sbSQL.Append(" IDINT065, "); sbSQL.Append(" DSRES, "); sbSQL.Append(" DSMARE, "); sbSQL.Append(" TO_CHAR(DTRES,'DD/MM/YYYY') As DTRES "); sbSQL.Append(" FROM "); sbSQL.Append(" TBINT065 "); sbSQL.Append(" WHERE "); sbSQL.Append(" IDINT058 = " + m_iId058); Conectar(); oAdapter = ObterDataAdapter(sbSQL.ToString()); ITableMapping objMapping = oAdapter.TableMappings.Add("Table", "DsResultado"); objMapping.ColumnMappings.Add("IDINT065", "IDINT065"); objMapping.ColumnMappings.Add("DSRES", "DSRES"); objMapping.ColumnMappings.Add("DSMARE", "DSMARE"); objMapping.ColumnMappings.Add("DTRES", "DTRES"); oAdapter.Fill(Ds); Desconectar(); return Ds; } catch (Exception en) { m_oLog.CriarArquivoLog(en.Message, UtSessao.Sessao["UNIDADE"].ToString()); throw new Dor.Util.OperacaoInvalidaBD(en); } }
/// <summary> /// Obter Dados Antimicrobiano... /// </summary> /// <param name="m_iId058"></param> /// <param name="m_sTipo"></param> /// <returns></returns> public DataSet ObterAntimicrobiano(long m_iId058, string m_sTipo) { DataSet Ds = new DataSet("DsAntimicrobiano"); try { sbSQL.Length = 0; sbSQL.Append(" SELECT "); sbSQL.Append(" anti.IDINT064, "); sbSQL.Append(" prod.PRODUTO As DSANTI, "); sbSQL.Append(" anti.DSPERI || ' ' || anti.DSVALI As DSPERI "); sbSQL.Append(" FROM "); sbSQL.Append(" TBINT064 anti INNER JOIN TBDEF prod ON anti.IDDWD016 = prod.SEQ_MED "); sbSQL.Append(" WHERE "); sbSQL.Append(" anti.TPMED = '" + m_sTipo.Trim() + "' AND anti.IDINT058 = " + m_iId058); //sbSQL.Append(" SELECT "); //sbSQL.Append(" anti.IDINT064, "); //sbSQL.Append(" prod.DSPRCD As DSANTI, "); //sbSQL.Append(" anti.DSPERI || ' ' || anti.DSVALI As DSPERI "); //sbSQL.Append(" FROM "); //sbSQL.Append(" TBINT064 anti INNER JOIN TBDWD016 prod ON anti.IDDWD016 = prod.IDDWD016 "); //sbSQL.Append(" WHERE "); //sbSQL.Append(" anti.TPMED = '" + m_sTipo.Trim() + "' AND anti.IDINT058 = " + m_iId058); Conectar(); oAdapter = ObterDataAdapter(sbSQL.ToString()); ITableMapping objMapping = oAdapter.TableMappings.Add("Table", "DsProcedimento"); objMapping.ColumnMappings.Add("IDINT064", "IDINT064"); objMapping.ColumnMappings.Add("DSANTI", "DSANTI"); objMapping.ColumnMappings.Add("DSPERI", "DSPERI"); oAdapter.Fill(Ds); Desconectar(); return Ds; } catch (Exception en) { m_oLog.CriarArquivoLog(en.Message, UtSessao.Sessao["UNIDADE"].ToString()); throw new Dor.Util.OperacaoInvalidaBD(en); } }
/// ///////////////////////////////////////////////////////////////// public int Fill(DataSet ds) { return(m_adapter.Fill(ds)); }
internal LsEmpresa Listar() { try { var m_oEmpresa = new LsEmpresa(); sbSQL.Length = 0; sbSQL.Append(" SELECT CDCNPJ, DSURL, IDPRO010, IDPRO011, IDPRO012, NMFANT, NMRAZASOCL,IDSUBEMPR"); sbSQL.Append(" FROM TBPRO012"); oAdapter = ObterDataAdapter(sbSQL.ToString()); ITableMapping objMapping = oAdapter.TableMappings.Add("Table", LsEmpresa.tbEmpresa); objMapping.ColumnMappings.Add("CDCNPJ", LsEmpresa.cpCNPJ); objMapping.ColumnMappings.Add("DSURL", LsEmpresa.cpUrl); objMapping.ColumnMappings.Add("IDPRO010", LsEmpresa.cpEndereco); objMapping.ColumnMappings.Add("IDPRO011", LsEmpresa.cpTelefone); objMapping.ColumnMappings.Add("IDPRO012", LsEmpresa.cpCodigo); objMapping.ColumnMappings.Add("NMFANT", LsEmpresa.cpNome); objMapping.ColumnMappings.Add("NMRAZASOCL", LsEmpresa.cpRazaoSocial); objMapping.ColumnMappings.Add("IDSUBEMPR", LsEmpresa.cpSubEmpresa); oAdapter.Fill(m_oEmpresa); return m_oEmpresa; } catch (Exception ex) { throw new Dor.Util.OperacaoInvalidaBD(ex); } }
public DataSet ObterDSMedicoDW(string m_sCRM, string m_sNome) { DataSet Ds = new DataSet("DsResultado"); try { sbSQL.Length = 0; sbSQL.Append(" Select DISTINCT"); sbSQL.Append(" dw09.IDDWD009, "); sbSQL.Append(" dw09.NRCONSPRFS, "); sbSQL.Append(" dw04.NMPESS As NMMEDC "); sbSQL.Append(" From "); sbSQL.Append(" TBDWD009 dw09 INNER JOIN TBDWD005 dw05 ON dw09.CLMEDC = dw05.CLPESSFISC "); sbSQL.Append(" INNER JOIN TBDWD004 dw04 ON dw05.CLPESSFISC = dw04.CLPESS "); sbSQL.Append(" INNER JOIN TBDWD001 dw01 ON Substr(dw09.CLMEDC,1,3) = dw01.NMMNMNUNDD "); sbSQL.Append(" INNER JOIN TBPRO012 pr12 ON pr12.IDDWD001 = dw01.IDDWD001 "); sbSQL.Append(" Where pr12.idpro012 = " + UtSessao.Empresa + " AND "); if (m_sCRM.Trim().Length > 0) { sbSQL.Append(" dw09.NRCONSPRFS Like '" + m_sCRM.ToString() + "' "); } else { sbSQL.Append(" dw04.NMPESS Like '" + m_sNome.ToString() + "' "); } Conectar(); oAdapter = ObterDataAdapter(sbSQL.ToString()); ITableMapping objMapping = oAdapter.TableMappings.Add("Table", "DsResultado"); objMapping.ColumnMappings.Add("IDDWD009", "IDDWD009"); objMapping.ColumnMappings.Add("NRCONSPRFS", "NRCONSPRFS"); objMapping.ColumnMappings.Add("NMMEDC", "NMMEDC"); oAdapter.Fill(Ds); Desconectar(); return Ds; } catch (Exception en) { m_oLog.CriarArquivoLog(en.Message, UtSessao.Sessao["UNIDADE"].ToString()); throw new Dor.Util.OperacaoInvalidaBD(en); } }
public static void WriteSelectionScratchTable(DataColumn[] targetColumns, DataTable idList) { try { int incidOrdinal = -1; if (idList.Columns.Contains(_incidTable.incidColumn.ColumnName)) { if (idList.Columns[_incidTable.incidColumn.ColumnName].DataType == _hluLayerStructure.incidColumn.DataType) { incidOrdinal = idList.Columns[_incidTable.incidColumn.ColumnName].Ordinal; } else { return; } } else { var q = idList.Columns.Cast <DataColumn>().Where(c => c.ColumnName.EndsWith( Resources.ColumnTableNameSeparator + _incidTable.incidColumn.ColumnName) && c.DataType == _incidTable.incidColumn.DataType); if (q.Count() == 1) { incidOrdinal = q.ElementAt(0).Ordinal; } else { return; } } // incid column always has the same name as in the GIS layer structure if (idList.Columns[incidOrdinal].ColumnName != _hluLayerStructure.incidColumn.ColumnName) { idList.Columns[incidOrdinal].ColumnName = _hluLayerStructure.incidColumn.ColumnName; } try { _scratchDb.ExecuteNonQuery(String.Format("DROP TABLE {0}", _scratchSelTable), _scratchDb.Connection.ConnectionTimeout, CommandType.Text); } catch { } if (String.IsNullOrEmpty(idList.TableName)) { idList.TableName = _scratchSelTable; } if ((idList.PrimaryKey == null) || (idList.PrimaryKey.Length == 0)) { idList.PrimaryKey = new DataColumn[] { idList.Columns[incidOrdinal] } } ; if (!_scratchDb.CreateTable(idList)) { return; } DataTable scratchTable = idList.Clone(); DataSet datasetOut = new DataSet(_scratchSelTable); IDbDataAdapter adapterOut = _scratchDb.CreateAdapter(scratchTable); adapterOut.Fill(datasetOut); adapterOut.TableMappings.Clear(); adapterOut.TableMappings.Add(scratchTable.TableName, datasetOut.Tables[0].TableName); scratchTable = datasetOut.Tables[0]; foreach (DataRow r in idList.Rows) { scratchTable.LoadDataRow(r.ItemArray, false); } adapterOut.Update(datasetOut); } catch { } finally { if ((_scratchDb != null) && (_scratchDb.Connection.State != ConnectionState.Closed)) { try { _scratchDb.Connection.Close(); } catch { } } } }
/// <summary> /// Obter Dados Item Receituário /// </summary> /// <param name="m_iId067"></param> /// <returns></returns> public DataSet ObterProcedimentos(long m_iId058) { DataSet Ds = new DataSet("DsProcedimento"); try { sbSQL.Length = 0; sbSQL.Append(" SELECT "); sbSQL.Append(" proce.IDINT063, "); sbSQL.Append(" prod.DSPROCED || ' ' || proce.DSVALI || ' ' || proce.DSVALCARDIO As Procedimento, "); sbSQL.Append(" TO_CHAR(proce.DTPRC,'DD/MM/YYYY') As Data "); sbSQL.Append(" FROM "); sbSQL.Append(" TBINT063 proce INNER JOIN TBINT106 prod ON proce.IDDWD016 = prod.IDINT106 "); sbSQL.Append(" WHERE "); sbSQL.Append(" proce.IDINT058 = " + m_iId058); //sbSQL.Append(" SELECT "); //sbSQL.Append(" proce.IDINT063, "); //sbSQL.Append(" prod.DSPRCD || ' ' || proce.DSVALI || ' ' || proce.DSVALCARDIO As Procedimento, "); //sbSQL.Append(" TO_CHAR(proce.DTPRC,'DD/MM/YYYY') As Data "); //sbSQL.Append(" FROM "); //sbSQL.Append(" TBINT063 proce INNER JOIN TBDWD016 prod ON proce.IDDWD016 = prod.IDDWD016 "); //sbSQL.Append(" WHERE "); //sbSQL.Append(" proce.IDINT058 = " + m_iId058); Conectar(); oAdapter = ObterDataAdapter(sbSQL.ToString()); ITableMapping objMapping = oAdapter.TableMappings.Add("Table", "DsProcedimento"); objMapping.ColumnMappings.Add("IDINT063", "IDINT063"); objMapping.ColumnMappings.Add("Procedimento", "Procedimento"); objMapping.ColumnMappings.Add("Data", "Data"); oAdapter.Fill(Ds); Desconectar(); return Ds; } catch (Exception en) { m_oLog.CriarArquivoLog(en.Message, UtSessao.Sessao["UNIDADE"].ToString()); throw new Dor.Util.OperacaoInvalidaBD(en); } }