Introduction
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 DataGrid
controls after the database is created.
About �DataTable� and �DataSet� Objects
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 a 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 DataTable
as �MyTable
� and if you defined the table as �Mytable
�, it won�t look for the table because DataTable
name is case sensitive. DataSet
and DataView
are the two data objects that use the DataTable
.
Some of the public properties of DataTable
are shown below:
Properties Description
CaseSensitive
This property returns a Boolean value which checks if the table name is case-sensitive. It returns True
if the comparison is case-sensitive; otherwise, False
. For example, if you named a DataTable
as �MyTable
� and if you defined the table as �Mytable
�, it won�t look for the table.
The CaseSensitive
property affects string comparisons in sorting, searching, and filtering.
ChildRelations
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.
Columns
Database fields or tuples.
Constraints
Constraints maintained for relationship between tables using keys such as primary key, index etc.
DataSet
Retrieves a set of data or records based on your table name.
DefaultView
Customized view of the table such as filtered view, or a cursor position.
HasErrors
If any row has errors, display errors.
Locale
Local information to compare the strings.
MinimumCapacity
Initial size of the table.
ParentRelations
Any relationship occuring with parent table.
PrimaryKey
A key used for unique identification of a row.
Rows
Number of rows as collections.
TableName
Name of the database table.
Some of the public methods of a DataTable
are shown below:
AcceptChanges
Commits a transaction.
BeginInit
Initialization of DataTable
.
BeginLoadData
Turns off notifications, index maintenance, and constraints.
Clear
Clears all the records from DataTable
.
Clone
Copy the structure to another table (including constraints, key fields, schemas etc.).
Compute
Filter data based on expression.
Copy
Copies the database structure and data (not like Clone
).
Dispose
Release from memory (unload).
EndInit
Fires when initialization ends.
EndLoadData
Fires when load data from DataTable
ends.
GetErrors
Gets an array of DataRow
objects that contain errors.
ImportRow
Importing records.
LoadDataRow
Updates the rows based on the condition. If no matching records found, it will insert the records.
NewRow
Creates a new row in DataTable
.
RejectChanges
Rolls back the changes.
Reset
Reset the DataTable
in original position.
Select
Gets an array of data records.
Some of the events associated with DataTable
are ColumnChanged
, ColumnChanging
, RowChanged
, RowChanging
, RowDeleted
, RowDeleting
etc.
Note: System.Data
namespace is used for including 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.
About �SQLDataAdapter� Class
SQLDataAdapter
class is used to retrieve and update the database records in a DataSet
. It is a bridge between SQL Server and DataSet
.
There are two methods used for adding or updating records. They are:
Add
method which will fill or refresh the rows or records in a DataSet
.
Update
method used for updating the records in a DataSet
. Please note that this will call the respective SQL statements such as INSERT
, UPDATE
, or DELETE
based on the SqlAdapter
state.
The following lines of code will help you to understand how the SqlDataAdapter
is responsible for using a SqlCommand
object to fill a DataSet
.
Dim Mycmd As New SqlCommand(�SELECT * FROM dbo.Customer�, myConnection)
Dim mySqlDataAdapter As New SqlDataAdapter(Mycmd)
Dim mydsCustomer As New DataSet()
mySqlDataAdapter.Fill(mydsCustomer, "Customer")
About DataGrid Control
A DataGrid
control fills the rows from a data source in a table. It is used to retrieve and update the database records. The DataGrid
control supports selection, editing, deleting, paging, and sorting.
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.
Note: System.Web.UI.WebControls
namespace is used for including this object into your application. The assembly name used for creating the DataGrid
control is System.Web
in System.Web.dll file.
Steps to use the DataGrid control on your .NET form
A DataGrid
control allows you to select, sort and edit the items from a data source in a table.
- Bind a
DataGrid
to the DataSet
.
First, you need to bind the DataSet
into a DataGrid
, then set the Visible
property to True
. If you don�t set the Visible
property to True
, the DataGrid
will still appear, but the scroll bar will be missing.
mydgCustomer.CaptionText = "Customer"
mydgCustomer.DataSource = mySqlDataAdapter.Tables(0)
mydgCustomer.Visible = True
Notice the syntax mySqlDataAdapter.Tables(0)
, you can also use the alternate syntax using the DataSet
table name �Customer�.
- Custom formatting a
DataGrid
.
Table style object allows you to custom format the DataGrid
object. The object name for custom formatting a DataGrid
object is called DataGridTableStyle
. It represents the table drawn by the System.Windows.Forms.DataGrid
control at run time. It is a class file that represents applying custom formatting to draw the grid.
The following is the list of DataGridTableStyle
properties that can be set to override System.Windows.Forms.DataGrid
control properties. This way, it allows your own custom formatting.
AllowSorting
property allows you to set the sorting based on the user clicks on each column header, which will sort either in 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
. The default value of this property is True
.
AlternatingBackColor
allows you to set the alternate row colors (or gets or sets the background color of odd-numbered rows of the grid). mydgCustomer.AlternatingBackColor = System.Drawing.Color.Red
BackColor
property sets the background color of the object.
ColumnHeadersVisible
property allows you to hide or make visible the column headers in a DataGrid
control on your form.
ForeColor
allows you set the foreground color of your DataGrid
control.
GridLineColor
allows you set the DataGrid
line color.
GridLineStyle
allows you to set the style of DataGrid
. mydgCustomer.GridLineStyle =
System.Windows.Forms.DataGridLineStyle.None
mydgCustomer.GridLineStyle =
System.Windows.Forms.DataGridLineStyle.Solid
HeaderBackColor
allows you to set or get the background color of the headers.
HeaderFont
allows you to set or get the header font on the DataGrid
control.
HeaderForeColor
sets the foreground color of the column headers.
LinkColor
allows you to set the color of a web link text.
PreferredColumnWidth
allows you to set the DataGrid
column width.
PreferredRowHeight
allows you to set the DataGrid
row height.
ReadOnly
property allows you to set the DataGrid
to read only. No editing is possible (update, insert or delete).
RowHeadersVisible
property allows you to get or set the visible properties of row headers on the DataGrid
control.
Steps to display data using DataSet and DataGrid controls:
You understand now the basic functionality of DataTable
and DataSet
objects, DataAdapter
classes, and DataGrid
control. Next, I am going to explain the step by step creation of the usage of these objects or classes or controls in your .NET applications.
- Creating and opening the database connection, database objects.
If you want to learn more on creating the databases and tables, look at 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.
- Add the
DataGrid
control to your form.
Add a new DataGrid
control to your form, name it as mydgCustomer
.
- 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.
If IsNothing(mydgCustomer.DataSource) Then
Dim strMySQL As String = _
"USE MyDatabase " & vbCrLf & _
"SELECT * " & _
"FROM myStoredProcedure"
Try
Dim myConnection As New SqlConnection(myConnection)
Dim mycmd As New SqlCommand(strMySQL, myConnection)
Dim mySqlDataAdapter As New SqlDataAdapter(mycmd)
Dim mydsCustomer As New DataSet()
mySqlDataAdapter.Fill(mydsCustomer, "Customer")
mydgCustomer.CaptionText = "Customer"
mydgCustomer.DataSource = mySqlDataAdapter.Tables(0)
setMyDataGridTableStyleProperties(mydgCustomer)
mydgCustomer.Visible = True
Catch sqlExc As SqlException
MessageBox.Show(sqlExc.ToString, "SQL Exception Error!", _
MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End If
- Update the
DataGrid
style properties.
The procedure setMyDataGridTableStyleProperties
allows you to set the DataGrid
style properties, passing the DataGrid
as an object parameter.
First, we need to create the DataGrid
style using DataGridTableStyle
object, and can apply the style properties such as AlternatingBackColor
, BackColor
, ForeColor
, GridLineColor
, HeaderrBackColor
, HeaderFont
, HeaderForeColor
, LinkColor
, Mapping
, etc. AddRange
method functionality allows you to set the styles in a particular range of your DataGrid
component.
Private Sub setMyDataGridTableStyleProperties (ByRef myDG as DataGrid)
Dim mydgTableStyle As New DataGridTableStyle()
Dim mygrdColStyle1, mygrdColStyle2, mygrdColStyle3, _
mygrdColStyle4, mygrdColStyle5 As New & _
DataGridTextBoxColumn()
With mydgTableStyle
.AlternatingBackColor = Color.LightCoral
.BackColor = Color.LawnGreen
.ForeColor = Color.LightGray
.GridLineColor = Color.LightGreenrodYellow
.GridLineStyle = System.Windows.Forms.DataGridLineStyle.
.HeaderBackColor = Color. LightGray
.HeaderFont = New Font("Courier", 10.0!, FontStyle.Bold)
.HeaderForeColor = Color. LawnGreen
.LinkColor = Color.Teal
.MappingName = "Customers"
.SelectionBackColor = Color. LawnGreen
.SelectionForeColor = Color. LightGray
End With
With mygrdColStyle1
.HeaderText = "ID#"
.MappingName = "CustomerID"
.Width = 50
End With
With mygrdColStyle2
.HeaderText = "Last Name"
.MappingName = "NameLast"
.Width = 140
End With
With mygrdColStyle3
.HeaderText = "Address"
.MappingName = "Address1"
.Width = 180
End With
With mygrdColStyle4
.HeaderText = "State"
.MappingName = "State"
.Width = 30
End With
With mygrdColStyle5
.HeaderText = "Phone"
.MappingName = "Phone"
.Width = 70
End With
mydgTableStyle.GridColumnStyles.AddRange _
(New DataGridColumnStyle() _
{ mygrdColStyle1, mygrdColStyle2, _
mygrdColStyle3, mygrdColStyle4, mygrdColStyle5})
myDG.TableStyles.Add(mydgTableStyle)
End Sub
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, we found the description of DataSet
, DataTable
objects, DataAdapter
classes. Also, you got a brief idea of using DataGrid
using Fill
method from the DataAdapter
class. It also explained how to use the styles in DataGrid
.
If you need any suggestions or help, contact me at benoyraj@yahoo.com.