jueves, 22 de mayo de 2014

How convert a list object to dataset -Como convertir una lista de objetos en dataset

  Public Function ListObjectToDataSet(Of T)(ByVal list As IList(Of T)) As DataSet
        Dim elementType As Type
        Dim ds As New DataSet()
        Dim t1 As New DataTable()
        Try
            elementType = GetType(T)
            ds.Tables.Add(t1)
            'add a column to table for each public property on T
            For Each propInfo In elementType.GetProperties()
                Dim ColType As Type = If(Nullable.GetUnderlyingType(propInfo.PropertyType), propInfo.PropertyType)
                t1.Columns.Add(propInfo.Name, ColType)
            Next
            'go through each property on T and add each value to the table      
            For Each item As T In list
                Dim row As DataRow = t1.NewRow()
                For Each propInfo In elementType.GetProperties()
                    row(propInfo.Name) = If(propInfo.GetValue(item, Nothing), DBNull.Value)
                Next
                t1.Rows.Add(row)
            Next
            Return ds
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

martes, 25 de marzo de 2014

Removing leading zeros in a nvarchar sql -Remover ceros a la izquierda en un nvarchar sql

SUBSTRING(Col1, PATINDEX('%[^0 ]%', Col1 + ' '), LEN(Col1))
ejemplo
select SUBSTRING('0052658f', PATINDEX('%[^0 ]%', '0052658f' + ' '), LEN('0052658f'))

resultado
52658f

jueves, 31 de octubre de 2013

How to concatenate all strings from a certain column for each group--Como concatenar string de cierta columna en un grupo de registros en sql

SELECT  (SELECT STUFF((
      SELECT '/'+ cast(NombrePasajero AS NVARCHAR(255))
      FROM  tblREserva REPA
      WHERE  REPA.CodigoResrva=123
      FOR XML PATH('')
      )
      ,1,1,'')) AS Pasajeros,
      intCantAdultos,
      intCantNinos
      intCantInfantes,
     

How validate email structure in javascript -Como validar la estructura de un correo electronico en javascript

 
function fnValidate() {

 
EmailHotel = document.getElementById( "MasterBody_txtEmailHotel").value;

if (EmailHotel.trim() == '') {

alert('Ingrese el o los correos electrónicos del hotel.');
document.getElementById( "MasterBody_txtEmailHotel").focus();

document.getElementById("MasterBody_OkButton").disabled = false;

return;

}

else {

var emails = document.getElementById("MasterBody_txtEmailHotel").value;

var emailArray = emails.split(/[';',',']+/);

var valid = true

for (i = 0; i <= (emailArray.length - 1) ; i++) {

if (checkEmail(emailArray[i].trim())) {

}

else {

alert("Correo inválido: " + emailArray[i]);

valid = false

}

}

if (valid == false) {

document.getElementById("MasterBody_txtEmailHotel").focus();

document.getElementById("MasterBody_OkButton").disabled = false;

document.getElementById("MasterBody_CancelButton").disabled = false;

return;

}

}

if (EmailHotel.trim().length > 200) {

alert( 'El correo electrónico del hotel no puede poseer más de 200 caracteres.');
document.getElementById( "MasterBody_txtEmailHotel").focus();
document.getElementById( "MasterBody_OkButton").disabled = false;
document.getElementById( "MasterBody_CancelButton").disabled = false;
return;

}
}
function checkEmail(email) {
var regExp = /^[-A-Za-z0-9~!$%^&*_=+}{\'?_]+(\.[-A-Za-z0-9~!$%^&*_=+}{\'?_]+)*@([A-Za-z0-9_][-A-Za-z0-9_]*(\.[-A-Za-z0-9_]+)*\.(aero|arpa|biz|com|coop|edu|gov|info|int|mil|museum|name|net|org|pro|travel|mobi|AERO|ARPA|BIZ|COM|COOP|EDU|GOV|INFO|INT|MIL|MUSEUM|NAME|NET|ORG|PRO|TRAVEL|MOBI|[a-z][a-z]|[A-Z][A-Z])|([0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}))(:[0-9]{1,5})?$/

return regExp.test(email);

}

How validate credentials to connect ftp -Como validar las credenciales para conectarse a un ftp


Private Sub ValidateFtp()
Dim dir As String = txtDireccionFtp.Text.Trim
Dim user As String = txtUsrFtp.Text.Trim
Dim pass As String = txtClaveFtp.Text.Trim
Dim peticionFTP As FtpWebRequest
Dim Script As String
' Creamos una peticion FTP con la dirección del objeto que queremos saber si existe
Try
peticionFTP = CType(WebRequest.Create(New Uri(dir)), FtpWebRequest)Catch
Script = "alert('Ruta ftp incorrecta');"
ClientScript.RegisterStartupScript(Me.GetType, "scriptSinDatos", Script, True)
Exit Sub
End Try
' Fijamos el usuario y la contraseña de la petición
peticionFTP.Credentials =New NetworkCredential(user, pass)
'Para saber si el objeto existe, solicitamos la fecha de creación del mismo

peticionFTP.Method = WebRequestMethods.Ftp.PrintWorkingDirectory

peticionFTP.UsePassive = False
Try
Dim respuestaFTP As FtpWebResponse
respuestaFTP = CType(peticionFTP.GetResponse(), FtpWebResponse)
Script = "alert('Credenciales y ruta ftp correctas');"

ClientScript.RegisterStartupScript(Me.GetType, "ftpValida", Script, True)
respuestaFTP.Close()

peticionFTP.Abort()


Catch ex As Exception

Script =
"alert('Credenciales incorrectas');"

ClientScript.RegisterStartupScript(Me.GetType, "ftpInvalida", Script, True)
 
End Try

End Sub

Hows export to csv and txt from vb.net ---Como exportar a csv y txt desde vb.net

'filename ej informe1.csv
'sepChar ej | o , o ;
Sub DataTable2CSV(ByVal table As DataTable, ByVal filename As String, _ByVal sepChar As String)
Dim writer As System.IO.StreamWriter
Try
writer = New System.IO.StreamWriter(filename)
Dim xx As String = DT2CSV(table, sepChar)
writer.Write(xx)
Finally
If Not writer Is Nothing Then writer.Close()
End Try
End Sub

'En esta funcion formateamos los datos para que el aneviarlos al csv cada columna del datatable quede en una columna del archivo
Public Function DT2CSV(ByVal DT As DataTable, Optional ByVal Separator As String = Nothing) As String
If DT Is Nothing Then Return String.Empty
If DT.Rows.Count = 0 Then Return String.Empty
Dim Result As String = String.Empty
'Inicializamos los encabezados
For I As Integer = 0 To DT.Columns.Count - 1
Result = Result &
"""" & DT.Columns(I).ColumnName & """" & Separator
Next
Result = Result.Substring(0, Result.Length - 1) & vbCrLfDim Line As String = String.Empty
For I As Integer = 0 To DT.Rows.Count - 1
For H As Integer = 0 To DT.Columns.Count - 1
If Not IsDBNull(DT.Rows(I)(H)) Then
Line = Line & """" & Quitar(CStr(DT.Rows(I)(H))) & """" & SeparatorElse Line = Line & """" & String.Empty & """" & Separator
End If
Next
Line = Line.Substring(0, Line.Length - 1)
Result = Result & Line & vbCrLf
Line = String.Empty
Next Return Result
End Function

'Con esta función eliminamos los espacios como enter tabs entre otros que pueden dañar el formatoPublic Function Quitar(ByVal Value As String, Optional ByVal Separador As String = Nothing) As String
Value = Value.Replace(vbCr, "") 
Value = Value.Replace(vbCrLf, "")Value = Value.Replace(vbLf, "")
Value = Value.Replace(vbTab, "")
Value = Value.Replace(vbNewLine, "")
Value = Value.Replace("\n", "")
Value = Value.Replace("\r", "")
Replace(Value, vbNewLine, "", , , vbTextCompare)
Value = Replace(Replace(Replace(Value, Chr(10), ""), Chr(13), ""), Chr(9), "")
If Not Separador Is Nothing Then

Value = Value.Replace(Separador, String.Empty)End If
Return Value
End Function


Sub DataTable2TXT(ByVal table As DataTable, ByVal filename As String, _ByVal sepChar As String)
Dim writer As System.IO.StreamWriter

Try
writer = New System.IO.StreamWriter(filename)' first write a line with the columns name Dim sep As String = ""
Dim builder As New System.Text.StringBuilder
For Each col As DataColumn In table.Columns
builder.Append(sep).Append(Quitar(col.ColumnName.ToString))
sep = sepChar
Next
writer.WriteLine(builder.ToString())
' then write all the rows
For Each row As DataRow In table.Rows
sep = ""
builder =New System.Text.StringBuilder
 For Each col As DataColumn In table.Columns
builder.Append(sep).Append(row(Quitar(col.ColumnName.ToString)))
sep = sepCharNext
writer.WriteLine(builder.ToString())Next
Finally
If Not writer Is Nothing Then writer.Close()

End Try
End Sub



How connect to sql database in script task SSIS --Como conectarse a una base de datos sql en un script task de SSIS

  • Create a New ADO .Net Connection  "BDRepoManager"
  • Create a script task right click and edit script
 Public Sub Main()
        Dim sqlConn As SqlClient.SqlConnection       ' Object to hold connection
        Dim sqlCmd As SqlCommand                     ' SQL Command object
        Dim rdrReader As SqlDataReader               ' Data reader to hold output of command
        Dim sqlCmdText As String                     ' SQL Command text
        sqlConn = DirectCast(Dts.Connections("BDRepoManager").AcquireConnection(Dts.Transaction), SqlClient.SqlConnection)

   sqlCmdText = Dts.Variables("User::storedProcedure").Value
        ' Execute the command and put the results into the data reader
        sqlCmd = New SqlCommand(sqlCmdText, sqlConn)
        sqlCmd.CommandType = CommandType.StoredProcedure
        Dim parameters As String = Dts.Variables("User::parameters").Value
        Dim parametersArray() As String
        parametersArray = parameters.Split(",")
        Dim paramValues As String = Dts.Variables("User::paramValues").Value
        Dim paramValuesArray() As String
        paramValuesArray = paramValues.Split(",")
        For i As Integer = 0 To parametersArray.Length - 1
            sqlCmd.Parameters.Add(New SqlParameter(parametersArray(i), paramValuesArray(i)))
        Next
        Dim sda As New SqlDataAdapter()
        Dim dt As New DataTable()
        sda.SelectCommand = sqlCmd
        sda.Fill(dt)
  End Sub