Use of PIVOT in SQL

April 18, 2017 2 comments

SELECT * FROM (SELECT

CASE

WHEN ProductCode=’SH’ THEN ‘SH’

WHEN ProductCode =’SF’ AND InvestmentAccountID =-1 THEN ‘CFDINdex’

ELSE ‘SF’ END AS ProductCode

FROM Positions WHERE Id= <any Value>

)

AS data

PIVOT

(

COUNT(ProductCode)

FOR ProductCode IN(SH, SF) –Name of column that you want to your output column wise

) AS pvt

 

Advertisements
Categories: SQL

Expose WCF service with nettcp and JSON protocols

February 22, 2017 Leave a comment

Service Exposure

[ServiceContract]
public interface ICommandService
{
[WebInvoke(Method = “GET”, ResponseFormat = WebMessageFormat.Json, RequestFormat = WebMessageFormat.Json, UriTemplate = “HelloPing/{value}”)]
[OperationContract]
string HelloPing(string value);

[WebInvoke(Method = “GET”, ResponseFormat = WebMessageFormat.Json, RequestFormat = WebMessageFormat.Json, UriTemplate = “GetOrderDetail/{OrderId}”)]
[OperationContract]
List<OrderDetail>GetOrderDetail(string OrderId);

[OperationContract]
bool GetDataUsingDataContract(string XMLData);

// TODO: Add your service operations here
}

Configure your service with this configuration

<system.serviceModel>
<services>
<service name=”WCTServiceTest.CommandService”>
<host>
<baseAddresses>
<add baseAddress = “http://localhost:8733/Design_Time_Addresses/WCTServiceTest/CommandService/&#8221; />
<add baseAddress = “net.tcp://localhost:8730/Design_Time_Addresses/WCTServiceTest/CommandService/” />

</baseAddresses>
</host>
<!– Service Endpoints –>
<!– Unless fully qualified, address is relative to base address supplied above –>
<endpoint address=”” binding=”basicHttpBinding” contract=”WCTServiceTest.ICommandService”>
<identity>
<dns value=”localhost”/>
</identity>
</endpoint>
<endpoint address=”” binding=”netTcpBinding” contract=”WCTServiceTest.ICommandService”>
<identity>
<dns value=”localhost”/>
</identity>
</endpoint>
<endpoint address=”JSON” binding=”webHttpBinding” contract=”WCTServiceTest.ICommandService” bindingConfiguration=”webHttpBindingWithJson” behaviorConfiguration=”Web”>
<identity>
<dns value=”localhost”/>
</identity>
</endpoint>
<!– Metadata Endpoints –>
<!– The Metadata Exchange endpoint is used by the service to describe itself to clients. –>
<!– This endpoint does not use a secure binding and should be secured or removed before deployment –>
<endpoint address=”mex” binding=”mexHttpBinding” contract=”IMetadataExchange”/>
</service>
</services>
<bindings>
<webHttpBinding>
<binding name=”webHttpBindingWithJson” crossDomainScriptAccessEnabled=”true” />
</webHttpBinding>
</bindings>
<behaviors>
<serviceBehaviors>
<behavior>
<serviceMetadata httpGetEnabled=”True” httpsGetEnabled=”True”/>
<serviceDebug includeExceptionDetailInFaults=”False” />
</behavior>
</serviceBehaviors>
<endpointBehaviors>
<behavior name=”Web”>
<webHttp defaultBodyStyle=”Bare” />
</behavior>
<behavior name=”EndPointBehavior”>
<enableWebScript/>
</behavior>
</endpointBehaviors>
</behaviors>
</system.serviceModel>

use below C# code to call them in your code
NetTcp Calling

void CallNetTCPService()
{
System.ServiceModel.NetTcpBinding binding = new NetTcpBinding();
EndpointAddress address = new EndpointAddress(“net.tcp://localhost:8730/Design_Time_Addresses/WCTServiceTest/CommandService”);

ChannelFactory channelFactory = new System.ServiceModel.ChannelFactory(binding, address);
ICommandService _clientProxy = channelFactory.CreateChannel();
string x = _clientProxy.HelloPing(“SArvesh”);
List order = _clientProxy.GetOrderDetail(“100”);
}

JSON Calling (using System.Runtime.Serialization.Json;)

protected void CallJSONWCFService()
{
try
{
//Creating the object of WebClient class
//Creating the object of WebClient class
WebClient objClient = new WebClient();
//Calling GetJsonMessage() method to get result from WCF service using JSON end point
byte[] jsonData = objClient.DownloadData(new Uri(“http://localhost:8733/Design_Time_Addresses/WCTServiceTest/CommandService/JSON/GetOrderDetail/1&#8221;));
//Creating the object of Stream class
Stream stream = new MemoryStream(jsonData);
//Creating the object of DataContractJsonSerializer class
DataContractJsonSerializer objDCJS = new DataContractJsonSerializer(typeof(List<OrderDetail>));
//string data = objDCJS.ReadObject(stream).ToString();
List<OrderDetail> data = (List<OrderDetail>)objDCJS.ReadObject(stream);
}
catch (Exception ex)
{
}
}

Categories: WCF

BizTalk

BizTalk coming sooon……

Categories: BizTalk

Fire Custom event with custom EventArgs

Step 1- Create Custom EventArgs

public class ExtendedEventArgs : EventArgs
{
public string Message { get; set;}
public ExtendedEventArgs(string message)
{
Message = message;
}
}

Step 2 – Create Delegate to handle Which will accept this argument

public delegate int ExtendedEvent(object sender, ExtendedEventArgs e);

Step 3 – Create and attach this delegate to event

public event ExtendedEvent OnExtendEvent;

Step 4 – Attach a method to delgate

this.OnExtendEvent += ExtendedEventRaised;

Step 5 – Create handler method to get requried parameter in EventAgrs

private int ExtendedEventRaised(object sender, ExtendedEventArgs e)
{
return e.Message.Length;
}

Step 6 – Fire event on your condition

if (OnExtendEvent != null)
OnExtendEvent(null, new ExtendedEventArgs(“Saresh”));

Categories: .Net, C#

Compare two object and get changes made in their values

Here we can compare values of a DataRow of DataTable or compare two object of same class. It will return you name of properties with differance in string.

string GetUpdatedHistory(DataRow orignalRecord, DataRow updatedRecord)
{
if (orignalRecord.Table.Columns.Count != updatedRecord.Table.Columns.Count)
{
return string.Empty;
}
StringBuilder text = new StringBuilder();
for (int i = 0; i < orignalRecord.Table.Columns.Count; i++)
{
if (Convert.ToString(orignalRecord[i]) != Convert.ToString(updatedRecord[i]))
{
text.Append("Column " + orignalRecord.Table.Columns[i].ColumnName +
string.Format(" changed from to ,”, Convert.ToString(orignalRecord[i]), Convert.ToString(updatedRecord[i])));
}
}
return text.ToString().Substring(0, text.ToString().Length – 1);
}
string GetUpdatedHistory(object type1, object type2)
{
StringBuilder text = new StringBuilder();
PropertyInfo[] instance1 = type1.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);
PropertyInfo[] instance2 = type2.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);
// Display information for all properties.
foreach (var propInfo in instance1)
{
PropertyInfo propInfo2 = (PropertyInfo)instance2.Where(x => x.Name == propInfo.Name).FirstOrDefault();
string value1 = Convert.ToString(propInfo.GetValue(type1, null));
string value2 = Convert.ToString(propInfo2.GetValue(type2, null));
if (value1 != value2)
{
text.Append(“Property ” + propInfo.Name +
string.Format(” changed from to ,”, Convert.ToString(value1), Convert.ToString(value2)));
}
}
return text.ToString().Substring(0, text.ToString().Length – 1);
}

Categories: .Net, C#

String type to ENum (Generic)

October 21, 2014 Leave a comment

///

/// Allows the discovery of an enumeration value based on the EnumTextValueAttribute
///

/// The type of the enum to get the value for.
/// The text of the EnumTextValueAttribute.
/// boxed representation of the enum value

public static T GetEnumValue(string text)
{
Type enumType = typeof(T);

MemberInfo[] members = enumType.GetMembers();
foreach (MemberInfo mi in members)
{
object[] attrs = mi.GetCustomAttributes(typeof(EnumTextValueAttribute), false);
if (attrs.Length == 1)
{
if (((EnumTextValueAttribute)attrs[0]).Text == text)
return (T) Enum.Parse(enumType, mi.Name);
}
}
throw new ArgumentOutOfRangeException(“text”, text, “The text passed does not correspond to an attributed enum value”);
}

Categories: C#

Ageing procedure

October 9, 2014 Leave a comment

This procedure will return data in age range format.

DECLARE @StartDate DATETIME =’2009-1-1′
DECLARE @EndDate DATETIME=’2010-12-31′
DECLARE @Table TABLE([MONTH] Varchar(20),
[18-24] money,
[25-34] money,
[35-44] money,
[45-54] money,
[55-64] money,
[65+] MONEY,
[Total] money)
INSERT INTO @Table
SELECT CONVERT(VARCHAR(4),YEAR(o.dtApplication)) + ‘/’ + CONVERT(VARCHAR(2),MONTH(o.dtApplication)) AS [MONTH],
sum(case when DATEDIFF(yy, o.DOB, GETDATE()) BETWEEN 18 and 24 then 1 else 0 end) as ’18-24′,
sum(case when DATEDIFF(yy, o.DOB, GETDATE()) BETWEEN 25 and 34 then 1 else 0 end) as ’25-34′,
sum(case when DATEDIFF(yy, o.DOB, GETDATE()) BETWEEN 35 and 44 then 1 else 0 end) as ’35-44′,
sum(case when DATEDIFF(yy, o.DOB, GETDATE()) BETWEEN 45 and 54 then 1 else 0 end) as ’45-54′,
sum(case when DATEDIFF(yy, o.DOB, GETDATE()) BETWEEN 55 and 64 then 1 else 0 end) as ’55-64′,
sum(case when DATEDIFF(yy, o.DOB, GETDATE()) > 64 then 1 else 0 end) as ’65+’,
0 AS [Total]
FROM table o WITH(NOLOCK) where o.dtApplication between @StartDate and @EndDate
GROUP BY CONVERT(VARCHAR(4),YEAR(o.dtApplication)) + ‘/’ + CONVERT(VARCHAR(2),MONTH(o.dtApplication))
— Update total
UPDATE @Table SET [Total] = [18-24] + [25-34] + [35-44] + [45-54] + [55-64] + [65+]
–SELECT * from @Table
— Update average
UPDATE @Table
SET [18-24] =[18-24]/(Total/100),
[25-34] =[25-34]/(Total/100),
[35-44] =[35-44]/(Total/100),
[45-54] =[45-54]/(Total/100),
[55-64] =[55-64]/(Total/100),
[65+] =[65+]/(Total/100)
–Add Bancorp average
INSERT INTO @Table
VALUES ( ‘Average’,
(select AVG([18-24]) FROM @Table),
(select AVG([25-34]) FROM @Table),
(select AVG([35-44]) FROM @Table),
(select AVG([45-54]) FROM @Table),
(select AVG([55-64]) FROM @Table),
(select AVG([65+]) FROM @Table),
(select AVG([Total]) FROM @Table)
)
— Select all
SELECT * from @Table

Categories: SQL