private void btnCrearDE_Click(object sender, EventArgs e)
{
ILog Logger = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
Stream myStream = null;
OpenFileDialog openFileDialog1 = new OpenFileDialog();
lblFechaInicioText.Text = "";
lblFechaFinText.Text = "";
lblResultado.Text = "";
openFileDialog1.InitialDirectory = "D:\\";
openFileDialog1.Filter = "Excel Files(.xls)|*.xls";
openFileDialog1.FilterIndex = 2;
openFileDialog1.RestoreDirectory = true;
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
try
{
btnCrearDE.Enabled = false;
string mensaje = string.Empty;
if ((myStream = openFileDialog1.OpenFile()) != null)
{
using (myStream)
{
lblFechaInicioText.Text = DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToLongTimeString();
string fullPath = openFileDialog1.FileName;
if (System.IO.File.Exists(fullPath))
{
using (var fileStreamUpload = new FileStream(fullPath, FileMode.Open, FileAccess.Read))
{
var workbook = new HSSFWorkbook(fileStreamUpload);
ISheet sheet = workbook.GetSheet("DataExtension");
bool result = false;
DataExtensionHelper dataExtensionHelper = new DataExtensionHelper();
DataExtensionHead dataExtensionHead = new DataExtensionHead();
dataExtensionHead.Nombre = sheet.GetRow(1).GetCell(1).ToString();
dataExtensionHead.Descripcion = sheet.GetRow(2).GetCell(1).ToString();
int usadoParaEnviar;
result = Int32.TryParse(sheet.GetRow(3).GetCell(1).ToString(), out usadoParaEnviar);
if (!result)
{
throw new Exception("El valor de Usado para enviar es inválido");
}
else
{
dataExtensionHead.EsUsadaComoBase = (usadoParaEnviar == (int)TipoSiNo.Si);
}
if (dataExtensionHead.EsUsadaComoBase)
{
dataExtensionHead.NombreBaseFuente = sheet.GetRow(3).GetCell(3).ToString();
dataExtensionHead.NombreBaseMostrar = sheet.GetRow(3).GetCell(5).ToString();
}
int rowNumber = 6;
while (sheet.GetRow(rowNumber)!= null)
{
DataExtensionDetalle dataExtensionDetalle = new DataExtensionDetalle();
string nombreCampo = sheet.GetRow(rowNumber).GetCell(0).ToString();
dataExtensionDetalle.Campo = nombreCampo;
int tipoDato;
result = Int32.TryParse(sheet.GetRow(rowNumber).GetCell(1).ToString(), out tipoDato);
if (!result)
{
throw new Exception("El Tipo de Dato del campo " + nombreCampo + " es inválido");
}
else
{
dataExtensionDetalle.Tipo = tipoDato;
}
int tamanio;
result = Int32.TryParse(sheet.GetRow(rowNumber).GetCell(2).ToString(), out tamanio);
if (!result)
{
throw new Exception("El Tamaño del campo " + nombreCampo + " es inválido");
}
else
{
dataExtensionDetalle.Tamanio = tamanio;
}
int clavePrimaria;
result = Int32.TryParse(sheet.GetRow(rowNumber).GetCell(3).ToString(), out clavePrimaria);
if (!result)
{
throw new Exception("El valor ingresado en Clave Primaria del campo " + nombreCampo + " es inválido");
}
else
{
dataExtensionDetalle.EsClavePrimaria = (clavePrimaria == (int)TipoSiNo.Si);
}
int campoNulo;
result = Int32.TryParse(sheet.GetRow(rowNumber).GetCell(4).ToString(), out campoNulo);
if (!result)
{
throw new Exception("El valor ingresado en Campo Nulo del campo " + nombreCampo + " es inválido");
}
else
{
dataExtensionDetalle.EsNulo = (campoNulo == (int)TipoSiNo.Si);
}
dataExtensionDetalle.ValorPorDefecto = sheet.GetRow(rowNumber).GetCell(5).ToString();
dataExtensionHead.DataExtensionDetalleList.Add(dataExtensionDetalle);
rowNumber++;
}
mensaje = dataExtensionHelper.Crear(dataExtensionHead);
}
}
lblFechaFinText.Text = DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToLongTimeString();
lblResultado.Text = mensaje;
}
}
btnCrearDE.Enabled = true;
}
catch (Exception ex)
{
lblResultado.Text = "Hubo un error en la generación de DataExtension.";
Logger.Info(ex.Message);
}
}
}
public string Crear(DataExtensionHead dataExtensionHead)
{
string respuesta = string.Empty;
//Create DataExtension object [Subscribers > Data Extensions > Data Extensions]
DataExtension de = new DataExtension();
de.CustomerKey = dataExtensionHead.Nombre;
de.Name = dataExtensionHead.Nombre;
de.Description = dataExtensionHead.Descripcion;
//Is this data base table going to house Subscribers?
if(dataExtensionHead.EsUsadaComoBase)
{
//Is this data base table going to house Subscribers?
//de.IsSendable = true;
//de.IsSendableSpecified = true;
de.IsSendable = dataExtensionHead.EsUsadaComoBase;
de.IsSendableSpecified = dataExtensionHead.EsUsadaComoBase;
//The Name of the Column in the DE to map to ET
de.SendableDataExtensionField = new DataExtensionField();
de.SendableDataExtensionField.FieldType = DataExtensionFieldType.Text;
de.SendableDataExtensionField.Name = dataExtensionHead.NombreBaseFuente;
//The Name of the Attribute in ET to Send to
de.SendableSubscriberField = new PartnerAPI.Attribute();
de.SendableSubscriberField.Name = dataExtensionHead.NombreBaseMostrar;//If Subscriber Key is turned on this must be Subscriber Key
}
List<DataExtensionField> fields = new List<DataExtensionField>();
foreach (var detalle in dataExtensionHead.DataExtensionDetalleList)
{
//Create the field
DataExtensionField field = new DataExtensionField();
field.Name = detalle.Campo;
field.FieldType = GetTypeDataExtension(detalle.Tipo);
field.FieldTypeSpecified = true;
field.IsRequired = !detalle.EsNulo;
field.IsRequiredSpecified = !detalle.EsNulo;
field.IsPrimaryKey = detalle.EsClavePrimaria;
field.IsPrimaryKeySpecified = detalle.EsClavePrimaria;
if (field.IsPrimaryKey || detalle.Tamanio>0)
{
field.MaxLength = detalle.Tamanio;
field.MaxLengthSpecified = true;
}
fields.Add(field);
}
de.Fields = fields.ToArray();
try
{
string cRequestID = String.Empty;
string cStatus = String.Empty;
//Call the Create method on the Subscriber object
CreateResult[] cResults = conexion.ETCliente.Create(new CreateOptions(), new APIObject[] { de }, out cRequestID, out cStatus);
//Display Results
respuesta += "Overall Status: " + cStatus;
respuesta += "<br/>";
respuesta += "Number of Results: " + cResults.Length;
respuesta += "<br/>";
//Loop through each object returned and display the StatusMessage
foreach (CreateResult cr in cResults)
{
respuesta += "Status Message: " + cr.StatusMessage;
respuesta += "<br/>";
}
}
catch (Exception exCreate)
{
//Set Message
respuesta += "<br/><br/>CREATE ERROR:<br/>" + exCreate.Message;
}
return respuesta;
}
