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 !



 

Bulk Insert C# DataTable into SQL Server

Here is the sample code to insert C# DataTable into SQL.

        public bool BulkInsertDataTable(string ConnectionString , DataTable dataTable)

        {
            bool isSuccuss;
            try
           {
                SqlConnection SqlConnectionObj = new SqlConnection( ConnectionString );
                SqlConnectionObj.Open();
                SqlBulkCopy bulkCopy = new SqlBulkCopy(SqlConnectionObj,
                                                         SqlBulkCopyOptions.TableLock
                                                       | SqlBulkCopyOptions.FireTriggers
                                                       | SqlBulkCopyOptions.UseInternalTransaction
                                                       , null);
                bulkCopy.DestinationTableName = "tableName";
                SQLUtil.SqlTableCreator sqlTabCre = new SQLUtil.SqlTableCreator(SqlConnectionObj);
                sqlTabCre.CreateFromDataTable(dataTable);
                bulkCopy.WriteToServer(dataTable);
                SqlConnectionObj.Close();
                isSuccuss = true;
            }
            catch (Exception ex)
            {
                isSuccuss = false;
                throw new Exception("Error when inserting table " + tableName + " into SQL using connection string '" + ConnectionString + "'. Error Details : " + ex.ToString() + ".");
            }
            return isSuccuss;
        }

The utility to create SQL Table (SqlTableCreator) can be found here.

Hope this saves you some time.

Happy coding.

Cheers
Adam

ASP.NET Web REST API Settings

I created a new ASP.NET Web API REST Service and I deployed it on the IIS Server. It didn't work.

I made sure that the .NET framework is correct.

Finally I found this link.

http://stackoverflow.com/questions/15622720/net-web-api-404-file-or-directory-not-found

As per the recommendation here, I downloaded the ASP.NET MVC Setup from the following Microsoft Link and installed it on the IIS Server.

http://www.microsoft.com/en-us/download/details.aspx?id=30683

Then it started working.

After few days, I installed Visual Studio 2015 and removed Visual Studio 2013 from my laptop.

In Visual Studio 2015, I created a brand new ASP.NET Web API REST Service and published on the same server. Once again , my application stopped working. At the same time, the old application which I published using Visual Studio 2013 was still working.

Then I started to compare the "Web.Config" file and I realized that Visual Studio 2015 was missing this parameter (runAllManagedModulesForAllRequests).

<system.webServer>

    <handlers>

      <remove name="ExtensionlessUrlHandler-Integrated-4.0" />

      <remove name="OPTIONSVerbHandler" />

      <remove name="TRACEVerbHandler" />

      <add name="ExtensionlessUrlHandler-Integrated-4.0" path="*." verb="*" type="System.Web.Handlers.TransferRequestHandler" preCondition="integratedMode,runtimeVersionv4.0" />

    </handlers>

    <modules runAllManagedModulesForAllRequests="true">

        <remove name="FormsAuthentication" />

    </modules>

  </system.webServer>

After adding this settings in the Web.Config, then the HTTP REST services started to work.

Hope these tips will save you some time.

Cheers
Adam