Introduction
<o:p>
My previous article explains about the step by step dynamic creation of a SQL Server database, tables and stored procedure using Visual Studio codename Whidbey and VB.NET. I hope that you got a good start up in dynamic creation of SQL Server database and tables. This article explains how we can display data using Dataset and Data Grid control after the database is created.<o:p>
<o:p>
About “DataTable” and “DataSet” Object
A DataSet is a major component of ADO.NET architecture. It is a set of data grouped together, which is an in-memory cache of data retrieved from a data source. A DataSet consists of collection DataTable Objects. A DataTable represents one table of in-memory data. The name of the DataTable is conditionally case-sensitive only if you set the public property of “CaseSensitive”, which indicates whether string comparisons within the table are case-sensitive. For example, if you named a data table as “MyTable” and if you defined the table as “Mytable”, it won’t look the table because DataTable name is case sensitive. “DataSet” and “DataView” are the two data objects that use the DataTable<o:p>
Some of the public properties of DataTable properties are shown below:<o:p>
|
Properties<o:p> |
Description<o:p> |
|
CaseSensitive<o:p> |
This property returns a Boolean value which checks the table name is case-sensitive. It returns true if the comparison is case-sensitive; otherwise, false. For example, if you named a data table as “MyTable” and if you defined the table as “Mytable”, it won’t look the table. The CaseSensitive property affects string comparisons in sorting, searching, and filtering.<o:p> |
|
ChildRelations<o:p> |
Simply it is the relationship between two tables. It is just like database relationship keys such as primary key, secondary key etc., which will relate two tables using a key field.<o:p> |
|
Columns<o:p> |
Data base fields or tuples<o:p> |
|
Constraints<o:p> |
Constraints maintains for relationship between tables using keys such as primary key, index etc.<o:p> |
|
DataSet<o:p> |
Retrieves a set of data or records based on your table name<o:p> |
|
DefaultView<o:p> |
Customized view of the table such as filtered view, or a cursor position<o:p> |
|
HasErrors<o:p> |
If any row has errors, display errors<o:p> |
|
Locale<o:p> |
Local information to compare the strings<o:p> |
|
MinimumCapacity<o:p> |
Initial size of the table<o:p> |
|
ParentRelations<o:p> |
Any relationship occurs with parent table<o:p> |
|
PrimaryKey<o:p> |
A key used for unique identification of a row<o:p> |
|
Rows<o:p> |
Number of rows as collections<o:p> |
|
TableName<o:p> |
Name of the database table<o:p> |
Some of the public events of a DataTable are shown below:<o:p>
|
Events<o:p> |
Description<o:p> |
|
AcceptChanges<o:p> |
Commits the transaction<o:p> |
|
BeginInit<o:p> |
Initialization of DataTable<o:p> |
|
BeginLoadData<o:p> |
Turns off notifications, index maintenance, and constraints<o:p> |
|
Clear<o:p> |
Clears all the records from DataTable<o:p> |
|
Clone<o:p> |
Copy the structure to another table (Including constraints, key fields, schemas etc.)<o:p> |
|
Compute<o:p> |
Filter data based on expression<o:p> |
|
Copy<o:p> |
Copies the database structure and data (not like Clone)<o:p> |
|
Dispose<o:p> |
Release from memory (unload)<o:p> |
|
EndInit<o:p> |
Fires during initialization ends<o:p> |
|
EndLoadData<o:p> |
Fires during load data from DataTable ends<o:p> |
|
GetErrors<o:p> |
Gets an array of DataRow objects that contain errors.<o:p> |
|
ImportRow<o:p> |
Importing records<o:p> |
|
LoadDataRow<o:p> |
Updates the rows based on the condition. If no matching records found, it will insert the records<o:p> |
|
NewRow<o:p> |
Creates a new row in DataTable<o:p> |
|
RejectChanges<o:p> |
Roll back the changes <o:p> |
|
Reset<o:p> |
Reset the DataTable in original position<o:p> |
|
Select<o:p> |
Gets an array of data records<o:p> |
Some of the events associated with data tables are ColumnChanged, ColumnChanging, RowChanged, RowChanging, RowDeleted, RowDeleting etc.<o:p>
Note: “System.Data” namespace is used for include into your project, which consists mostly of the classes that constitute the ADO.NET architecture. The assembly name used for creating the DataSet object is System.Data in System.Data.Dll file.<o:p>
<o:p>
<o:p>About “SQLDataAdapter” Class
<o:p>
SQLDataAdapter class is used to retrieve and update the data base records in a DataSet. It is a bridge between SQL Server and DataSet <o:p>
There are two methods used for add or update records. They are <o:p>
1. Add method which will fill or refresh the rows or records in a DataSet
2. Update method used for updating the records in a DataSet. Please note that this will calls the respective SQL statements such as INSERT, UPDATE, or DELETE based on the SqlAdapter state.
The following lines of code helps you to understand how the SqlDataAdapter is responsible for using a SqlCommand object to fill a DataSet.<o:p>
' fill a DataSet.<o:p>
' A SqlCommand object is used to execute sql statements, which we already discussed the Article 1<o:p>
Dim Mycmd As New SqlCommand(“SELECT * FROM dbo.Customer”, myConnection)<o:p>
Dim mySqlDataAdapter As New SqlDataAdapter(Mycmd)<o:p>
Dim mydsCustomer As New DataSet()<o:p>
<o:p>
mySqlDataAdapter.Fill(mydsCustomer, "Customer")<o:p>
<o:p>
About “DataGrid” Control
<o:p>
A DataGrid control fills the rows from a data source in a table. It is used to retrieve and update the data base records using DataGrid control. The DataGrid control supports selection, editing, deleting, paging, and sorting.<o:p>
Different column types in a DataGrid determine the behavior of the columns in the control. Some of the column types are BoundColumn, ButtonColumn, EditCommandColumn, HyperLinkColumn and TemplateColumn etc.<o:p>
<o:p>
Note: “System.Web.UI.WebControls” namespace is used for inlcude this object into your. The assembly name used for creating the DataGrid control is System.Web in System.Web.Dll file.<o:p>
<o:p>
How to use “DataGrid” Control<o:p>
A DataGrid control allows you to select, sort and edit the items from a data source in a table. <o:p>
1. Bind a DataGrid to the DataSet
<o:p>
First, you need to bind the DataSet into a DataGrid, then set the visible property to true. If you don’t set the Visible to true, the DataGrid will still appear, but the scroll bar will be missing.<o:p>
' Set the DataGrid caption, bind it to the DataSet, and then make it<o:p>
' Visible <o:p>
mydgCustomer.CaptionText = "Customer"<o:p>
' Notice here that instead of using the DataSet table name, <o:p>
' "customers", the alternate syntax of table index is used.<o:p>
mydgCustomer.DataSource = mySqlDataAdapter.Tables(0)<o:p>
mydgCustomer.Visible = True<o:p>
Notice that the syntax mySqlDataAdapter.Tables(0), you can also use the alternate syntax using the DataSet table name “Customer”
<o:p>
<o:p>
2. Custom formatting a DataGrid
<o:p>
Table style object allows you to custom formatting to the DataGrid object. The object name for custom formatting a DataGrid object is called “DataGridTableStyle” object. It represents the table drawn by the System.Windows.Forms.DataGrid control at run time. It is a class file that represents to apply custom formatting to drawn the grid only.<o:p>
The following is the list of DataGridTableStyle properties that can be set to override System.Windows.Forms.DataGrid control properties. That way it allows your own custom formatting.<o:p>
AllowSorting property allows you to set the sorting based on the user clicks on each column header, which will sort either ascending or descending order. When the AllowSorting property is set to true, a triangle appears in each column header indicating the direction of the sort. The user can click on any column header to sort the grid by that column. Clicking the column a second time changes the direction of the sort. It returns true if sorting is allowed, otherwise, it returns false value. The default value of this property is true.<o:p>
AlternatingBackColor allows you to set the alternative rows color (or Gets or sets the background color of odd-numbered rows of the grid).<o:p>
mydgCustomer.AlternatingBackColor = System.Drawing.Color.Red
BackColor property sets the Background color of the object<o:p>
ColumnHeadersVisible property allows you to hide or visible the column headers in a DataGrid control on your form.<o:p>
ForeColor allows you set the foreground color of your DataGrid control<o:p>
GridLineColor allows you set the DataGrid line color<o:p>
GridLineStyle allows you to set the style of DataGrid<o:p>
mydgCustomer.GridLineStyle = <o:p>
System.Windows.Forms.DataGridLineStyle.None<o:p>
mydgCustomer.GridLineStyle = <o:p>
System.Windows.Forms.DataGridLineStyle.Solid<o:p>
HeaderBackColor allows you to set or get the background color of the headers<o:p>
HeaderFont allows you to set or get the header font on the DataGrid control<o:p>
HeaderForeColor sets the foreground color of the column headers<o:p>
LinkColor allows you set the color of a web link text<o:p>
PreferredColumnWidth allows you to set the DataGrid column width<o:p>
PreferredRowHeight allows you set the DataGrid row height<o:p>
ReadOnly property allows you to set the DataGrid to read only property. No editing is possible (update, insert or delete).<o:p>
RowHeadersVisible property allows you to get or set the visible properties of row headers on the DataGrid control.<o:p>
<o:p> <o:p>
Steps to create to display data using DataSet and DataGrid control:
<o:p>
You understand that the basic functionality of DataTable and DataSet objects, DataAdapter classes, and DataGrid control. Next is going to explain step by step creation of the usage of these objects or classes or controls into your .NET applications.<o:p>
<o:p>
1. Creating and Opening the DataBase Connection, Database Objects
If you want to learn more on to create the databases and tables, look on my previous article, which describes how to create and open a database connection using WhidBey or .NET environment. Also, the previous article explains how to open a SQL database connection if we only installed the MSDE. For solving this issue, we used SQL error exception code in .NET Whidbey.<o:p>
2. Add a new DataGrid Control to your form
Add a new DataGrid control to your form, named it as mydgCustomer.<o:p>
3. Bind to a DataGrid for Display
After adding the DataGrid control on your form, next task is to populate data using DataAdapter. The following routine gets the customer information from the Mydatabase table puts it into a DataSet which is used to bind to a DataGrid for display. <o:p>
<o:p>
If IsNothing(mydgCustomer.DataSource) Then<o:p>
Dim strMySQL As String = _<o:p>
"USE MyDatabase " & vbCrLf & _<o:p>
"SELECT * " & _<o:p>
"FROM myStoredProcedure"<o:p>
<o:p>
Try<o:p>
' The SqlConnection class allows you to communicate with SQL Server and DataTable.<o:p>
Dim myConnection As New SqlConnection(myConnection)<o:p>
<o:p>
' A SqlCommand object is used to execute the SQL commands.<o:p>
Dim mycmd As New SqlCommand(strMySQL, myConnection)<o:p>
<o:p>
Dim mySqlDataAdapter As New SqlDataAdapter(mycmd)<o:p>
Dim mydsCustomer As New DataSet()<o:p>
<o:p>
' The SqlDataAdapter is responsible for using a SqlCommand object to <o:p>
' fill a DataSet.<o:p>
<o:p>
mySqlDataAdapter.Fill(mydsCustomer, "Customer")<o:p>
<o:p>
' Set the DataGrid caption, bind it to the DataSet, and then make it<o:p>
' Visible <o:p>
<o:p>
mydgCustomer.CaptionText = "Customer"<o:p>
<o:p>
' Notice here that instead of using the DataSet table name, <o:p>
' "Customers", the alternate syntax of table index is used.<o:p>
<o:p>
mydgCustomer.DataSource = mySqlDataAdapter.Tables(0)<o:p>
<o:p>
' Settings to the DataGrid Styles, which will call the procedure<o:p>
‘ setMyDataGridTableStyleProperties and the parameter<o:p>
‘ as DataGrid<o:p>
<o:p>
setMyDataGridTableStyleProperties(mydgCustomer)<o:p>
<o:p>
mydgCustomer.Visible = True<o:p>
<o:p>
<o:p>
Catch sqlExc As SqlException<o:p>
<o:p>
MessageBox.Show(sqlExc.ToString, "SQL Exception Error!", _<o:p>
MessageBoxButtons.OK, MessageBoxIcon.Error)<o:p>
<o:p>
End Try<o:p>
<o:p>
End If<o:p>
<o:p>
4. The Procedure "setMyDataGridTableStyleProperties" allows you to set the DataGrid Style properties and passing the DataGrid as a object parameter
The following procedure allows you to set the custom data style properties to dataGrid object.
<o:p>
Private Sub setMyDataGridTableStyleProperties (ByRef myDG as DataGrid)<o:p>
' Use a table style object to apply custom formatting <o:p>
‘ to the DataGrid.<o:p>
<o:p>
Dim mydgTableStyle As New DataGridTableStyle()<o:p>
Dim mygrdColStyle1, mygrdColStyle2, mygrdColStyle3, <o:p>
mygrdColStyle4, mygrdColStyle5 As New & _<o:p>
DataGridTextBoxColumn()<o:p>
<o:p>
With mydgTableStyle<o:p>
.AlternatingBackColor = Color.LightCoral<o:p>
.BackColor = Color.LawnGreen<o:p>
.ForeColor = Color.LightGray<o:p>
.GridLineColor = Color.LightGreenrodYellow<o:p>
.GridLineStyle = System.Windows.Forms.DataGridLineStyle.<o:p>
.HeaderBackColor = Color. LightGray<o:p>
.HeaderFont = New Font("Courier", 10.0!, FontStyle.Bold)<o:p>
.HeaderForeColor = Color. LawnGreen<o:p>
.LinkColor = Color.Teal<o:p>
<o:p>
' Do not forget to set the MappingName property. <o:p>
' Without this, the DataGridTableStyle properties<o:p>
' and any associated DataGridColumnStyle objects<o:p>
' will have no effect.<o:p>
<o:p>
.MappingName = "Customers"<o:p>
<o:p>
.SelectionBackColor = Color. LawnGreen<o:p>
.SelectionForeColor = Color. LightGray<o:p>
End With<o:p>
<o:p>
' Use column style objects to apply formatting specific<o:p>
‘ to each column of customer table.<o:p>
<o:p>
With mygrdColStyle1<o:p>
.HeaderText = "ID#"<o:p>
.MappingName = "CustomerID"<o:p>
.Width = 50<o:p>
End With<o:p>
<o:p>
With mygrdColStyle2<o:p>
.HeaderText = "Last Name"<o:p>
.MappingName = "NameLast"<o:p>
.Width = 140<o:p>
End With<o:p>
<o:p>
With mygrdColStyle3<o:p>
.HeaderText = "Address"<o:p>
.MappingName = "Address1"<o:p>
.Width = 180<o:p>
End With<o:p>
<o:p>
With mygrdColStyle4<o:p>
.HeaderText = "State"<o:p>
.MappingName = "State"<o:p>
.Width = 30<o:p>
End With<o:p>
<o:p>
With mygrdColStyle5<o:p>
.HeaderText = "Phone"<o:p>
.MappingName = "Phone"<o:p>
.Width = 70<o:p>
End With<o:p>
<o:p>
' Add the column style objects to the tables style's <o:p>
‘ column styles collection. If you fail to do this the column <o:p>
‘ styles will not apply.<o:p>
<o:p>
mydgTableStyle.GridColumnStyles.AddRange _<o:p>
(New DataGridColumnStyle() _<o:p>
{ mygrdColStyle1, mygrdColStyle2, <o:p>
mygrdColStyle3, mygrdColStyle4, mygrdColStyle5})<o:p>
<o:p>
' Add the table style object to the DataGrid's table styles <o:p>
' collection. Again, failure to add the style to the collection <o:p>
' will cause the style to not take effect.<o:p>
<o:p>
myDG.TableStyles.Add(mydgTableStyle)<o:p>
<o:p>
End Sub<o:p>
<o:p>
Requirements
Microsoft Visual Studio.Whidbey Ver 8.0 Or
Microsoft Visual Studio.NET Professional or greater.
Windows 2000 or Windows XP.
Summary:
From this article, it found the description of DataSet, DataTable objects, DataAdapter classes. Also, you got a brief idea of using DataGrid using fill method from DataAdapter class. It also explained how to use the styles in DataGrid.
If you need any suggestions or help, let me contact at benoyraj@yahoo.com<o:p>
Submit an article or tip