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.
113 lines
3.4 KiB
113 lines
3.4 KiB
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;
|
|
}
|
|
|
|
}
|
|
}
|
|
|