How can I create a Database with Tableс in memory with Sqlite C#?

Question

I'm trying to create a database in memory but when I try to run the application I get the following error:

System.Data.SQLite.SQLiteException: 'constraint failed

UNIQUE constraint failed: spisakhyd.Station'

Can some help on where I'm wrong and what needs to change?

My code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SQLite;

namespace WindowsFormsApp1
{
    public partial class Form1 : Form
    {
        private SQLiteConnection Connection;

        public Form1()
        {
            InitializeComponent();

            label3.Hide();
            label4.Hide();

            SQLiteConnection.CreateFile("hydrodb.sqlite");
            SQLiteConnection Connection = new SQLiteConnection("Data Source=hydrodb.sqlite;Version=3;");
            Connection.Open();

            string createTable = ("CREATE TABLE hyddnev (Station UNSIGNED INT(5) NOT NULL PRIMARY KEY, Dat datetime NOT NULL, Stoej int(5) DEFAULT NULL, Vkol UNSIGNED FLOAT(7,3) DEFAULT NULL, CodH varchar(1) DEFAULT NULL, CodQ varchar(1) DEFAULT NULL, Temp float(3,1) DEFAULT NULL)");
            SQLiteCommand createHydDnev = new SQLiteCommand(createTable, Connection);
            createHydDnev.ExecuteNonQuery();

            string createTable2 = ("CREATE TABLE hydmes (Station UNSIGNED INT(5) NOT NULL PRIMARY KEY, Dat datetime NOT NULL, StoejMin smallint(5) DEFAULT NULL, VkolMin UNSIGNED FLOAT(7,3) DEFAULT NULL, StoejSre smallint(5) DEFAULT NULL, VkolSre UNSIGNED FLOAT(7,3) DEFAULT NULL, StoejMax smallint(5) DEFAULT NULL, VkolMax UNSIGNED FLOAT(7,3) DEFAULT NULL)");
            SQLiteCommand createHydMes = new SQLiteCommand(createTable2, Connection);
            createHydMes.ExecuteNonQuery();

            string createTable3 = ("CREATE TABLE spisakhyd (Station UNSIGNED INT(5) NOT NULL PRIMARY KEY, NasMesto varchar(50) DEFAULT NULL, ImeReka varchar(50) DEFAULT NULL, Lati varchar(7) DEFAULT NULL, Longi varchar(7) DEFAULT NULL, Alti float(6,2) DEFAULT NULL, Star varchar(10) DEFAULT NULL)");
            SQLiteCommand createSpisakHyd = new SQLiteCommand(createTable3, Connection);
            createSpisakHyd.ExecuteNonQuery();

            this.Connection = Connection;
        }

        string pathFolder;
        string pathFolder2;

        string resultStation;
        string resultStation2;

        List<string> resultYears = new List<string>();
        List<string> resultYears2 = new List<string>();

        private void button1_Click(object sender, EventArgs e)
        {
            using (OpenFileDialog dialog = new OpenFileDialog())
            {
                if (dialog.ShowDialog(this) == DialogResult.OK)
                {
                    string sFileName = dialog.FileName;
                    pathFolder = sFileName;

                    label3.Text = pathFolder;
                    label3.Show();                 

                    string[] lines = System.IO.File.ReadAllLines(dialog.FileName);

                    int i = 0;

                    foreach (var line in lines)
                    {

                        var splittedValues = line.Split(',');

                        var firstWord = splittedValues[0];
                        var firstYear = splittedValues[1];

                        if (!resultYears.Contains(firstYear))
                        {
                            resultYears.Add(firstYear);
                        }


                        if (i == 0)
                        {
                            resultStation = firstWord;
                        }
                        else
                        {
                            if (resultStation != firstWord)
                            {
                                MessageBox.Show("Файла с дневни данни трябва да съдържа само една станция!");
                                return;
                            }
                        }

                        i++;

                        string insertStation = ("insert into spisakhyd(Station) values(" + resultStation + ")");

                        SQLiteCommand insertSpisakHyd = new SQLiteCommand(insertStation, Connection);
                        insertSpisakHyd.ExecuteNonQuery();

                    }
                    resultYears.Sort();
                }
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            using (OpenFileDialog dialog = new OpenFileDialog())
            {
                if (dialog.ShowDialog(this) == DialogResult.OK)
                {
                    string sFileName = dialog.FileName;
                    pathFolder2 = sFileName;

                    label4.Text = pathFolder2;
                    label4.Show();

                    string[] lines = System.IO.File.ReadAllLines(dialog.FileName);

                    int i = 0;

                    foreach (var line in lines)
                    {
                        var splittedValues = line.Split(',');

                        var firstWord = splittedValues[0];
                        var firstYear2 = splittedValues[1];

                        if (!resultYears2.Contains(firstYear2))
                        {
                            resultYears2.Add(firstYear2);
                        }

                        if (i == 0)
                        {
                            resultStation2 = firstWord;
                        }
                        else
                        {
                            if (resultStation2 != firstWord)
                            {
                                MessageBox.Show("Файла с месечни данни трябва съдържа само една станция!");
                                return;
                            }
                        }

                        i++;
                    }

                    resultYears2.Sort();
                }
            }
        }

        public void label3_Click(object sender, EventArgs e)
        {

        }

        public void label4_Click(object sender, EventArgs e)
        {

        }

        private void button3_Click(object sender, EventArgs e)
        {
            if (resultStation != resultStation2)
            {
                MessageBox.Show("Номера на станцията в единия файл не отговаря на номера на станцията в другият файл!" + Environment.NewLine + Environment.NewLine +
                    "ЗАБЕЛЕЖКА!" + Environment.NewLine + Environment.NewLine + "В двата файла, номера на станцията трябва да бъде един и същ!");
            }

            comboBox1.Items.Add(resultStation);

            if (string.Join(", ", resultYears) == string.Join(", ", resultYears2))
            //if (resultYears.Equals(resultYears2))
            {
                for (int i = 0; i < this.resultYears.Count; i++)
                {
                    comboBox2.Items.Add(resultYears[i]);
                }
            }
            else
            {
                MessageBox.Show("Годините от двата файла не съвпадат.");
            }

        }
    }
}

Debugger stoped on Line 102.. I do not understand why when I create the database and close relationship and just want to be in use next class debugger stops on line 102?

CL. · Accepted Answer · 2017-04-12 08:08:10Z

3

As shown in the documentation, a type-name can have numbers in parentheses only after the last word. That is, you have to put the unsigned before the mediumint.

(Please note that SQLite uses dynamic typing, so unsigned mediumint(5) is the same as somewhat signed notoriously big int(-5), or simply int.)

answered Apr 12, 2017 at 8:08

CL.

182k18 gold badges241 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Evk Over a year ago

In addition to that, you cannot define composite primary key the way it is done in question, or it will complain that table "has more than one primary key".

CL. Over a year ago

Assuming he wants a composite PK. Oh, and there is an extra comma.

Collectives™ on Stack Overflow

How can I create a Database with Tableс in memory with Sqlite C#?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related