.net qr code generator free Using Commands in Visual Basic .NET

Printer QR Code 2d barcode in Visual Basic .NET Using Commands

A command represents any kind of SQL statement made against your database. This gives you powerful application control over your database structure and contents, enabling you to create and delete databases, insert and retrieve data, and manipulate table structures, among other things. A command is implemented via the SqlCommand class and controlled using the SqlCommand.CommandType and SqlCommand.CommandText properties, which are often used in tandem. If you set the CommandType to CommandType.Text, the CommandText property (a string) should contain the SQL statement that you want to execute. If the type is CommandType. StoredProcedure, the CommandText should contain the name of the stored procedure to execute. Finally, if the type is CommandType.Table, the command text should contain the name

of a table in your database. Executing this type of command will return all records stored in that table. So, for example, if you want to create a query that returns the street address for customers in the AdventureWorks address database who live at postal code 98011, you would use code like this:

string connectionString = WebConfigurationManager.ConnectionStrings["AW"].ConnectionString; SqlConnection sqlCon = new SqlConnection(connectionString); SqlCommand sqlComm = new SqlCommand(); sqlComm.Connection = sqlCon; sqlComm.CommandType = CommandType.Text; sqlComm.CommandText = "SELECT AddressLine1 FROM Person.Address " + "WHERE (PostalCode = N'98011')";

Now that you have your command, you are going to want to execute it to do anything meaningful. There are four different methods for executing an ADO.NET command:

a result set. For example, if you are inserting, updating, or deleting records, you can use the command s ExecuteNonQuery method. It will return an integer containing the number of records that were affected.

ExecuteScalar: This executes the query and returns the first column of the first row of the result set. This is very useful for queries that use SQL COUNT or SUM, or other

ExecuteReader: This executes a SELECT query and returns a DataReader object that can be used to provide forward-only read access to your data. ExecuteXmlReader: This is similar to ExecuteReader except that it gives you an XmlReader

to access the data. So, executing a command to generate the required feedback is very straightforward. Here s an example of executing the previous query, with the results available via a SqlDataReader:

SqlDataReader sRead = sqlComm.ExecuteReader();

In the next section, you ll see how to use this reader to step through the results of the query and access the first line of the returned addresses.

When you execute the previous command, ADO.NET returns a SqlDataReader. This reader is a forward-based read-only cursor that moves forward by one record every time you call its Read method. The Read method returns True if it reads a record and False otherwise. Upon a successful read, it will then load an array of values with the index of the array representing the column name so reader["ColumnName"] will contain this record s value for ColumnName. Thus, we can iterate through the returned result set using a while loop, and upon a successful read, retrieve the result set s data by simply accessing the reader as if it were an array. Listing 4-1 contains the complete code to access the addresses for postal code 98011 in the AdventureWorks database.

using using using using using using using using using using using System; System.Data; System.Configuration; System.Web; System.Web.Security; System.Web.UI; System.Web.UI.WebControls; System.Web.UI.WebControls.WebParts; System.Web.UI.HtmlControls; System.Web.Configuration; System.Data.SqlClient;

using System.Text; public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { string connectionString = WebConfigurationManager.ConnectionStrings["AW"].ConnectionString; StringBuilder strReturn = new StringBuilder(); using (SqlConnection sqlCon = new SqlConnection(connectionString)) { SqlCommand sqlComm = new SqlCommand();

sqlComm.Connection = sqlCon; sqlComm.CommandType = CommandType.Text; sqlComm.CommandText = "SELECT AddressLine1 FROM Person.Address " + "WHERE (PostalCode = N'98011')"; sqlCon.Open(); SqlDataReader sRead = sqlComm.ExecuteReader(); while (sRead.Read()) { strReturn.Append("<li>"); strReturn.Append(sRead["AddressLine1"]); strReturn.Append("</li>"); } } litResults.Text = strReturn.ToString(); } }

You can see the results of running this in Figure 4-17.