C#ADO.NET基础一

简介

使用SQLite进行讲解

1.基础类：

SQLiteConnection	连接数据库
SQLiteCommand	执行命令（增，删，改，查），或存储过程
SQLiteDataReader	读取查询到的数据

2.SQLiteCommand讲解

 

CommandType	获取或设置Command对象要执行命令的类型
CommandText	获取或设置要对数据库执行的SQL语句或存储过程名或表名
CommandTimeOut	获取或设置在终止对执行命令的尝试并生成错误之前的等待时间
Parameters	获取Command对象需要使用的参数集合
ExecuteScalar	执行命令（查）返回数据中第一行第一列的值。
ExecuteNonQuery	执行命令（增，删，改，查）
ExecuteReader	执行命令（查）返回查到的所有数据

 

一.下载SQLite库

二.使用

1.连接

$@"Data Source={Application.StartupPath}\Test.db;Password=;Version=3;"

2.增

        private void btnAdd_Click(object sender, EventArgs e)
        {
            string name = "张三";
            int age = 19;

            using (SQLiteConnection conn = new SQLiteConnection(connStrl))
            {
                conn.Open();
                using (SQLiteCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "insert into test(name,age) values(@name,@age);";
                    cmd.Parameters.AddWithValue("@name", name);
                    cmd.Parameters.AddWithValue("@age", age);
                    cmd.ExecuteNonQuery();
                }
                conn.Close();
            }
        }

 

3.删

        private void btnDelete_Click(object sender, EventArgs e)
        {
            string name = "张三";

            try
            {
                using (SQLiteConnection conn = new SQLiteConnection(connStrl))
                {
                    conn.Open();
                    using (SQLiteCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = $"delete from test where name=@name";
                        cmd.Parameters.AddWithValue("@name", name);
                        cmd.ExecuteNonQuery();
                    }
                    conn.Close();
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

 

4.改

        private void btnUpdate_Click(object sender, EventArgs e)
        {
            string nameOld = "张一";
            string nameNew = "张四";

            try
            {
                using (SQLiteConnection conn = new SQLiteConnection(connStrl))
                {
                    conn.Open();
                    using (SQLiteCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = $"update test set name=@nameNew where name=@nameOld";
                        cmd.Parameters.AddWithValue("@nameOld", nameOld);
                        cmd.Parameters.AddWithValue("@nameNew", nameNew);
                        cmd.ExecuteNonQuery();
                    }
                    conn.Close();
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

 

5.查

        private void btnSelect_Click(object sender, EventArgs e)
        {
            textBox1.Text = "";
            try
            {
                using (SQLiteConnection conn = new SQLiteConnection(connStrl))
                {
                    conn.Open();
                    using (SQLiteCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = "select name,age from test";
                        using (SQLiteDataReader reader = cmd.ExecuteReader())
                        {
                            if (reader.HasRows)
                                while (reader.Read())
                                {
                                    string name = reader.GetString(0);
                                    int age = reader.GetInt32(1);

                                    textBox1.Text += $"name:{name} age:{age}\r\n";
                                }
                        }
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }
        }