c 连接mysql中dataset_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);