使用OleDbConnection如何随机化单词

Question

使用OleDbConnection，如何连接到Access中包含20个单词的表，然后让它从表中随机选择一个单词并将其存储在表单的标签中？

public partial class MainForm : Form
{
// Use this connection string if your database has the extension .accdb
private const String access7ConnectionString =
@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|WordsDB.accdb";

// Data components
private OleDbConnection myConnection;
private DataTable myDataTable;
private OleDbDataAdapter myAdapter;
private OleDbCommandBuilder myCommandBuilder;
// Index of the current record
private int currentRecord = 0;

private void MainForm_Load(object sender, EventArgs e)
{
String command = "SELECT * FROM Words";
try
{
myConnection = new OleDbConnection(access7ConnectionString);
myAdapter = new OleDbDataAdapter(access7ConnectionString, myConnection);
myCommandBuilder = new OleDbCommandBuilder(myAdapter);
myDataTable = new DataTable();
FillDataTable(command);
DisplayRow(currentRecord);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void FillDataTable(String selectCommand)
{
try
{
myConnection.Open();
myAdapter.SelectCommand.CommandText = selectCommand;
// Fill the datatable with the rows reurned by the select command
myAdapter.Fill(myDataTable);
myConnection.Close();
}
catch(Exception ex)
{
MessageBox.Show("Error in FillDataTable : rn" + ex.Message);
}
}
private void DisplayRow(int rowIndex)
{
// Check that we can retrieve the given row
if (myDataTable.Rows.Count == 0)
return; // nothing to display
if (rowIndex >= myDataTable.Rows.Count)
return; // the index is out of range
// If we get this far then we can retrieve the data
try
{
DataRow row = myDataTable.Rows[rowIndex];
WordsLbl.Text = row["SpellingWords"].ToString();   
}
catch (Exception ex)
{
MessageBox.Show("Error in DisplayRow : rn" + ex.Message);
}
}
}

正如你所看到的，这就是代码，我试图理解的是，如何从数据库中的表中获得一个随机单词并将其存储在标签中？此外，我正在使用OleDbConnection来连接到数据库！

Answer 1

您可以在DataTable上使用AsEnumerable，然后可以在其中随机选择一个单词。例如，在填写数据表后：

FillDataTable(command); 
var words = myDataTable.AsEnumerable()
.Select(d => d["word"].ToString())
.ToList();
Random rnd = new Random();
int randomNumber = rnd.Next(0, words.Count + 1);
string randomWord = words[randomNumber];
label1.Text = randomWord;

这应该行得通。您只需要用数据库中的列名更改word，如下所示：.Select(d => d["word"].ToString())

Answer 2

由于您使用的是访问，所以一旦您的数据表中填充了行，就可以执行此操作。我已经能够在填充数据表之后对此进行测试。我从另一个数据库填充了我的数据表，但这并不重要。我的代码的最后4行帮助您每次从数据表中获得一个随机记录。

{
const string query = "SELECT name FROM NewTable";
var command = new SqlCommand(){CommandText = query, CommandType = System.Data.CommandType.Text,Connection=sqlConn};
var dataAdapter = new SqlDataAdapter() { SelectCommand = command };
DataTable dataTable = new DataTable("Names");
dataAdapter.Fill(dataTable);
int count = dataTable.Rows.Count; 
int index = new Random().Next(count);
DataRow d = dataTable.Rows[index];
Console.WriteLine(d[0]);
}

Answer 3

在查看了我的答案的"被拒绝的编辑"后，以下是适用于原始问题的部分：

你可以尝试使用"随机"类来生成这样的随机数：

// =========== Get a Random Number first, then call DisplayRow() ===============
// Figure out the upper range of numbers to choose from the rows returned
int maxCount = myDataTable.Rows.Count;
// generate a random number to use for "rowIndex" in your 'myDataTable.Rows[rowIndex]'
Random randomNR = new Random();
int myRndNR = randomNR.Next(0, maxCount - 1);
// Execute your DisplayRow(int rowIndex) using myRndNR
DisplayRow(myRndNR);
// ===========

至于为什么你的程序不起作用(在评论中被问到)，我建议如下：

我将您的代码复制并粘贴到一个新的Project中，并按照您的描述创建了一个Access数据库。。。我相信你遇到的问题是：

问题一：你的程序找不到"WordsDB.accdb".

可能的解决方案：

(1) 您可以找出如何在"连接字符串"或中创建正确的|DataDirectory|路径

(2) 将你的"WordsDB.accdb"放在一个可以引用的文件夹中，完整路径如下"Data Source=C:\inetpub\wwwroot\app_Data\WordsDB.accdb"，这样你的"连接字符串"看起来如下：

private const String access7ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;
Data Source=C:inetpubwwwrootapp_dataWordsDB.accdb";

问题二：您需要将Random代码移出当前位置，以便在填充DataTable之后执行它。

可能的解决方案：

(1) 将Form1.cs更改为如下所示：

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.OleDb;
namespace WindowsFormsApplication1
{
public partial class Form1 : Form
{
// Use this connection string if your database has the extension .accdb
private const String access7ConnectionString =
@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:inetpubwwwrootapp_dataWordsDB.accdb";

// Data components
private OleDbConnection myConnection;
private DataTable myDataTable;
private OleDbDataAdapter myAdapter;
private OleDbCommandBuilder myCommandBuilder;
// Index of the current record
private int currentRecord = 0;
private void MainForm_Load(object sender, EventArgs e)
{
String command = "SELECT * FROM Words";
try
{
myConnection = new OleDbConnection(access7ConnectionString);
myAdapter = new OleDbDataAdapter(access7ConnectionString, myConnection);
myCommandBuilder = new OleDbCommandBuilder(myAdapter);
myDataTable = new DataTable();
FillDataTable(command);
/* Move this "DisplayRow(currentRecord);" down below and use 
after you get a random number to pick a random word as shown
below... */
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
// =========== Get a Random Number first, then call DisplayRow() ===============
// Figure out the upper range of numbers to choose from the rows returned
int maxCount = myDataTable.Rows.Count;
// generate a random number to use for "rowIndex" in your 'myDataTable.Rows[rowIndex]'
Random randomNR = new Random();
int myRndNR = randomNR.Next(0, maxCount - 1);
// Execute your DisplayRow(int rowIndex) using myRndNR
DisplayRow(myRndNR);
// ===========
}
private void FillDataTable(String selectCommand)
{
try
{
myConnection.Open();
myAdapter.SelectCommand.CommandText = selectCommand;
// Fill the DataTable with the rows returned by the select command
myAdapter.Fill(myDataTable);
myConnection.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error in FillDataTable : rn" + ex.Message);
}
}
private void DisplayRow(int rowIndex)
{
// Check that we can retrieve the given row
if (myDataTable.Rows.Count == 0)
return; // nothing to display
if (rowIndex >= myDataTable.Rows.Count)
return; // the index is out of range
// If we get this far then we can retrieve the data
try
{
DataRow row = myDataTable.Rows[rowIndex];
WordsLbl.Text = row["SpellingWords"].ToString();
}
catch (Exception ex)
{
MessageBox.Show("Error in DisplayRow : rn" + ex.Message);
}
}
public Form1()
{
InitializeComponent();
}
}
}

潜在问题：

您的表单可能没有设置"事件、行为、加载"来执行

MainForm_Load

潜在解决方案：

a。转到Visual Studio中的设计视图，右键单击您的窗体。

b。点击弹出窗口上的"属性">

c。在"属性"窗口(可能在Visual Studio的右下角)中查找"事件">

d。一旦您找到"事件"，请查找"行为"(如果您按"类别"对属性进行排序)或"加载"(如果属性按字母顺序排序)

e。在"加载"框中，确保上面写着"MainForm_Load">