Tech Tips: October 2016

Thursday, October 27, 2016

Format RDLC report to export to an excel, group wise

Bind data to a dataset.

Use a matrix to present the data.

If we want to export this report to an excel sheet wise by name, it can be done as follows.
Right click on the "Name" in Row groups and select Group Properties.
Mark the check box as follows.

After exporting to an excel it will be like this.

If we want to get the "Name" as sheet name, instead of Sheet1, Sheet2....it can be done with the following simple modification.
First select the "Name" group in the Row Groups pane so that its properties are displayed in the Properties pane. In the Properties pane, locate the Group > PageName property and specify the following expression:

[code] =Fields!Name.Value [/code]

Result will be like this.

Where The Sheets Have A Name

Format report based on conditions (SSRS) (Part2)

Assume that there are multiple datasets, and we want to do a calculation in a textbox using multiple values from multiple datasets.

Add a textbox to the report, and write the expression as fallowing.

[code]
=First(Fields!StartersIssueValue.Value, "StartersIssue")- First(Fields!StartersReturnValue.Value, "StartersReturn") -First(Fields!StartersClosingValue.Value, "StartersClosing") [/code]

When getting the total of a particular value, it can be written like this.

[code] =Sum(Fields!Value.Value, "Cost") [/code]

Change the background color based on a condition

Select the table row and set background properties as follows.

[code]
=SWITCH(Fields!Font.Value ="1B", "#dcf9db",Fields!Font.Value ="1N", "#dcf9db", Fields!Font.Value="2B", "#cee9c3",Fields!Font.Value="2N", "#cee9c3")
[/code]

Result will be like this.

Data Driven Colored Text for Reporting Services Reports

Wednesday, October 12, 2016

Using the JQuery Select2 with dropdown lists

Download select2 style sheets and java scripts. Add them in the header of the page.

[code]
<script src="../Scripts/select2.js"></script>
<script src="../Scripts/select2.min.js"></script>
<link href="../Styles/select2.css" rel="stylesheet" />
<link href="../Styles/select2.min.css" rel="stylesheet" />

<asp:DropDownList ID="ddlSupplier" runat="server" CssClass="form-control" Width="200px" AutoPostBack="True"></asp:DropDownList>
[/code]

Add the following java script to apply the autocomplete option.

[code]
<script>
function pageLoad() {
$("#" + "<%=ddlSupplier.ClientID%>").select2({
placeholder: "Select Supplier",
allowClear: true
});
}
</script>
[/code]

When the dropdownlist is in a gridview, some changes should be done in the above javascript.

[code]
<script>
function pageLoad() {
$("#<%=GridView1.ClientID %> select[id*='ddlItem']").select2({
placeholder: "Select an Item",
allowClear: true
});
}
</script>
[/code]

[code]
<asp:TemplateField HeaderText="Item">
<EditItemTemplate>
<asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("ItemName") %>'></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:DropDownList ID="ddlItem" runat="server" AutoPostBack="True" CssClass="form-control input-sm" OnSelectedIndexChanged="ddlItem_SelectedIndexChanged" Width="200px">
</asp:DropDownList>
</FooterTemplate>
<ItemTemplate>
<asp:Label ID="lblItem" runat="server" Text='<%# Bind("ItemName") %>'></asp:Label>
</ItemTemplate>

</asp:TemplateField>
[/code]

Links: Using the Select2 JQuery Plugin with Bootstrap 3

Select2 3.5.3

Tuesday, October 4, 2016

Save and retrieve images from database using C# in ASP.NET

Create the interface with a file uploader and an image like this.

Create a table to save images.

We'll see how to save an image to the database. Add the following html code to the create the interface.

<head runat="server">
<title></title>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script type="text/javascript">

function readURL(input) {
if (input.files && input.files[0]) {
var reader = new FileReader();
reader.onload = function (e) {
$('#<%=Image1.ClientID%>').prop('src', e.target.result)
}
reader.readAsDataURL(input.files[0]);
}
}
$("#uploadFile").change(function () {
readURL(this);
});
</script>
</head>
<body>
<form id="form1" runat="server">

<table>
<tr>
<td>
<asp:Label ID="Label1" runat="server" Text="Attach Image:" Style="font-weight: 700"></asp:Label>
</td>
<td>
<asp:FileUpload ID="uploadFile" runat="server" onchange="readURL(this)" />
</td>
</tr>
<tr>
<td colspan="2">
<asp:Image ID="Image1" runat="server" Height="100px" Width="100px" />
</td>
</tr>
<tr>
<td>
<asp:Button ID="btnSave" runat="server" Text="Save" Width="75px" ValidationGroup="Save" OnClick="btnSave_Click" />
</td>
<td>
<asp:Button ID="btnRetrive" runat="server" Text="Retrive" OnClick="btnRetrive_Click" />
</td>
</tr>
</table>
</form>

</body>

After attaching an image, it shows the preview in the image control.

The image saving code is as follows.

protected void btnSave_Click(object sender, EventArgs e)

{

int imageId = 1;//Table's first row - default image

if (uploadFile.PostedFile.FileName != string.Empty)

{

imageId = imageUpload();

}

Image1.ImageUrl = "Image.aspx?ImageID=" + imageId + "";

}

private int imageUpload()

{

string filePath = uploadFile.PostedFile.FileName;

int imageId = 1;

string filename = Path.GetFileName(filePath);

string ext = Path.GetExtension(filename);

ext = ext.ToLower();

string contenttype = String.Empty;

switch (ext)

{

case ".jpg":

contenttype = "image/jpg";

break;

case ".png":

contenttype = "image/png";

break;

case ".gif":

contenttype = "image/gif";

break;

default:

break;

}

if (contenttype != String.Empty)

{

Stream fs = uploadFile.PostedFile.InputStream;

BinaryReader br = new BinaryReader(fs);

byte[] bytes = br.ReadBytes((Int32)fs.Length);

imageId = page6Service.UpdateImage(filename, contenttype, bytes);

}

return imageId;

}

We can retrieve a particular saved image with the following code

protected void btnRetrive_Click(object sender, EventArgs e)

{

Image1.ImageUrl = "Image.aspx?ImageID=1";

}

To retrieve an image like this, first we have to create a web page as Image.aspx with an empty form. In the Page_Load event of that page add this code.

protected void Page_Load(object sender, EventArgs e)
{
if (Request.QueryString["ImageID"] != null)
{
try
{
DataTable dt = page6Service.retrieveImage(Convert.ToInt32(Request.QueryString["ImageID"]));
if (dt.Rows.Count > 0)
{
byte[] bytes = (byte[])dt.Rows[0]["Data"];
Response.Buffer = true;
Response.Charset = "";
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType = dt.Rows[0]["ContentType"].ToString();
Response.AddHeader("content-disposition", "attachment;filename=" + dt.Rows[0]["Name"].ToString());
Response.BinaryWrite(bytes);
Response.Flush();
Response.End();
}
}
catch (Exception ex)
{
throw ex;
}
}
}

Monday, October 3, 2016

Adding date and time picker

[code]
<asp:Content ID="Content1" ContentPlaceHolderID="head" runat="server">
<script type="text/javascript" src="//cdn.jsdelivr.net/momentjs/latest/moment.min.js"></script>
<script type="text/javascript" src="//cdn.jsdelivr.net/bootstrap.daterangepicker/2/daterangepicker.js"></script>
<link rel="stylesheet" type="text/css" href="//cdn.jsdelivr.net/bootstrap.daterangepicker/2/daterangepicker.css" />
</asp:Content>

<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">
<div class="col-md-2">
<asp:TextBox ID="txtSessiontime" runat="server" CssClass="form-control" Width="200px" />
<script type="text/javascript">
$(function () {

$("[id$=txtSessiontime]").daterangepicker({
singleDatePicker: true,
timePicker: true,
timePickerIncrement: 15,
locale: {
format: 'DD-MM-YYYY h:mm A'
}
});
});
</script>
</div>
[/code]

Adding a grid view filter (Part I)

First add the grid view to the page.

Add the following java script in the header section

[code]
<script>
function filter2(phrase, _id) {
var words = phrase.value.toLowerCase().split(" ");
var table = document.getElementById(_id);
var ele;
for (var r = 1; r < table.rows.length; r++) {
ele = table.rows[r].innerHTML.replace(/<[^>]+>/g, "");
var displayStyle = 'none';
for (var i = 0; i < words.length; i++) {
if (ele.toLowerCase().indexOf(words[i]) >= 0)
displayStyle = '';
else {
displayStyle = 'none';
break;
}
}
table.rows[r].style.display = displayStyle;
}
}
</script>
[/code]

Then add a search text box with this html code.

[code]
<div class="input-group add-on" style="width: 60%">
<input name="filter" onkeyup="filter2(this, 'GridView1')" class="form-control" placeholder="Search" type="text" />
<div class="input-group-btn">
<asp:LinkButton ID="LinkButton1" runat="server" CssClass="btn btn-default"><i aria-hidden="true" class="glyphicon glyphicon-search"></i></asp:LinkButton>
</div>
</div>
[/code]

This filters texts in any column.

Full code

[code]
<asp:Content ID="Content1" ContentPlaceHolderID="head" runat="server">
<script>
function filter2(phrase, _id) {
var words = phrase.value.toLowerCase().split(" ");
var table = document.getElementById(_id);
var ele;
for (var r = 1; r < table.rows.length; r++) {
ele = table.rows[r].innerHTML.replace(/<[^>]+>/g, "");
var displayStyle = 'none';
for (var i = 0; i < words.length; i++) {
if (ele.toLowerCase().indexOf(words[i]) >= 0)
displayStyle = '';
else {
displayStyle = 'none';
break;
}
}
table.rows[r].style.display = displayStyle;
}
}
</script>
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">
<div class="input-group add-on" style="width: 60%">
<input name="filter" onkeyup="filter2(this, 'GridView1')" class="form-control" placeholder="Search" type="text" />
<div class="input-group-btn">
<asp:LinkButton ID="LinkButton1" runat="server" CssClass="btn btn-default"><i aria-hidden="true" class="glyphicon glyphicon-search"></i></asp:LinkButton>
</div>
</div>
<br />
<asp:GridView ID="GridView1" ClientIDMode="Static" runat="server" Width="60%" CssClass="table table-striped table-bordered table-hover" AutoGenerateColumns="False"
DataKeyNames="CustomerID" EmptyDataText="There are no data records to display."
Height="16px" AllowPaging="True">
<Columns>
<asp:BoundField DataField="Name" HeaderText="Patient Name" SortExpression="Name">
<HeaderStyle HorizontalAlign="Left" />
<ItemStyle Width="170px" />
</asp:BoundField>
<asp:BoundField DataField="Address" HeaderText="Address" HeaderStyle-HorizontalAlign="Right" SortExpression="Address" HeaderStyle-CssClass="visible-lg" ItemStyle-CssClass="visible-lg">
<ControlStyle CssClass="gridViewHeader" />
<HeaderStyle Width="450px" HorizontalAlign="Left" />
<ItemStyle HorizontalAlign="left" />
</asp:BoundField>
<asp:BoundField DataField="Age" HeaderText="Age" SortExpression="Age" HeaderStyle-CssClass="visible-lg" ItemStyle-CssClass="visible-lg">
<HeaderStyle HorizontalAlign="Left" />
<ItemStyle Width="100px" />
</asp:BoundField>
<asp:BoundField DataField="Mobile" HeaderText="Mobile" SortExpression="Mobile" ItemStyle-CssClass="hidden-xs" HeaderStyle-CssClass="hidden-xs">
<HeaderStyle HorizontalAlign="Left" />
<ItemStyle Width="100px" />
</asp:BoundField>
</Columns>
</asp:GridView>
</asp:Content>

[/code]

Sunday, October 2, 2016

Format report based on conditions (SSRS) (Part1)

Bind the data set to the report.

Select the "Font" text box and set font properties as follows.

[code] =iif(Fields!Font.Value = "B","Bold",iif(Fields!Font.Value = "BU","Bold",iif(Fields!Font.Value = "BUU","Bold","Normal"))) [/code]

Select the "Balance" text box and set Border style properties as follows.

Top
[code] =iif(Previous(Fields!Font.Value) ="BUU","Double","Solid") [/code]

Bottom
[code] =iif(Fields!Font.Value = "BUU","Double","Solid") [/code]

For the double lines, set Border width properties as follows.

Top
[code] =iif(Previous(Fields!Font.Value) ="BUU","3pt","1pt")[/code]

Bottom
[code] =iif(Fields!Font.Value = "BUU","3pt","1pt") [/code]

Result will be like this.

You can see that the bottom row also should have double lines as the font is "BUU", but there is only a single line. Just simply add an extra row below the table.

Tip: When there are double lines ,

MSDN thread suggests a few requirements to get this running:

Double Line border turn to be single in Reporting Service

The border width must be 3pt at least.
There must be at least one row beneath the Double border text box.

One more note - this is not required for Excel - just add a 1pt Double border and it will be fine. The above series of steps are only required for Web, PDF, etc.