/// <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; }