Tag Archives: DataView

How to create a DataView in C#


DataView is special table view which allows you to traverse through rows and columns respectively. C# DataView is an offline data holder as DataSet.

What you need is

  1. A Database connection object
  2. A Data Adapter which configures the data table using SQL query.
  3. And a dataset which holds the table information.

Create a data view object and initialize it with the dataset tables name as follows

SqlDataAdapter AccountRegistration = new SqlDataAdapter(“select * from accountregistration order by grpid”, Common.con);

DataSet AccountRegistrationDataset=new DataSet();
AccountRegistration.Fill(AccountRegistrationDataset);
DataView AccountRegistrationTableView = new DataView(AccountRegistrationDataset.Tables[0]);

Learn about more DatView operations

 

Ado.Net connection,adapter,dataset and dataview explained


I wrote this post for those who have no idea about C# database connection.

When starting with Visual Studio language like C#/VB.Net, you need to understand the concept of adapter, dataset and data view.

SQL connection

SQL connection class helps you to build the connection string.Visual studio can build connection string for you or  can create your in App config [Solution Explorer]

<add name=”MACCon” connectionString=”Data Source=(LocalDB)\MSSQLlocalDB;AttachDbFilename=e:\Developerm\c# Projects\MAcc_Prime\MAcc\MACCDATA.mdf;Integrated Security=True” />

Use SQL connection object to configure the connection as follows

con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings[“MACCon”].ConnectionString;
con.Open();

Now you’re ready to use database

Data Adapter

The data adapter is the bridge between database tables with the application. The Adapter class helps you to the configugure table. The Adapter connects to the database throw the connection object.

geTradp = new SqlDataAdapter(“select dr,cr from accounttransactions where entry=” + mcode + ” and acid=” + acid + ” and tdate>='” + sdate + “‘ and tdate<='” + edate + “‘”, Common.con);

Here “conn” is the SQL database connection object

Dataset and Data Views

DataSet is the local version of your database table, you can work with the dataset and its data even if the connection has closed. Data Set accompanying with command builder also simplifies operation like adding new data, updating /deleting data etc

Dataset can also be used as data source for controls like data grid view.

Data View is a special object which offers table view so that you can fetch columns, rows, filter, extract rows with Find, FindRow methods loops through recordsets with the help subclasses of data view like Data RowView.

Let’s begin with .Net programming

 

 

 

How to use dataView rowfilter for searching rows in C#.Net


You can simply use SQL query statements to retrieve desired data from database server with C# and ADO.Net. But there are plenty of ways to search your localized version of data which is stored in dataSet and dataView.

We already learn how to use Find and Findrow methods for searching data in Visual Studio App. This post will show how to use row filter property of  DataView for sorting data rows.

  1. First up all you need to create Data adaptor, dataSet, and dataView.
  2. In our example, I have a date field in the database table, so that I can filter for specific date ranges as follows
 Common.AccountReportDV.RowFilter = "invoicedate>='" + dateTimePicker1.Value.ToShortDateString() + "' and invoicedate<='" + dateTimePicker2.Value.ToShortDateString() + "'";

Here common is a public static class where kept the Database functions. The above statement will be looking for data between the two dates.

You have to use Common.AccountReportDV and not Common.AccountReportDV.Table.rows  for retrieving the result as follows.

foreach (DataRowView row in Common.AccountReportDV)
 { 

console.WriteLine(row["<column name here>"]);

 }

DataRowView is a special class which helps to access data rows in a DataView object.

How to fetch data rows from a data view in C#


Data View is a representation of data table which can be used to access rows in a table. In Database programming sometimes we need to search and extract/copy rows which meet some criteria/condition. C# and .Net framework allows you to do this using rich set of objects available in Visual Studio.

In our example we have a table Groups which is used to store various accounting groups. I want to extract those groups with pgid[Primary Group ID] as 0, this is made possible with Data View object’s FindRows method.

Common.GroupTableView.Sort = "pgid";
 dv.Sort = "pgid";
 DataRowView[] nodes = Common.GroupTableView.FindRows(0);

FindRows () return a collection of data rows, so that we used array of dataRowView to store the objects.

You can use Foreach loop to get access values as follows [ add a tree view control to your C# Project]

foreach (DataRowView node in nodes)
 {
 treeView1.Nodes[0].Nodes.Add(node["name"].ToString()); 
 }
 treeView1.ExpandAll();