近日有需要写点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. 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);