Keskustelu: Koodit: C# Access kuvakikkailua ilman Officea

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.IO;
using System.Threading.Tasks;
using System.Windows.Forms;
using ADOX;  //COM Reference: Microsoft ADO Ext. 6.0 for DDL and Security
using System.Data.OleDb;
using System.Threading;
using System.Text.RegularExpressions;

namespace AccessPicCSharp
{

/* Form1 ohjausobjektiti:
 1 PictureBox (pictureBox1)
 3 nappia (button1 - button3) Tekstit: Vie kantaan, Tuo kannasta, Slide
 1 alasvetovalikko (comboBox1)
 1 NumericUpDown (numericUpDown1) Value 2, Increment 1, Maximum 20, Minimum 1
 1 Label (label1) Teksti: Viive
 1 OpenFileDialog (openFileDialog1)
 1 SaveFileDialog (saveFileDialog1) */

    public partial class Form1 : Form
    {
        private bool AllowExit;
        private string connstr = string.Empty;
        private string dbName = string.Empty;
        private string dbPath = string.Empty;
        private OleDbConnection conn = null;
        private OleDbCommand cmd = null;
        private string query = string.Empty;
        private OleDbDataAdapter da = null;
        private DataSet ds = null;
        private OleDbCommandBuilder cb = null;
        private FileInfo fInfo = null;

        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            dbName = "picbase.accdb";
            dbPath = Environment.GetFolderPath(Environment.SpecialFolder.Personal) + @"\PicBase";
            connstr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + dbPath + @"\" + dbName + ";";
            try
            {
                Directory.CreateDirectory(dbPath);
            }
            catch (Exception)
            {
            }

            if (Directory.Exists(dbPath) &! File.Exists(dbPath + @"\" + dbName))
            {
                ADOX.Catalog cat = new ADOX.Catalog();
                cat.Create(connstr);
                cat = null;

                if (File.Exists(dbPath + @"\" + dbName))
                {
                    conn = new OleDbConnection(connstr);
                    cmd = conn.CreateCommand();
                    cmd.CommandText = "CREATE TABLE pictures (fname "
                    + "TEXT(255) PRIMARY KEY NOT NULL, picdata OleObject NOT NULL)";
                    conn.Open();
                    cmd.ExecuteNonQuery();
                    cmd = null;
                    conn.Close();
                }
            }
            else
            {
                CboFill();
            }
        }

        public void CboFill()
        {
            comboBox1.Items.Clear();

            conn = new OleDbConnection(connstr);
            query = "SELECT fname FROM pictures";
            ds = new DataSet();
            conn.Open();
            da = new OleDbDataAdapter(query, conn);
            da.Fill(ds, "pictures");

            if (ds.Tables["pictures"].Rows.Count > 0)
            {
                foreach (DataRow row in ds.Tables["pictures"].Rows)
                    this.comboBox1.Items.Add(row[0]);
            }

            conn.Close();
            conn = null;
            ds = null;
            da = null;
        }

        private void Button1_Click(object sender, EventArgs e)
        {

            this.openFileDialog1.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyPictures);
            this.openFileDialog1.Filter = "Image Files (*.bmp *.jpg *.jpeg *.gif *.png *.tiff *.jfif)|*.bmp;*.jpg;*jpeg;*.gif;*.png;*.tiff;*.jfif";
            this.openFileDialog1.RestoreDirectory = true;
            this.openFileDialog1.FileName = "";
            if (this.openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                string fname = this.openFileDialog1.FileName;
                fInfo = new FileInfo(fname);
                long numBytes = fInfo.Length;
                FileStream fs = new FileStream(fname, FileMode.Open, FileAccess.Read);
                BinaryReader br = new BinaryReader(fs);
                Byte[] bytes = br.ReadBytes((int)numBytes);
                br.Close();
                fs.Close();
                conn = new OleDbConnection(connstr);
                query = "SELECT * FROM pictures WHERE fname = '" + fInfo.Name + "'";
                ds = new DataSet();
                conn.Open();
                da = new OleDbDataAdapter(query, conn);
                cb = new OleDbCommandBuilder(da);
                da.Fill(ds, "pictures");
                if (ds.Tables["pictures"].Rows.Count > 0)
                {
                    int msgresult = (int)MessageBox.Show("Tietokannassa on jo saman niminen kuva" + Environment.NewLine + "Korvataanko kuva?", "Tietokantailmoitus", MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
                    if (msgresult != 1)
                    {
                        goto ExitProc;
                    }
                    else
                    {
                        ds.Tables["pictures"].Rows[0].Delete();
                        da.Update(ds, "pictures");
                    }
                }

                query = "SELECT * From pictures";
                cb.RefreshSchema();
                ds.Tables.Clear();
                da.TableMappings.Clear();
                da.SelectCommand = new OleDbCommand(query, conn);
                cb.DataAdapter = da;
                da.Fill(ds, "pictures");
                DataRow row = ds.Tables["pictures"].NewRow();
                row["fname"] = fInfo.Name;
                row["picdata"] = bytes;
                ds.Tables["pictures"].Rows.Add(row);
                da.Update (ds, "pictures");

            ExitProc:

                fInfo = null;
                ds = null;
                cb = null;
                da = null;
                conn.Close();
                conn = null;
                query = string.Empty;
                CboFill();
            }
        }

        private void Button2_Click(object sender, EventArgs e)
        {

            if (this.comboBox1.Items.Count == 0)
            {
                MessageBox.Show("Tietokanta ei sisällä kuvadataa!");
                return;
            }

            conn = new OleDbConnection(connstr);
            query = "SELECT * FROM pictures";
            ds = new DataSet();
            conn.Open();
            da = new OleDbDataAdapter(query, conn);
            da.Fill(ds, "pictures");
            conn.Close();
            da = null;
            if (ds.Tables["pictures"].Rows.Count > 0)
            {
                foreach (DataRow row in ds.Tables["pictures"].Rows)
                {
                    string fname = row["fname"].ToString();
                    fInfo = new FileInfo(fname);

                    switch (fInfo.Extension.ToLower())
                    {
                        case ".bmp":
                            {
                                this.saveFileDialog1.Filter = "Bitmap (*.bmp)|*.bmp";
                                break;
                            }

                        case ".gif":
                            {
                                this.saveFileDialog1.Filter = "Compuserve (*.gif)|*.gif";
                                break;
                            }

                        case ".png":
                            {
                                this.saveFileDialog1.Filter = "Portaple (*.png)|*.png";
                                break;
                            }

                        case ".jpg":
                            {
                                this.saveFileDialog1.Filter = "JPG (*.jpg)|*.jpg";
                                break;
                            }

                        case ".jpeg":
                            {
                                this.saveFileDialog1.Filter = "JPEG (*.jpeg)|*.jpeg";
                                break;
                            }
                        case ".tiff":
                            {
                                this.saveFileDialog1.Filter = "Tagged Image File (*.tiff)|*.tiff";
                                break;
                            }
                        case ".jfif":
                            {
                                this.saveFileDialog1.Filter = "JPEG Interchange Format (*.jfif)|*.jfif";
                                break;
                            }
                    }

                    fInfo = null;

                    this.saveFileDialog1.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
                    this.saveFileDialog1.FileName = row[0].ToString();
                    if (this.saveFileDialog1.ShowDialog() == DialogResult.OK)
                    {
                        if (File.Exists(this.saveFileDialog1.FileName))
                        {
                            File.Delete(this.saveFileDialog1.FileName);
                        }

                        this.pictureBox1.Image = ArrayToImage((byte[])row["picdata"]);
                        File.WriteAllBytes(this.saveFileDialog1.FileName, (byte[])row["picdata"]);
                    }
                    else
                    {
                        DialogResult dialogResult = MessageBox.Show("Keskeytetäänkö haku", this.Name, MessageBoxButtons.YesNo);
                        if (dialogResult == DialogResult.Yes)
                        {
                            return;
                        }
                    }
                }

                ds = null;
            }
        }

        private void Button3_Click(object sender, EventArgs e)
        {
            if (this.comboBox1.Items.Count == 0) {
                MessageBox.Show("Tietokanta ei sisällä kuvadataa!");
                return;
            }

            int cnt = this.comboBox1.SelectedIndex;
            if (cnt == this.comboBox1.Items.Count - 1)
            {
                cnt = 0;
                this.comboBox1.SelectedIndex = 0;
                AllowExit = false;
            }

            if (this.comboBox1.Items.Count > 0 & this.button3.Text == "Slide")
            {
                this.button3.Text = "Stop";
                for (int i = cnt, loopTo = this.comboBox1.Items.Count - 1; i <= loopTo; i++)
                {
                    if (AllowExit)
                    {
                        AllowExit = !AllowExit;
                        break;
                    }

                    this.comboBox1.SelectedIndex = i;
                    Viive();
                    if (this.comboBox1.SelectedIndex == this.comboBox1.Items.Count - 1) {
                        this.button3.Text = "Slide";
                    }
                }
            }
        }

        private void Button3_MouseUp(object sender, MouseEventArgs e)
        {
            if (this.button3.Text == "Stop")
            {
                this.button3.Text = "Slide";
                AllowExit = true;
            }
        }

        private void ComboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            conn = new OleDbConnection(connstr);
            query = "SELECT * FROM pictures Where fname = '" + this.comboBox1.SelectedItem + "'";
            ds = new DataSet();
            conn.Open();
            da = new OleDbDataAdapter(query, conn);
            cb = new OleDbCommandBuilder(da);
            cb.RefreshSchema();
            ds.Tables.Clear();
            da.Fill(ds, "pictures");
            conn.Close();
            conn = null;
            cb = null;
            da = null;
            this.pictureBox1.Image = ArrayToImage((byte[])ds.Tables[0].Rows[0][1]);
            ds = null;
        }

        private void ComboBox1_MouseDown(object sender, MouseEventArgs e)
        {
            AllowExit = false;
            this.button3.Text = "Slide";

        }
        public Image ArrayToImage(byte[] byteArrayIn)
        {
            using (MemoryStream mStream = new MemoryStream(byteArrayIn))
            {
                return Image.FromStream(mStream);
            }
        }

        public void Viive()
        {
            Application.DoEvents();
            int delay = ((int)this.numericUpDown1.Value * 1000);
            Thread.Sleep(delay);
        }

    }
}