一、实验目的
掌握ADO .NET数据离线与在线访问模型；
掌握应用LINQ进行数据查询；

二、实验内容

基于ADO.NET技术进行数据操作

已提供一个控制台程序的框架。附件中提供了各种格式的Northwind数据库。

参考案例程序，连接上数据库，进行各种操作，补全该程序。

一些可用的SQL语句：

select * from 客户 where 城市='南京'//找到所有位于南京的客户

select count(*) from 客户 where 城市='南京'//统计南京客户的数量

update 客户 set 城市='天津' where 客户ID='ALFKI'//更新客户信息，若满足条件，就将“城市”字段改为天津

可自由选择所查询的数据库表和参数。注意，SQL中字符串需加单引号。

    class Test
    {
        static string _mdbFile = "D:/Northwind.mdb";
        static OleDbConnection _connection;
        static OleDbDataAdapter _adapter;
        static DataSet _dataset;

        public static void Main(string[] argv)
        {
            while (true)
            {
                int index = 1;
                Console.WriteLine("=====Menu=====");
                Console.WriteLine(index++ + ". 连接数据库");
                Console.WriteLine(index++ + ". 查询数据行");
                Console.WriteLine(index++ + ". 数据统计");
                Console.WriteLine(index++ + ". 更新数据库");
                Console.WriteLine(index++ + ". 数据库离线查询");
                Console.WriteLine(index++ + ". 离线LINQ查询");
                Console.WriteLine(index++ + ". 断开数据库");
                Console.WriteLine(index++ + ". 退出");
                Console.WriteLine("=====End======");
                Console.Write("请选择一个操作：");

                string input = Console.ReadLine();
                int choice;
                bool isDigit = int.TryParse(input, out choice);
                if (!isDigit || choice >= index || choice < 1)
                {
                    Console.WriteLine("错误选项");
                }

                if (choice == index - 1)
                    break;

                switch (choice)
                {
                    case 1:
                        ConnectToDB();
                        break;
                    case 2:
                        QueryWithReader();
                        break;
                    case 3:
                        QueryScalar();
                        break;
                    case 4:
                        UpdateOnLine();
                        break;
                    case 5:
                        QueryOffLine();
                        break;
                    case 6:
                        LINQQuery();
                        break;
                    case 7:
                        CloseDB();
                        break;
                }
                Console.WriteLine();
            }
        }

        public static bool IsConnected()
        {
            if (_connection == null || _connection.State != ConnectionState.Open)
            {
                Console.WriteLine("数据库未连接");
                return false;
            }
            return true;
        }

        public static void ConnectToDB()
        {
            string connString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + _mdbFile;
            _connection = new OleDbConnection(connString);
            //补全
        }

        public static void QueryWithReader()
        {
            if (!IsConnected())
                return;

            var command = _connection.CreateCommand();
            //补全
        }

        public static void QueryScalar()
        {
        }

        public static void UpdateOnLine()
        {
        }

        public static void QueryOffLine()
        {
        }

        public static void LINQQuery()
        {
        }

        public static void CloseDB()
        {
        }
    }

源代码

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Homework12
{


    class Test
    {
        static string _mdbFile = "E:\School Life\University\Study\计算机\程序语言\C#\第十三次课0529\Northwind\Northwind.mdb";
        static OleDbConnection _connection;
        static OleDbDataAdapter _adapter;
        static DataSet _dataset;

        public static void Main(string[] argv)
        {
            while (true)
            {
                int index = 1;
                Console.WriteLine("=====Menu=====");
                Console.WriteLine(index++ + ". 连接数据库");
                Console.WriteLine(index++ + ". 查询数据行");
                Console.WriteLine(index++ + ". 数据统计");
                Console.WriteLine(index++ + ". 更新数据库");
                Console.WriteLine(index++ + ". 数据库离线查询");
                Console.WriteLine(index++ + ". 离线LINQ查询");
                Console.WriteLine(index++ + ". 断开数据库");
                Console.WriteLine(index++ + ". 退出");
                Console.WriteLine("=====End======");
                Console.Write("请选择一个操作：");

                string input = Console.ReadLine();
                int choice;
                bool isDigit = int.TryParse(input, out choice);
                if (!isDigit || choice >= index || choice < 1)
                {
                    Console.WriteLine("错误选项");
                }

                if (choice == index - 1)
                    break;

                switch (choice)
                {
                    case 1:
                        ConnectToDB();
                        break;
                    case 2:
                        QueryWithReader();
                        break;
                    case 3:
                        QueryScalar();
                        break;
                    case 4:
                        UpdateOnLine();
                        break;
                    case 5:
                        QueryOffLine();
                        break;
                    case 6:
                        LINQQuery();
                        break;
                    case 7:
                        CloseDB();
                        break;
                }
                Console.WriteLine();
            }
        }

        public static bool IsConnected()
        {
            if (_connection == null || _connection.State != ConnectionState.Open)
            {
                Console.WriteLine("数据库未连接");
                return false;
            }
            return true;
        }

        public static void ConnectToDB()
        {
            string connString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + _mdbFile;
            _connection = new OleDbConnection(connString);
            //补全
            try
            {
                _connection.Open();
                Console.WriteLine("数数据库连接成功：" + _mdbFile + " is " + _connection.State);
            }
            catch(Exception e){
                Console.WriteLine(e.Message);
                _connection.Close();
            }
            finally {
                
            }

        }

        public static void QueryWithReader()
        {
            if (!IsConnected())
                return;

            var command = _connection.CreateCommand();
            //补全
            command.CommandText = "select * from 客户 where 城市='南京'";
            OleDbDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {
                Console.WriteLine(reader[0].ToString());
            }
            reader.Close();
        }

        public static void QueryScalar()
        {
            if (!IsConnected())
                return;

            var command = _connection.CreateCommand();
            command.CommandText = "select count(*) from 客户 where 城市='南京'";
            int count = (int)command.ExecuteScalar();
            Console.WriteLine("记录总数："+count);
        }

        public static void UpdateOnLine()
        {
            if (!IsConnected())
                return;

            var command = _connection.CreateCommand();
            command.CommandText = "update 客户 set 城市='天津' where 客户ID='ALFKI'";
            int count = (int)command.ExecuteNonQuery();
            Console.WriteLine("更新 " + count + " 行");
        }

        public static void QueryOffLine()
        {
            if (!IsConnected())
                return;

            var command = _connection.CreateCommand();
            command.CommandText = "select * from 客户";
            _dataset = new DataSet();
            DataTable dt1 = new DataTable("Scene");
            _dataset.Tables.Add(dt1);
            _adapter = new OleDbDataAdapter(command);
            _adapter.Fill(dt1);
            var query = from x in dt1.AsEnumerable()
                        select x;
            foreach (DataColumn column in dt1.Columns)
                Console.Write(column.ColumnName+"t");
            Console.WriteLine();
            foreach (var row in query){
                foreach (DataColumn column in dt1.Columns)
                    Console.Write(row[column.ColumnName] + "t");
                Console.WriteLine();
            }
        }

        public static void LINQQuery()
        {
            if (!IsConnected())
                return;

            var command = _connection.CreateCommand();
            command.CommandText = "select * from 客户 where 城市='南京'";
            _dataset = new DataSet();
            DataTable dt1 = new DataTable("Scene");
            _dataset.Tables.Add(dt1);
            _adapter = new OleDbDataAdapter(command);
            _adapter.Fill(dt1);
            var query = from x in dt1.AsEnumerable()
                        where (string)x["城市"] == "南京"
                        select x;
            foreach (DataColumn column in dt1.Columns)
                Console.Write(column.ColumnName + "t");
            Console.WriteLine();
            foreach (var row in query)
            {
                foreach (DataColumn column in dt1.Columns)
                    Console.Write(row[column.ColumnName] + "t");
                Console.WriteLine();
            }
        }

        public static void CloseDB()
        {
            if (!IsConnected())
                return;

            _connection.Close();
            Console.WriteLine("数数据库关闭成功：" + _mdbFile + " is " + _connection.State);
        }
    }
}

运行结果

三、实验心得与体会

掌握ADO .NET数据离线与在线访问模型；
掌握应用LINQ进行数据查询；

参考文章

https://blog.csdn.net/zac_sian/article/details/51161563

https://www.runoob.com/csharp/csharp-for-loop.html

https://docs.microsoft.com/zh-cn/dotnet/api/system.data.datarow?view=netframework-4.8

https://docs.microsoft.com/zh-cn/dotnet/api/system.data.oledb.oledbdataadapter?view=dotnet-plat-ext-3.1

C#——《C#语言程序设计》实验报告——数据库编程——基于ADO.NET技术的数据库操作DEMO

一、实验目的
掌握ADO .NET数据离线与在线访问模型；
掌握应用LINQ进行数据查询；

.Net相关栏目本月热门文章

C#——《C#语言程序设计》实验报告——数据库编程——基于ADO.NET技术的数据库操作DEMO

一、实验目的 掌握ADO .NET数据离线与在线访问模型；掌握应用LINQ进行数据查询；

.Net相关栏目本月热门文章

一、实验目的
掌握ADO .NET数据离线与在线访问模型；
掌握应用LINQ进行数据查询；