public static DataTable ConvertDataReaderToDataTable(DbDataReader reader) { try { DataTable table = new DataTable(); int fieldCount = reader.FieldCount; for (int fieldIndex = 0; fieldIndex < fieldCount; ++fieldIndex) { table.Columns.Add(reader.GetName(fieldIndex), reader.GetFieldType(fieldIndex)); } table.BeginLoadData(); object[] rowValues = new object[fieldCount]; while (reader.Read()) { reader.GetValues(rowValues); table.LoadDataRow(rowValues, true); } reader.Close(); table.EndLoadData(); return table; } catch (Exception ex) { throw new Exception("DataReader转换为DataTable时出错!", ex); } }
public void DisplayRows(DbDataReader nt) { this.listView1.Clear(); for (int i = 0; i < nt.FieldCount; i++) { listView1.Columns.Add(nt.GetName(i)); } int rowCount = 0; while (nt.NextResult()) { rowCount++; string contents = ""; if (nt[0] != null) { contents = nt[0].ToString(); } ListViewItem lvi = new ListViewItem(contents); for (int i = 1; i < nt.FieldCount; i++) { contents = ""; if (nt[i] != null) { contents = nt[i].ToString(); } lvi.SubItems.Add(contents); } this.listView1.Items.Add(lvi); } this.toolStripStatusLabel1.Text = rowCount.ToString() + " row(s) returned."; }
public static void Write(XmlDocument target, DbDataReader source ) { /* Style: <root> <raw><name>name1</name><index>name2</index></raw> <raw><name>name1</name><index>name2</index></raw> <raw><name>name1</name><index>name2</index></raw> </root> */ XmlNode head = target.CreateNode(XmlNodeType.Element, "head", ""); XmlNode body = target.CreateNode(XmlNodeType.Element, "body", ""); for (int i = 0; i < source.FieldCount; ++i) { string vl = source.GetName(i); string local = (string)HttpContext.GetGlobalResourceObject("local", vl); if (local != null) vl = local; Util.AddNodedText(head, "column", vl, false); } while (source.Read()) { XmlNode raw = target.CreateNode(XmlNodeType.Element, "raw", ""); for (int i = 0; i < source.FieldCount; ++i) Util.AddNodedText(raw, "value", Util.GetString( source, i ), false); body.AppendChild(raw); } target.FirstChild.AppendChild(head); target.FirstChild.AppendChild(body); }
public static void Print(string sql, DbDataReader reader) { log.Info("Results of [" + sql + "]"); string results = "\n"; if (reader.HasRows) { for (int j = 0; j < reader.FieldCount; j++) { results += reader.GetName(j); if (j < reader.FieldCount - 1) { results += "|"; } } results += "\n"; while (reader.Read()) { for (int i = 0; i < reader.FieldCount; i++) { results += reader.GetValue(i); if (i < reader.FieldCount - 1) { results += "|"; } } results += "\n"; } results = results.Substring(0, results.Length - 1); log.Info(results); } }
// criar instancia a partir dos dados obtidos do banco public FluxoCaixa(DbDataReader r) { for (int i = 0; i < r.FieldCount; i++) { switch (r.GetName(i)) { case "flc_id": id = Convert.ToInt16(r[i]); break; case "flc_data": data = Convert.ToDateTime(r[i]); break; case "flc_descricao": descricao = Convert.ToString(r[i]); break; case "flc_tipo": tipo = new TipoFluxo(r); break; case "flc_valor": valor = Convert.ToDouble(r[i]); break; } } }
public Table(DbDataReader reader) { Headers = Enumerable.Range(0, reader.FieldCount).Select(column => reader.GetName(column)).ToList(); Rows = new List<List<string>>(); while (reader.Read()) Rows.Add(Enumerable.Range(0, reader.FieldCount).Select(column => reader[column]).Select(value => GetDBValue(value)).ToList()); }
private static string[] GetColumnNames(DbDataReader dr, int numberOfColumns) { var colNames = new List<string>(); for (int i = 0; i < numberOfColumns; i++) { colNames.Add(dr.GetName(i)); } return colNames.ToArray(); }
// criar instancia a partir dos dados obtidos do banco public Contato(DbDataReader r) { for (int i=0; i < r.FieldCount; i++) { switch (r.GetName(i)) { case "nome": nome = Convert.ToString(r[i]); break; case "telefones": telefones = Convert.ToString(r[i]); break; case "endereco": endereco = Convert.ToString(r[i]); break; case "aniversario": DateTime niverDt = Convert.ToDateTime(r[i]); aniversario = String.Format("{0:dd/MM/yyyy}", niverDt); break; case "outros": outros = Convert.ToString(r[i]); break; } } }
public DynamicEntityValue(DbDataReader reader) { for(int i = 0; i < reader.FieldCount; i++) { this._nameValues.Add(reader.GetName(i), reader.GetValue(i)); } }
public static DataRow ImportDataReader(this DataTable table, DbDataReader dr) { var row = table.NewRow(); for (var i = 0; i < dr.FieldCount; i++) { var columnName = dr.GetName(i); row[columnName] = dr[i]; } table.Rows.Add(row); return row; }
/// <summary> /// Initializes a new instance of the EFCachingDataReaderCacheWriter class. /// </summary> /// <param name="wrappedReader">The wrapped reader.</param> /// <param name="maxRows">The maximum number of rows which can be cached.</param> /// <param name="addToCache">The delegate used to add the result to the cache when the reader finishes.</param> public EFCachingDataReaderCacheWriter(DbDataReader wrappedReader, int maxRows, Action<DbQueryResults> addToCache) { this.wrappedReader = wrappedReader; this.addToCache = addToCache; this.maxRows = maxRows; for (int i = 0; i < wrappedReader.FieldCount; ++i) { this.queryResults.ColumnNames.Add(wrappedReader.GetName(i)); } }
public BasicFieldNameLookup(DbDataReader reader) { int length = reader.FieldCount; string[] fieldNames = new string[length]; for (int i = 0; i < length; ++i) { fieldNames[i] = reader.GetName(i); } _fieldNames = fieldNames; }
public FieldNameLookup(DbDataReader reader, int defaultLocaleID) { int length = reader.FieldCount; string[] fieldNames = new string[length]; for (int i = 0; i < length; ++i) { fieldNames[i] = reader.GetName(i); } _fieldNames = fieldNames; _defaultLocaleID = defaultLocaleID; }
public static DataTable CreateTable(DbDataReader reader) { DataTable table = new DataTable(); for (int i = 0; i < reader.FieldCount; i++) { DataColumn column = new DataColumn(reader.GetName(i), reader.GetFieldType(i)); table.Columns.Add(column); } table.AcceptChanges(); return table; }
public static void ExportExcel(DbDataReader dataReader, string strTitle, string fileFullPath, bool isAutoFit) { Workbook workbook = new Workbook(); workbook.Worksheets.Add(); object[] header = new object[dataReader.FieldCount]; bool first = true; int rowIndex = 0; Style style = new Style(); while (dataReader.Read()) { for (int i = 0; i < dataReader.FieldCount; i++) { if (first) { style.Custom = "@"; style.Font.IsBold = true; workbook.Worksheets[0].Cells[rowIndex, i].SetStyle(style); workbook.Worksheets[0].Cells[rowIndex, i].Value = dataReader.GetName(i); } style.Font.IsBold = false; if (dataReader[i] is string) { style.Custom = "@"; } else if (dataReader[i] is DateTime) { style.Custom = "yyyy-MM-dd"; } else if (dataReader[i] is decimal) { style.Custom = "####.#####"; style.ShrinkToFit = true; } workbook.Worksheets[0].Cells[rowIndex + 1, i].SetStyle(style); workbook.Worksheets[0].Cells[rowIndex + 1, i].Value = dataReader[i]; } first = false; rowIndex++; } if (isAutoFit) { workbook.Worksheets[0].AutoFitColumns(); workbook.Worksheets[0].AutoFitRows(true); } if (fileFullPath.ToLower().EndsWith(".xlsx")) workbook.Save(fileFullPath, FileFormatType.Excel2007Xlsx); else workbook.Save(fileFullPath, FileFormatType.Default); }
public virtual IRelationalValueBufferFactory CreateValueBufferFactory( IRelationalValueBufferFactoryFactory relationalValueBufferFactoryFactory, DbDataReader dataReader) { Check.NotNull(relationalValueBufferFactoryFactory, nameof(relationalValueBufferFactoryFactory)); Check.NotNull(dataReader, nameof(dataReader)); var readerColumns = Enumerable .Range(0, dataReader.FieldCount) .Select(i => new { Name = dataReader.GetName(i), Ordinal = i }) .ToList(); var types = new Type[_selectExpression.Projection.Count]; var indexMap = new int[_selectExpression.Projection.Count]; for (var i = 0; i < _selectExpression.Projection.Count; i++) { var aliasExpression = _selectExpression.Projection[i] as AliasExpression; if (aliasExpression != null) { var columnName = aliasExpression.Alias ?? aliasExpression.TryGetColumnExpression()?.Name; if (columnName != null) { var readerColumn = readerColumns.SingleOrDefault(c => string.Equals(columnName, c.Name, StringComparison.OrdinalIgnoreCase)); if (readerColumn == null) { throw new InvalidOperationException(Strings.FromSqlMissingColumn(columnName)); } types[i] = _selectExpression.Projection[i].Type; indexMap[i] = readerColumn.Ordinal; } } } return relationalValueBufferFactoryFactory.Create(types, indexMap); }
static int GetName(IntPtr L) { try { ToLua.CheckArgsCount(L, 2); System.Data.Common.DbDataReader obj = (System.Data.Common.DbDataReader)ToLua.CheckObject(L, 1, typeof(System.Data.Common.DbDataReader)); int arg0 = (int)LuaDLL.luaL_checknumber(L, 2); string o = obj.GetName(arg0); LuaDLL.lua_pushstring(L, o); return(1); } catch (Exception e) { return(LuaDLL.toluaL_exception(L, e)); } }
public static string ConvertRowToString(DbDataReader row, StringBuilder headers) { var output = new List<string>(); for (var i = 0; i < row.FieldCount; i++) { output.Add("" + row.GetValue(i)); } if (headers.Length == 0) { for (var i = 0; i < row.FieldCount; i++) { headers.Append(row.GetName(i) + ", "); } } return String.Join(", ", output.ToArray()); }
public Lembrete(DbDataReader r) { for (int i=0; i < r.FieldCount; i++) { switch (r.GetName(i)) { case "id": id = Convert.ToInt16(r[i]); break; case "descricao": descricao = Convert.ToString(r[i]); break; } } }
// criar instancia a partir dos dados obtidos do banco public PaginaDiario(DbDataReader r) { for (int i = 0; i < r.FieldCount; i++) { switch (r.GetName(i)) { case "data": data = Convert.ToDateTime(r[i]); break; case "texto": texto = Convert.ToString(r[i]); break; } } }
public void WriteColumns2File(DbDataReader reader, string file) { if (reader == null) return; using (System.IO.StreamWriter writer = new System.IO.StreamWriter(file)) { for (int i = 0; i < reader.VisibleFieldCount; i++) { string index = (i + 1).ToString("D2"); string columnName = reader.GetName(i).ToUpper(); string type = reader.GetFieldType(i).Name; string comment = "说明"; writer.WriteLine(string.Format("{0}\t|\t{1}\t|\t{2}\t|\t{3}", index, columnName, type, comment)); } } }
protected PosName[] GetColumnNames(DbDataReader dataReader, PocoData pocoData) { if (_columnNames != null) return _columnNames; var cols = Enumerable.Range(0, dataReader.FieldCount) .Select(x => new PosName { Pos = x, Name = dataReader.GetName(x) }) .Where(x => !string.Equals("poco_rn", x.Name)) .ToList(); if (cols.Any(x => x.Name.StartsWith(PropertyMapperNameConvention.SplitPrefix, StringComparison.OrdinalIgnoreCase))) { return (_columnNames = cols.ConvertFromNewConvention(pocoData).ToArray()); } return (_columnNames = cols.ConvertFromOldConvention(pocoData.Members).ToArray()); }
public DataTable(DbDataReader reader) { this.fields = new List<string>(); this.data = new List<object[]>(); for (int i = 0; i < reader.FieldCount; i++) { fields.Add(reader.GetName(i)); } while (reader.Read()) { object[] values = new object[reader.FieldCount]; for (int i = 0; i < fields.Count; i++) { values[i] = reader.GetValue(reader.GetOrdinal(fields[i])); } data.Add(values); } }
public DataResult(DbDataReader reader) { ColumnNames = Enumerable.Range(0, reader.FieldCount) .Select(x => reader.GetName(x)) .ToList(); Rows = new List<RowData>(); while (reader.Read()) { Dictionary<string, string> rowData = new Dictionary<string, string>(); for (var i = 0; i < reader.FieldCount; i++) { if (reader[i].GetType() == typeof(DateTime)) { // Use ISO time rowData[ColumnNames[i]] = ((DateTime)reader[i]).ToString("s"); } else { rowData[ColumnNames[i]] = reader[i].ToString(); } } Rows.Add(rowData); } }
// criar instancia a partir dos dados obtidos do banco public TipoFluxo(DbDataReader r) { for (int i = 0; i < r.FieldCount; i++) { switch (r.GetName(i)) { case "tfc_id": id = Convert.ToInt16(r[i]); break; case "tfc_descricao": descricao = Convert.ToString(r[i]); break; case "tfc_fluxo": fluxo = (TipoFluxo.Fluxos)Convert.ToInt16(r[i]); break; } } }
/// <summary> /// This may be useful if you want to serialize a full table as json. Usually, a IDictionary object is serialized as JSON object (eg Newtonsoft.Json) /// It can be used with dynmaic as well (http://stackoverflow.com/questions/18290852/generate-dynamic-object-from-dictionary-with-c-reflection) /// </summary> /// <typeparam name="T">The type of the IDictionary, usually Dictionary<string,object></typeparam> /// <param name="table">The DbDataReader to convert to a Dictionary List</param> /// <param name="inputList">An existing list to append the items. Can be null to create a new list</param> /// <returns>A List containing the Objects</returns> public static List <T> AsDictionaryList <T>(this System.Data.Common.DbDataReader table, List <T>?inputList = null) where T : IDictionary <string, object?>, new() { if (inputList == null) { inputList = new List <T>(); } int columnCount = table.FieldCount; while (table.Read()) { T item = new T(); foreach (var col in Enumerable.Range(0, columnCount)) { item.Add(table.GetName(col), table.IsDBNull(col) ? null : table[col]); } inputList.Add(item); } return(inputList); }
// criar instancia a partir dos dados obtidos do banco public Compromisso(DbDataReader r) { for (int i=0; i < r.FieldCount; i++) { switch (r.GetName(i)) { case "cpm_id": id = Convert.ToInt32(r[i]); break; case "cpm_inicio": inicio = Convert.ToDateTime(r[i]); break; case "cpm_fim": fim = Convert.ToDateTime(r[i]); break; case "cpm_descricao": descricao = Convert.ToString(r[i]); break; } } }
public String ViewTableTxt(String TableName) { ESData.GetInst.Open_Conn(); String txt = "TabeName:" + TableName + "\n"; System.Data.Common.DbDataReader dr = ESData.GetInst.Reader(String.Format("select * from {0};", TableName)); for (int i = 0; i < dr.FieldCount; i++) { txt += dr.GetName(i) + "\t "; } txt += "\n"; //if (dr.HasRows) while (dr.Read()) { for (int i = 0; i < dr.FieldCount; i++) { try{ txt += dr[i].ToString() + "\t "; } catch { MessageBox.Show(dr[0].ToString()); } } txt += "\n"; } dr.Close(); dr.Dispose(); return(txt); }
private FeatureDataTable CreateTableFromReader(DbDataReader reader, int geomIndex) { var res = new FeatureDataTable {TableName = Table}; for (var c = 0; c < geomIndex; c++) { var fieldType = reader.GetFieldType(c); if (fieldType == null) throw new Exception("Unable to retrieve field type for column " + c); res.Columns.Add(DequoteIdentifier(reader.GetName(c)), fieldType); } return res; }
protected void Page_Load(object sender, EventArgs e) { if (Request.QueryString["excel"] != null) { ExportExcel(); return; } string url = Request.QueryString["url"]; //Response.Write(url); string html = System.IO.File.ReadAllText(Server.MapPath("~" + url)); string recordid = Request.QueryString["recordid"]; string path = Request.QueryString["altPath"] ?? Request.QueryString["path"]; // <conn>.<schema>.<object> string type = Request.QueryString["type"] ?? "application"; string[] pathArray = path.Contains('_') ? path.Split('_') : path.Split('.'); System.Data.Common.DbDataReader reader = null; String TypeString = Request.QueryString["Type"]; if (TypeString.Equals("timesheet")) { System.Collections.IDictionary param = new Dictionary <string, object>(); var UserID = Request.QueryString["userID"]; var OrgUnit = Request.QueryString["orgID"]; var start = Request.QueryString["start"]; var end = Request.QueryString["end"]; var rank = Request.QueryString["rank"]; param.Add("ID", null); param.Add("OrganizationUnitID", null); param.Add("StartDate", null); param.Add("EndDate", null); param.Add("Rank", null); if (UserID.Length != 0) { param["ID"] = UserID; } if (OrgUnit.Length != 0) { param["OrganizationUnitID"] = OrgUnit; } if (start.Length != 0) { param["StartDate"] = start; } if (end.Length != 0) { param["EndDate"] = end; } if (rank.Length != 0) { param["Rank"] = rank; } param.Add("CreatedBy", 1); param.Add("Offset", 0); param.Add("Limit", 0); int typeID = 0; string p = Request.QueryString["Path"].Split('.')[0]; try { typeID = Convert.ToInt32(Request.QueryString["typeID"]); } catch (Exception) { } SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[p].ToString()); conn.Open(); using (SqlCommand command = new SqlCommand()) { command.Connection = conn; command.CommandType = CommandType.StoredProcedure; command.CommandText = "att.spGetMonthlyTimeSheet"; foreach (var ent in param.Keys) { command.Parameters.Add(new SqlParameter(ent.ToString(), param[ent])); } command.CommandTimeout = 200; reader = command.ExecuteReader(); }; //reader = plus.Data.DAL.Read("AMS", "att.spGetMonthlyTimeSheet",param); Dictionary <int, String> maps = new Dictionary <int, string>(); maps.Add(2, "PR"); maps.Add(3, "AB"); maps.Add(4, "HD"); maps.Add(5, "ALH"); maps.Add(6, "AL"); StringBuilder sb = new StringBuilder(); sb.Append("<thead>"); sb.Append("<tr>"); sb.Append("<th>کود</th><th>نام مکمل</th><th>نام پدر</th><th>وظیفه</th><th>بست</th><th>اداره</th>"); for (int i = 0; i < reader.FieldCount; i++) { if (i > 6) { sb.Append("<th>" + reader.GetName(i).Split('_')[2] + "</th>"); } } sb.Append("</tr></thead>"); sb.Append("<tbody>"); while (reader.Read()) { sb.Append("<tr>"); for (int i = 1; i < reader.FieldCount; i++) { if (i < 7 || (typeID == 0 || typeID == 1)) { sb.Append("<td>" + reader[i].ToString() + "</td>"); } else { string s = reader[i].ToString(); if (maps[typeID].Equals(s)) { sb.Append("<td>" + reader[i].ToString() + "</td>"); } else { sb.Append("<td> </td>"); } } } sb.Append("</tr>"); } sb.Append("<tbody>"); html = html.Replace("$body", sb.ToString()); Response.Write(html); conn.Close(); return; } else if (TypeString.Equals("attreport")) { int typeID = 0; try { typeID = Convert.ToInt32(Request.QueryString["typeID"]); } catch (Exception) { } System.Collections.IDictionary param = new Dictionary <string, object>(); var UserID = Request.QueryString["userID"]; var OrgUnit = Request.QueryString["orgID"]; var start = Request.QueryString["start"]; var end = Request.QueryString["end"]; var rank = Request.QueryString["rank"]; param.Add("UserID", null); param.Add("OrganizationUnit", null); param.Add("StartDate", null); param.Add("EndDate", null); param.Add("RankID", null); param.Add("AttID", null); param.Add("HRCode", null); param.Add("CreatedBy", null); param.Add("Type", null); if (UserID.Length != 0) { param["UserID"] = UserID; } if (OrgUnit.Length != 0) { param["OrganizationUnit"] = OrgUnit; } if (start.Length != 0) { param["StartDate"] = start; } if (end.Length != 0) { param["EndDate"] = end; } if (rank.Length != 0) { param["RankID"] = rank; } if (typeID != 0) { param["Type"] = typeID; } reader = plus.Data.DAL.Read("AMS", "att.spGenerateTimeSheet", param); StringBuilder sb = new StringBuilder(); sb.Append("<thead>"); sb.Append("<tr>"); sb.Append("<th> کود کادری </th><th> نام مکمل </th><th> نام پدر </th><th> وظیفه </th><th> بست </th><th> ریاست </th><th> شیفت </th><th> تاریخ </th><th> وقت دخول </th><th> وقت خروج </th><th> حاضری </th>"); sb.Append("</tr>"); sb.Append("</thead>"); sb.Append("<tbody>"); while (reader.Read()) { sb.Append("<tr>"); for (int i = 1; i < reader.FieldCount; i++) { if (!reader.GetName(i).Equals("AttType")) { sb.Append("<td>" + reader[i].ToString() + "</td>"); } } sb.Append("</tr>"); } sb.Append("</tbody>"); html = html.Replace("$body", sb.ToString()); Response.Write(html); return; } if (type.Equals("idcard")) { //string keyCol = Request.QueryString["PID"] ?? "ID"; System.Data.DataSet ds = plus.Data.DAL.GetDataSet("hcm", "select FullName, FatherName, HRCode, Position, IssueDate, PhotoPath from rec.vCardPrint where ID=" + recordid); string imgPath = ds.Tables[0].Rows[0]["PhotoPath"].ToString(); string xName = ds.Tables[0].Rows[0]["FullName"].ToString(); string xNameEng = (ds.Tables[0].Rows[0]["FullName"].ToString()) + "s"; string xSO = ds.Tables[0].Rows[0]["Fathername"].ToString(); string xDuty = ds.Tables[0].Rows[0]["Position"].ToString(); string xCode = ds.Tables[0].Rows[0]["HRCode"].ToString(); string xIssueDate = ds.Tables[0].Rows[0]["IssueDate"].ToString(); //Labels string lblName = "اسم:"; string lblSO = "ولد/بنت:"; string lblDuty = "وظیفه:"; string lblID = "شماره شناخت:"; string lblIssueDate = "تاریخ صدور:"; string lblNameEng = "Name:"; string lblSOEng = "S/O:"; string lblDutyEng = "Duty:"; string lblIDEng = "ID Number:"; string lblIssueDateEng = "IssueDate:"; //Get the Data string Name = xName; //"نادر"; string SO = xSO; //"روشن"; string Duty = xDuty; //"انجنیر نرم افزار"; string ID = xCode; //"SC000122"; string IssueDate = xIssueDate; //"1396-12-2"; string NameEng = xNameEng; //"Nadir"; string SOEng = xSO; //"Roshan"; string DutyEng = xDuty; //"Software Engineer"; string IDEng = xCode; //"SC000122"; string IssueDateEng = xIssueDate; //"2017-12-2"; //imgPath = imgPath.Contains('\\') ? imgPath.Replace('\\', '/') : imgPath.Replace('\\','/'); string photo = Server.MapPath("~/hcm/userPhoto" + imgPath); //Set X and Y axis for Labels and Data PointF lblNameLoc = new PointF(535f, 130f); PointF lblSOLoc = new PointF(535f, 190f); PointF lblDutyLoc = new PointF(535f, 250f); PointF lblIDLoc = new PointF(535f, 310f); PointF lblIssueDateLoc = new PointF(535f, 365f); PointF lblNameEngLoc = new PointF(265f, 130f); PointF lblSOEngLoc = new PointF(265f, 190f); PointF lblDutyEngLoc = new PointF(265f, 250f); PointF lblIDEngLoc = new PointF(265f, 310f); PointF lblIssueDateEngLoc = new PointF(265f, 365f); PointF nameLoc = new PointF(360f, 130f); PointF SOLoc = new PointF(360f, 190f); PointF DutyLoc = new PointF(360f, 250f); PointF IDLoc = new PointF(360f, 310f); PointF IssueDateLoc = new PointF(360f, 365f); PointF photoLoc = new PointF(600f, 140f); PointF nameEngLoc = new PointF(450f, 130f); PointF SOEngLoc = new PointF(450f, 190f); PointF DutyEngLoc = new PointF(450f, 250f); PointF IDEngLoc = new PointF(450f, 310f); PointF IssueDateEngLoc = new PointF(450f, 365f); PointF photoEngLoc = new PointF(20f, 140f); //Dari Text Direction StringFormat rtl = new StringFormat(StringFormatFlags.DirectionRightToLeft); //Cards path string imageDariFilePath = Server.MapPath("~/page/templ/idcard/dari/Administration.jpg"); //@"~/hcm/page/idcard/dari/Administration.jpg"; //template path dari string imageEngFilePath = Server.MapPath("~/page/templ/idcard/dari/Administration.jpg"); //@"~/hcm/page/idcard/eng/Administration.jpg"; //template path eng string imageDariOutput = Server.MapPath("~/page/templ/idcard/Generated/" + Name + ".jpg"); //@"~/hcm/page/idcard/Generated/" + Name + ".jpg"; //Path after generated dari string imageEngOutput = Server.MapPath("~/page/templ/idcard/Generated/" + NameEng + ".jpg"); //@"~/hcm/page/idcard/Generated/" + NameEng + ".jpg"; //Path after generated eng //Resize Image System.Drawing.Image imgResized = ScaleImage(System.Drawing.Image.FromFile(photo), 70, 80); //Convert Image to bitmap Bitmap daribitmap = (Bitmap)System.Drawing.Image.FromFile(imageDariFilePath); Bitmap engbitmap = (Bitmap)System.Drawing.Image.FromFile(imageEngFilePath); Bitmap bitmapPhoto = (Bitmap)imgResized; //Write the labels and data on image using graphics class using (Graphics gdari = Graphics.FromImage(daribitmap)) using (Graphics geng = Graphics.FromImage(engbitmap)) { using (Font arialFont = new Font("Times New Roman", 8)) { //Dari Card----------------------------------------------------------------- //Draw labels gdari.DrawString(lblName, arialFont, Brushes.Black, lblNameLoc, rtl); gdari.DrawString(lblSO, arialFont, Brushes.Black, lblSOLoc, rtl); gdari.DrawString(lblDuty, arialFont, Brushes.Black, lblDutyLoc, rtl); gdari.DrawString(lblID, arialFont, Brushes.Black, lblIDLoc, rtl); gdari.DrawString(lblIssueDate, arialFont, Brushes.Black, lblIssueDateLoc, rtl); //Draw data //gdari.CompositingMode = CompositingMode.SourceOver; //bitmapPhoto.MakeTransparent(); gdari.DrawImage(bitmapPhoto, photoLoc); gdari.DrawString(Name, arialFont, Brushes.Black, nameLoc, rtl); gdari.DrawString(SO, arialFont, Brushes.Black, SOLoc, rtl); gdari.DrawString(Duty, arialFont, Brushes.Black, DutyLoc, rtl); gdari.DrawString(ID, arialFont, Brushes.Black, IDLoc, rtl); gdari.DrawString(IssueDate, arialFont, Brushes.Black, IssueDateLoc, rtl); // English Card-------------------------------------------------------------- //Draw labels geng.DrawString(lblNameEng, arialFont, Brushes.Black, lblNameEngLoc); geng.DrawString(lblSOEng, arialFont, Brushes.Black, lblSOEngLoc); geng.DrawString(lblDutyEng, arialFont, Brushes.Black, lblDutyEngLoc); geng.DrawString(lblIDEng, arialFont, Brushes.Black, lblIDEngLoc); geng.DrawString(lblIssueDateEng, arialFont, Brushes.Black, lblIssueDateEngLoc); //Draw data //geng.CompositingMode = CompositingMode.SourceOver; //bitmapPhoto.MakeTransparent(); geng.DrawImage(bitmapPhoto, photoEngLoc); geng.DrawString(NameEng, arialFont, Brushes.Black, nameEngLoc); geng.DrawString(SOEng, arialFont, Brushes.Black, SOEngLoc); geng.DrawString(DutyEng, arialFont, Brushes.Black, DutyEngLoc); geng.DrawString(IDEng, arialFont, Brushes.Black, IDEngLoc); geng.DrawString(IssueDateEng, arialFont, Brushes.Black, IssueDateEngLoc); } } // Save the generated card using (MemoryStream memorydari = new MemoryStream()) using (MemoryStream memoryeng = new MemoryStream()) { using (FileStream fsdari = new FileStream(imageDariOutput, FileMode.Create, FileAccess.ReadWrite)) using (FileStream fseng = new FileStream(imageEngOutput, FileMode.Create, FileAccess.ReadWrite)) { daribitmap.Save(memorydari, ImageFormat.Jpeg); engbitmap.Save(memoryeng, ImageFormat.Jpeg); byte[] bytesdari = memorydari.ToArray(); byte[] byteseng = memoryeng.ToArray(); fsdari.Write(bytesdari, 0, bytesdari.Length); fseng.Write(byteseng, 0, byteseng.Length); } } //html = html.Replace("{imgPath}", Convert.ToString(imageDariOutput)); } else { if (type.Equals("DailyUserReport")) { string reportdate = Request.QueryString["reportdate"]; string reporttype = Request.QueryString["reporttype"]; string shamsidate = Request.QueryString["shamsidate"]; string issent = Request.QueryString["issent"]; string rolename = Request.QueryString["rolename"]; reader = plus.Data.DAL.Read(pathArray[0], string.Format("SELECT * FROM {0}.{1} WHERE RoleID=@RoleID AND ReportDate = @ReportDate and ReportType=@ReportType and Issent=@Issent order by Total DESC", pathArray[1], pathArray[2]) , "RoleID", recordid, "ReportDate", reportdate, "ReportType", reporttype, "Issent", issent); String tbody = " <page size='A4' class='Print'>"; tbody += "<table class='table' style='width:90%;'>"; String thead = ""; thead += "<thead>"; thead += "<tr>"; thead += "<th colspan='10' style='border:none; font-size:16px;'>گزارش روزانه کارمندان بخش $RoleName بابت تاریخ $rptdate </th>"; thead += "</tr>"; thead += "<tr>"; thead += "<th> شماره</th>"; thead += "<th> اسم کارمند</th>"; thead += "<th> دیپارتمنت</th>"; thead += "<th> تاریخ</th>"; thead += "<th> نوع گزارش</th>"; thead += "<th>حالت اسناد</th>"; thead += "<th>تعداد</th>"; thead += "</tr>"; thead += "</thead>"; thead = thead.Replace("$RoleName", rolename); thead = thead.Replace("$rptdate", shamsidate); tbody += thead; tbody += "<tbody>"; int counter = 1; while (reader.Read()) { string trow = "<tr>"; trow += "<td>$NO</td>"; trow += "<td>$FullName</td>"; trow += "<td>$RoleName</td>"; trow += "<td>$ReportDate_Shamsi</td>"; trow += "<td>$RptTypeName</td>"; trow += "<td>$SENTName</td>"; trow += "<td>$Total</td>"; trow += "</tr>"; trow = trow.Replace("$NO", counter.ToString()); for (int i = 0; i < reader.FieldCount; i++) { string colName = reader.GetName(i); trow = trow.Replace("$" + colName, Convert.ToString(reader[colName])); } tbody += trow; counter++; } tbody += "</tbody></table> <br/>"; tbody += "<div style='margin-right:20px; margin-left:20px;'>"; System.Data.Common.DbDataReader summaryReader = plus.Data.DAL.Read(pathArray[0], string.Format("exec [rpt].[spGetGrandTotalDailyUsersReport] {0}, '{1}', {2}, {3}", recordid, reportdate, reporttype, issent)); String sthead = ""; String summary = "<table class='table' style='width:90%;'>"; String stbody = "<tbody>"; sthead += "<thead>"; sthead += "<tr>"; sthead += "<th colspan='10' style='border:none; font-size:16px;'>مجموع عمومی:</th>"; sthead += "</tr>"; sthead += "</thead>"; summary += sthead; while (summaryReader.Read()) { stbody += "<tr>"; stbody += "<th>@GrandTotal</th>"; stbody += "</tr>"; for (int i = 0; i < summaryReader.FieldCount; i++) { string colName = summaryReader.GetName(i); stbody = stbody.Replace("@" + colName, Convert.ToString(summaryReader[colName])); } } summary += stbody; summary += "</table>"; tbody += summary; tbody += "</div>"; tbody += "</page>"; html = html.Replace("$body", tbody); } else if (!type.Equals("closedlist")) { string keyCol = Request.QueryString["keyCol"] ?? "ID"; reader = plus.Data.DAL.Read(pathArray[0], string.Format("SELECT * FROM {0}.{1} WHERE {2} = @RecordID", pathArray[1], pathArray[2], keyCol) , "RecordID", recordid); if (type.Equals("application")) { while (reader.Read()) { // loop through columns for (int i = 0; i < reader.FieldCount; i++) { string colName = reader.GetName(i); // firstName if (!colName.Equals("BirthDate")) { html = html.Replace("{" + colName + "}", Convert.ToString(reader[colName])); } else { html = html.Replace("{" + colName + "}", Convert.ToDateTime(reader[colName]).ToString("dd MMM yyyy")); } } } } else if (type.Equals("progresslist")) { string subpath = "preport_rpt_vUserReport"; string[] subPathArray = subpath.Split('_'); string subKeyCol = "ID"; System.Data.Common.DbDataReader subReader = plus.Data.DAL.Read(subPathArray[0], string.Format("SELECT * FROM {0}.{1} WHERE {2} = @RecordID", subPathArray[1], subPathArray[2], subKeyCol) , "RecordID", recordid); String tbody = " <page size='A4' class='Print'>"; tbody += "<table class='table' style='font-size:10px !important;'>"; String thead = ""; thead += "<thead>"; thead += "<tr>"; thead += "<th colspan='10' style='border:none; font-size:14px;'>گزارش نمبر $CODE از فعالیت و کارکرد مورخ $SHAMSI محترم $Employee کارمند بخش $RoleName</th>"; thead += "</tr>"; thead += "<tr>"; thead += "<th> شماره</th>"; thead += "<th> کود متقاضی</th>"; thead += "<th> نام متقاضی</th>"; thead += "<th>نام پدر</th>"; thead += "<th>نام پدرکلان</th>"; thead += "<th>محل تولد</th>"; thead += "<th> تاریخ تولد</th>"; thead += "<th> تاریخ اجرأت کارمند</th>"; thead += "<th> زمان اجرأت کارمند</th>"; thead += "<th> مرحله ارسالی</th>"; thead += "</tr>"; thead += "</thead>"; while (subReader.Read()) { for (int i = 0; i < subReader.FieldCount; i++) { string subColName = subReader.GetName(i); thead = thead.Replace("$" + subColName, Convert.ToString(subReader[subColName])); } } tbody += thead; tbody += "<tbody>"; int counter = 1; while (reader.Read()) { string trow = "<tr>"; trow += "<td>$NO</td>"; trow += "<td>$Code</td>"; trow += "<td>$FullName</td>"; trow += "<td>$FatherNameLocal</td>"; trow += "<td>$GrandFatherNameLocal</td>"; trow += "<td>$BirthLocation</td>"; trow += "<td>$BirthDate</td>"; trow += "<td>$ProcessedDate</td>"; trow += "<td>$ProcessedTime</td>"; trow += "<td>$ProcessName</td>"; trow += "</tr>"; trow = trow.Replace("$NO", counter.ToString()); for (int i = 0; i < reader.FieldCount; i++) { string colName = reader.GetName(i); trow = trow.Replace("$" + colName, Convert.ToString(reader[colName])); } tbody += trow; counter++; } tbody += "</tbody></table> <br/>"; tbody += "<div style='margin-right:10px; margin-left:10px;'>"; String sthead = ""; String summary = "<table class='table' style='font-size:16px; width:90%;'>"; String stbody = "<tbody>"; sthead += "<tr>"; sthead += "<th>امضأ و نام مکمل کارمند</th>"; sthead += "<th>امضأ و نام مکمل آمر بخش</th>"; sthead += "<th>امضأ و نام مکمل تسلیم شونده</th>"; sthead += "</tr>"; sthead += "</thead>"; summary += sthead; stbody += "<tr style='height:100px;'>"; stbody += "<th></th>"; stbody += "<th></th>"; stbody += "<th></th>"; stbody += "</tr>"; summary += stbody; summary += "</table>"; tbody += summary; tbody += "</div>"; tbody += "</page>"; html = html.Replace("$body", tbody); } else if (type.Equals("postprogresslist")) { string subpath = "preport_rpt_vUserReport"; string[] subPathArray = subpath.Split('_'); string subKeyCol = "ID"; System.Data.Common.DbDataReader subReader = plus.Data.DAL.Read(subPathArray[0], string.Format("SELECT * FROM {0}.{1} WHERE {2} = @RecordID", subPathArray[1], subPathArray[2], subKeyCol) , "RecordID", recordid); String tbody = " <page size='A4' class='Print'>"; tbody += "<table class='table' style='font-size:10px !important;'>"; String thead = ""; thead += "<thead>"; thead += "<tr>"; thead += "<th colspan='10' style='border:none; font-size:14px;'>گزارش نمبر $CODE از فعالیت و کارکرد مورخ $SHAMSI محترم $Employee کارمند بخش $RoleName</th>"; thead += "</tr>"; thead += "<tr>"; thead += "<th> شماره</th>"; thead += "<th> نمبر پاسپورت</th>"; thead += "<th> نام متقاضی</th>"; thead += "<th>نام پدر</th>"; thead += "<th>نام پدرکلان</th>"; thead += "<th>محل تولد</th>"; thead += "</tr>"; thead += "</thead>"; while (subReader.Read()) { for (int i = 0; i < subReader.FieldCount; i++) { string subColName = subReader.GetName(i); thead = thead.Replace("$" + subColName, Convert.ToString(subReader[subColName])); } } tbody += thead; tbody += "<tbody>"; int counter = 1; while (reader.Read()) { string trow = "<tr>"; trow += "<td>$NO</td>"; trow += "<td>$PassportNumber</td>"; trow += "<td>$FullName</td>"; trow += "<td>$FatherNameLocal</td>"; trow += "<td>$GrandFatherNameLocal</td>"; trow += "<td>$BirthLocation</td>"; trow += "</tr>"; trow = trow.Replace("$NO", counter.ToString()); for (int i = 0; i < reader.FieldCount; i++) { string colName = reader.GetName(i); trow = trow.Replace("$" + colName, Convert.ToString(reader[colName])); } tbody += trow; counter++; } tbody += "</tbody></table> <br/>"; tbody += "<div style='margin-right:10px; margin-left:10px;'>"; String sthead = ""; String summary = "<table class='table' style='font-size:16px; width:90%;'>"; String stbody = "<tbody>"; sthead += "<tr>"; sthead += "<th>امضأ و نام مکمل کارمند</th>"; sthead += "<th>امضأ و نام مکمل آمر بخش</th>"; sthead += "<th>امضأ و نام مکمل تسلیم شونده</th>"; sthead += "</tr>"; sthead += "</thead>"; summary += sthead; stbody += "<tr style='height:100px;'>"; stbody += "<th></th>"; stbody += "<th></th>"; stbody += "<th></th>"; stbody += "</tr>"; summary += stbody; summary += "</table>"; tbody += summary; tbody += "</div>"; tbody += "</page>"; html = html.Replace("$body", tbody); } else if (type.Equals("printedlist")) { string subpath = "RegistryServices_stc_vStockOut"; string[] subPathArray = subpath.Split('_'); string subKeyCol = "ID"; System.Data.Common.DbDataReader subReader = plus.Data.DAL.Read(subPathArray[0], string.Format("SELECT * FROM {0}.{1} WHERE {2} = @RecordID", subPathArray[1], subPathArray[2], subKeyCol) , "RecordID", recordid); String tbody = " <page size='A4' class='Print'>"; tbody += "<table class='table'>"; String thead = ""; thead += "<thead>"; thead += "<tr>"; thead += "<th colspan='10' style='border:none; font-size:16px;'> گزارش پاسپورت های چاپ شده از نمبر ($StartSerial) الی نمبر ($EndSerial) <br/> که به تاریخ $Date در جمع $FullName ثبت گردیده</th>"; thead += "</tr>"; thead += "<tr>"; thead += "<th> شماره</th>"; thead += "<th> نمبر پاسپورت</th>"; thead += "<th> نام متقاضی</th>"; thead += "<th> پاسپورت</th>"; thead += "<th>درخواست</th>"; thead += "<th> جریمه</th>"; thead += "<th> مدت</th>"; thead += "<th> پرداخت</th>"; thead += "<th>قیمت</th>"; thead += "<th> تاریخ صدور</th>"; thead += "<th> حالت</th>"; thead += "</tr>"; thead += "</thead>"; while (subReader.Read()) { for (int i = 0; i < subReader.FieldCount; i++) { string subColName = subReader.GetName(i); thead = thead.Replace("$" + subColName, Convert.ToString(subReader[subColName])); } } tbody += thead; tbody += "<tbody>"; int counter = 1; while (reader.Read()) { string trow = "<tr>"; trow += "<td>$NO</td>"; trow += "<td>$PassportNumber</td>"; trow += "<td>$FullName</td>"; trow += "<td>$PassportType</td>"; trow += "<td>$ApplicationType</td>"; trow += "<td>$FineName</td>"; trow += "<td>$DurationName</td>"; trow += "<td>$PaymentType</td>"; trow += "<td>$PaymentPrice</td>"; trow += "<td>$IssueDate_Shamsi</td>"; trow += "<td>$StatusName</td>"; trow += "</tr>"; trow = trow.Replace("$NO", counter.ToString()); for (int i = 0; i < reader.FieldCount; i++) { string colName = reader.GetName(i); trow = trow.Replace("$" + colName, Convert.ToString(reader[colName])); } tbody += trow; counter++; } tbody += "</tbody></table> <br/>"; tbody += "<div style='margin-right:20px; margin-left:20px;'>"; System.Data.Common.DbDataReader summaryReader = plus.Data.DAL.Read(subPathArray[0], string.Format("exec [rpt].[spGetPassportCountReportByStockOutId] {0}, {1}, {2}", 1, recordid, "Status") , "RecordID", recordid); String sthead = ""; String summary = "<table class='table' style='width:90%;'>"; String stbody = "<tbody>"; sthead += "<thead>"; sthead += "<tr>"; sthead += "<th colspan='10' style='border:none; font-size:16px;'>خلص گزارش پاسپورت های چاپ شده نظر به مقدار پرداخت</th>"; sthead += "</tr>"; sthead += "<tr>"; sthead += "<th>نوعیت پرداخت</th>"; sthead += "<th> مقدار پرداخت</th>"; sthead += "<th> باطل شده</th>"; sthead += "<th>تائید شده</th>"; sthead += "<th>مجموع چاپ شده</th>"; sthead += "</tr>"; sthead += "</thead>"; summary += sthead; while (summaryReader.Read()) { stbody += "<tr>"; stbody += "<th>@PaymentType</th>"; stbody += "<th>@PaymentPrice</th>"; stbody += "<th>@SPOILED</th>"; stbody += "<th>@PRINTED</th>"; stbody += "<th>@TOTAL</th>"; stbody += "</tr>"; for (int i = 0; i < summaryReader.FieldCount; i++) { string colName = summaryReader.GetName(i); stbody = stbody.Replace("@" + colName, Convert.ToString(summaryReader[colName])); } } summary += stbody; summary += "</table>"; tbody += summary; tbody += "</div>"; tbody += "</page>"; html = html.Replace("$body", tbody); } } else if (type.Equals("closedlist")) { string start = Request.QueryString["start"]; string end = Request.QueryString["end"]; string starts = Request.QueryString["starts"]; string ends = Request.QueryString["ends"]; string postofficename = Request.QueryString["postofficename"]; reader = plus.Data.DAL.Read(pathArray[0], string.Format("SELECT * FROM {0}.{1} WHERE ClosedDate BETWEEN '{2}' AND '{3}' AND PostOfficeID = @PostOffice", pathArray[1], pathArray[2], start, end) , "PostOffice", recordid); String tbody = " <page size='A4' class='Print'>"; tbody += "<table class='table'>"; String thead = ""; thead += "<thead>"; thead += "<tr>"; thead += "<th colspan='10' style='border:none; font-size:16px;'>گزارش درخواست های مربوط پسته خانه $PostOffice که از تاریخ $starts الی تاریخ $ends تکمیل و نهائی گردیده اند</th>"; thead += "</tr>"; thead += "<tr>"; thead += "<th> شماره</th>"; thead += "<th> نمبر پاسپورت</th>"; thead += "<th> نام متقاضی</th>"; thead += "<th> نام پدر</th>"; thead += "<th> نام پدر کلان</th>"; thead += "<th>محل تولد</th>"; thead += "<th>پوسته خانه</th>"; thead += "<th>نمبر تلفون</th>"; thead += "<th>ملاحضات</th>"; thead += "</tr>"; thead += "</thead>"; thead = thead.Replace("$PostOffice", postofficename); thead = thead.Replace("$starts", starts); thead = thead.Replace("$ends", ends); tbody += thead; tbody += "<tbody>"; int counter = 1; while (reader.Read()) { string trow = "<tr>"; trow += "<td>$NO</td>"; trow += "<td>$PassportNumber</td>"; trow += "<td>$FullName</td>"; trow += "<td>$FatherNameLocal</td>"; trow += "<td>$GrandFatherNameLocal</td>"; trow += "<td>$LocationName</td>"; trow += "<td>$PostOffice</td>"; trow += "<td>$Mobile</td>"; trow += "<td style='width:150px;'></td>"; trow += "</tr>"; trow = trow.Replace("$NO", counter.ToString()); for (int i = 0; i < reader.FieldCount; i++) { string colName = reader.GetName(i); trow = trow.Replace("$" + colName, Convert.ToString(reader[colName])); } tbody += trow; counter++; } tbody += "</tbody>"; tbody += "</page>"; html = html.Replace("$body", tbody); } reader.Close(); Response.Write(html); } }
/// <summary> /// 根据模板将datareaser数据导成过滤指定列的EXCEL格式 /// </summary> /// <param name="dataReader">数据集</param> /// <param name="strFilePath">模板文件路径</param> /// <param name="intStartRow">起始行</param> /// <param name="strFileFullPath">全路径</param> /// <param name="arrFilterColumn">过滤指定列,列名(别名)列表</param> public static void ExportExcelByTemplate(DbDataReader dataReader, string strFilePath, int intStartRow, string strFileFullPath, string[] arrFilterColumn) { Workbook excelTemplate = new Workbook(); Workbook excel = new Workbook(); //建立excel并打开模板文件 excelTemplate.Open(strFilePath); int intSheetRowCount = 0; int intSheetCount = 0; excel.Worksheets[intSheetRowCount].Copy(excelTemplate.Worksheets[0]); Cells cells = excel.Worksheets[intSheetCount].Cells;// sheet.Cells; Object[] arrObj = new Object[dataReader.FieldCount];// List<string> list = null; if (arrFilterColumn != null) { list = new List<string>(arrFilterColumn);//过滤指定列,列名(别名)列表 } while (dataReader.Read()) { if (intSheetRowCount == IntPageSize) { excel.Worksheets.Add(); intSheetCount += 1; excel.Worksheets[intSheetCount].Copy(excelTemplate.Worksheets[0]); intSheetRowCount = 0; cells = excel.Worksheets[intSheetCount].Cells; } if (arrFilterColumn != null) { ArrayList arrList = new ArrayList(); for (int i = 0; i < dataReader.FieldCount; i++) { if (!Array.Exists(list.ToArray(), p => { return p.Equals(dataReader.GetName(i), StringComparison.OrdinalIgnoreCase); })) { arrList.Add(dataReader.GetValue((i))); } } Object[] arrObjTmp = arrList.ToArray(); cells.ImportObjectArray(arrObjTmp, intStartRow + intSheetRowCount, 0, false); } else { dataReader.GetValues(arrObj); cells.ImportObjectArray(arrObj, intStartRow + intSheetRowCount, 0, false); } intSheetRowCount += 1; } excel.Worksheets.Add(); excel.Save(strFileFullPath, SaveFormat.Auto); }
private static ColumnMetadata[] GetTableMetadata(DbDataReader reader) { var columnMetadata = new ColumnMetadata[reader.FieldCount]; for (var i = 0; i < reader.FieldCount; i++) { columnMetadata[i] = new ColumnMetadata( reader.GetName(i), reader.GetDataTypeName(i), reader.GetFieldType(i)); } return columnMetadata; }
public void ExportExcel() { ExcelPackage pck = new ExcelPackage(); var ws = pck.Workbook.Worksheets.Add("Sample1"); ws.View.RightToLeft = true; System.Data.Common.DbDataReader reader = null; String TypeString = Request.QueryString["Type"]; String path = Request.QueryString["Path"].Split('.')[0]; if (TypeString.Equals("timesheet")) { System.Collections.IDictionary param = new Dictionary <string, object>(); var UserID = Request.QueryString["userID"]; var OrgUnit = Request.QueryString["orgID"]; var start = Request.QueryString["start"]; var end = Request.QueryString["end"]; var rank = Request.QueryString["rank"]; param.Add("ID", null); param.Add("OrganizationUnitID", null); param.Add("StartDate", null); param.Add("EndDate", null); param.Add("Rank", null); if (UserID.Length != 0) { param["ID"] = UserID; } if (OrgUnit.Length != 0) { param["OrganizationUnitID"] = OrgUnit; } if (start.Length != 0) { param["StartDate"] = start; } if (end.Length != 0) { param["EndDate"] = end; } if (rank.Length != 0) { param["Rank"] = rank; } param.Add("CreatedBy", 1); param.Add("Offset", 0); param.Add("Limit", 0); int typeID = 0; try { typeID = Convert.ToInt32(Request.QueryString["typeID"]); } catch (Exception) { } SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[path].ToString()); conn.Open(); using (SqlCommand command = new SqlCommand()) { command.Connection = conn; command.CommandType = CommandType.StoredProcedure; command.CommandText = "att.spGetMonthlyTimeSheet"; foreach (var ent in param.Keys) { command.Parameters.Add(new SqlParameter(ent.ToString(), param[ent])); } command.CommandTimeout = 200; reader = command.ExecuteReader(); }; //reader = plus.Data.DAL.Read("AMS", "att.spGetMonthlyTimeSheet",param); Dictionary <int, String> maps = new Dictionary <int, string>(); maps.Add(2, "PR"); maps.Add(3, "AB"); maps.Add(4, "HD"); maps.Add(5, "ALH"); maps.Add(6, "AL"); StringBuilder sb = new StringBuilder(); sb.Append("<thead>"); sb.Append("<tr>"); sb.Append("<th></th><th></th><th></th><th></th><th></th><th></th>"); char a = 'A'; ws.Cells[a + "" + 1].Value = "کود"; a++; ws.Cells[a + "" + 1].Value = "نام مکمل"; a++; ws.Cells[a + "" + 1].Value = "نام پدر"; a++; ws.Cells[a + "" + 1].Value = "وظیفه"; a++; ws.Cells[a + "" + 1].Value = "بست"; a++; ws.Cells[a + "" + 1].Value = "اداره"; a++; char b = ' '; for (int i = 0; i < reader.FieldCount; i++) { if (i > 6) { if (a > 'Z') { b = 'A'; a = 'A'; } String val = reader.GetName(i).Split('_')[2]; ws.Cells[b + "" + a + "" + 1].Value = val; a++; } } int g = 2; while (reader.Read()) { a = 'A'; b = ' '; for (int i = 1; i < reader.FieldCount; i++) { if (i < 7 || (typeID == 0 || typeID == 1)) { String val = reader[i].ToString(); ws.Cells[b + "" + a + "" + g].Value = val; } else { string s = reader[i].ToString(); if (maps[typeID].Equals(s)) { String val = reader[i].ToString(); ws.Cells[b + "" + a + "" + g].Value = val; } else { } } a++; if (a > 'Z') { a = 'A'; b = 'A'; } } g++; } conn.Close(); } else if (TypeString.Equals("attreport")) { int typeID = 0; try { typeID = Convert.ToInt32(Request.QueryString["typeID"]); } catch (Exception) { } System.Collections.IDictionary param = new Dictionary <string, object>(); var UserID = Request.QueryString["userID"]; var OrgUnit = Request.QueryString["orgID"]; var start = Request.QueryString["start"]; var end = Request.QueryString["end"]; var rank = Request.QueryString["rank"]; param.Add("UserID", null); param.Add("OrganizationUnit", null); param.Add("StartDate", null); param.Add("EndDate", null); param.Add("RankID", null); param.Add("AttID", null); param.Add("HRCode", null); param.Add("CreatedBy", null); param.Add("Type", null); if (UserID.Length != 0) { param["UserID"] = UserID; } if (OrgUnit.Length != 0) { param["OrganizationUnit"] = OrgUnit; } if (start.Length != 0) { param["StartDate"] = start; } if (end.Length != 0) { param["EndDate"] = end; } if (rank.Length != 0) { param["RankID"] = rank; } if (typeID != 0) { param["Type"] = typeID; } reader = plus.Data.DAL.Read("AMS", "att.spGenerateTimeSheet", param); StringBuilder sb = new StringBuilder(); char a = 'A'; ws.Cells[a + "" + 1].Value = " کود کادری "; a++; ws.Cells[a + "" + 1].Value = "نام مکمل"; a++; ws.Cells[a + "" + 1].Value = "نام پدر"; a++; ws.Cells[a + "" + 1].Value = "وظیفه"; a++; ws.Cells[a + "" + 1].Value = "بست"; a++; ws.Cells[a + "" + 1].Value = "ریاست"; a++; ws.Cells[a + "" + 1].Value = "شیفت"; a++; ws.Cells[a + "" + 1].Value = "تاریخ"; a++; ws.Cells[a + "" + 1].Value = "وقت دخول"; a++; ws.Cells[a + "" + 1].Value = "وقت خروج"; a++; ws.Cells[a + "" + 1].Value = "حاضری"; a++; char b = ' '; int g = 2; while (reader.Read()) { a = 'A'; b = ' '; for (int i = 1; i < reader.FieldCount; i++) { if (!reader.GetName(i).Equals("AttType")) { ws.Cells[b + "" + a + "" + g].Value = reader[i].ToString(); } a++; if (a > 'Z') { a = 'A'; b = 'A'; } } g++; } } ws.Cells[ws.Dimension.Address].AutoFitColumns(); pck.SaveAs(Response.OutputStream); Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.AddHeader("content-disposition", "attachment; filename=Sample1.xlsx"); }
private static DataTable FillColumns(DbDataReader reader) { var xdata = new DataTable(); Console.WriteLine("FillColumns " + new { reader.FieldCount }); for (int i = 0; i < reader.FieldCount; i++) { var columName = reader.GetName(i); Console.WriteLine("FillColumns " + new { i, columName }); xdata.Columns.Add(columName); } return xdata; }
public static bool HasName(this DbDataReader reader, string columnName) => Enumerable.Range(0, reader.FieldCount) .Any(i => string.Equals(reader.GetName(i), columnName, StringComparison.OrdinalIgnoreCase));
/// <summary> /// Requires: a public type with a public, default constructor. Returns a column map initializing the type /// and all properties of the type with a public setter taking a primitive type and having a corresponding /// column in the reader. /// </summary> internal static CollectionColumnMap CreateColumnMapFromReaderAndClrType(DbDataReader reader, Type type, MetadataWorkspace workspace) { Debug.Assert(null != reader); Debug.Assert(null != type); Debug.Assert(null != workspace); // we require a default constructor ConstructorInfo constructor = type.GetConstructor(BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance, null, Type.EmptyTypes, null); if (type.IsAbstract || (null == constructor && !type.IsValueType)) { throw EntityUtil.InvalidOperation( Strings.ObjectContext_InvalidTypeForStoreQuery(type)); } // build a LINQ expression used by result assembly to create results var memberInfo = new List<Tuple<MemberAssignment, int, EdmProperty>>(); foreach (PropertyInfo prop in type.GetProperties(BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance)) { // for enums unwrap the type if nullable var propertyUnderlyingType = Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType; Type propType = propertyUnderlyingType.IsEnum ? propertyUnderlyingType.GetEnumUnderlyingType() : prop.PropertyType; EdmType modelType; int ordinal; if (TryGetColumnOrdinalFromReader(reader, prop.Name, out ordinal) && MetadataHelper.TryDetermineCSpaceModelType(propType, workspace, out modelType) && (Helper.IsScalarType(modelType)) && prop.CanWrite && prop.GetIndexParameters().Length == 0 && null != prop.GetSetMethod(/* nonPublic */true)) { memberInfo.Add(Tuple.Create( Expression.Bind(prop, Expression.Parameter(prop.PropertyType, "placeholder")), ordinal, new EdmProperty(prop.Name, TypeUsage.Create(modelType)))); } } // initialize members in the order in which they appear in the reader MemberInfo[] members = new MemberInfo[memberInfo.Count]; MemberBinding[] memberBindings = new MemberBinding[memberInfo.Count]; ColumnMap[] propertyMaps = new ColumnMap[memberInfo.Count]; EdmProperty[] modelProperties = new EdmProperty[memberInfo.Count]; int i = 0; foreach (var memberGroup in memberInfo.GroupBy(tuple => tuple.Item2).OrderBy(tuple => tuple.Key)) { // make sure that a single column isn't contributing to multiple properties if (memberGroup.Count() != 1) { throw EntityUtil.InvalidOperation(Strings.ObjectContext_TwoPropertiesMappedToSameColumn( reader.GetName(memberGroup.Key), String.Join(", ", memberGroup.Select(tuple => tuple.Item3.Name).ToArray()))); } var member = memberGroup.Single(); MemberAssignment assignment = member.Item1; int ordinal = member.Item2; EdmProperty modelProp = member.Item3; members[i] = assignment.Member; memberBindings[i] = assignment; propertyMaps[i] = new ScalarColumnMap(modelProp.TypeUsage, modelProp.Name, 0, ordinal); modelProperties[i] = modelProp; i++; } NewExpression newExpr = null == constructor ? Expression.New(type) : Expression.New(constructor); MemberInitExpression init = Expression.MemberInit(newExpr, memberBindings); InitializerMetadata initMetadata = InitializerMetadata.CreateProjectionInitializer( (EdmItemCollection)workspace.GetItemCollection(DataSpace.CSpace), init, members); // column map (a collection of rows with InitializerMetadata markup) RowType rowType = new RowType(modelProperties, initMetadata); RecordColumnMap rowMap = new RecordColumnMap(TypeUsage.Create(rowType), "DefaultTypeProjection", propertyMaps, null); CollectionColumnMap collectionMap = new SimpleCollectionColumnMap(rowType.GetCollectionType().TypeUsage, rowType.Name, rowMap, null, null); return collectionMap; }
public static dynamic consulta(string query, bool tipoSelect = false, bool eliminando = false) { var consulta = new List <Dictionary <string, object> >(); try { conexion.Open(); NpgsqlCommand cmd = new NpgsqlCommand(query, conexion); if (!tipoSelect) { System.Data.Common.DbDataReader datos = cmd.ExecuteReader(); while (datos.Read()) { Dictionary <string, object> objeto = new Dictionary <string, object>(); for (int x = 0; x < datos.FieldCount; x++) { objeto.Add(datos.GetName(x), datos.GetValue(x)); } consulta.Add(objeto); } // consulta.Clear(); // consulta = new List<Dictionary<string, object>>(); } else { try { cmd.ExecuteNonQuery(); conexion.Close(); cmd.Dispose(); return(true); } catch (Exception e) { if (eliminando) { return(false); } MessageBox.Show(e.Message, "Advertencia", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); conexion.Close(); return(false); } } conexion.Close(); } catch (Exception e) { if (eliminando) { return(false); } MessageBox.Show(e.Message, "Advertencia", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); conexion.Close(); } return(consulta); }