近日有需要写点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.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());
二、利用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.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);
C#操作Dataset数据集与SQLite数据库
原文：http://www.cnblogs.com/qufly/p/3532243.html
