一、实验目的
- 掌握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