pt3NhSC0

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