This tutorial describes how to use MySqlCommand component to insert data into tables by means of executing SQL queries.
This walkthrough supposes that you know how to connect to server (tutorial Logging onto the server) and that necessary objects are already created on the server (tutorial Creating database objects).
This article uses classes from dotConnect for MySQL, a high-performance ADO.NET provider with the ORM support (Entity Framework, NHibernate, and LinqConnect).
Note that if you do not use design-time (specifically, if you do not place on a designer MySqlConnection component from toolbox), you have to embed licensing information manually. This is described in dotConnect for MySQL documentation.
Data on server can be modified (inserted, changed or deleted) using Data Manipulation Language (DML), which is a part of SQL. The DML statements can be executed on server by account that has necessary privileges.
There are two ways to manipulate a database. You can build DML statements manually and run them within some component like MySqlCommand. Another way is to use design-time features that provide graphical user interface to manage database. We will discuss both ways.
The goal of this tutorial is to insert the following data into tables dept and emp :
Table dept
deptno |
dname |
loc |
---|---|---|
10 | Accounting | New York |
20 | Sales | Dallas |
30 | Sales2 | Chicago |
Table emp
empno |
ename |
job |
mgr |
hiredate |
sal |
comm |
deptno |
---|---|---|---|---|---|---|---|
7369 | Smith | Clerk | 7566 | 1980-12-17 | 800 | Null | 20 |
7499 | Allen | Salesman | 7698 | 1981-02-20 | 1600 | 300 | 30 |
7521 | Ward | Salesman | 7698 | 1981-02-22 | 1250 | 500 | 30 |
7566 | Jones | Manager | 7839 | 1981-04-02 | 2975 | Null | 20 |
7654 | Martin | Salesman | 7698 | 1981-09-28 | 1250 | 1400 | 30 |
7698 | Blake | Manager | 7839 | 1981-05-01 | 2850 | Null | 30 |
7839 | King | President | Null | 1981-11-17 | 5000 | Null | 10 |
To insert the first row into table dept you can use the following statement:
INSERT INTO demo.dept (deptno, dname, loc) VALUES (10,'Accounting','New York')
The following code fragment executes the query:
[C#]MySqlConnection conn = new MySqlConnection("User Id=root;Password=mypassword;Host=127.0.0.1;"); MySqlCommand cmd = new MySqlCommand(); cmd.CommandText = "INSERT INTO dept (deptno, dname, loc) VALUES (10,'Accounting','New York')"; cmd.Connection = conn; conn.Open(); try { int aff = cmd.ExecuteNonQuery(); MessageBox.Show(aff + " rows were affected."); } catch { MessageBox.Show("Error encountered during INSERT operation."); } finally { conn.Close(); }[Visual Basic]
Dim conn As MySqlConnection = New MySqlConnection("User Id=root;Password=mypassword;Host=127.0.0.1;") Dim cmd As MySqlCommand = New MySqlCommand() cmd.CommandText = "INSERT INTO dept (deptno, dname, loc) VALUES (10,'Accounting','New York')" cmd.Connection = conn conn.Open() Try Dim aff As Integer = cmd.ExecuteNonQuery() MessageBox.Show(aff & " rows were affected.") Catch MessageBox.Show("Error encountered during INSERT operation.") Finally conn.Close() End Try
The sample first creates a connection with hardcoded connection string. Then it creates MySqlCommand object, assigns the query text and connection to the MySqlCommand instance. Connection is opened then. The ExecuteNonQuery() method of MySqlCommand runs SQL statement in the CommandText property and returns number of rows affected by the query. This method is not intended to run SELECT statements. We will discuss retrieving data in other tutorials.
If the query is executed successfully you are notified about number of affected rows. If some error occurs you get the error message. The connection is closed anyway. It is recommended that you use try ... finally clauses to make sure the connections are closed properly.
Note that the database is explicitly mentioned in the query. This is because we did not specify default database in the connection string. To avoid this we could add the following argument to the connection string: database=demo;.
The same operations in design time include following steps:
Note that the last two steps might be easier to do in the MySqlCommand editor. To invoke it choose CommandText item form MySqlCommand shortcut menu or click on the ellipsis in this property in Properties window.
Sometimes it is very handy to modify data in design time. This section describes one of the ways to edit data in a grid. These operations are convenient to do in dbForge Fusion for MySQL.
This tutorial describes how you can use MySqlCommand component to insert data into tables. Actually there are lots of ways to insert data into tables. Any tool or component that is capable of running a SQL query, can be used to manage data. Some components are best for performing certain tasks. For example, MySqlLoader is the fastest way to insert data, MySqlScript is designed for executing series of statements. For more information on these components refer to dotConnect for MySQL documentation.
We use cookies to provide you with a better experience on the Devart website. You can read more about our use of cookies in our Cookies Policy.
Click OK to continue browsing the Devart site. Be aware you can disable cookies at any time.