近日有需要写点C#程序,有用到Dataset数据集和SQLite数据库,由于我从来就不擅长记各种编程语言的语法,所以在查阅一堆资料后,留下以下内容备忘:
一、SQLite操作,直接贴代码,很简单:
//创建一个数据库文件
string
datasource=Application.StartupPath
+
"\\test.db";
System.Data.SQLite.SQLiteConnection.
CreateFile(datasource);
//连接数据库
System.Data.SQLite.SQLiteConnection conn =
new
System.Data.SQLite.
SQLiteConnection();
System.Data.SQLite.SQLiteConnectionStringBu
ilder connstr =
new
System.Data.SQLite.
SQLiteConnectionStringBu ilder();
connstr.DataSource = datasource;
connstr.Password =
"admin";
//设置密码,SQLite ADO.NET实现了数据库密码保护
conn.ConnectionString = connstr.
ToString();
conn.
Open();
//创建表
System.Data.SQLite.SQLiteCommand cmd =
new
System.Data.SQLite.
SQLiteCommand();
string
sql =
"CREATE TABLE test(username varchar(20),password varchar(20))";
cmd.CommandText=sql;
cmd.Connection=conn;
cmd.
ExecuteNonQuery();
//插入数据
sql =
"INSERT INTO test VALUES('dotnetthink','mypassword')";
cmd.CommandText = sql;
cmd.
ExecuteNonQuery();
//取出数据
sql =
"SELECT * FROM test";
cmd.CommandText = sql;
System.Data.SQLite.SQLiteDataReader reader = cmd.
ExecuteReader();
StringBuilder sb =
new
StringBuilder();
while
(reader.
Read())
{
sb.
Append(
"username:").
Append(reader.
GetString(
0)).
Append(
"\n")
.
Append(
"password:").
Append(reader.
GetString(
1));
}
MessageBox.
Show(sb.
ToString());
二、利用Dataset数据集向SQLite数据库插入数据,也直接贴代码:
DialogResult dlgResult= openFileDialog1.
ShowDialog();
// 打开要导入的文件
if
(openFileDialog1.FileName ==
""
||
dlgResult
!= DialogResult.OK)
return;
// 利用StreamReader类读取文本内容
StreamReader sr=
new
StreamReader
(File.
OpenRead(openFileDialog1.FileName),System.Text.Encoding.Default);
//连接数据库
System.Data.SQLite.SQLiteConnection conn =
new
System.Data.SQLite.
SQLiteConnection();
System.Data.SQLite.SQLiteConnectionStringBu
ilder connstr
=
new
System.Data.SQLite.
SQLiteConnectionStringBu ilder();
connstr.DataSource = datasource;
connstr.Password =
"admin";
//设置密码,SQLite ADO.NET实现了数据库密码保护
conn.ConnectionString = connstr.
ToString();
conn.
Open();
//大量更新时采用事务的方式,先缓存事务,然后SQLiteDataAdapter.update后批量commit
SQLiteTransaction ts = conn.
BeginTransaction();
string
sql=
" select name,number from test limit 1";
SQLiteDataAdapter dta =
new
SQLiteDataAdapter(sql,conn);
SQLiteCommandBuilder scb =
new
SQLiteCommandBuilder(dta);
dta.InsertCommand=scb.
GetInsertCommand();
DataSet DS =
new
DataSet();
dta.
FillSchema(DS,SchemaType.Source,
"Temp");
//加载表架构注意
dta.
Fill(DS,
"Temp");
//加载表数据
DataTable DT = DS.Tables[
"Temp"];
//插入数据
while
(!sr.EndOfStream)
{
string[]
strArr = sr.
ReadLine().
Split(
new
Char[]
{
'\t'
});
if
(strArr[
0]
!=
""
&&
strArr[
1]
!=
"")
{
DataRow DR = DT.
NewRow();
DR[
0]=strArr[
0];
DR[
1]=strArr[
1];
DT.Rows.
Add(DR);
}
}
int
result=dta.
Update(DT);
// 如不用BeginTransaction和Commit批量提交事务,性能会很低,350条数据20多秒
ts.
Commit();
// 提交事务
DS.
AcceptChanges();
// 释放资源
dta.
Dispose();
DS.
Clear();
conn.
Close();
conn.
Dispose();
sr.
Close();
sr.
Dispose();
MessageBox.
Show(
"成功导入了: "
+
result.
ToString()
+
" 行数据。",
"提示",
MessageBoxButtons.OK,
MessageBoxIcon.Information);
C#操作Dataset数据集与SQLite数据库
最新推荐文章于 2023-02-24 16:04:34 发布