/// <summary>
/// Requires the Adventure Works Sample Database for SQL 2014: https://msftdbprodsamples.codeplex.com/releases/view/125550
/// </summary>
/// <returns></returns>
public static bool Setup()
{
if (SqlBuilder.DefaultConnection == null)
{
SqlBuilder.DefaultConnection = "Data Source=(localdb)\\ProjectsV12;Initial Catalog=TinyCrm;Integrated Security=True";
}
if (SqlBuilder.DefaultMetadata == null)
{
if (!File.Exists(_MetadataFileName))
{
MetadataDatabase mdb = SqlMetadataDatabase.FromConnection(SqlBuilder.DefaultConnection, true).BuildMetadata();
mdb.ToFile(_MetadataFileName);
SqlBuilder.DefaultMetadata = mdb;
}
else
{
SqlBuilder.DefaultMetadata = SerializationExtensions.FromFile(_MetadataFileName);
}
}
// General options for SqlBuilder
//string dir = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
//SqlBuilder.DefaultConnection = string.Format("Server=(localdb)\\ProjectsV12;Database=TinyCrm;Trusted_Connection=Yes", dir);
return(true);
}
public void GenerateMetadataForDatabase()
{
Guid g = StopWatch.Start();
SqlMetadataDatabase meta = SqlMetadataDatabase.FromConnection(SqlBuilder.DefaultConnection);
MetadataDatabase mdb = meta.BuildMetadata();
Console.WriteLine(StopWatch.Stop(g, StopWatch.WatchTypes.Seconds, "Metadata generated in {0}s"));
Console.WriteLine("Database contains {0} tables and a total of {1} columns", mdb.Tables.Count, mdb.Tables.Values.SelectMany(x => x.Columns).Count());
string FileName = System.IO.Path.Combine(System.IO.Path.GetTempPath(), System.IO.Path.GetRandomFileName() + ".json");
string FileName2 = System.IO.Path.Combine(System.IO.Path.GetTempPath(), System.IO.Path.GetRandomFileName() + ".bson");
g = StopWatch.Start();
mdb.ToFile(FileName);
Console.WriteLine("Metadata persisted as {0} in {1}ms", FileName, StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds));
g = StopWatch.Start();
mdb = SerializationExtensions.FromFile(FileName);
Console.WriteLine("Metadata read from file '{0}' in {1}ms", FileName, StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds));
g = StopWatch.Start();
SerializationExtensions.ToFile <MetadataDatabase>(mdb, FileName2, true, false, SerializerFormats.Bson);
Console.WriteLine("Metadata persisted as bson {0} in {1}ms", FileName2, StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds));
g = StopWatch.Start();
mdb = SerializationExtensions.FromFile <MetadataDatabase>(FileName2, SerializerFormats.Bson);
Console.WriteLine("Metadata read from file '{0}' in {1}ms", FileName2, StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds));
FileInfo fi = new FileInfo(FileName);
Console.WriteLine("The File {1} is {0:0.00}MB in size", (double)fi.Length / (double)(1024 * 1024), FileName);
fi = new FileInfo(FileName2);
Console.WriteLine("The File {1} is {0:0.00}MB in size", (double)fi.Length / (double)(1024 * 1024), FileName2);
File.Delete(FileName);
File.Delete(FileName2);
Assert.IsTrue(!File.Exists(FileName));
Assert.IsTrue(!File.Exists(FileName2));
}
public static void Initialize()
{
HttpServerUtility u = HttpContext.Current.Server;
SqlBuilder.DefaultMetadata = SerializationExtensions.FromFile(u.MapPath("~/Content/Metadata/TinyCrm.json"));
SqlBuilder.DefaultConnection = System.Configuration.ConfigurationManager.ConnectionStrings["TinyCrm"].ConnectionString;
SqlBuilder.DefaultCulture = System.Globalization.CultureInfo.GetCultureInfo(1030);
}
public void SelectAndSerializeDataRows()
{
SqlBuilder builder = SqlBuilder.Select(100)
.From("Account")
.AllColumns(false)
//.WhereNotExists("Contact").And("AccountID", SqlOperators.Equal, "AccountID")
//.EndExists().Builder.BaseTable()
.SubSelect("Contact", "AccountID", "AccountID", "c")
.AllColumns(false)
.Builder();
Console.WriteLine(builder.ToSql());
ResultTable result = builder.Execute();
string prefix = DateTime.Now.Ticks.ToString();
string path = Path.GetTempPath();
for (int i = 0; i < result.Count; i++)
{
SerializationExtensions.ToFile <RowData>(result[i], Path.Combine(path, string.Format("Row{0}@{1}.json", i + 1, prefix)));
}
ResultTable result2 = new ResultTable();
for (int i = 0; i < result.Count; i++)
{
result2.Add(SerializationExtensions.FromFile <RowData>(Path.Combine(path, string.Format("Row{0}@{1}.json", i + 1, prefix))));
}
Assert.IsTrue(result.Count == result2.Count, "Number of rows does not match");
result = new ResultTable(builder, 60, false);
string prefix2 = DateTime.Now.Ticks.ToString();
for (int i = 0; i < result.Count; i++)
{
SerializationExtensions.ToFile <RowData>(result[i], Path.Combine(path, string.Format("Row{0}@{1}.json", i + 1, prefix2)));
}
string[] files = Directory.EnumerateFiles(path, "Row*.json").ToArray();
foreach (string file in files)
{
File.Delete(file);
}
}
private MetadataDatabase FromCache()
{
if (UseCache == false)
{
return(null);
}
MetadataDatabase mdb = CacheMetadata(MetadataKey);
if (mdb != null)
{
return(mdb);
}
if (!string.IsNullOrEmpty(FileName))
{
mdb = SerializationExtensions.FromFile(FileName);
CacheMetadata(MetadataKey, mdb);
}
return(mdb);
}