Speed Up Your Data Set Design Using VS.Net XML Schema Designer

Here is a four-step process for working with SQL-stored procedure:Microsoft .Net framework uses XML and data set heavily, and Visual Studio provides built-in support for automating many numerous tasks involved in building web application by using data set and XML. The advantage of using XML schema based data set definition over un-typed data set created inside the code-behind file is that the data set definition and the UI codes are in separate files; therefore, the schema can be reused, which also eases code maintenance. To reuse the data set definition, simply reference it in another project. You can consolidate all data set definitions in one project, then reference them from other projects.In this article, I am going to talk about how to use the built-in data set design tools in Visual Studio to create data set, then use them in your web applications. It's a four-step process.Step 1. Define the data set XML schema in VS.Net To get started with creating a data set based on a certain SQL-stored procedure, right click on the current project in Visual Studio .Net's solution explorer, then go to File->Add New Item.Choose "Data Set" in the dialog box and give a descriptive name to the data set you will be creating, such as CustomersDataSet, EmployeesDataSet, OrdersDataSet, etc. The file extension will be .xsd. Another two files will be created: one is the code-behind file, if you create CustomersDataSet.xsd, then the code behind will be CustomersDataSet.cs; the other companion file has the .xsx extension, which is for the layout of components of the .xsd file. Here is a snapshot of the three files created in the solution explorer.For building application, you don't really have to touch these two auto-generated files.Step 2. Pick the stored procedure you needFrom the Server explorer, drag and drop a desired stored procedure or table into the design view. It doesn't have to be a stored procedure. Anything that is relational will be alright, so tables or views are fine as well. However, in enterprise applications, we usually use stored procedures.Once you drop the stored procedure in the design view, a GUI XML data set definition will be rendered for you. You can view the XML source code for this auto-generated data set from the XML view or by using the shortcut Ctrl+PgDown.The accompanying .cs file is of interest to us when we are building SqlDataAdapter for the real data set for web application. Take a closer look at the .cs file generated. You will see that there might be several classes associated with the main class. Step 3: Building the connection and adapterData set can be used anywhere you want an interface to a relational data source. Declare SqlDataAdapter and SqlConnection objects:SqlConnection myConnection = new SqlConnection(DataAccess.Constants.getConnString());SqlDataAdapter myAdapter = new SqlDataAdapter();//The CommandText is the stored procedure name you used to create the schema, so be sure they matchmyAdapter.SelectCommand.CommandText ="SelectAllCustomers "myAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;/*Add parameters as necessary. This stored procedure doesn't have a parameter, so we don't need one here. But ifyou need some, add it after the CommandText is set. */ Step 4: Retrieve and update data from the data setTo use the typed data set we created in step 1, simply add one statement like the one below (Note: You have to add a reference in your project's reference space)categoriesDataSet1 = new Common.CategoriesDataSet();myAdapter.Fill(categoriesDataSet1);Note: There is usually only one table in the data set, so don't specify another table name as you would otherwise. Therefore, in the code below, use Tables[0] to reference the single table in your schema.Based on the XML schema in step 1, we have one table in the data set we just created. After the data set is populated, we can use Asp.net server data bound controls, such as data list, data grid to present the data to the user. For example, if we have a data grid called dgrid_data, then simply: //attach the data source to the data grid controldgrid_data.datasource = categoriesDataSet1.Tables[0];//bind data to the controldgrid_data.databind(); Steps 3 and 4 can also be automated using the design view data component designer to some extent.