Ver información vistas SQL

select * from INFORMATION_SCHEMA.VIEWS

xml

-Cargar documento xml
Dim doc As XDocument = XDocument.Load("ruta archivo xml")

Guardar archivo

Dim ruta As String="ruta de origen"
sv1.FileName = "nombre del archivo"

            If sv1.ShowDialog = System.Windows.Forms.DialogResult.OK Then
                Try
                    If File.Exists(sv1.FileName) Then
                        Kill(sv1.FileName)
                    End If
                    File.Copy(ruta, sv1.FileName)
                    Kill(ruta)
                    MessageBox.Show("Fin!", "", MessageBoxButtons.OK, MessageBoxIcon.Information)
                Catch ex As Exception
                End Try
            End If

ExecuteScalar sql

Dim conex As String =""
Dim con As New SqlConnection(conex)
Try
Dim query As String=""
con.Open()
 Using cmd As New SqlCommand(query, con)
                cmd.Parameters.Add("@id", SqlDbType.Int).Value = TextBox.Text
                id_adjunto_IN = cmd.ExecuteScalar()
            End Using
con.Close()
Catch ex As Exception
con.close()
End Try

Obtener carpeta temporal de windows

Dim tempPath As String = Path.GetTempPath()

C:\Users\user\AppData\Local\Temp\

fuente

Enmascarar caracteres contraseña

Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        DataGridView1.Rows.Add("usuario1", "123456")
        DataGridView1.Rows.Add("usuario2", "abcdef")
    End Sub

    Private Sub DataGridView1_CellFormatting(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles DataGridView1.CellFormatting
        If e.ColumnIndex = 1 AndAlso e.Value <> Nothing AndAlso Not CheckBox1.Checked Then
            e.Value = New String("*", e.Value.ToString().Length)
        End If
    End Sub

    Private Sub CheckBox1_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CheckBox1.CheckedChanged
        DataGridView1.Refresh()
    End Sub
End Class

fuente

Obtener archivos de un directorio con filtro-patrón de selección

Dim directorio As String = "D:\compra"
        Dim rgx As String = "2007-06*.*"
        Dim dirs As String() = Directory.GetFiles(directorio, rgx, SearchOption.TopDirectoryOnly).ToArray

fuente

Obtener nombre del penúltimo directorio

Dim dirfolder As String = "D:\bin\Debug\empresas\789-K"
Dim namefolder As String = Path.GetDirectoryName(dirfolder).Split("\").LastOrDefault


Obtener el directorio si la ruta viene con un archivo

Path.GetDirectoryName(D:\capturas\1-9\config.txt)

resultado: D:\capturas\1-9

Obtener las carpetas de un directorio

Dim directorios() As String = Directory.GetDirectories(Directory.GetCurrentDirectory & "\" & dirEmpresas)

Remover un TabPage de un TabControl

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        TabControl1.TabPages.Remove(TabPage2)
    End Sub

Codificación base 64

Dim bytefile As Byte() = File.ReadAllBytes(ruta_archivo)
Dim file As String  = Convert.ToBase64String(bytefile)

Crear hash md5

Imports System.Security.Cryptography
Imports System.Text

Public Class getmd5

    Public Function getmd5hash(ByVal md5Hash As MD5, ByVal input As String) As String
        Dim data As Byte() = md5Hash.ComputeHash(Encoding.UTF8.GetBytes(input))
        Dim sBuilder As New StringBuilder()
        Dim i As Integer
        For i = 0 To data.Length - 1
            sBuilder.Append(data(i).ToString("x2"))
        Next i
        Return sBuilder.ToString()
    End Function
End Class

Private Sub btnok_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnok.Click
        Try
            Dim bc_getmd5 As New getmd5
            Dim source As String = tbdtefolioclave.Text.Trim
            Dim hash As String = ""
            Using md5hash As MD5 = MD5.Create()
                hash = bc_getmd5.getmd5hash(md5hash, source)
            End Using
            tbmd5.Text = hash
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try
    End Sub

Ejemplo2
'dte+folio+clave
Dim source As String = "33" & "123" & "123456"
Dim hash As String = getHashMd5(source) 'Resultado: 2492f78aa4119e8d5c545ce9006f56b1


Public Function getHashMd5(ByVal stringSource As String) As String
        Using md5hash As MD5 = MD5.Create
            Dim data As Byte() = md5hash.ComputeHash(Encoding.UTF8.GetBytes(stringSource))
            Dim sBuilder As New StringBuilder()
            Dim i As Integer
            For i = 0 To data.Length - 1
                sBuilder.Append(data(i).ToString("x2"))
            Next i
            Return sBuilder.ToString()
        End Using
    End Function




fuente

Cargar un tabpage especifico al iniciar programa

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        TabControl1.SelectedTab = TabPage2
    End Sub

Obtener el nombre del último directorio

For Each d As String In empresas
Dim dname As String = New DirectoryInfo(d).Name
Next


Dim dirfolder As String = "D:\bin\Debug\empresas\789-K"
Dim namefolder As String = Path.GetFileName(dirfolder)

Cantidad de directorios en un directorio

Dim nDir As Integer = Directory.GetDirectories(DirEmpresas).Length

Quitar los ceros a la izquierda en un string sql

declare @x1 varchar (10)=SUBSTRING('000123', PATINDEX('%[^0]%','000123'+'.'), LEN('000123'))
print @x1

fuente

If en sql

IF @variable
BEGIN
--
END
ELSE IF @variable
BEGIN
--
END
ELSE
BEGIN
--
END

fuente:https://stackoverflow.com/questions/20413064/multiple-separate-if-conditions-in-sql-server

validar rut sql

DECLARE @rut VARCHAR(8)
DECLARE @dv CHAR
DECLARE @ntotal INT

SET @rut = '4321'
SET @dv = '4'
SET @rut = REVERSE(@rut)
SET @ntotal = LEN(@rut)

DECLARE @n INT = 0
DECLARE @sumarut INT = 0
DECLARE @mul INT = 1

WHILE @n < @ntotal
BEGIN
SET @mul = @mul + 1
SET @sumarut = @sumarut + SUBSTRING(@rut,@n+1,1) * @mul 

IF @mul = 7
BEGIN
SET @mul = 1
END
SET @n = @n + 1
END -- fin while

DECLARE @calcdv CHAR(2)
SET @calcdv = 11 - @sumarut % 11

IF @calcdv='10'
BEGIN
SET @calcdv='K'
END
ELSE IF @calcdv='11'
BEGIN
SET @calcdv='0'
END

print @calcdv

IF @calcdv = @dv
BEGIN
print 'El rut es válido'
END
ELSE
BEGIN
print 'El rut es inválido'
END

Obtener fecha en sql, formato año-mes-dia

DECLARE @f VARCHAR(20)
DECLARE @anio CHAR(4)
DECLARE @mes CHAR(2)
DECLARE @dia CHAR(2)
SET @f = (SELECT SYSDATETIME())
SET @anio = SUBSTRING(@f,1,4)
SET @mes = SUBSTRING(@f,6,2)
SET @dia = SUBSTRING(@f,9,2)
PRINT @anio + '-' + @mes + '-' + @dia

Habilitar-deshabilitar trigger sql

disable trigger no_delete_admin on empresas
ALTER TABLE empresas ENABLE TRIGGER no_delete_admin
ALTER TABLE table_name DISABLE TRIGGER tr_name


fuente

Tipo de bulto, factura exportación, ambiente certificación

No especifica el tipo de bulto según https://www.aduana.cl/aduana/site/artic/20101026/pags/20101026191745.html
utilizar:

CONTENEDOR REFRIGERADO=75