none
Retrieving Detailed DB Schema from SQL in C# RRS feed

  • Question

  • Hi All,

     

    I am trying to retrieve a detailed table schema in code without success so far.

     

    When you generate a dataset in visual studio the schema information is very detailed similiar to the following:

     

     <xs:element name="GCMDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true" msprop:Generator_UserDSName="GCMDataSet" msprop:Generator_DataSetName="GCMDataSet">
        <xs:complexType>
          <xs:choice minOccurs="0" maxOccurs="unbounded">
            <xs:element name="EntityName" msprop:Generator_UserTableName="EntityName" msprop:Generator_RowDeletedName="EntityNameRowDeleted" msprop:Generator_TableClassName="EntityNameDataTable" msprop:Generator_RowChangedName="EntityNameRowChanged" msprop:Generator_RowClassName="EntityNameRow" msprop:Generator_RowChangingName="EntityNameRowChanging" msprop:Generator_RowEvArgName="EntityNameRowChangeEvent" msprop:Generator_RowEvHandlerName="EntityNameRowChangeEventHandler" msprop:Generator_TablePropName="EntityName" msprop:Generator_TableVarName="tableEntityName" msprop:Generator_RowDeletingName="EntityNameRowDeleting">
              <xs:complexType>
                <xsTongue Tiedequence>
                  <xs:element name="EntityNameCounter" msdata:ReadOnly="true" msdata:AutoIncrement="true" msprop:Generator_UserColumnName="EntityNameCounter" msprop:Generator_ColumnPropNameInRow="EntityNameCounter" msprop:Generator_ColumnVarNameInTable="columnEntityNameCounter" msprop:Generator_ColumnPropNameInTable="EntityNameCounterColumn" type="xs:int" />
                  <xs:element name="EntityIDCounter" msprop:Generator_UserColumnName="EntityIDCounter" msprop:Generator_ColumnPropNameInRow="EntityIDCounter" msprop:Generator_ColumnVarNameInTable="columnEntityIDCounter" msprop:Generator_ColumnPropNameInTable="EntityIDCounterColumn" type="xs:int" />
                  <xs:element name="EntityName" msprop:Generator_UserColumnName="EntityName" msprop:Generator_ColumnPropNameInRow="EntityName" msprop:Generator_ColumnVarNameInTable="columnEntityName" msprop:Generator_ColumnPropNameInTable="EntityNameColumn">
                    <xsTongue TiedimpleType>
                      <xs:restriction base="xsTongue Tiedtring">
                        <xs:maxLength value="200" />
                      </xs:restriction>
                    </xsTongue TiedimpleType>
                  </xs:element>
                  <xs:element name="SortName" msprop:Generator_UserColumnName="SortName" msprop:Generator_ColumnPropNameInRow="SortName" msprop:Generator_ColumnVarNameInTable="columnSortName" msprop:Generator_ColumnPropNameInTable="SortNameColumn" minOccurs="0">
                    <xsTongue TiedimpleType>
                      <xs:restriction base="xsTongue Tiedtring">
                        <xs:maxLength value="485" />
                      </xs:restriction>
                    </xsTongue TiedimpleType>
                  </xs:element>

     <xs:element name="IsConfirmed" msprop:Generator_UserColumnName="IsConfirmed" msprop:Generator_ColumnPropNameInRow="IsConfirmed" msprop:Generator_ColumnVarNameInTable="columnIsConfirmed" msprop:Generator_ColumnPropNameInTable="IsConfirmedColumn" type="xs:boolean" />
                  <xs:element name="DateEffectiveTo" msprop:Generator_UserColumnName="DateEffectiveTo" msprop:Generator_ColumnPropNameInRow="DateEffectiveTo" msprop:Generator_ColumnVarNameInTable="columnDateEffectiveTo" msprop:Generator_ColumnPropNameInTable="DateEffectiveToColumn" type="xsBig SmileateTime" minOccurs="0" />
                  <xs:element name="DateEffectiveFrom2" msprop:Generator_UserColumnName="DateEffectiveFrom2" msprop:Generator_ColumnPropNameInRow="DateEffectiveFrom2" msprop:Generator_ColumnVarNameInTable="columnDateEffectiveFrom2" msprop:Generator_ColumnPropNameInTable="DateEffectiveFrom2Column" type="xsBig SmileateTime" minOccurs="0" />
                  <xs:element name="DateEffectiveTo2" msprop:Generator_UserColumnName="DateEffectiveTo2" msprop:Generator_ColumnPropNameInRow="DateEffectiveTo2" msprop:Generator_ColumnVarNameInTable="columnDateEffectiveTo2" msprop:Generator_ColumnPropNameInTable="DateEffectiveTo2Column" type="xsBig SmileateTime" minOccurs="0" />
                  <xs:element name="CreatedByUserCounter" msprop:Generator_UserColumnName="CreatedByUserCounter" msprop:Generator_ColumnPropNameInRow="CreatedByUserCounter" msprop:Generator_ColumnVarNameInTable="columnCreatedByUserCounter" msprop:Generator_ColumnPropNameInTable="CreatedByUserCounterColumn" type="xs:int" minOccurs="0" />
                  <xs:element name="DateCreated" msprop:Generator_UserColumnName="DateCreated" msprop:Generator_ColumnPropNameInRow="DateCreated" msprop:Generator_ColumnVarNameInTable="columnDateCreated" msprop:Generator_ColumnPropNameInTable="DateCreatedColumn" type="xsBig SmileateTime" minOccurs="0" />
                  <xs:element name="ModifiedByUserCounter" msprop:Generator_UserColumnName="ModifiedByUserCounter" msprop:Generator_ColumnPropNameInRow="ModifiedByUserCounter" msprop:Generator_ColumnVarNameInTable="columnModifiedByUserCounter" msprop:Generator_ColumnPropNameInTable="ModifiedByUserCounterColumn" type="xs:int" minOccurs="0" />
                  <xs:element name="DateLastModified" msprop:Generator_UserColumnName="DateLastModified" msprop:Generator_ColumnPropNameInRow="DateLastModified" msprop:Generator_ColumnVarNameInTable="columnDateLastModified" msprop:Generator_ColumnPropNameInTable="DateLastModifiedColumn" type="xsBig SmileateTime" minOccurs="0" />
                  <xs:element name="mTimestamp" msdata:ReadOnly="true" msprop:Generator_UserColumnName="mTimestamp" msprop:Generator_ColumnPropNameInRow="mTimestamp" msprop:Generator_ColumnVarNameInTable="columnmTimestamp" msprop:Generator_ColumnPropNameInTable="mTimestampColumn" type="xs:base64Binary" minOccurs="0" />
                </xsTongue Tiedequence>
              </xs:complexType>
            </xs:element>
          </xs:choice>
        </xs:complexType>
        <xs:unique name="Constraint1" msdataStick out tonguerimaryKey="true">
          <xsTongue Tiedelector xpath=".//mstns:EntityName" />
          <xs:field xpath="mstns:EntityNameCounter" />
        </xs:unique>
      </xs:element>

     

     

    I have tried using the following code to retrieve the scheme but this only a less complete schema.

     

    SqlCommand cmd = new SqlCommand(sql, conn);

    SqlDataReader reader = cmd.ExecuteReader();

    DataTable schema = reader.GetSchemaTable();

    dstable.Tables.Add(schema);

    dstable.WriteXmlSchema(myStringWriter);

     

     

    Can anyone direct me as to how to retrive a schema siliar to that generated when a dataset is created?

     

     

    Lastbuilders

     

     

     

    Wednesday, April 23, 2008 3:31 PM