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
Solved_Bugs
jueves, 22 de mayo de 2014
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
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,
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 archivoPublic 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.EmptyNext 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
'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 archivoPublic 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.EmptyNext 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
Etiquetas:
csv,
datatable,
export,
streamwriter,
txt
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
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
Etiquetas:
ado.net,
conenction,
scripttask,
sql,
ssis
Suscribirse a:
Entradas (Atom)