C# SqlDataReader.GetSchemaTable方法代码示例

 public static string GetCreateSql(SqlDataReader reader, string tbName)
 {
     if (reader == null)
         return null;
     StringBuilder sb = new StringBuilder("Create table [" + tbName + "](");
     DataTable dt = reader.GetSchemaTable();
     if (dt == null || dt.Rows.Count <= 0)
         return null;
     foreach (DataRow row in dt.Rows)
     {
         string type = row["DataTypeName"].ToString().ToLower();
         sb.Append("[" + row["ColumnName"] + "] " + type);
         switch (type)
         {
             case "bigint":
             case "bit":
             case "datetime":
             case "float":
             case "image":
             case "int":
             case "money":
             case "ntext":
             case "real":
             case "smalldatetime":
             case "smallint":
             case "smallmoney":
             case "sql_variant":
             case "text":
             case "timestamp":
             case "tinyint":
             case "uniqueidentifier":
             case "xml":
                 break;
             case "decimal":
             case "numeric":
                 sb.Append("(" + row["NumericPrecision"] + ", " + row["NumericScale"] + ")");
                 break;
             case "binary":
             case "char":
             case "nchar":
             case "nvarchar":
             case "varbinary":
             case "varchar":
                 if ((int)row["ColumnSize"] == int.MaxValue)
                     sb.Append("(max)");
                 else
                     sb.Append("(" + row["ColumnSize"] + ")");
                 break;
         }
         sb.Append(",");
     }
     sb.Remove(sb.Length - 1, 1);// 移除最后一个逗号
     sb.Append(")");
     return sb.ToString();
 }

        public static bool readerExists(SqlDataReader dr, string columnName)
        {
            dr.GetSchemaTable().DefaultView.RowFilter = "ColumnName= '" +

            columnName + "'";

            return (dr.GetSchemaTable().DefaultView.Count > 0);
        }

 public static DataTable ConvertDataReaderToDataTable(SqlDataReader dataReader)
 {
     DataTable table = new DataTable();
     DataTable schemaTable = dataReader.GetSchemaTable();
     DataTable result;
     try
     {
         foreach (DataRow row in schemaTable.Rows)
         {
             DataColumn column = new DataColumn {
                 DataType = row.GetType(),
                 ColumnName = row[0].ToString()
             };
             table.Columns.Add(column);
         }
         while (dataReader.Read())
         {
             DataRow row2 = table.NewRow();
             for (int i = 0; i < schemaTable.Rows.Count; i++)
             {
                 row2[i] = dataReader[i].ToString();
             }
             table.Rows.Add(row2);
         }
         schemaTable = null;
         dataReader.Close();
         result = table;
     }
     catch (Exception ex)
     {
         SystemError.SystemLog(ex.Message);
         throw new Exception(ex.Message, ex);
     }
     return result;
 }

 /// <summary>
 /// ��DataReader תΪ DataTable
 /// </summary>
 /// <param name="DataReader">DataReader</param>
 public DataTable ConvertDataReaderToDataTable(SqlDataReader dataReader)
 {
     DataTable datatable = new DataTable();
     try
     {
         DataTable schemaTable = dataReader.GetSchemaTable();
         //��̬�����
         foreach (DataRow myRow in schemaTable.Rows)
         {
             DataColumn myDataColumn = new DataColumn();
             myDataColumn.DataType = System.Type.GetType("System.String");
             myDataColumn.ColumnName = myRow[0].ToString();
             datatable.Columns.Add(myDataColumn);
         }
         //�������
         while (dataReader.Read())
         {
             DataRow myDataRow = datatable.NewRow();
             for (int i = 0; i < schemaTable.Rows.Count; i++)
             {
                 myDataRow[i] = dataReader[i].ToString();
             }
             datatable.Rows.Add(myDataRow);
             myDataRow = null;
         }
         schemaTable = null;
     }
     finally
     {
         dataReader.Close();
         dataReader.Dispose();
     }
     return datatable;
 }

        /// <summary>
        /// [static] PAB.Data.Utils.DataReaderConverters.ConvertDataReaderToDataTable
        /// converts SqlDataReader to a DataTable
        /// </summary>
        /// <param name="reader">SqlDataReader</param>
        /// <returns>System.Data.DataTable</returns>
        public static DataTable ConvertDataReaderToDataTable(SqlDataReader reader)
        {
            System.Data.DataTable table = reader.GetSchemaTable();
            System.Data.DataTable dt = new System.Data.DataTable();
            System.Data.DataColumn dc;
            System.Data.DataRow row;
            System.Collections.ArrayList al = new System.Collections.ArrayList();

            for (int i = 0; i < table.Rows.Count; i ++)
            {
                dc = new System.Data.DataColumn();
                if (! dt.Columns.Contains(table.Rows[i]["ColumnName"].ToString()))
                {
                    dc.ColumnName = table.Rows[i]["ColumnName"].ToString();
                    dc.Unique = Convert.ToBoolean(table.Rows[i]["IsUnique"]);
                    dc.AllowDBNull = Convert.ToBoolean(table.Rows[i]["AllowDBNull"]);
                    dc.ReadOnly = Convert.ToBoolean(table.Rows[i]["IsReadOnly"]);
                    al.Add(dc.ColumnName);
                    dt.Columns.Add(dc);
                }
            }
            while (reader.Read())
            {
                row = dt.NewRow();
                for ( int i = 0; i < al.Count; i++)
                {
                    row[((System.String) al[i])] = reader[(System.String) al[i]];
                }
                dt.Rows.Add(row);
            }
            return dt;
        }

        private static DbfHeader BuildHeader(SqlDataReader reader, out int geomOrdinal, out int colCount)
        {
            DbfHeader dbfHeader = new DbfHeader();
            DataTable schema = reader.GetSchemaTable();
            geomOrdinal = -1;
            colCount = schema.Rows.Count - 1;

            foreach (DataRow row in schema.Rows)
            {
                int oridinal = (int)row["ColumnOrdinal"];
                int size = (int)row["ColumnSize"];
                string name = row["ColumnName"] as string;

                switch ((row["DataType"] as Type).Name)
                {
                    case "String":
                        dbfHeader.AddCharacter(name, (byte)size);
                        break;
                    case "SqlGeometry":
                        geomOrdinal = oridinal;
                        break;
                    default:
                        throw new Exception(String.Format("'{0}' is not a recognized data type", (row["DataType"] as Type).Name));
                }
            }

            if (geomOrdinal == -1)
                throw new Exception("Geometry column was not found");

            return dbfHeader;
        }

 private static DataTable attemptToGetSchemaTable(SqlString command, SqlDataReader reader)
 {
     try
     {
         return reader.GetSchemaTable();
     }
     catch (Exception e)
     {
         throw new InvalidResultSetException("The command [" + command.ToString() + "] did not return a valid result set", e);
     }
 }

 public static bool ReaderContainsColumn(SqlDataReader reader, string col)
 {
     DataTable schema = reader.GetSchemaTable();
     foreach (DataRow row in schema.Rows)
     {
         if (string.Compare(row["ColumnName"].ToString(), col, true) == 0)
         {
             return true;
         }
     }
     return false;
 }

    //-----------googled Apr. 01 2011----------------------
    //authors.aspalliance.com/stevesmith/articles/convertReadertoSet.asp
    //---------------------------------------------------------------------
    ///    <summary>
    ///    Converts a SqlDataReader to a DataSet
    ///    <param name='reader'>
    /// SqlDataReader to convert.</param>
    ///    <returns>
    /// DataSet filled with the contents of the reader.</returns>
    ///    </summary>
    public static DataSet convertDataReaderToDataSet(SqlDataReader reader)
    {
        DataSet dataSet = new DataSet();
        do
        {
            // Create new data table

            DataTable schemaTable = reader.GetSchemaTable();
            DataTable dataTable = new DataTable();

            if (schemaTable != null)
            {
                // A query returning records was executed

                for (int i = 0; i < schemaTable.Rows.Count; i++)
                {
                    DataRow dataRow = schemaTable.Rows[i];
                    // Create a column name that is unique in the data table
                    string columnName = (string)dataRow["ColumnName"]; //+ "<C" + i + "/>";
                    // Add the column definition to the data table
                    DataColumn column = new DataColumn(columnName, (Type)dataRow["DataType"]);
                    dataTable.Columns.Add(column);
                }

                dataSet.Tables.Add(dataTable);

                // Fill the data table we just created

                while (reader.Read())
                {
                    DataRow dataRow = dataTable.NewRow();

                    for (int i = 0; i < reader.FieldCount; i++)
                        dataRow[i] = reader.GetValue(i);

                    dataTable.Rows.Add(dataRow);
                }
            }
            else
            {
                // No records were returned

                DataColumn column = new DataColumn("RowsAffected");
                dataTable.Columns.Add(column);
                dataSet.Tables.Add(dataTable);
                DataRow dataRow = dataTable.NewRow();
                dataRow[0] = reader.RecordsAffected;
                dataTable.Rows.Add(dataRow);
            }
        }
        while (reader.NextResult());
        return dataSet;
    }

        private static SqlMetaData[] createMetaDataForResultset(SqlDataReader dataReader)
        {
            DataTable schema = dataReader.GetSchemaTable();
            LinkedList<DataRow> columns = getDisplayedColumns(schema);

            SqlMetaData[] meta = new SqlMetaData[columns.Count];
            int columnCount = 0;
            foreach (DataRow column in columns)
            {
                meta[columnCount] = createSqlMetaDataForColumn(column);
                columnCount++;
            }

            return meta;
        }

        /// <summary>
        /// 数据表转换
        /// </summary>
        /// <param name="dataReader"></param>
        /// <returns></returns>
        public static DataTable ConvertDataReaderToDataTable(SqlDataReader dataReader)
        {
            DataTable datatable = new DataTable("DataTable");
            DataTable schemaTable = dataReader.GetSchemaTable();

            //动态添加列

            try
            {
                if (dataReader != null)
                {

                    foreach (DataRow myRow in schemaTable.Rows)
                    {
                        DataColumn myDataColumn = new DataColumn();
                        myDataColumn.DataType = myRow["DataTypeName"].GetType();
                        myDataColumn.ColumnName = myRow[0].ToString();
                        datatable.Columns.Add(myDataColumn);
                    }

                    //添加数据
                    while (dataReader.Read())
                    {
                        DataRow myDataRow = datatable.NewRow();
                        for (int i = 0; i < schemaTable.Rows.Count; i++)
                        {
                            myDataRow[i] = dataReader[i].ToString();
                        }
                        datatable.Rows.Add(myDataRow);
                        myDataRow = null;
                    }
                    schemaTable = null;
                    dataReader.Close();

                }

                return datatable;
            }
            catch (Exception ex)
            {
                throw new Exception("转换出错出错!", ex);
            }
        }

 /// <summary>
 /// ��DataReader תΪ DataTable
 /// </summary>
 /// <param name="DataReader">DataReader</param>
 public static DataTable ConvertDataReaderToDataTable(SqlDataReader dataReader)
 {
     DataTable datatable = new DataTable();
     DataTable schemaTable = dataReader.GetSchemaTable();
     List<DataColumn> listCols = new List<DataColumn>();
     //��̬�����
     try
     {
         if (null != schemaTable)
         {
             foreach (DataRow drow in schemaTable.Rows)
             {
                 string columnName = System.Convert.ToString(drow["ColumnName"]);
                 DataColumn column = new DataColumn(columnName, (Type)(drow["DataType"]));
                 column.Unique = (bool)drow["IsUnique"];
                 column.AllowDBNull = (bool)drow["AllowDBNull"];
                 column.AutoIncrement = (bool)drow["IsAutoIncrement"];
                 listCols.Add(column);
                 datatable.Columns.Add(column);
             }
         }
         //�������
         while(dataReader.Read())
         {
             DataRow myDataRow = datatable.NewRow();
             for (int i = 0; i < listCols.Count; i++)
             {
                 myDataRow[((DataColumn)listCols[i])] = dataReader[i];
             }
             datatable.Rows.Add(myDataRow);
         }
         schemaTable = null;
         dataReader.Close();
         return datatable;
     }
     catch(Exception ex)
     {
         Error.Log(ex.ToString());
         throw new Exception("ת���������!",ex);
     }
 }

        // этот метод создает ограничения на столбцы таблице на основе полученного объекта DataReader
        private static void CreateSchemaFromReader(this DataTable table, SqlDataReader reader)
        {
            DataTable schemaTable = reader.GetSchemaTable(); // Метод Возвращает таблицу описывающую метаданные столбца объекта SqlDataReader. 

            foreach (DataRow schemaRow in schemaTable.Rows)
            {
                DataColumn column = new DataColumn((string)schemaRow["ColumnName"]);    // создание столбца с именем столбца в источнике данных
                column.AllowDBNull = (bool)schemaRow["AllowDbNull"];                    // получение значения свойства AllowDBNull
                column.DataType = (Type)schemaRow["DataType"];                          // получение значения свойства DataType
                column.Unique = (bool)schemaRow["IsUnique"];                            // получение значения свойства Unique
                column.ReadOnly = (bool)schemaRow["IsReadOnly"];                        // получение значения свойства Readonly
                column.AutoIncrement = (bool)schemaRow["IsIdentity"];                   // получение значения свойства AutoIncrement

                if (column.DataType == typeof(string))                                  // если поле типа string
                    column.MaxLength = (int)schemaRow["ColumnSize"];                    // получить значение свойства MaxLength

                if (column.AutoIncrement == true)                                       // Если поле с автоинкрементом 
                { column.AutoIncrementStep = -1; column.AutoIncrementSeed = 0; }        // задать свойства AutoIncrementStep и AutoIncrementSeed

                table.Columns.Add(column);                                              // добавить созданный столбец в коллекцию Columns таблицы
            }
        }

        internal static FilterDefs FillTerminalFilterFromReader(SqlDataReader reader)
        {
            var terminalFilter = new FilterDefs();

            if (reader != null && !reader.IsClosed)
            {
                DataTable dt = reader.GetSchemaTable();
                if (dt.Select("ColumnName='" + "FilterId" + "'").Length > 0)
                    if (!reader.IsDBNull(reader.GetOrdinal("FilterId")))
                        terminalFilter.FilterId = reader.GetInt32(reader.GetOrdinal("FilterId"));
                if (dt.Select("ColumnName='" + "FilterName" + "'").Length > 0)
                    if (!reader.IsDBNull(reader.GetOrdinal("FilterName")))
                        terminalFilter.FilterName = reader.GetString(reader.GetOrdinal("FilterName"));
                if (dt.Select("ColumnName='" + "Description" + "'").Length > 0)
                    if (!reader.IsDBNull(reader.GetOrdinal("Description")))
                        terminalFilter.Description = reader.GetString(reader.GetOrdinal("Description"));
                if (dt.Select("ColumnName='" + "SQL" + "'").Length > 0)
                    if (!reader.IsDBNull(reader.GetOrdinal("SQL")))
                        terminalFilter.SQL = reader.GetString(reader.GetOrdinal("SQL"));
                if (dt.Select("ColumnName='" + "FilterExpression" + "'").Length > 0)
                    if (!reader.IsDBNull(reader.GetOrdinal("FilterExpression")))
                        terminalFilter.FilterExpression = reader.GetString(reader.GetOrdinal("FilterExpression"));
                if (dt.Select("ColumnName='" + "CreatedOn" + "'").Length > 0)
                    if (!reader.IsDBNull(reader.GetOrdinal("CreatedOn")))
                        terminalFilter.CreatedOn = reader.GetDateTime(reader.GetOrdinal("CreatedOn"));
                if (dt.Select("ColumnName='" + "CreatedBy" + "'").Length > 0)
                    if (!reader.IsDBNull(reader.GetOrdinal("CreatedBy")))
                        terminalFilter.CreatedBy = reader.GetInt16(reader.GetOrdinal("CreatedBy"));
                if (dt.Select("ColumnName='" + "VisibleToOthers" + "'").Length > 0)
                    if (!reader.IsDBNull(reader.GetOrdinal("VisibleToOthers")))
                        terminalFilter.VisibleToOthers = reader.GetByte(reader.GetOrdinal("VisibleToOthers"));
                if (dt.Select("ColumnName='" + "ShownOnModules" + "'").Length > 0)
                    if (!reader.IsDBNull(reader.GetOrdinal("ShownOnModules")))
                        terminalFilter.ShownOnModules = reader.GetInt32(reader.GetOrdinal("ShownOnModules"));
            }
            return terminalFilter;
        }

    private static void SqlDataReaderToXml(
    SqlDataReader AReader,
    //DataTable     ADataTable,
    XmlTextWriter AWriter,
    Boolean       ASkipNameColumn,
    String        ARowTag,
    TRowSetMap    AMap
  )
    {
        const String ROW_TAG      = "ROW";
        const String METADATA_TAG = "METADATA";
        const String FIELD_TAG    = "FIELD";
        const String NONAME_FIELD_PREFIX = "FIELD";

        int
          i,
          start_field_index   = ASkipNameColumn ? 1 : 0,
          empty_name_field_no = 1;
        String FieldName;

        DataTable LDataTable = AReader.GetSchemaTable();

        List<TFieldAlias> LFields = new List<TFieldAlias>();
        TFieldAlias LField;
        int FieldCount = AReader.FieldCount;

        if(AMap.Fields.Length > 0)
        {
          foreach (String FieldMap in AMap.Fields.Split(new char[] {','}))
          {
        i = FieldMap.IndexOf('=');
        if(i >= 0)
        {
          LField.Name = FieldMap.Substring(0, i);
          FieldName   = FieldMap.Substring(i + 1);
        }
        else
        {
          LField.Name = FieldMap;
          FieldName   = FieldMap;
        }
        for(i = start_field_index; i < FieldCount; i++)
        {
          if(FieldName.Equals(AReader.GetName(i), StringComparison.InvariantCultureIgnoreCase))
            break;
        }
        if((i < 0) || (i >= FieldCount))
          throw new SystemException("RowSet Field = [" + FieldName + "] not found.");
        // LField.Name = XmlConvert.EncodeLocalName(LField.Name);
        LField.FieldIndex = i;
        LFields.Add(LField);

        //AWriter.WriteStartAttribute(LField.Name);
        //AWriter.WriteValue(SqlMetaDataToString(LDataTable.Rows[i]));
          }
        }
        else
        {
          for(LField.FieldIndex = start_field_index; LField.FieldIndex < FieldCount; LField.FieldIndex++)
          {
        FieldName = AReader.GetName(LField.FieldIndex);
        if(FieldName.Length == 0)
          FieldName = NONAME_FIELD_PREFIX + (empty_name_field_no++).ToString();
        LField.Name = FieldName;
        //LField.Name = XmlConvert.EncodeLocalName(FieldName);
        LFields.Add(LField);

        //AWriter.WriteStartAttribute(LField.Name);
        //AWriter.WriteValue(SqlMetaDataToString(LDataTable.Rows[LField.FieldIndex]));
          }
        }

        AWriter.WriteStartElement(METADATA_TAG);
        for (i = 0; i < LFields.Count; i++)
        {
          AWriter.WriteStartElement(FIELD_TAG);
        AWriter.WriteStartAttribute(ATTRIBUTE_INDEX);
        AWriter.WriteValue(i + 1);
        AWriter.WriteStartAttribute(ATTRIBUTE_NAME);
        AWriter.WriteValue(LFields[i].Name);
        AWriter.WriteStartAttribute(ATTRIBUTE_TYPE);
        AWriter.WriteValue(SqlMetaDataToString(LDataTable.Rows[LFields[i].FieldIndex]));
          AWriter.WriteEndElement();
        }
        AWriter.WriteEndElement();

        object Value;
        while (AReader.Read())
        {
          AWriter.WriteStartElement(ARowTag ?? ROW_TAG);

          for (i = 0; i < LFields.Count; i++)
          {
        Value = AReader.GetValue(LFields[i].FieldIndex);
        if (Value != DBNull.Value) // NULL пропускаем
        {
          AWriter.WriteStartAttribute(XmlConvert.EncodeLocalName(LFields[i].Name));
          try
          {
            AWriter.WriteValue(Value);
//.........这里部分代码省略.........