Find Distinct Column Values in C# DataTable

 Here is the code snippet to find the Distinct Column Values from a C# DataTable.


   DataView tempView = new DataView(tableInfo);
   DataTable distinctValues = tempView.ToTable(true, "ColumnName"); 


Happy Coding !

Convert C# SQL Parameters list to C# DataTable

Here is the code snippet to convert C# SQL parameters list to C# DataTable.

Happy Coding.

        private DataTable GetDataTableFromParamResults(List<SqlParameter> sqlParameterCollection)

        {

            DataTable paramResultsTable = new DataTable("ParamResultsTable");

            foreach(SqlParameter eachParam in sqlParameterCollection)

            {

                DataColumn newCol = newDataColumn(eachParam.ParameterName.TrimStart(new char[] { '@' }), GetDBType( eachParam.DbType) );

                paramResultsTable.Columns.Add(newCol);

            }

            DataRow newRow = paramResultsTable.NewRow();

            foreach (SqlParameter eachParam in sqlParameterCollection)

            {

                string colName = eachParam.ParameterName.TrimStart(new char[] { '@' });

                newRow[colName] = eachParam.Value;

            }

            paramResultsTable.Rows.Add(newRow);

 

            return paramResultsTable;

        }

 

        private Type GetDBType(DbType inDbType)

        {

            Type togoType = null;

            switch (inDbType)

            {

                case DbType.AnsiString:

                    togoType = Type.GetType("System.String");

                    break;

                case DbType.Binary:

                    togoType = Type.GetType("System.Byte");

                    break;

                case DbType.Byte:

                    togoType = Type.GetType("System.Byte");

                    break;

                case DbType.Boolean:

                    togoType = Type.GetType("System.Boolean");

                    break;

                case DbType.Currency:

                    togoType = Type.GetType("System.Decimal");

                    break;

                case DbType.Date:

                    togoType = Type.GetType("System.DateTime");

                    break;

                case DbType.DateTime:

                    togoType = Type.GetType("System.DateTime");

                    break;

                case DbType.Decimal:

                    togoType = Type.GetType("System.Decimal");

                    break;

                case DbType.Double:

                    togoType = Type.GetType("System.Double");

                    break;

                case DbType.Guid:

                    togoType = Type.GetType("System.Guid");

                    break;

                case DbType.Int16:

                    togoType = Type.GetType("System.Int16");

                    break;

                case DbType.Int32:

                    togoType = Type.GetType("System.Int32");

                    break;

                case DbType.Int64:

                    togoType = Type.GetType("System.Int64");

                    break;

                case DbType.Object:

                    togoType = Type.GetType("System.Object");

                    break;

                case DbType.SByte:

                    togoType = Type.GetType("System.SByte");

                    break;

                case DbType.Single:

                    togoType = Type.GetType("System.Single");

                    break;

                case DbType.String:

                    togoType = Type.GetType("System.String");

                    break;

                case DbType.Time:

                    togoType = Type.GetType("System.DateTime");

                    break;

                case DbType.UInt16:

                    togoType = Type.GetType("System.UInt16");

                    break;

                case DbType.UInt32:

                    togoType = Type.GetType("System.UInt32");

                    break;

                case DbType.UInt64:

                    togoType = Type.GetType("System.UInt64");

                    break;

                case DbType.VarNumeric:

                    togoType = Type.GetType("System.Decimal");

                    break;

                case DbType.AnsiStringFixedLength:

                    togoType = Type.GetType("System.String");

                    break;

                case DbType.StringFixedLength:

                    togoType = Type.GetType("System.String");

                    break;

                case DbType.Xml:

                    togoType = Type.GetType("System.String");

                    break;

                case DbType.DateTime2:

                    togoType = Type.GetType("System.DateTime");

                    break;

                case DbType.DateTimeOffset:

                    togoType = Type.GetType("System.DateTimeOffset");

                    break;

                default:

                    throw new Exception("Unable to convert the type  " + inDbType.ToString() +".");

            }

            return togoType;

        }

 

Convert DataTable to C# Class

Here is the code snippet to convert a C# DataTable to an existing C# class. If the column name matches with C# field name, then the class object will be created and pushed out.

public static UserInfo GetUserInfoFromDataTable(DataTable userInfoTable)

        {
  if (userInfoTable.Rows.Count == 0) throw new Exception("No UserInfo records found.");

            if (userInfoTable.Rows.Count > 1) throw new Exception("More than one UserInfo records found.");

            DataRow oneRow = userInfoTable.Rows[0];

            UserInfo togoUserInfo = new UserInfo();

            var props = typeof(UserInfo).GetProperties();

            foreach (var prop in props)

            {
  string propName = prop.Name;
 if (oneRow.Table.Columns.Contains(propName))

                {

                    object propNewValue = oneRow[propName];

                    prop.SetValue(togoUserInfo, propNewValue);

                }

            }

            return togoUserInfo;

        }

Happy Coding.

Cheers
Adam

Log4Net not logging in SQL Server

I was using Log4Net for one of my projects. The idea is to log everything into SQL. For some reason it was not logging. Finally after checking all these items, I was able to make it work. Hope this checklist will help you as well.

 

1. Copy the SQL config files from this example link.

 

2. Check the SQL Database connection string in the config file. Make sure that the Database Name is correct.

 

3. Check the SQL command in the config file. Make sure the table name is right.

 

4. Check the SQL Table in SQL and make sure it has the "Identity Column." Usually for the Id column the "Identity" should be enabled to true.

 

5. Turn on the SQL Server Profiler and examine the command coming into the SQL server.

 

6. If nothing is coming into SQL, then probably the Log4Net configuration file is not loaded. Try to load the configuration file programmatically.

 

Good Luck.

 

Cheers

Adam

 

Build HTML Email from SQL Table content.

There was a need to create HTML table email body from the contents in the SQL table.

Here is the code snippet to do it.

 

 

Set @body = select

                                         AgencyID AS [TD]

                                    , Name AS [TD]

                                    , IsClient AS [TD]

                           from data.Agencies

                           For XML raw('tr'), Elements

 

I must give credit for the following site which provided this idea and good explanation.

 

http://www.sqlservercentral.com/blogs/robert_davis/2010/06/15/Building-HTML-Emails-With-SQL-Server-and-XML/

 

Happy Coding.

 

Cheers

Adam

 

 

Create HTML content using XML and XSLT in memory

There was a need to create HTML content from XML and XSLT.

Also only the XSLT file will be loaded from the disk. The XML and HTML should be created in memory and returned as a string.

Here is the code snippet on how to achieve this.

Happy coding.

Cheers
Adam

#region Create HTML File

                #region Generate XML String in memory based on the C# class object

                string xmlMemString = string.Empty;
                using (MemoryStream memStreamXml = new MemoryStream())
                {
                    System.Xml.XmlWriterSettings xmlWriterSettings = new System.Xml.XmlWriterSettings();
                   xmlWriterSettings.Encoding = System.Text.Encoding.UTF8;
                    System.Xml.XmlWriter xmlWriter = System.Xml.XmlWriter.Create(memStreamXml, xmlWriterSettings);
                    using (xmlWriter)
                    {
                        System.Xml.Serialization.XmlSerializer xmlSer = new System.Xml.Serialization.XmlSerializer(typeof(CSharpClass));
// This cSharpClassObject contains the XML data which needs to be serialized
                        xmlSer.Serialize(xmlWriter, cSharpClassObject);
                    }
                    memStreamXml.Seek(0, SeekOrigin.Begin);
                    StreamReader xmlMemReader = new StreamReader(memStreamXml);
                    xmlMemString = xmlMemReader.ReadToEnd();
                    xmlMemReader.Close();
                }
                #endregion

                #region Read XML file and convert to HTML

                System.Xml.XmlDocument xdoc = new System.Xml.XmlDocument();
                xdoc.LoadXml(xmlMemString);
                // load xslt to do transformation
                System.Xml.Xsl.XslCompiledTransform xsl = new System.Xml.Xsl.XslCompiledTransform();
                xsl.Load(xslFileName); // xslFileName is the XSLT style sheet with full path. This contains the instructions to convert the XML content into HTML.

               // load xslt arguments to load specific page from xml file
                // this can be used if you have multiple pages
                // in your xml file and you loading them one at a time
              System.Xml.Xsl.XsltArgumentList xslarg = new System.Xml.Xsl.XsltArgumentList();
                MemoryStream htmlMemStream = new MemoryStream();
                xsl.Transform(xdoc, xslarg, htmlMemStream);
                htmlMemStream.Flush();
                reportHtml = System.Text.Encoding.UTF8.GetString(htmlMemStream.ToArray());
                #endregion

#endregion

 

List of C# class objects to .NET DataTable

Here is the code snippet to convert a List of C# Objects to DataTable.

Enjoy.

Happy Coding.

Cheers
Adam



public DataTable GetDataTable<T>(IList<T> list, string tableName)

        {

            PropertyDescriptorCollection props = TypeDescriptor.GetProperties(typeof(T));

            DataTable table = new DataTable(tableName);

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

            {

                PropertyDescriptor prop = props[i];

                table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);

            }

            object[] values = new object[props.Count];

            foreach (T item in list)

            {

                for (int i = 0; i < values.Length; i++)

                    values[i] = props[i].GetValue(item) ?? DBNull.Value;

                table.Rows.Add(values);

            }

            return table;

        }

Using enumerations in C#

Let’s assume we have an enumeration like shown below. I have the integer value of the enumeration and I want to get the enumeration type.

public enum DataTypeCodes

    {
        DATATYPE_1 = 11,
        DATATYPE_2 = 12,
        DATATYPE_3 = 23,
        DATATYPE_4 = 34,
        DATATYPE_5 = 45,
        DATATYPE_6 = 56,
        DATATYPE_7 = 67,
        DATATYPE_8 = 78
    }

Here is the code snippet to achieve this.

Int value = 23;
DataTypeCodes dt = (DataTypeCodes) value;

This should give me 'DATATYPE_3'.

Cheers
Adam

 

 

 

 

Insert XSL Stylesheet reference in the XML document using C# program

Here is the code snippet to insert the XSL style sheet reference in the XML document.

Thanks
Adam

XmlDocument xmlDoc = new XmlDocument();
using (MemoryStream memStreamXml = new MemoryStream())
{
     XmlWriterSettings xmlWriterSettings = new XmlWriterSettings();
     xmlWriterSettings.Encoding = Encoding.UTF8;
     XmlWriter xmlWriter = XmlWriter.Create(memStreamXml, xmlWriterSettings);
     xmlSer = new XmlSerializer(typeof(SomeClass));
     xmlSer.Serialize(xmlWriter, SomeClassObject);
     memStreamXml.Seek(0, SeekOrigin.Begin);
     xmlDoc.Load(memStreamXml);
}
// Now add the stylesheet processing
// instruction to the XML document

XmlProcessingInstruction newPI;
String PItext = string.Format("type='text/xsl' href='{0}'", xslFileName);
newPI = xmlDoc.CreateProcessingInstruction("xml-stylesheet", PItext);
xmlDoc.InsertAfter(newPI, xmlDoc.FirstChild);

// Now write the document
// out to the final output stream
using (StreamWriter streamWriter = new StreamWriter(xmlFileName, false, Encoding.UTF8))
{
    using (XmlWriter xmlWriter = XmlWriter.Create(streamWriter)
    {
       xmlDoc.WriteTo(xmlWriter);
    }
}

XML Document SelectNodes will not give results

 
When I tried to select nodes from the sample XML document shown below, I was not getting any results.
 
The reason is the "namespace".
 
When I select the nodes, I must use the right "namespace".
 
Here is the sample code which can add namespace first and then perform "SelectNodes" operation.
 
 

XmlDocument doc = new XmlDocument();

doc.Load(xmlFileName);
XmlNode docElement = doc.DocumentElement as XmlNode;
XmlNamespaceManager nsman = new XmlNamespaceManager(doc.NameTable);
nsman.AddNamespace("a", docElement.NamespaceURI);
XmlNodeList nodes = docElement.SelectNodes("a:Details/a:Section",nsman);

foreach (XmlNode node in nodes)
{
        // use node variable here.
}

Cheers
Happy Coding !