sqlite常用操作

?using System;
using System.Collections.Generic;
using https://www.360docs.net/doc/082680677.html,ponentModel;
using System.Data;
using System.Data.SQLite;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.IO;


namespace exam_x86
{
public partial class Form1 : Form
{
string datasource = "exam.sqlite";

public Form1()
{
InitializeComponent();
}

private void butDatabaseTest_Click(object sender, EventArgs e)
{
//创建数据库
SQLiteConnection.CreateFile(datasource);

//打开数据库
SQLiteConnection conn = new SQLiteConnection();
SQLiteConnectionStringBuilder connstr = new SQLiteConnectionStringBuilder();
connstr.DataSource = datasource;
//connstr.Password = "admin";//设置密码
conn.ConnectionString = connstr.ToString();
conn.Open();

//创建表
SQLiteCommand cmd = new SQLiteCommand();
https://www.360docs.net/doc/082680677.html,mandText = "CREATE TABLE test(ID int , USERNAME varchar,PASSWORD varchar)";
cmd.Connection = conn;
cmd.ExecuteNonQuery();

//插入数据
https://www.360docs.net/doc/082680677.html,mandText = "INSERT INTO test VALUES(0,'yehao','123456')";
cmd.ExecuteNonQuery();

//取出数据
https://www.360docs.net/doc/082680677.html,mandText = "SELECT * FROM test";
SQLiteDataReader reader = cmd.ExecuteReader();
StringBuilder sb = new StringBuilder();
while (reader.Read())
sb.Append("ID:").Append(reader.GetInt32(0).ToString()).Append("username:").Append(reader.GetString(1)).Append(" ").Append("password:").Append(reader.GetString(2)).Append("\r\n");

conn.Close();

FileInfo logfile = new FileInfo(DateTime.Now.ToString("yyyy-MM-dd HHmmss")+".txt");//(DateTime.Now.ToString("yyyy-MM-dd HHmmss") + ".txt")
using (FileStream fs = logfile.Create())//FileStream fs = logfile.OpenWrite();
{
StreamWriter w = new StreamWriter(fs);
//w.BaseStream.Seek(0, SeekOrigin.End);
w.Write(sb);
w.Flush();
w.Close();
}

MessageBox.Show("保存成功");
}

private void button1_Click(object sender, EventArgs e)
{

}

private void butOpenDatabase_Click(object sender, EventArgs e)
{

}

private void button6_Click(object sender, EventArgs e)
{

}

private void button2_Click(object sender, EventArgs e)
{

}

private void butInsertData_Click(object sender, EventArgs e)
{
Random r = new Random( );

//打开数据库
SQLiteConnection conn = n

ew SQLiteConnection();
SQLiteConnectionStringBuilder connstr = new SQLiteConnectionStringBuilder();
connstr.DataSource = datasource;
conn.ConnectionString = connstr.ToString();
conn.Open();

SQLiteCommand cmd = new SQLiteCommand();
cmd.Connection = conn;

//插入数据
string sCMD = string.Format("INSERT INTO test VALUES({0},'user{1}','key{2}')", r.Next(0, 65536), r.Next(0, 65536), r.Next(0, 65536));
https://www.360docs.net/doc/082680677.html,mandText = sCMD;
cmd.ExecuteNonQuery();
textBoxDisplaySta.Text += "\r\n" + DateTime.Now.ToString() + " 插入成功" +"\r\n" + sCMD + "\r\n";

conn.Close();

textBoxDisplaySta.SelectionStart = textBoxDisplaySta.Text.Length;
textBoxDisplaySta.ScrollToCaret();
//MessageBox.Show("插入成功");
}

private void butDeleteData_Click(object sender, EventArgs e)
{

}

private void butCreatTable_Click(object sender, EventArgs e)
{
//打开数据库
SQLiteConnection conn = new SQLiteConnection();
SQLiteConnectionStringBuilder connstr = new SQLiteConnectionStringBuilder();
connstr.DataSource = datasource;
conn.ConnectionString = connstr.ToString();
conn.Open();

SQLiteCommand cmd = new SQLiteCommand();
https://www.360docs.net/doc/082680677.html,mandText = "CREATE TABLE COMPANY(ID INT , NAME VARCHAR , AGE INT , ADDRESS VARCHAR , SALARY REAL);";
cmd.Connection = conn;
cmd.ExecuteNonQuery();

conn.Close();

//labDisplaySta.Text = "创建成功";
//MessageBox.Show("创建成功");
}

private void butInsertMan_Click(object sender, EventArgs e)
{
Random r = new Random( );

//打开数据库
SQLiteConnection conn = new SQLiteConnection();
SQLiteConnectionStringBuilder connstr = new SQLiteConnectionStringBuilder();
connstr.DataSource = datasource;
conn.ConnectionString = connstr.ToString();
conn.Open();

SQLiteCommand cmd = new SQLiteCommand();
cmd.Connection = conn;
//https://www.360docs.net/doc/082680677.html,mandText = "INSERT INTO COMPANY VALUES (1, 'Paul', 32, 'California', 20000.00 );";
string sCMD = string.Format("INSERT INTO COMPANY VALUES({0},'Paul{1}',{2},'China',{3})", r.Next(0, 65536), r.Next(0, 65536), r.Next(18, 40), r.Next(5000, 50000));
https://www.360docs.net/doc/082680677.html,mandText = sCMD;
cmd.ExecuteNonQuery();
textBoxDisplaySta.Text += "\r\n" + DateTime.Now.ToString() + " 插入成功" + "\r\n"+sCMD+ "\r\n";

conn.Close();

textBoxDisplaySta.SelectionStart = textBoxDisplaySta.Text.Length;
textBoxDisplaySta.ScrollToCaret()

;
}

private void butRead_Click(object sender, EventArgs e)
{
//打开数据库
SQLiteConnection conn = new SQLiteConnection();
SQLiteConnectionStringBuilder connstr = new SQLiteConnectionStringBuilder();
connstr.DataSource = datasource;
conn.ConnectionString = connstr.ToString();
conn.Open();

SQLiteCommand cmd = new SQLiteCommand();
cmd.Connection = conn;

https://www.360docs.net/doc/082680677.html,mandText = "SELECT * FROM COMPANY";
SQLiteDataReader reader = cmd.ExecuteReader();
String sb = "";
while (reader.Read())
{
sb += "ID:" + reader.GetInt32(0).ToString() + "\r\n" + "NAME:" + reader.GetString(1) + "\r\n"
+ "AGE:" + reader.GetInt32(2).ToString() + "\r\n" + "ADDRESS:" + reader.GetString(3) + "\r\n"
+ "SALARY:" + reader.GetFloat(4).ToString() + "\r\n\r\n";
//sb.Append("username:").Append(reader.GetString(0)).Append(" ").Append("password:").Append(reader.GetString(1)).Append("\r\n");
}
conn.Close();

FileInfo logfile = new FileInfo(DateTime.Now.ToString("yyyy-MM-dd HHmmss") +"测试文件"+ ".txt");//(DateTime.Now.ToString("yyyy-MM-dd HHmmss") + ".txt")
using (FileStream fs = logfile.Create())//FileStream fs = logfile.OpenWrite();
{
StreamWriter w = new StreamWriter(fs);
//w.BaseStream.Seek(0, SeekOrigin.End);
w.Write(sb);
w.Flush();
w.Close();
}

conn.Close();

textBoxDisplaySta.Text += "\r\n" + sb + "\r\n" + DateTime.Now.ToString() + " 读取成功" + "\r\n";
textBoxDisplaySta.SelectionStart = textBoxDisplaySta.Text.Length;
textBoxDisplaySta.ScrollToCaret();

//MessageBox.Show(sb);
}

private void butUpdata_Click(object sender, EventArgs e)
{
//打开数据库
SQLiteConnection conn = new SQLiteConnection();
SQLiteConnectionStringBuilder connstr = new SQLiteConnectionStringBuilder();
connstr.DataSource = datasource;
conn.ConnectionString = connstr.ToString();
conn.Open();

SQLiteCommand cmd = new SQLiteCommand();
cmd.Connection = conn;

https://www.360docs.net/doc/082680677.html,mandText = "UPDATE COMPANY SET SALARY = 25000.00 where ID = 11036;";
cmd.ExecuteNonQuery();

conn.Close();
}

private void Form1_Load(object sender, EventArgs e)
{
textBoxDisplaySta.Text = "";
}

private void Form1Closed(object sender, FormClosedEventArgs e)
{
FileInfo logfile = new FileInfo(DateTime.Now.ToString("yyyy-MM-dd HHmmss") + "数据库日志" + ".txt");//(DateTime.

Now.ToString("yyyy-MM-dd HHmmss") + ".txt")
using (FileStream fs = logfile.Create())//FileStream fs = logfile.OpenWrite();
{
StreamWriter w = new StreamWriter(fs);
//w.BaseStream.Seek(0, SeekOrigin.End);
w.Write(textBoxDisplaySta.Text);
w.Flush();
w.Close();
}
}

private void butDelRec_Click(object sender, EventArgs e)
{
//打开数据库
SQLiteConnection conn = new SQLiteConnection();
SQLiteConnectionStringBuilder connstr = new SQLiteConnectionStringBuilder();
connstr.DataSource = datasource;
conn.ConnectionString = connstr.ToString();
conn.Open();

SQLiteCommand cmd = new SQLiteCommand();
cmd.Connection = conn;

https://www.360docs.net/doc/082680677.html,mandText = "DELETE FROM COMPANY where ID = 2938;";
cmd.ExecuteNonQuery();

conn.Close();
}

private void button1_Click_1(object sender, EventArgs e)
{
//创建数据库
string datasource = "data.sqlite";
SQLiteConnection.CreateFile(datasource);

//打开数据库
SQLiteConnection conn = new SQLiteConnection();
SQLiteConnectionStringBuilder connstr = new SQLiteConnectionStringBuilder();
connstr.DataSource = datasource;
connstr.Password = "N95llYqDYRt5oPlx";//设置密码
conn.ConnectionString = connstr.ToString();
conn.Open();

SQLiteCommand cmd = new SQLiteCommand();
cmd.Connection = conn;

//创建表
https://www.360docs.net/doc/082680677.html,mandText = "CREATE TABLE STU(ID INT , NAME VARCHAR , PASSWORD VARCHAR);CREATE TABLE XZ(ID INT , TOPIC TEXT , ANSWER TEXT );CREATE TABLE PD(ID INT , TOPIC TEXT , ANSWER TEXT );CREATE TABLE EXAM(ID INT , EXAM_SCORE FLOAT, START_TIME DATATIME, END_TIME DATATIME,EXAM_CONTENT VARCHAR);CREATE TABLE EXAM_STU(ID INT , EXAM_ID INT , STU_ID INT);";

cmd.ExecuteNonQuery( );
conn.Close( );
}
}
}

相关文档
最新文档