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