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