C#.NET sqlDatabase CommandBuilder and DataAdapter: The name 'Adapter' does not exit in the current context

Question

I've been following a C# .Net tutorial online. Created a class and have declare the variable Adapter1 for my DataAdapter.

I'm using CommandBuilder with Adapter1 to update, save, delete or insert new record to the database.

The problem I'm having is that the UpdateDatabase method I've declared seems not to see the variable Adapter1.

Please take a look at the code below and tell me what I'm doing wrong. Code that's causing error is at the very bottom

private string sql_string;
private string strCon; //This is a write-only property

public string Sql
{
    set { sql_string = value; }
}

public string connection_string
{
    set { strCon = value; }
}

public System.Data.DataSet GetConnection
{
    get { return MyDataSet(); }
}

private System.Data.DataSet MyDataSet()
{
    System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(strCon);
    con.Open();

    System.Data.SqlClient.SqlDataAdapter Adapter1 = new System.Data.SqlClient.SqlDataAdapter(sql_string, con);

    System.Data.DataSet dat_set = new System.Data.DataSet();
    Adapter1.Fill(dat_set, "Table_Data_1");

    con.Close();

    return dat_set;
}

public void UpdateDatabase (System.Data.DataSet ds)
{
    System.Data.SqlClient.SqlCommandBuilder cb = new System.Data.SqlClient.SqlCommandBuilder(Adapter1);
    cb.DataAdapter.Update(ds.Tables[0]);
}

Answer 1

The reason you're getting this specific error is that there is no variable named Adapter1 that is in scope in function UpdateDatabase(). You could make the local variable that you've declared in function MyDataSet() a member variable, then it would be visible in UpdateDatabase(). This is not the best approach, however, because you'll be counting on code that uses this helper class to always call the MyDataSet() function before the UpdateDatabase() function, otherwise the data adapter won't be initialized (you'll get a null reference error). If you want to structure the code this way, you should initialize your adapter in the constructor of the class that you're building, that way the code that uses your library can be sure that the adapter will always be initialized properly. If you structure the code that way, you can make the connection string a constructor parameter instead of a write- only property.

By all means keep working on this code as an exercise, but be aware that DataAdapters and DataSets are a very old part of .NET, and this idiom isn't used much in modern data tiers. Either people use ORMs (like EntityFramework) or they use ADO.NET commands directly if they want control and efficiency. I would also point out that the class you're building appears to be trying to fill the role of a DataAdapter, so it wouldn't have any utility in real life (people would just use a SqlDataAdapter directly instead of your class).