Christian - November 2010 Entries

Posts: 394
Comments: 124
Trackbacks: 0

November 2010 Entries

SQL-Skript zur Ausgabe aller Tabellen, samt dazugehörigen Feldern und Feldtypen

Hier ein kleines SQL-Skript mit welchem man sich alle Tabellen, samt der dazugehörigen Feldern und Datentpen ausgeben lassen kann.

SELECT
        table_name=sysobjects.name,
        column_name=syscolumns.name,
        datatype=systypes.name,
        length=syscolumns.length

FROM
        sysobjects
        JOIN syscolumns ON sysobjects.[id] = syscolumns.[id]
        JOIN systypes ON syscolumns.xtype=systypes.xtype

WHERE
sysobjects.[xtype]='U'

ORDER BY
sysobjects.[name],
syscolumns.colid

Wobei gilt:

sysobjects.[xtype]='U' -- für Tabellen
sysobjects.[xtype]='P' -- für stored procedures
sysobjects.[xtype]='v' -- für Views

Praktisch für die Dokumentation der Tabellen einer Datenbank.

posted @ Thursday, November 25, 2010 6:11 AM | Feedback (0)

Fun mit t-SQL und foreign keys

Hier ein paar code snippets die man immer mal wieder brauchen kann:

Alle FK's einer Datenbank anzeigen:

SELECT OBJECT_NAME(fk.parent_object_id) , fk.name
FROM sys.foreign_keys AS fk
INNER JOIN sys.foreign_key_columns AS fc
ON fk.OBJECT_ID = fc.constraint_object_id

Alle FK's einer Datenbank löschen (Vorsicht!):

SELECT 'ALTER TABLE ' + OBJECT_NAME(fk.parent_object_id)+
' DROP CONSTRAINT ' + fk.name
FROM sys.foreign_keys AS fk
INNER JOIN sys.foreign_key_columns AS fc
ON fk.OBJECT_ID = fc.constraint_object_id

Anzeigen welche FKs von welcher Tabelle aus auf eine gegebene Tabelle verweisen (inkl. Felder):

DECLARE @tabelle nvarchar(100)
set @tabelle = 'TabellennName'

select object_name(fkeyid) Child_Table, object_name(constid) FKey_Name,
c1.name FKey_Col,object_name(rkeyid) This_Table, c2.name Ref_KeyCol
from sysforeignkeys s
inner join syscolumns c1 on ( s.fkeyid = c1.id and s.fkey = c1.colid )
inner join syscolumns c2 on ( s.rkeyid = c2.id and s.rkey = c2.colid )
where object_name(rkeyid)=@tabelle
order by 1,2,4

posted @ Thursday, November 11, 2010 9:02 PM | Feedback (0)

Das Ergebnis einer LinQ-Abfrage einem DataTable zuweisen

Vor kurzem musste ich in einer Anwendung eine LinQtoSQL-Abfrage einem DataTable zuweisen.

Im Web fand ich auf einer chinesischen Seite hierfür folgende nützliche Funktion:(http://blog.csdn.net/gdjlc/archive/2010/07/06/5714918.aspx)

Für' Archiv hier in C#

public DataTable ConvertToDataTable<T>(IEnumerable<T> varlist)
{
DataTable dtReturn = new DataTable();

// column names
PropertyInfo[] oProps = null;

if (varlist == null) return dtReturn;

    foreach (T rec in varlist)
    {
        // Use reflection to get property names, to create table, Only first time, others will follow
        if (oProps == null)
        {
            oProps = ((Type)rec.GetType()).GetProperties();
            foreach (PropertyInfo pi in oProps)
            {
                Type colType = pi.PropertyType;
                if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition() == typeof(Nullable<>)))
                {
                    colType = colType.GetGenericArguments()[0];
                }
                dtReturn.Columns.Add(new DataColumn(pi.Name, colType));
            }
        }

DataRow dr = dtReturn.NewRow();

        foreach (PropertyInfo pi in oProps)
        {
            dr[pi.Name] = pi.GetValue(rec, null) == null ? DBNull.Value : pi.GetValue(rec, null);
        }

dtReturn.Rows.Add(dr);
}

return dtReturn;
}

und in VB.NET:

Public Function ConvertToDataTable(Of T)(ByVal varlist As IEnumerable(Of T)) As DataTable

Dim dtReturn As New DataTable()

' column names
Dim oProps As PropertyInfo() = Nothing

     If varlist Is Nothing Then
         Return dtReturn
     End If

     For Each rec As T In varlist
         ' Use reflection to get property names, to create table
         If oProps Is Nothing Then
             oProps = DirectCast(rec.[GetType](), Type).GetProperties()
             For Each pi As PropertyInfo In oProps
                 Dim colType As Type = pi.PropertyType
                 If (colType.IsGenericType)
                     AndAlso (colType.GetGenericTypeDefinition().ToString()
                     = GetType(Nullable(Of )).ToString())
                 Then
                     colType = colType.GetGenericArguments()(0)
                 End If

                 dtReturn.Columns.Add(New DataColumn(pi.Name, colType))
             Next
         End If

         Dim dr As DataRow = dtReturn.NewRow()
         For Each pi As PropertyInfo In oProps
             dr(pi.Name) = If(pi.GetValue(rec, Nothing) Is Nothing, DBNull.Value, pi.GetValue(rec, Nothing))
         Next

dtReturn.Rows.Add(dr)

Return dtReturn

End Function

posted @ Wednesday, November 03, 2010 8:55 PM | Feedback (0)

Archives

Post Categories

test