public static IDbPersist getConn(string tipo, string connectionString, string var_key = "connection_system") { IDbPersist saida = null; /* * if ( $tipo == self::DB_POSTGRESQL) * $saida = new PostGreSQLConnection ($arr_conection, $var_name); * * * if ( $tipo == self::DB_SQLSERVER) * $saida = new MSSQLConnection($arr_conection, $var_name); * * */ if (tipo == DB_MYSQL) { saida = new ConnMySql(); // MYSQLConnection($arr_conection, $var_name); saida.connect(connectionString, var_key); } if (tipo == DB_ODBC) { saida = new ConnODBC(); saida.connect(connectionString, var_key); } // $saida = new ODBCConnection($arr_conection, $var_name); return(saida); }
private void carregaNome(string opcao) { if (getValor(txtCodigo) == String.Empty && opcao != "1") { setValor(txtDescricao, String.Empty); setValor(hdIDRegistro, String.Empty); return; } IDbPersist oConn = ConnAccess.getConn(); string filtro = " and " + this.ColunaCodigoConsulta + " = " + valorFormatado(); if (opcao == "1") { filtro = " and " + this.ColunaIDConsulta + " = " + getValor(hdIDRegistro); } Control txtIdColigada = Utilities.Format.localizaControl("txtIdColigada", this.Page.Form); if (txtIdColigada != null && getValor(txtIdColigada) != String.Empty) { filtro += " and CODCOLIGADA = " + getValor(txtIdColigada); } if (TabelaConsulta.Substring(0, 1) == ".") { // TabelaConsulta = SessionFacade.getApp("bancoRM") + TabelaConsulta; } DataTable dt = ConnAccess.fetchData(oConn, "select " + this.ColunaIDConsulta + " as id, " + this.ColunaCodigoConsulta + " as cod, " + this.ColunaDescricao + " as descr from " + this.TabelaConsulta + " where 1 = 1 " + filtro); if (dt.Rows.Count > 0) { DataRow dr = dt.Rows[0]; setValor(txtCodigo, dr["cod"]); setValor(hdIDRegistro, dr["id"]); setValor(txtDescricao, dr["descr"]); if (DispararAposLocalizar != null) { DispararAposLocalizar(dr, EventArgs.Empty); } } else { Alert("Registro não localizado!"); setValor(txtDescricao, String.Empty); setValor(hdIDRegistro, String.Empty); return; } }
/// <summary> /// Método para salvar os emails que vem da tela. /// </summary> /// <param name="id_contato"></param> /// <param name="oConn"></param> private void salvaEmails(int id_contato, IDbPersist oConn) { DataRow dr_email = ConnAccess.getNewRow(oConn, "contato_email"); int qtde_emails = RendLibrary.Helper.RequestValue <int>("qtde_emails").Value; string str_ids = "0"; int ordem = 0; string emails = ""; for (int i = 0; i <= qtde_emails; i++) { string email = RendLibrary.Helper.RequestString("email" + i.ToString()); if (email.Trim() != String.Empty) { ordem++; if (emails != String.Empty) { emails += ", "; } emails += email.Trim(); dr_email["email"] = email; dr_email["id_contato"] = id_contato; dr_email["ordem_cadastro"] = ordem; string sql_item = " select id from contato_email where email='" + email + "' and id_contato=" + id_contato.ToString(); object id_tmp = ConnAccess.executeScalar(oConn, sql_item); if (id_tmp != null && id_tmp != DBNull.Value) { dr_email["id"] = id_tmp; ConnAccess.Update(oConn, dr_email, "id"); } else { ConnAccess.Insert(oConn, dr_email, "id", true); dr_email["id"] = ConnAccess.getMax(oConn, "id", dr_email.Table.TableName, " where id_contato = " + id_contato.ToString()); } str_ids += "," + dr_email["id"].ToString(); } } ConnAccess.executeCommand(oConn, " delete from contato_email where id_contato = " + id_contato.ToString() + " and id not in ( " + str_ids + " ) "); ConnAccess.executeCommand(oConn, " update contato set emails = '" + emails + "' where id = " + id_contato.ToString()); }
public static int getMax(IDbPersist oConn, string coluna, string tabela, string where) { string sql = " select max(" + coluna + ") from " + tabela + where; object cod = oConn.executeScalar(sql); if (cod != null && cod.ToString() != String.Empty) { return(Convert.ToInt32(cod.ToString())); } return(0); }
/// <summary> /// Get One Row from DataBase /// </summary> /// <param name="oConn"></param> /// <param name="table"></param> /// <param name="where"></param> /// <param name="order"></param> /// <returns></returns> public static DataRow fastOne(IDbPersist oConn, string table, string where = "", string order = "") { DataTable ar = ConnAccess.fastQuerie(oConn, table, where, order); // connAccess.fastQuerie( $oConn, $table, $where, $order ); if (ar.Rows.Count > 0) { DataRow ret = ar.Rows[0]; return(ret); } return(null); }
/// <summary> /// Fast querie. Get a sql querie informing table + where /// </summary> /// <param name="oConn"></param> /// <param name="table"></param> /// <param name="where"></param> /// <param name="order"></param> /// <returns></returns> public static DataTable fastQuerie(IDbPersist oConn, string table, string where = "", string order = "") { string sql = " select * from " + table; if (where != "") { sql += " where " + where; } if (order != "") { sql += " order by " + order; } return(oConn.fetchDados(sql)); }
public static IDbPersist getConn() { System.Web.HttpContext Gcontext = System.Web.HttpContext.Current; IDbPersist oConn = null; if (Gcontext.Items["_myconn"] != null) { oConn = (IDbPersist)Gcontext.Items["_myconn"]; } else { oConn = FactoryConn.getConn(System.Configuration.ConfigurationManager.AppSettings["tipo_sgbd"], System.Configuration.ConfigurationManager.ConnectionStrings["bdConnectionString"].ToString(), "connmysql"); } return(oConn); }
public static DataRow getRow(IDbPersist conn, string tabela, string pk, string id) { string sql = " select * from " + tabela + " where " + pk + " = " + id; DataTable ds = new DataTable(); ds = ConnAccess.fetchData(conn, sql); ds.TableName = tabela; if (ds.Rows.Count > 0) { return(ds.Rows[0]); } return(null); }
/// <summary> /// Cria um DataRow de dados vazio /// </summary> /// <param name="conn"></param> /// <param name="tabela"></param> /// <returns></returns> public static DataRow getNewRow(IDbPersist conn, string tabela) { string sql = " select * from " + tabela + " where 1 = 0 "; DataTable ds = new DataTable(); try { ds = ConnAccess.fetchData(conn, sql); DataRow drr = ds.NewRow(); drr.Table.TableName = tabela; return(drr); } catch (Exception exp) { throw exp; } return(null); }
/// <summary> /// Obtém uma linha DataRow. /// </summary> /// <param name="conn">Conexão Usada</param> /// <param name="tabela"></param> /// <param name="comp"></param> /// <returns></returns> public static DataRow getRow(IDbPersist conn, string tabela, string comp) { string sql = " select * from " + tabela + " where 1 = 1 " + comp; try { DataTable ds = new DataTable(); ds = ConnAccess.fetchData(conn, sql); ds.TableName = tabela; if (ds.Rows.Count > 0) { return(ds.Rows[0]); } } catch (Exception exp) { throw new Exception(sql + " - " + exp.Message); } return(null); }
private void insereCampoFiltro(HtmlTableCell td, string nome, string tipo, string regra, string[] parametros, DataRowView drv) { if (parametros.Length > 0 && parametros[0] != String.Empty) { DropDownList cmb = new DropDownList(); cmb.ID = "g_txt_filtro_" + nome; string lista = parametros[0]; if (lista.IndexOf("lista:") > -1) { string[] qu0 = lista.Split(':'); string[] qu = qu0[1].Split('|'); System.Collections.Generic.IList <Entities.SimplesCodigoNome> ls_combo = new System.Collections.Generic.List <Entities.SimplesCodigoNome>(); for (int i = 0; i < qu.Length; i++) { string item = qu[i]; string[] it = item.Split(','); ls_combo.Add(new Entities.SimplesCodigoNome(it[0], it[1])); } carregaCombo(cmb, ls_combo, "Codigo", "Nome", String.Empty, true); } if (lista.IndexOf("querie:") > -1) { string[] qu = lista.Split(':'); //----------- Filtro por dependência ------------------ if (drv.DataView.Table.Columns.Contains("dependencia")) { if (drv["dependencia"].ToString() != String.Empty) { string[] ar_ttmp = drv["dependencia"].ToString().Split('$'); for (int sy = 0; sy < ar_ttmp.Length; sy++) { if (ar_ttmp[sy].Trim() == String.Empty) { continue; } string[] lipmid = ar_ttmp[sy].Split(new string[] { "->" }, System.StringSplitOptions.None); if (getRequest("g_txt_filtro_" + lipmid[0]) != "") { qu[1] = qu[1].Replace("($)", lipmid[1].Replace("{0}", getRequest("g_txt_filtro_" + lipmid[0]))); } } } //Control cr_btPesq = Utilities.Format.localizaControl("btPesquisar", this.Page.Form); //&& cr_btPesq != null && cr_btPesq is Button for (int sy = 0; sy < drv.DataView.Count; sy++) { if (drv.DataView[sy]["dependencia"] != DBNull.Value && drv.DataView[sy]["dependencia"].ToString().IndexOf(nome + "->") > -1) { cmb.Attributes.Remove("onchange"); cmb.Attributes.Add("onchange", this.Page.GetPostBackClientEvent(Button1, String.Empty)); } } } qu[1] = qu[1].Replace("($)", ""); // ------------------------------------------- try { IDbPersist oConn = ConnAccess.getConn(); DataTable dtaB = ConnAccess.fetchData(oConn, qu[1]); carregaCombo(cmb, dtaB, dtaB.Columns[0].ColumnName, dtaB.Columns[1].ColumnName, String.Empty, true); dtaB.Dispose(); } catch (Exception exp) { //problema ao montar o componente!.. } } td.Controls.Add(cmb); return; } if (tipo == "System.Double" || tipo == "System.Decimal") { HtmlGenericControl span = new HtmlGenericControl("span"); span.InnerHtml = " de "; td.Controls.Add(span); TextBox txt = new TextBox(); txt.ID = "g_txt_filtro_" + nome; txt.Width = Unit.Parse("70px"); txt.CssClass = "c_txt_filtro"; txt.MaxLength = 18; setSoDecimal(txt); td.Controls.Add(txt); HtmlGenericControl span2 = new HtmlGenericControl("span"); span2.InnerHtml = " até "; td.Controls.Add(span2); TextBox txt2 = new TextBox(); txt2.ID = "g_txt_filtro_" + nome + "2"; txt2.Width = Unit.Parse("70px"); txt2.CssClass = "c_txt_filtro"; txt2.MaxLength = 18; setSoDecimal(txt2); td.Controls.Add(txt2); return; } else if (tipo == "System.DateTime") { System.Web.UI.UserControl uc = (System.Web.UI.UserControl) this.Page.LoadControl("~/controles/UcFaseDatas.ascx"); td.Controls.Add(uc); ((IField)uc).Auxiliar = "N"; uc.ID = "g_txt_filtro_" + nome; if (uc is IFieldDataHora) { ((IFieldDataHora)uc).mostraHora = true; } return; } else { TextBox txt = new TextBox(); txt.ID = "g_txt_filtro_" + nome; txt.Width = Unit.Parse("150px"); txt.CssClass = "c_txt_filtro"; txt.MaxLength = 250; if (tipo == "System.Int32" || tipo == "System.Int16" || tipo == "System.Int64" || tipo == "System.Byte") { txt.MaxLength = 20; setSoNumero(txt); } else { txt.Width = Unit.Parse("95%"); } td.Controls.Add(txt); } }
public DataView getCampos() { string str_driver = PageCadastroBase.getExcelDriver(); if (str_driver == "") { str_driver = "Microsoft Excel Driver (*.xls)"; } string conexao = "Driver={" + str_driver + "};DriverId=790;DBQ=" + this.CaminhoExcel + ";ReadOnly= true ; "; //conn.ConnectionString = conexao; ConnODBC OdbcConn = null; try { IDbPersist oTmp = FactoryConn.getConn("odbc", conexao, "odbc"); OdbcConn = (ConnODBC)oTmp; //conn.Open(); } catch (Exception exp) { Trace.Write("Erro ao tentar conectar o banco de dados de filtro via ODBC: " + exp.Message + " - Arquivo em formato inválido! - String de Conexão:" + conexao); //Alert("Erro ao tentar conectar o banco de dados de filtro via ODBC: " + // exp.Message + " - Arquivo em formato inválido! "); return(null); } OdbcConnection conn = (OdbcConnection)OdbcConn.getConn(); string[] restrictions = new string[4]; restrictions[3] = "Table"; DataTable userTables = conn.GetSchema("Tables"); string my_tabela = this.Tabela; for (int i = 0; i < userTables.Rows.Count; i++) { string tab_col = userTables.Rows[i][2].ToString(); if (userTables.Rows[i][3].ToString().IndexOf("TABLE") > -1 && tab_col == this.Tabela + "$") { my_tabela = userTables.Rows[i][2].ToString(); break; } } DataTable dt_campos = ConnAccess.fetchData(OdbcConn, "select * from [" + my_tabela + "] where visivel = 'True' order by tipo desc, ordem asc, label asc "); //primaryKey desc, label, conn.Close(); conn.Dispose(); string add_filtro = string.Empty; if (dt_campos.Columns.Contains("filtro")) { add_filtro += " and filtro = '1' "; } DataView dw = dt_campos.DefaultView; return(dw); }
/// <summary> /// Insert new record /// </summary> /// <param name="oConn"></param> /// <param name="dr"></param> /// <param name="pk"></param> /// <param name="auto_increment"></param> public static void Insert(IDbPersist oConn, DataRow dr, string pk, bool auto_increment = true) { oConn.Insert(dr, auto_increment, pk); }
public void carregaMenu() { string filtro = string.Empty; //if (!SessionFacade.Admin) //{ // filtro += " and pagina in ('"+ // SessionFacade.getPropriedade("_paginas").Replace(",","','")+"') "; //} if (!SessionFacade.Admin) { filtro += " and id in (select id_processo from perfil_processos where id_perfil in ( " + SessionFacade.listaProcessos + ") and acao is not null ) "; } ////if (!SessionFacade.Admin) ////{ //// filtro += " and id in ( " + //// SessionFacade.listaProcessos + ") "; ////} IDbPersist oConn = ConnAccess.getConn(); DataTable dt = ConnAccess.fetchData(oConn, " select * from menu where nivel not like 'MO%' " + " and id_item_pai is null " + filtro + " order by nivel asc "); string menu = string.Empty; for (int i = 0; i < dt.Rows.Count; i++) { DataRow dr = dt.Rows[i]; DataTable dt_itens = ConnAccess.fetchData(oConn, " select * from menu where id_item_pai = " + dr["id"].ToString() + filtro + " order by funcionalidade asc "); if (base.Nvl(dr["pagina"], "#").ToString() == "#" && dt_itens.Rows.Count <= 0) { //É um menu, sem filhos, então não precisa aparecer... } else { menu += System.Environment.NewLine + "<li class=\"pureCssMenui0\"><a class=\"pureCssMenui0\" " + " href=\"" + base.Nvl(dr["pagina"], "#").ToString() + "\"><span>" + dr["funcionalidade"].ToString() + "</span><![if gt IE 6]></a><![endif]><!--[if lte IE 6]><table><tr><td><![endif]-->"; } if (dt_itens.Rows.Count > 0) { menu += System.Environment.NewLine + "<ul class=\"pureCssMenum\">"; for (int z = 0; z < dt_itens.Rows.Count; z++) { menu += System.Environment.NewLine + "<li class=\"pureCssMenui\"><a class=\"pureCssMenui\" href=\"" + dt_itens.Rows[z]["pagina"].ToString() + "\">" + dt_itens.Rows[z]["funcionalidade"].ToString() + "</a></li>"; } menu += "</ul>"; } menu += "<!--[if lte IE 6]></td></tr></table></a><![endif]--></li>"; } ul_menu.InnerHtml = menu; }
/// <summary> /// Cuida de salvar informações provenientes da página /// </summary> /// <param name="bas"></param> /// <param name="entidade"></param> /// <param name="carregaGrid"></param> /// <param name="msg"></param> /// <param name="msgSemSucesso"></param> protected virtual void salvar(DataRow entidade, Boolean carregaGrid, string msg, string msgSemSucesso) { Boolean validado = true; if (validaSalvar) { if (this.Tipovalidacao.Equals(1)) { validado = validar(); } else { validado = validar(entidade); } } try { if (!validado) { return; } string nomepk = ConnAccess.getNomePrimaryKey(entidade.Table); IDbPersist oConn = ConnAccess.getConn(); if (entidade[nomepk] != DBNull.Value && Convert.ToInt32(entidade[nomepk]) > 0) { ConnAccess.Update(oConn, entidade, nomepk); } else { ConnAccess.Insert(oConn, entidade, nomepk, true); } if (carregaGrid) { this.carregaGrid(); } if (msg != String.Empty) { Alert(msg); } if (this.limpaFormulario) { this.limpaForm(); } } catch (Exception e) { this.HouveErro = true; if (msgSemSucesso != String.Empty || e is System.Data.OleDb.OleDbException) { string innerMsg = string.Empty; if (e.InnerException != null) { innerMsg = " ( " + e.InnerException.Message + ") "; } if (msgSemSucesso != String.Empty) { Alert(msgSemSucesso + " :" + e.Message + innerMsg); } else { Alert(e.Message + innerMsg); } } } }
/// <summary> /// Execute a command /// </summary> /// <param name="oConn"></param> /// <param name="sql"></param> public static void executeCommand(IDbPersist oConn, string sql) { oConn.executeCommand(sql); }
/// <summary> /// Executing a scalar function /// </summary> /// <param name="oConn"></param> /// <param name="sql"></param> /// <returns></returns> public static object executeScalar(IDbPersist oConn, string sql) { return(oConn.executeScalar(sql)); }
/// <summary> /// Perfoms a querie /// </summary> /// <param name="oConn"></param> /// <param name="sql"></param> /// <returns></returns> public static DataTable fetchData(IDbPersist oConn, string sql) { return(oConn.fetchDados(sql)); }
public StoreScp(ILogger <StoreScp> logger, IDbPersist dbPersist) { _logger = logger; db = dbPersist; }
/// <summary> /// Update a record /// </summary> /// <param name="oConn"></param> /// <param name="dr"></param> /// <param name="pk"></param> public static void Update(IDbPersist oConn, DataRow dr, string pk) { oConn.Update(dr, pk); }