You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

114 lines
3.4 KiB

1 year ago
using CK.SCP.Models;
using CK.SCP.Models.Enums;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace CK.SCP.Controller
{
public class SCP_COM_CONTROLLER
{
public class GridPageSearch
{
public int Begin { set; get; }
public int End { set; get; }
public string Fields { set; get; }
public string Where { set; get; }
public string OrderBy { set; get; }
public string TableView { set; get; }
public DB_CONNECTION Connection { set; get; }
public GridPageSearch()
{
Fields = " * ";
Where = " 1=1 ";
OrderBy = string.Empty;
TableView = "";
Connection = DB_CONNECTION.ScpDB;
}
}
public static DataSet GetDataLimit(GridPageSearch p_search)
{
DataSet ds = new DataSet();
var dbSetting = GlobalConfig.ScpDatabase;
switch (p_search.Connection)
{
case DB_CONNECTION.AppBoxDB:
dbSetting = GlobalConfig.AppBoxDatabase;
break;
case DB_CONNECTION.DataCenterDB:
dbSetting = GlobalConfig.DataCenterDB;
break;
case DB_CONNECTION.ExchangeCenterDB:
dbSetting = GlobalConfig.ExchangeCenterDB;
break;
case DB_CONNECTION.ScpDB:
dbSetting = GlobalConfig.ScpDatabase;
break;
case DB_CONNECTION.UniApiDB:
dbSetting = GlobalConfig.UniApiDatabase;
break;
case DB_CONNECTION.WmsDB:
dbSetting = GlobalConfig.WmsDatabase;
break;
}
var strConn = EntitiesFactory.GetEfConnectionString(dbSetting);
SqlConnection conn = new System.Data.SqlClient.SqlConnection();
conn.ConnectionString = strConn;
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
if (p_search.Fields == "") p_search.Fields = "*";
string sql = @"
select {0} from (
select ROW_NUMBER() over(order by {1}) as rows_number_9999999, {0} from {2} where {5}
) as query_temp_table_9999999 where rows_number_9999999 between {3} and {4} and ({5})
";
object[] obj = new object[6];
obj[0] = p_search.Fields;
obj[1] = p_search.OrderBy;
obj[2] = p_search.TableView;
obj[3] = p_search.Begin;
obj[4] = p_search.End;
obj[5] = p_search.Where;
sql = string.Format(sql, obj);
cmd.CommandText = sql;
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(ds);
SqlCommand cmd1 = new SqlCommand();
cmd1.Connection = conn;
cmd1.CommandText = string.Format("select count(*) from {0}",p_search.TableView);
SqlDataAdapter adapter1 = new SqlDataAdapter(cmd1);
adapter1.Fill(ds);
conn.Close();
return ds;
}
}
}