Usefull datatable functions

Public Shared Function SelectDistinct(ByVal SourceTable As DataTable, ByVal ParamArray FieldNames() As String) As DataTable
Dim lastValues() As Object
Dim newTable As DataTable

If FieldNames Is Nothing OrElse FieldNames.Length = 0 Then
Throw New ArgumentNullException(”FieldNames”)
End If

lastValues = New Object(FieldNames.Length - 1) {}
newTable = New DataTable

For Each field As String In FieldNames
newTable.Columns.Add(field, SourceTable.Columns(field).DataType)
Next

For Each Row As DataRow In SourceTable.Select(”", String.Join(”, “, FieldNames))
If Not fieldValuesAreEqual(lastValues, Row, FieldNames) Then
newTable.Rows.Add(createRowClone(Row, newTable.NewRow(), FieldNames))

setLastValues(lastValues, Row, FieldNames)
End If
Next

Return newTable
End Function
=====================
Private Shared Function fieldValuesAreEqual(ByVal lastValues() As Object, ByVal currentRow As DataRow, ByVal fieldNames() As String) As Boolean
Dim areEqual As Boolean = True

For i As Integer = 0 To fieldNames.Length - 1
If lastValues(i) Is Nothing OrElse Not lastValues(i).Equals(currentRow(fieldNames(i))) Then
areEqual = False
Exit For
End If
Next
Return areEqual
End Function
========================
Private Shared Function createRowClone(ByVal sourceRow As DataRow, ByVal newRow As DataRow, ByVal fieldNames() As String) As DataRow
For Each field As String In fieldNames
newRow(field) = sourceRow(field)
Next
Return newRow
End Function
====================
Private Shared Sub setLastValues(ByVal lastValues() As Object, ByVal sourceRow As DataRow, ByVal fieldNames() As String)
For i As Integer = 0 To fieldNames.Length - 1
lastValues(i) = sourceRow(fieldNames(i))
Next
End Sub

filling the textbox,dropdownlist from the database

1 create table teamtable (teamcode int, teamname varchar(10))
2 insert into teamtable values(1,'teamA')
3 insert into teamtable values(2,'teamB')
4 insert into teamtable values(3,'teamC')
5
6 create table teammemtable(teamcode int, memcode int, memname varchar(50), memage int, memgndr char(1))
7 insert into teammemtable values(1,1,'dhana',27,'M')
8 insert into teammemtable values(1,2,'balan',27,'M')
9 insert into teammemtable values(1,3,'deepa',24,'F')
10
11 insert into teammemtable values(2,1,'balaji',26,'M')
12 insert into teammemtable values(2,2,'thunaivan',35,'M')
13 insert into teammemtable values(2,3,'geetha',28,'F')
14
15 insert into teammemtable values(3,1,'jibin',21,'M')
16 insert into teammemtable values(3,2,'rajan',27,'M')
17 insert into teammemtable values(3,3,'uma',26,'F')



aspx page

1 <%@ Page Language="C#" AutoEventWireup="true" CodeFile="t-1208420.aspx.cs" Inherits="DropDownList_t_1208420" %>
2
3
4
5 6
7
8
9
10

11

12
13

14
15
16

17

18
19
20
21
22
25
29
30
31
34
37
38

23 24	26 27 28
32 33	35 36

39
40
41
42
43
44

45

46

47
48
49



CS file

1 using System;
2 using System.Data;
3 using System.Configuration;
4 using System.Collections;
5 using System.Web;
6 using System.Web.Security;
7 using System.Web.UI;
8 using System.Web.UI.WebControls;
9 using System.Web.UI.WebControls.WebParts;
10 using System.Web.UI.HtmlControls;
11 using System.Data.SqlClient;
12
13 public partial class DropDownList_t_1208420 : System.Web.UI.Page
14 {
15 protected void Page_Load(object sender, EventArgs e)
16 {
17 if (!Page.IsPostBack)
18 {
19 AssignDDLDataSource();
20 ddl1_SelectedIndexChanged(null, null);
21 }
22 }
23
24 private void AssignDDLDataSource()
25 {
26 SqlConnection con = CreateConnection();
27 if (con.State != ConnectionState.Open)
28 con.Open();
29 string sqlQry = "SELECT TEAMNAME DISPMEM, TEAMCODE VALMEM FROM TEAMTABLE";
30 SqlCommand cmd = new SqlCommand(sqlQry,con);
31 ddl1.DataSource = cmd.ExecuteReader();
32 ddl1.DataTextField = "DISPMEM";
33 ddl1.DataValueField = "VALMEM";
34 ddl1.DataBind();
35 }
36
37 private SqlConnection CreateConnection()
38 {
39 SqlConnection con = new SqlConnection("Data Source=yourservername;Initial Catalog=database;Persist Security Info=True;User ID=username;Password=password");
40 return con;
41 }
42
43 private void AssignTextBoxValues(string teamval,string memval)
44 {
45 SqlConnection con = CreateConnection();
46 if (con.State != ConnectionState.Open)
47 con.Open();
48
49 //AssignSubjectDDL(val);
50 string sqlQry = "Select MEMNAME,MEMAGE,MEMGNDR from TEAMMEMTABLE where TEAMCODE = '" + teamval +"' AND MEMCODE = '" + memval + "'";
51 SqlCommand cmd = new SqlCommand(sqlQry, con);
52 SqlDataReader rdr = cmd.ExecuteReader();
53 while (rdr.Read())
54 {
55 txtName.Text = rdr["MEMNAME"].ToString().Trim();
56 txtAge.Text = rdr["MEMAGE"].ToString().Trim();
57 if (rdr["MEMGNDR"].ToString().Trim() == "M")
58 {
59 rdoBtnMale.Checked = true;
60 rdoBtnFeMale.Checked = false;
61 }
62 else if (rdr["MEMGNDR"].ToString().Trim() == "F")
63 {
64 rdoBtnMale.Checked = false;
65 rdoBtnFeMale.Checked = true;
66 }
67 else
68 {
69 rdoBtnMale.Checked = false;
70 rdoBtnFeMale.Checked = false;
71 }
72 }
73 }
74
75 private void AssignSubjectDDL(string val)
76 {
77 SqlConnection con = CreateConnection();
78 if (con.State != ConnectionState.Open)
79 con.Open();
80 string sqlQry = "SELECT MEMNAME DISPMEM, MEMCODE VALMEM FROM TEAMMEMTABLE where TEAMCODE = '" + val + "'";
81 SqlCommand cmd1 = new SqlCommand(sqlQry, con);
82 ddl2.DataSource = cmd1.ExecuteReader();
83 ddl2.DataTextField = "DISPMEM";
84 ddl2.DataValueField = "VALMEM";
85 ddl2.DataBind();
86 ddl2_SelectedIndexChanged(null, null);
87
88 }
89 protected void ddl1_SelectedIndexChanged(object sender, EventArgs e)
90 {
91 AssignSubjectDDL(ddl1.SelectedValue);
92 }
93 protected void ddl2_SelectedIndexChanged(object sender, EventArgs e)
94 {
95 AssignTextBoxValues(ddl1.SelectedValue,ddl2.SelectedValue);
96 }
97 }