public static DataSetData FromDataSet(DataSet ds) { DataSetData dsd = new DataSetData(); dsd.Tables = new ObservableCollection <DataTableInfo>(); foreach (DataTable t in ds.Tables) { DataTableInfo tableInfo = new DataTableInfo { TableName = t.TableName }; dsd.Tables.Add(tableInfo); tableInfo.Columns = new ObservableCollection <DataColumnInfo>(); foreach (DataColumn c in t.Columns) { DataColumnInfo col = new DataColumnInfo { ColumnName = c.ColumnName, ColumnTitle = c.ColumnName, DataTypeName = c.DataType.FullName, MaxLength = c.MaxLength, IsKey = c.Unique, IsReadOnly = (c.Unique || c.ReadOnly), IsRequired = !c.AllowDBNull }; if (c.DataType == typeof(System.Guid)) { col.IsReadOnly = true; col.DisplayIndex = -1; } tableInfo.Columns.Add(col); } } dsd.DataXML = ds.GetXml(); return(dsd); }
public static DataSet ToDataSet(DataSetData dsd) { DataSet ds = new DataSet(); UTF8Encoding encoding = new UTF8Encoding(); Byte[] byteArray = encoding.GetBytes(dsd.DataXML); MemoryStream stream = new MemoryStream(byteArray); XmlReader reader = new XmlTextReader(stream); ds.ReadXml(reader); XDocument xd = XDocument.Parse(dsd.DataXML); foreach (DataTable dt in ds.Tables) { var rs = from row in xd.Descendants(dt.TableName) select row; int i = 0; foreach (var r in rs) { DataRowState state = (DataRowState)Enum.Parse(typeof(DataRowState), r.Attribute("RowState").Value); DataRow dr = dt.Rows[i]; dr.AcceptChanges(); if (state == DataRowState.Deleted) dr.Delete(); else if (state == DataRowState.Added) dr.SetAdded(); else if (state == DataRowState.Modified) dr.SetModified(); i++; } } return ds; }
public DataSetData GetDataSetData(string SQL, int PageNumber, int PageSize, out CustomException ServiceError) { try { DataSet ds = GetDataSet(SQL, PageNumber, PageSize); ServiceError = null; return(DataSetData.FromDataSet(ds)); } catch (Exception ex) { ServiceError = new CustomException(ex); } return(null); }
public static IEnumerable GetDataList(DataSetData data) { if (data == null) { return(null); } if (data.Tables.Count == 0) { return(null); } DataTableInfo tableInfo = data.Tables[0]; System.Type dataType = BuildDataObjectType(tableInfo.Columns, "MyDataObject"); //ObservableCollection<DataObject> l = new ObservableCollection<DataObject>(); var listType = typeof(ObservableCollection <>).MakeGenericType(new[] { dataType }); var list = Activator.CreateInstance(listType); XDocument xd = XDocument.Parse(data.DataXML); var table = from row in xd.Descendants(tableInfo.TableName) select row.Elements().ToDictionary(r => r.Name, r => r.Value); foreach (var r in table) { var rowData = Activator.CreateInstance(dataType) as CustomerDataObject; if (rowData != null) { foreach (DataColumnInfo col in tableInfo.Columns) { if (r.ContainsKey(col.ColumnName) && col.DataTypeName != typeof(System.Byte[]).FullName && col.DataTypeName != typeof(System.Guid).FullName) { if (col.IsEncrypt) { rowData.SetFieldValue(col.ColumnName, SMT.SaaS.FrameworkUI.Common.Utility.AESDecrypt(r[col.ColumnName]), true); } else { rowData.SetFieldValue(col.ColumnName, r[col.ColumnName], true); } } } } listType.GetMethod("Add").Invoke(list, new[] { rowData }); } ObservableCollection <CustomerDataObject> l = list as ObservableCollection <CustomerDataObject>; return(list as IEnumerable); }
public bool Update(DataSetData d, out CustomException ServiceError) { try { DataSet ds = DataSetData.ToDataSet(d); UpdataDataSet(ds); ServiceError = null; return(true); } catch (Exception ex) { ServiceError = new CustomException(ex); } return(false); }
public DataSetData GetEventTypes(out CustomException ServiceError) { try { string SQL = "select EVENT_TYPE_ID, EVENT_TYPE_NAME from EVENT_TYPES;"; int PageNumber = 1; int PageSize = 10000; DataSet ds = GetDataSet(SQL, PageNumber, PageSize); ServiceError = null; return(DataSetData.FromDataSet(ds)); } catch (Exception ex) { ServiceError = new CustomException(ex); } return(null); }
public DataSetData GetSchools(out CustomException ServiceError) { try { string SQL = "select SCHOOL_ID, SCHOOL_NAME from SCHOOLS;"; int PageNumber = 1; int PageSize = 10000; DataSet ds = GetDataSet(SQL, PageNumber, PageSize); ServiceError = null; return(DataSetData.FromDataSet(ds)); } catch (Exception ex) { ServiceError = new CustomException(ex); } return(null); }
public DataSetData GetSchoolBuildings(int school_id, out CustomException ServiceError) { try { string SQL = String.Format("select SCHOOL_BUILDINGS_ID, BUILDING_NAME from SCHOOL_BUILDINGS where (SCHOOL_ID = {0});", school_id); int PageNumber = 1; int PageSize = 10000; DataSet ds = GetDataSet(SQL, PageNumber, PageSize); ServiceError = null; return(DataSetData.FromDataSet(ds)); } catch (Exception ex) { ServiceError = new CustomException(ex); } return(null); }
public static DataSet ToDataSet(DataSetData dsd) { DataSet ds = new DataSet(); UTF8Encoding encoding = new UTF8Encoding(); Byte[] byteArray = encoding.GetBytes(dsd.DataXML); MemoryStream stream = new MemoryStream(byteArray); XmlReader reader = new XmlTextReader(stream); ds.ReadXml(reader); XDocument xd = XDocument.Parse(dsd.DataXML); foreach (DataTable dt in ds.Tables) { var rs = from row in xd.Descendants(dt.TableName) select row; int i = 0; foreach (var r in rs) { DataRowState state = (DataRowState)Enum.Parse(typeof(DataRowState), r.Attribute("RowState").Value); DataRow dr = dt.Rows[i]; dr.AcceptChanges(); if (state == DataRowState.Deleted) { dr.Delete(); } else if (state == DataRowState.Added) { dr.SetAdded(); } else if (state == DataRowState.Modified) { dr.SetModified(); } i++; } } return(ds); }
public static DataSetData FromDataSet(DataSet ds) { DataSetData dsd = new DataSetData(); dsd.Tables = new ObservableCollection<DataTableInfo>(); foreach (DataTable t in ds.Tables) { DataTableInfo tableInfo = new DataTableInfo {TableName = t.TableName}; dsd.Tables.Add(tableInfo); tableInfo.Columns = new ObservableCollection<DataColumnInfo>(); foreach (DataColumn c in t.Columns) { DataColumnInfo col = new DataColumnInfo { ColumnName = c.ColumnName, ColumnTitle = c.ColumnName, DataTypeName = c.DataType.FullName, MaxLength=c.MaxLength, IsKey=c.Unique, IsReadOnly=(c.Unique || c.ReadOnly), IsRequired = !c.AllowDBNull}; if (c.DataType == typeof(System.Guid)) { col.IsReadOnly = true; col.DisplayIndex = -1; } tableInfo.Columns.Add(col); } } dsd.DataXML = ds.GetXml(); return dsd; }
public IEnumerable GetDataListForSalary(DataSetData data) { if (data == null) return null; if (data.Tables.Count == 0) return null; DataTableInfo tableInfo = data.Tables[0]; System.Type dataType = DynamicDataBuilder.BuildDataObjectType(tableInfo.Columns, "MyDataObject"); var listType = typeof(ObservableCollection<>).MakeGenericType(new[] { dataType }); var list = Activator.CreateInstance(listType); XDocument xd = XDocument.Parse(data.DataXML); var table = from row in xd.Descendants(tableInfo.TableName) select row.Elements().ToDictionary(r => r.Name, r => r.Value); recordCount = table == null ? 0 : table.Count(); #region 取出合计值和平均值 var table1 = from row in xd.Descendants("Table2") select row.Elements().ToDictionary(r => r.Name, r => r.Value); foreach (var r in table1) { tbTotalMoney.Text = r["sum"]; tbAvgMoney.Text = r["avg"]; } #endregion foreach (var r in table) { var rowData = Activator.CreateInstance(dataType) as CustomerDataObject; if (rowData != null) { foreach (DataColumnInfo col in tableInfo.Columns) { if (r.ContainsKey(col.ColumnName) && col.DataTypeName != typeof(System.Byte[]).FullName && col.DataTypeName != typeof(System.Guid).FullName) { if (col.IsEncrypt) { rowData.SetFieldValue(col.ColumnName, SMT.SaaS.FrameworkUI.Common.Utility.AESDecrypt(r[col.ColumnName]), true); } else { rowData.SetFieldValue(col.ColumnName, r[col.ColumnName], true); } } } } listType.GetMethod("Add").Invoke(list, new[] { rowData }); } ObservableCollection<CustomerDataObject> l = list as ObservableCollection<CustomerDataObject>; return list as IEnumerable; }
void client_GetAuditSalaryRecordsCompleted(object sender, GetAuditSalaryRecordsCompletedEventArgs e) { if (e.Error == null) { dataSet = e.Result; if (dataSet == null) { RefreshUI(RefreshedTypes.HideProgressBar); ComfirmWindow.ConfirmationBoxs(Utility.GetResourceStr("WARNING"), Utility.GetResourceStr("NODATA") + ",请检查是否未获得员工月薪生成的权限,或提交数据已被更改。", Utility.GetResourceStr("CONFIRM"), MessageIcon.Information); return; } IEnumerable list = GetDataListForSalary(e.Result); DtGriddy = new DataGrid(); #region 设置当前加载DtGriddy样式 DtGriddy.Style = Application.Current.Resources["DataGridStyle"] as Style; DtGriddy.CellStyle = Application.Current.Resources["DataGridCellStyle"] as Style; DtGriddy.RowHeaderStyle = Application.Current.Resources["DataGridRowHeaderStyle"] as Style; DtGriddy.ColumnHeaderStyle = Application.Current.Resources["DataGridColumnHeaderStyle"] as Style; DtGriddy.RowStyle = Application.Current.Resources["DataGridRowStyle"] as Style; DtGriddy.FrozenColumnCount = 8; if (recordCount >= 17) //数据少了 固定高度 空白太多 { DtGriddy.Height = 400; } //DtGriddy.AreRowDetailsFrozen = true; DtGriddy.IsReadOnly = true; DtGriddy.CanUserSortColumns = false; DtGriddy.CanUserReorderColumns = false; DtGriddy.VerticalScrollBarVisibility = ScrollBarVisibility.Visible; DtGriddy.AutoGenerateColumns = false; DtGriddy.IsReadOnly = true; #endregion #region chexkbox列 DataGridTemplateColumn colchb = new DataGridTemplateColumn(); colchb.Header = Utility.GetResourceStr("SELECT"); colchb.CellEditingTemplate = (DataTemplate)Resources["DataTemplates"]; colchb.HeaderStyle = Resources["DataGridCheckBoxColumnHeaderStyleAudit"] as Style; if (dataSet.Tables.Count > 0 && SalaryRecordBatch.CHECKSTATE != Convert.ToInt32(CheckStates.UnSubmit).ToString()) { colchb.Visibility = Visibility.Collapsed; } DtGriddy.Columns.Add(colchb); #endregion #region 序号列 DataGridTemplateColumn colNo = new DataGridTemplateColumn(); colNo.Header = Utility.GetResourceStr("序号"); colNo.CellEditingTemplate = (DataTemplate)Resources["DataTemplateTextBlock"]; //colchb.HeaderStyle = Resources["DataGridCheckBoxColumnHeaderStyleAudit"] as Style; DtGriddy.Columns.Add(colNo); #endregion #region 初始列 if (e.Result.Tables.Count > 0) { foreach (DataColumnInfo column in e.Result.Tables[0].Columns) { #region 注释 //if (column.DisplayIndex != -1) //{ // DataGridColumn col; // DataTemplate dt; // if (column.DataTypeName == typeof(bool).FullName) // { // DataGridCheckBoxColumn checkBoxColumn = new DataGridCheckBoxColumn(); // checkBoxColumn.Binding = new Binding(column.ColumnName); // col = checkBoxColumn; // } // else if (column.DataTypeName == typeof(DateTime).FullName) // { // DataGridTemplateColumn templateColumn = new DataGridTemplateColumn(); // string temp = TemplateManager.DataTemplates["DateTimeCellTemplate"]; // temp = temp.Replace("@HorizontalAlignment@", HorizontalAlignment.Left.ToString()); // temp = temp.Replace("@Text@", column.ColumnName); // temp = temp.Replace("@DateTimeFormat@", "MM/dd/yyyy"); // dt = XamlReader.Load(temp) as DataTemplate; // templateColumn.CellTemplate = dt; // DataTemplate t = new DataTemplate(); // temp = TemplateManager.DataTemplates["DateTimeCellEditingTemplate"]; // temp = temp.Replace("@HorizontalAlignment@", HorizontalAlignment.Left.ToString()); // temp = temp.Replace("@SelectedDate@", column.ColumnName); // dt = XamlReader.Load(temp) as DataTemplate; // templateColumn.CellEditingTemplate = dt; // col = templateColumn; // } // else // { // DataGridTextColumn textColumn = new DataGridTextColumn(); // textColumn.Binding = new Binding(column.ColumnName); // textColumn.Binding.ValidatesOnExceptions = true; // col = textColumn; // } #endregion DataGridColumn col; DataGridTextColumn textColumn = new DataGridTextColumn(); textColumn.Binding = new Binding(column.ColumnName); textColumn.Binding.ValidatesOnExceptions = true; col = textColumn; col.IsReadOnly = column.IsReadOnly; col.Visibility = column.IsShow ? Visibility.Visible : Visibility.Collapsed; col.Header = column.ColumnTitle; col.SortMemberPath = column.ColumnName; col.CanUserSort = false; col.MinWidth = 100; DtGriddy.Columns.Add(col); } } #endregion DtGriddy.LoadingRow += new EventHandler<DataGridRowEventArgs>(DtGriddy_LoadingRow); DtGriddy.ItemsSource = list; SpSalaryRecord.Children.Add(DtGriddy); RefreshUI(RefreshedTypes.AuditInfo); RefreshUI(RefreshedTypes.HideProgressBar); #region //if (its != null) //{ // for (int i = 0; i < its.Count; i++) // { // V_salaryRecordDetailView itemTmp = new V_salaryRecordDetailView(); // itemTmp.ACTUALLYPAY = its[i].ACTUALLYPAY; // itemTmp.EMPLOYEESALARYRECORDID = its[i].EMPLOYEESALARYRECORDID; // itemTmp.EMPLOYEEID = its[i].EMPLOYEEID; // itemTmp.EMPLOYEENAME = its[i].EMPLOYEENAME; // itemTmp.DEPARTMENT = its[i].DEPARTMENT; // itemTmp.POST = its[i].POST; // its[i].ACTUALLYPAY = SMT.SaaS.FrameworkUI.Common.Utility.AESDecrypt(its[i].ACTUALLYPAY); // decimal actPay = string.IsNullOrEmpty(its[i].ACTUALLYPAY) ? 0 : Convert.ToDecimal(its[i].ACTUALLYPAY); // its[i].ACTUALLYPAY = actPay.ToString(); // if (SalarySumList.ContainsKey("实发工资")) // { // SalarySumList["实发工资"] += actPay; // } // else // { // SalarySumList.Add("实发工资", actPay); // } // if (its[i].SALARYLEVEL != null && its[i].POSTLEVEL != null) // { // its[i].POSTLEVELCODE = SMT.SaaS.FrameworkUI.Common.Utility.GetConvertResources("POSTLEVEL", its[i].POSTLEVEL.ToString()); // its[i].POSTLEVELCODE += "-" + its[i].SALARYLEVEL.ToString(); // } // itemTmp.POSTLEVELCODE = its[i].POSTLEVELCODE; // for (int j = 0; j < its[i].EMPLOYEESALARYRECORDITEMS.Count(); j++) // { // V_EMPLOYEESALARYRECORDITEM singleRecordItems = new V_EMPLOYEESALARYRECORDITEM(); // singleRecordItems.SALARYITEMNAME = its[i].EMPLOYEESALARYRECORDITEMS[j].SALARYITEMNAME; // singleRecordItems.SUM = its[i].EMPLOYEESALARYRECORDITEMS[j].SUM; // singleRecordItems.SALARYITEMID = its[i].EMPLOYEESALARYRECORDITEMS[j].SALARYITEMID; // singleRecordItems.SALARYRECORDITEMID = its[i].EMPLOYEESALARYRECORDITEMS[j].SALARYRECORDITEMID; // singleRecordItems.SALARYITEMCODE = its[i].EMPLOYEESALARYRECORDITEMS[j].SALARYITEMCODE; // its[i].EMPLOYEESALARYRECORDITEMS[j].SUM = SMT.SaaS.FrameworkUI.Common.Utility.AESDecrypt(its[i].EMPLOYEESALARYRECORDITEMS[j].SUM); // //计算每个薪资项合计值 不包括以下几项 // if (singleRecordItems.SALARYITEMNAME != "备注" && singleRecordItems.SALARYITEMNAME != "纳税系数" && singleRecordItems.SALARYITEMNAME != "扣税基数" && singleRecordItems.SALARYITEMNAME != "税率") // { // decimal sum = string.IsNullOrEmpty(its[i].EMPLOYEESALARYRECORDITEMS[j].SUM) ? 0 : Convert.ToDecimal(its[i].EMPLOYEESALARYRECORDITEMS[j].SUM); // if (SalarySumList.ContainsKey(singleRecordItems.SALARYITEMNAME)) // { // SalarySumList[singleRecordItems.SALARYITEMNAME] += sum; // } // else // { // SalarySumList.Add(singleRecordItems.SALARYITEMNAME, sum); // } // } // } // } // dataPager.PageCount = e.pageCount; // #region 合计 // V_salaryRecordDetailView sumRecorder = new V_salaryRecordDetailView(); // sumRecorder.ORDINAL = "合计"; // sumRecorder.EMPLOYEESALARYRECORDID = Guid.NewGuid().ToString(); // sumRecorder.DEPARTMENT = "--"; // sumRecorder.POST = "--"; // sumRecorder.POSTLEVELCODE = "--"; // sumRecorder.EMPLOYEENAME = "--"; // sumRecorder.EMPLOYEESALARYRECORDITEMS = new ObservableCollection<V_EMPLOYEESALARYRECORDITEM>(); // if (SalarySumList.ContainsKey("实发工资")) // { // sumRecorder.ACTUALLYPAY = SalarySumList["实发工资"].ToString(); // } // var salaryRecordItems = its.FirstOrDefault().EMPLOYEESALARYRECORDITEMS; // foreach (var ent in salaryRecordItems) // { // V_EMPLOYEESALARYRECORDITEM sumRecordItem = new V_EMPLOYEESALARYRECORDITEM(); // if (SalarySumList.ContainsKey(ent.SALARYITEMNAME)) // { // sumRecordItem.SALARYITEMID = ent.SALARYITEMID; // sumRecordItem.SALARYITEMNAME = ent.SALARYITEMNAME; // sumRecordItem.SUM = SalarySumList[ent.SALARYITEMNAME].ToString(); // } // sumRecorder.EMPLOYEESALARYRECORDITEMS.Add(sumRecordItem); // } // its.Add(sumRecorder); // #endregion // SpSalaryRecord.Children.Clear(); // DtGriddy = new DataGrid(); // #region --- 初始化固定列 --- // DataGridTemplateColumn colchb = new DataGridTemplateColumn(); // colchb.Header = Utility.GetResourceStr("SELECT"); // colchb.CellEditingTemplate = (DataTemplate)Resources["DataTemplates"]; // colchb.HeaderStyle = Resources["DataGridCheckBoxColumnHeaderStyleAudit"] as Style; // if (its.Count > 0 && its[0].CHECKSTATE != Convert.ToInt32(CheckStates.UnSubmit).ToString()) // { // colchb.Visibility = Visibility.Collapsed; // } // DtGriddy.Columns.Add(colchb); // DataGridTextColumn col = new DataGridTextColumn(); // col.Header = Utility.GetResourceStr("EMPLOYEESALARYRECORDID"); // col.Binding = new Binding("EMPLOYEESALARYRECORDID"); // col.Visibility = Visibility.Collapsed; // col.CanUserSort = false; // DtGriddy.Columns.Add(col); // DataGridTextColumn colOrder = new DataGridTextColumn(); // colOrder.Header = Utility.GetResourceStr("序号"); // colOrder.Binding = new Binding("ORDINAL"); // colOrder.Visibility = Visibility.Visible; // colOrder.CanUserSort = false; // DtGriddy.Columns.Add(colOrder); // DataGridTextColumn coldepartment = new DataGridTextColumn(); // coldepartment.Header = Utility.GetResourceStr("DEPARTMENT"); // coldepartment.Binding = new Binding("DEPARTMENT"); // coldepartment.CanUserSort = false; // DtGriddy.Columns.Add(coldepartment); // DataGridTextColumn colPost = new DataGridTextColumn(); // colPost.Header = Utility.GetResourceStr("POST"); // colPost.Binding = new Binding("POST"); // colPost.CanUserSort = false; // DtGriddy.Columns.Add(colPost); // DataGridTextColumn colEmployee = new DataGridTextColumn(); // colEmployee.Header = Utility.GetResourceStr("EMPLOYEENAME"); // colEmployee.Binding = new Binding("EMPLOYEENAME"); // colEmployee.CanUserSort = false; // DtGriddy.Columns.Add(colEmployee); // DataGridTextColumn col2 = new DataGridTextColumn(); // col2.Header = Utility.GetResourceStr("POSTLEVELCODE"); // col2.Binding = new Binding("POSTLEVELCODE"); // col2.CanUserSort = false; // DtGriddy.Columns.Add(col2); // DataGridTextColumn col8 = new DataGridTextColumn(); // col8.Header = Utility.GetResourceStr("ACTUALLYPAY"); // col8.Binding = new Binding("ACTUALLYPAY"); // col8.CanUserSort = false; // DtGriddy.Columns.Add(col8); // recordPoint = DtGriddy.Columns.Count;//统计固定列的个数 // #endregion // #region 动态添加薪资项目 // var tmp = its.FirstOrDefault().EMPLOYEESALARYRECORDITEMS.OrderBy(s => s.ORDERNUMBER).ToList();//取一条薪资记录的薪资项,动态创建薪资项列 // foreach (var it in tmp) // { // DataGridTextColumn txtCol = new DataGridTextColumn(); // txtCol.Header = it.SALARYITEMNAME; // txtCol.Binding = new Binding("SUM"); // txtCol.Width = DataGridLength.SizeToCells; // txtCol.MinWidth = 100; // DtGriddy.Columns.Add(txtCol); // } // #endregion // for (int i = 0; i < its.Count; i++) // { // if (Number == 0) // Number = dataPager.PageSize * (dataPager.PageIndex - 1) + 1; // else // Number++; // if (its[i].ORDINAL != "合计") // { // its[i].ORDINAL = Number.ToString(); // } // } // DtGriddy.ItemsSource = its; // SpSalaryRecord.Loaded += new RoutedEventHandler(SpSalaryRecord_Loaded); // SpSalaryRecord.Children.Add(DtGriddy); // try // { // var ent = e.Result.FirstOrDefault() as V_salaryRecordDetailView; // tbTotalMoney.Text = ent.PAYTOTALMONEY + " " + Utility.GetResourceStr("MONETARYUNIT"); // tbAvgMoney.Text = ent.AVGMONEY + " " + Utility.GetResourceStr("MONETARYUNIT"); // SalaryRecord = ent; // } // catch (Exception exc) // { // exc.Message.ToString(); // loadbar.Stop(); // } #endregion //} //else //{ // ComfirmWindow.ConfirmationBoxs(Utility.GetResourceStr("ERROR"), Utility.GetResourceStr("NODATA"), Utility.GetResourceStr("CONFIRM"), MessageIcon.Error); // loadbar.Stop(); //} } else { ComfirmWindow.ConfirmationBoxs(Utility.GetResourceStr("ERROR"), Utility.GetResourceStr("ERROR"), Utility.GetResourceStr("CONFIRM"), MessageIcon.Error); RefreshUI(RefreshedTypes.HideProgressBar); } }
public bool Update(DataSetData d, out CustomException ServiceError) { try { DataSet ds = DataSetData.ToDataSet(d); UpdataDataSet(ds); ServiceError = null; return true; } catch(Exception ex) { ServiceError = new CustomException(ex); } return false; }