JSON Serialization and Deserialization

Found a good articale on JSON Serialization and Deserialization:

http://www.codeproject.com/Articles/272335/JSON-Serialization-and-Deserialization-in-ASP-NET

I used this article to create a class that serializes DataSet, DataTable and DataRow objects:

I just changed one aspect of the codeproject article, I used the regular expression "\\/Date\((-?\d+)\)\\/" instead of  “\\/Date\((\d+)\+\d+\)\\/”. The regular expression “\\/Date\((-?\d+)\)\\/” takes into account dates before epoch (1979-1-1).

/// <summary>
/// Contains JSON helper functions.
/// </summary>
public class JsonHelper
{
    /// <summary>
    /// Date time format, used to convert a datetime to a string.
    /// By default "yyyy-MM-dd HH:mm:ss".
    /// </summary>
    public string DateTimeFormat { get; set; }

    /// <summary>
    /// Initializes properties.
    /// </summary>
    public JsonHelper()
    {
        DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
    }

    /// <summary>
    /// Converts a DataSet to a JSON string.
    /// </summary>
    /// <param name="dataSet">The dataset to convert.</param>
    /// <exception cref="System.ArgumentException">Thrown when parameter [dataSet] is null.</exception>
    /// <returns>
    /// - JSON in the format [[[Table0Row0Column0,Table0Row0Column1],[Table0Row1Column0,Table0Row1Column1]],[[Table1Row0Column0,Table1Row0Column1],[Table1Row1Column0,Table1Row1Column1]]]
    /// - Empty string, when dataset contains no tables.
    /// - Empty string, when all tables contain no rows.
    /// </returns>
    public string ToJson(DataSet dataSet)
    {
        if (dataSet == null) { throw new ArgumentNullException("dataSet"); }

        StringBuilder result = new StringBuilder(string.Empty);
        if (dataSet.Tables.Count > 0)
        {
            result.Append("[");
            foreach (DataTable table in dataSet.Tables)
            {
                result.Append(ToJson(table));
            }
            result.Append("]");
        }
        return result.ToString();
    }

    /// <summary>
    /// Converts a DataTable to a JSON string.
    /// </summary>
    /// <param name="table">The dataset to convert.</param>
    /// <exception cref="System.ArgumentException">Thrown when parameter [table] is null.</exception>
    /// <returns>
    /// - JSON in the format JSON in the format [[Row0Column0,Row0Column1],[Row1Column0,Row1Column1]]
    /// - Empty string, when datatable contains no rows.
    /// </returns>
    public string ToJson(DataTable table)
    {
        if (table == null) { throw new ArgumentNullException("table"); }

        StringBuilder result = new StringBuilder(string.Empty);
        if (table.Rows.Count > 0)
        {
            result.Append("[");
            foreach (DataRow row in table.Rows)
            {
                result.Append(ToJson(row));
            }
            result.Append("]");
        }
        return result.ToString();
    }

    /// <summary>
    /// Converts a DataRow to a JSON string.
    /// </summary>
    /// <param name="row">The data row to convert.</param>
    /// <exception cref="System.ArgumentException">Thrown when parameter [row] is null.</exception>
    /// <exception cref="System.ArgumentException">Thrown when property [DateTimeFormat] is null, empty or contains only whitespaces.</exception>
    /// <returns>
    /// - JSON in the format [Row0Column0,Row0Column1].
    /// - Empty string, when datarow contains no columns.
    /// </returns>
    public string ToJson(DataRow row)
    {
        if (row == null) { throw new ArgumentNullException("row"); }
        if (string.IsNullOrWhiteSpace(DateTimeFormat)) { throw new ArgumentNullException("DateTimeFormat"); }

        StringBuilder result = new StringBuilder(string.Empty);
        if (row.ItemArray.Count() > 0)
        {
            var serializer = new JavaScriptSerializer();
            string json = serializer.Serialize(row.ItemArray);

            // Replace Date(...) by a string in the format found in the property [DateTimeFormat].
            var matchEvaluator = new MatchEvaluator(ConvertJsonDateToDateString);
            var regex = new Regex(@"\/Date\((-?\d+)\)\/");
            json = regex.Replace(json, matchEvaluator);

            result.Append(json);
        }
        return result.ToString();
    }

    /// <summary>
    /// Converts a JSON string to a object array.
    /// </summary>
    /// <param name="input">The input.</param>
    /// <exception cref="System.ArgumentException">Thrown when input is null.</exception>
    /// <returns></returns>
    public object[] FromJson(string input)
    {
        if (input == null) { throw new ArgumentNullException("input"); }
        var serializer = new JavaScriptSerializer();
        object[] result = serializer.Deserialize(input, typeof(object[])) as object[];

        return result;
    }

    /// <summary>
    /// Replace JSON dates, like "\/Date(1330740183000)\/" to a string in the format found in the property [DateTimeFormat].
    /// </summary>
    /// <param name="match">When null, throws exception</param>
    /// <exception cref="ArgumentNullException">Throws ArgumentNullException, when property [DateTimeFormat] is null, empty or contains only white spaces.</exception>
    /// <returns>A string in the format found in the property [DateTimeFormat]</returns>
    public string ConvertJsonDateToDateString(Match match)
    {
        if (match == null) { throw new ArgumentNullException("match"); }
        if (string.IsNullOrWhiteSpace(DateTimeFormat)) { throw new ArgumentNullException("DateTimeFormat"); }

        string result = string.Empty;
        DateTime dt = new DateTime(1970, 1, 1); // Epoch date, used by the JavaScriptSerializer to represent starting point of datetime in JSON.
        dt = dt.AddMilliseconds(long.Parse(match.Groups[1].Value));
        dt = dt.ToLocalTime();
        result = dt.ToString(DateTimeFormat);
        return result;
    }
}

 

Just an other tip on JSON, if you want to de-serialize a JSON string containing a object with field names, you can use:

string json = @"{Id:1,Barcode:""WWW12312345678""}";
var serializer = new JavaScriptSerializer();
var dict = serializer.Deserialize<Dictionary<string, string>>(json);
Console.WriteLine(dict["Barcode"]);

How to get table schema / structure information with T-SQL

If you want to get table schema / structure information with T-SQL, you have several options, the most commonly used by me are:

Using the system stored procedure sp_help

exec sp_help ‘<your table name>’

 

Using a custom query

-- Dump table schema / structure info
declare @tabelObjectId int
set @tabelObjectId = Object_ID(N'<Your table name>')

select
  'Column_name' = ac.name,
  'Type'        = type_name(ac.user_type_id),
  'Length'            = convert(int, ac.max_length),
  'Nullable'        = case when ac.is_nullable = 0 then 'No' else 'Yes' end,
  'Identity'    = case when ac.is_identity = 0 then 'No' else 'Yes' end,
  'PK'          = case when exists(
                    select 1 from sys.index_columns ic
                    inner join sys.columns c  on  ic.object_id = c.object_id
                                              and c.column_id = ic.column_id
                    where ic.object_id = @tabelObjectId and c.name = ac.name
                  ) then 'Yes' else 'No' end,
  'FK'          = case when exists(
                    select 1 from sys.foreign_key_columns fc
                    inner join sys.columns c  on  c.object_id = parent_object_id
                                              and c.column_id = fc.parent_column_id
                    where fc.parent_object_id = @tabelObjectId and c.name = ac.name
                  ) then 'Yes' else 'No' end
from sys.all_columns ac where ac.object_id = @tabelObjectId

Result for a table

 

Column_name Type Length Nullable Identity PK FK
Id int 4 No Yes Yes No
AddressId int 4 No No No Yes
Weekday smallint 2 No No No No
From time 5 Yes No No No
To time 5 Yes No No No
ResetDate date 3 Yes No No No

How to hide the command line window, when started from an other batch (*.cmd or *.bat) file.

If you want to start a batch file from an other batch file you can use the start command.

If you want to hide the command line window, that will be displayed by default, use the /B parameter.

 

Example

Test1.bat contents:

start /B /wait Test2.bat

exit

 

When you execute Test1.bat on the command line, the Test2.bat will be executed without showing a window.

(The Test1.bat will wait until Test2.bat is finished, because the /wait argument is supplied.)

How to insert the result of a stored procedure in a table variable

Found the solution at:

http://stackoverflow.com/questions/653714/how-to-select-into-temp-table-from-stored-procedure

The following T-SQL code executes the system stored procedure sp_helpfile and stores the result in the table variable @temp.

 

declare @temp table
(
    name varchar(255),
    field varchar(255),
    filename varchar(255),
    filegroup varchar(255),
    size varchar(255),
    maxsize varchar(255),
    growth varchar(255),
    usage varchar(255)
);
INSERT @temp  Exec sp_helpfile;
select * from @temp;