• 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

  • 相关阅读:
    leepcode题目解析4
    Python爬虫6-利用ProxyHandler设置代理服务器
    Python爬虫5-利用usergent伪装访问方式
    Python爬虫4-URLError与HTTPError
    Python爬虫3-parse编码与利用parse模拟post请求
    中间件
    跨域
    ORM中的锁和事务
    cookie和session
    之Ajax
  • 原文地址:https://www.cnblogs.com/llstart-new0201/p/6816424.html
Copyright © 2020-2023  润新知