C# DataSet.CreateDataReader方法代码示例

        private void ReadExcelData(OleDbConnection conn, string sheetName)
        {
            Console.WriteLine("Reading data...");
            var excelDbCommand = new OleDbCommand(@"SELECT * FROM [" + sheetName + "]", conn);
            using (var oleDbDataAdapter = new OleDbDataAdapter(excelDbCommand))
            {
                DataSet ds = new DataSet();
                oleDbDataAdapter.Fill(ds);
                var locations = new List<Location>();
                using (var reader = ds.CreateDataReader())
                {
                    while (reader.Read())
                    {
                        var loc = new Location();
                        loc.Name = reader["City"].ToString();
                        locations.Add(loc);
                    }
                }

                var db = new MagicalCreatureDbContext();
                foreach (var loc in locations)
                {
                    db.Locations.Add(loc);
                }

                db.SaveChanges();
            }
        }

        public void GetAppwiseActionsChart1()
        {
            try
            {
                //BindingList<string> analyticDataList;
                dsAnalytics = new DataSet();
                dsAnalytics = analyticsBiz.GetAppwiseActionsChart1(CustomerName, AppName);

                DataTableReader reader = dsAnalytics.CreateDataReader();

                Chart8.DataBindCrossTable(reader, "NotifierType", "YearMonth", "NotifierTypeCount", "Label=NotifierTypeCount");

                PeriodViseDrill();
                foreach (Series ser in Chart8.Series)
                {
                    ser.ShadowOffset = 0;
                    ser.BorderWidth = 2;
                    ser.ChartType = SeriesChartType.StackedBar;

                    //ser.MarkerSize = 5;
                    //ser.MarkerStyle = marker;
                    ser.MarkerBorderColor = Color.FromArgb(64, 64, 64);
                    ser.Font = new Font("Lucida Grande", 8, FontStyle.Regular);
                    //marker++;
                }

            }
            catch (Exception ex)
            {
                throw;
            }
        }

        public void ReadExcelSheet(string fileName, string sheetName, Action<DataTableReader> actionForEachRow)
        {
            var connectionString = string.Format(ExcelSettings.Default.ExcelConnectionString, fileName);

            using (var excelConnection = new OleDbConnection(connectionString))
            {
                excelConnection.Open();

                if (sheetName == null)
                {
                    var excelSchema = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    if (excelSchema != null)
                    {
                        sheetName = excelSchema.Rows[0]["TABLE_NAME"].ToString();
                    }
                }

                var excelDbCommand = new OleDbCommand(@"SELECT * FROM [" + sheetName + "]", excelConnection);

                using (var oleDbDataAdapter = new OleDbDataAdapter(excelDbCommand))
                {
                    var dataSet = new DataSet();
                    oleDbDataAdapter.Fill(dataSet);

                    using (var reader = dataSet.CreateDataReader())
                    {
                        while (reader.Read())
                        {
                            actionForEachRow(reader);
                        }
                    }
                }
            }
        }

        private static void ReadExcelData()
        {
            var connectionString = GetConnectionString();
            using (var oleDbConnection = new OleDbConnection(connectionString))
            {
                oleDbConnection.Open();
                var sheetName = GetSheetName(oleDbConnection);
                var cmd = GetOleDbCommand(sheetName, oleDbConnection);

                using (var oleDbAdapter = new OleDbDataAdapter(cmd))
                {
                    var dataSet = new DataSet();
                    oleDbAdapter.Fill(dataSet);

                    using (var reader = dataSet.CreateDataReader())
                    {
                        while (reader.Read())
                        {
                            var name = reader["Name"];
                            var score = reader["Score"];
                            Console.WriteLine("{0}: {1}", name, score);
                        }
                    }
                }
            }
        }

 /// <summary>
 /// Initializes a new instance of the <see cref="ProtoDataStream"/> class.
 /// </summary>
 /// <param name="dataSet">The <see cref="DataSet"/>who's contents to serialize.</param>
 /// <param name="options"><see cref="ProtoDataWriterOptions"/> specifying any custom serialization options.</param>
 /// <param name="bufferSize">Buffer size to use when serializing rows. 
 /// You should not need to change this unless you have exceptionally
 /// large rows or an exceptionally high number of columns.</param>
 public ProtoDataStream(
     DataSet dataSet,
     ProtoDataWriterOptions options,
     int bufferSize = DefaultBufferSize)
     : this(dataSet.CreateDataReader(), options, bufferSize)
 {
 }

        public ICollection<Flight> ImportFlightsDataFromFile(string filePath)
        {
            if (!Regex.IsMatch(filePath, FlightsWorksheetFilePattern))
            {
                throw new ArgumentException(string.Format(InvalidFileNameMessage, "Flights"));
            }

            OleDbConnection connection = new OleDbConnection();
            connection.ConnectionString = string.Format(AirportsDbSettings.Default.ExcelReaderConnectionString, filePath);

            connection.Open();

            using (connection)
            {
                var schema = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                var sheetName = schema.Rows[0]["TABLE_NAME"].ToString();

                OleDbCommand selectAllRowsCommand = new OleDbCommand("SELECT * FROM [" + sheetName + "]", connection);

                ICollection<Flight> importedFlights = new HashSet<Flight>();

                using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectAllRowsCommand))
                {
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);

                    using (DataTableReader reader = dataSet.CreateDataReader())
                    {
                        while (reader.Read())
                        {
                            try
                            {
                                string flightCode = reader["FlightCode"].ToString();
                                int airlineId = int.Parse(reader["AirlineId"].ToString());
                                int departureAirportId = int.Parse(reader["DepartureAirportId"].ToString());
                                int arrivalAirportId = int.Parse(reader["ArrivalAirportId"].ToString());
                                double durationHours = double.Parse(reader["Duration"].ToString());
                                DateTime date = DateTime.Parse(reader["DateTime"].ToString(), CultureInfo.InvariantCulture);

                                var flight = new Flight()
                                {
                                    FlightCode = flightCode,
                                    AirlineId = airlineId,
                                    FlightDate = date,
                                    DurationHours = durationHours,
                                    DepartureAirportId = departureAirportId,
                                    ArrivalAirportId = arrivalAirportId
                                };

                                importedFlights.Add(flight);
                            }
                            catch (FormatException)
                            { }
                        }
                    }
                }

                return importedFlights;
            }
        }

        private static void ReadExcelData()
        {
            using (var excelConnection = new OleDbConnection(Settings.Default.excelConnection))
            {
                excelConnection.Open();
                string sheetName = GetSheetName(excelConnection);
                OleDbCommand excelCommand = GetOleDbCommand(sheetName, excelConnection);

                using (OleDbDataAdapter oleDbDataAdapter = new OleDbDataAdapter(excelCommand))
                {
                    DataSet dataSet = new DataSet();
                    oleDbDataAdapter.Fill(dataSet);

                    using (DataTableReader reader = dataSet.CreateDataReader())
                    {
                        while (reader.Read())
                        {
                            var fullName = reader["Name"];
                            var score = reader["Score"];

                            Console.WriteLine(fullName + " -> " + score);
                        }
                    }
                }
            }
        }

        static void Main()
        {
            string excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=../../score_db.xlsx;Extended Properties='Excel 12.0 xml;HDR=Yes';";
            var excelConnection = new OleDbConnection(excelConnectionString);
            excelConnection.Open();

            DataTable excelSchema = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            string sheetName = excelSchema.Rows[0]["TABLE_NAME"].ToString();

            OleDbCommand excelCommand = new OleDbCommand("SELECT * FROM [" + sheetName + "]", excelConnection);

            using (excelConnection)
            {
                using (OleDbDataAdapter adapter = new OleDbDataAdapter(excelCommand))
                {
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);

                    using (DataTableReader reader = dataSet.CreateDataReader())
                    {
                        while (reader.Read())
                        {
                            var userName = reader["Name"];
                            var score = reader["Score"];

                            Console.WriteLine(userName + " -> " + score);
                        }
                    }
                }
            }
        }

        public static void Main()
        {
            var excelConnection = new OleDbConnection(Settings.Default.ExcelConnection);
            excelConnection.Open();

            DataTable excelSchema = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            string sheetName = excelSchema.Rows[0]["TABLE_NAME"].ToString();

            OleDbCommand excelCommand = new OleDbCommand(@"SELECT *
                                                           FROM [" + sheetName + "]", excelConnection);

            using (excelConnection)
            {
                using (OleDbDataAdapter oleDbDataAdapter = new OleDbDataAdapter(excelCommand))
                {
                    DataSet dataSet = new DataSet();
                    oleDbDataAdapter.Fill(dataSet);

                    using (DataTableReader reader = dataSet.CreateDataReader())
                    {
                        while (reader.Read())
                        {
                            var fullName = reader["Name"];
                            var score = reader["Score"];

                            Console.WriteLine(fullName + " -> " + score);
                        }
                    }
                }
            }
        }

        static void Main()
        {
            var connectionStringFor2007OrNewer = "Provider = Microsoft.ACE.OLEDB.12.0; Extended Properties = Excel 12.0 XML; Data Source = ../../../scores.xlsx;";
            //var connectionStringForOlder = "Provider = Microsoft.Jet.OLEDB.4.0; Extended Properties = Excel 8.0; Data Source = ../../../scores.xlsx;";

            using (var dbCon = new OleDbConnection(connectionStringFor2007OrNewer))
            {
                dbCon.Open();
                var docName = dbCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
                var cmd = new OleDbCommand("SELECT * FROM [" + docName + "]", dbCon);

                using (var oleDbAdapter = new OleDbDataAdapter(cmd))
                {
                    var dataSet = new DataSet();
                    oleDbAdapter.Fill(dataSet);

                    using (var reader = dataSet.CreateDataReader())
                    {
                        while (reader.Read())
                        {
                            var name = reader["Name"];
                            var score = reader["Score"];
                            Console.WriteLine("{0}: {1}", name, score);
                        }
                    }
                }
            }
        }

        public static void Main()
        {
            //// 6. Create an Excel file with 2 columns: name and score:
            //// Write a program that reads your MS Excel file through the OLE DB data provider and displays the name and score 
            //// row by row.

            var excelConnection = new OleDbConnection(Settings.Default.excelConnection);
            excelConnection.Open();

            DataTable excelSchema = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            string sheetName = excelSchema.Rows[0]["TABLE_NAME"].ToString();

            var excelCommand = new OleDbCommand(@"SELECT * FROM [" + sheetName + "]", excelConnection);

            using (excelConnection)
            {
                using (OleDbDataAdapter oleDbDataAdapter = new OleDbDataAdapter(excelCommand))
                {
                    var dataSet = new DataSet();
                    oleDbDataAdapter.Fill(dataSet);

                    using (DataTableReader reader = dataSet.CreateDataReader())
                    {
                        while (reader.Read())
                        {
                            var fullName = reader["Name"];
                            var score = reader["Age"];

                            Console.WriteLine(fullName + " -> " + score);
                        }
                    }
                }
            }
        }

        public static void ReadFromExcel()
        {
            OleDbConnection excelConnection = new OleDbConnection(InputFileConnectionString);

            using (excelConnection)
            {
                excelConnection.Open();

                DataTable tableSchema = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                string sheetName = tableSchema.Rows[0]["TABLE_NAME"].ToString();

                OleDbCommand command = new OleDbCommand("SELECT * FROM [" + sheetName + "]", excelConnection);

                OleDbDataAdapter dataAdapter = new OleDbDataAdapter(command);

                using (dataAdapter)
                {
                    DataSet dataSet = new DataSet();

                    dataAdapter.Fill(dataSet);

                    using (var reader = dataSet.CreateDataReader())
                    {
                        while (reader.Read())
                        {
                            //Console.WriteLine("{0} has a score of {1}", reader["Name"], reader["Score"]);
                            Console.WriteLine(
                                string.Format("TesterName: {0}, PlaceName: {1}, PlaceArea: {2}, Slope: {3}, Date: {4}, TestResult: {5}",
                                reader["TesterName"], reader["PlaceName"], reader["PlaceArea"], reader["Slope"], reader["Date"], reader["TestResult"]));
                        }
                    }
                }
            }
        }

        public ICollection<Contract> ImportContractsDataFromFile(string filePath)
        {
            Console.WriteLine("Importing data from excel files");

            OleDbConnection connection = new OleDbConnection();

            string connectionString =
                ConfigurationManager.ConnectionStrings["TelecommunicationProviderExcelData"].ConnectionString;
            connection.ConnectionString = string.Format(connectionString, filePath);

            connection.Open();

            using (connection)
            {
                var schema = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                var sheetName = schema.Rows[0]["TABLE_NAME"].ToString();

                OleDbCommand selectAllRowsCommand = new OleDbCommand("SELECT * FROM [" + sheetName + "]", connection);

                ICollection<Contract> importedContracts = new HashSet<Contract>();

                using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectAllRowsCommand))
                {
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);

                    using (DataTableReader reader = dataSet.CreateDataReader())
                    {
                        while (reader.Read())
                        {
                            try
                            {
                                DateTime startDate = DateTime.Parse(reader["StartDate"].ToString());
                                DateTime endDate = DateTime.Parse(reader["EndDate"].ToString());
                                int packageId = int.Parse(reader["PackageID"].ToString());
                                int telephoneId = int.Parse(reader["TelephoneID"].ToString());

                                var contract = new Contract
                                                   {
                                                       StartDate = startDate,
                                                       EndDate = endDate,
                                                       PackageId = packageId,
                                                       TelephoneNumberId = telephoneId
                                                   };

                                importedContracts.Add(contract);
                            }
                            catch (FormatException ex)
                            {
                                Console.WriteLine(ex.Message);
                            }
                        }
                    }
                }

                Console.WriteLine("Importing data from excel completed!");
                return importedContracts;
            }
        }

 private void button1_Click_1(object sender, EventArgs e)
 {
     bool goodop = false;
     DialogResult dr = MessageBox.Show("Warning, this will erase all current students and repopulate with the selected Excel spreadsheet\nMake sure the Excel document is formated in this order and format \"Student ID\" \"LastName\" \"FirstName\" \"Grade\"\nAll student Info must be on sheet1\nExcel document type: 97-2003 *.xls\nDo you want to continue?", "Verification", MessageBoxButtons.YesNo);
     if (dr == DialogResult.Yes)
     {
         OpenFileDialog ofd = new OpenFileDialog();
         if (ofd.ShowDialog() == DialogResult.OK)
         {
             DialogResult dr2 = MessageBox.Show("Warning, this will erase all current students and repopulate with the selected Excel spreadsheet\nMake sure the Excel document is formated in this order\n\"Student ID\" \"LastName\" \"FirstName\" \"Grade\"\nThis will reappear and notify when the operation is completed\n5-10 minutes is required to complete this operation.\nDo you want to continue?", "Verification", MessageBoxButtons.YesNo);
             if (dr2 == DialogResult.Yes)
             {
                 try
                 {
                     this.Hide();
                     if (gl.oleconnection.State == ConnectionState.Open)
                         gl.oleconnection.Close();
                     System.Data.OleDb.OleDbConnection excelconnection = new System.Data.OleDb.OleDbConnection(@"provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + ofd.FileName + "';Extended Properties=Excel 8.0;");
                     System.Data.DataSet DtSet = new System.Data.DataSet();
                     System.Data.OleDb.OleDbDataAdapter oledbd_readexcel = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", excelconnection);
                     oledbd_readexcel.Fill(DtSet);
                     excelconnection.Close();
                     if (gl.oleconnection.State == ConnectionState.Closed) gl.oleconnection.Open();
                     DataTableReader dtr_excel = DtSet.CreateDataReader(DtSet.Tables[0]);
                     string dsql = "DELETE * FROM `" + gl.tbl_studentinfo + "`";
                     string[] dpar = { };
                     string[] dvalues = { };
                     dbi.dbcommands(dsql, dpar, dvalues);
                     while (dtr_excel.Read())
                     {
                         if (gl.oleconnection.State == ConnectionState.Closed) gl.oleconnection.Open();
                         OleDbDataAdapter oledba_massadd = new OleDbDataAdapter();
                         string studentid = dtr_excel.GetValue(0).ToString();
                         string lastname = dtr_excel.GetValue(1).ToString();
                         string firstname = dtr_excel.GetValue(2).ToString();
                         string grade = dtr_excel.GetValue(3).ToString();
                         string sql = "INSERT INTO `" + gl.tbl_studentinfo + "` VALUES ('" + 0 + "',@studentid,@lastname,@firstname,@grade)";
                         string[] pars = { "@studentid", "@lastname", "@firstname", "@grade" };
                         string[] values = { studentid, lastname, firstname, grade };
                         dbi.dbcommands(sql, pars, values);
                     }
                     this.Show();
                     dbupdater();
                     //datagridupdate();
                     MessageBox.Show("All students have been added successfully");
                     goodop = true;
                 }
                 catch (Exception ex)
                 {
                     this.Show();
                     MessageBox.Show(ex.ToString());
                 }
             }
         }
     }
     if (!goodop)
         MessageBox.Show("Canceled", "Operation Status");
 }

 public void GetLocationDrillDown1_Actions()
 {
     //IEnumerable<string> analyticDataList;
     dsAnalytics = new DataSet();
     dsAnalytics = analyticsBiz.GetLocationDrillDown1_Actions(CustomerName, AppName, State);
     DataTableReader reader=dsAnalytics.CreateDataReader();
     Chart4.DataBindCrossTable(reader, "Gender", "NotifierType", "NotifierTypeCount", "Label=NotifierTypeCount");
     GenderDrill();
 }