/// <summary>
/// 获取分页数据
/// </summary>
public DatabaseResultModel<List<TDemoModel>> getListData(TDemoModel searchModel)
{
var result = new DatabaseResultModel<List<TDemoModel>>();
try
{
using (databaseConnector = getDatabaseConnector())
using (databaseExecutor = new Executor(databaseConnector))
{
databaseScriptModel = new DatabaseScriptModel();
databaseScriptModel.command = (new TDemoScript()).getListData(searchModel);
result.data = databaseExecutor.sqlQueryToList<TDemoModel>(databaseScriptModel);
}
}
catch (Exception ex) { result.exception = ex; }
return result;
}
/// <summary>
/// 删除
/// </summary>
public DatabaseResultModel<bool> delete(TDemoModel model)
{
var result = new DatabaseResultModel<bool>();
try
{
using (databaseConnector = getDatabaseConnector())
using (databaseExecutor = new Executor(databaseConnector))
{
databaseScriptModel = new DatabaseScriptModel();
databaseScriptModel.command = (new TDemoScript()).delete(model);
executeSqlCommand(new TDemoScript());
result.data = true;
}
}
catch (Exception ex) { result.exception = ex; result.data = false; }
return result;
}
/// <summary>
/// 获取数据
/// </summary>
public string getData(TDemoModel model)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine(" SELECT ");
sb.AppendLine(" [ID] ");
sb.AppendLine(" ,[Column1] ");
sb.AppendLine(" ,[Column2] ");
sb.AppendLine(" ,[Column3] ");
sb.AppendLine(" ,[Column4] ");
sb.AppendLine(" FROM [dbo].[TDemo] ");
sb.AppendLine(" WHERE ");
sb.AppendLine(" [Column1] = @Column1 ");
return sb.ToString();
}
/// <summary>
/// 获取分页数据
/// </summary>
public string getListData(TDemoModel searchModel = null)
{
StringBuilder sb = new StringBuilder();
string searchText = "";
//检索
StringBuilder searchScript = new StringBuilder();
searchScript.AppendLine(" WHERE ");
searchScript.AppendLine(" 1 = 1 ");
if (!String.IsNullOrEmpty(searchModel.fullTextSearch))
{
searchScript.AppendLine(" AND ( ");
searchScript.AppendLine(" [Column1] LIKE N'%" + searchModel.fullTextSearch.quote() + "%'");
searchScript.AppendLine(" OR [Column2] = '" + searchModel.fullTextSearch.quote() + "'");
searchScript.AppendLine(" OR [Column3] = '" + searchModel.fullTextSearch.quote() + "'");
searchScript.AppendLine(" OR [Column4] = '" + searchModel.fullTextSearch.quote() + "'");
searchScript.AppendLine(" ) ");
}
searchText = searchScript.toString();
//获取总数据量
sb.AppendLine(" DECLARE @dataCount int ");
sb.AppendLine(" ");
sb.AppendLine(" SELECT ");
sb.AppendLine(" @dataCount = COUNT(1) ");
sb.AppendLine(" FROM [dbo].[TDemo]");
sb.AppendLine(searchText);
sb.Append(" ; ");
//数据分页
sb.AppendLine(" WITH TMP AS ( ");
sb.AppendLine(" SELECT ");
sb.AppendLine(" [ID] ");
sb.AppendLine(" ,[Column1] ");
sb.AppendLine(" ,[Column2] ");
sb.AppendLine(" ,[Column3] ");
sb.AppendLine(" ,[Column4] ");
sb.AppendLine(" ,ROW_NUMBER() OVER(" + string.Format("ORDER BY {0} {1}",
searchModel.sortColumn,
searchModel.sortDirection == System.ComponentModel.ListSortDirection.Ascending ? "ASC" : "DESC")
+ ") AS [RowNumber] ");
sb.AppendLine(" FROM [dbo].[TDemo]");
sb.AppendLine(searchText);
sb.AppendLine(" ) ");
//获取分页数据
sb.AppendLine(" SELECT ");
sb.AppendLine(" A.[ID] ");
sb.AppendLine(" ,A.[Column1] ");
sb.AppendLine(" ,A.[Column2] ");
sb.AppendLine(" ,A.[Column3] ");
sb.AppendLine(" ,A.[Column4] ");
sb.AppendLine(" ,@dataCount AS [DataCount] ");
sb.AppendLine(" FROM TMP AS A ");
if (searchModel != null)
{
sb.AppendLine(" WHERE ");
sb.AppendLine(" 1 = 1 ");
sb.AppendLine(string.Format(" AND A.[RowNumber] BETWEEN {0} AND {1} ", searchModel.startRowNumber, searchModel.endRowNumber));
}
if (searchModel != null && !Check.getIns().isEmpty(searchModel.sortColumn))
{
sb.AppendLine(string.Format("ORDER BY A.{0} {1}",
searchModel.sortColumn,
searchModel.sortDirection == System.ComponentModel.ListSortDirection.Ascending ? "ASC" : "DESC"));
}
return sb.ToString();
}
/// <summary>
/// 更新（新增和更新）
/// </summary>
public string update(TDemoModel model)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine(" IF ((SELECT COUNT(1) ");
sb.AppendLine(" FROM [dbo].[TDemo] ");
sb.AppendLine(" WHERE ");
sb.AppendLine(" [ID] = " + model.ID.sqlString() + ") <= 0) ");
sb.AppendLine(" BEGIN ");
sb.AppendLine(" INSERT INTO [dbo].[TDemo] ");
sb.AppendLine(" ([Column1] ");
sb.AppendLine(" ,[Column2] ");
sb.AppendLine(" ,[Column3] ");
sb.AppendLine(" ,[Column4]) ");
sb.AppendLine(" VALUES ");
sb.AppendLine(" (" + model.Column1.sqlString());
sb.AppendLine(" ," + model.Column2.sqlString());
sb.AppendLine(" ," + model.Column3.sqlString());
sb.AppendLine(" ," + model.Column4.sqlString());
sb.AppendLine(" ) ");
sb.AppendLine(" END ");
sb.AppendLine(" ELSE ");
sb.AppendLine(" BEGIN ");
sb.AppendLine(" UPDATE [dbo].[TDemo] ");
sb.AppendLine(" SET ");
sb.AppendLine(" [Column1] = " + model.Column1.sqlString());
sb.AppendLine(" ,[Column2] = " + model.Column2.sqlString());
sb.AppendLine(" ,[Column3] = " + model.Column3.sqlString());
sb.AppendLine(" ,[Column4] = " + model.Column4.sqlString());
sb.AppendLine(" WHERE ");
sb.AppendLine(" [ID] = " + model.ID.sqlString());
sb.AppendLine(" End ");
return sb.ToString();
}
/// <summary>
/// 删除
/// </summary>
public string delete(TDemoModel model)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine(" DELETE FROM [dbo].[TDemo] ");
sb.AppendLine(" WHERE ");
sb.AppendLine(" [ID] = " + model.ID.sqlString());
return sb.ToString();
}
/// <summary>
/// 获取数据检索参数
/// </summary>
public SqlParameter[] getDataParameters(TDemoModel model)
{
SqlParameter[] parameters = new SqlParameter[1];
SqlParameter parameter = new SqlParameter("@Column1", SqlDbType.NVarChar);
parameter.Value = model.Column1;
parameters[0] = parameter;
return parameters;
}
