public override void Write(int?chunkId, int?subChunkId, System.Data.IDataReader reader, string tableName) { if (reader == null) { return; } if (tableName.ToLower().StartsWith("_chunks")) { tableName = SourceSchema + "." + tableName; } else { tableName = DestinationSchema + "." + tableName; } var fields = new string[reader.FieldCount]; for (var i = 0; i < reader.FieldCount; i++) { fields[i] = reader.GetName(i); } var q = $"COPY {tableName} ({string.Join(",", fields)}) from STDIN (FORMAT BINARY)"; using (var inStream = _connection.BeginBinaryImport(q)) { while (reader.Read()) { inStream.StartRow(); for (var i = 0; i < reader.FieldCount; i++) { var value = reader.GetValue(i); if (value is null) { inStream.WriteNull(); } else { var fd = GetFieldType(reader.GetFieldType(i), reader.GetName(i)); inStream.Write(value, fd); } } } inStream.Complete(); } }
public override void Write(int?chunkId, int?subChunkId, System.Data.IDataReader reader, string tableName) { var folder = _cdmFolder; folder = Path.Combine(folder, tableName); if (!Directory.Exists(folder)) { Directory.CreateDirectory(folder); } if (chunkId.HasValue) { _fileName = Path.Combine(folder, chunkId.Value.ToString(CultureInfo.InvariantCulture)); } var isFirstRow = true; using (var writer = new StreamWriter(_fileName)) { while (reader.Read()) { if (isFirstRow) { var header = new StringBuilder(); for (var i = 0; i < reader.FieldCount; i++) { header.Append(reader.GetName(i)); if (i != reader.FieldCount - 1) { header.Append("\t"); } } writer.WriteLine(header); isFirstRow = false; } var row = new StringBuilder(); for (var i = 0; i < reader.FieldCount; i++) { row.Append(reader.GetValue(i)); if (i != reader.FieldCount - 1) { row.Append("\t"); } } writer.WriteLine(row); } } }
public static V Enum <V>(System.Data.IDataReader reader, MapPoint mapPoint, V defaultValue) { try { if (mapPoint.IsInResultSet() && !reader.IsDBNull(mapPoint.Index)) { return((V)System.Enum.Parse(typeof(V), reader.GetValue(mapPoint.Index).ToString())); } } catch (Exception) { throw; } return(defaultValue); }
protected override LuUserType MapObject(System.Data.IDataReader r) { var entity = new LuUserType(); try { var ordinal = r.GetOrdinal("ID"); if (!r.IsDBNull(ordinal)) { entity.Id = ((int)(r.GetValue(ordinal))); } } catch (Exception) { } try { var ordinal = r.GetOrdinal("USER_TYPE"); if (!r.IsDBNull(ordinal)) { entity.UserType = ((string)(r.GetValue(ordinal))); } } catch (Exception) { } try { var ordinal = r.GetOrdinal("ACTIVE_FLAG"); if (!r.IsDBNull(ordinal)) { entity.ActiveFlag = ((char)(r.GetValue(ordinal))); } } catch (Exception) { } return(entity); }
private T Reader2Item(System.Data.IDataReader reader) { if (!reader.Read()) { return(null); } var row = new T(); row.DataFilling = true; for (var i = 0; i < reader.FieldCount; i++) { row[reader.GetName(i)] = reader.GetValue(i); } row.DataFilling = false; return(row); }
private static IEnumerable <string> ReaderToStringList(System.Data.IDataReader rd) { using (rd) while (rd.Read()) { var v = rd.GetValue(0); if (v == null || v == DBNull.Value) { yield return(null); } else { yield return(v.ToString()); } } }
internal void CollectAll(System.Data.IDataReader dr) { var _pg = dr.GetValue(0); var _str = _pg as string; if (_str != null) { list.Add(_DatabaseCommon.FactoryFormABC_Input.InputConverter.CreateFromRecord(cms.UseBufferedReader(_str), 0, locator)); } else { var _tr = _pg as System.IO.TextReader ?? new System.IO.StringReader(_pg.ToString()); try { list.Add(_DatabaseCommon.FactoryFormABC_Input.InputConverter.CreateFromRecord(cms.UseBufferedReader(_tr), 0, locator)); } finally { _tr.Dispose(); } } }
internal void CollectOne(System.Data.IDataReader dr) { var _pg = dr.GetValue(0); var _str = _pg as string; if (_str != null) { one = _DatabaseCommon.FactoryTest_XYZ.XYZConverter.CreateFromRecord(cms.UseBufferedReader(_str), 0, locator); } else { var _tr = _pg as System.IO.TextReader ?? new System.IO.StringReader(_pg.ToString()); try { one = _DatabaseCommon.FactoryTest_XYZ.XYZConverter.CreateFromRecord(cms.UseBufferedReader(_tr), 0, locator); } finally { _tr.Dispose(); } } }
public object Get(UserCsvListRequestTep request) { var context = TepWebContext.GetWebContext(PagePrivileges.AdminOnly); var csv = new System.Text.StringBuilder(); var filename = DateTime.UtcNow.ToString("yy-MM-dd"); try { context.Open(); context.LogInfo(this, string.Format("/user/csv GET")); filename = context.GetConfigValue("SiteNameShort") + "-" + filename; string sql = string.Format("SELECT usr.id,usr.username,usr.email,usr.firstname,usr.lastname,usr.level,usr.affiliation,usr.country,(SELECT log_time FROM usrsession WHERE id_usr=usr.id ORDER BY log_time ASC LIMIT 1) AS registration_date FROM usr;"); csv.Append("Id,Username,Email,FisrtName,LastName,Level,Affiliation,Country,Registration date" + Environment.NewLine); System.Data.IDbConnection dbConnection = context.GetDbConnection(); System.Data.IDataReader reader = context.GetQueryResult(sql, dbConnection); while (reader.Read()) { csv.Append(String.Format("{0},{1},{2},{3},{4},{5},{6},{7},{8}{9}", reader.GetValue(0) != DBNull.Value ? reader.GetString(0) : "", reader.GetValue(1) != DBNull.Value ? reader.GetString(1) : "", reader.GetValue(2) != DBNull.Value ? reader.GetString(2) : "", reader.GetValue(3) != DBNull.Value ? reader.GetString(3) : "", reader.GetValue(4) != DBNull.Value ? reader.GetString(4) : "", reader.GetValue(5) != DBNull.Value ? reader.GetString(5) : "", reader.GetValue(6) != DBNull.Value ? reader.GetString(6) : "", reader.GetValue(7) != DBNull.Value ? reader.GetString(7) : "", reader.GetValue(8) != DBNull.Value ? reader.GetString(8) : "", Environment.NewLine)); } context.CloseQueryResult(reader, dbConnection); context.Close(); } catch (Exception e) { context.LogError(this, e.Message, e); context.Close(); throw e; } Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}.csv", filename)); return(csv.ToString()); }
/// <summary> /// 从阅读取中获取当前数据行 /// </summary> /// <param name="reader">数据库表游标</param> /// <returns>用列名为key。值为value的结果集</returns> private static Dictionary <string, object> GetDataByReader(System.Data.IDataReader reader) { var result = new Dictionary <string, object>(); for (var i = 0; i < reader.FieldCount; i++) { //读取列名,并最小化 var name = reader.GetName(i).ToLower(); if (!result.ContainsKey(name)) { var value = reader.GetValue(i); result.Add(name, value); } } return(result); }
} // End Function RemoveSingleLineSqlComments public virtual T GetClass <T>(System.Data.IDbCommand cmd, T tThisClassInstance) { System.Type t = typeof(T); lock (cmd) { using (System.Data.IDataReader idr = ExecuteReader(cmd)) { lock (idr) { while (idr.Read()) { int iFieldCount = idr.FieldCount; Setter_t <T>[] mems = new Setter_t <T> [iFieldCount]; for (int i = 0; i < iFieldCount; ++i) { string strName = idr.GetName(i); mems[i] = LinqHelper.GetSetter <T>(strName); } // Next i for (int i = 0; i < idr.FieldCount; ++i) { Setter_t <T> setter = mems[i]; if (setter != null) { object objVal = idr.GetValue(i); setter(tThisClassInstance, objVal); } } // Next i break; } // Whend idr.Close(); } // End Lock idr } // End Using idr } // End lock cmd return(tThisClassInstance); } // End Function GetClass
/// <summary> /// Reperimento dei ruoli che hanno la visibilità sul nodo di titolario /// </summary> /// <param name="idNodoTitolario"></param> /// <returns></returns> public static string[] getRuoliNodoTitolario(string idNodoTitolario) { List <string> list = new List <string>(); using (DocsPaDB.DBProvider dbProvider = new DocsPaDB.DBProvider()) { string commandText = string.Format("SELECT g.group_id FROM security s, groups g WHERE s.personorgroup = g.system_id AND s.thing = {0}", idNodoTitolario); using (System.Data.IDataReader reader = dbProvider.ExecuteReader(commandText)) { while (reader.Read()) { list.Add(reader.GetValue(0).ToString()); } } } return(list.ToArray()); }
public static bool EnableHTML5SocketOption() { bool retVal = false; using (DocsPaDB.DBProvider dbProvider = new DBProvider()) { DocsPaUtils.Query queryDef = DocsPaUtils.InitQuery.getInstance().getQuery("GET_TIPO_COMPONENTI"); string commandText = queryDef.getSQL(); using (System.Data.IDataReader reader = dbProvider.ExecuteReader(commandText)) { while (reader.Read()) { if (reader.GetValue(reader.GetOrdinal("CHA_TIPO_COMPONENTI")).ToString() == "4") { retVal = true; } } } } return(retVal); }
public static Guid Guid(System.Data.IDataReader reader, MapPoint mapPoint, Guid defaultValue) { try { if (mapPoint.IsInResultSet() && !reader.IsDBNull(mapPoint.Index)) { if (mapPoint.Type == System.Data.SqlDbType.UniqueIdentifier) { return(reader.GetGuid(mapPoint.Index)); } else { return(new Guid(reader.GetValue(mapPoint.Index).ToString())); } } } catch (Exception) { throw; } return(defaultValue); }
private List <T> Reader2List(System.Data.IDataReader reader) { string[] fields = new string[reader.FieldCount]; for (var i = 0; i < reader.FieldCount; i++) { fields[i] = reader.GetName(i); } var list = new List <T>(); while (reader.Read()) { var row = new T(); row.DataFilling = true; for (var i = 0; i < reader.FieldCount; i++) { row[fields[i]] = reader.GetValue(i); } row.DataFilling = false; list.Add(row); } return(list); }
public List <KeyValuePair <string, int> > GetTopUsedServices(int count, List <int> userIds = null) { List <KeyValuePair <string, int> > result = new List <KeyValuePair <string, int> >(); string sql = string.Format("SELECT r.sname,SUM(r.snb) as nb FROM " + "(SELECT service.name as sname, count(wpsjob.process) as snb FROM wpsjob INNER JOIN service ON service.identifier = wpsjob.process {1}group by wpsjob.process order by snb desc) as r " + "GROUP BY r.sname " + "ORDER BY nb desc limit {0};", count, userIds != null ? "AND wpsjob.id_usr IN (" + string.Join(",", userIds) + ") " : ""); System.Data.IDbConnection dbConnection = Context.GetDbConnection(); System.Data.IDataReader reader = Context.GetQueryResult(sql, dbConnection); while (reader.Read()) { if (reader.GetValue(0) != DBNull.Value) { var service = reader.GetString(0); var nb = reader.GetInt32(1); result.Insert(0, new KeyValuePair <string, int>(service, nb)); } } Context.CloseQueryResult(reader, dbConnection); return(result); }
/// <summary> /// 从数据读取器中读取数据并填充到一个对象中 /// </summary> /// <param name="ObjInstance">对象实例</param> /// <param name="info">数据库绑定信息对象</param> /// <param name="reader">数据读取器</param> private int InnerReadValues(object ObjInstance, TableBindInfo info, System.Data.IDataReader reader) { // 检查参数 if (ObjInstance == null) { throw new ArgumentNullException("ObjectInstance"); } if (info == null) { throw new ArgumentNullException("info"); } if (reader == null) { throw new ArgumentNullException("reader"); } int FieldCount = 0; // 依次读取各个属性值 foreach (FieldBindInfo field in info.Fields) { // 绑定的属性是只读的 if (field.Property.CanWrite == false) { continue; } // 没有找到绑定的字段 if (field.FieldIndex < 0) { continue; } // 读取数据字段值 object v = reader.GetValue(field.FieldIndex); v = field.FromDataBase(v); // 设置对象属性值 field.Property.SetValue(ObjInstance, v, null); FieldCount++; } //foreach return(FieldCount); }
/// <summary> /// Gets the new users. /// </summary> /// <returns>The new users.</returns> /// <param name="context">Context.</param> /// <param name="startdate">Startdate.</param> /// <param name="enddate">Enddate.</param> /// <param name="skipids">Skipids.</param> /// <param name="allowedids">Allowedids.</param> public static NameValueCollection GetNewUsers(IfyContext context, string startdate, string enddate, string skipids, string allowedids) { var ids = new NameValueCollection(); string sql = string.Format("SELECT DISTINCT usr.id,usr.level FROM usr {0}{1}{2};", !string.IsNullOrEmpty(startdate) && !string.IsNullOrEmpty(enddate) ? " WHERE id NOT IN (SELECT id_usr FROM usrsession WHERE usrsession.log_time < '" + startdate + "' ) AND id IN (SELECT id_usr FROM usrsession WHERE usrsession.log_time <= '" + enddate + "')" : "", string.IsNullOrEmpty(skipids) ? "" : (string.IsNullOrEmpty(startdate) || string.IsNullOrEmpty(enddate) ? " WHERE " : " AND ") + "id NOT IN (" + skipids + ")", string.IsNullOrEmpty(allowedids) ? "" : ((string.IsNullOrEmpty(startdate) || string.IsNullOrEmpty(enddate)) && string.IsNullOrEmpty(skipids) ? " WHERE " : " AND ") + " id IN (" + allowedids + ")"); System.Data.IDbConnection dbConnection = context.GetDbConnection(); System.Data.IDataReader reader = context.GetQueryResult(sql, dbConnection); while (reader.Read()) { if (reader.GetValue(0) != DBNull.Value) { var id = reader.GetInt32(0); var level = reader.GetInt32(1); ids.Set(id + "", level + ""); } } context.CloseQueryResult(reader, dbConnection); return(ids); }
public static IEnumerable <Dictionary <string, object> > AsEnumerable(this System.Data.IDataReader source) { if (source == null) { throw new ArgumentNullException("source"); } while (source.Read()) { Dictionary <string, object> row = new Dictionary <string, object>(); for (int i = 0; i < source.FieldCount; i++) { object value = source.GetValue(i); // return an empty string for dbnull value of field type string if (source.GetFieldType(i) == typeof(string) && source.IsDBNull(i)) { value = string.Empty; } row.Add(source.GetName(i), value); } yield return(row); } }
// // il modificatore sealed impedisce ad altre classi di ereditare da tale classe. /// <summary> /// /// </summary> /// <param name="codiceAmministrazione"></param> /// <returns></returns> public static ContatoriEsibizione GetByCodice(string codiceAmministrazione, string idGruppo, string idPeople) { DocsPaVO.utente.Amministrazione[] amministrazioni = (DocsPaVO.utente.Amministrazione[]) BusinessLogic.Amministrazione.AmministraManager.GetAmministrazioni().ToArray(typeof(DocsPaVO.utente.Amministrazione)); if (amministrazioni.Count(e => e.codice.ToLowerInvariant() == codiceAmministrazione.ToLowerInvariant()) == 0) { throw new ApplicationException(string.Format("Amministrazione con codice '{0}' non trovata", codiceAmministrazione)); } else { ContatoriEsibizione retValue = new ContatoriEsibizione(); using (DocsPaDB.DBProvider dbProvider = new DocsPaDB.DBProvider()) { DocsPaUtils.Query queryDef = DocsPaUtils.InitQuery.getInstance().getQuery("S_GET_COUNT_ITEMS_ESIBIZIONE"); queryDef.setParam("codiceAmministrazione", codiceAmministrazione); queryDef.setParam("idPeople", idPeople); string q1 = " SELECT dpa_corr_globali.system_id FROM dpa_corr_globali WHERE dpa_corr_globali.id_gruppo = " + idGruppo; string idRuoloInUo = string.Empty; dbProvider.ExecuteScalar(out idRuoloInUo, q1); queryDef.setParam("idRuoloInUo", idRuoloInUo); string commandText = queryDef.getSQL(); using (System.Data.IDataReader reader = dbProvider.ExecuteReader(commandText)) { if (reader != null) { while (reader.Read()) { string stato = reader.GetString(reader.GetOrdinal("STATO")); string cha_certificata = string.Empty; try { cha_certificata = reader.GetString(reader.GetOrdinal("CERTIFICATA")); } catch (Exception e) { cha_certificata = string.Empty; } // Campo totale della Count int totale = Convert.ToInt32(reader.GetValue(reader.GetOrdinal("TOTALE")).ToString()); if (stato == DocsPaConservazione.StatoIstanzaEsibizione.NUOVA) { if (string.IsNullOrEmpty(cha_certificata) || (!string.IsNullOrEmpty(cha_certificata) && cha_certificata == "0")) { retValue.Nuove = totale; } if (!string.IsNullOrEmpty(cha_certificata) && cha_certificata == "1") { retValue.Nuove_Certificata = totale; } } else if (stato == DocsPaConservazione.StatoIstanzaEsibizione.CHIUSA) { if (string.IsNullOrEmpty(cha_certificata) || (!string.IsNullOrEmpty(cha_certificata) && cha_certificata == "0")) { retValue.Chiuse = totale; } if (!string.IsNullOrEmpty(cha_certificata) && cha_certificata == "1") { retValue.Chiuse_Certificata = totale; } } else if (stato == DocsPaConservazione.StatoIstanzaEsibizione.RIFIUTATA) { if (string.IsNullOrEmpty(cha_certificata) || (!string.IsNullOrEmpty(cha_certificata) && cha_certificata == "0")) { retValue.Rifiutate = totale; } if (!string.IsNullOrEmpty(cha_certificata) && cha_certificata == "1") { retValue.Rifiutate_Certificata = totale; } } else if (stato == DocsPaConservazione.StatoIstanzaEsibizione.IN_ATTESA_DI_CERTIFICAZIONE) { if (string.IsNullOrEmpty(cha_certificata) || (!string.IsNullOrEmpty(cha_certificata) && cha_certificata == "0")) { retValue.InAttesaDiCertificazione = totale; } if (!string.IsNullOrEmpty(cha_certificata) && cha_certificata == "1") { retValue.InAttesaDiCertificazione_Certificata = totale; } } else if (stato == DocsPaConservazione.StatoIstanzaEsibizione.IN_TRANSIZIONE) { if (string.IsNullOrEmpty(cha_certificata) || (!string.IsNullOrEmpty(cha_certificata) && cha_certificata == "0")) { retValue.Transizione = totale; } if (!string.IsNullOrEmpty(cha_certificata) && cha_certificata == "1") { retValue.Transizione_Certificata = totale; } } } } } } return(retValue); } }
public ArrayList getInsertQueries(string operation, DocsPaVO.documento.SchedaDocumento schedaDoc, DocsPaDB.DBProvider db) { //DocsPaDB.DBProvider db=null; logger.Debug("DocsPaAS400: getInsertQueries"); ArrayList res = new ArrayList(); if (schedaDoc.protocollo == null || schedaDoc.protocollo.numero == null || schedaDoc.protocollo.numero.Equals("")) { logger.Debug("Documento non protocollato"); return(res); } string folderCode = getFolderCode(schedaDoc); if (folderCode != null && !folderCode.Equals("")) { logger.Debug("Folder code=" + folderCode); } else { logger.Debug("Folder code nullo"); return(res); } try { string key = ""; string keyQuery = getSelectKeyQuery(); logger.Debug(keyQuery); string temp = ""; //db=new DocsPaDB.DBProvider(); //db.ExecuteScalar(out temp,keyQuery); using (System.Data.IDataReader reader = db.ExecuteReader(keyQuery)) { if (reader.Read()) { temp = reader.GetValue(reader.GetOrdinal("K")).ToString(); } } if (temp == null || temp.Equals("")) { key = "0"; } else { int val = Int32.Parse(temp) + 1; key = val.ToString(); } logger.Debug("lkey:" + key); System.Collections.ArrayList queries = getQueryArray(key, operation, folderCode, schedaDoc); for (int i = 0; i < queries.Count; i++) { string query = (string)queries[i]; logger.Debug(query); res.Add(query); } string insertLogQuery = getInsertLogQuery(key); logger.Debug(insertLogQuery); res.Add(insertLogQuery); } catch (Exception e) { throw e; } finally { } return(res); }
public T GetFieldValue <T>(int ordinal) { return(OKHOSTING.Data.Convert.ChangeType <T>(NativeReader.GetValue(ordinal))); }
private CartitemInfo getDataReader(iSqlDataReader dar) { try { int i = -1; CartitemInfo info = new CartitemInfo(); info.CartID = dar.IsDBNull(++i) ? 0 : dar.GetInt32(i); info.ProductID = dar.IsDBNull(++i) ? 0 : dar.GetInt32(i); info.Productname = dar.IsDBNull(++i) ? string.Empty : dar.GetString(i); info.Quantity = dar.IsDBNull(++i) ? -1 : dar.GetInt32(i); info.Amount = dar.IsDBNull(++i) ? 0 : Math.Round(dar.GetFloat(i)); info.TemporaryID = dar.IsDBNull(++i) ? 0 : long.Parse(dar.GetValue(i).ToString()); info.Id = dar.IsDBNull(++i) ? 0 : dar.GetInt32(i); return info; } catch (Exception ex) { throw ex; } }
public void GenerateXlsxReport(System.Data.Common.DbConnection conn) { SetDbParams(conn); using (ExcelPackage package = (templateFile != null ? new ExcelPackage(newFile, templateFile) : new ExcelPackage(newFile))) { foreach (var tab in def.tabs) { var ws = GetWs(package, tab); var riadok = 1; var stlpec = 1; var start = 1; // set start position for Label - that would be Global or Local if (templateFile != null) { ExcelNamedRange label = null; try { label = ws.Names["Label"]; } catch { //Console.WriteLine("{0} Exception caught.", e); try { label = package.Workbook.Names["Label"]; } catch { SimpleLog.WriteLog("Label field not found in this workbook/template"); } } if (label != null) { riadok = label.Start.Row; stlpec = label.Start.Column; } } var nadpis = ""; if (!(tab.title == null || tab.title == "")) { if (tab.title.Trim().ToUpper().Substring(0, 6) == "SELECT") { nadpis = conn.QuerySingle <string>(tab.title, QuerryArguments(tab.title)); } else { nadpis = tab.title; } } // Main Select // https://github.com/ericmend/oracleClientCore-2.0/blob/master/test/dotNetCore.Data.OracleClient.test/OracleClientCore.cs System.Data.IDataReader reader = conn.ExecuteReader(tab.query, QuerryArguments(tab.query)); List <TableFields> rowConfig = GetDeclaration(reader, tab.fields); outDef.tabs.Add(new Tab { name = tab.name, title = tab.title, query = tab.query, fields = rowConfig }); int r = 0; int activeColCount = 0; while (reader.Read()) { r++; //Initial section for sheet if (r == 1) { if (nadpis != null && nadpis != "") { ws.Cells[riadok, stlpec].Value = nadpis; if (templateFile == null) { using (ExcelRange rr = ws.Cells[riadok, stlpec, riadok, stlpec - 1 + rowConfig.Where(o => o.order != 0).Count()]) { rr.Merge = true; //rr.Style.Font.SetFromFont(new Font("Britannic Bold", 12, FontStyle.Italic)); rr.Style.Font.Size = 12; rr.Style.Font.Bold = true; rr.Style.Font.Color.SetColor(Color.FromArgb(63, 63, 63)); rr.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.CenterContinuous; rr.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; //r.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(23, 55, 93)); rr.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(242, 242, 242)); } } riadok++; } // set Data position if (templateFile != null) { ExcelNamedRange label = null; try { label = ws.Names["Data"]; } catch { try { label = package.Workbook.Names["Data"]; } catch { SimpleLog.WriteLog("Data field not found in this workbook/template"); } } if (label != null) { riadok = label.Start.Row - 1; // Header je nad riadkom (above row) stlpec = label.Start.Column; } } //Add the headers for (int i = 0; i < rowConfig.Count; i++) { if (rowConfig[i].order != 0) { activeColCount++; if (templateFile == null || tab.printHeader) { ws.Cells[riadok, activeColCount + stlpec - 1].Value = rowConfig[i].title; } ws.Names.Add(rowConfig[i].name, ws.Cells[riadok, activeColCount + stlpec - 1]); } } //Ok now format the values; //ws.Cells[1, 1, 1, tab.fields.Count].Style.Font.Bold = true; //Font should be bold //ws.Cells[1, 1, 1, tab.fields.Count].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; //ws.Cells[1, 1, 1, tab.fields.Count].Style.Fill.BackgroundColor.SetColor(Color.Aqua); if (templateFile == null || tab.printHeader) { using (var range = ws.Cells[riadok, stlpec, riadok, activeColCount + stlpec - 1]) { range.Style.Font.Color.SetColor(Color.White); range.Style.Fill.PatternType = ExcelFillStyle.Solid; //range.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(184, 204, 228)); range.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(79, 129, 189)); range.Style.Font.Bold = true; range.Style.WrapText = true; //Only need to grab the first cell of the merged range //ws.Cells[$"A{row}"].Style.VerticalAlignment = ExcelVerticalAlignment.Center; //ws.Cells[$"A{row}"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; } } riadok++; // set line for data start = riadok; } //data section, FETCH RECORDS for (int i = 0; i < rowConfig.Count(); i++) { var colId = rowConfig[i].colId; if (rowConfig[i].order != 0 && !reader.IsDBNull(colId)) { var a = reader.GetValue(colId); switch (rowConfig[i].type) { case "String": var pom = reader.GetString(colId); ws.SetValue(riadok, rowConfig[i].order + stlpec - 1, pom.Substring(0, pom.Length /*- correctStringChars */)); break; case "Integer": ws.SetValue(riadok, rowConfig[i].order + stlpec - 1, reader.GetInt32(colId)); break; case "DateTime": ws.SetValue(riadok, rowConfig[i].order + stlpec - 1, reader.GetDateTime(colId)); break; case "Date": ws.SetValue(riadok, rowConfig[i].order + stlpec - 1, reader.GetDateTime(colId)); break; case "Decimal": ws.SetValue(riadok, rowConfig[i].order + stlpec - 1, reader.GetDecimal(colId)); break; case "Byte[]": ws.SetValue(riadok, rowConfig[i].order + stlpec - 1, reader.GetByte(colId)); break; default: ws.SetValue(riadok, rowConfig[i].order + stlpec - 1, reader.GetValue(colId).ToString()); break; } ; } } riadok++; } // no rows if (r == 0) { if (nadpis != null) { ws.Cells[riadok, stlpec].Value = nadpis; using (ExcelRange rr = ws.Cells[riadok, stlpec, riadok, 8 + stlpec]) { rr.Merge = true; rr.Style.Font.Size = 12; rr.Style.Font.Bold = true; rr.Style.Font.Color.SetColor(Color.FromArgb(63, 63, 63)); rr.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.CenterContinuous; rr.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; rr.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(242, 242, 242)); } riadok++; } ws.Cells[riadok, stlpec].Value = "No rows in querry result"; SimpleLog.WriteLog("No rows in Query from " + tab.name); } else { foreach (var row in rowConfig.Where(o => o.format != null && o.format != "auto")) { ws.Cells[start, row.order + stlpec - 1, riadok, row.order + stlpec - 1].Style.Numberformat.Format = row.format; } //ws.Cells[1, 1, Rows, 1].Style.Numberformat.Format = "#,##0"; //ws.Cells[1, 3, Rows, 3].Style.Numberformat.Format = "YYYY-MM-DD"; //ws.Cells[1, 4, Rows, 5].Style.Numberformat.Format = "#,##0.00"; // add comp fields // worksheet.Cell(5, 2).Formula = string.Format("SUM({0}:{1})", calcStartAddress, calcEndAddress); // Autofit if (templateFile == null) { ws.Cells[start - 1, stlpec, riadok, activeColCount + stlpec - 1].AutoFitColumns(); foreach (var row in rowConfig.Where(o => o.order != 0)) { if (ws.Column(row.order).Width < row.minsize) { ws.Column(row.order).Width = row.minsize; } } } //Create an autofilter(global settings) for the range if (def.autofilter) { ws.Cells[start - 1, stlpec, riadok, activeColCount + stlpec - 1].AutoFilter = true; } } if (enabledTimestamp && def.timestamp) { ws.Cells[riadok + 2, stlpec].Value = "Created : " + DateTime.Now.ToString("dd.MM.yyyy H:mm:ss"); } } package.Workbook.Calculate(); // Set document properties package.Workbook.Properties.Comments = "Created with EpSqlGen Copyright © 2018 Miroslav Dubovsky"; package.Workbook.Properties.Created = DateTime.Now; package.Workbook.Properties.Title = outFileName; var pomProp = System.Configuration.ConfigurationManager.AppSettings.Get("Author"); if (pomProp != null) { package.Workbook.Properties.Author = pomProp; } pomProp = System.Configuration.ConfigurationManager.AppSettings.Get("Company"); if (pomProp != null) { package.Workbook.Properties.Company = pomProp; } if (def.version != null) { package.Workbook.Properties.Subject = "Template " + defFileName + ", version:" + def.version; } else { package.Workbook.Properties.Subject = "Template " + defFileName; } package.Save(); } if (!enabledConsoleLog) { Console.Write(JsonConvert.SerializeObject(new { output_file = newFile.FullName }, Formatting.Indented)); } else { SimpleLog.WriteLog("Generated file: " + newFile.FullName); } }
public Fileversion(System.Data.IDataReader reader) { m_reader = reader; this.Path = reader.GetValue(0).ToString(); this.More = true; }
public BlockWithSources(System.Data.IDataReader rd) : base(rd.GetValue(0).ToString(), Convert.ToInt64(rd.GetValue(1))) { m_rd = rd; Done = !m_rd.Read(); }
/// <summary> /// /// </summary> /// <param name="codiceAmministrazione"></param> /// <returns></returns> public static Contatori GetByCodice(string codiceAmministrazione) { DocsPaVO.utente.Amministrazione[] amministrazioni = (DocsPaVO.utente.Amministrazione[]) BusinessLogic.Amministrazione.AmministraManager.GetAmministrazioni().ToArray(typeof(DocsPaVO.utente.Amministrazione)); if (amministrazioni.Count(e => e.codice.ToLowerInvariant() == codiceAmministrazione.ToLowerInvariant()) == 0) { throw new ApplicationException(string.Format("Amministrazione con codice '{0}' non trovata", codiceAmministrazione)); } else { Contatori retValue = new Contatori(); using (DocsPaDB.DBProvider dbProvider = new DocsPaDB.DBProvider()) { DocsPaUtils.Query queryDef = DocsPaUtils.InitQuery.getInstance().getQuery("S_GET_COUNT_ITEMS_CONSERVAZIONE"); queryDef.setParam("codiceAmministrazione", codiceAmministrazione); string commandText = queryDef.getSQL(); using (System.Data.IDataReader reader = dbProvider.ExecuteReader(commandText)) { while (reader.Read()) { string stato = reader.GetString(reader.GetOrdinal("STATO")); int totale = Convert.ToInt32(reader.GetValue(reader.GetOrdinal("TOTALE")).ToString()); if (stato == DocsPaConservazione.StatoIstanza.DA_INVIARE) { retValue.DaInviare = totale; } else if (stato == DocsPaConservazione.StatoIstanza.INVIATA) { retValue.Inviate = totale; } else if (stato == DocsPaConservazione.StatoIstanza.RIFIUTATA) { retValue.Rifiutate = totale; } else if (stato == DocsPaConservazione.StatoIstanza.IN_LAVORAZIONE) { retValue.InLavorazione = totale; } else if (stato == DocsPaConservazione.StatoIstanza.FIRMATA) { retValue.Firmate = totale; } else if (stato == DocsPaConservazione.StatoIstanza.CONSERVATA) { retValue.Conservate = totale; } else if (stato == DocsPaConservazione.StatoIstanza.CHIUSA) { retValue.Chiuse = totale; } } } } // Reperimento contantore delle notifiche //retValue.Notifiche = DocsPaConservazione.Notifiche.GetCountNotifiche(codiceAmministrazione); // MEV CS 1.5 // aggiungo le notifiche di leggibilità al contatore retValue.Notifiche = DocsPaConservazione.Notifiche.GetCountNotifiche(codiceAmministrazione) + DocsPaConservazione.Notifiche.GetCountNotificheLeggibilita(codiceAmministrazione); return(retValue); } }
public VtDocs.BusinessServices.Entities.Spotlight.SpotlightResponse SpotlightDocumenti(VtDocs.BusinessServices.Entities.Spotlight.SpotlightRequest request) { VtDocs.BusinessServices.Entities.Spotlight.SpotlightResponse response = new VtDocs.BusinessServices.Entities.Spotlight.SpotlightResponse(); try { using (DocsPaDB.DBProvider dbProvider = new DocsPaDB.DBProvider()) { string filterString = string.Format("{0}*", request.Filter).Replace("'", "''"); DocsPaUtils.Query queryDef = DocsPaUtils.InitQuery.getInstance().getQuery("SPOTLIGHT_GET_COUNT_DOCUMENTI"); queryDef.setParam("filter", filterString); queryDef.setParam("idPeople", request.InfoUtente.idPeople); queryDef.setParam("idGruppo", request.InfoUtente.idGruppo); string commandText = queryDef.getSQL(); string countField; if (dbProvider.ExecuteScalar(out countField, commandText)) { response.TotalRecordCount = Convert.ToInt32(countField); queryDef = DocsPaUtils.InitQuery.getInstance().getQuery("SPOTLIGHT_GET_DOCUMENTI"); queryDef.setParam("filter", filterString); queryDef.setParam("idPeople", request.InfoUtente.idPeople); queryDef.setParam("idGruppo", request.InfoUtente.idGruppo); queryDef.setParam("start", request.Start.ToString()); queryDef.setParam("limit", request.Limit.ToString()); commandText = queryDef.getSQL(); List <VtDocs.BusinessServices.Entities.Spotlight.SpotlightResultRecord> records = new List <VtDocs.BusinessServices.Entities.Spotlight.SpotlightResultRecord>(); using (System.Data.IDataReader reader = dbProvider.ExecuteReader(commandText)) { while (reader.Read()) { List <VtDocs.BusinessServices.Entities.Spotlight.SpotlightResultCell> cells = new List <VtDocs.BusinessServices.Entities.Spotlight.SpotlightResultCell>(); cells.Add ( new VtDocs.BusinessServices.Entities.Spotlight.SpotlightResultCell { FieldName = "IdProfile", FieldValue = reader.GetValue(reader.GetOrdinal("IdProfile")).ToString() } ); cells.Add ( new VtDocs.BusinessServices.Entities.Spotlight.SpotlightResultCell { FieldName = "Oggetto", FieldValue = reader.GetString(reader.GetOrdinal("Oggetto")) } ); cells.Add ( new VtDocs.BusinessServices.Entities.Spotlight.SpotlightResultCell { FieldName = "DocName", FieldValue = reader.GetString(reader.GetOrdinal("DocName")) } ); records.Add( new VtDocs.BusinessServices.Entities.Spotlight.SpotlightResultRecord { Cells = cells.ToArray() }); } } response.Records = records.ToArray(); response.Success = true; } else { throw new ApplicationException(dbProvider.LastExceptionMessage); } } } catch (Exception ex) { response.Success = false; if (request.TrowOnError) { throw DocsPaUtils.Exceptions.SoapExceptionFactory.Create(ex); } else { response.Exception = ex.Message; } } return(response); }
protected virtual bool OnMutated(IDataMutateContext context, TStatement statement, System.Data.IDataReader reader) { if (context is DataIncrementContextBase increment) { if (reader.Read()) { increment.Result = reader.IsDBNull(0) ? 0 : (long)Convert.ChangeType(reader.GetValue(0), TypeCode.Int64); } else { return(false); } } return(true); }
private static void LargeDataToXML( string table_schema , string table_name , System.Xml.XmlWriter writer , System.Data.IDataReader dr) { writer.WriteStartDocument(true); writer.WriteStartElement("table"); // writer.WriteStartElement(table_name); writer.WriteAttributeString(null, "table_schema", null, table_schema); writer.WriteAttributeString(null, "table_name", null, table_name); writer.WriteAttributeString("xmlns", "xsi", null, System.Xml.Schema.XmlSchema.InstanceNamespace); // writer.WriteAttributeString("xsi", "schemaLocation", null, System.Xml.Schema.XmlSchema.InstanceNamespace); int fc = dr.FieldCount; string[] columnNames = new string[fc]; System.Type[] columnTypes = new System.Type[fc]; for (int i = 0; i < dr.FieldCount; ++i) { columnNames[i] = dr.GetName(i); columnTypes[i] = dr.GetFieldType(i); } // Next i while (dr.Read()) { writer.WriteStartElement("row"); for (int i = 0; i < fc; ++i) { writer.WriteStartElement(columnNames[i]); object obj = dr.GetValue(i); if (obj != System.DBNull.Value) { if (object.ReferenceEquals(columnTypes[i], typeof(System.DateTime))) { System.DateTime dt = (System.DateTime)obj; writer.WriteValue(dt.ToString("yyyy-MM-dd'T'HH':'mm':'ss'.'fff", System.Globalization.CultureInfo.InvariantCulture)); } else { writer.WriteValue(System.Convert.ToString(obj, System.Globalization.CultureInfo.InvariantCulture)); } } else { writer.WriteAttributeString("xsi", "nil", System.Xml.Schema.XmlSchema.InstanceNamespace, "true"); } writer.WriteEndElement(); } // Next i writer.WriteEndElement(); } // Whend writer.WriteEndElement(); } // End Sub LargeDataToXML
} // WriteColumnDefinition private static async System.Threading.Tasks.Task WriteAsXmlAsync( string table_schema , string table_name , XmlRenderType_t format , System.Xml.XmlWriter writer , System.Data.IDataReader dr) { await writer.WriteStartDocumentAsync(true); bool dataAsAttributes = format.HasFlag(XmlRenderType_t.DataInAttributes); bool dataTableOnly = format.HasFlag(XmlRenderType_t.DataTable); if (!dataTableOnly) { await writer.WriteStartElementAsync(null, "dataset", null); } do { await writer.WriteStartElementAsync(null, "table", null); if (dataTableOnly) { if (table_schema != null) { await writer.WriteAttributeStringAsync(null, "table_schema", null, table_schema); } if (table_name != null) { await writer.WriteAttributeStringAsync(null, "table_name", null, table_name); } } if (!dataAsAttributes) { await writer.WriteAttributeStringAsync("xmlns", "xsi", null, System.Xml.Schema.XmlSchema.InstanceNamespace); } int fc = dr.FieldCount; if (format.HasFlag(XmlRenderType_t.WithColumnDefinition)) { await WriteColumnDefinition(writer, dr, format); } string[] columnNames = new string[fc]; System.Type[] columnTypes = new System.Type[fc]; for (int i = 0; i < dr.FieldCount; ++i) { columnNames[i] = dr.GetName(i); columnTypes[i] = dr.GetFieldType(i); } // Next i while (dr.Read()) { await writer.WriteStartElementAsync(null, "row", null); for (int i = 0; i < fc; ++i) { if (!dataAsAttributes) { await writer.WriteStartElementAsync(null, columnNames[i], null); } object obj = dr.GetValue(i); if (obj != System.DBNull.Value) { string value = null; if (object.ReferenceEquals(columnTypes[i], typeof(System.DateTime))) { System.DateTime dt = (System.DateTime)obj; value = dt.ToString("yyyy-MM-dd'T'HH':'mm':'ss'.'fff", System.Globalization.CultureInfo.InvariantCulture); } else { value = System.Convert.ToString(obj, System.Globalization.CultureInfo.InvariantCulture); } if (dataAsAttributes) { await writer.WriteAttributeStringAsync(null, columnNames[i], null, value); } else { writer.WriteValue(value); } } // End if (obj != System.DBNull.Value) else { if (!dataAsAttributes) { await writer.WriteAttributeStringAsync("xsi", "nil", System.Xml.Schema.XmlSchema.InstanceNamespace, "true"); } } if (!dataAsAttributes) { await writer.WriteEndElementAsync(); // column } } // Next i await writer.WriteEndElementAsync(); // row } // Whend await writer.WriteEndElementAsync(); // table await writer.FlushAsync(); if (dataTableOnly) { break; } } while (dr.NextResult()); if (!dataTableOnly) { await writer.WriteEndElementAsync(); // dataset await writer.FlushAsync(); } } // End Sub WriteAsXmlAsync