Add, delete, modify, and query code for the SQLite Database

// I collected and sorted out the online code. I couldn't find an insert statement after searching for half a day. It's so depressing.

// It seems that the speed is good. Small files. Many small applications are using this database.

// SQLite usage. Directly copy the DLL file system. Data. SQLite. DLL to the application DEBUG directory. Add a reference to the project and find the file.

// Add reference

Using system;
Using system. Collections. Generic;
Using system. componentmodel;
Using system. Data;
Using system. drawing;
Using system. text;
Using system. Windows. forms;

Using system. IO;
Using system. net;
Using system. Data. SQLite;

 

Private Static string favoritedbpath = system. environment. currentdirectory + "// favorite. DB ";

Private bool createsqlitedb ()
{
Try
{
System. Data. SQLite. sqliteconnection. createfile (favoritedbpath );
System. Data. SQLite. sqliteconnection conn = new system. Data. SQLite. sqliteconnection ();
System. Data. SQLite. sqliteconnectionstringbuilder connstr = new system. Data. SQLite. sqliteconnectionstringbuilder ();
Connstr. datasource = favoritedbpath;
Connstr. Password = "admin"; // set the password. SQLite ADO. Net implements database password protection.
Conn. connectionstring = connstr. tostring ();
Conn. open ();
System. Data. SQLite. sqlitecommand cmd = new system. Data. SQLite. sqlitecommand ();
String SQL = "CREATE TABLE admin (username varchar (20), userpass varchar (20 ))";
Cmd. commandtext = SQL;
Cmd. Connection = conn;
Cmd. executenonquery ();
Conn. Dispose ();
Return true;
}
Catch (exception ex)
{
Console. writeline (ex. Message );
Return false;
}
}

Public bool createlinkdatatable ()
{
Try
{
System. Data. SQLite. sqliteconnection conn = new system. Data. SQLite. sqliteconnection ();
System. Data. SQLite. sqliteconnectionstringbuilder connstr = new system. Data. SQLite. sqliteconnectionstringbuilder ();
Connstr. datasource = favoritedbpath;
Connstr. Password = "admin"; // set the password. SQLite ADO. Net implements database password protection.
Conn. connectionstring = connstr. tostring ();
Conn. open ();
System. Data. SQLite. sqlitecommand cmd = new system. Data. SQLite. sqlitecommand ();
String SQL = "CREATE TABLE [favoritelist] ([ID] integer not null primary key autoincrement, [linktext] nvarchar (256) null, [linkurl] nvarchar (1000 ), [addtime] timestamp default current_timestamp null )";
Cmd. commandtext = SQL;
Cmd. Connection = conn;
Cmd. executenonquery ();
Conn. Dispose ();
Return true;
}
Catch (exception)
{
Return false;
}
}
Public bool insertlinkdatatable (string strlinktext, string strlinkurl, datetime dtfiletime)
{
Try
{
System. Data. SQLite. sqliteconnection conn = new system. Data. SQLite. sqliteconnection ();
System. Data. SQLite. sqliteconnectionstringbuilder connstr = new system. Data. SQLite. sqliteconnectionstringbuilder ();
Connstr. datasource = favoritedbpath;
Connstr. Password = "admin"; // set the password. SQLite ADO. Net implements database password protection.
Conn. connectionstring = connstr. tostring ();
Conn. open ();
System. Data. SQLite. sqlitecommand cmd = new system. Data. SQLite. sqlitecommand ();
String strinsertsql = "insert into [favoritelist] (linktext, linkurl, addtime) values ('" + strlinktext + "', '" + strlinkurl + "', '"+ dtfiletime + "');";
Console. writeline (strinsertsql );
Cmd. commandtext = strinsertsql;
Cmd. Connection = conn;
Cmd. executenonquery ();
Conn. Dispose ();
Return true;
}
Catch (exception ex)
{
Console. writeline (ex. Message );
Return false;
}
}
Public bool getlinkdatatablerecordlist ()
{
Try
{
System. Data. SQLite. sqliteconnection conn = new system. Data. SQLite. sqliteconnection ();
System. Data. SQLite. sqliteconnectionstringbuilder connstr = new system. Data. SQLite. sqliteconnectionstringbuilder ();
Connstr. datasource = favoritedbpath;
Connstr. Password = "admin"; // set the password. SQLite ADO. Net implements database password protection.
Conn. connectionstring = connstr. tostring ();
Conn. open ();
System. Data. SQLite. sqlitecommand cmd = new system. Data. SQLite. sqlitecommand ();
Cmd. Connection = conn;
Cmd. commandtext = "select * from [favoritelist]";
Sqlitedatareader DR = cmd. executereader ();
While (dr. Read ())
{
Console. writeline ("Number" + system. convert. tostring (Dr ["ID"]) + "text" + (string) Dr ["linktext"] + "Address" + (string) Dr ["linkurl"]);
}
Conn. Dispose ();
Return true;
}
Catch (exception)
{
Return false;
}
}

Private void create database file toolstripmenuitem_click (Object sender, eventargs E)
{
Try
{
If (system. Io. file. exists (favoritedbpath ))
{
System. Io. file. Delete (favoritedbpath );
}
If (createsqlitedb ())
{
MessageBox. Show ("database created successfully ");
}
Else
{
MessageBox. Show ("database creation failed ");
}
}
Catch (exception ex)
{
MessageBox. Show ("An error occurred while creating the database file" + ex. Message );
}
}

Private void create favorites data table toolstripmenuitem_click (Object sender, eventargs E)
{
Try
{
If (createlinkdatatable ())
{
MessageBox. Show ("the favorite data table is successfully created ");
}
Else
{
MessageBox. Show ("failed to create a favorite data table ");
}
}
Catch (exception ex)
{
MessageBox. Show ("An error occurred while creating the favorites data table" + ex. Message );
}
}

Private void query toolstripmenuitem_click (Object sender, eventargs E)
{
Try
{
If (getlinkdatatablerecordlist ())
{
MessageBox. Show ("the favorite data table is queried successfully ");
}
Else
{
MessageBox. Show ("failed to query the favorites data table ");
}
}
Catch (exception ex)
{
MessageBox. Show ("An error occurred while querying the favorites data table" + ex. Message );
}
}

Private void Insert new data record toolstripmenuitem_click (Object sender, eventargs E)
{
Try
{
If (insertlinkdatatable ("gjkhkjhk", "hlkjljl", datetime. Now ))
{
MessageBox. Show ("the new data record is successfully inserted ");
}
Else
{
MessageBox. Show ("failed to insert new data records ");
}
}
Catch (exception ex)
{
MessageBox. Show ("An error occurred while inserting new data records" + ex. Message );
}
}