Bases de Datos – Conociendo aun mas

IndexedDB: Tu base de datos local en HTML5

Usar GSM Modem para enviar mensajes SMS C#

https://www.c-sharpcorner.com/UploadFile/e881fb/send-sms-using-gsm-modem-with-C-Sharp/

Crear clase C# desde tabla SQL

Usar lo siguiente


declare @TableName sysname = 'TableName'

declare @Result varchar(max) = 'public class ' + @TableName + '

{'
select @Result = @Result + '

    public ' + ColumnType + NullableSign + ' ' + ColumnName + ' { get; set; }

'

from

(

    select

        replace(col.name, ' ', '_') ColumnName,

        column_id ColumnId,

        case typ.name

            when 'bigint' then 'long'

            when 'binary' then 'byte[]'

            when 'bit' then 'bool'

            when 'char' then 'string'

            when 'date' then 'DateTime'

            when 'datetime' then 'DateTime'

            when 'datetime2' then 'DateTime'

            when 'datetimeoffset' then 'DateTimeOffset'

            when 'decimal' then 'decimal'

            when 'float' then 'float'

            when 'image' then 'byte[]'

            when 'int' then 'int'

            when 'money' then 'decimal'

            when 'nchar' then 'char'

            when 'ntext' then 'string'

            when 'numeric' then 'decimal'

            when 'nvarchar' then 'string'

            when 'real' then 'double'

            when 'smalldatetime' then 'DateTime'

            when 'smallint' then 'short'

            when 'smallmoney' then 'decimal'

            when 'text' then 'string'

            when 'time' then 'TimeSpan'

            when 'timestamp' then 'DateTime'

            when 'tinyint' then 'byte'

            when 'uniqueidentifier' then 'Guid'

            when 'varbinary' then 'byte[]'

            when 'varchar' then 'string'

            else 'UNKNOWN_' + typ.name

        end ColumnType,

        case

            when col.is_nullable = 1 and typ.name in ('bigint', 'bit', 'date', 'datetime', 'datetime2', 'datetimeoffset', 'decimal', 'float', 'int', 'money', 'numeric', 'real', 'smalldatetime', 'smallint', 'smallmoney', 'time', 'tinyint', 'uniqueidentifier')

            then '?'

            else ''

        end NullableSign

    from sys.columns col

        join sys.types typ on

            col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id

    where object_id = object_id(@TableName)

) t

order by ColumnId
set @Result = @Result  + '

}'
print @Result

Collation en SQL Server

Problema detectado: Error en las consultas porque las bases de datos tienen diferente intercalación

https://www.mssqltips.com/sqlservertip/4395/understanding-the-collate-databasedefault-clause-in-sql-server/

Consultas utiles SQL Server

Scripts de utilidad para administradores de bases de datos

https://dataedo.com/blog/useful-sql-server-data-dictionary-queries-every-dba-should-have

Ver log de envio de correos sql mail

Checking Sent Email Messages on SQL Server

SENT MESSAGES LOG

SELECT TOP 20 *
FROM [msdb].[dbo].[sysmail_sentitems]
ORDER BY [send_request_date] DESC

FAILED MESSAGES LOG

SELECT TOP 20 *
FROM [msdb].[dbo].[sysmail_faileditems]
ORDER BY [send_request_date] DESC

ALL MESSAGES – REGARDLESS OF STATUS

SELECT TOP 20 *
FROM [msdb].[dbo].[sysmail_allitems]
ORDER BY [send_request_date] DESC

Buscar texto en todas las tablas de una base de datos SQL Server

Ejecutar lo siguiente

 DECLARE @StrValorBusqueda nvarchar(100)
 
 SET @StrValorBusqueda='texto a buscar'
 
 DECLARE @Resultado TABLE (NombreColumna nvarchar(370), ValorColumna nvarchar(3630))
 
 SET NOCOUNT ON DECLARE @NombreTabla nvarchar(256), 
 
 @NombreColumna nvarchar(128), 
 @StrValorBusqueda2 nvarchar(110)
 
 SET @NombreTabla = ''
 SET @StrValorBusqueda2 = QUOTENAME('%' + @StrValorBusqueda + '%','''')
 WHILE @NombreTabla IS NOT NULL
 
 BEGIN
 SET @NombreColumna = ''
 SET @NombreTabla = 
 
 (SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
 FROM INFORMATION_SCHEMA.TABLES
 WHERE TABLE_TYPE = 'BASE TABLE'
 AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @NombreTabla
 AND OBJECTPROPERTY(
 OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0)
 WHILE (@NombreTabla IS NOT NULL) AND (@NombreColumna IS NOT NULL)
 BEGIN
 SET @NombreColumna =
 (SELECT MIN(QUOTENAME(COLUMN_NAME))
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_SCHEMA = PARSENAME(@NombreTabla, 2)
 AND TABLE_NAME = PARSENAME(@NombreTabla, 1)
 AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
 AND QUOTENAME(COLUMN_NAME) > @NombreColumna)
 IF @NombreColumna IS NOT NULL
 BEGIN
 INSERT INTO @Resultado
 EXEC
 ('SELECT ''' + @NombreTabla + '.' + @NombreColumna + ''', LEFT(' + @NombreColumna + ', 3630) 
 FROM ' + @NombreTabla + ' (NOLOCK) ' + ' WHERE ' + @NombreColumna + ' LIKE ' + @StrValorBusqueda2)
 END 
 END 
 END
 SELECT NombreColumna, ValorColumna FROM @Resultado

funciones y comando sql no comunes

uso de rollup, genera una fila tipo «salto subtotal» en una consulta SQL

uso de compute, que genera una consulta adicional con la funcion de agregado especificada:

uso del COMPUTE BY, realiza el totalizado con saltos (resultado) en un mismo query

Campos calculados

Generar clases .NET desde SQL

Clases entidad en base a tablas SQL

declare @TableName sysname = 'TableName'
declare @Result varchar(max) = 'public class ' + @TableName + '
{'

select @Result = @Result + '
    public ' + ColumnType + NullableSign + ' ' + ColumnName + ' { get; set; }
'
from
(
    select 
        replace(col.name, ' ', '_') ColumnName,
        column_id ColumnId,
        case typ.name 
            when 'bigint' then 'long'
            when 'binary' then 'byte[]'
            when 'bit' then 'bool'
            when 'char' then 'string'
            when 'date' then 'DateTime'
            when 'datetime' then 'DateTime'
            when 'datetime2' then 'DateTime'
            when 'datetimeoffset' then 'DateTimeOffset'
            when 'decimal' then 'decimal'
            when 'float' then 'float'
            when 'image' then 'byte[]'
            when 'int' then 'int'
            when 'money' then 'decimal'
            when 'nchar' then 'string'
            when 'ntext' then 'string'
            when 'numeric' then 'decimal'
            when 'nvarchar' then 'string'
            when 'real' then 'double'
            when 'smalldatetime' then 'DateTime'
            when 'smallint' then 'short'
            when 'smallmoney' then 'decimal'
            when 'text' then 'string'
            when 'time' then 'TimeSpan'
            when 'timestamp' then 'DateTime'
            when 'tinyint' then 'byte'
            when 'uniqueidentifier' then 'Guid'
            when 'varbinary' then 'byte[]'
            when 'varchar' then 'string'
            else 'UNKNOWN_' + typ.name
        end ColumnType,
        case 
            when col.is_nullable = 1 and typ.name in ('bigint', 'bit', 'date', 'datetime', 'datetime2', 'datetimeoffset', 'decimal', 'float', 'int', 'money', 'numeric', 'real', 'smalldatetime', 'smallint', 'smallmoney', 'time', 'tinyint', 'uniqueidentifier') 
            then '?' 
            else '' 
        end NullableSign
    from sys.columns col
        join sys.types typ on
            col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id
    where object_id = object_id(@TableName)
) t
order by ColumnId

set @Result = @Result  + '
}'

print @Result

Generar objectos c# para DAO

declare @TableName sysname = 'NotaEntradaDetalle'
declare @Result varchar(max) = ''

select @Result = @Result + '
        ocomando.Parameters.Add("@' + [ColumnName] + '", SqlDbType.' + ColumnType + ').Value=pItems.' + [ColumnName] + ';'
from
(
    select 
        replace(col.name, ' ', '_') ColumnName,
        column_id ColumnId,
        case typ.name 
            when 'bigint' then 'BigInt'
            when 'binary' then 'Binary'
            when 'bit' then 'Bit'
            when 'char' then 'Char'
            when 'date' then 'DateTime'
            when 'datetime' then 'DateTime'
            when 'datetime2' then 'DateTime'
            when 'datetimeoffset' then 'DateTimeOffset'
            when 'decimal' then 'Decimal'
            when 'float' then 'Float'
            when 'image' then 'Image'
            when 'int' then 'Int'
            when 'money' then 'Money'
            when 'nchar' then 'NChar'
            when 'ntext' then 'NText'
            when 'numeric' then 'decimal'
            when 'nvarchar' then 'string'
            when 'real' then 'double'
            when 'smalldatetime' then 'DateTime'
            when 'smallint' then 'short'
            when 'smallmoney' then 'decimal'
            when 'text' then 'string'
            when 'time' then 'TimeSpan'
            when 'timestamp' then 'DateTime'
            when 'tinyint' then 'byte'
            when 'uniqueidentifier' then 'Guid'
            when 'varbinary' then 'byte[]'
            when 'varchar' then 'VarChar'
            else 'UNKNOWN_' + typ.name
        end ColumnType,
        case 
            when col.is_nullable = 1 and typ.name in ('bigint', 'bit', 'date', 'datetime', 'datetime2', 'datetimeoffset', 'decimal', 'float', 'int', 'money', 'numeric', 'real', 'smalldatetime', 'smallint', 'smallmoney', 'time', 'tinyint', 'uniqueidentifier') 
            then '?' 
            else '' 
        end NullableSign
    from sys.columns col
        join sys.types typ on
            col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id
    where object_id = object_id(@TableName)
) t
order by ColumnId

set @Result = @Result  + ''

print @Result

Generando codigo para el reader

declare @TableName sysname = 'NotaEntradaDetalle'
declare @Result varchar(max) = ''

select @Result = @Result + '
        objBE.' + [ColumnName] + '=(' + ColumnType + ')oDataReader["' + [ColumnName] + '"];'
from
(
    select 
        replace(col.name, ' ', '_') ColumnName,
        column_id ColumnId,
        case typ.name 
            when 'bigint' then 'long'
            when 'binary' then 'byte[]'
            when 'bit' then 'bool'
            when 'char' then 'string'
            when 'date' then 'DateTime'
            when 'datetime' then 'DateTime'
            when 'datetime2' then 'DateTime'
            when 'datetimeoffset' then 'DateTimeOffset'
            when 'decimal' then 'decimal'
            when 'float' then 'float'
            when 'image' then 'byte[]'
            when 'int' then 'int'
            when 'money' then 'decimal'
            when 'nchar' then 'string'
            when 'ntext' then 'string'
            when 'numeric' then 'decimal'
            when 'nvarchar' then 'string'
            when 'real' then 'double'
            when 'smalldatetime' then 'DateTime'
            when 'smallint' then 'short'
            when 'smallmoney' then 'decimal'
            when 'text' then 'string'
            when 'time' then 'TimeSpan'
            when 'timestamp' then 'DateTime'
            when 'tinyint' then 'byte'
            when 'uniqueidentifier' then 'Guid'
            when 'varbinary' then 'byte[]'
            when 'varchar' then 'string'
            else 'UNKNOWN_' + typ.name
        end ColumnType,
        case 
            when col.is_nullable = 1 and typ.name in ('bigint', 'bit', 'date', 'datetime', 'datetime2', 'datetimeoffset', 'decimal', 'float', 'int', 'money', 'numeric', 'real', 'smalldatetime', 'smallint', 'smallmoney', 'time', 'tinyint', 'uniqueidentifier') 
            then '?' 
            else '' 
        end NullableSign
    from sys.columns col
        join sys.types typ on
            col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id
    where object_id = object_id(@TableName)
) t
order by ColumnId

set @Result = @Result  + ''

print @Result

Categoría: Bases de Datos

Manejo de excepciones sql server

Base de datos local html5