public static string CreateSelectAll(DataTable dataTable)
{
var columns = SqlSelectHelper.ArrangeColumnsWithComma(dataTable);
var tableName = dataTable.TableName;
return($"SELECT {columns.Trim()} FROM [{tableName.Trim()}];");
}
public static string CreateSelectCountByKeyword(DataTable dataTable)
{
var tableName = dataTable.TableName;
var whereLikeKeyword = SqlSelectHelper.ArrangeColumeLikeKeyword(dataTable);
return($"SELECT COUNT({dataTable.Columns[0]}) FROM [{tableName}] WHERE ({whereLikeKeyword});");
}
public static string SelectByPrimaryKey(DataTable dataTable)
{
var columns = SqlSelectHelper.ArrangeColumnsWithComma(dataTable);
var tableName = dataTable.TableName;
return($"SELECT TOP 1 {columns.Trim()} FROM [{tableName.Trim()}] WHERE {SqlPrimaryKeyStringHelper.ArrangeColumnsWithParam(dataTable)};");
}
public static string CreateSelectByKeyword(DataTable dataTable)
{
var columns = SqlSelectHelper.ArrangeColumnsWithComma(dataTable);
var tableName = dataTable.TableName;
var keys = SqlPrimaryKeyStringHelper.ArrangeColumnsWithComma(dataTable);
var whereLikeKeyword = SqlSelectHelper.ArrangeColumeLikeKeyword(dataTable);
var subQuery = $"(SELECT ROW_NUMBER() OVER (ORDER BY {{orderByColumnName}} {{orderDirection}}) AS RowSequence, {columns.Trim()} FROM [{tableName.Trim()}] WHERE ({whereLikeKeyword})) AS [{tableName.Trim()}]";
return($"SELECT {columns.Trim()} FROM {subQuery} WHERE RowSequence BETWEEN @Start AND @End;");
}
