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;