MySQL之insert用法

1，先在App.config中添加语句。

<connectionStrings>
		<add name="connStr" connectionString="server=localhost;database=endmill; uid=root;pwd=123456" providerName="MySql.Data.MySqlClient" />
	</connectionStrings>

2，在NuGet中导入MySQL包。

3，添加引用using

using MySql.Data.MySqlClient;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

4，在MySQL中创建students数据表，如下图所示。

5，采用 insert 添加数据，代码如下：

            string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;

            using (MySqlConnection connect = new MySqlConnection(connStr))
            {
                //string sql = "insert into students(name,class,score) values ('小刚',5.23,89)";

                string sql = "insert into students set name='小刚',class=5.23,score=89";

                //string sql = "insert into students(name,class,score) values ('小刚',5.23,89),('小明',5.34,100)";

                MySqlCommand cmd = new MySqlCommand(sql, connect);

                connect.Open();

                cmd.ExecuteNonQuery();

                connect.Close();

                this.Close();
            }

添加数据有多种写法，分别如下所示，均可成功在数据表中添加数据：前两种可添加一行数据，第三种可一次添加多行数据。

第一种

//string sql = "insert into students(name,class,score) values ('小刚',5.23,89)";

第二种

string sql = "insert into students set name='小刚',class=5.23,score=89";

第三种

//string sql = "insert into students(name,class,score) values ('小刚',5.23,89),('小明',5.34,100)";

6，采用界面输入方式添加数据。

界面xaml代码及布局如下：

    <Grid>
        <StackPanel>
            <Label Content="name1" Width="60" Height="30" HorizontalAlignment="Left" Margin="20,10,10,10"></Label>
            <Label Content="class1" Width="100" Height="30" HorizontalAlignment="Left" Margin="20,10,10,10"></Label>
            <Label Content="score1" Width="100" Height="30" HorizontalAlignment="Left" Margin="20,10,10,10"></Label>
        </StackPanel>

        <StackPanel>
            <TextBox Name="name2" Width="150" Height="30" Margin="100,10,10,10" HorizontalAlignment="Left"></TextBox>
            <TextBox Name="class2" Width="150" Height="30" Margin="100,10,10,10" HorizontalAlignment="Left"></TextBox>
            <TextBox Name="score2" Width="150" Height="30" Margin="100,10,10,10" HorizontalAlignment="Left"></TextBox>
        </StackPanel>

        <StackPanel Orientation="Horizontal">
            <Button Name="write" Width="100" Height="30" HorizontalAlignment="Left"  Margin="10,130,20,20" Click="write_Click">界面输入数据</Button>
            <Button Name="writeLine" Width="100" Height="30" HorizontalAlignment="Left" Margin="10,180,20,20" Click="writeLine_Click" >单条写入</Button>
            <Button Name="mult_writeLine" Width="100" Height="30" HorizontalAlignment="Left" Margin="10,180,20,20" Click="mult_writeLine_Click">多条写入</Button>
            <Button Name="updateline" Width="100" Height="30" HorizontalAlignment="Left" Margin="10,180,20,20" Click="updateline_Click">更新数据</Button>
        </StackPanel>
    </Grid>

后台代码写在 “界面输入数据” 按钮下，代码如下：

        private void write_Click(object sender, RoutedEventArgs e)
        {
            string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;

            using (MySqlConnection connect = new MySqlConnection(connStr))
            {
                MySqlCommand cmd = new MySqlCommand("insert into students (name,class,score) values (@name,@class,@score)", connect);

                cmd.Parameters.AddWithValue("@name", name2.Text);

                cmd.Parameters.AddWithValue("@class", class2.Text);

                cmd.Parameters.AddWithValue("@score", score2.Text);
                
                connect.Open();

                cmd.ExecuteNonQuery();
                
                connect.Close();

                this.Close();
            }
            
        }