Comma separated values in SQL Server

For comma seperated value



;WITH ctm
AS
(
SELECT id,id AS idlist ,VName [Name],0 AS [Status] FROM testTableData
WHERE fk_id = 0
UNION ALL
SELECT ct.id,tt.id AS idlist,tt.Vname [Name],1 AS [Status] FROM testTableData tt
INNER JOIN ctm AS ct ON ct.id = tt.FK_id
AND ct.[Status] = 0
)

SELECT * FROM ctm ORDER BY id,idlist,[Name]

Automate mail merge in C#

private void MailMerge()
{
# region local variables

Object oMissing = System.Reflection.Missing.Value;

Object oTrue = true;
Object oFalse = false;

Word.Application oWord = new Word.Application();
Word.Document oWordDoc = new Word.Document();


oWord.Visible = true;

// Word Mail Merge Template file
Object oTemplatePath = System.Windows.Forms.Application.StartupPath+ "\\Report.dot";

oWordDoc = oWord.Documents.Add(ref oTemplatePath, ref oMissing, ref oMissing, ref oMissing);

#endregion

foreach (Word.Field myMergeField in oWordDoc.Fields)
{

iTotalFields++;
Word.Range rngFieldCode = myMergeField.Code;
String fieldText = rngFieldCode.Text;

if (fieldText.StartsWith(" MERGEFIELD"))
{
// example: MERGEFIELD Name \* MERGEFORMAT
Int32 endMerge = fieldText.IndexOf("\\");
Int32 fieldNameLength = fieldText.Length - endMerge;
String fieldName = fieldText.Substring(11, endMerge - 11); // [Name]

fieldName = fieldName.Trim();

if (fieldName == "Name")
{
myMergeField.Select();
oWord.Selection.TypeText(txt_name.Text);
}

if (fieldName == "Address")
{
myMergeField.Select();
oWord.Selection.TypeText(txt_address.Text);
}

if (fieldName == "Age")
{
myMergeField.Select();
oWord.Selection.TypeText(num_age.Text);
}

if (fieldName == "EAddress")
{
myMergeField.Select();
oWord.Selection.TypeText(txt_email.Text);
}

if (fieldName == "Company")
{
myMergeField.Select();
oWord.Selection.TypeText(txt_company.Text);
}

if (fieldName == "TelNo")
{
myMergeField.Select();
oWord.Selection.TypeText(txt_tel.Text);
}

if (fieldName == "ODetails")
{
myMergeField.Select();
oWord.Selection.TypeText(txt_odetails.Text);
}
}
}

// Open File

}

Structure of the template document

Name: «Name»
Address: «Address»
Age: «Age»
E-mail Address: «EAddress»
Company: «Company»
Telephone Number: «TelNo»
Other Details: «ODetails»

Recursive Search for Folders and Files

public ClsFolderNFileCollection GetCollectionOfRecursiveDirAndFiles(string Src, List Elements)
{
ClsFolderNFileCollection objDirAndFileCollection = new ClsFolderNFileCollection();
string[] Files;

try
{

Files = Directory.GetFileSystemEntries(Src);
foreach (string Element in Files)
{
// Sub directories

if (Directory.Exists(Element))
{
objDirAndFileCollection.FolderCollection.Add(Element);
GetCollectionOfRecursiveDirAndFiles(Element, Elements);
}

else
{
objDirAndFileCollection.FileCollection.Add(Element);
}
}


}
catch (Exception ex)
{
throw new Exception(ex.Message);
}

return objDirAndFileCollection;
}


}

// Return Class

public class ClsFolderNFileCollection
{
public List FileCollection { get; set; }
public List FolderCollection { get; set; }

}

DateTime Stamp Functions

public static string DateTimeIn24Hours()
{
return DateTime.Now.ToString("yyyyMMddHHmm");
}

public static string DateTimeWithUniqueID()
{
string guid = Guid.NewGuid().ToString();

return DateTime.Now.ToString("yyyymmdd") + guid;
}

General Class for FTP Functions

class FTPFunctions
{
///

/// To Make Directory on the FTP-Server
///

/// public static void MakeDir(string dirName)
{
FtpWebRequest reqFTP = null;
Stream ftpStream = null;
try
{

reqFTP = (FtpWebRequest)FtpWebRequest.Create(new Uri("ftp://" + dirName));

reqFTP.Method = WebRequestMethods.Ftp.MakeDirectory;
reqFTP.UseBinary = true;
reqFTP.Credentials = new NetworkCredential(FTPSettings.UserID, FTPSettings.Password);
FtpWebResponse response = (FtpWebResponse)reqFTP.GetResponse();
ftpStream = response.GetResponseStream();
ftpStream.Close();
response.Close();
}
catch (Exception ex)
{
if (ftpStream != null)
{
ftpStream.Close();
ftpStream.Dispose();
}
throw new Exception(ex.Message.ToString());
}
}
///

/// To Check Whether Folder exists or not on the FTP-Server
///

/// /// /// /// ///
public static bool FtpDirectoryExists(string OurDir, string FTPDir, string FTPUser, string FTPPassword)
{
bool IsExists = true;
try
{
FtpWebRequest request;

// Create FtpWebRequest object from the Uri provided
if (!FTPDir.Contains("ftp://"))
{
request = (FtpWebRequest)FtpWebRequest.Create
(new Uri("ftp://" + FTPDir));
}
else
{
request = (FtpWebRequest)FtpWebRequest.Create
(new Uri(FTPDir));
}

// request = (FtpWebRequest)WebRequest.Create(FTPDir);
request.Credentials = new NetworkCredential(FTPUser, FTPPassword);
request.Method = WebRequestMethods.Ftp.PrintWorkingDirectory;

FtpWebResponse response = (FtpWebResponse)request.GetResponse();
}
catch (WebException ex)
{
IsExists = false;
}
return IsExists;
}

///

/// Upload file on the FTP-Server
///

/// /// /// /// ///
public static bool ftpfile(string inputfilepath, string ftpfilepath, string strFTPUser, string strFTPPwd)
{

try
{

FileInfo fileInf = new FileInfo(inputfilepath);

FtpWebRequest ftp;

// Create FtpWebRequest object from the Uri provided
if (!ftpfilepath.Contains("ftp://"))
{
ftp = (FtpWebRequest)FtpWebRequest.Create
(new Uri("ftp://" + ftpfilepath + "/" + fileInf.Name));
}
else
{
ftp = (FtpWebRequest)FtpWebRequest.Create
(new Uri(ftpfilepath + "/" + fileInf.Name));
}

ftp.Credentials = new NetworkCredential(strFTPUser, strFTPPwd);

ftp.KeepAlive = true;
ftp.UseBinary = true;
ftp.Method = WebRequestMethods.Ftp.UploadFile;
FileStream fs = File.OpenRead(inputfilepath);
byte[] buffer = new byte[fs.Length];
fs.Read(buffer, 0, buffer.Length);
fs.Close();
Stream ftpstream = ftp.GetRequestStream();
ftpstream.Write(buffer, 0, buffer.Length);
ftpstream.Close();

return true;

}
catch (Exception ex)
{
// WriteLog("Unable to upload zip file: '"+strFilename+"' - Excep: " + ex.Message);
return false;
}
}

///

/// Get MetaData of the FTP-File
///

/// /// /// /// /// ///
public static FTPFileMetaData GetMetaDataFTPFile(string ftpServerIP, string ftpUserID, string ftpPassword, string strFolderName, string strFileName)
{
FTPFileMetaData metaData = new FTPFileMetaData();
FtpWebRequest reqFTP;


long fileSize = 0;

if (!ftpServerIP.Contains("ftp://"))
{
reqFTP = (FtpWebRequest)FtpWebRequest.Create(new Uri("ftp://" +
ftpServerIP + "/" + strFolderName + "/" + strFileName));
}
else
{
reqFTP = (FtpWebRequest)FtpWebRequest.Create(new Uri(ftpServerIP +
"/" + strFolderName + "/" + strFileName));
}
reqFTP.Method = WebRequestMethods.Ftp.GetDateTimestamp;
reqFTP.UseBinary = true;
reqFTP.KeepAlive = false;
reqFTP.Credentials = new NetworkCredential(ftpUserID,
ftpPassword);
FtpWebResponse response;
try
{
response = (FtpWebResponse)reqFTP.GetResponse();

DateTime lastModified = response.LastModified;

metaData.FileName = strFileName;
metaData.ModifiedTime = lastModified;
Stream ftpStream = response.GetResponseStream();
fileSize = response.ContentLength;
metaData.FileLength = fileSize;
metaData.FileLength = GetFTPFileSize(ftpServerIP, ftpUserID, ftpPassword, strFolderName, strFileName);

return metaData;
}
catch (Exception ex)
{
// BLayer.WriteLog("Error in FTPModule for MetaData " + ex.ToString() + "-" + DateTime.Now);
return metaData;
}
}

///

/// Delete file from the FTP-Server
///

/// /// /// /// /// ///
public static bool DeleteFileFromFTPServer(string ftpServerIP, string ftpUserID, string ftpPassword, string strFolderName, string strFileName)
{



FtpWebRequest request;

try
{

if (!ftpServerIP.Contains("ftp://"))
{
request = (FtpWebRequest)FtpWebRequest.Create(new Uri("ftp://" +
ftpServerIP + "/" + strFolderName + "/" + strFileName));
}
else
{
request = (FtpWebRequest)FtpWebRequest.Create(new Uri(ftpServerIP +
"/" + strFolderName + "/" + strFileName));
}

request.Credentials = new NetworkCredential(ftpUserID,
ftpPassword);
request.Method = WebRequestMethods.Ftp.DeleteFile;

FtpWebResponse response = (FtpWebResponse)request.GetResponse();

response.Close();
return true;
}
catch (Exception ex)
{
return false;
}

}

///

/// Get File-Size
///

/// /// /// /// /// ///
public static long GetFTPFileSize(string ftpServerIP, string ftpUserID, string ftpPassword, string strFolderName, string strFileName)
{
FtpWebRequest reqFTP;
long fileSize = 0;
try
{
reqFTP = (FtpWebRequest)FtpWebRequest.Create(new Uri("ftp://" + ftpServerIP + "/" + strFolderName + "/" + strFileName));
reqFTP.Method = WebRequestMethods.Ftp.GetFileSize;
reqFTP.UseBinary = true;
reqFTP.KeepAlive = false;
reqFTP.Credentials = new NetworkCredential(ftpUserID, ftpPassword);
FtpWebResponse response = (FtpWebResponse)reqFTP.GetResponse();
Stream ftpStream = response.GetResponseStream();
fileSize = response.ContentLength;

ftpStream.Close();
response.Close();
}
catch (Exception ex)
{
// BLayer.WriteLog("Error in FTPModule for FileSize " + ex.ToString() + "-" + DateTime.Now);
}
return fileSize;
}

///

/// Download file from the FTP-Server
///

/// /// /// /// /// /// ///

public static bool DownloadFileFromFTPServer(string ftpServerIP, string ftpUserID, string ftpPassword, string strFolderName, string strFileName, string DestFolder)
{
FtpWebRequest reqFTP;

if (!ftpServerIP.Contains("ftp://"))
{
reqFTP = (FtpWebRequest)FtpWebRequest.Create(new Uri("ftp://" +
ftpServerIP + "/" + strFolderName + "/" + strFileName));


}
else
{
reqFTP = (FtpWebRequest)FtpWebRequest.Create(new Uri(ftpServerIP +
"/" + strFolderName + "/" + strFileName));



}

reqFTP.Method = WebRequestMethods.Ftp.DownloadFile;
reqFTP.UseBinary = true;
reqFTP.KeepAlive = false;
reqFTP.Credentials = new NetworkCredential(ftpUserID,
ftpPassword);
FtpWebResponse response = (FtpWebResponse)reqFTP.GetResponse();
Stream ftpStream = response.GetResponseStream();
long cl = response.ContentLength;
int bufferSize = 2048;
int readCount;
byte[] buffer = new byte[bufferSize];

readCount = ftpStream.Read(buffer, 0, bufferSize);
Directory.CreateDirectory(DestFolder);

FileStream outputStream = new FileStream(DestFolder +
Path.DirectorySeparatorChar + strFileName, FileMode.Create);

try
{

while (readCount > 0)
{
outputStream.Write(buffer, 0, readCount);
readCount = ftpStream.Read(buffer, 0, bufferSize);
}

ftpStream.Close();
outputStream.Close();
response.Close();

return true;
}
catch (Exception ex)
{
string errMsg = "There is problem in downloading file from: " + DestFolder + "\\" + strFileName;
errMsg = errMsg + ": " + ex.Message.ToString();

return false;
}
}

///

/// Get File-listing from the FTP-Server
///

/// /// /// /// ///
public static List GetFTPFileList(string ftpServerIP, string ftpUserID, string ftpPassword, string strFolderName)
{
string[] downloadFiles;
StringBuilder result = new StringBuilder();
FtpWebRequest reqFTP;
List FTPFiles = new List();
try
{
if (!ftpServerIP.Contains("ftp://"))
{
reqFTP = (FtpWebRequest)FtpWebRequest.Create(new Uri("ftp://" +
ftpServerIP + "/" + strFolderName));
}
else
{
reqFTP = (FtpWebRequest)FtpWebRequest.Create(new Uri(ftpServerIP +
"/" + strFolderName));
}
reqFTP.UseBinary = true;
reqFTP.KeepAlive = false;
reqFTP.Credentials = new NetworkCredential(ftpUserID, ftpPassword);
reqFTP.Method = WebRequestMethods.Ftp.ListDirectory;
WebResponse response = reqFTP.GetResponse();
StreamReader reader = new StreamReader(response.GetResponseStream());
//MessageBox.Show(reader.ReadToEnd());
string line = reader.ReadLine();
while (line != null)
{
result.Append(line);
result.Append("\n");
line = reader.ReadLine();
}
result.Remove(result.ToString().LastIndexOf('\n'), 1);
reader.Close();
response.Close();
//MessageBox.Show(response.StatusDescription);
downloadFiles = result.ToString().Split('\n');


foreach (string s in downloadFiles)
{
FTPFileMetaData data = GetMetaDataFTPFile(ftpServerIP, ftpUserID, ftpPassword, strFolderName, s);

// Here we requires only file name

FTPFiles.Add(data.FileName);


}

return FTPFiles;
}
catch (Exception ex)
{

return FTPFiles;
}
}

///

/// Get Folder List from the FTP-Server
///

/// /// /// /// ///
public static List GetDirectoryListingFromFTPServer(string ftpServerIP, string ftpUserID, string ftpPassword, string strFolderName)
{

FtpWebRequest request;

StringBuilder result = new StringBuilder();
List directories = new List();

try
{

if (!ftpServerIP.Contains("ftp://"))
{
request = (FtpWebRequest)FtpWebRequest.Create(new Uri("ftp://" +
ftpServerIP + "/" + strFolderName));
}
else
{
request = (FtpWebRequest)FtpWebRequest.Create(new Uri(ftpServerIP +
"/" + strFolderName));
}


request.Method = WebRequestMethods.Ftp.ListDirectoryDetails;
request.Credentials = new NetworkCredential(ftpUserID,
ftpPassword);


FtpWebResponse response = (FtpWebResponse)
request.GetResponse();

Stream responseStream = response.GetResponseStream();
StreamReader reader = new StreamReader(responseStream);
string line = reader.ReadLine();
while (line != null)
{
// Filtering folder names from the ListingString
if (line.StartsWith("drw-") && !(line.EndsWith(".")))
{
if (line.Contains(":"))
{
result.Append(line.Substring(line.LastIndexOf(":") + 4));
result.Append("\n");
}
}
line = reader.ReadLine();
}


directories.AddRange(result.ToString().Split(new string[] { "\n", "\r\n" }, StringSplitOptions.RemoveEmptyEntries));


reader.Close();
response.Close();

return directories;
}
catch (WebException e)
{
return directories;
}
}

///

/// Check Existence of the File on FTP-Server
///

/// /// /// ///
public bool IsFileExistsFTPServerURI(string fullFtpFilepath, string userName,string passWord)
{

bool exists = true;

var request =

(FtpWebRequest)WebRequest.Create(fullFtpFilepath);

request.Credentials =

new NetworkCredential(userName, passWord);

request.Method =

WebRequestMethods.Ftp.GetDateTimestamp;

try
{

FtpWebResponse response =

(FtpWebResponse)request.GetResponse();

}

catch (WebException ex)
{

FtpWebResponse response = (FtpWebResponse)ex.Response;

if (response.StatusCode ==

FtpStatusCode.ActionNotTakenFileUnavailable)
{

exists = false; //File does not exist

}

}

return exists;

}


}

Data Table Export to Excel with XSLT

Util Class:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Xml;
using System.Threading;
using System.Data;
using System.Xml.Xsl;
using System.Web;
using System.Xml.XPath;

namespace Auditor_Apps
{
class DataSetToExcelUtil
{


private string appType;
private HttpResponse response;

private void CreateStylesheet(XmlTextWriter writer, string[] sHeaders, string[] sFileds, ExportFormat FormatType)
{
try
{
string ns = "http://www.w3.org/1999/XSL/Transform";
writer.Formatting = Formatting.Indented;
writer.WriteStartDocument();
writer.WriteStartElement("xsl", "stylesheet", ns);
writer.WriteAttributeString("version", "1.0");
writer.WriteStartElement("xsl:output");
writer.WriteAttributeString("method", "text");
writer.WriteAttributeString("version", "4.0");
writer.WriteEndElement();
writer.WriteStartElement("xsl:template");
writer.WriteAttributeString("match", "/");
for (int i = 0; i < sHeaders.Length; i++) { writer.WriteString("\""); writer.WriteStartElement("xsl:value-of"); writer.WriteAttributeString("select", "'" + sHeaders[i] + "'"); writer.WriteEndElement(); writer.WriteString("\""); if (i != (sFileds.Length - 1)) { writer.WriteString((FormatType == ExportFormat.CSV) ? "," : "\t"); } } writer.WriteStartElement("xsl:for-each"); writer.WriteAttributeString("select", "Export/Values"); writer.WriteString("\r\n"); for (int j = 0; j < sFileds.Length; j++) { writer.WriteString("\""); writer.WriteStartElement("xsl:value-of"); writer.WriteAttributeString("select", sFileds[j]); writer.WriteEndElement(); writer.WriteString("\""); if (j != (sFileds.Length - 1)) { writer.WriteString((FormatType == ExportFormat.CSV) ? "," : "\t"); } } writer.WriteEndElement(); writer.WriteEndElement(); writer.WriteEndElement(); writer.WriteEndDocument(); } catch (Exception exception) { throw exception; } } public enum ExportFormat { CSV = 1, Excel = 2 } public enum AppType { WEB = 1, Winform = 2 } public void ExportDetails(DataTable DetailsTable, ExportFormat FormatType, AppType appType, string FileName) { try { if (DetailsTable.Rows.Count == 0) { throw new Exception("There are no details to export."); } DataSet dsExport = new DataSet("Export"); DataTable table = DetailsTable.Copy(); table.TableName = "Values"; dsExport.Tables.Add(table); string[] sHeaders = new string[table.Columns.Count]; string[] sFileds = new string[table.Columns.Count]; for (int i = 0; i < table.Columns.Count; i++) { sHeaders[i] = table.Columns[i].ColumnName; sFileds[i] = this.ReplaceSpclChars(table.Columns[i].ColumnName); } if (appType == AppType.WEB) { this.Export_with_XSLT_Web(dsExport, sHeaders, sFileds, FormatType, FileName); } else if (appType == AppType.Winform) { this.Export_with_XSLT_Windows(dsExport, sHeaders, sFileds, FormatType, FileName); } } catch (Exception exception) { throw exception; } } private void Export_with_XSLT_Web(DataSet dsExport, string[] sHeaders, string[] sFileds, ExportFormat FormatType, string FileName) { try { this.response.Clear(); this.response.Buffer = true; if (FormatType == ExportFormat.CSV) { this.response.ContentType = "text/csv"; this.response.AppendHeader("content-disposition", "attachment; filename=" + FileName); } else { this.response.ContentType = "application/vnd.ms-excel"; this.response.AppendHeader("content-disposition", "attachment; filename=" + FileName); } MemoryStream w = new MemoryStream(); XmlTextWriter writer = new XmlTextWriter(w, Encoding.UTF8); this.CreateStylesheet(writer, sHeaders, sFileds, FormatType); writer.Flush(); w.Seek(0L, SeekOrigin.Begin); XmlDataDocument document = new XmlDataDocument(dsExport); XslTransform transform = new XslTransform(); transform.Load(new XmlTextReader(w), null, null); StringWriter writer2 = new StringWriter(); transform.Transform((IXPathNavigable)document, null, (TextWriter)writer2, null); this.response.Write(writer2.ToString()); writer2.Close(); writer.Close(); w.Close(); this.response.End(); } catch (ThreadAbortException exception) { string message = exception.Message; } catch (Exception exception2) { throw exception2; } } public void ExportDetails(DataTable DetailsTable, int[] ColumnList, ExportFormat FormatType, AppType appType, string FileName) { try { if (DetailsTable.Rows.Count == 0) { throw new Exception("There are no details to export"); } DataSet dsExport = new DataSet("Export"); DataTable table = DetailsTable.Copy(); table.TableName = "Values"; dsExport.Tables.Add(table); if (ColumnList.Length > table.Columns.Count)
{
throw new Exception("ExportColumn List should not exceed Total Columns");
}
string[] sHeaders = new string[ColumnList.Length];
string[] sFileds = new string[ColumnList.Length];
for (int i = 0; i < ColumnList.Length; i++) { if ((ColumnList[i] < 0) || (ColumnList[i] >= table.Columns.Count))
{
throw new Exception("ExportColumn Number should not exceed Total Columns Range");
}
sHeaders[i] = table.Columns[ColumnList[i]].ColumnName;
sFileds[i] = this.ReplaceSpclChars(table.Columns[ColumnList[i]].ColumnName);
}
if (appType == AppType.WEB)
{
this.Export_with_XSLT_Web(dsExport, sHeaders, sFileds, FormatType, FileName);
}
else if (appType == AppType.Winform)
{
this.Export_with_XSLT_Windows(dsExport, sHeaders, sFileds, FormatType, FileName);
}
}
catch (Exception exception)
{
throw exception;
}
}
private void Export_with_XSLT_Windows(DataSet dsExport, string[] sHeaders, string[] sFileds, ExportFormat FormatType, string FileName)
{
try
{
MemoryStream w = new MemoryStream();
XmlTextWriter writer = new XmlTextWriter(w, Encoding.UTF8);
this.CreateStylesheet(writer, sHeaders, sFileds, FormatType);
writer.Flush();
w.Seek(0L, SeekOrigin.Begin);
XmlDataDocument document = new XmlDataDocument(dsExport);
XslTransform transform = new XslTransform();
transform.Load(new XmlTextReader(w), null, null);
StringWriter writer2 = new StringWriter();
transform.Transform((IXPathNavigable)document, null, (TextWriter)writer2, null);
StreamWriter writer3 = new StreamWriter(FileName);
writer3.WriteLine(writer2.ToString());
writer3.Close();
writer2.Close();
writer.Close();
w.Close();
}
catch (Exception exception)
{
throw exception;
}
}



private string ReplaceSpclChars(string fieldName)
{
fieldName = fieldName.Replace(" ", "_x0020_");
fieldName = fieldName.Replace("%", "_x0025_");
fieldName = fieldName.Replace("#", "_x0023_");
fieldName = fieldName.Replace("&", "_x0026_");
fieldName = fieldName.Replace("/", "_x002F_");
return fieldName;
}



}
}



Implementation code:

DataSetToExcelUtil ObjUtil = new DataSetToExcelUtil();

ObjUtil.ExportDetails(dt, DataSetToExcelUtil.ExportFormat.Excel, DataSetToExcelUtil.AppType.Winform, ExcelPath);

Get ASCII in C#

public static string GetASCIIValue(string strValue)
{
return Encoding.ASCII.GetString(
Encoding.Convert(
Encoding.UTF8,
Encoding.GetEncoding(
Encoding.ASCII.EncodingName,
new EncoderReplacementFallback(string.Empty),
new DecoderExceptionFallback()
),
Encoding.UTF8.GetBytes(strValue)
)
);

}



string s1 = FinancialMIS_Apps.StringManipulation.GetASCIIValue("Expression");
string s2 = FinancialMIS_Apps.StringManipulation.GetASCIIValue("Räksmörgås");