public void SqlServerConnectionFactory_ReadProcedure_GetObjects()
{
var parameterFactory = new SqlServerParameterFactory();
var adapterFactory = new SqlServerDataAdapterFactory();
var connectionFactory = new SqlServerConnectionFactory();
using (var connection = connectionFactory.Open(AppState.ConnectionString))
using (var transaction = connection.BeginTransaction())
using (var proc = new ReadProcedure(connection, parameterFactory, adapterFactory))
{
var getParams = new Get
{
Id = 1
};
var readResult = proc.Read(getParams, transaction);
var tests = readResult.DataSet.Tables[0].Rows.ToArray <Test>();
Assert.IsNotNull(tests);
Assert.AreEqual(1, tests.Length);
var test = tests.FirstOrDefault();
Assert.IsNotNull(test);
Assert.AreEqual(1, test.Id);
Assert.AreEqual("one", test.Name);
transaction.Commit();
}
}
public void SqlServerConnectionFactory_ReadProcedure()
{
var parameterFactory = new SqlServerParameterFactory();
var adapterFactory = new SqlServerDataAdapterFactory();
var connectionFactory = new SqlServerConnectionFactory();
// on using transactions
// https://stackoverflow.com/questions/6418992/is-it-a-better-practice-to-explicitly-call-transaction-rollback-or-let-an-except
using (var connection = connectionFactory.Open(AppState.ConnectionString))
using (var transaction = connection.BeginTransaction())
using (var proc = new ReadProcedure(connection, parameterFactory, adapterFactory))
{
Assert.IsNotNull(proc);
var getParams = new Get
{
Id = 1
};
var readResult = proc.Read(getParams, transaction);
Assert.AreEqual(-1, readResult.ReturnValue);
Assert.AreEqual(1, readResult.DataSet.Tables.Count);
Assert.AreEqual(1, readResult.DataSet.Tables[0].Rows.Count);
Assert.AreEqual(1, getParams.Status);
Assert.AreEqual(1, readResult.DataSet.Tables[0].Rows[0]["Id"]);
Assert.AreEqual("one", readResult.DataSet.Tables[0].Rows[0]["Name"]);
transaction.Commit();
}
}
static void Main(string[] args)
{
IDataComponentFactory connectionFactory;
var entFactory = new EntityDefinitionFactory();
var codeGen = new CSharpCodeGen();
//Add your SQLServer Connection String as environment variable "TESTCONNSTRING"
connectionFactory = new SqlServerDataComponentFactory(Environment.GetEnvironmentVariable("TESTCONNSTRING"));
var prm = new UspMOBGetSyncData()
{
ClientId = "CAFEFRESH",
LastSync = DateTime.Now,
UserId = 44720,
PreviousSyncApiId = 0.0f
};
using (var proc = new ReadProcedure(connectionFactory))
{
var readResult = proc.Read(prm);
DataSet ds = readResult.DataSet;
var tables = new List <TableDefinition>();
var rows = ds.Tables[ds.Tables.Count - 1];
var manifest = ds.Tables[ds.Tables.Count - 1].Rows.ToArray <Models.Manifest>();
foreach (var manifestTbl in manifest)
{
var dataTable = ds.Tables[manifestTbl.table_index];
var outputTable = entFactory.ToTable(dataTable, manifestTbl.table_name);
var pkeyColumn = outputTable.Columns.Where(col => col.Name == manifestTbl.primary_key).FirstOrDefault();
if (pkeyColumn != null)
{
pkeyColumn.IsPrimaryKey = true;
}
tables.Add(outputTable);
}
System.IO.File.WriteAllText("L:\\foo.cs", codeGen.ToFile(tables, "GSP.X.Repository.Local", true));
}
Console.ReadKey();
}
public void SqlServerConnectionFactory_ReadProcedure()
{
var parameterFactory = new SqlServerParameterFactory();
var adapterFactory = new SqlServerDataAdapterFactory();
var connectionFactory = new SqlServerConnectionFactory();
using (var connection = connectionFactory.Open(AppState.ConnectionString))
using (var proc = new ReadProcedure(connection, parameterFactory, adapterFactory))
{
Assert.IsNotNull(proc);
var getParams = new Get();
var readResult = proc.Read(getParams);
Assert.AreEqual(99, readResult.ReturnValue);
Assert.AreEqual(1, readResult.DataSet.Tables.Count);
Assert.AreEqual(1, readResult.DataSet.Tables[0].Rows.Count);
Assert.AreEqual(33, getParams.Out);
Assert.AreEqual(1, readResult.DataSet.Tables[0].Rows[0]["TestId"]);
Assert.AreEqual("one", readResult.DataSet.Tables[0].Rows[0]["Name"]);
}
}
public SqlServerReadProcedureWithRetry(IDataComponentFactory factory, RetryOptions retryOptions)
{
var instance = new ReadProcedure(factory);
_proxy = RetryProxy.Create <IReadProcedure>(instance, retryOptions, new SqlServerTransientErrorTester());
}
public SqlServerReadProcedureWithRetry(DbConnection dbConnection, RetryOptions retryOptions)
{
var instance = new ReadProcedure(dbConnection, new SqlServerParameterFactory(), new SqlServerDataAdapterFactory());
_proxy = RetryProxy.Create <IReadProcedure>(instance, retryOptions, new SqlServerTransientErrorTester());
}
public void FullTest()
{
IDataComponentFactory connectionFactory;
IDataComponentFactory sqliteConnectionFactory;
var entFactory = new EntityFactory();
var outputFileName = OUTPUT_FILENAME;
var sqlLiteConnectionString = $"Data Source={outputFileName}";
if (System.IO.File.Exists(outputFileName))
{
// System.IO.File.Delete(outputFileName);
}
//Add your SQLServer Connection string as environment variable "TESTCONNSTRING"
connectionFactory = new SqlServerDataComponentFactory(Environment.GetEnvironmentVariable("TESTCONNSTRING"));
sqliteConnectionFactory = new SqliteDataComponentFactory(sqlLiteConnectionString);
var prm = new SyncProcParams()
{
ClientId = "CAFEFRESH",
LastSync = DateTime.Now,
UserId = 44720,
PreviousSyncApiId = 0.0f
};
using (var outputStream = new MemoryStream())
{
using (var proc = new ReadProcedure(connectionFactory))
{
var readResult = proc.Read(prm);
var ds = readResult.DataSet;
var rows = ds.Tables[ds.Tables.Count - 1];
var manifest = ds.Tables[ds.Tables.Count - 1].Rows.ToArray <Manifest>();
var binaryWriter = new BinaryWriter(outputStream);
var count = manifest.Length;
binaryWriter.Write(count);
foreach (var manifestTbl in manifest)
{
var dataTable = ds.Tables[manifestTbl.table_index];
var outputTable = entFactory.ToTable(dataTable, manifestTbl.table_name);
var pkeyColumn = outputTable.Columns.Where(col => col.Name == manifestTbl.primary_key).FirstOrDefault();
if (pkeyColumn != null)
{
pkeyColumn.IsPrimaryKey = true;
}
outputStream.Write(outputTable);
binaryWriter.Write(dataTable.Rows.Count);
binaryWriter.WriteToStream(outputTable, dataTable.Rows);
}
}
outputStream.Seek(0, SeekOrigin.Begin);
var recordCount = 0;
var sqliteConnection = sqliteConnectionFactory.Open();
var binaryReader = new BinaryReader(outputStream);
var tableCount = binaryReader.ReadInt32();
for (var idx = 0; idx < tableCount; ++idx)
{
var batchWriter = new BatchWriter();
var tbl = outputStream.ReadFromStream <Table>();
batchWriter.Init(tbl, sqliteConnection, truncate: true);
batchWriter.Begin();
var rowCount = binaryReader.ReadInt32();
for (var rowIdx = 0; rowIdx < rowCount; ++rowIdx)
{
var values = binaryReader.ReadRowFromStream(tbl);
batchWriter.Execute(values);
recordCount++;
/* Code to write the object array to SQLite in a transaction */
}
batchWriter.End();
}
}
}
public SqlServerReadProcedureWithRetry(IDataComponentFactory factory, SqlServerTransientRetryPolicy retryPolicy)
{
var instance = new ReadProcedure(factory);
_proxy = RetryProxy.Create <IReadProcedure>(instance, retryPolicy);
}
public SqlServerReadProcedureWithRetry(DbConnection dbConnection, SqlServerTransientRetryPolicy retryPolicy)
{
var instance = new ReadProcedure(dbConnection, new SqlServerParameterFactory(), new SqlServerDataAdapterFactory());
_proxy = RetryProxy.Create <IReadProcedure>(instance, retryPolicy);
}
