public static async System.Threading.Tasks.Task Test()
{
SqlFactory fac = new SqlClientFactory();
// DbConnection.ProviderFactory => DbProviderFactory;
using (System.Data.Common.DbConnection con = fac.Connection)
{
string sql = "SELECT * FROM T_BlogPost";
string sql_paged = sql += fac.PagingTemplate(3, 2);
string sql_limited = sql += fac.PagingTemplate(1);
IEnumerable <T_BlogPost> a = con.Query <T_BlogPost>(sql);
IEnumerable <T_BlogPost> aa = await con.QueryAsync <T_BlogPost>(sql_paged);
T_BlogPost b = con.QuerySingle <T_BlogPost>(sql_limited);
T_BlogPost ba = await con.QuerySingleAsync <T_BlogPost>(sql_limited);
} // End Using con
} // End Sub Test
} // End Action ShowEntry
//
// GET: /Blog/Delete/5
public ActionResult EditEntry(System.Guid?id)
{
T_BlogPost bp = null;
// string sql = "SELECT {0} BP_UID
string sql = "SELECT BP_UID FROM T_BlogPost ORDER BY BP_EntryDate DESC" + this.m_fac.PagingTemplate(1);
using (System.Data.Common.DbConnection con = this.m_fac.Connection)
{
id = con.QuerySingle <System.Guid>(sql);
}
sql = "SELECT * FROM T_BlogPost WHERE BP_UID = @__bp_uid";
using (System.Data.Common.DbConnection con = this.m_fac.Connection)
{
bp = con.QuerySingle <T_BlogPost>(sql, new { __bp_uid = id });
}
return(View(bp));
} // End Action EditEntry
//
// GET: /Blog/Delete/5
public ActionResult ShowEntry_old(System.Guid?id)
{
string host = (string)this.RouteData.Values["Host"];
System.Console.WriteLine(host);
T_BlogPost bp = null;
// string lol = "http://localhost/image.aspx?&postimage_text=%0A%5Burl%3Dhttp%3A%2F%2Fpostimg.org%2Fimage%2Fu0zc6aznf%2F%5D%5Bimg%5Dhttp%3A%2F%2Fs1.postimg.org%2Fu0zc6aznf%2Fhtc_hero_wallpaper_03.jpg%5B%2Fimg%5D%5B%2Furl%5D%0A";
// //"http://localhost/image.aspx?&postimage_text=[url=http://postimg.org/image/u0zc6aznf/][img]http://s1.postimg.org/u0zc6aznf/htc_hero_wallpaper_03.jpg[/img][/url]
// lol = System.Web.HttpUtility.UrlDecode(lol);
// System.Console.WriteLine(lol);
if (!id.HasValue)
{
string fetchLatestId = "SELECT BP_UID FROM T_BlogPost ORDER BY BP_EntryDate DESC" + this.m_fac.PagingTemplate(1);
using (System.Data.Common.DbConnection con = this.m_fac.Connection)
{
id = con.QuerySingle <System.Guid>(fetchLatestId);
}
}
string sql = "SELECT * FROM T_BlogPost WHERE BP_UID = @__bp_uid";
using (System.Data.Common.DbConnection con = this.m_fac.Connection)
{
bp = con.QuerySingle <T_BlogPost>(sql, new { __bp_uid = id });
}
bp.BP_Content = ReplaceURLs(bp.BP_Content);
// http://stackoverflow.com/questions/16389234/create-dropdown-with-predefined-values-in-asp-net-mvc-3-using-razor-view/16389278#16389278
ViewData["myList"] =
new SelectList(new[] { "10", "15", "25", "50", "100", "1000" }
.Select(x => new { value = x, text = x }),
"value", "text", "15");
System.Collections.Generic.List <SelectListItem> ls = new System.Collections.Generic.List <SelectListItem>();
ls.Add(new SelectListItem()
{
Text = "Yes", Value = "true", Selected = true
});
ls.Add(new SelectListItem()
{
Text = "No", Value = "false", Selected = false
});
ls.Add(new SelectListItem()
{
Text = "Not Applicable", Value = "NULL", Selected = false
});
ViewData["myList"] = ls;
ViewData["myList"] = new[] { "10", "15", "25", "50", "100", "1000" }
.Select(x => new SelectListItem
{
Selected = x == "25",
Text = x,
Value = x
});
ViewData["myList"] =
new SelectList(new[] { "10", "15", "25", "50", "100", "1000" }
.Select(x => new SelectListItem {
Value = x, Text = x
}),
"Value", "Text", "15");
ViewData["myList"] =
from c in new[] { "10", "15", "25", "50", "100", "1000" }
select new SelectListItem
{
Selected = (c == "25"),
Text = c,
Value = c
};
return(View(bp));
} // End Action ShowEntry
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);
}
}
