/// <summary> /// 把 List 集合转化为 DataTable 对象 /// </summary> /// <param name="objectList"></param> /// <returns></returns> private static System.Data.DataTable ToDataTable(IList objectList) { System.Data.DataTable result = new System.Data.DataTable(); if (objectList.Count <= 0) { return(result); } PropertyInfo[] propertys = objectList[0].GetType().GetProperties(); foreach (PropertyInfo pi in propertys) { result.Columns.Add(pi.Name, pi.PropertyType); } for (int i = 0; i < objectList.Count; i++) { ArrayList tempList = new ArrayList(); foreach (PropertyInfo pi in propertys) { object obj = pi.GetValue(objectList[i], null); tempList.Add(obj); } object[] array = tempList.ToArray(); result.LoadDataRow(array, true); } return(result); }
/// <summary> /// 将list集合转换成datatable /// </summary> /// <param name="list"></param> /// <returns></returns> public System.Data.DataTable ListToDataTable(IList list) { System.Data.DataTable result = new System.Data.DataTable(); if (list.Count > 0) { PropertyInfo[] propertys = list[0].GetType().GetProperties(); foreach (PropertyInfo pi in propertys) { //获取类型 Type colType = pi.PropertyType; //当类型为Nullable<>时 if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition() == typeof(Nullable <>))) { colType = colType.GetGenericArguments()[0]; } result.Columns.Add(pi.Name, colType); } for (int i = 0; i < list.Count; i++) { ArrayList tempList = new ArrayList(); foreach (PropertyInfo pi in propertys) { object obj = pi.GetValue(list[i], null); tempList.Add(obj); } object[] array = tempList.ToArray(); result.LoadDataRow(array, true); } } return(result); }
private System.Data.DataTable ProcessObjects(bool useHeaderRow, bool ignoreEmptyRows, object[,] valueArray) { System.Data.DataTable dt = new System.Data.DataTable(); var beginat = 1; if (useHeaderRow) { for (int k = 1; k <= valueArray.GetLength(1); k++) { dt.Columns.Add((string)valueArray[1, k]); //add columns to the data table. } beginat = 2; } else { for (int k = 1; k <= valueArray.GetLength(1); k++) { dt.Columns.Add(k.ToString()); //add columns to the data table. } beginat = 1; } object[] singleDValue = new object[valueArray.GetLength(1)]; //value array first row contains column names. so loop starts from 2 instead of 1 for (int i = beginat; i <= valueArray.GetLength(0); i++) { bool hasValue = false; for (int j = 0; j < valueArray.GetLength(1); j++) { if (valueArray[i, j + 1] != null) { singleDValue[j] = valueArray[i, j + 1].ToString(); } else { singleDValue[j] = valueArray[i, j + 1]; } if (ignoreEmptyRows && singleDValue[j] != null) { if (!string.IsNullOrEmpty(singleDValue[j].ToString())) { hasValue = true; } } } if (!ignoreEmptyRows) { hasValue = true; } if (hasValue) { dt.LoadDataRow(singleDValue, System.Data.LoadOption.PreserveChanges); } } dt.AcceptChanges(); return(dt); }
public static System.Data.DataTable GetInputDatatable(Activity dnActivity, IEnumerable <LinkedService> linkedServices, IEnumerable <Dataset> datasets) { //SQL or Azure Blob CSV only var inLS = LinkedServiceHelper.GetInputLinkedService(dnActivity, linkedServices, datasets); System.Data.DataTable dsRtn = GetInputDatatableShell(dnActivity, linkedServices, datasets); //Figure out which Type switch (inLS.Properties.Type) { case "AzureStorage": CloudStorageAccount inputStorageAccount = CloudStorageAccount.Parse(((AzureStorageLinkedService)inLS.Properties.TypeProperties).ConnectionString); CloudBlobClient inputClient = inputStorageAccount.CreateCloudBlobClient(); AzureBlobDataset abdInput = datasets.Single(d => d.Name == dnActivity.Inputs.First().Name).Properties.TypeProperties as AzureBlobDataset; CloudBlockBlob cbbInputFile = new CloudBlockBlob(new Uri(inputStorageAccount.BlobEndpoint.AbsoluteUri + abdInput.FolderPath + "/" + abdInput.FileName)); System.IO.MemoryStream ms = new System.IO.MemoryStream(); cbbInputFile.DownloadToStream(ms); ms.Position = 0; using (Microsoft.VisualBasic.FileIO.TextFieldParser tfp = new Microsoft.VisualBasic.FileIO.TextFieldParser(ms)) { tfp.TextFieldType = Microsoft.VisualBasic.FileIO.FieldType.Delimited; tfp.SetDelimiters(","); while (!tfp.EndOfData) { string[] fields = tfp.ReadFields(); dsRtn.LoadDataRow(fields, true); } } break; case "AzureSqlDatabase": AzureSqlTableDataset astInput = datasets.Single(d => d.Name == dnActivity.Inputs.First().Name).Properties.TypeProperties as AzureSqlTableDataset; System.Data.SqlClient.SqlConnection scInput = new System.Data.SqlClient.SqlConnection(((AzureSqlDatabaseLinkedService)inLS.Properties.TypeProperties).ConnectionString); System.Data.SqlClient.SqlCommand commInput = new System.Data.SqlClient.SqlCommand(); commInput.Connection = scInput; commInput.CommandType = System.Data.CommandType.Text; commInput.CommandText = string.Format("SELECT * FROM [{0}]", astInput.TableName); System.Data.SqlClient.SqlDataAdapter sdaInput = new System.Data.SqlClient.SqlDataAdapter(commInput); sdaInput.Fill(dsRtn); break; default: throw new NotImplementedException(); } return(dsRtn); }
/// <summary> /// Retorna un DataTable personalizado para ser usado en la grilla de la UI /// </summary> /// <param name="Cedula"></param> /// <param name="Placa"></param> /// <param name="id_orden"></param> /// <param name="estado">-1 representa que no busque segun el estado</param> /// <returns></returns> public System.Data.DataTable ConnsultarOrdenesAsignadasTecnicosPorCedula_UI_customized(string Cedula, string Placa, string id_orden, int estado) { try { System.Data.DataTable dt = new cls_data_orden().ObtenerOrdenesByTecnicoAsignado_UI(Cedula, Placa, id_orden, estado); //dataGridView1.Columns["tipo_oden"].ValueType = typeof(string); // dt.Columns["tipo_oden"].DataType = typeof(string); System.Data.DataTable dt_clodana = dt.Clone(); dt_clodana.Columns["tipo_oden"].DataType = typeof(string); dt_clodana.Columns["ve_vehiculo_id"].DataType = typeof(string); dt_clodana.Columns["estado"].DataType = typeof(string); dt_clodana.Columns["hora"].DataType = typeof(string); foreach (System.Data.DataRow dr in dt.Rows) { dt_clodana.LoadDataRow(dr.ItemArray, false); } dt.Clear(); dt.Dispose(); for (int i = 0; i < dt_clodana.Rows.Count; i++) { entidades.vialsur.prefectura.per_persona persona_responsable = new logica.vialsur.prefectura.Catalogos.cls_logica_per_persona().Consultar_Per_Persona( dt_clodana.Rows[i]["cedula_responsable"].ToString()); dt_clodana.Rows[i]["cedula_responsable"] = persona_responsable.apellidos + ", " + persona_responsable.nombres; entidades.vialsur.prefectura.per_persona persona_chofer = new logica.vialsur.prefectura.Catalogos.cls_logica_per_persona().Consultar_Per_Persona( dt_clodana.Rows[i]["chofer"].ToString()); dt_clodana.Rows[i]["chofer"] = persona_chofer.apellidos + ", " + persona_chofer.nombres; dt_clodana.Rows[i]["tipo_oden"] = Orden_TipoMantenimientoById(int.Parse(dt_clodana.Rows[i]["tipo_oden"].ToString())); dt_clodana.Rows[i]["ve_vehiculo_id"] = new logica.vialsur.prefectura.Catalogos.cls_logica_ve_vehiculo().GetPlacaById(int.Parse(dt_clodana.Rows[i]["ve_vehiculo_id"].ToString())); dt_clodana.Rows[i]["estado"] = Orden_TipoEstadoById(int.Parse(dt_clodana.Rows[i]["estado"].ToString())); dt_clodana.Rows[i]["hora"] = dt_clodana.Rows[i]["hora"].ToString().Substring(0, 5); dt_clodana.Rows[i]["per_persona_cedula_crea"] = new logica.vialsur.prefectura.Catalogos.cls_logica_per_persona().Consultar_Per_Persona( dt_clodana.Rows[i]["per_persona_cedula_crea"].ToString()).ApellidosNombres; } return(dt_clodana); } catch (Exception ex) { throw ex; } }
private System.Data.DataTable GenerateDataTable <T>(int rows) { var datatable = new System.Data.DataTable(typeof(T).Name); typeof(T).GetProperties().ToList().ForEach( x => datatable.Columns.Add(x.Name.Remove(0, 3))); Builder <T> .CreateListOfSize(rows).Build() .ToList().ForEach( x => datatable.LoadDataRow(x.GetType().GetProperties().Select( y => y.GetValue(x, null)).ToArray(), true)); return(datatable); }
private System.Data.DataTable ProcessObjects(bool useHeaderRow, object[,] valueArray) { System.Data.DataTable dt = new System.Data.DataTable(); #region Get the COLUMN names if (useHeaderRow) { for (int k = 1; k <= valueArray.GetLength(1); k++) { dt.Columns.Add((string)valueArray[1, k]); //add columns to the data table. } } else { for (int k = 1; k <= valueArray.GetLength(1); k++) { dt.Columns.Add(k.ToString()); //add columns to the data table. } } #endregion #region Load Excel SHEET DATA into data table object[] singleDValue = new object[valueArray.GetLength(1)]; //value array first row contains column names. so loop starts from 2 instead of 1 for (int i = 2; i <= valueArray.GetLength(0); i++) { for (int j = 0; j < valueArray.GetLength(1); j++) { if (valueArray[i, j + 1] != null) { singleDValue[j] = valueArray[i, j + 1].ToString(); } else { singleDValue[j] = valueArray[i, j + 1]; } } dt.LoadDataRow(singleDValue, System.Data.LoadOption.PreserveChanges); } #endregion return(dt); }
public void It_should_find_the_max_value_in_the_given_range_tuple() { var dataRows = new[] { _DataTable.LoadDataRow(new object[] { "Item-1", 3.25 }, true), _DataTable.LoadDataRow(new object[] { "Item-2", 5.75 }, true), _DataTable.LoadDataRow(new object[] { "Item-3", 2.00 }, true) }; var maxDataRow = _DataTable.Max <decimal>(((1, 0), (1, 2))); Assert.AreEqual(dataRows[1], maxDataRow); }
public void It_should_find_the_max_value_in_the_specified_column() { var dataRows = new[] { _DataTable.LoadDataRow(new object[] { "Price", 18.25 }, true), _DataTable.LoadDataRow(new object[] { "Price", 22.75 }, true), _DataTable.LoadDataRow(new object[] { "Price", 3 }, true), _DataTable.LoadDataRow(new object[] { "Tax", 15 }, true), _DataTable.LoadDataRow(new object[] { "Delivery", 5 }, true), _DataTable.LoadDataRow(new object[] { "Delivery", 7.5 }, true) }; var maxPerGroup = _DataTable.GroupBy("A1:A?").Max <decimal>(1); CollectionAssert.AreEquivalent(new [] {
/// <summary> /// Author:王达国 /// Time:2015.10.28 /// Content:将集合类转换成DataTable /// </summary> /// <param name="list">集合</param> /// <returns></returns> public static System.Data.DataTable ListToDataTable(IList list) { System.Data.DataTable result = new System.Data.DataTable(typeof(T).Name); result = CreateData(); if (list.Count > 0) { PropertyInfo[] propertys = list[0].GetType().GetProperties(); for (int i = 0; i < list.Count; i++) { ArrayList tempList = new ArrayList(); foreach (PropertyInfo pi in propertys) { object obj = pi.GetValue(list[i], null); tempList.Add(obj); } object[] array = tempList.ToArray(); result.LoadDataRow(array, true); } } return(result); }
protected override void DumpControls() { /* * _row = sy_Usuarios.GetByPk(this._idUsuario); * string encriptada = new mz.erp.security.dll.Encryption.Symmetric().EncryptStringBase64( this._claveNueva2 ); * _row.Clave = encriptada; * _data.sy_Usuarios.ImportRow(_row); * * */ System.Data.DataTable tableUsuarios = _data.Tables["sy_Usuarios"]; System.Data.DataTable tableUsuariosPerfiles = _data.Tables["sy_PerfilesUsuariosEmpresas"]; System.Data.DataRow rowUsuarios = tableUsuarios.Rows[0]; rowUsuarios["IdUsuario"] = txtNombreUsuario.Text; //rowUsuarios["Clave"] = ultraTextEditor1.Text; string encriptada = new mz.erp.security.dll.Encryption.Symmetric().EncryptStringBase64(ultraTextEditor1.Text); rowUsuarios["Clave"] = encriptada; string IdPersona = Convert.ToString(mzCmbPersona.DataValue); if (IdPersona != null && !IdPersona.Equals(string.Empty)) { rowUsuarios["IdPersona"] = IdPersona; System.Data.DataRow row = mz.erp.businessrules.tsh_Personas.GetByPk(IdPersona); if (row != null) { rowUsuarios["Nombre"] = row["Nombre"]; } else { rowUsuarios["Nombre"] = txtNombreUsuario.Text; } } rowUsuarios["VencimientoClave"] = mz.erp.businessrules.Sistema.DateTime.Now; rowUsuarios["HabilitacionCuenta"] = mz.erp.businessrules.Sistema.DateTime.Now; rowUsuarios["Expiracion"] = mz.erp.businessrules.Sistema.DateTime.Now; rowUsuarios["Activo"] = chkActivo.Checked; //Viviana rowUsuarios["Equipo"] = this.equipo.Value; if (!ultraTextEditor1.Text.Equals(uteConfirmaPassword.Text)) { rowUsuarios.SetColumnError("Clave", "Las claves no coinciden"); } //if(this.State.Equals("NEW")) //{ for (int i = 0; i < perfiles.Count; i++) //por cada item { bool ok = false; Perfil p = (Perfil)perfiles[i]; if (p.Tildado) //si esta tildado --> lo agrego (si no está) { foreach (System.Data.DataRow row in tableUsuariosPerfiles.Rows) { if (row.RowState != System.Data.DataRowState.Deleted) { if ((long)row["IdPerfil"] == Convert.ToInt64(p.IdPerfil)) { row["IdUsuario"] = txtNombreUsuario.Text; ok = true; break; } } } if (!ok) { System.Data.DataRow row = mz.erp.businessrules.sy_PerfilesUsuariosEmpresas.NewRow(); row["IdUsuario"] = txtNombreUsuario.Text; row["IdPerfil"] = p.IdPerfil; row["IdEmpresa"] = Security.IdEmpresa; tableUsuariosPerfiles.LoadDataRow(row.ItemArray, false); } } else //si no esta tildado { if (p.Tildado != p.EstadoInicial) // y es distinto del estado anterior -->(borrarlo de la base) { foreach (System.Data.DataRow row in tableUsuariosPerfiles.Rows) { if (row.RowState != System.Data.DataRowState.Deleted) { string IdPerfil = Convert.ToString(row["IdPerfil"]); string IdUsuario = Convert.ToString(row["IdUsuario"]); if (p.IdPerfil == IdPerfil && txtNombreUsuario.Text == IdUsuario) { //borro la tupla row.Delete(); break; } } } } } } //} //Modifico los responsables de las secciones sy_UsuariosExDataset.tlg_ResponsablesDepositosSeccionesDataTable tableResponsablesSecciones = (sy_UsuariosExDataset.tlg_ResponsablesDepositosSeccionesDataTable)_data.Tables["tlg_ResponsablesDepositosSecciones"]; string IdUsu = (string)rowUsuarios["IdUsuario"]; foreach (TreeNode nodeD in treeViewSecciones.Nodes) { string IdDeposito = (string)idsTree[nodeD]; foreach (TreeNode nodeS in nodeD.Nodes) { string IdSeccion = (string)idsTree[nodeS]; sy_UsuariosExDataset.tlg_ResponsablesDepositosSeccionesRow rowRS = tableResponsablesSecciones.FindByIdUsuarioIdDepositoIdSeccion(IdUsu, IdDeposito, IdSeccion); if (nodeS.Checked) //si esta tildado --> lo agrego (si no está) { if (rowRS == null) { rowRS = tableResponsablesSecciones.Newtlg_ResponsablesDepositosSeccionesRow(); rowRS.IdUsuario = IdUsu; rowRS.IdDeposito = IdDeposito; rowRS.IdSeccion = IdSeccion; rowRS.Activo = true; rowRS.FechaCreacion = DateTime.Now; rowRS.IdConexionCreacion = Security.IdConexion; tableResponsablesSecciones.LoadDataRow(rowRS.ItemArray, false); } } else //si no esta tildado --> lo borro (si está) { if (rowRS != null) { rowRS.Delete(); } } } } }
private System.Data.DataTable ProcessObjects(Microsoft.Office.Interop.Excel.Range range, bool useHeaderRow, bool guessColumnType, bool ignoreEmptyRows, object[,] valueArray) { System.Data.DataTable dt = new System.Data.DataTable(); var beginat = 1; if (useHeaderRow) { for (int k = 1; k <= valueArray.GetLength(1); k++) { var v = (worksheet.Cells[range.Row + 1, range.Column + (k - 1)] as Range).get_Value(Type.Missing); if (v == null) { v = (worksheet.Cells[range.Row, range.Column + (k - 1)] as Range).get_Value(Type.Missing); } Type type = typeof(string); if (guessColumnType && v != null) { type = v.GetType(); } dt.Columns.Add((string)valueArray[1, k], type); //add columns to the data table. } beginat = 2; } else { for (int k = 1; k <= valueArray.GetLength(1); k++) { var v = (worksheet.Cells[range.Row, range.Column + (k - 1)] as Range).get_Value(Type.Missing); Type type = typeof(string); if (guessColumnType && v != null) { type = v.GetType(); } dt.Columns.Add(k.ToString(), type); //add columns to the data table. } beginat = 1; } object[] singleDValue = new object[valueArray.GetLength(1)]; //value array first row contains column names. so loop starts from 2 instead of 1 for (int i = beginat; i <= valueArray.GetLength(0); i++) { bool hasValue = false; for (int j = 0; j < valueArray.GetLength(1); j++) { if (valueArray[i, j + 1] != null) { singleDValue[j] = valueArray[i, j + 1].ToString(); } else { singleDValue[j] = valueArray[i, j + 1]; } if (ignoreEmptyRows && singleDValue[j] != null) { if (!string.IsNullOrEmpty(singleDValue[j].ToString())) { hasValue = true; } } } if (!ignoreEmptyRows) { hasValue = true; } if (hasValue) { dt.LoadDataRow(singleDValue, System.Data.LoadOption.PreserveChanges); } } dt.AcceptChanges(); return(dt); }