文章目录
前言
sqlite是一种轻量级的本地数据库,对于一般应用程序来说,非常方便。数据库读取无非建立连接,然后执行。
1.建立连接
string DBFile = @"D:\test\sqliteTest\myTest\test.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分别说明一下批量插入数据的方法,写入数据时由于每写入一次要执行一次开启和关闭,所以速度很慢,自此可以使用事务。
3.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();
}
trans.Commit();
3.2 采用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中。
4.1 采用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"] + " ");
}
4.2 采用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();
//}
4.3 采用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();
}
5.完整代码
static void Main(string[] args)
{
string DBFile = @"D:\test\sqliteTest\myTest\test.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();
}