dotConnect for SQL Server Documentation
In This Topic
    Inserting Data Into Tables
    In This Topic

    This tutorial describes how to use SqlCommand component to insert data into tables by means of executing SQL queries.

    In this walkthrough:

    Requirements

    This walkthrough supposes that you know how to connect to a server (tutorial Logging onto the server) and that necessary objects are already created on the server (tutorial Creating database objects).

    Note that if you do not use design-time (specifically, if you do not place on a form designer SqlConnection component from toolbox), you have to embed licensing information manually. This is described in the Licensing topic.

    General information

    Data on a server can be modified (inserted, updated 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 SqlCommand. Another way is to use design-time features that provide graphical user interface to manage database.

    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

    Inserting data in run time

    To insert the first row into table dept you can use the following statement:

    INSERT INTO dept (deptno, dname, loc) VALUES (10,'Accounting','New York')

    The following code fragment executes the query:

    SqlConnection conn = new SqlConnection("User Id=sa;Password=mypassword;DataSource=127.0.0.1;Database=Test");
    SqlCommand cmd = new SqlCommand();
    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();
    }
    
    
    Dim conn As SqlConnection = New SqlConnection("User Id=sa;Password=mypassword;DataSource=127.0.0.1;Database=Test")
    Dim cmd As SqlCommand = New SqlCommand()
    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 SqlCommand object, assigns the query text and connection to the SqlCommand instance. Connection is opened then. The ExecuteNonQuery() method of SqlCommand runs SQL statement in the CommandText property and returns the 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.

    Design time setup

    Same operations in design time include following steps:

    1. Place SqlConnection component on a designer.
    2. Setup its properties and open connection by changing the State property to Open
      - or -
      right-click on the component, choose Connect item and use the dialog to connect to the server.
    3. Place SqlCommand component on the designer.
    4. In its Connection property select name of the SqlConnection instance on the designer.
    5. In the CommandText property type in the following query:

      INSERT INTO dept VALUES (20,'Sales','Dallas');
      INSERT INTO dept VALUES (30,'Sales2','Chicago');
    6. Right-click on the SqlCommand and choose Execute from popup menu.

    Note that the last two steps might be easier to do in the SqlCommand editor. To invoke it choose CommandText item from SqlCommand popup menu or click on the dots in this property in Properties window.

    Additional information

    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, SqlLoader is the fastest way to insert data, SqlScript is designed for executing series of statements. For more information on these components refer to dotConnect for SQL Server reference.

    See Also

    dotConnect for SQL Server Tutorials  | SqlCommand Class  | SqlLoader  | SqlScript Class