728x90
반응형
WPF & C# - SQLite Sample ( 샘플 / SQLlite) |
관련자료
SQLlite Sample
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697 string PassWord(){string Password = "Password = password2";return Password;}string sqliteFile = Environment.CurrentDirectory + "\\" + @"mySQLite5.db"; // SQLlite 저장 파일명private void CreateDB_Click(object sender, RoutedEventArgs e){// 파일이 없을 시 생성SQLiteConnection.CreateFile(sqliteFile);string connString = @"Data Source = " + sqliteFile + ";";// DB 파일에 테이블을 생성하는 코드using (SQLiteConnection conn = new SQLiteConnection(connString)){conn.SetPassword("password2");conn.Open();// 테이블 및 필드생성string sql = "create table members (name varchar(20), age int)";SQLiteCommand command = new SQLiteCommand(sql, conn);int result = command.ExecuteNonQuery();// 인덱스 설정sql = "create index idx01 on members(name)";command = new SQLiteCommand(sql, conn);result = command.ExecuteNonQuery();command.Dispose();conn.Close();}// 생성한 테이블에 Row를 추가하는 코드using (SQLiteConnection conn = new SQLiteConnection(connString + PassWord())){conn.Open();// 필드에 데이터 추가String sql = "insert into members (name, age) values ('홍길동', 32)";SQLiteCommand command = new SQLiteCommand(sql, conn);int result = command.ExecuteNonQuery();// 필드에 데이터 추가String sql2 = "insert into members (name, age) values ('레오나르도다빈치', 30)";SQLiteCommand command2 = new SQLiteCommand(sql2, conn);int result2 = command2.ExecuteNonQuery();command.Dispose();command2.Dispose();conn.Close();}// 테이블에 저장된 데이터에 대한 조회 코드using (SQLiteConnection conn = new SQLiteConnection(connString + PassWord())){conn.Open();String sql = "select * from members";SQLiteCommand cmd = new SQLiteCommand(sql, conn);SQLiteDataReader rdr = cmd.ExecuteReader();while (rdr.Read()){MessageBox.Show(rdr["name"] + " " + rdr["age"]);}rdr.Close();cmd.Dispose();conn.Close();}}private void btn2_Click(object sender, RoutedEventArgs e){string connString = @"Data Source = " + sqliteFile + ";";// 테이블에 저장된 데이터에 대한 조회 코드using (SQLiteConnection conn = new SQLiteConnection(connString + PassWord())){conn.Open();String sql = "select * from members";SQLiteCommand cmd = new SQLiteCommand(sql, conn);SQLiteDataReader rdr = cmd.ExecuteReader();while (rdr.Read()){MessageBox.Show(rdr["name"] + " " + rdr["age"]);}rdr.Close();cmd.Dispose();conn.Close();}}}}cs
SQLite 이미지 첨부하여 추가 시 예제
12345678910111213141516171819202122232425 // sqlite ADD 예제using (SQLiteConnection conn = new SQLiteConnection(strConn)){conn.Open();// 이미지 파일을 바이너리 형태로 형변환byte[] imageBytes = null;using (MemoryStream ms = new MemoryStream()){System.Windows.Media.Imaging.BmpBitmapEncoder bbe = new BmpBitmapEncoder();bbe.Frames.Add(BitmapFrame.Create(new Uri(img.Source.ToString(), UriKind.RelativeOrAbsolute)));bbe.Save(ms);imageBytes = ms.ToArray();}// 필드에 데이터 추가String sql = "insert into data (smtp, img) values ('홍길동', @img2)";SQLiteCommand command = new SQLiteCommand(sql, conn);SQLiteParameter param = new SQLiteParameter("@img2", System.Data.DbType.Binary);param.Value = imageBytes;command.Parameters.Add(param);int result = command.ExecuteNonQuery();}cs
SQLite Delete 예제
12345678910 // SQLite 삭제문using (SQLiteConnection conn = new SQLiteConnection(connString)){string sql = "DELETE FROM sign WHERE date ='" + str + "'";using (SQLiteCommand cmd = new SQLiteCommand(sql, conn)){conn.Open();int result = cmd.ExecuteNonQuery();}}cs
관련자료
728x90
반응형