DataConnector
データのクエリ
ADO.NET provider for Salesforce > データのクエリ

The ADO.NET Provider for Salesforce implements two classes you can use to perform CRUD (Create, Read, Update, and Delete) operations: DbDataReader and C1SalesforceDataAdapter classes. The tabs below describe the classes and code implementations.

Querying With DbDataReader

The DbDataReader class can be used to fetch data in subset increments as required. It can retrieve data quicker than the C1SalesforceDataAdapter 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.

C#
コードのコピー 
static void InsertDataReader()
{
    Console.WriteLine("Insert operation started !!!");
    using (C1SalesforceConnection conn = new C1SalesforceConnection(GCSalesforceServerConnectionString))
    {
        conn.Open();
        string insertSql = @"Insert into [Order] (AccountId, Status, BillingState,BillingStreet,BillingCity,BillingPostalCode,BillingCountry,EffectiveDate) 
                        Values ('0012w00000Ak4iLAAR', 'Draft','Delhi','Delhi','NCR','110009','India','23-JAN-1992')";

        //Insert コマンドを作成します。
        C1SalesforceCommand command = new C1SalesforceCommand(conn, insertSql);

        //Insert コマンドを実行します。
        int i = command.ExecuteNonQuery();
        if (i != -1)
        {
            Console.WriteLine("Insert operation successful !!! \n \n");
        }
    }
}

Read Data

This examples retrieves data by executing the Select command.

C#
コードのコピー 
static void ReadDataReader()
{
    Console.WriteLine("Read operation started !!!");
    using (C1SalesforceConnection con = new C1SalesforceConnection(GCSalesforceServerConnectionString))
    {
        con.Open();

        //Read コマンドを作成します。
        var cmd = con.CreateCommand();
        cmd.CommandText = "Select  *  FROM [Order] limit 10 ";

        //Readコマンドを実行し、取得したデータを表示します。
        var rdr = cmd.ExecuteReader();
        while (rdr.Read())
        {
            Console.WriteLine(String.Format("\t{0} --> \t\t{1} --> \t\t{2}", rdr["Id"], rdr["AccountId"], rdr["BillingCity"]));
        }
        Console.WriteLine("Read operation successful !!! \n \n");
    }
}

Update Data

This example modifies data by executing Update command.

C#
コードのコピー 
static void UpdateDataReader()
{
    Console.WriteLine("Update operation started !!!");
    using (C1SalesforceConnection conn = new C1SalesforceConnection(GCSalesforceServerConnectionString))
    {
        conn.Open();

        //Update コマンドを作成します。
        C1SalesforceCommand command = new C1SalesforceCommand(conn, "UPDATE [Order] SET BillingCity=@BillingCity where AccountId=@AccountId");
        command.Parameters.AddWithValue("@AccountId", "0012w00000Ak4iLAAR");
        command.Parameters.AddWithValue("@BillingCity", "Delhi");

        //Update コマンドを実行します。
        int i = command.ExecuteNonQuery();
        if (i != -1)
        {
            Console.WriteLine("Update operation successful !!! \n \n");
        }
    }
}

Delete Data

This example removes data by executing Delete command.

C#
コードのコピー 
static void DeleteDataReader()
{
    Console.WriteLine("Delete operation started!!!");
    using (C1SalesforceConnection conn = new C1SalesforceConnection(GCSalesforceServerConnectionString))
    {
        conn.Open();

        //Delete コマンドを作成します。
        C1SalesforceCommand command = new C1SalesforceCommand(conn, "Delete from [Order] where AccountId = @AccountId ");
        command.Parameters.AddWithValue("@AccountId", "0012w00000Ak4iLAAR");

        //Delete コマンドを実行します。
        int i = command.ExecuteNonQuery();
        if (i != -1)
        {
            Console.WriteLine("Delete operation successful !!! \n \n");
        }
    }
}

Querying With C1SalesforceDataAdapter

The C1SalesforceDataAdapter class can be used to retrieve a single result set containing all the data that matches a given query. The C1SalesforceDataAdapter 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 C1SalesforceDataAdapter is slower compared to the DbDataReader.

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

 

Create Data

The example adds new records by executing the Insert command.

C#
コードのコピー 
static void InsertDataAdapter()
{
    using (C1SalesforceConnection conn = new C1SalesforceConnection(GCSalesforceServerConnectionString))
    {
        //DataTable を設定します。
        C1SalesforceDataAdapter adapter = new C1SalesforceDataAdapter(conn, "Select * from [Order]");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);

        //Insert コマンドを作成します。
        adapter.InsertCommand = new C1SalesforceCommand(conn);
        adapter.InsertCommand.CommandText = "Insert into [Order] (AccountId,Status,BillingState,BillingCountry,EffectiveDate) values (@AccountId,@Status,@BillingState,@BillingCountry,@EffectiveDate)";
        adapter.InsertCommand.Parameters.Add("@AccountId", "AccountId");
        adapter.InsertCommand.Parameters.Add("@Status", "Status");
        adapter.InsertCommand.Parameters.Add("@BillingState", "BillingState");
        adapter.InsertCommand.Parameters.Add("@BillingCountry", "BillingCountry");
        adapter.InsertCommand.Parameters.Add("@EffectiveDate", "EffectiveDate");

        adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;

        //新しい行を挿入します。
        DataRow orderRow1 = dataTable.NewRow();
        orderRow1["AccountId"] = "0012w00000Ak4iLAAR";
        orderRow1["Status"] = "Draft";
        orderRow1["BillingState"] = "Delhi";
        orderRow1["BillingCountry"] = "India";
        orderRow1["EffectiveDate"] = "23-Jan-1992";
        dataTable.Rows.Add(orderRow1);

        //データベースを更新します。
        adapter.Update(dataTable);
    }
}

Read Data

This examples retrieves data by executing the Select command.

C#
コードのコピー 
static void ReadDataAdapter()
{
    using (C1SalesforceConnection con = new C1SalesforceConnection(GCSalesforceServerConnectionString))
    {
        //DataTable を設定します。
        C1SalesforceConnection conn = new C1SalesforceConnection(GCSalesforceServerConnectionString);
        C1SalesforceDataAdapter adapter = new C1SalesforceDataAdapter(conn, "Select * from [Order] limit 10");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);

        //取得したデータを表示します。
        foreach (DataRow row in dataTable.Rows)
        {
            Console.WriteLine("{0}\t{1}", row["AccountId"], row["BillingState"]);
        }
    }

Update Data

This example modifies data by executing Update command.

C#
コードのコピー 
static void UpdateDataAdapter()
{
    using (C1SalesforceConnection conn = new C1SalesforceConnection(GCSalesforceServerConnectionString))
    {
        //DataTable を設定します。
        C1SalesforceDataAdapter adapter = new C1SalesforceDataAdapter(conn, "Select * from [Order]");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);

        //Update コマンドを作成します。
        adapter.UpdateCommand = new C1SalesforceCommand(conn);
        adapter.UpdateCommand.CommandText = "UPDATE [Order] SET BillingState=@BillingState where AccountId=@AccountId";
        adapter.UpdateCommand.Parameters.Add("@BillingState", "BillingState");
        adapter.UpdateCommand.Parameters.Add("@AccountId", "AccountId");
        adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;

        // 既存の行を更新します。
        DataRow OrderRow1 = dataTable.Rows[0];
        OrderRow1["AccountId"] = "0012w00000Ak4iLAAR";
        OrderRow1["BillingState"] = "Mumbai";

        //データベースを更新します。
        adapter.Update(dataTable);
    }
}

Delete Data

This example removes data by executing Delete command.

C#
コードのコピー 
static void DeleteDataAdapter()
{
    using (C1SalesforceConnection conn = new C1SalesforceConnection(GCSalesforceServerConnectionString))
    {
        // データテーブルにデータを入力します。
        C1SalesforceDataAdapter adapter = new C1SalesforceDataAdapter(conn, "Select * from [Order]");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);

        //Delete コマンドを作成します。
        adapter.DeleteCommand = new C1SalesforceCommand(conn);
        adapter.DeleteCommand.CommandText = "Delete from [Order] where AccountId=@AccountId";
        adapter.DeleteCommand.Parameters.Add("@AccountId", "AccountId");
        adapter.DeleteCommand.UpdatedRowSource = UpdateRowSource.None;

        //行を削除します。
        DataRow OrderRow1 = dataTable.Rows[3];
        OrderRow1["AccountId"] = "8012w000000djLDAAY";
        OrderRow1.Delete();

        //データベースを更新します。
        adapter.Update(dataTable);
    }
}

 

 

Copyright © MESCIUS, Inc. All rights reserved.