C# Export Dataset To XML, ISNULL Exporting fix

Hi All, Now see what happen, when export Datate filled with a standard SQL query/stetement:
With this SQL Table:


CREATE TABLE [dbo].[TabExample](
    [FieldNvar] [nvarchar](10) NULL,
    [FieldInt] [int] NULL,
    [FieldDate] [datetime] NULL
   
) ON [PRIMARY]

GO

With this record #1:

 FieldNvar | FieldInt | FieldDate
 lwebcode  |   NULL   | 01/01/2017


with standard SQL to fill Dataset we can have some problem:

Dataset ds = ... "SELECT * FROM TabExample"

ds.WriteXml(sTargetXML);

//will return this XML:

<?xml version="1.0" standalone="true"?>
<NewDataSet>
    <Table>
        <FieldNvar>lwebcode</FieldNvar>
        <FieldDate>2017-01-01T00:00:00+01:00</FieldDate>
    </Table>
</NewDataSet>

FieldInt Was Not Write!

With this record #2:

 FieldNvar | FieldInt | FieldDate
    NULL   |    2     | 01/01/2017

with standard SQL to fill Dataset we can have some problem:

Dataset ds = ... "SELECT * FROM TabExample"

ds.WriteXml(sTargetXML);

//will return this XML:

<?xml version="1.0" standalone="true"?>
<NewDataSet>
    <Table>
        <FieldInt>2</FieldInt>
        <FieldDate>2017-01-01T00:00:00+01:00</FieldDate>
    </Table>
</NewDataSet>

FieldNvar Was Not Write!

With this record #3:

 FieldNvar | FieldInt | FieldDate
    NULL   |    2     | NULL


with standard SQL to fill Dataset we can have some problem:

Dataset ds = ... "SELECT * FROM TabExample"

ds.WriteXml(sTargetXML);

//will return this XML

<?xml version="1.0" standalone="true"?>
<NewDataSet>
    <Table>
        <FieldInt>2</FieldInt>
        <FieldDate>1900-01-01T00:00:00+01:00</FieldDate>
    </Table>
</NewDataSet>

If datetime has NULL value it will put firs day of datetime in xml
As in #2 FieldNvar Was Not Write!

To fix this issue, you have to edit SQL Statement using ISNULL Function:

Dataset ds = ... "SELECT ISNULL(FieldNvar, '') FieldNvar, ISNULL(FieldInt, '') FieldInt, ISNULL(FieldDate , '') FieldDate FROM TabExample"

with record #1, we have a error on converting Numeric Value to char, see later what to do.

with record #2
XML file will like:

<?xml version="1.0" standalone="true"?>
<NewDataSet>
    <Table>
        <FieldNvar/>
        <FieldInt>2</FieldInt>
        <FieldDate>2017-01-01T00:00:00+01:00</FieldDate>
    </Table>
</NewDataSet>

with record #3
XML file will like:

<?xml version="1.0" standalone="true"?>
<NewDataSet>
    <Table>
        <FieldNvar/>
        <FieldInt>2</FieldInt>
        <FieldDate/>
    </Table>
</NewDataSet>

with record #1 there is a last thing to do on all numeric value (int, decimal etc...), in SQL you neet to put ISNULL function with CAST our SQL query will Like:

Dataset ds = ... "SELECT ISNULL(FieldNvar, '') FieldNvar, CASE WHEN FieldInt IS NULL THEN '' ELSE CAST(FieldInt AS NVARCHAR(50)) END FieldInt, ISNULL(FieldDate , '') FieldDate FROM TabExample"

Now XML file will like:

<?xml version="1.0" standalone="true"?>
<NewDataSet>
    <Table>
        <FieldNvar>lwebcode</FieldNvar>
        <FieldInt/>
        <FieldDate>2017-01-01T00:00:00+01:00</FieldDate>
    </Table>
</NewDataSet>

With this fix in sql statement, we can export every kind of fields and be sure that each field will be write on xml file.

c# fast traslation of websites, Extract *.resx To *.xls

Hi All, Here a very quick Class which get all Entries to translate from resource *.resx file and make a Excel *.xls file with a list of all Entries.
Program writes entries in “A” column, 
In “B” (“C”,”D”…if you have more languages) Final user have to puts his language translation,
when done, after xls is saved and came back:
make a copy of *.resx file
With advanced text editor such as notepad++ or ultraedit after have make a copy of *.resx file, with “Find and Replace” tool I can easly traslate all the entries.
here the code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Xml;
using Excel; // Add refer to Excel (in COM object Microsoft Excel Library )
using System.IO;

//#LWEBCODE
//Website Building & free script at http://lwebcode.blogspot.com/

namespace LWEBCODE
{
    public partial class ResxToXls
    {
        public static void MyStart()
        {
        string sPath =@"\\server\folder\" // where resx files are
        string DestFile = "", Title = "", ResxFile = "";
        string[] SourceFiles = Directory.GetFiles(sPath);
        if (SourceFiles.Length > 0)
        {
            for (int i = 0; i < SourceFiles.Length; i++)
            {
                if (SourceFiles[i].EndsWith(".resx"))
                {
                ResxFile = SourceFiles[i];
                Title = PageNameASPX(ResxFile);
                DestFile = ResxFile.Substring(0, ResxFile.LastIndexOf(@"\") + 1) + Title + ".xls";

                ReadResx(ResxFile, DestFile);
                }
            }
        }
        }

        public static void ReadResx(string ResxPath, string XlsPath)
        {
        Excel.ApplicationClass excel = new ApplicationClass();
        XmlTextReader xTr = new XmlTextReader(ResxPath);
        string ris = "";
        bool bwrite = true;
            while (xTr.Read())
            {
                // Do some work here on the data.
                //Console.WriteLine(xTr.Name);
                switch (xTr.NodeType)
                {
                case XmlNodeType.Element: // The node is an element.
                Console.Write("");

                break;
                case XmlNodeType.Text: //Display the text in each element.
                Console.WriteLine(xTr.Value);

                if (bwrite)
                ris += xTr.Value + ";";
                break;
                case XmlNodeType.EndElement: //Display the end of the element.
                Console.Write("");

                if ((xTr.Name.ToLower() == "comment") || (xTr.Name.ToLower() == "resheader"))
                bwrite = true;
                break;
                }
            }
        Console.Write(ris);
        Excel.Workbook workbook = excel.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
        Excel.Sheets sheets = workbook.Worksheets;

        excel.Visible = true;
        Excel.Worksheet mySheet = (Excel.Worksheet)sheets.get_Item(1);

        string[] splRis = ris.Split(Convert.ToChar(";"));
        int ExRow = 0;
        for (int j = 1; j < splRis.Length; j++)
        {
            ExRow = j + 4;
            Excel.Range myCell = (Excel.Range)mySheet.get_Range("A" + ExRow.ToString(), "A" + ExRow.ToString());
            myCell.Value = splRis[j - 1];
        }
        workbook.SaveAs(XlsPath, Excel.XlFileFormat.xlExcel9795, null, null, false, false, XlSaveAsAccessMode.xlNoChange, null, null, null, null);
        excel.Quit();
        }

        public static string PageNameASPX(string ResxPath)
        {
        return ResxPath.Substring(ResxPath.LastIndexOf(@"\") + 1).Replace(".resx","");
        }
    }
}

SQL - Fast Table Copy

Hi All, this script is very easy,a simple and fast solution to copy (structure and data) a table :

SELECT * INTO Table_Destination_Name FROM Table_Source_Name

Warning:Using this script Index aren't copied in new table (Table_Destination_Name) for adds it you have to add it manually.