Get stuck in

Monday 13 January 2020

SQL Table : Change Row As Column and Group Them... ( Setting column values as column names in the SQL query result )

Setting column values as column names in the SQL query result

Problem Statement :

id	col1	col2
1	Pending	30
1	Resolved	48
3	Pending	7
3	Resolved	1
4	Pending	11
4	Resolved	22

Output :

id	Pending	Resolved
1	30	48
3	7	1
4	11	22

SQL Query :

SELECT
id,
MAX(CASE WHEN (col1 = 'Pending') THEN col2 ELSE NULL END) AS Pending,
MAX(CASE WHEN (col1 = 'Resolved') THEN col2 ELSE NULL END) AS Resolved
FROM
tableName
GROUP BY id
ORDER BY id

-- col1 matches the 'Pending' string of this CASE, return col2, otherwise return NULL
-- Then, the outer MAX() aggregate will eliminate all NULLs and collapse it down to one row per id

Thursday 11 October 2018

Date time Picker / Calendar in ASP.Net Visual Studio with Jquery with Sample Code

DateTime Picker / Calendar in ASP.Net Visual Studio with Jquery with Sample Code

Step 1 - Download Jquery utility used in this Sample Project
-----------------------------------------------------------------------------------------------
Step 2 - Extract the above Jquery.rar And Put it in your project folder
-----------------------------------------------------------------------------------------------
Step 3 - Create a class file JQueryUtils under Util namespace as follows

using System;
using System.Collections.Generic;
using System.Text;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace Util
{
public class JQueryUtils
{
public static void RegisterTextBoxForDatePicker(Page page, params TextBox[] textBoxes)
{
RegisterTextBoxForDatePicker(page, "dd/mm/yy", textBoxes);
}

public static void RegisterTextBoxForDatePicker(Page page, string format, params TextBox[] textBoxes)
{
bool allTextBoxNull = true;
foreach (TextBox textBox in textBoxes)
{
if (textBox != null) allTextBoxNull = false;
}

if (allTextBoxNull) return;

page.ClientScript.RegisterClientScriptInclude(page.GetType(), "jquery", "JQuery/jquery.js");
page.ClientScript.RegisterClientScriptInclude(page.GetType(), "jquery.ui.all", "JQuery/ui/jquery.ui.all.js");
page.ClientScript.RegisterClientScriptBlock(page.GetType(), "datepickerCss", "<link rel=\"stylesheet\" href=\"JQuery/themes/flora/flora.datepicker.css\" />");

StringBuilder sb = new StringBuilder();
sb.Append("$(document).ready(function() {");
foreach (TextBox textBox in textBoxes)
{
if (textBox != null)
{
sb.Append("$('#" + textBox.ClientID + "').datepicker({dateFormat: \"" + format + "\"});");
}
}
sb.Append("});");
page.ClientScript.RegisterClientScriptBlock(page.GetType(), "jQueryScript", sb.ToString(), true);
}
}
}
----------------------------------------------------------------------------------------
Step 4 - Now on your .aspx page let suppose it is Default.aspx, add a text box control as follow -

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Date Page</title>
</head>
<body>
<form id="form1" runat="server">
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
</form>
</body>

</html>

-------------------------------------------------------------------------------------------

Step 5 - Now in the code page of above .aspx page (step4) Default.aspx.cs add following line of code in page load event

public partial class _Default : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

Util.JQueryUtils.RegisterTextBoxForDatePicker(Page, TextBox1);

}

* Please note above underline control name is same as in .aspx page.

-------------------------------------------------------------------------------------------

The complete sample project can be downloaded from here.

Wednesday 3 October 2018

Execute SQL queries with a batch file or Command Line

OSQL is a command line tool that allows you to issue commands to Microsoft SQL Server.
The OSQL allows you to enter Transact-SQL statements, system procedures, and script files. This utility uses ODBC to communicate with the server.
Once you have the OSQL command written, save the entire line into a file with a .bat extension.

Command Arguments:

-S (SQL Server\Instance name) Ex. -S nicsw5\SQL Express

-E (Uses a trusted connection instead of requesting a password)

-U (Login ID)

-P (Password)

-i (Input file) Ex. -i "c:\Folder\Script.sql"

-o (Output file) Ex. -o "c:\Folder\ScriptLog.txt"

Syntax :

Using Windows Authentication

osql -S nicsw5\SQL Express -E -i "c:\Script_file.sql" -o "c:\Script_file_log.txt"

Using SQL Authentication

osql -S nicsw5\SQL Express -U sql_user -P sql_password -i "c:\Script_file.sql" -o "c:\Script_file_log.txt"

Monday 24 September 2018

Could not load file or assembly 'Newtonsoft.Json, Version=4.5.0.0, Culture=neutral, PublicKeyToken=30ad4fe6b2a6aeed' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)

Please Use Following code in your web config just before </configuration> section

<runtime>

<assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">

<dependentAssembly>

<assemblyIdentity name="Newtonsoft.Json" publicKeyToken="30ad4fe6b2a6aeed" culture="neutral" />

<bindingRedirect oldVersion="0.0.0.0-7.0.0.0" newVersion="7.0.0.0" />

</dependentAssembly>

</assemblyBinding>

</runtime>

Sunday 16 September 2018

Convert C# Data table into JSON format. (VS2010, Newtonsoft JSON)

There are 3 Ways to Convert DataTable to JSON String in ASP.NET C#

Convert DataTable to JSON using StringBuilder.
Convert DataTable to JSON using JavaScriptSerializer.
Convert DataTable to JSON using JSON.Net DLL.

Method 1

Convert DataTable to JSON using StringBuilder.

This is how the JSON sample data looks: {"firstName":"Chandra", "lastName":"Kant"}.

JSON objects are written inside curly braces and can contain multiple name/values pairs.

So using StringBuilder we can create a similar JSON Structured String.

Since we are using StringBuilder we need to import the System.Text namespace in our page as in the following:

Using System.Text;

The following code will generate a JSON string. Here we are making a for loop over our DataTable rows and columns. Fetch the data (values) and append it to our JSONString StringBuilder. This is how our code looks:

public string DataTableToJSONWithStringBuilder(DataTable table)

{

var JSONString = new StringBuilder();

if (table.Rows.Count > 0)

{

JSONString.Append("[");

for (int i = 0; i < table.Rows.Count; i++)

{

JSONString.Append("{");

for (int j = 0; j < table.Columns.Count; j++)

{

if (j < table.Columns.Count - 1)

{

JSONString.Append("\"" + table.Columns[j].ColumnName.ToString() + "\":" + "\"" + table.Rows[i][j].ToString() + "\",");

}

else if (j == table.Columns.Count - 1)

{

JSONString.Append("\"" + table.Columns[j].ColumnName.ToString() + "\":" + "\"" + table.Rows[i][j].ToString() + "\"");

}

if (i == table.Rows.Count - 1)

{

JSONString.Append("}");

}

else

{

JSONString.Append("},");

}

JSONString.Append("]");

}

return JSONString.ToString();

}

-----------------------------------------------------------------------------------------------

Method 2

Convert the DataTable to JSON using JavaScriptSerializer.

 Since we are using JavaScriptSerializer we first need to import the System.Web.Script.Serialization namespace into our page as in the following code:

using System.Web.Script.Serialization;

The JavaScriptSerializer class is used internally by the asynchronous communication layer to serialize and deserialize the data.

 To serialize an object, use the Serialize method. To deserialize a JSON string, use the Deserialize or DeserializeObject methods.

Here we use the serialize method to get the JSON format data. So our code looks as in the following:

public string DataTableToJSONWithJavaScriptSerializer(DataTable table) 

{

    JavaScriptSerializer jsSerializer = new JavaScriptSerializer();  

    List < Dictionary < string, object >> parentRow = new List < Dictionary < string, object >> ();  

    Dictionary < string, object > childRow;  

    foreach(DataRow row in table.Rows) 

{

        childRow = new Dictionary < string, object > ();  

        foreach(DataColumn col in table.Columns) 

{

            childRow.Add(col.ColumnName, row[col]);  

}

        parentRow.Add(childRow);  

}

    return jsSerializer.Serialize(parentRow);  

}

-----------------------------------------------------------------------------------------------

Method 3

Convert DataTable to JSON using JSON.Net DLL (Newtonsoft).

Now in this method, we will convert our C# Datatable to JSON using the Newtonsoft DLL.

For this first, we need to download JSON.Net DLL. We can download it from Nuget.org and then import the Newtonsoft.JSON namespace into our page as in the following code. JSON.NET is a popular high-performance JSON framework for .NET.

using Newtonsoft.JSON;  

public string DataTableToJSONWithJSONNet(DataTable table)

{

string JSONString=string.Empty;

JSONString = JSONConvert.SerializeObject(table);

return JSONString;

}

Yes we are done with JSON,

 Newtonsoft JSON DLL

Monday 3 July 2017

VB6 Crashed when insert Crystal Report 8.5 in Windows 7 Error Code: C0000005.

Crystal Report 8.5

VB6: "Visual basic has stopped working"

I had been facing the same issue since long, and due to this i was not able to upgrade my laptop to Win7 or 8, finally i resolved the issue,

just go to the Root Directory where VB6 id installed,
select properties, and in compatibility tab select "Run this program in compatibility mode (Windows 7)
Run this program as an administrator

PIVOT and UNPIVOT SQL

PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output.

UNPIVOT performs the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.

The following is annotated syntax for PIVOT.

SELECT <non-pivoted column>,

[first pivoted column] AS <column name>,

[second pivoted column] AS <column name>,

...

[last pivoted column] AS <column name>

FROM

(<SELECT query that produces the data>)

AS <alias for the source query>

PIVOT

(

<aggregation function>(<column being aggregated>)

FOR

[<column that contains the values that will become column headers>]

IN ( [first pivoted column], [second pivoted column],

... [last pivoted column])

) AS <alias for the pivot table>

<optional ORDER BY clause>;

Example

Here is the result set.

DaysToManufacture AverageCost

0 5.0885

1 223.88

2 359.1082

4 949.4105

-- Pivot table with one row and five columns
SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days, 
[0], [1], [2], [3], [4]
FROM
(SELECT DaysToManufacture, StandardCost 
    FROM Production.Product) AS SourceTable
PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
) AS PivotTable;

Here is the result set.

Cost_Sorted_By_Production_Days 0 1 2 3 4

AverageCost 5.0885 223.88 359.1082 NULL 949.4105