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()); 二、利用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数据库

标签：