utorok, 05 február 2013 14:04 Written by 2886 times
Rate this item
(3 votes)

C# - Operácie s Access databázami

V tomto článku sú ukázané základné operácie s access databázami (mdb) v C#.

Jednotlivé metódy v zdrojovom kóde predstavujú operácie ako vytvorenie mdb, vytvorenie tabuľky v databáze, vloženie údajov do tabuľky, čítanie dát z tabuľky, zmazanie údajov z tabuľky.

Použité referencie:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ADOX;
using System.Data.OleDb;
using System.Data;
using System.IO;

Vytvorenie access databázy:

        //Create Access database
        public bool createMDB(string fileName)
        {
            try
            {
                if(File.Exists(fileName + ".mdb"))
                {
                    File.Delete(fileName + ".mdb");
                }
                ADOX.Catalog catalog = new ADOX.Catalog();
                string str = "provider=Microsoft.Jet.OleDb.4.0;Data Source={0};Jet OLEDB:Engine Type=5";
                catalog.Create(String.Format(str, fileName + ".mdb"));
                catalog = null;
                return true;
            }
            catch (Exception ex)
            {
                this.errorMessage = ex.Message;
                return false;
            }
        }

Vytvorenie tabuľky (mdb) s názvom "Table1" a parametrami "name1", "name2", "name3":

        //Create Table
        public bool createTable(string fileName)
        {
            try
            {
                OleDbConnection dbConn = new OleDbConnection("provider=Microsoft.Jet.OleDb.4.0;Data Source=" + fileName + ".mdb;");
                dbConn.Open();
                OleDbCommand dbCmd = new OleDbCommand();
                dbCmd.Connection = dbConn;
                dbCmd.CommandText = "CREATE TABLE Table1 ([name1] Text, [name2] Text, [name3] Text)";
                dbCmd.ExecuteNonQuery();
                dbConn.Close();
                return true;
            }
            catch (Exception ex)
            {
                this.errorMessage = ex.Message;
                return false;
            }
        }

createTable

Vloženie údajov do tabuľky

string data1 = "value1",

string data2 = "value2",

string data3 = "value3".

        //Insert Data
        public bool insertData(string fileName, string data1, string data2, string data3)
        {
            try
            {
                OleDbConnection dbConn = new OleDbConnection("provider=Microsoft.Jet.OleDb.4.0;Data Source=" + fileName + ".mdb;");
                dbConn.Open();
                OleDbCommand dbCmd = new OleDbCommand();
                dbCmd.Connection = dbConn;
                
                dbCmd.CommandText = "INSERT INTO Table1 (name1, name2, name3) values ('" + data1 + "','" + data2 + "','" + data3 + "');";
                int numRowsAffected = dbCmd.ExecuteNonQuery();
                if (numRowsAffected == 0)
                {
                    this.errorMessage = "Failed to insert data";
                    return false;
                }
                dbConn.Close();
                return true;
            }
            catch (Exception ex)
            {
                this.errorMessage = ex.Message;
                return false;
            }
        }

insertValues

Čítanie údajov z tabuľky Table1:

        //Read data
        public bool readData(string fileName, out DataTable dbRead)
        {
            dbRead = new DataTable();
            try
            {
                OleDbConnection dbConn = new OleDbConnection("provider=Microsoft.Jet.OleDb.4.0;Data Source=" + fileName + ".mdb;");
                dbConn.Open();
                string cmd = "SELECT name1, name2, name3 FROM Table1";
                OleDbDataAdapter datAdapt = new OleDbDataAdapter(cmd, dbConn);
                OleDbCommandBuilder dbCmdBuild = new OleDbCommandBuilder(datAdapt);
                datAdapt.Fill(dbRead);
                dbConn.Close();
                return true;
            }
            catch (Exception ex)
            {
                this.errorMessage = ex.Message;
                return false;
            }
        }

Údaje sú uložené v premennej dbRead, ktorá je typu DataTable.

Ak je potrebné uložit data napríklad do poľa (dvojrozmerného), môžeme to urobiť takto:

        //Array of data from data table
        public bool dataToArray(string fileName, DataTable dbRead, out string[,] dataArray)
        {
            dataArray = new string[dbRead.Rows.Count, dbRead.Columns.Count];
            try
            {
                for (int rowNum = 0; rowNum < dbRead.Rows.Count; rowNum++)
                {
                    for (int colNum = 0; colNum < dbRead.Columns.Count; colNum++)
                    {
                        dataArray[rowNum, colNum] = dbRead.Rows[rowNum][colNum].ToString();
                    }
                }
                return true;
            }
            catch (Exception ex)
            {
                this.errorMessage = ex.Message;
                return false;
            }
        }

 

Vymazanie údajov z tabuľky.

Z tabuľky sa zmaže riadok, v ktorom je splnená podmienka name1 = value1.

Ak je potrebné použiť viac ako jednu podmienku, je možné použit napríklad zápis (name1 = ´value1´ AND name2 = ´value2´).

        //Delete data
        public bool deleteData(string fileName)
        {
            try
            {
                OleDbConnection dbConn = new OleDbConnection("provider=Microsoft.Jet.OleDb.4.0;Data Source=" + fileName + ".mdb;");
                dbConn.Open();
                OleDbCommand dbCmd = new OleDbCommand();
                dbCmd.Connection = dbConn;
                dbCmd.CommandText = "DELETE FROM Table1 WHERE (name1 = 'value1')";
                dbCmd.ExecuteNonQuery();
                dbConn.Close();
                return true;
            }
            catch (Exception ex)
            {
                this.errorMessage = ex.Message;
                return false;
            }
        }

 

 

Last modified on pondelok, 19 december 2016 21:22
Ing.Peter Šuba

Zakladateľ www.projectik.eu

It's time for another revolution

(Why so serious?)

Website: www.projectik.eu