If you’re working frequently with the connected layer, the previous post would be sufficient for most purposes (given adequate knowledge of SQL), if perhaps a little awkward. The following information might make your life a little bit easier.
Connection String Builders
Connection strings can be somewhat difficult to manage. The ADO.NET data providers that Microsoft supplies support ConnectionStringBuilder objects, which offer an alternative to providing a literal connection string. There are two ways to use it. You can either use the default constructor and provide all the values…
var csb = new SqlConnectionStringBuilder(); csb.InitialCatalog = "CatalogName"; csb.DataSource = @"(local)\SQLEXPRESS"; csb.ConnectTimeout = 30; csb.IntegratedSecurity = true; // for illustrative purposes... string connectionString = csb.ConnectionString;
… or you can provide a connection string and change or add values as needed:
string connectionString = ConfigurationManager.ConnectionStrings["SqlProvider"]; var csb = new SqlConnectionStringBuilder(connectionString); csb.ConnectTimeout = 5; connectionString = csb.ConnectionString;
More on Command Objects
The Command object can be initialized after construction via properties, as in my previous post, or it can be initialized with a connection and SQL statement in its constructor, like so:
cn.ConnectionString = @"Data Source=(local)\SQLEXPRESS;" + "Integrated Security=SSPI;Initial Catalog=AutoLot"; cn.Open(); string strSQL = "SELECT * FROM Inventory"; SqlCommand myCommand = new SqlCommand(strSQL, cn);
It also has a CommandType enumeration that can be either Text (default), StoredProcedure, or TableDirect. The Text and StoredProcedure command types are self-explanatory, but TableDirect is a new one to me. The documentation on it sounds a bit self-contradictory (“Multiple table access is not supported when CommandType is set to TableDirect.” / “In order to access multiple tables, use a comma delimited list”), and a cursory google search returns limited results which are mostly documentation, which suggests that it isn’t used very often, so I’m not going to spend any more time trying to figure it out.
More on Data Readers
Data readers are read-only and forward-only, and return data one record at a time. Unlike a DataSet, they do not retain their values in memory. They maintain an open connection to the data store. Their Read() method returns boolean, indicating whether another record was found.
In addition to the indexer property, the data reader has GetName(int) and GetValue(int) methods that return the column name and value of a given index in the result set. The number of fields present in the result set is contained in the dataReader.FieldCount property.
A data reader can also contain multiple results. The command could contain a query like “SELECT * FROM Inventory; SELECT * FROM Customers;”. Use the data reader’s NextResult() method to move to the result of the next query in order.