Jump to content
  • Advertisement
Sign in to follow this  
sanch3x

[.net] C# adding/reading data to access database

This topic is 4252 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

Hello, I agreed to make a simple app for a friend of mine who's trying to learn stuff on the go. Basically the app shows him a defination and he has to guess the word. He wanted me to add categories so whenever he added new words to try and memorize he could study only specific categories. So I made an access database and I've got my categories table and my words table. I managed to define my binding sources and my table adapters but I can't figure out how to add data and/or how to read data from these tables. It would be incredibly easy to do this through a web app (since that's where all my experience lies) but these windows forms are giving me a headache. Essentially when I click on 'add' I'd like: insert into words columns(word, defination, category) values ( txtBox_word.Text, txtBox_defination.Text, cbBox_category.selected // these are the fields filled out by the user ) (some example code would be nice) Any help is appreciated.

Share this post


Link to post
Share on other sites
Advertisement
Here's a complete listing for a little utility class I wrote up a while ago. Its set up to read DBASEIII files rather than MDBs so there are a few references to dbf floating around in it. I commented out the Extended Properties parameter. Without that, it'll want an MDB instead of a directory full of dbase files.

I made a few changes here and there that I reckon it would need but no promises - I havn't really tested it. It should certainly provide you with enough to get started.


using System;
using System.IO;
using System.Collections;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;

namespace Whatever
{
public static class DBase
{
private const string dbfconstr = @"Provider=Microsoft.Jet.OLEDB.4.0;"
+ "Data Source={0};"
//+ @"Extended Properties=DBASE III;";

public static string MakeConString(string dbfpath)
{
//string dbfdir = System.IO.Path.GetDirectoryName(dbfpath);
//string constr = string.Format(dbfconstr, dbfdir);
//return constr;

// Modified for use with MDB
return string.Format(dbfconstr, dbfpath);
}

public static Dictionary<string, Type> GetSchema(string dbfpath)
{
Dictionary<string, Type> fields = new Dictionary<string, Type>();

string constr = MakeConString(dbfpath);

using (OleDbConnection cnn = new OleDbConnection(constr))
{
cnn.Open();

string dbfname = System.IO.Path.GetFileNameWithoutExtension(dbfpath);
string sql = "SELECT * FROM " + dbfname + ";";

using (OleDbCommand cmd = new OleDbCommand(sql))
{
cmd.Connection = cnn;
using (OleDbDataReader rdr = cmd.ExecuteReader())
{
using (DataTable schema = rdr.GetSchemaTable())
{
foreach (DataRow row in schema.Rows)
{
string nme = row["ColumnName"].ToString().ToUpper();
Type typ = (System.Type)row["DataType"];
fields.Add(nme, typ);
}
}
}
}
}

return fields;
}


public static DbDataReader GetDBASE(string dbfpath)
{
string constr = MakeConString(dbfpath);
OleDbConnection cnn = new OleDbConnection(constr);
cnn.Open();

string dbfname = System.IO.Path.GetFileNameWithoutExtension(dbfpath);
string sql = "SELECT * FROM " + dbfname + ";";
OleDbCommand cmd = new OleDbCommand(sql);
cmd.Connection = cnn;

OleDbDataReader rdr = cmd.ExecuteReader();

return rdr;
}

public static DataTable Load(string dbfpath)
{
//string dbfdir = System.IO.Path.GetDirectoryName(dbfpath);
//string constr = string.Format(dbfconstr, dbfdir);

string constr = MakeConString(dbfpath);

OleDbConnection cnn = new OleDbConnection(constr);
cnn.Open();

string dbfname = System.IO.Path.GetFileNameWithoutExtension(dbfpath);
string sql = "SELECT * FROM " + dbfname + ";";
OleDbCommand cmd = new OleDbCommand(sql);
cmd.Connection = cnn;

OleDbDataReader rdr = cmd.ExecuteReader();

DataTable table = new DataTable(dbfname);

using (DataTable schema = rdr.GetSchemaTable())
{
foreach (DataRow row in schema.Rows)
{
DataColumn col = table.Columns.Add();
col.ColumnName = row["ColumnName"].ToString();
col.DataType = (System.Type)row["DataType"];
}
}

object[] vals = new object[table.Columns.Count];

while (rdr.Read())
{
for (int i = 0; i < table.Columns.Count; i++)
vals = rdr.GetValue(i);
DataRow newrow = table.Rows.Add(vals);
}

return table;

}

public static int MinFieldWidth(DataTable table, string colname)
{
int minwidth = 0;
foreach (DataRow row in table.Rows)
{
string val = row[colname].ToString();
minwidth = Math.Max(minwidth, val.Length);
}
return minwidth;

}
}
}





Scanning it again, it won't work at all because of pathing differences between an MDB and DBASE files. The deal is that with DBASE, the directory is the "Database" and the individual files are the "Tables". So where I parse out file names etc, those become table names. I'm sure you can figure it out - the important part is how to use OleDB.

p.s. - I guess I should add that this code gets you a System.Data.DataTable object, containing the data from your MDB. This object can be displayed in a DataGridView where you can add and delete rows, edit fields, etc. It won't be super-easy to put back into the MDB, though. DataTables Serialize just fine on their own. Attach it to a DataSet and you can load/store it as XML with a single function call.

If you want a hotlink straight into an MDB, use the View/Data Sources command in the IDE and there's stuff for setting it up in there. I don't know much about that stuff. Maybe TheTroll has figured it out by now.

[Edited by - dalep on January 30, 2007 7:44:41 PM]

Share this post


Link to post
Share on other sites
Sign in to follow this  

  • Advertisement
×

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

We are the game development community.

Whether you are an indie, hobbyist, AAA developer, or just trying to learn, GameDev.net is the place for you to learn, share, and connect with the games industry. Learn more About Us or sign up!

Sign me up!