DataConnector
ADO.NET provider for Salesforce / CRUD Operations
In This Topic
    CRUD Operations
    In This Topic

    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 interfaces and code implementations.

    Querying With DbDataReader

    DbDataReader class can be used to fetch data in subset increments as required.

    The DbDataReader can fetch data quicker than the C1SalesforceDataAdapter as it retrieves data in pages. When you read data from 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 DbDataReader.

     

    Create Data

    The example adds new records by executing the Insert command.

    C#
    Copy Code
    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')";
    
        //Create Insert command;
        C1SalesforceCommand command = new C1SalesforceCommand(conn, insertSql);
    
        // Execute Insert command
        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#
    Copy Code
    Console.WriteLine("Read operation started !!!");
    using (C1SalesforceConnection con = new C1SalesforceConnection(GCSalesforceServerConnectionString))
    {
        con.Open();
    
        //Create Read command
        var cmd = con.CreateCommand();
        cmd.CommandText = "Select  *  FROM [Order] limit 10 ";
    
        //Execute Read command and display fetched data
        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#
    Copy Code
    Console.WriteLine("Update operation started !!!");
    using (C1SalesforceConnection conn = new C1SalesforceConnection(GCSalesforceServerConnectionString))
    {
        conn.Open();
    
        //Create Update command
        C1SalesforceCommand command = new C1SalesforceCommand(conn, "UPDATE [Order] SET BillingCity=@BillingCity where AccountId=@AccountId");
        command.Parameters.AddWithValue("@AccountId", "0012w00000Ak4iLAAR");
        command.Parameters.AddWithValue("@BillingCity", "Delhi");
    
        //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 Delete command.

    C#
    Copy Code
    Console.WriteLine("Delete operation started!!!");
    using (C1SalesforceConnection conn = new C1SalesforceConnection(GCSalesforceServerConnectionString))
    {
        conn.Open();
    
        //Create Delete command
        C1SalesforceCommand command = new C1SalesforceCommand(conn, "Delete from [Order] where AccountId = @AccountId ");
        command.Parameters.AddWithValue("@AccountId", "0012w00000Ak4iLAAR");
    
        //Execute Delete command
        int i = command.ExecuteNonQuery();
        if (i != -1)
        {
            Console.WriteLine("Delete operation successful !!! \n \n");
        }
    }
    

    Querying With C1SalesforceDataAdapter

    C1SalesforceDataAdapter class can be used to retrieve a single result set of all the data that matches a query.

    DataAdapter uses its Fill method to fetch data from the data source. An empty DataTable instance is passed as an argument to the Fill method. When the method returns, the DataTable instance is populated with the queried data.

    The Fill method needs to retrieve all the data from data source before returning, and hence C1SalesforceDataAdapter is slower than the DbDataReader.

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

     

    Create Data

    The example adds new records by executing the Insert command.

    C#
    Copy Code
     using (C1SalesforceConnection conn = new C1SalesforceConnection(GCSalesforceServerConnectionString))
    {
        //Populate DataTable
        C1SalesforceDataAdapter adapter = new C1SalesforceDataAdapter(conn, "Select * from [Order]");
       DataTable dataTable = new DataTable();
       adapter.Fill(dataTable);
    
        //Create Insert command
       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;
        
      //Insert new row
      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);
    
         //Update database
      adapter.Update(dataTable);
     }
    

    Read Data

    This examples retrieves data by executing the Select command.

    C#
    Copy Code
    using (C1SalesforceConnection con = new C1SalesforceConnection(GCSalesforceServerConnectionString))
    {
        //Populate DataTable
        C1SalesforceConnection conn = new C1SalesforceConnection(GCSalesforceServerConnectionString);
        C1SalesforceDataAdapter adapter = new C1SalesforceDataAdapter(conn, "Select * from [Order] limit 10");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);
    
        //Display fetched data
        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#
    Copy Code
    using (C1SalesforceConnection conn = new C1SalesforceConnection(GCSalesforceServerConnectionString))
    {
        //Populate DataTable
        C1SalesforceDataAdapter adapter = new C1SalesforceDataAdapter(conn, "Select * from [Order]");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);
    
        //Create Update command
        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;
    
        //Update existing row
        DataRow OrderRow1 = dataTable.Rows[0];
        OrderRow1["AccountId"] = "0012w00000Ak4iLAAR";
        OrderRow1["BillingState"] = "Mumbai";
    
        //Update database
        adapter.Update(dataTable);
    }
    

    Delete Data

    This example removes data by executing Delete command.

    C#
    Copy Code
    using (C1SalesforceConnection conn = new C1SalesforceConnection(GCSalesforceServerConnectionString))
    {
        //Populate Datatable
        C1SalesforceDataAdapter adapter = new C1SalesforceDataAdapter(conn, "Select * from [Order]");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);
    
        //Create Delete command
        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;
    
        //Delete a row
        DataRow OrderRow1 = dataTable.Rows[3];
        OrderRow1["AccountId"] = "8012w000000djLDAAY";
        OrderRow1.Delete();
    
        //Update Database
        adapter.Update(dataTable);
    }