视图的调用方法与表的调用是一样的,由于视图是虚表,所以一般用在查询中。例如在C#中调用视图中的结果时通常会用到SqlDataAdapter类,然后将视图中的结果通过Fill方法去填充结果集(DataSet)。
首先创建一个用于显示查询结果的视图View_Try。然后建立一个数据操作类ClsView.cs,其中抱括连库方法ConDB与方法dsView(用于查询视图),当程序运行的时候,通过调用数据操作类(ClsView)实现对虚拟表(由t_People表和t_Class表组成)的查询操作,同时也实现了对查询结果的某一字段过滤与排序的操作。
程序代码如下。
(1)ConDB方法用于建立数据库的连接。
(2)dsView方法用于查询视图(View_Try)。
ConDB方法代码如下:
1 public SqlConnection ConDB() 2![](/Images/OutliningIndicators/ExpandedBlockStart.gif) { 3 con = new SqlConnection("server=.;uid=sa;pwd=;database=DB_ADONET"); 4 if (con.State == ConnectionState.Closed) 5![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 6 con.Open(); 7 } 8 return con; 9 } | dsView方法代码如下:
1 public DataSet dsView(string SQLWhere) 2![](/Images/OutliningIndicators/ExpandedBlockStart.gif) { 3 string SQL = "select * from View_Try"; 4 SQL += SQLWhere; 5 try 6![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 7 DataSet ds = new DataSet(); 8 da = new SqlDataAdapter(SQL, ConDB()); 9 da.Fill(ds); 10 return ds; 11 } 12 catch 13![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 14 return null; 15 } 16 } | 单击【查询】按钮,将会生成SQL语句,然后将SQL语句传递给dsView方法,实现信息查询。【查询】按钮的Click事件代码如下:
1 private void button1_Click(object sender, EventArgs e) 2![](/Images/OutliningIndicators/ExpandedBlockStart.gif) { 3 StringBuilder strSQLWhere = new StringBuilder(); 4 strSQLWhere.Append(" where "); 5 strSQLWhere.Append(" 编号like '%" + this.textBox1.Text.Trim().ToString() 6 + "%' 7 and"); 8 strSQLWhere.Append(" 班级like '%" + this.textBox2.Text.Trim().ToString() 9 + "%' 10 and"); 11 strSQLWhere.Append(" 人数like '%" + this.textBox3.Text.Trim().ToString() 12 + "%' 13 and"); 14 strSQLWhere.Append(" 姓名like '%" + this.textBox4.Text.Trim().ToString() 15 + "%' 16 and"); 17 strSQLWhere.Append(" 性别like '%" + this.textBox5.Text.Trim().ToString() 18 + "%'"); 19 ClsDB.ClsView CV = new OptDB.ClsDB.ClsView(); 20 this.dataGridView1.DataSource=CV.dsView( strSQLWhere.ToString()).Tables[0]; 21 22 } | ChangeRowFilter方法用于实现对数据源进行筛选,代码如下:
1 private void ChangeRowFilter(string str) 2![](/Images/OutliningIndicators/ExpandedBlockStart.gif) { 3 if (this.textBox6.Text.Trim().ToString() != "") 4![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 5 DataTable custDV = (DataTable)this.dataGridView1.DataSource; 6 if (custDV != null) 7![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 8 custDV.DefaultView.RowFilter = "人数" + str; 9 this.dataGridView1.DataSource = custDV; 10 } 11 else 12![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 13 MessageBox.Show("无操作数据"); 14 } 15 } 16 } | SortRow方法用于实现对数据源进行排序,代码如下:
![](/Images/OutliningIndicators/ContractedBlock.gif) Code 1 private void SortRow(string str) 2![](/Images/OutliningIndicators/ExpandedBlockStart.gif) { 3 DataTable custDV = (DataTable)this.dataGridView1.DataSource; 4 if (custDV != null) 5![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 6 7 custDV.DefaultView.Sort = " 编号"+str; 8 this.dataGridView1.DataSource = custDV; 9 } 10 else 11![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif) { 12 MessageBox.Show("无操作数据"); 13 } 14 15 } 16![](/Images/OutliningIndicators/None.gif) | 单击【过滤】按钮,调用ChangeRowFilter方法实现对数据源的过滤功能。【过滤】按钮的Click事件代码如下:
![](/Images/OutliningIndicators/ContractedBlock.gif) Code 1 private void button2_Click(object sender, EventArgs e) 2![](/Images/OutliningIndicators/ExpandedBlockStart.gif) { 3 this.ChangeRowFilter(this.textBox6.Text.Trim().ToString()); 4 } | 单击【排序】按钮,调用SortRow方法实现对数据源的排序功能。【排序】按钮的Click事件代码如下:
![](/Images/OutliningIndicators/ContractedBlock.gif) Code 1 private void button3_Click(object sender, EventArgs e) 2![](/Images/OutliningIndicators/ExpandedBlockStart.gif) { 3 string strState; 4 if (this.radioButton1.Checked) 5 strState = "asc"; 6 else 7 strState = "desc"; 8 SortRow(strState); 9 } 10![](/Images/OutliningIndicators/None.gif) | |