sqlite C#

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