sqlite是一种轻量级的本地数据库,对于一般应用程序来说,非常方便。数据库读取无非建立连接,然后执行。
1、建立连接
string DBFile = @"D: estsqliteTestmyTest est.db"; if (File.Exists(DBFile)) { File.Delete(DBFile); } SQLiteConnection conn = new SQLiteConnection(string.Format("Data Source={0}", DBFile)); conn.Open();
2、创建执行命令,创建两个表格。
SQLiteCommand cmd = conn.CreateCommand(); cmd.CommandText = "create table table1(ID,Name,Sex,Age,Salary)"; cmd.ExecuteNonQuery(); cmd.CommandText = "create table table2(ID,Major,Title,Level)"; cmd.ExecuteNonQuery();
3.写入数据
//插入一条数据 cmd.CommandText = @"insert into table1(ID,Name,Sex,Age,Salary)" + "values('01','name01','male','28','8000')"; cmd.ExecuteNonQuery(); cmd.CommandText = @"insert into table2(ID,Major,Title,Level)" + "values('01','major01','t01','01')"; cmd.ExecuteNonQuery();
下面采用sqliteparamater和string.format分别说明一下批量插入数据的方法,写入数据时由于每写入一次要执行一次开启和关闭,所以速度很慢,自此可以使用事务
(一)采用string.format协助写入数据
SQLiteTransaction trans = conn.BeginTransaction(); string strFormat = @"insert into table1(ID,Name,Sex,Age,Salary)" + " values('{0}','{1}','{2}','{3}','{4}')"; for (int i=0;i<1000;i++) { string id = i.ToString(); string name = "name" + i; string sex = "male"; string age = "28"; string salary = "8000"; //string.Format(stringFormat, id, name, sex, age, salary); cmd.CommandText = string.Format(strFormat,id,name,sex,age,salary); cmd.ExecuteNonQuery(); } trans.Commit();
(二)采用sqliteparamater协助写入数据
cmd.CommandText = @"insert into table2(ID,Major,Title,Level)" + "values(@ID,@Major,@Title,@Level)"; for(int i=0;i<1000;i++) { SQLiteParameter[] para = new SQLiteParameter[] { new SQLiteParameter("@ID",i.ToString()), new SQLiteParameter("@Major", "major" + i), new SQLiteParameter("@Title", "title" + i), new SQLiteParameter("@Level", "level" + i), }; cmd.Parameters.AddRange(para); cmd.ExecuteNonQuery(); }
4、数据读取
数据读取可以采用sqlitedatareader读取,或者采用sqliteadapter读取到datable或者dataset中。
(一)采用sqlitedatareader读取数据
SQLiteCommand openCmd = conn1.CreateCommand(); openCmd.CommandText = "select *from table2"; SQLiteDataReader reader = openCmd.ExecuteReader(); while (reader.Read()) { Console.Write(reader["ID"] + " "); Console.Write(reader["Major"] + " "); Console.Write(reader["Title"] + " "); Console.WriteLine(reader["Level"] + " "); }
(二)采用sqliteadapter读取到datatable中
//SQLiteDataAdapter adapter = new SQLiteDataAdapter(openCmd); //DataTable dt = new DataTable(); //adapter.Fill(dt); //for(int i=0;i<dt.Rows.Count;i++) //{ // for(int j=0;j<dt.Columns.Count;j++) // { // Console.Write(dt.Rows[i][j] + " "); // } // Console.WriteLine(); //}
(三)采用sqliteadapter读取到dataset中
//读入到dataset里面 //SQLiteDataAdapter adapter1 = new SQLiteDataAdapter(Sql,conn1); SQLiteDataAdapter adapter1 = new SQLiteDataAdapter(openCmd); DataSet ds = new DataSet(); // adapter1.FillSchema(ds, SchemaType.Source, "ta"); adapter1.Fill(ds, "ta"); DataTable dt = ds.Tables["ta"]; for (int i = 0; i < dt.Rows.Count; i++) { for (int j = 0; j < dt.Columns.Count; j++) { Console.Write(dt.Rows[i][j] + " "); } Console.WriteLine(); }
完整代码:
static void Main(string[] args) { string DBFile = @"D: estsqliteTestmyTest est.db"; if (File.Exists(DBFile)) { File.Delete(DBFile); } SQLiteConnection conn = new SQLiteConnection(string.Format("Data Source={0}", DBFile)); conn.Open(); SQLiteCommand cmd = conn.CreateCommand(); cmd.CommandText = "create table table1(ID,Name,Sex,Age,Salary)"; cmd.ExecuteNonQuery(); cmd.CommandText = "create table table2(ID,Major,Title,Level)"; cmd.ExecuteNonQuery(); //插入一条数据 cmd.CommandText = @"insert into table1(ID,Name,Sex,Age,Salary)" + "values('01','name01','male','28','8000')"; cmd.ExecuteNonQuery(); cmd.CommandText = @"insert into table2(ID,Major,Title,Level)" + "values('01','major01','t01','01')"; cmd.ExecuteNonQuery(); //cmd.Dispose(); //conn.Close(); //批量添加数据:1、借助string.format辅助添加。2、采用sqliteparamater进行添加 //往table1中添加数据,采用方法1,借助string.format SQLiteTransaction trans = conn.BeginTransaction(); string strFormat = @"insert into table1(ID,Name,Sex,Age,Salary)" + " values('{0}','{1}','{2}','{3}','{4}')"; for (int i=0;i<1000;i++) { string id = i.ToString(); string name = "name" + i; string sex = "male"; string age = "28"; string salary = "8000"; //string.Format(stringFormat, id, name, sex, age, salary); cmd.CommandText = string.Format(strFormat,id,name,sex,age,salary); cmd.ExecuteNonQuery(); } //方法1 //string strFormat1 = @"insert into table2(ID,Major,Title,Level)" + // " values('{0}','{1}','{2}','{3}')"; //for(int i=0;i<1000;i++) //{ // string id = i.ToString(); // string major = "major" + i; // string title = "title" + i; // string level = "level" + i; // cmd.CommandText = string.Format(strFormat1, id, major, title, level); // cmd.ExecuteNonQuery(); //} //方法2 cmd.CommandText = @"insert into table2(ID,Major,Title,Level)" + "values(@ID,@Major,@Title,@Level)"; for(int i=0;i<1000;i++) { SQLiteParameter[] para = new SQLiteParameter[] { new SQLiteParameter("@ID",i.ToString()), new SQLiteParameter("@Major", "major" + i), new SQLiteParameter("@Title", "title" + i), new SQLiteParameter("@Level", "level" + i), }; cmd.Parameters.AddRange(para); cmd.ExecuteNonQuery(); } trans.Commit(); cmd.Dispose(); conn.Close(); //读取sqlite SQLiteConnection conn1 = new SQLiteConnection(string.Format("Data Source={0}", DBFile)); conn1.Open(); SQLiteCommand openCmd = conn1.CreateCommand(); openCmd.CommandText = "select *from table2"; //SQLiteDataReader reader = openCmd.ExecuteReader(); //while (reader.Read()) //{ // Console.Write(reader["ID"] + " "); // Console.Write(reader["Major"] + " "); // Console.Write(reader["Title"] + " "); // Console.WriteLine(reader["Level"] + " "); //} //读入到datable里面 //SQLiteDataAdapter adapter = new SQLiteDataAdapter(openCmd); //DataTable dt = new DataTable(); //adapter.Fill(dt); //for(int i=0;i<dt.Rows.Count;i++) //{ // for(int j=0;j<dt.Columns.Count;j++) // { // Console.Write(dt.Rows[i][j] + " "); // } // Console.WriteLine(); //} //读入到dataset里面 //SQLiteDataAdapter adapter1 = new SQLiteDataAdapter(Sql,conn1); SQLiteDataAdapter adapter1 = new SQLiteDataAdapter(openCmd); DataSet ds = new DataSet(); // adapter1.FillSchema(ds, SchemaType.Source, "ta"); adapter1.Fill(ds, "ta"); DataTable dt = ds.Tables["ta"]; for (int i = 0; i < dt.Rows.Count; i++) { for (int j = 0; j < dt.Columns.Count; j++) { Console.Write(dt.Rows[i][j] + " "); } Console.WriteLine(); } openCmd.Dispose(); conn1.Close(); Console.ReadKey(); }
http://www.runoob.com/sql/sql-create-db.html