public object getSubscriberGroupAll_DataTable(DTParameters param)
{
string search = param.Search.Value;
string sortOrder = param.SortOrder;
int start = param.Start;
int length = param.Length;
List <string> columnFilters = DataTablesJS <Model_SubscriberGroup> .getcolumnSearch(param);
using (SqlConnection cn = new SqlConnection(this.ConnectionString))
{
string[] filerName = { "", "", "SGName", "SGDetail" };
StringBuilder strfilter = new StringBuilder();
for (int i = 0; i < columnFilters.Count; i++)
{
if (!string.IsNullOrEmpty(columnFilters[i]))
{
strfilter.Append(" AND LOWER(" + filerName[i] + ") LIKE @filer_" + i);
}
}
string strcmd = @"
;WITH SubscriberGroup_cte AS (
SELECT *
FROM dbo.SubscriberGroup
WHERE Sbin = 1 " +
(string.IsNullOrEmpty(search) ? "" : "AND (LOWER(SGName) LIKE @search OR SGDetail LIKE @search) ") +
(columnFilters.Count > 0 ? strfilter.ToString() : "")
+ @"
)
SELECT
db.*,
tCountOrders.CountOrders AS TotalRows
FROM SubscriberGroup_cte db
CROSS JOIN (SELECT Count(*) AS CountOrders FROM SubscriberGroup_cte) AS tCountOrders
ORDER BY " + sortOrder + @"
OFFSET @Start ROWS
FETCH NEXT @Size ROWS ONLY;
";
SqlCommand cmd = new SqlCommand(strcmd, cn);
cmd.Parameters.Add("@Start", SqlDbType.Int).Value = start;
cmd.Parameters.Add("@Size", SqlDbType.Int).Value = length;
if (!string.IsNullOrEmpty(search))
{
string searchTerm = string.Format("%{0}%", search);
cmd.Parameters.Add(new SqlParameter("@search", searchTerm));
//cmd.Parameters.Add("@search", SqlDbType.NVarChar).Value = searchTerm;
}
if (columnFilters.Count > 0)
{
if (!string.IsNullOrEmpty(columnFilters[2]))
{
string searchTerm = string.Format("%{0}%", columnFilters[2]);
cmd.Parameters.Add(new SqlParameter("@filer_2", searchTerm));
}
if (!string.IsNullOrEmpty(columnFilters[3]))
{
string searchTerm = string.Format("%{0}%", columnFilters[3]);
cmd.Parameters.Add(new SqlParameter("@filer_3", searchTerm));
}
}
cn.Open();
IDataReader reader = ExecuteReader(cmd);
IList <Model_SubscriberGroup> result = MappingObjectCollectionFromDataReader(reader);
int countTotal = (result.Count() > 0? result[0].TotelRows : 0);
return(DataTablesJS <Model_SubscriberGroup> .ResponeDataTable(param, result, countTotal));
}
}
public object getSubscriberAll_DataTable(DTParameters param)
{
string search = param.Search.Value;
string sortOrder = param.SortOrder;
int start = param.Start;
int length = param.Length;
List <string> columnFilters = DataTablesJS <Model_Subscriber> .getcolumnSearch(param);
DTCustomSerach custom = param.CustomSearch;
DTStatus Status = param.Status;
using (SqlConnection cn = new SqlConnection(this.ConnectionString))
{
string[] filerName = { "", "", "FirstName", "LastName", "Email" };
StringBuilder strfilter = new StringBuilder();
for (int i = 0; i < columnFilters.Count; i++)
{
if (!string.IsNullOrEmpty(columnFilters[i]))
{
strfilter.Append(" AND LOWER(" + filerName[i] + ") LIKE @filer_" + i);
}
}
string strcmd = @"
;WITH Subscriber_cte AS (
SELECT *
FROM dbo.Subscriber
WHERE SID > 1 " +
(string.IsNullOrEmpty(search) ? "" : "AND (FirstName LIKE @search OR LastName LIKE @search OR Email LIKE @search) ") +
(custom.Value != null ? "AND " + custom.Key + " = @CustomKeyValue" : "") + " " + (Status.Value != null ? "AND " + Status.Key + " = @Sbin" : "") +
(columnFilters.Count > 0 ? strfilter.ToString() : "")
+ @"
)
SELECT
db.*,
tCountOrders.CountOrders AS TotalRows
FROM Subscriber_cte db
CROSS JOIN (SELECT Count(*) AS CountOrders FROM Subscriber_cte) AS tCountOrders
ORDER BY " + sortOrder + @"
OFFSET @Start ROWS
FETCH NEXT @Size ROWS ONLY;
";
SqlCommand cmd = new SqlCommand(strcmd, cn);
cmd.Parameters.Add("@Start", SqlDbType.Int).Value = start;
cmd.Parameters.Add("@Size", SqlDbType.Int).Value = length;
if (custom.Value != null)
{
cmd.Parameters.Add("@CustomKeyValue", SqlDbType.Int).Value = custom.Value;
}
if (Status.Value != null)
{
bool status = true;
if (Status.Value == "0")
{
status = false;
}
cmd.Parameters.Add("@Sbin", SqlDbType.Bit).Value = status;
}
if (!string.IsNullOrEmpty(search))
{
string searchTerm = string.Format("%{0}%", search);
cmd.Parameters.Add(new SqlParameter("@search", searchTerm));
//cmd.Parameters.Add("@search", SqlDbType.NVarChar).Value = searchTerm;
}
if (columnFilters.Count > 0)
{
if (!string.IsNullOrEmpty(columnFilters[2]))
{
string searchTerm = string.Format("%{0}%", columnFilters[2]);
cmd.Parameters.Add(new SqlParameter("@filer_2", searchTerm));
}
if (!string.IsNullOrEmpty(columnFilters[3]))
{
string searchTerm = string.Format("%{0}%", columnFilters[3]);
cmd.Parameters.Add(new SqlParameter("@filer_3", searchTerm));
}
}
cn.Open();
IDataReader reader = ExecuteReader(cmd);
List <Model_Subscriber> result = MappingObjectCollectionFromDataReaderByName(reader);
int countTotal = (result.Count() > 0 ? result[0].TotalRows : 0);
return(DataTablesJS <Model_Subscriber> .ResponeDataTable(param, result, countTotal));
}
}