DATABASE

ADO.NET Programming : Microsoft SQL Server (part 1) - Connecting to SQL Server, Creating Command Objects

5/22/2013 9:45:06 PM

If, as we said earlier, a variety of databases are available for mobile devices, an even greater variety of databases are available for desktop machines and servers. Of these, Microsoft SQL Server was designed with built-in support for .NET Compact Framework applications—it provides synchronization of data between a SQL Server database and a SQL Server CE database. The .NET Compact Framework also provides applications with access to SQL Server databases through the provider-specific classes that run on Windows CE devices. Just as there are SqlCeConnection, SqlCeCommand, and other classes in the .NET Compact Framework for accessing SQL Server CE, there are also SqlConnection, SqlCommand, and other classes available for accessing SQL Server.

The Microsoft SQL Server provider-specific ADO.NET classes are located in the System.Data.SqlClient namespace. Because the provider-specific classes adhere to a common specification, accessing SQL Server from your application is similar to accessing SQL Server CE: You open connections, submit commands, process returned rows with a data reader, and transfer data to and from a data set by using a data adapter. The text within the ConnectionString and CommandText properties may be more complex when accessing SQL Server, but the properties and methods of the classes are predominately the same.

We begin by recapping some statements regarding the differences between SQL Server and SQL Server CE as they relate to accessing each from your application.

  • Connecting to a SQL Server database is more complicated than connecting to a SQL Server CE database.

    - A network connection to the server must exist.

    - One server can host multiple SQL Servers.

    - One SQL Server can contain many databases.

    - SQL Server provides for detailed user access security.

  • The syntax is more complete in SQL Server than in SQL Server CE.

    - Views, triggers, functions, and stored procedures are supported.

    - GRANT, REVOKE, and DENY statements are supported, along with system stored procedures for maintaining user access rights.

    - The ANSI standard INFORMATION_SCHEMA views are supported.

  • Data integrity is more rigorous in SQL Server than in SQL Server CE.

    - Triggers and stored procedures provide for additional server-side data checking that cannot be overridden by the application.

    - User access permissions can be granted at the table and stored procedure levels.

  • Stored procedures are supported in SQL Server but not in SQL Server CE.

    - Parameterized application functionality can be defined on the server and invoked from the client.

    - The SQLCommand class contains built-in support for stored procedures.

  • Some System.Data.SqlServerCe classes, properties, methods, and events are not available in System.Data.SqlClient.

    - No Engine class exists. Instead, databases must be created by submitting DDL statements.

    - The data reader’s Seek method is not supported.

  • The relationship between a connection and a data reader is different.

    - SQL Server allows multiple open connections but only one open reader per open connection.

    - SQL Server CE allows only one open connection but multiple open readers per open connection.

  • Concurrency is handled differently.

    - SQL Server provides concurrency protection.

    - SQL Server CE does not provide concurrency protection.

  • More things can go wrong in SQL Server.

    - The need for a remote connection, the granularity of user access security, and the server-side data integrity capabilities all increase the possibility that your application’s request can fail and an exception will be raised. (This is a client-side judgment. From SQL Server’s point of view, access security and data integrity constraints don’t cause things to go wrong, they prevent things from going wrong.)

Despite the major differences between the two products, accessing each from your application is remarkably similar. We hope this emerges from the upcoming discussion of the subject.

Once we have set an assembly reference to the System.Data.SqlClient.dll module and optionally added the matching using statement shown here, we are ready to create a connection object and open a connection to a SQL Server database:

using System.Data.SqlClient;

1. Connecting to SQL Server

We use essentially the same connection class to connect to a SQL Server database as we used to connect to a SQL Server CE database, although the connection classes reside in different namespaces and have slightly different names. (In SQL Server CE, we connect using the SqlCeConnection[4] class, whereas with SQL Server we use the SqlConnection[5] class.) But both classes adhere to the same Microsoft specification for provider-specific classes, so the PMEs are the same.

[4] Fully qualified name: System.Data.SqlServerCe.SqlCeConnection.

[5] Fully qualified name: System.Data.SqlClient.SqlConnection.

The code shown in Listing 1 is the code for the DataReader application that we originally wrote for SQL Server CE and have now modified for SQL Server. For convenience, in Figure 1 we again show the main screen of the DataReader program.

Listing 1. Using a DataReader Object with SQL Server
using System;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;

namespace DataReader
{
   /// <summary>
   /// Summary description for FormMain
   /// </summary>
   public class FormMain : System.Windows.Forms.Form
   {
      #region Assorted Generated Code

      #region Windows Form Designer generated code


      //  Connection string
      private string  strConn =
         "data source=OurServer;" +
         "initial catalog=Northwind;" +
         "user id=DeliveryDriver;" +
         "pwd=DD;" +
         "workstation id=OurDevice;" +
         "packet size=4096;" +
         "persist security info=False;";

      //  Select product keys.
      private string strGetProductIDs =
         " SELECT ProductID FROM Products ";

      //  Select one product, joined with its category.
      private string strGetOneProduct =
         " SELECT ProductID, ProductName, CategoryName " +
         "   FROM Products P " +
         "   JOIN Categories C on C.CategoryID = P.CategoryID " +
         "  WHERE P.ProductID = ";

      //  Used to bypass the SelectIndexChanged event
      //     during the loading of the ComboBox
      private bool boolLoading = true;


      private void FormMain_Load(object sender, EventArgs e)
      {
         //  Display a close box.
         this.MinimizeBox = false;

         //  Let the form present itself.
         Application.DoEvents();

         //  Load product keys into the ComboBox
         //     and select the first one.
         LoadProductIDs();
         comboKeys.SelectedIndex = 0;
      }


      private void comboKeys_SelectedIndexChanged(object sender,
                                                  EventArgs e)
      {
         //  A product key has been selected; retrieve
         //     and display the corresponding product.
         if (! boolLoading )
         {
            LoadOneProduct((int)comboKeys.SelectedItem);
         }
      }


      private void textProductName_Validated(object sender,
                                             EventArgs e)
      {
         //  Update this product row in the database.
         UpdateSelectedRow(int.Parse(textProductID.Text),
                           textProductName.Text);
      }


      private void LoadProductIDs()
      {
         //  Clear the ComboBox.
         comboKeys.Items.Clear();

         //  A connection, a command, and a reader
         SqlConnection connDB =
            new SqlConnection(strConn);
         SqlCommand cmndDB =
            new SqlCommand(strGetProductIDs, connDB);
         SqlDataReader drdrDB;

         try
         {
            //  Open the connection.
            connDB.Open();

            //  Submit the SQL statement and receive
            //     the SqlReader for the results set.
            drdrDB = cmndDB.ExecuteReader();

            //  Read each row.  Add the contents of its
            //     only column as an entry in the ComboBox.
            while ( drdrDB.Read() )
            {
               comboKeys.Items.Add(drdrDB["ProductID"]);
            }

            //  Close the reader.
            drdrDB.Close();
         }
         catch( SqlException exSQL )
         {
            foreach( SqlError errSQL in exSQL.Errors )
            {
               MessageBox.Show(errSQL.Message);
            }
         }
         finally
         {
            //  Close the connection.
            connDB.Close();

            //  Start responding to the ComboBox's
            //     SelectedIndexChanged events.
            this.boolLoading = false;
         }
      }


      private void LoadOneProduct( int intProductID )
      {
         //  Append the param ProductID to the SELECT statement.
         string  strSQL = strGetOneProduct + intProductID;

         //  A connection, a command, and a reader
         SqlConnection  connDB = new SqlConnection(strConn);
         SqlCommand  cmndDB = new SqlCommand(strSQL,connDB);
         SqlDataReader drdrDB;

         //  Open the connection.
         connDB.Open();

         //  Submit the SQL statement and receive
         //     the SqlReader for the one-row
         //     results set.
         drdrDB = cmndDB.ExecuteReader();

         //  Read the first (only) row.
         //     Display it.  Close the reader.
         if ( drdrDB.Read() )
         {
            LoadControlsFromRow(drdrDB);
         }
         drdrDB.Close();

         //  Close the connection.
         connDB.Close();
      }


      private void LoadControlsFromRow( SqlDataReader drdrDB )
      {
         //  Transfer the column titles and the field
         //     contents of the current row from the
         //     reader to the form's controls.
         lblProductID.Text = drdrDB.GetName(0);
         textProductID.Text = drdrDB.GetValue(0).ToString();
         lblProductName.Text = drdrDB.GetName(1);
         textProductName.Text = drdrDB.GetValue(1).ToString();
         lblCategoryName.Text = drdrDB.GetName(2);
         textCategoryName.Text = drdrDB.GetValue(2).ToString();
      }


      private void UpdateSelectedRow(int intProductID,
                                     string strProductName)
      {
         //  A connection and a command
         SqlConnection connDB = new SqlConnection(strConn);
         SqlCommand cmndDB = new SqlCommand();

         //  Open the connection.
         connDB.Open();

         //  Update the product name for the selected product.
         cmndDB.Connection = connDB;
         cmndDB.CommandText =
            " UPDATE Products " +
            " SET ProductName = " + "'" + strProductName + "'" +
            " WHERE ProductID = " + intProductID;
         cmndDB.ExecuteNonQuery();

         //  Close the connection.
         connDB.Close();
      }
   }
}

					  

Figure 1. The DataReader Program (Repeated)


As shown in Listing 6.11, we modified the original code to access SQL Server. We did so by changing the statement in which the connection string is defined, by changing SqlCe to Sql, and by setting a reference to the SQL Server client namespace rather than to the SQL Server CE client namespace. Because the Seek method is not supported by the SqlDataReader class.

The one statement that we changed to effect the transition from SQL Server CE to SQL Server is shown here in its modified form:

//  Connection string
private string  strConn =
   "data source=OurServer;" +
   "initial catalog=Northwind;" +
   "user id=DeliveryDriver;" +
   "pwd=DD;" +
   "workstation id=OurDevice;" +
   "packet size=4096;" +
   "persist security info=False;";

As you can see from the connection string, a SQL Server connection is more complex than its SQL Server CE counterpart, which was:

private string  strConn = "Data Source=" +
                           @"My Documents\ourProduceCo.sdf";

In the new version, we specify the server name, database name, SQL Server username and password, and some other information. We did not code this ourselves—we got Visual Studio .NET to do it for us, and we recommend that you do the same.

To obtain this assistance, your development environment must be able to connect to your development server. Open a new Windows Application project rather than a Smart Device Application project. In a Windows Application project, the entries under the Toolbox’s Data tab are enabled and you can drag and drop them onto the form. Figure 2 shows a connection object being dropped on the form.

Figure 2. Dragging and Dropping a Connection Object


When dropped, a connection object does not appear in the form itself but rather in the blank area beneath the form. Once the connection object is there, you can select it and access its properties. One of your choices in the connection string drop-down list is <New Connection...> (see Figure 3). Choosing this item produces the dialog box shown in Figure 4.

Figure 3. Requesting a New Connection String


Figure 4. Specifying Connection Information


Once you enter your information and dismiss the dialog box, the generated connection string appears in the Properties window. From there, you can cut and paste the connection string into your .NET Compact Framework program.

2. Creating Command Objects

Once you have the connection object, you can move on to creating a command object. As you can see from the code, there is very little difference between the SqlCommand class and the SqlCeCommand class. Nor is there much difference between the two data reader classes. The only change we had to make in the program is caused by the absence of the Seek method in the SqlDataReader class.

Although there is very little difference between the two command classes, there can be a great difference in how you use them. This is because SQL Server has stored procedures and SQL Server CE does not, and access to the stored procedures is provided only through the SqlCommand class.

Other  
  •  SQL Server 2008 R2 : Database Maintenance - Executing a Maintenance Plan
  •  SQL Server 2008 R2 : Database Maintenance - Managing Maintenance Plans Without the Wizard
  •  SQL Server 2008 R2 : The Maintenance Plan Wizard (part 2)
  •  SQL Server 2008 R2 : The Maintenance Plan Wizard (part 1)
  •  SQL Server 2008 : Common performance problems (part 2)
  •  SQL Server 2008 : Common performance problems (part 1) - Procedure cache bloating
  •  SQL Server 2008 : SQLOS schedulers, Wait analysis
  •  MySQL for Python : Creating Users and Granting Access - Removing privileges in MySQL, Using REVOKE in Python
  •  MySQL for Python : Creating Users and Granting Access - GRANT access in MySQL
  •  MySQL for Python : Creating Users and Granting Access - Removing users in MySQL, DROPping users in Python, Granting access in Python
  •  
    Video
    Top 10
    SG50 Ferrari F12berlinetta : Prancing Horse for Lion City's 50th
    The latest Audi TT : New angles for TT
    Era of million-dollar luxury cars
    Game Review : Hearthstone - Blackrock Mountain
    Game Review : Battlefield Hardline
    Google Chromecast
    Keyboards for Apple iPad Air 2 (part 3) - Logitech Ultrathin Keyboard Cover for iPad Air 2
    Keyboards for Apple iPad Air 2 (part 2) - Zagg Slim Book for iPad Air 2
    Keyboards for Apple iPad Air 2 (part 1) - Belkin Qode Ultimate Pro Keyboard Case for iPad Air 2
    Michael Kors Designs Stylish Tech Products for Women
    REVIEW
    - First look: Apple Watch

    - 3 Tips for Maintaining Your Cell Phone Battery (part 1)

    - 3 Tips for Maintaining Your Cell Phone Battery (part 2)
    Popular Tags
    Video Tutorail Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Exchange Server Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe Photoshop CorelDRAW X5 CorelDraw 10 windows Phone 7 windows Phone 8 Iphone