Use of PIVOT in SQL
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
Expose WCF service with nettcp and JSON protocols
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/” />
<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”));
//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)
{
}
}
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”));
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);
}
String type to ENum (Generic)
///
///
/// 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”);
}
Ageing procedure
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
Microsoft.Win32.SystemEvents in Winform
You can catch You machine logon\logoff event in .Net
using System.Text;
using System.Threading.Tasks;
using Microsoft.Win32;
using System.IO;
namespace WindowsFormsApplication1
{
public class SessionEvent
{
public SessionEvent()
{
SystemEvents.SessionSwitch += new SessionSwitchEventHandler(SystemEvents_SessionSwitch);
Microsoft.Win32.SystemEvents.SessionEnding += SystemEvents_SessionEnding;
}void SystemEvents_SessionEnding(object sender, SessionEndingEventArgs e)
{
string path = @”C:\Datafeed\Example.txt”;
if (!File.Exists(path))
{
File.Create(path);
TextWriter tw = new StreamWriter(path);
tw.WriteLine(e.Reason + ” ” + DateTime.Now.ToString());
tw.Close();
}
else if (File.Exists(path))
{
using (StreamWriter sw = File.AppendText(path))
{
sw.WriteLine(e.Reason + ” ” + DateTime.Now.ToString());
}
}
}~SessionEvent()
{
//Do this during application close to avoid handle leak
Microsoft.Win32.SystemEvents.SessionSwitch -= new SessionSwitchEventHandler(SystemEvents_SessionSwitch);
}private void SystemEvents_SessionSwitch(object sender, SessionSwitchEventArgs e)
{
if (e.Reason == SessionSwitchReason.SessionLogon ||
e.Reason == SessionSwitchReason.SessionLogoff ||
e.Reason == SessionSwitchReason.SessionLock ||
e.Reason == SessionSwitchReason.SessionUnlock)
{
string path = @”C:\Datafeed\Example.txt”;
if (!File.Exists(path))
{
File.Create(path);
TextWriter tw = new StreamWriter(path);
tw.WriteLine(e.Reason + ” ” + DateTime.Now.ToString());
tw.Close();
}
else if (File.Exists(path))
{
using (StreamWriter sw = File.AppendText(path))
{
sw.WriteLine(e.Reason + ” ” + DateTime.Now.ToString());
}
}
}}
}
}
Export DataGridView into Excel without using COM
This feature will allow to you export data into excel without using or refering any COM
static Boolean ExportToExcel(string FilePath, DataGridView dataGridView, out Exception ex)
{
ex = null;
string strFileNameWithPath = FilePath;
try
{
string connectionString = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” + strFileNameWithPath + “;Extended Properties=Excel 8.0”;
OleDbConnection objConnection = new OleDbConnection(connectionString);
objConnection.Open();
OleDbDataAdapter objAdapter = new OleDbDataAdapter();
//Dim objCommand As New OleDbCommand(“CREATE TABLE [Employees] ( EmployeeID int,LastName nvarchar(20),FirstName nvarchar(10),Title nvarchar(30))”, objConnection)
//generate command
string createText = “CREATE TABLE [” + dataGridView.Name + “] (“;
for (int i = 0; i <= dataGridView.Columns.Count – 1; i++)
{
createText += dataGridView.Columns[i].HeaderText +” varchar(255), “;
}
if (createText.EndsWith(“, “))
{
createText = createText.Substring(0, createText.Length – 2);
}
createText +=”)”;
OleDbCommand objCommand = new OleDbCommand(createText, objConnection);
objCommand.ExecuteNonQuery();
//Dim objExportCommand As New OleDbCommand(“INSERT INTO Employees (EmployeeID, LastName,FirstName,Title) VALUES (@EmployeeID,@LastName, @FirstName,@Title)”, objConnection)
string insertText = “INSERT INTO [” + dataGridView.Name + “] (“;
for (int i = 0; i <= dataGridView.Columns.Count – 1; i++)
{
insertText += dataGridView.Columns[i].HeaderText +”,”;
}
insertText = insertText.Substring(0, insertText.Length – 1) +
“)”;
//’ add parameter
for (int i = 0; i < dataGridView.Rows.Count; i++)
{
string commandInsert = “”;
for (int j = 0; j < dataGridView.Columns.Count; j++)
{
commandInsert +=”‘” + Convert.ToString(dataGridView.Rows[i].Cells[j].Value).Trim() + “‘,”;
}
if (commandInsert.EndsWith(“,”))
{
commandInsert = commandInsert.Substring(0, commandInsert.Length – 1);
}
commandInsert = insertText +” VALUES(” + commandInsert + “)”;
OleDbCommand objCommandInsert = new OleDbCommand(commandInsert, objConnection);
objCommandInsert.ExecuteNonQuery();
}
objConnection.Dispose();
return true;
}
catch (Exception exc)
{
ex = exc;
return false;
}
}
Sample how to use it.
saveFileDialog1.ShowDialog();
Exception ex;
ExportToExcel(saveFileDialog1.FileName, dataGridView1, out ex);
Print file without open it in c#
In this method you can put condition of file printing as per file type with attached executable
public static Boolean PrintFile(string fileName)
{
try
{
Process proc = new Process();
proc.StartInfo.WindowStyle = ProcessWindowStyle.Hidden;
proc.StartInfo.Verb = “print”;//Define location of adobe reader/command line
//switches to launch adobe in “print” mode
proc.StartInfo.FileName = “Your application executable path”;
proc.StartInfo.Arguments = String.Format(@”/p /h {0}”, fileName);
proc.StartInfo.UseShellExecute = false;
proc.StartInfo.CreateNoWindow = true;proc.Start();
proc.StartInfo.WindowStyle = ProcessWindowStyle.Hidden;
if (proc.HasExited == false)
{
proc.WaitForExit(10000);
}proc.EnableRaisingEvents = true;
proc.Close();
return true;
}
catch
{
return false;
}
Sarvesh's Blog 