C# DataTable.CreateDataReader方法代碼示例

        public object BusinessCreateLight(List<BusinessLight> businesses, Converter converter, object dataObject)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("ExternalId", typeof(string));
            dt.Columns.Add("BusinessId", typeof(int));
            dt.Columns.Add("GenderFCheckIns", typeof(int));
            dt.Columns.Add("GenderMCheckIns", typeof(int));

            Random rnd = new Random();
            int icount = 0;
            businesses.ForEach(x =>
                {
                    DataRow dr = dt.NewRow();
                    dr["ExternalId"] = x.ExternalID;
                    dr["BusinessId"] = rnd.Next();
                    dr["GenderFCheckIns"] = icount;
                    dr["GenderMCheckIns"] = icount;
                    icount = icount + 10;
                    dt.Rows.Add(dr);
                });
            DataTableReader dtr = dt.CreateDataReader();
            while(dtr.Read())
            {
                converter(dtr, dataObject);
            }
            //dt.Select().ToList().ForEach(x=> converter(x,dataObject));

            return dt;
        }

        public static DataTableReader GetData(string connStr, string query, Properties props)
        {
            DataTable results = new DataTable();

            using (SOConnection connection = new SOConnection(connStr))
            {
                using (SOCommand command = new SOCommand(query, connection))
                {
                    using (SODataAdapter adapter = new SODataAdapter(command))
                    {
                        foreach (Property prop in props)
                        {
                            if (prop.Value != null)
                            {
                                command.Parameters.AddWithValue(prop.Name, prop.Value);
                            }
                        }
                        connection.DirectExecution = true;
                        connection.Open();
                        adapter.Fill(results);
                    }
                }
                connection.Close();
            }
            return results.CreateDataReader();
        }

        public void GetValueOrDefaultTest()
        {
            var dt = new DataTable();
              dt.Columns.Add("int32", typeof(Int32));

              dt.Rows.Add(new Object[] { DBNull.Value });
              dt.Rows.Add(new Object[] { 42 });
              dt.Rows.Add(new Object[] { DBNull.Value });
              dt.Rows.Add(new Object[] { 42 });

              using (var dr = dt.CreateDataReader())
              {
            // Reading a DBNull.Value into a nullable Int32 will succeed.
            dr.Read();
            dr.GetValueOrDefault<Int32?>("int32");

            // Reading 42 into a nullable Int32 will succeed.
            dr.Read();
            dr.GetValueOrDefault<Int32?>("int32");

            // Reading a DBNull.Value into a non-nullable Int32 will throw an InvalidCastException.
            dr.Read();
            Assert.Catch<InvalidCastException>(() => dr.GetValueOrDefault<Int32>("int32"));

            // Reading 42 into a non-nullable Int32 will succeed.
            dr.Read();
            dr.GetValueOrDefault<Int32>("int32");
              }
        }

        public void SetUp()
        {
            _mockCache = MockComponentProvider.CreateDataCacheProvider();
            MockComponentProvider.CreateEventLogController();



            _hostSettingsTable = new DataTable("HostSettings");

            var nameCol = _hostSettingsTable.Columns.Add("SettingName");
            _hostSettingsTable.Columns.Add("SettingValue");
            _hostSettingsTable.Columns.Add("SettingIsSecure");
            _hostSettingsTable.PrimaryKey = new[] {nameCol};

            _hostSettingsTable.Rows.Add("String_1_S", "String_1_S", true);
            _hostSettingsTable.Rows.Add("String_2_S", "String_1_S", true);
            _hostSettingsTable.Rows.Add("String_3_U", "Value_3_U", false);
            _hostSettingsTable.Rows.Add("String_4_U", "Value_4_U", false);
            _hostSettingsTable.Rows.Add("Int_5_U", "5", false);
            _hostSettingsTable.Rows.Add("Int_6_S", "6", true);
            _hostSettingsTable.Rows.Add("Double_7_S", "7", true);
            _hostSettingsTable.Rows.Add("Double_8_U", "8", false);
            _hostSettingsTable.Rows.Add("Bool_9_U", false, false);
            _hostSettingsTable.Rows.Add("Bool_10_S", false, true);


            _mockData = MockComponentProvider.CreateDataProvider();
            _mockData.Setup(c => c.GetHostSettings()).Returns(_hostSettingsTable.CreateDataReader());
            _mockData.Setup(c => c.GetProviderPath()).Returns(String.Empty);


            DataCache.ClearCache();
        }

        static void Main(string[] args)
        {
            //Create map once
            Mapper.CreateMap<IDataReader, Person>();

            //Setup a DataTable with Data
            var dataTable = new DataTable();

            dataTable.Columns.Add(new DataColumn("Name", typeof (string)));
            dataTable.Columns.Add(new DataColumn("Age", typeof (int)));

            dataTable.Rows.Add("Phillip", "26");
            dataTable.Rows.Add("jchannon", "3");

            //Create a reader from the DataTable
            //This part could be from SQL or where ever
            var reader = dataTable.CreateDataReader();

            //Convert the contents of the IDataReader to a collection
            var result = Mapper.Map<IDataReader, IEnumerable<Person>>(reader);

            foreach (var person in result)
            {
                Console.WriteLine(string.Format("{0} is {1} years old", person.Name, person.Age));
            }

            Console.ReadKey();
        }

        private void LoginRandomEmployee()
        {
            DataTable employeeTable = new DataTable();
            Person employee = null;

            using (OracleConnection objConn = new OracleConnection(Global.ConnectionString))
            {
                OracleCommand objCmd = new OracleCommand("TICKETS_QUERIES.getRandomEmployee", objConn) { BindByName = true, CommandType = CommandType.StoredProcedure };

                objCmd.Parameters.Add("p_Return", OracleDbType.RefCursor, ParameterDirection.ReturnValue);

                try
                {
                    objConn.Open();
                    OracleDataAdapter adapter = new OracleDataAdapter(objCmd);
                    adapter.Fill(employeeTable);
                    employee = Mapper.DynamicMap<IDataReader, List<Person>>(employeeTable.CreateDataReader()).FirstOrDefault();
                }
                catch (Exception)
                {
                    Response.Redirect("../Default.aspx");
                }

                objConn.Close();
            }
            if (employee != null)
            {
                Global.CurrentPerson = employee;
                Global.CurrentPerson.accountType = Account.Faculty;
            }
            else
                Response.Redirect("../Default.aspx");
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                if (Global.CurrentPerson == null)
                    Response.Redirect("Index.aspx");

                DataTable seasonsTable = new DataTable();
                List<Season> seasons = new List<Season>();

                using (OracleConnection objConn = new OracleConnection(Global.ConnectionString))
                {
                    // Set up the seasons command
                    var seasonsCommand = new OracleCommand("TICKETS_QUERIES.getSeasonsForPurchase", objConn) { BindByName = true, CommandType = CommandType.StoredProcedure };
                    seasonsCommand.Parameters.Add("p_Return", OracleDbType.RefCursor, ParameterDirection.ReturnValue);
                    seasonsCommand.Parameters.Add("p_PersonId", OracleDbType.Int64, Global.CurrentPerson.person_id, ParameterDirection.Input);

                    try
                    {
                        // Execute the queries and auto map the results to models
                        objConn.Open();
                        var seasonsAdapter = new OracleDataAdapter(seasonsCommand);
                        seasonsAdapter.Fill(seasonsTable);
                        seasons = Mapper.DynamicMap<IDataReader, List<Season>>(seasonsTable.CreateDataReader());
                    }
                    catch (Exception)
                    {
                        Response.Redirect("Index.aspx");
                    }

                    objConn.Close();
                }

                // Fill list dropdowns with data from the database
                if (seasons.Count > 0)
                {
                    var seasonsWithEvents = seasons.GroupBy(s => s.season_id).Select(season => new Season()
                    {
                        season_id = season.First().season_id,
                        name = season.First().name,
                        price = season.First().price,
                        ticket_count = season.First().ticket_count,
                        event_names = seasons.Where(ev => ev.season_id == season.First().season_id).Select(en => en.event_name).ToList()
                    }).ToList();

                    SeasonDropDown.DataTextField = "name";
                    SeasonDropDown.DataValueField = "season_id";
                    SeasonDropDown.DataSource = seasonsWithEvents;
                    SeasonDropDown.DataBind();

                    SeasonListView.DataSource = seasonsWithEvents;
                    SeasonListView.DataBind();
                }
                if (Request.QueryString["Success"] != null)
                {
                    Error.Text = "Successfully purchased season ticket!";
                    Error.Visible = true;
                }
            }
        }

        public IDataReader BuildDataReader()
        {
            var authorizationSetDataTable = new DataTable();
            authorizationSetDataTable.Columns.Add(FieldName.SmallInt, typeof(Int16));
            authorizationSetDataTable.Columns.Add(FieldName.Int, typeof(Int32));
            authorizationSetDataTable.Columns.Add(FieldName.BigInt, typeof(Int64));
            authorizationSetDataTable.Columns.Add(FieldName.Guid, typeof(Guid));
            authorizationSetDataTable.Columns.Add(FieldName.Float, typeof(float));
            authorizationSetDataTable.Columns.Add(FieldName.Double, typeof(Double));
            authorizationSetDataTable.Columns.Add(FieldName.Decimal, typeof(Decimal));
            authorizationSetDataTable.Columns.Add(FieldName.DateTime, typeof(DateTime));
            authorizationSetDataTable.Columns.Add(FieldName.Byte, typeof(Byte));
            authorizationSetDataTable.Columns.Add(FieldName.Boolean, typeof(Boolean));
            authorizationSetDataTable.Columns.Add(FieldName.Something, typeof(DateTime));

            var authorizationSetDataRow = authorizationSetDataTable.NewRow();
            authorizationSetDataRow[FieldName.SmallInt] = 22;
            authorizationSetDataRow[FieldName.Int] = 6134;
            authorizationSetDataRow[FieldName.BigInt] = 61346154;
            authorizationSetDataRow[FieldName.Guid] = Guid.NewGuid();
            authorizationSetDataRow[FieldName.Float] = 642.61;
            authorizationSetDataRow[FieldName.Double] = 67164.64;
            authorizationSetDataRow[FieldName.Decimal] = 94341.61;
            authorizationSetDataRow[FieldName.DateTime] = DateTime.Now;
            authorizationSetDataRow[FieldName.Byte] = 0x12;
            authorizationSetDataRow[FieldName.Boolean] = true;
            authorizationSetDataRow[FieldName.Something] = DateTime.MaxValue;
            authorizationSetDataTable.Rows.Add(authorizationSetDataRow);

            return authorizationSetDataTable.CreateDataReader();
        }

        static void TesteDataTable()
        {
            DataTable tabTeste = new DataTable();
            DataRow linhaTeste;
            DataColumn col01 = new DataColumn("col01", typeof(string));
            DataColumn col02 = new DataColumn("col02", typeof(string));

            tabTeste.Columns.Add(col01);
            tabTeste.Columns.Add(col02);

            int linha=1;

            while(linha <=10)
            {
                linhaTeste = tabTeste.NewRow();
                linhaTeste["col01"] = "Fabio";
                linhaTeste["col02"] = linha.ToString();

                tabTeste.Rows.Add(linhaTeste);

                linha+=1;

            }

            DataTableReader rd = tabTeste.CreateDataReader();

            while(rd.Read())
            {
                Console.WriteLine(rd[0].ToString());
                Console.WriteLine(rd[1].ToString());

            }

            Console.ReadLine();
        }

        public IDataReader LoadTableData()
        {
            DataTable mockTableData = new DataTable();
            mockTableData.Columns.Add("Table_Catalog");
            mockTableData.Columns.Add("Table_Schema");
            mockTableData.Columns.Add("Table_Name");

            DataRow firstRow = mockTableData.NewRow();
            firstRow["Table_Catalog"] = "TestCatalog";
            firstRow["Table_Schema"] = "TestSchema";
            firstRow["Table_Name"] = "FirstTestTable";
            mockTableData.Rows.Add(firstRow);

            DataRow secondRow = mockTableData.NewRow();
            secondRow["Table_Catalog"] = "TestCatalog";
            secondRow["Table_Schema"] = "TestSchema";
            secondRow["Table_Name"] = "SecondTestTable";
            mockTableData.Rows.Add(secondRow);

            DataRow thirdRow = mockTableData.NewRow();
            thirdRow["Table_Catalog"] = "TestCatalog";
            thirdRow["Table_Schema"] = "TestSchema";
            thirdRow["Table_Name"] = "ThirdTestTable";
            mockTableData.Rows.Add(thirdRow);

            return mockTableData.CreateDataReader();
        }

        public void Map()
        {
            DataTable dt = new DataTable();
            DataRow row = null;
            IDataReader dataReader = null;

            dt.Columns.Add(new DataColumn("id", typeof(int)));
            dt.Columns.Add(new DataColumn("name", typeof(string)));
            dt.Columns.Add(new DataColumn("prop01", typeof(int)));
            dt.Columns.Add(new DataColumn("unset"));
            row = dt.NewRow();
            row["id"] = 5;
            row["name"] = "idIs5";
            row["prop01"] = DBNull.Value;
            dt.Rows.Add(row);
            row = dt.NewRow();
            row["id"] = DBNull.Value;
            row["name"] = "idWithDBNull";
            row["prop01"] = 2;
            dt.Rows.Add(row);
            dataReader = dt.CreateDataReader();
            var entities = FieldMapper.Map<MyEntity01>(new FieldAdoDataReader(dataReader)).ToList();
            Assert.IsTrue(entities.Count == 2, "All entities are created");
            Assert.IsTrue(entities[0].Id == 5, "Field mapping of int is correct");
            Assert.IsTrue(entities[0].Name == "idIs5", "Field mapping of string is correct");
            Assert.IsTrue(entities[0].Prop01 == 99, "Field custom default value is set");
            Assert.IsTrue(entities[0].Unset == default(string), "Field decorated but not returned by the data source is mapped with type's default value");
            Assert.IsTrue(entities[0].UnsetWithDefaultValue == "unset", "Field decorated but not returned by the data source is mapped with field's default value");
            Assert.IsTrue(entities[0].IgnoreDecorated == default(string), "Field decorated with [Ignore] is ignored");
            Assert.IsTrue(entities[0].IgnoreNotDecorated == default(string), "Field not decorated with [Ignore] but not returned by the data source is ignored");
            Assert.IsTrue(entities[1].Id == default(int), "Field mapping of int with DBNull value is correct");
            Assert.IsTrue(entities[1].Prop01 == 2, "Field with default value is mapped with its' correct value if provided by the data source");
        }

        /// <summary>
        /// Compares the schema.
        /// </summary>
        /// <param name="tableName">Name of the table.</param>
        /// <param name="connectionString">The connection string.</param>
        /// <param name="table">The table.</param>
        /// <returns></returns>
        public bool CompareSchema(string tableName, string connectionString, DataTable table)
        {
            bool schemaMatch = false;

            SqlConnection connection = new SqlConnection(connectionString);

            string stagingPrefix = System.Configuration.ConfigurationManager.AppSettings.Get("SapStagingTablePrefix");

            if (IsTableExists(stagingPrefix + tableName, connectionString))
            {
                string selectStatement = "SELECT TOP(1) * FROM " + stagingPrefix + tableName;
                SqlCommand cmd = new SqlCommand(selectStatement);
                cmd.Connection = connection;

                connection.Open();

                try
                {
                    SqlDataReader dr = cmd.ExecuteReader();

                    schemaMatch = dr.GetSchemaTable().Equals(table.CreateDataReader().GetSchemaTable());
                }
                catch (Exception)
                {
                    connection.Close();
                    return false;
                }
            }
            connection.Close();

            return schemaMatch;
        }

 static IDataReader SetupReader()
 {
     var table = new DataTable();
     table.Columns.Add("Name", Type.GetType("System.String"));
     table.Columns.Add("Age", Type.GetType("System.Int32"));
     table.Rows.Add(new object[] { "John Doe", "21" });
     table.Rows.Add(new object[] { "Jane Doe", "19" });
     return table.CreateDataReader();
 }

        protected void Page_Load(object sender, EventArgs e)
        {
            if (Global.CurrentPerson == null || string.IsNullOrEmpty(Request.QueryString["Series"]))
                Response.Redirect("Index.aspx");

            SeriesId = long.Parse(Request.QueryString["Series"]);

            DataTable eventSeatsTable = new DataTable();
            List<Models.EventSeats> eventSeatsModel = new List<Models.EventSeats>();

            using (OracleConnection objConn = new OracleConnection(Global.ConnectionString))
            {
                // Set up the getEventSeats command
                var eventSeatsCommand = new OracleCommand("TICKETS_QUERIES.getEventSeats", objConn) { BindByName = true, CommandType = CommandType.StoredProcedure };
                eventSeatsCommand.Parameters.Add("p_Return", OracleDbType.RefCursor, ParameterDirection.ReturnValue);
                eventSeatsCommand.Parameters.Add("p_SeriesId", OracleDbType.Int64, SeriesId, ParameterDirection.Input);
                eventSeatsCommand.Parameters.Add("p_PersonId", OracleDbType.Int64, Global.CurrentPerson.person_id, ParameterDirection.Input);

                try
                {
                    // Execute the queries and auto map the results to models
                    objConn.Open();
                    var eventSeatsAdapter = new OracleDataAdapter(eventSeatsCommand);
                    eventSeatsAdapter.Fill(eventSeatsTable);
                    eventSeatsModel = Mapper.DynamicMap<IDataReader, List<Models.EventSeats>>(eventSeatsTable.CreateDataReader());
                }
                catch (Exception)
                {
                    Response.Redirect("EventSignup.aspx?Series=" + SeriesId);
                }

                objConn.Close();

                // If the person already has tickets, redirect them to the page where they can review it
                if (eventSeatsModel.Any())
                {
                    Event.Text = eventSeatsModel.FirstOrDefault().name;
                    Date.InnerText = eventSeatsModel.FirstOrDefault().event_datetime.ToString("dddd, MMMM d - h:mm tt");
                    Section.InnerText = eventSeatsModel.FirstOrDefault().description;

                    string location = string.Format("Row {0}, ", eventSeatsModel.FirstOrDefault().seat_row);
                    if (eventSeatsModel.Count > 1)
                    {
                        location += string.Format("Seats {0}-{1}", eventSeatsModel.Min(t => t.seat_number), eventSeatsModel.Max(t => t.seat_number));
                    }
                    else
                    {
                        location += "Seat " + eventSeatsModel.FirstOrDefault().seat_number.ToString();
                    }
                    Location.InnerText = location;

                    Door.InnerText = "Enter By Door " + eventSeatsModel.FirstOrDefault().door;
                }
                else
                    Response.Redirect("EventSignup.aspx?Series=" + SeriesId);
            }
        }

        public void Initialize()
        {
            DataTable table = new DataTable();
            DataColumn idColumn = table.Columns.Add("ID", typeof(int));
            table.Columns.Add("Name", typeof(string));
            table.Rows.Add(new object[] { 1, "Iran" });
            table.Rows.Add(new object[] { 2, "Irak" });
            table.Rows.Add(new object[] { 3, "Isaac" });

            dataReader = table.CreateDataReader();
        }