Implementing T-SQL in Web Applications in VS .NET

Printing Code 128 Code Set A in VS .NET Implementing T-SQL in Web Applications
Implementing T-SQL in Web Applications
Code 128B Scanner In .NET Framework
Using Barcode Control SDK for .NET Control to generate, create, read, scan barcode image in .NET applications.
Many situations require Web applications to retrieve, add, modify, and delete data stored in a database on a server. For example, consider a Web application that enables users to register as customers. When a customer fills out the Registration form and submits it, the customer registration information must be stored in a database on a server so as to maintain the registered customer's records. After the registration, the customer might need to change their customer details, such as telephone number or address. Later, the customer might want to discontinue purchasing from the same store. In such a situation, the Web application must take care of addition, modification, and deletion of data in the respective database on a server. In this section, you'll create a Web application to retrieve, add, modify, and delete data in a table stored on a SQL server. You can choose to use either Visual Basic or C# to do so. In the following example, you'll create a Visual Basic Web application project. Figure 12-1 displays the schematic diagram of the tables used to illustrate the server-side data access from a Web application.
Draw Code 128A In .NET Framework
Using Barcode drawer for Visual Studio .NET Control to generate, create Code-128 image in .NET framework applications.
Figure 12-1: A schematic diagram of the Sales database Before you start implementing T-SQL in your Web application, create the tables as shown in the preceding schematic diagram. Also, add records in the Products and Customers tables. You can refer to Figure 12-2 and Figure 12-3 to add records in the Products and Customers tables.
Reading ANSI/AIM Code 128 In .NET
Using Barcode reader for .NET Control to read, scan read, scan image in .NET framework applications.
Figure 12-2: A sample Order form After creating the ASP.NET Web Application project, design two Web Forms as shown in Figure 12-2 and Figure 12-3. The Order form will enable customers on the Web to place orders for products. Refer to Table 12-2 to specify IDs for the controls (that are used in code examples) on the Order form. In this form, you'll implement the functionality to view the complete product list or to view the details of a specific product. For this to happen, you'll access data from the Products table in a database called "Sales" stored on a SQL server.
Barcode Encoder In Visual Studio .NET
Using Barcode maker for .NET framework Control to generate, create barcode image in .NET framework applications.
Figure 12-3: A sample Customer form Table 12-2: IDs of the Controls on the Order Forms Control Order ID TexBox ID Order_ID
Scan Barcode In .NET Framework
Using Barcode scanner for Visual Studio .NET Control to read, scan read, scan image in VS .NET applications.
Table 12-2: IDs of the Controls on the Order Forms Control Customer ID TextBox Product ID TextBox Quantity TextBox Order Date TextBox Data Grid ID Customer_ID Product_ID Order_Quantity Order_Date MyDataGrid
Code 128C Creation In C#.NET
Using Barcode generation for .NET framework Control to generate, create Code 128A image in .NET framework applications.
The Customer form will enable users to register themselves as customers. Table 12-3 shows the IDs of the controls (that are used in code examples) on the Customer form. This form uses the Customers table in the "Sales" database stored on a SQL server. Table 12-3: IDs of the Controls on the Customer Form Control Customer ID TextBox Customer Name TextBox Address TextBox City TextBox State TextBox Zip TextBox DataGrid Label next to the Add button ID Cust_ID Cust_Name Cust_Address Cust_City Cust_State Cust_Zip MyDataGrid LblMessage
Create Code 128A In Visual Studio .NET
Using Barcode encoder for ASP.NET Control to generate, create Code 128 Code Set C image in ASP.NET applications.
The sample forms use a DataGrid control to display records from the tables stored in a SQL database on a SQL server. A DataGrid control enables a form to display data bound to a data source. Accessing data After designing the forms, you'll add the desired functionality to them. First, you'll add the functionality to the Order form. The form should enable customers to view the complete product list by clicking the View Product List button. Also, the form should enable customers to view the details of a specific product by clicking the View Product Details button. To implement this functionality, open the code behind file (with .vb extension) of the Order form. At the top of the Order form, import the two namespaces as follows: Imports System.Data Imports System.Data.SqlClient Next, in the Click event of the button labeled View Product List, enter the following code: 'Declare the objects of the SqlConnection, 'SqlDataAdapter, and DataSet classes Dim DS As DataSet Dim MyConnection As SqlConnection Dim MyCommand As SqlDataAdapter
Code128 Maker In VB.NET
Using Barcode printer for .NET Control to generate, create Code-128 image in Visual Studio .NET applications.
'Initializing the SqlConnection object MyConnection = New SqlConnection ("server=localhost; uid=sa;pwd=;database=Sales") 'Initializing the SqlDataAdapter object with the SQL 'query to access data from the Products table MyCommand = New SqlDataAdapter("select * from Products", MyConnection) 'Initializing the DataSet object and filling the data set with the query result DS = new DataSet() MyCommand.Fill(DS,"Products") 'Setting the DataSource property of the DataGrid control MyDataGrid.DataSource=DS.Tables("Products").DefaultView 'Binding the DataGrid control with the data MyDataGrid.DataBind() In this code, the comments provide explanation for the statements that follow. However, some statements need more explanation: When initializing the SqlConnection object, the constructor takes four parameters: o The first parameter, which represents the SQL Server, is localhost, indicating that the server resides on the local computer. However, if the SQL server resides on a network, you need to give its complete address. o The uid and pwd parameters represent the User ID and Password on the SQL Server. o The database parameter represents the name of the SQL database that you want to access. In this case, the database is "Sales." When initializing the SqlDataAdapter object, the constructor takes two parameters: o The first parameter represents the SQL query. In this case, the query is used to retrieve all the records from the Products table. o The second parameter represents the SqlConnection object. The Fill method of the SqlDataAdapter class is used to fill the DataSet object with the data. This method takes two parameters: The DataSet object The identifier for the DataTable
Bar Code Encoder In .NET
Using Barcode generation for Visual Studio .NET Control to generate, create bar code image in Visual Studio .NET applications.
When setting the DataSource property of the DataGrid control, the default view of the Products table in the DataSet object is used.
Making USS Code 39 In .NET Framework
Using Barcode generator for .NET framework Control to generate, create Code39 image in Visual Studio .NET applications.
After you write the respective code, save the project and execute it. When you click the button with the "View Product List" caption, the product details are displayed in the DataGrid control. Now, you'll implement the functionality to display the product details of only that product whose ID is entered in the Product ID text box. To do so, write the following code in the Click event of the button labeled "View Product Details": Dim DS As DataSet Dim MyConnection As SqlConnection Dim MyCommand As SqlDataAdapter 'Initializing a String variable with the SQL query to be passed as a 'parameter for the SqlDataAdapter constructor Dim SelectCommand As String = "select * from Products where ProductID = @prod" MyConnection = New SqlConnection("server=localhost; uid=sa;pwd=;database=Sales") MyCommand = New SqlDataAdapter(SelectCommand, MyConnection) 'Creating a SQL parameter called @prod whose data type is VarChar with size 4 MyCommand.SelectCommand.Parameters.Add(New SqlParameter ("@prod", SqlDbType.NVarChar, 4)) 'Setting the SQL parameter @prod with the value of the text box displaying Product ID MyCommand.SelectCommand.Parameters("@prod").Value = Product_ID.Text DS = New DataSet() MyCommand.Fill(DS, "Products") MyDataGrid.DataSource = DS.Tables("Products").DefaultView MyDataGrid.DataBind() After you've written this code, save the project and execute it to check the desired functionality.
EAN13 Creation In Visual Studio .NET
Using Barcode creator for .NET Control to generate, create GS1 - 13 image in VS .NET applications.
Adding data You'll implement the functionality to add data in the Customer form shown earlier in Figure 12-3. The form should enable a user to add the customer registration information upon clicking the Add button. To implement this functionality, add the following code at the top of the code behind file of the Customer form: Imports System.Data Imports System.Data.SqlClient Tip As a good programming practice, the objects that are shared across the form are declared globally. Also, the code that implements data binding to the DataGrid control has been segregated in a separate procedure, which can be called whenever required. In the Declaration section of the form class, declare the object of the SqlConnection class as follows: Dim MyConnection As SqlConnection Next, create a procedure called BindGrid to bind data from the Customers table to the DataGrid control. To do so, write the following code in the form class: Sub BindGrid() Dim MyCommand As SqlDataAdapter = New SqlDataAdapter("select * from Customers", MyConnection) Dim DS As DataSet = New DataSet() MyCommand.Fill(DS, "Customers") MyDataGrid.DataSource = DS.Tables("Customers").DefaultView MyDataGrid.DataBind() End Sub Then, in the Click event of the Add button, write the following code: Dim DS As DataSet Dim MyCommand As SqlCommand 'Checking for the customer details. If the values are not entered, an 'error is displayed If Cust_ID.Text = "" Or Cust_Name.Text = "" or Cust_Address. Text="" or Cust_City="" or Cust_State="" Then lblMessage.Text = "Null values not allowed in these fields " BindGrid() End If 'Defining the SQL query for inserting data into the Customers table Dim InsertCmd As String = "insert into Customers values (@CID, @Cname,
Paint Code 9/3 In Visual Studio .NET
Using Barcode creation for Visual Studio .NET Control to generate, create ANSI/AIM Code 93 image in Visual Studio .NET applications.
@Caddress,@Ccity,@Cstate,@Czip)" 'Passing the SQL query in the SqlCommand object MyCommand = New SqlCommand(InsertCmd, MyConnection) 'Adding the SQL parameters and setting their values MyCommand.Parameters.Add(New SqlParameter("@CId", SqlDbType.NVarChar, 4)) MyCommand.Parameters("@CId").Value = Cust_ID.Text MyCommand.Parameters.Add(New SqlParameter("@Cname", SqlDbType.NVarChar, 20)) MyCommand.Parameters("@Cname").Value = Cust_Name.Text MyCommand.Parameters.Add(New SqlParameter("@Caddress", SqlDbType.NVarChar, 20)) MyCommand.Parameters("@Caddress").Value = Cust_Address.Text MyCommand.Parameters.Add(New SqlParameter("@Ccity", SqlDbType.NVarChar, 20)) MyCommand.Parameters("@Ccity").Value = Cust_City.Text MyCommand.Parameters.Add(New SqlParameter("@Cstate", SqlDbType.NVarChar, 20)) MyCommand.Parameters("@Cstate").Value = Cust_State.Text MyCommand.Parameters.Add(New SqlParameter("@Czip", SqlDbType.NVarChar, 20)) MyCommand.Parameters("@Czip").Value = Cust_Zip.Text 'Opening the connection MyCommand.Connection.Open() 'Executing the command MyCommand.ExecuteNonQuery() lblMessage.Text = "Record Added successfully" 'Closing the connection MyCommand.Connection.Close() 'calling the BindGrid method to reflect the added record in
Create Bar Code In Java
Using Barcode drawer for Java Control to generate, create barcode image in Java applications.
the DataGrid control BindGrid() When you run this application, you'll notice that the customer details are reflected in the DataGrid control after you enter the data in the respective text boxes and click the Add button. Modifying and deleting data The DataGrid control enables users to modify and delete records. To allow rows to be edited, the EditItemIndex property of the DataGrid control is used. By default, this property is set to -1, indicating that no rows are editable. The DataGrid control has a property called Columns that you can use to add buttons to allow user interaction with individual data rows. To add a button column, follow these steps: 1. Open the Property Window of the DataGrid control. 2. Click the ellipsis in the Columns property to open the Properties dialog box, as shown in Figure 12-4.
Barcode Recognizer In Java
Using Barcode scanner for Java Control to read, scan read, scan image in Java applications.
Figure 12-4: The Properties dialog box 3. In the left pane, click Columns. 4. In the right pane, under the Available Columns list, under Button Columns, select Edit, Update, Cancel and click the > button to add this button column to the control. 5. Click OK to close the dialog box. The DataGrid control can have three types of button columns, described as follows: The Select button column renders a Select link button used to access a specific row. The Edit, Update, Cancel button column renders three link buttons: Edit, Update, and Cancel. The Edit button is used to display the row in Edit mode. After the row switches to Edit mode, the column displays Update and Cancel buttons, which are used to update or cancel the changes made to the row. The Delete button column renders a Delete button that enables users to delete a specific row. To add the update functionality, add the Edit, Update, Cancel button column to your DataGrid control. When the Edit button is clicked, the EditCommand method of the DataGrid control is called. The UpdateCommand method is called when the Update button is clicked. And, when the Cancel button is clicked, the CancelCommand method
Draw DataMatrix In Java
Using Barcode creator for Java Control to generate, create ECC200 image in Java applications.
is called. Therefore, you need to write appropriate code in these methods to implement the desired functionality. In the EditCommand method of the DataGrid control, set the EditItemIndex property as follows: Public Sub MyDataGrid_EditCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles MyDataGrid.EditCommand 'Setting the EditItemIndex property of the DataGrid control to indicate the row to be edited MyDataGrid.EditItemIndex = e.Item.ItemIndex End Sub In this code: The EditCommand method takes two arguments: o source: Represents the object that generates the event. In this case, the source is the DataGrid control. o e: Represents the object of the DataGridCommandeventArgs class. This argument represents the event information of the source. Item indicates the item that generated the event. In this case, it is the DataGrid control. ItemIndex represents the row number for the item. After you've written this code, you need to use the following SQL statement as a String variable in the UpdateCommand method to modify the customer address based on a customer ID: Dim UpdateCmd As String = "Update Customers Set Address = @Address Where CustomerID = @CID" MyCommand = New SqlCommand(UpdateCmd, MyConnection) Now, let us discuss how we can implement data deletion in a SQL table. The first step is to add a Delete button column to the DataGrid control. Then, in the DeleteCommand method of the DataGrid control, add the code to delete a customer record. The following SQL statement needs to be used as a String variable to delete a customer record based on a customer ID: 'Defining the SQL query to delete a record from Customers table Dim DeleteCmd As String = "Delete from Customers where CustomerID = @CID" MyCommand = New SqlCommand(DeleteCmd, MyConnection) After understanding how to update and delete data in a SQL Server database, let us now see how to use stored procedures through your Web applications. Using stored procedures As mentioned earlier, stored procedures perform database operations more efficiently than the ad hoc SQL queries, because stored procedures are stored on the SQL Server.
Make EAN13 In VB.NET
Using Barcode creator for .NET Control to generate, create EAN 13 image in .NET applications.
You simply need to write the procedure's name and the procedure parameters, if any, to execute the stored procedure. When using stored procedure, the traffic is less as compared to passing the complete set of SQL queries to the server. Therefore, the performance is greatly improved. If a stored procedure already exists on a SQL Server, use the following syntax to create the SqlDataAdapter object: MyCommand = New SqlDataAdapter("Procedure_Name", MyConnection) MyCommand.SelectCommand.CommandType = CommandType.StoredProcedure In this syntax: MyCommand is the object of the SqlDataAdapter class. MyConnection is the object of the SqlConnection class. Procedure_Name represents the name of the procedure to be called. The second statement specifies that the command passed in statement1 is a stored procedure.
Code 128C Creator In Java
Using Barcode generation for Java Control to generate, create Code 128A image in Java applications.
Stored procedures can also take parameters that need to be passed while executing them. Parameters make the stored procedures more flexible because they return results based on user input. For example, you can create a stored procedure that takes a product name as a parameter and displays the product details for the specified product. To use stored procedures that take parameters, use the following syntax: MyCommand = New SqlDataAdapter("Procedure_Name", MyConnection) MyCommand.SelectCommand.CommandType = CommandType.StoredProcedure 'Adding a SQL parameter with SQL data type MyCommand.SelectCommand.Parameters.Add(New SqlParameter("@Parameter_name", SqlDbType.datatype, size)) 'Setting the value of the SQL parameter MyCommand.SelectCommand.Parameters("@Parameter_name").Value = TextBox1.Text In the last statement, the value of the parameter is initialized. Here, the value is initialized by a value entered in a text box at run time. Before you can use a stored procedure in your Web application, create a procedure named "DisplayCustomer." The code for the same is given as follows: Create Procedure DisplayCustomer (@CustID Varchar(4)) As Select * from Customers Where CustomerID=@CustID Return Next, you'll extend the functionality of the Customer form shown in Figure 12-3.
Printing UPC-A Supplement 2 In .NET
Using Barcode creator for ASP.NET Control to generate, create UPC Symbol image in ASP.NET applications.
Add a button with ID and text as "Query." In the Click event of this button, write the following code: Dim DS As DataSet Dim MyConnection As SqlConnection Dim MyCommand As SqlDataAdapter MyConnection = New SqlConnection("server=localhost;uid=sa;pwd=;database=Pubs") 'Calling the DisplayCustomers stored procedure MyCommand = New SqlDataAdapter("DisplayCustomers", MyConnection) MyCommand.SelectCommand.CommandType = CommandType.StoredProcedure 'Adding the SQL parameter MyCommand.SelectCommand.Parameters.Add(New SqlParameter("@CustID", SqlDbType.NVarChar, 4)) 'Specifying the parameter value MyCommand.SelectCommand.Parameters("@CustID").Value = Customer_ID.Text DS = New DataSet() MyCommand.Fill(DS, "Customers") MyDataGrid.DataSource = DS.Tables("Customers").DefaultView MyDataGrid.DataBind() When you run the application, you can test the code for its functionality. To do so, enter a customer ID in the Customer ID text box and click the Query button. The DataGrid control now displays only one record with the specified customer ID.
Reading Code 39 Extended In .NET
Using Barcode reader for .NET Control to read, scan read, scan image in Visual Studio .NET applications.
Using ADO Extensions (ADOX)
Painting Bar Code In Java
Using Barcode drawer for Java Control to generate, create bar code image in Java applications.
Data is stored and maintained in different data sources. Some data source applications include MS-Access, SQL Server, Oracle, and Sybase. Each data source uses its own native syntax. Therefore, when you need to manage data stored in data sources from your applications, you would prefer to use standard objects and syntaxes irrespective of the data sources. It is inconvenient to use different objects, methods, and syntaxes to manage different data sources. ADOX provides a set of standard objects that you can use to manage data stored in different data sources. ActiveX Data Objects Extensions (ADOX) is an extension of the ADO objects and programming model that allows creation, modification, and manipulation of schema objects, such as databases, tables, and columns. ADOX also includes security objects that enable you to maintain users and groups that access the schema objects. ADOX
security objects can be used to grant and revoke permissions on objects that are accessed by different users and groups. ADOX is part of the Microsoft Data Access Components (MDAC) SDK. When you install Visual Studio .NET, the Windows Component update installs MDAC 2.7. You can visit the http://www.microsoft.com/data/download.htm site to get the latest release of MDAC SDK. Standard ADOX objects ADOX objects are a set of standard objects that are used to create and manipulate data stored in different data sources irrespective of their native syntaxes. Table 12-4 describes the ADOX objects. Table 12-4: ADOX objects Object Catalog Description Represents the collections that describe the schema catalog of a data source. Represents a table stored in a database. This object includes columns, indexes, and keys. Represents a column that might be from a table, index, or key. Represents a table index. Represents a key field: primary, foreign, or unique. Represents a view, which is a set of filtered records from a table. Represents a stored procedure in