BPt1KZtm

1using System;
2using System.Collections.Generic;
3using System.Linq;
4using System.Text;
5using System.Threading.Tasks;
6using System.Data.SQLite;
7using System.IO;
8using System.Data;
9
10namespace SQLite
11{
12     static class Info
13    {
14        public static string dbFileName = "SQLiteEx.db";
15        public static string connectionString = @"Data Source=C:\Users\augus\Desktop\кроссплатформенные базы данных\SQLite\SQLite\bin\" +
16            Info.dbFileName + "; " +
17            "Version=3;" +
18            "Foreign Keys = True;";
19        public static SQLiteConnection mDbConn = new SQLiteConnection(connectionString);
20        public static SQLiteCommand mSqlCmd=new SQLiteCommand();
21    }
22    class Program
23    {
24        static void Create()
25        {
26            if (!File.Exists(Info.dbFileName))
27                SQLiteConnection.CreateFile(Info.dbFileName);
28            try
29            {
30                //Info.mDbConn = new SQLiteConnection("Data Source=" + Info.dbFileName + ";Version=3;Foreign Keys=True;");
31                Info.mDbConn = new SQLiteConnection(Info.connectionString);
32                Info.mDbConn.Open();
33                Info.mSqlCmd.Connection = Info.mDbConn;
34
35                Info.mSqlCmd.CommandText = "CREATE TABLE IF NOT EXISTS Language " +
36                    "(Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE," +
37                    " LangTitle TEXT NOT NULL UNIQUE CHECK(LangTitle<> '')";
38                Info.mSqlCmd.ExecuteNonQuery();
39
40                Info.mSqlCmd.CommandText = "CREATE TABLE Author (" +
41                    "Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE," +
42                    "Name TEXT NOT NULL UNIQUE," +
43                    "Nationality TEXT CHECK(Nationality<> '')," +
44                    "BirthDay INTEGER CHECK(BirthDay > 0 AND BirthDay < 32)," +
45                    "BirthMonth INTEGER CHECK(BirthMonth > 0 AND BirthMonth < 13)," +
46                    "BirthYear INTEGER CHECK(BirthYear > 0 AND BirthYear < 5000))";
47                Info.mSqlCmd.ExecuteNonQuery();
48
49                Info.mSqlCmd.CommandText = "CREATE TABLE Quote (" +
50                    "Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE," +
51                    "Content TEXT NOT NULL UNIQUE CHECK(Content <> '')," +
52                    "AuthorId INTEGER NOT NULL DEFAULT '1' REFERENCES Author(Id) _" +
53                    "ON DELETE RESTRICT ON UPDATE RESTRICT," +
54                    "LanguageId INTEGER NOT NULL DEFAULT '1' _" +
55                    "REFERENCES Language(Id) ON DELETE RESTRICT ON UPDATE RESTRICT)";
56                Info.mSqlCmd.ExecuteNonQuery();
57
58                Console.WriteLine("Connected");
59            }
60            catch (SQLiteException ex)
61            {
62                Console.WriteLine("Disconnected Error:" + ex.Message);
63            }
64            Info.mDbConn.Close();
65            Console.ReadKey();
66        }
67
68        static void Connect()
69        {
70            if (!File.Exists(Info.dbFileName))
71                Console.WriteLine("Please, create DB and blank table (Push \"Create\" button)");
72
73            try
74            {
75                //Info.mDbConn = new SQLiteConnection("Data Source=" + Info.dbFileName + ";Version=3;Foreign Keys=True;");
76                Info.mDbConn.Open();
77                Info.mSqlCmd.Connection = Info.mDbConn;
78
79                Console.WriteLine("Connected");
80            }
81            catch (SQLiteException ex)
82            {
83                Console.WriteLine("Disconnected Error:" + ex.Message);
84            }
85            Console.ReadKey();
86        }
87
88        static void Exit()
89        {
90            Console.WriteLine("приложение заканчивает работу");
91            Environment.Exit(0);
92        }
93
94        static void ReadTable(string value)
95        {
96            DataTable dTable = new DataTable();
97            String sqlQuery;
98            if (Info.mDbConn.State != ConnectionState.Open)
99            {
100                Console.WriteLine("Open connection with database");
101                return;
102            }
103            try
104            {
105                sqlQuery = "SELECT * FROM "+value;
106                SQLiteDataAdapter adapter = new SQLiteDataAdapter(sqlQuery, Info.mDbConn);
107                adapter.Fill(dTable);
108
109                if (dTable.Rows.Count > 0)
110                {
111                    Console.Clear();
112
113                    for (int i = 0; i < dTable.Rows.Count; i++)
114                        for (int j = 0; j < dTable.Rows[i].ItemArray.Length; j++)
115                            Console.WriteLine(dTable.Rows[i].ItemArray[j]);
116                    Console.WriteLine('\n');
117                }
118                else
119                    Console.WriteLine("Language is empty");
120            }
121            catch (SQLiteException ex)
122            {
123                Console.WriteLine("Error: " + ex.Message);
124            }
125            //Info.mDbConn.Close();
126        }
127
128        static void Read()
129
130        {
131            ReadTable("Language");
132            //ReadTable("Author");
133            //ReadTable("Quote");
134            Console.ReadKey();
135
136        }
137
138        static int UpdateLang(int id, string newLangTitle)
139        {
140            int result = -1;
141            if (Info.mDbConn.State != ConnectionState.Open)
142            {
143                Console.WriteLine("Open connection with database");
144                return result;
145            }
146            Info.mSqlCmd.CommandText = "UPDATE Language "
147                        + "SET LangTitle = @Lang "
148                        + "WHERE Id = @Id";
149            Info.mSqlCmd.Prepare();
150            Info.mSqlCmd.Parameters.AddWithValue("@Lang", newLangTitle);
151            Info.mSqlCmd.Parameters.AddWithValue("@Id", id);
152            try
153            {
154                result = Info.mSqlCmd.ExecuteNonQuery();
155            }
156            catch (SQLiteException ex)
157            {
158                Console.WriteLine("Error: " + ex.Message);
159            }
160            //Info.mDbConn.Close();
161            return result;
162        }
163        static void Update()
164        {
165            Console.WriteLine("пример обновления данных таблицы Language");
166            Console.Write("ввод id：");
167            int id = Convert.ToInt32(Console.ReadLine());
168            Console.Write("ввод LangTitle：");
169            string LangTitle = Console.ReadLine();
170            UpdateLang(id, LangTitle);
171            Console.ReadKey();
172        }
173        static int DeleteLang(int id)
174        {
175            int result = -1;
176            if (Info.mDbConn.State != ConnectionState.Open)
177            {
178                Console.WriteLine("Open connection with database");
179                return result;
180            }
181            Info.mSqlCmd.CommandText = "Delete from Language where Id=@I";
182            Info.mSqlCmd.Prepare();
183            Info.mSqlCmd.Parameters.AddWithValue("@I", id);
184            try
185            {
186                result = Info.mSqlCmd.ExecuteNonQuery();
187            }
188            catch (SQLiteException ex)
189            {
190                Console.WriteLine("Error: " + ex.Message);
191            }
192            //Info.mDbConn.Close();
193            return result;
194        }
195        static void Delete()
196        {
197            Console.WriteLine("пример удаления данных таблицы Language");
198            Console.Write("ввод id：");
199            int id = Convert.ToInt32(Console.ReadLine());
200            DeleteLang(id);
201            Console.ReadKey();
202        }
203        static int AddLang(string LangTitle)
204        {
205            int result = -1;
206            if (Info.mDbConn.State != ConnectionState.Open)
207            {
208                Console.WriteLine("Open connection with database");
209                return result;
210            }
211            Info.mSqlCmd.CommandText = "Insert into Language(LangTitle) VALUES (@lang)";
212            Info.mSqlCmd.Prepare();
213            Info.mSqlCmd.Parameters.AddWithValue("@Lang", LangTitle);
214            try
215            {
216                result = Info.mSqlCmd.ExecuteNonQuery();
217            }
218            catch (SQLiteException ex)
219            {
220                Console.WriteLine("Error: " + ex.Message);
221            }
222            //Info.mDbConn.Close();
223            return result;
224        }
225        static void Add()
226        {
227            Console.WriteLine("пример добавления данных таблицы Language");
228            Console.Write("ввод LangTitle：");
229            string LangTitle = Console.ReadLine();
230            AddLang(LangTitle);
231            Console.ReadKey();
232        }
233
234        static void Main(string[] args)
235        {
236            while (true)
237            {
238                int counter;
239                string[] items = { "создание базы данных", "подключение", "чтение", "обновление", "удаление", "добавление", "Выход" };
240
241                PrintMenu(items, out counter);
242                Console.Clear();
243                select(counter);
244            }
245        }
246        static int PrintMenu(string[] menuitems, out int counter)
247        {
248
249
250            counter = 0;
251            ConsoleKeyInfo key;
252            do
253            {
254                Console.Clear();
255                for (int i = 0; i < menuitems.Length; i++)
256                {
257                    if (counter == i)
258                    {
259                        Console.BackgroundColor = ConsoleColor.White;
260                        Console.ForegroundColor = ConsoleColor.Black;
261                        Console.WriteLine(menuitems[i]);
262                        Console.BackgroundColor = ConsoleColor.Black;
263                        Console.ForegroundColor = ConsoleColor.White;
264                    }
265                    else
266                        Console.WriteLine(menuitems[i]);
267
268                }
269                key = Console.ReadKey();
270                if (key.Key == ConsoleKey.UpArrow)
271                {
272                    counter--;
273                    if (counter == -1) counter = menuitems.Length - 1;
274                }
275                if (key.Key == ConsoleKey.DownArrow)
276                {
277                    counter++;
278                    if (counter == menuitems.Length) counter = 0;
279                }
280            }
281            while (key.Key != ConsoleKey.Enter);
282            return counter;
283        }
284
285        static void select(int sel)
286        {
287            switch (sel)
288            {
289                case 0:Create();
290                    break;
291                case 1:Connect();
292                    break;
293                case 2:Read();
294                    break;
295                case 3:Update();
296                    break;
297                case 4:Delete();
298                    break;
299                case 5:Add();
300                    break;
301                default:
302                    Exit();
303                    break;
304            }
305        }
306    }
307}