# Querying Data

## Content



The ADO.NET Provider for Kintone implements two classes you can use to perform CRUD (Create, Read, Update, and Delete) operations: **DbDataReader** and [C1KintoneDataAdapter](/componentone/api/services/online-dataconnector/dotnet-standard-api/C1.AdoNet.Kintone/C1.AdoNet.Kintone.C1KintoneDataAdapter.html) classes. The tabs below describe the interfaces and code implementations.

### Data Reader

### Querying With DbDataReader

The **DbDataReader** class can be used to fetch data in subset increments as required. It can retrieve data quicker than the **C1KintoneDataAdapter** as it retrieves data in pages. When you read data from the **DbDataReader**, it requests the succeeding page from the data source to load the result, which makes the data retrieval faster.

The following **code examples** demonstrate create, read, update, and delete operations from the data source using the **DbDataReader**.

### Create Data

The example adds new records by executing the **Insert** command.

```csharp
static void InsertDataReader()
{
    string kintoneConnection = string.Format("Username={0};Password={1};Url={2}", Username, Password, Url);
    using (C1KintoneConnection conn = new C1KintoneConnection(kintoneConnection))
    {
        conn.Open();
        //Create Insert command
        C1KintoneCommand command = new C1KintoneCommand(conn, $"INSERT INTO Products(ProductName) VALUES('Lorem Ipsum')");
        //Execute Insert command
        int i = command.ExecuteNonQuery();
        if (i != -1)
        {
            Console.WriteLine("Insert operation successful !!! \n \n");
        }
    }
}
```

### Read Data

This example retrieves data by executing the **Select** command.

```csharp
static void ReadDataReader()
{
    string kintoneConnection = string.Format("Username={0};Password={1};Url={2}", Username, Password, Url);
    using (C1KintoneConnection con = new C1KintoneConnection(kintoneConnection))
    {
        con.Open();
        //Create Read command
        var cmd = con.CreateCommand();
        cmd.CommandText = "SELECT  *  FROM Products";
        //Execute Read command and display fetched data
        DbDataReader rdr = cmd.ExecuteReader();
        while (rdr.Read())
        {
            Console.WriteLine(String.Format("\t{0} --> \t\t{1}", rdr["Updated By"], rdr["ProductName"]));
        }
        Console.WriteLine("Read operation successful !!! \n \n");
    }
}
```

### Update Data

This example modifies data by executing the **Update** command.

```csharp
static void UpdateDataReader()
{
    string kintoneConnection = string.Format("Username={0};Password={1};Url={2}", Username, Password, Url);
    using (C1KintoneConnection conn = new C1KintoneConnection(kintoneConnection))
    {
        conn.Open();
        //Create Update command
        C1KintoneCommand command = new C1KintoneCommand(conn, "UPDATE Products SET [ProductName]='Lorem Ipsum updated' WHERE ProductName = 'Lorem Ipsum'");
        //Execute Update command
        int i = command.ExecuteNonQuery();
        if (i != -1)
        {
            Console.WriteLine("Update operation successful !!! \n \n");
        }
    }
}
```

### Delete Data

This example removes data by executing the **Delete** command.

```csharp
static void DeleteDataReader()
{
    string kintoneConnection = string.Format("Username={0};Password={1};Url={2}", Username, Password, Url);
    using (C1KintoneConnection conn = new C1KintoneConnection(kintoneConnection))
    {
        conn.Open();
        //Create Delete command
        C1KintoneCommand command = new C1KintoneCommand(conn, "DELETE FROM Products WHERE ProductName = 'Lorem Ipsum updated'");
        //Execute Delete command
        int i = command.ExecuteNonQuery();
        if (i != -1)
        {
            Console.WriteLine("Delete operation successful !!! \n \n");
        }
    }
}
```

### Data Adapter

### Querying With C1KintoneDataAdapter

The **C1KintoneDataAdapter** class can be used to retrieve a single result set containing all the data that matches a given query. The **C1KintoneDataAdapter** uses its Fill method to fetch data from the data source. An empty DataTable instance is passed as an argument to the Fill method. Once the method returns, the DataTable instance is populated with the queried data. Since the Fill method must retrieve all the data from the data source before returning, the **C1KintoneDataAdapter** is slower compared to the **DbDataReader**.

The following **code examples** demonstrate create, read, update, and delete operations from the data source using the **C1KintoneDataAdapter**.

### Create Data

The example adds new records by executing the **Insert** command.

```csharp
static void InsertDataAdapter()
{
    string kintoneConnection = string.Format("Username={0};Password={1};Url={2}", Username, Password, Url);
    using (C1KintoneConnection conn = new C1KintoneConnection(kintoneConnection))
    {
        //Populate DataTable
        C1KintoneDataAdapter adapter = new C1KintoneDataAdapter(conn, "SELECT * FROM Products");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);
        //Create Insert command
        adapter.InsertCommand = new C1KintoneCommand(conn);
        adapter.InsertCommand.CommandText = "INSERT INTO Products(ProductName, UnitPrice) VALUES(@ProductName, @UnitPrice)";
        adapter.InsertCommand.Parameters.Add("@UnitPrice", "UnitPrice");
        adapter.InsertCommand.Parameters.Add("@ProductName", "ProductName");
        adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
        //Insert new row
        DataRow productRow = dataTable.NewRow();
        productRow["UnitPrice"] = 100;
        productRow["ProductName"] = "Lorem Ipsum";
        dataTable.Rows.Add(productRow);
        //Update database
        var i = adapter.Update(dataTable);
        if (i != -1)
        {
            Console.WriteLine("Insert operation successful !!! \n \n");
        }
    }
}
```

### Read Data

This examples retrieves data by executing the **Select** command.

```csharp
static void ReadDataAdapter()
{
    string kintoneConnection = string.Format("Username={0};Password={1};Url={2}", Username, Password, Url);
    using (C1KintoneConnection conn = new C1KintoneConnection(kintoneConnection))
    {
        //Populate DataTable                
        C1KintoneDataAdapter adapter = new C1KintoneDataAdapter(conn, "SELECT * FROM Products");
        DataTable dataTable = new DataTable();
        var i = adapter.Fill(dataTable);
        if (i != -1)
        {
            Console.WriteLine("Read operation successful !!! \n \n");
            //Display fetched data
            foreach (DataRow row in dataTable.Rows)
            {
                Console.WriteLine("{0}\t{1}", row["Updated By"], row["ProductName"]);
            }
        }
    }
}
```

### Update Data

This example modifies data by executing the **Update** command.

```csharp
static void UpdateDataAdapter()
{
    string kintoneConnection = string.Format("Username={0};Password={1};Url={2}", Username, Password, Url);
    using (C1KintoneConnection conn = new C1KintoneConnection(kintoneConnection))
    {
        //Populate DataTable
        C1KintoneDataAdapter adapter = new C1KintoneDataAdapter(conn, "SELECT * FROM Products");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);
        //Create Update command
        adapter.UpdateCommand = new C1KintoneCommand(conn);
        adapter.UpdateCommand.CommandText = "UPDATE Products SET UnitPrice = @UnitPrice WHERE ProductName = @ProductName";
        adapter.UpdateCommand.Parameters.Add("@UnitPrice", "UnitPrice");
        adapter.UpdateCommand.Parameters.Add("@ProductName", "ProductName");
        adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
        //Update existing row
        DataRow productRow = dataTable.Rows[0];
        productRow["UnitPrice"] = 550;
        productRow["ProductName"] = "Lorem Ipsum";
        //Update database
        var i = adapter.Update(dataTable);
        if (i != -1)
        {
            Console.WriteLine("Update operation successful !!! \n \n");
        } 
    }
}
```

### Delete Data

This example removes data by executing the **Delete** command.

```csharp
static void DeleteDataAdapter()
{
    using (C1KintoneConnection conn = new C1KintoneConnection(kintoneConnection))
    {
        //Populate Datatable
        C1KintoneDataAdapter adapter = new C1KintoneDataAdapter(conn, "Select * from Products");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);
        //Create Delete command
        adapter.DeleteCommand = new C1KintoneCommand(conn);
        adapter.DeleteCommand.CommandText = "Delete from Products where Name = @Name";
        adapter.DeleteCommand.Parameters.Add("@Name", "Name");
        adapter.DeleteCommand.UpdatedRowSource = UpdateRowSource.None;
        //Delete a row
        DataRow bookRow1 = dataTable.Rows[3];
        bookRow1["Name"] = "'Test Incremental Cache by Demen'";
        bookRow1.Delete();
        //Update Database
        var i = adapter.Update(dataTable);
        if (i != -1)
        {
            Console.WriteLine("Delete operation successful !!! \n \n");
        }
    } 
```