Hey all, I am currently fiddling around with SQL/Access statements in C# and created a very simple dictionary program that will search for the words depending on the length of each text entered in one text box. So when the user presses the search button it the program will search for the word according to letter or by words itself. I use this data access class to search my database (which is an access database). Here is the class:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
namespace DictionarySystemLibrary
{
public class OLEDBDataAccess : IDataAccess
{
private static string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;" +
@"Data Source=C:\Documents and Settings\Dictionary.accdb;" +
@"Persist Security Info=False;";
private string word_InsertStatement = @"INSERT INTO Word (Word, Definition, PartOfSpeech) " +
@"VALUES (?, ?, ?)";
private string dictionary_SelectAll = @"SELECT * " +
@"FROM Word";
private string dictionary_SelectByWord = @"SELECT [WordID], [Word], [Definition], [PartOfSpeech] " +
@"FROM [Word] " +
@"WHERE [Word] LIKE '" + @"?" + "*'";
private string dictionary_SelectByOneLetter = @"SELECT [Word], [Definition], [PartOfSpeech] " +
@"FROM Word " +
@"WHERE [Word] LIKE '" + @"?" + @"*'";
private string dictionary_SelectByWordID = @"SELECT [Word], [Definition], [PartOfSpeech] " +
@"FROM [Word] " +
@"WHERE [WordID] = ?";
public List<Word> GetListOfWords()
{
List<Word> listOfWords = new List<Word>();
OleDbConnection connectionGate = new OleDbConnection(connectionString);
connectionGate.Open();
OleDbDataAdapter dataBridge = new OleDbDataAdapter(dictionary_SelectAll, connectionGate);
DataSet currentDataSet = new DataSet(dictionary_SelectAll);
dataBridge.Fill(currentDataSet);
foreach (DataRow currentDataRow in currentDataSet.Tables[0].Rows)
{
Word currentWord = new Word();
currentWord.WordID = Convert.ToInt64(currentDataRow["WordID"].ToString());
currentWord.WordName = currentDataRow["Word"].ToString();
currentWord.WordDefinition = currentDataRow["Definition"].ToString();
currentWord.WordPartOfSpeech = (PartOfSpeech)Enum.Parse(typeof(PartOfSpeech), currentDataRow["PartOfSpeech"].ToString());
listOfWords.Add(currentWord);
}
connectionGate.Close();
return listOfWords;
}
public Word GetWordByWholeWord(string currentWordName)
{
Word returningWord = new Word();
OleDbConnection connectionGate = new OleDbConnection(connectionString);
OleDbCommand commandLine = new OleDbCommand(dictionary_SelectByWord, connectionGate);
commandLine.Parameters.Add("Word", OleDbType.VarWChar, 255).Value = currentWordName;
OleDbDataReader dataReader = null;
connectionGate.Open();
dataReader = commandLine.ExecuteReader();
dataReader.Read();
if (dataReader.HasRows)
{
returningWord.WordID = Convert.ToInt64(dataReader["WordID"].ToString());
returningWord.WordName = dataReader["Word"].ToString();
returningWord.WordDefinition = dataReader["Definition"].ToString();
returningWord.WordPartOfSpeech = (PartOfSpeech)Enum.Parse(typeof(PartOfSpeech), dataReader["PartOfSpeech"].ToString());
}
else
{
returningWord = null;
}
connectionGate.Close();
return returningWord;
}
public Word GetWordByLetters(string currentWordLetters)
{
Word returningWord = new Word();
OleDbConnection connectionGate = new OleDbConnection(connectionString);
OleDbCommand commandLine = new OleDbCommand(dictionary_SelectByOneLetter, connectionGate);
commandLine.Parameters.Add("Word", OleDbType.VarWChar, 255).Value = currentWordLetters;
OleDbDataReader dataReader = null;
connectionGate.Open();
dataReader = commandLine.ExecuteReader();
dataReader.Read();
if (dataReader.HasRows)
{
returningWord.WordID = Convert.ToInt64(dataReader["WordID"].ToString());
returningWord.WordName = dataReader["Word"].ToString();
returningWord.WordDefinition = dataReader["Definition"].ToString();
returningWord.WordPartOfSpeech = (PartOfSpeech)Enum.Parse(typeof(PartOfSpeech), dataReader["PartOfSpeech"].ToString());
}
else
{
returningWord = null;
}
connectionGate.Close();
return returningWord;
}
public Word GetWordByWordID(long wordID)
{
Word returningWord = new Word();
OleDbConnection connectionGate = new OleDbConnection(connectionString);
OleDbCommand commandLine = new OleDbCommand(dictionary_SelectByWordID, connectionGate);
commandLine.Parameters.Add("WordID", OleDbType.VarWChar, 255).Value = wordID;
OleDbDataReader dataReader = null;
connectionGate.Open();
dataReader = commandLine.ExecuteReader();
dataReader.Read();
if (dataReader.HasRows)
{
returningWord.WordID = wordID;
returningWord.WordName = dataReader["Word"].ToString();
returningWord.WordDefinition = dataReader["Definition"].ToString();
returningWord.WordPartOfSpeech = (PartOfSpeech)Enum.Parse(typeof(PartOfSpeech), dataReader["PartOfSpeech"].ToString());
}
else
{
returningWord = null;
}
connectionGate.Close();
return returningWord;
}
public void AddWord(Word currentWord)
{
OleDbConnection connectionGate = new OleDbConnection(connectionString);
OleDbCommand commandLine = new OleDbCommand(word_InsertStatement, connectionGate);
commandLine.Parameters.Add("Word", OleDbType.VarWChar, 255).Value = currentWord.WordName;
commandLine.Parameters.Add("Definition", OleDbType.VarWChar, 255).Value = currentWord.WordDefinition;
commandLine.Parameters.Add("PartOfSpeech", OleDbType.VarWChar, 255).Value = currentWord.WordPartOfSpeech.ToString();
connectionGate.Open();
commandLine.ExecuteNonQuery();
connectionGate.Close();
}
}
}I know that my search is not that correct because it only retrieves one word per search, I will be editing the code to make it retrieve a list of words later instead. I do not know still why my data access can't retrieve even just a single word though. I mean if the data reader does extract a single row from the database it must retrieve at least one word right? I used the '*' sign for my like statements because when I use '%' it doesn't retrieve anything. I created a query in Access while using the '*' symbol and it works, I then copy pasted my query in C# and surprisingly it does not work.
Input, suggestions, and solutions will surely be appreciated. Thank you.