public DataTable Read()
{
var sqlType = FindSqlType(_providerName);
var originSql = new SqlWriter(_databaseTable, sqlType);
var selectAll = originSql.SelectPageSql();
var dt = new DataTable(_databaseTable.Name)
{
Locale = CultureInfo.InvariantCulture
};
var dbFactory = DbProviderFactories.GetFactory(_providerName);
using (var con = dbFactory.CreateConnection())
{
con.ConnectionString = _connectionString;
using (var cmd = con.CreateCommand())
{
BuildCommand(cmd, selectAll, sqlType);
using (var da = dbFactory.CreateDataAdapter())
{
da.SelectCommand = cmd;
da.Fill(dt);
}
}
}
return(dt);
}
public DataTable Read(DbConnection connection)
{
if (connection == null)
{
throw new ArgumentNullException(nameof(connection));
}
if (string.IsNullOrEmpty(connection.ConnectionString))
{
throw new ArgumentException("Connection has no connection string");
}
var providerName = _providerName;
if (string.IsNullOrEmpty(providerName))
{
providerName = connection.GetType().Namespace;
}
var sqlType = FindSqlType(providerName);
#if NETSTANDARD2_0
var dbFactory = FactoryFinder.FindFactory(connection);
#else
var dbFactory = DbProviderFactories.GetFactory(providerName);
#endif
var originSql = new SqlWriter(_databaseTable, sqlType);
var selectAll = originSql.SelectPageSql();
var dt = new DataTable(_databaseTable.Name)
{
Locale = CultureInfo.InvariantCulture
};
if (dbFactory == null)
{
Console.WriteLine("Cannot find the DbProviderFactory (provider not netstandard 2.0?)");
return(dt);
}
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
using (var cmd = connection.CreateCommand())
{
BuildCommand(cmd, selectAll, sqlType);
using (var da = dbFactory.CreateDataAdapter())
{
da.SelectCommand = cmd;
da.Fill(dt);
}
}
return(dt);
}
public void BuildTableSelectPaged(DatabaseTable databaseTable)
{
var sqlWriter = new SqlWriter(databaseTable, _sqlType);
try
{
var txt = sqlWriter.SelectPageSql();
Clipboard.SetText(txt, TextDataFormat.UnicodeText);
}
catch (Exception exception)
{
Debug.WriteLine(exception.Message);
}
}
public string BuildTableSelectPaged(DatabaseTable databaseTable)
{
StringBuilder sb = new StringBuilder();
var sqlWriter = new SqlWriter(databaseTable, m_sqlType);
try
{
sb.Append(sqlWriter.SelectPageSql());
}
catch (Exception exception)
{
Debug.WriteLine(exception.Message);
}
return(sb.ToString());
}
public DataTable Read()
{
var sqlType = FindSqlType();
var originSql = new SqlWriter(_databaseTable, sqlType);
var selectAll = originSql.SelectPageSql();
var dt = new DataTable(_databaseTable.Name)
{
Locale = CultureInfo.InvariantCulture
};
var dbFactory = DbProviderFactories.GetFactory(_providerName);
using (var con = dbFactory.CreateConnection())
{
con.ConnectionString = _connectionString;
using (var cmd = con.CreateCommand())
{
cmd.CommandText = selectAll;
cmd.CommandTimeout = 6000;
var p = cmd.CreateParameter();
var parameterName = "currentPage";
if (sqlType == SqlType.SqlServerCe)
{
parameterName = "offset";
}
p.ParameterName = parameterName;
p.Value = 1;
if (sqlType == SqlType.SqlServerCe)
{
p.Value = 0;
}
cmd.Parameters.Add(p);
var ps = cmd.CreateParameter();
ps.ParameterName = "pageSize";
ps.Value = _pageSize;
cmd.Parameters.Add(ps);
using (var da = dbFactory.CreateDataAdapter())
{
da.SelectCommand = cmd;
da.Fill(dt);
}
}
}
return(dt);
}
private void SelectPaged()
{
string procName = Prefix + "GETPAGED" + Suffix;
string fileName = procName + ".sql";
var writer = CreateProcedureWriter(procName);
writer.AddParameter(ParameterName("currentPage"), ColumnDataType("INT"));
writer.AddParameter(ParameterName("pageSize"), ColumnDataType("INT"));
writer.AddOutputParameter(ParameterName("total"), ColumnDataType("INT"));
writer.BeginProcedure(true);
//returns two result sets- paged, and count of total
writer.AddQuerySql(SqlWriter.SelectPageSql());
writer.AddSql(SqlWriter.CountSql(ParameterName("total")));
var txt = writer.End();
WriteFile(fileName, txt);
WriteSignature(writer.Signature());
}
public void TestGeneratedSqlForPaging()
{
var table = LoadCategoriesFromNorthwind();
//arrange
var writer = new SqlWriter(table, SqlType.SqlServerCe);
var sql = writer.SelectPageSql(); //sane as writer.SelectPageStartToEndRowSql()
//parameters are offset and pageSize, not the standard ones (limitations of OFFSET/FETCH in sqlserverCe)
var dataTable = new DataTable();
//run generated sql
using (var con = _factory.CreateConnection())
{
con.ConnectionString = _connectionString;
using (var cmd = con.CreateCommand())
{
cmd.CommandText = sql;
var pageSize = cmd.CreateParameter();
pageSize.ParameterName = writer.ParameterName("pageSize");
pageSize.Value = 2;
cmd.Parameters.Add(pageSize);
var currentPage = cmd.CreateParameter();
currentPage.ParameterName = writer.ParameterName("offset");
currentPage.Value = 2;
cmd.Parameters.Add(currentPage);
var da = _factory.CreateDataAdapter();
da.SelectCommand = cmd;
da.Fill(dataTable);
}
}
//assert
Assert.IsTrue(dataTable.Rows.Count > 0, "There should be some categories (this test may fail if database table is empty)");
Assert.IsTrue(dataTable.Rows.Count <= 4, "Should only return the page size (or less)");
foreach (var column in table.Columns)
{
var name = column.Name;
Assert.IsTrue(dataTable.Columns.Contains(name), "Should retrieve column " + name);
}
}
public void RunPagingSql()
{
//arrange
var writer = new SqlWriter(_table, _sqlType);
var sql = writer.SelectPageSql();
var dataTable = new DataTable();
Console.WriteLine(sql);
//run generated sql
using (var con = _factory.CreateConnection())
{
con.ConnectionString = _connectionString;
using (var cmd = con.CreateCommand())
{
cmd.CommandText = sql;
var pageSize = cmd.CreateParameter();
pageSize.ParameterName = writer.ParameterName("pageSize");
pageSize.Value = 4;
cmd.Parameters.Add(pageSize);
var currentPage = cmd.CreateParameter();
currentPage.ParameterName = writer.ParameterName("currentPage");
currentPage.Value = 1;
cmd.Parameters.Add(currentPage);
var da = _factory.CreateDataAdapter();
da.SelectCommand = cmd;
da.Fill(dataTable);
}
}
//assert
Assert.IsTrue(dataTable.Rows.Count > 0, "There should be some categories (this test may fail if database table is empty)");
Assert.IsTrue(dataTable.Rows.Count <= 4, "Should only return the page size (or less)");
foreach (var column in _table.Columns)
{
var name = column.Name;
Assert.IsTrue(dataTable.Columns.Contains(name), "Should retrieve column " + name);
}
}
public void ReadNorthwindProductsWithCodeGen()
{
var dbReader = GetNortwindReader();
dbReader.DataTypes(); //load the datatypes
var table = dbReader.Table("Products");
Debug.WriteLine("Table " + table.Name);
foreach (var column in table.Columns)
{
//Cs properties (the column name could be made .Net friendly too)
Debug.WriteLine("\tpublic " + column.DataType.NetCodeName(column) + " " + column.Name + " { get; set; }");
}
// public int ProductID { get; set; }
// public string ProductName { get; set; }
// public int SupplierID { get; set; }
// public int CategoryID { get; set; }
// public string QuantityPerUnit { get; set; }
// public decimal UnitPrice { get; set; }
// public short UnitsInStock { get; set; }
// public short UnitsOnOrder { get; set; }
// public short ReorderLevel { get; set; }
// public bool Discontinued { get; set; }
//get the sql
var sqlWriter =
new SqlWriter(table, DatabaseSchemaReader.DataSchema.SqlType.SqlServer);
var sql = sqlWriter.SelectPageSql(); //paging sql
sql = SqlWriter.SimpleFormat(sql); //remove line breaks
Debug.WriteLine(sql);
//SELECT [ProductID], [ProductName], ...etc...
//FROM
//(SELECT ROW_NUMBER() OVER( ORDER BY [ProductID]) AS
//rowNumber, [ProductID], [ProductName], ...etc..
//FROM [Products]) AS countedTable
//WHERE rowNumber >= (@pageSize * (@currentPage - 1))
//AND rowNumber <= (@pageSize * @currentPage)
}
public void TestGeneratedSqlForPaging()
{
//arrange
var table = LoadCategoriesFromNorthwind();
var writer = new SqlWriter(table, SqlType.SqlServer);
var sql = writer.SelectPageSql();
var factory = DbProviderFactories.GetFactory(ProviderName);
var dataTable = new DataTable();
//run generated sql
using (var con = factory.CreateConnection())
{
con.ConnectionString = ConnectionString;
using (var cmd = con.CreateCommand())
{
cmd.CommandText = sql;
var pageSize = factory.CreateParameter();
pageSize.ParameterName = "@pageSize";
pageSize.Value = 4;
cmd.Parameters.Add(pageSize);
var currentPage = factory.CreateParameter();
currentPage.ParameterName = "@currentPage";
currentPage.Value = 1;
cmd.Parameters.Add(currentPage);
var da = factory.CreateDataAdapter();
da.SelectCommand = cmd;
da.Fill(dataTable);
}
}
//assert
Assert.IsTrue(dataTable.Rows.Count > 0, "There should be some categories (this test may fail if database table is empty)");
foreach (var column in table.Columns)
{
var name = column.Name;
Assert.IsTrue(dataTable.Columns.Contains(name), "Should retrieve column " + name);
}
}
public DataTable Read()
{
var sqlType = FindSqlType();
var originSql = new SqlWriter(_databaseTable, sqlType);
var selectAll = originSql.SelectPageSql();
var dt = new DataTable(_databaseTable.Name) { Locale = CultureInfo.InvariantCulture };
var dbFactory = DbProviderFactories.GetFactory(_providerName);
using (var con = dbFactory.CreateConnection())
{
con.ConnectionString = _connectionString;
using (var cmd = con.CreateCommand())
{
cmd.CommandText = selectAll;
var p = cmd.CreateParameter();
var parameterName = "currentPage";
if (sqlType == SqlType.SqlServerCe) parameterName = "offset";
p.ParameterName = parameterName;
p.Value = 1;
if (sqlType == SqlType.SqlServerCe) p.Value = 0;
cmd.Parameters.Add(p);
var ps = cmd.CreateParameter();
ps.ParameterName = "pageSize";
ps.Value = _pageSize;
cmd.Parameters.Add(ps);
using (var da = dbFactory.CreateDataAdapter())
{
da.SelectCommand = cmd;
da.Fill(dt);
}
}
}
return dt;
}
