ADO.net how to start

Question

For the last 3 hours I've been trying to figure out how ADO.NET works with no success. Could someone point me at a great tutorial or somethign similar? I am trying to build a DB from scratch and working with it in my WPF program.

I have worked before with JDBC and sqlite but I didn't find a tutorial from zero to a DB where I can connect and query.

Thanks for your help.

I still need one good example with building DB from zero. The Northwind example just doesn't work for me. Here's my code and the error I'm getting:

try
        {
            // step 1: create a SqlConnection object to connect to the
            // SQL Server Northwind database
            SqlConnection mySqlConnection = new SqlConnection("server=localhost;database=Northwind;uid=sa;pwd=sa");
            // step 2: create a SqlCommand object
            SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
            // step 3: set the CommandText property of the SqlCommand object to
            // a SQL SELECT statement that retrieves a row from the Customers table
            mySqlCommand.CommandText =
            "SELECT CustomerID, CompanyName, ContactName, Address " +
            "FROM Customers " +
            "WHERE CustomerID = ‘ALFKI’";
            // step 4: open the database connection using the
            // Open() method of the SqlConnection object
            mySqlConnection.Open();
            //DEVELOPING YOUR FIRST ADO.NET PROGRAM 5
            // step 5: create a SqlDataReader object and call the ExecuteReader()
            // method of the SqlCommand object to run the SELECT statement
            SqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader();
            // step 6: read the row from the SqlDataReader object using
            // the Read() method
            mySqlDataReader.Read();
            // step 7: display the column values
            Console.WriteLine("mySqlDataReader[\"CustomerID\"] = " +
            mySqlDataReader["CustomerID"]);
            Console.WriteLine("mySqlDataReader[\"CompanyName\"] = " +
            mySqlDataReader["CompanyName"]);
            Console.WriteLine("mySqlDataReader[\"ContactName\"] = " +
            mySqlDataReader["ContactName"]);
            Console.WriteLine("mySqlDataReader[\"Address\"] = " +
            mySqlDataReader["Address"]);
            // step 8: close the SqlDataReader object using the Close() method
            mySqlDataReader.Close();
            // step 9: close the SqlConnection object using the Close() method
            mySqlConnection.Close();
        }
        catch (SqlException e)
        {
            Console.WriteLine("A SqlException was thrown");
            Console.WriteLine("Number = " + e.Number);
            Console.WriteLine("Message = " + e.Message);
            Console.WriteLine("StackTrace:\n" + e.StackTrace);
        }
    }
}

The error:

A SqlException was thrown
Number = 2
Message = A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

Answer 1

The ADO.Net bit goes in the middle so I'd suggest that that's not what you should start with. I'd suggest first designing the DB. Then look at this article for a simple tutorial for how to bind a list control to some data in the DB.

Or you could just use SqlCommand etc to populate your GUI manually if you don't want to use data binding.

And here's another, more comprehensive article, for data binding in WPF.

Answer 2

For information about ADO.NET check MSDN. Generally you need DB server with database you want to connect and than three classes from System.Data.SqlClient namespace:

• SqlConnection
• SqlCommand
• SqlDataReader

Example of simple access is:

string connectionString = "Data Source=.;Initial Catalog=TestDB;Integrated Security=SSPI";
string query = "SELECT * FROM dbo.TestTable"

using (var connection = new SqlConnection(connectionString))
{
  var command = new SqlCommand(query, connection);
  connection.Open();

  using (var reader = command.ExcecuteReader())
  {
    while (reader.Read())
    {
      Console.WriteLine(reader.GetString(0));
    }
  }
}

Answer 3

The problem is with your connection string. I suggest you change this:

        SqlConnection mySqlConnection = new SqlConnection("server=localhost;database=Northwind;uid=sa;pwd=sa");

to this:

        SqlConnection mySqlConnection = new SqlConnection("server=.;database=Northwind;uid=sa;pwd=sa");

Then go to http://www.ConnectionStrings.com if you ever need to look up connection strings. It is a great site.

p.s. Best practice is to store your connection string in app.config or web.config.