public DataTable ExecuteCommand(Guid userID, string commandText, int pageIndex, int pageSize, out int totalRow) { using (var context = new VnrHrmDataContext()) { DataTable result = new DataTable("Sys_SQLCommanderModel"); var connection = context.Database.Connection; using (DbCommander commander = new DbCommander(connection)) { if (pageIndex >= 0 && pageSize > 0) { var fromIndex = commandText.ToLower().IndexOf("from"); var countCommand = commandText.Substring(fromIndex); countCommand = "Select Count(*) " + countCommand; var rowCount = commander.ExecuteScalar(countCommand); totalRow = rowCount.TryGetValue<int>(); int startRow = pageIndex * pageSize; commander.Fill(result, startRow, pageSize, commandText); } else { commander.Fill(result, commandText); totalRow = result.Rows.Count; } if ((result.Columns.Contains(Constant.GrossAmount) && result.Columns[Constant.GrossAmount].DataType == typeof(string)) || (result.Columns.Contains(Constant.Amount) && result.Columns[Constant.Amount].DataType == typeof(string))) { foreach (DataRow row in result.Rows) { try { if (result.Columns.Contains(Constant.Amount) && result.Columns[Constant.Amount].DataType == typeof(string)) { if (!string.IsNullOrWhiteSpace(row[Constant.Amount].GetString())) { var grossAmount = row[Constant.Amount].GetString(); row[Constant.Amount] = grossAmount.Decrypt(); } } if (result.Columns.Contains(Constant.GrossAmount) && result.Columns[Constant.GrossAmount].DataType == typeof(string)) { if (!string.IsNullOrWhiteSpace(row[Constant.GrossAmount].GetString())) { var grossAmount = row[Constant.GrossAmount].GetString(); row[Constant.GrossAmount] = grossAmount.Decrypt(); } } } catch (Exception) { } } } } return result; } }
public DataTable ExecuteCommand(Guid userID, string commandText, int pageIndex, int pageSize, out int totalRow) { using (var context = new VnrHrmDataContext()) { DataTable result = new DataTable("Sys_SQLCommanderModel"); var connection = context.Database.Connection; using (DbCommander commander = new DbCommander(connection)) { if (pageIndex >= 0 && pageSize > 0) { var fromIndex = commandText.ToLower().IndexOf("from"); var countCommand = commandText.Substring(fromIndex); countCommand = "Select Count(*) " + countCommand; var rowCount = commander.ExecuteScalar(countCommand); totalRow = rowCount.TryGetValue <int>(); int startRow = pageIndex * pageSize; commander.Fill(result, startRow, pageSize, commandText); } else { commander.Fill(result, commandText); totalRow = result.Rows.Count; } if ((result.Columns.Contains(Constant.GrossAmount) && result.Columns[Constant.GrossAmount].DataType == typeof(string)) || (result.Columns.Contains(Constant.Amount) && result.Columns[Constant.Amount].DataType == typeof(string))) { foreach (DataRow row in result.Rows) { try { if (result.Columns.Contains(Constant.Amount) && result.Columns[Constant.Amount].DataType == typeof(string)) { if (!string.IsNullOrWhiteSpace(row[Constant.Amount].GetString())) { var grossAmount = row[Constant.Amount].GetString(); row[Constant.Amount] = grossAmount.Decrypt(); } } if (result.Columns.Contains(Constant.GrossAmount) && result.Columns[Constant.GrossAmount].DataType == typeof(string)) { if (!string.IsNullOrWhiteSpace(row[Constant.GrossAmount].GetString())) { var grossAmount = row[Constant.GrossAmount].GetString(); row[Constant.GrossAmount] = grossAmount.Decrypt(); } } } catch (Exception) { } } } } return(result); } }