近日有需要写点C#程序，有用到Dataset数据集和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.SQLiteConnectionStringBuilder
connstr
=
new System.Data.SQLite.SQLiteConnectionStringBuilder();
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());
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.SQLiteConnectionStringBuilder connstr
= new System.Data.SQLite.SQLiteConnectionStringBuilder();
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);