The first thing you need to do when accessing data, of course is to create a connection to the database. For this entire tutorial, we’ll use SQL Server for the database, but it can be done with most OleDB databases also.
Naturally, we’ll use/import the necessary namespaces (Sytem.Data and System.Data.SQLClient). In VB, we’ll use the ‘Imports’ statement, and in C#, we’ll use the ‘using’ statement.
VB: Dim Conn as new SqlConnection ("data source=localhost uid=yourUID; & _ "pwd= YourPWD;database=Northwind")
C#: SqlConnection Conn = new SqlConnection ("data source=localhost;uid= yourUID; pwd= YourPWD; database=Northwind");
There is no explicit opening or closing of the connection to the database when using a DataSet/DataAdapter scenario.
The next thing you would need to do is to create a DataSet. You can pass any name you’d like, in the constructor. In this case, we’ll use ‘Employees’ since, for this example, we’ll be querying against the Employees table.
VB: Dim ds as new DataSet(“Employees”)
C#: DataSet ds = new DataSet ("Employees");
Next, we’ll create a Command, in order to query the database. Here, the best thing to do is to use a Stored Procedure, but it can be done with parameterized sql also. For general display purposes, we’ll use an inline SQL statement, though that’s not recommended.
VB: Dim cmd as New SQLCommand("Select * from Employees", Conn)
C#: SqlCommand cmd = new SqlCommand ("Select * from employee", Conn);
(We could just skip the Command part right here, but I prefer to put it in as a general practice, just in case I need any parameters for the Command.)
When using a Stored Procedure, we’d also add a line to show the CommandType as a Stored Procedure too:
Now, we have a connection, we have a dataset and command object, but we really don’t have anything yet, because we have no DataAdapter. We need to create a DataAdapter, which would refer to the connection already created, along with the Command Object. The DataAdapter is what is used to directly interact with the DataBase. So, here we create the DataAdapter, assign the Command object to the SelectCommand of the DataAdapter, and we use the ‘Fill’ method of the DataAdaptor, assigning the contents of the DataSet to it:
VB: Dim da as New SqlDataAdapter da.SelectCommand=cmd da.Fill(ds)
C#: SqlDataAdapter da=new SqlDataAdapter(); da.SelectCommand=cmd; da.Fill(ds);
Of course, the best thing would be to include this as a method in a class, but that’s not the subject of this tutorial. We’ll address that later, at another time.
All that’s left now (in the display page), is to assign the dataset as the datasource for a control (like a Gridview control) and perform the DataBind method on the control.
All Things DotNet Discussed – Winforms/ASP.Net/SharePoint/WPF