ADO.NET 增删改查

ADO.NET是一种数据访问技术，可以通过ADO.NET将内存中的临时数据写入到数据库中，也可以将数据库中的数据提取到内存中供程序调用。

• 建立链接
• 创建指令
• 链接开启
• 执行指令
• 关闭链接

基础表

新增

public static void TestSqlInsert()
{
        string source = "server=ip;database=northwind;uid=tomcat;pwd=root;";
        using (SqlConnection connection = new SqlConnection(source))
        {
                SqlCommand command = connection.CreateCommand();
                string commandText = "INSERT INTO Categories(CategoryName,Description) VALUES(@CategoryName,@Description)";
                command.CommandText = commandText;
                command.CommandType = CommandType.Text;
                command.Parameters.AddWithValue("@CategoryName", "Mushroom");
                command.Parameters.AddWithValue("@Description", "菌类");
                connection.Open();
                command.ExecuteNonQuery();
                connection.Close();
        }
}

删除

public static void TestSqlDelete()
{
        string source = "server=ip;database=northwind;uid=tomcat;pwd=root;";
        using (SqlConnection connection = new SqlConnection(source))
        {
                string commandText = "DELETE FROM Categories WHERE CategoryID=@CategoryID";

                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                command.CommandText = commandText;
                command.CommandType = CommandType.Text;
                command.Parameters.AddWithValue("@CategoryID", "1002");
                connection.Open();
                command.ExecuteNonQuery();
                connection.Close();
        }
}

修改

public static void TestSqlUpdate()
{
        string source = "server=ip;database=northwind;uid=tomcat;pwd=root;";
        using (SqlConnection connection = new SqlConnection(source))
        {
                SqlCommand command = connection.CreateCommand();
                string commandText = "UPDATE Categories SET CategoryName=@CategoryName, Description=@Description WHERE CategoryID=@CategoryID";
                command.CommandText = commandText;
                command.CommandType = CommandType.Text;
                command.Parameters.AddWithValue("@CategoryID", "1");
                command.Parameters.AddWithValue("@CategoryName", "Beverages");
                command.Parameters.AddWithValue("@Description", "Soft drinks, coffees, teas, beers, and ales");
                connection.Open();
                command.ExecuteNonQuery();
                connection.Close();
        }
}

查询

检索数据分两种方式：有链接的和无链接的。

public static void TestSqlSelect()
{
        string source = "server=ip;database=northwind;uid=tomcat;pwd=root;";
        using (SqlConnection connection = new SqlConnection(source))
        {
                SqlCommand cmd = connection.CreateCommand();
                cmd.Connection = connection;
                cmd.CommandText = "SELECT * FROM Categories";
                cmd.CommandType = CommandType.Text;
                connection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                DataTable table = reader.GetSchemaTable();
                foreach(DataRow row in table.Rows)
                {
                        LogHelper.Log($"{row[0]}-{row[1]}-{row[2]}");
                }
                connection.Close();
        }
}

查询二

public static void TestSqlSearch()
{
        string source = "server=ip;database=northwind;uid=tomcat;pwd=root;";
        using (SqlConnection connection = new SqlConnection(source))
        {
                SqlCommand command  = connection.CreateCommand();
                command.Connection = connection;
                command.CommandText = "SELECT * FROM Categories";
                command.CommandType = CommandType.Text;
                command.Parameters.AddWithValue("@CategoryName", "Tuber");
                command.Parameters.AddWithValue("@Description", "块茎");
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                DataSet ds = new DataSet();
                adapter.Fill(ds, "Categories");
                foreach(DataRow row in ds.Tables["Categories"].Rows)
                {
                        LogHelper.Log($"CategoryID{row[0]} CategoryName {row[1]} Description {row[2]}");
                }
        }
}