DataConnector | ComponentOne
ADO.NET provider for Snowflake / Querying Data

In This Topic
    Querying Data
    In This Topic

    The ADO.NET Provider for Snowflake implements two classes you can use to perform the Read operation: DbDataReader and C1SnowflakeDataAdapter classes. The tabs below describe the classes and code implementation.

    Read operation with DbDataReader

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

    Create Data

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

    C#
    Copy Code 
    static void InsertDataReader()
   {
       var sql = "Insert Into ORDER_DETAILS (ORDER_ID,PRODUCT_ID, UNIT_PRICE, DISCOUNT, STATUS_ID) VALUES (1, 40, 5999, 30, 5);";
       using (var con = new C1SnowflakeConnection(connectionString))
       {
           con.Open();
           var command = con.CreateCommand();
           command.CommandText = sql;
           Console.WriteLine("Insert operation started !!! \\n \\n");
           //Execute Insert command
           int i = command.ExecuteNonQuery();
           if (i != -1)
               Console.WriteLine("Insert operation successful !!! \\n \\n");
       }
   }

    Create Data and return created values

    SQLite RETURNING clause is used at the end of the INSERT statement to return the inserted values. More on the SQLite’s RETURNING clause can be found here. When providing the RETURNING clause, call the ExecuteReader method to obtain the inserted values. This method will return a reader which can be used to read the result table containing the newly inserted values.

    C#
    Copy Code 
    static void ReturningClause()
{
    using (var con = new C1SnowflakeConnection(connectionString))
    {
        con.Open();
        // Create Insert command
        var cmd = con.CreateCommand();
        cmd.CommandText = "INSERT INTO TRACK_TABLE (id,c1) VALUES(1, 'red') returning id;";
        // Execute Insert command and use the reader object with the values returned
        var reader = cmd.ExecuteReader();
        while(reader.Read()){
            Console.WriteLine($"Id value --> {reader["id"]}");
        }
    }
}

    The following code examples demonstrate read operation from the data source using the DbDataReader and retrieves data by executing the Select command.

    C#
    Copy Code 
    static void ReadDataReader()
{
    Console.WriteLine("Read operation started !!!");
    using (C1SnowflakeConnection con = new C1SnowflakeConnection(connectionString))
    {
      con.Open();     
      //Create Read command
      var cmd = con.CreateCommand();
      cmd.CommandText = "Select * FROM CALL_CENTER";
 
      //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[0], rdr[1], rdr[2]));
      }
      Console.WriteLine("Read operation successful !!! \\n \\n");
    }
}

    Read Operation with C1SnowflakeDataAdapter

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

    Create Data 

    The example adds new records by executing the Insert command.

    C#
    Copy Code 
        public void InsertWithDataAdapter()
    {
        string sqlQuery = "Insert Into ORDER_DETAILS (ORDER_ID,PRODUCT_ID, UNIT_PRICE, DISCOUNT, STATUS_ID, INVENTORY_ID) VALUES (1, 40, 5999, 30, 60 ,@INVENTORY_ID);";
        using (var con = new C1SnowflakeConnection(connectionString))
        {
            con.Open();
            var command = con.CreateCommand();
            command.CommandText = sqlQuery;
            DbParameter parameter = command.CreateParameter();
            parameter.ParameterName = "INVENTORY_ID";
            parameter.Value = "2000";
            parameter.DbType = DbType.Int32;
            command.Parameters.Add(parameter);
            var result = command.ExecuteNonQuery();
        }
    }

    The following code demonstrates a read operation from the data source using the C1SnowflakeDataAdapter and retrieves data by executing the Select command.

    C#
    Copy Code 
    static void ReadDataAdapter()
{
    using (C1SnowflakeConnection con = new C1SnowflakeConnection(connectionString))
    {
        //Populate DataTable
        C1SnowflakeDataAdapter adapter = new C1SnowflakeDataAdapter(con, "Select * from CALL_CENTER");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable); 
        //Display fetched data
        foreach (DataRow row in dataTable.Rows)
        {
           Console.WriteLine(String.Format("\\t{0} --> \\t\\t{1} --> \\t\\t{2}", row[0], row[1], row[2]));
        }
     }
}