Kennesaw State University Department of Computer Sciences

1. Kennesaw State UniversityDepartment of Computer Sciences Usa Rungratchakanon Data Set Merging CS 8628 Software Architecture Spring 2006

2. The objective of this project is to combine two datasets (merging dataset), one from xml file and another from database, and then user can modify the merging dataset and can choose to save the merge dataset either on the xml file or the database. Objective

3. The Merging Dataset application follows by the requirement should be: User can choose the xml file User can retrieve data from the database User can modify the merge dataset User can choose the save the merging dataset to selected xml file or database; or can save as the new xml file Characteristic of Application

4. SQL Server Visual Studio.Net (VB) Technologies Used

5. Application Start application

6. Application • Select xml file

7. Application • Connect to database and choose table data for merge

8. Application • Merge data

9. Application • Save as the dataset

10. Two datasets must have the same structure Limitation of application

11. Describe application Reading XML Part • Browse for the XML file me.OpenFileDialog1.Filter = "xml files (*.xml)|*.xml" Me.OpenFileDialog1.FilterIndex = 1 Me.OpenFileDialog1.ShowDialog() • Select XML file and press Open Me.TextBox1.Text = Me.OpenFileDialog1.FileName dsXML = New DataSet() dsXML.Reset() dsXML.ReadXml(Me.TextBox1.Text) Me.Label1.Text = dsXML.Tables(0).TableName.ToString

12. Describe application Database Connection(1) • Connect Server; retrieve all database name and add to select database combo box Dim strConn As String If Me.TextBox2.Text = "" Then strConn = "Initial Catalog =master;Server =(local); Integrated Security = SSPI;" Else strConn = "Initial Catalog =master;Server =" + Me.TextBox2.Text + "; Integrated Security = SSPI;" End If Dim conn As New SqlClient.SqlConnection(strConn) conn.Open() Dim da As New SqlClient.SqlDataAdapter("SELECT sysdatabases.name FROM sysdatabases", conn) Dim ds As New DataSet da.Fill(ds, "SystemDatabases") ComboBox1.DataSource = ds.Tables("SystemDatabases") ComboBox1.DisplayMember = "name" conn.Close()

13. Describe application Database Connection(2) • Connect database; retrieve all table name and add table name to Select table combo box Dim strConn As String If Me.ComboBox1.Text <> "" Then strConn = "Initial Catalog =" + Me.ComboBox1.Text + ";Server =" + Me.TextBox2.Text + "; Integrated Security = SSPI;" Dim conn As New SqlClient.SqlConnection(strConn) conn.Open() Dim da As New SqlClient.SqlDataAdapter("select name from sysobjects where type='U'", conn) Dim ds As New DataSet ds.Reset() da.Fill(ds, "SystemDatabases") ComboBox2.DataSource = ds.Tables("SystemDatabases") ComboBox2.DisplayMember = "name" conn.Close() End If

14. Describe application Load data from database and/or merge Connect database; retrieve all table name and add table name to Select table combo box strConn = "Initial Catalog =" + Me.ComboBox1.Text + ";Server =" + Me.TextBox2.Text + "; Integrated Security = SSPI;" Dim connMerge As New SqlClient.SqlConnection(strConn) connMerge.Open() Dim daMerge As New SqlClient.SqlDataAdapter("select * from " + Me.ComboBox2.Text, connMerge) dsDatabase = New DataSet(Me.ComboBox2.Text) dsDatabase.Reset() daMerge.Fill(dsDatabase, Me.ComboBox2.Text) Dim dstemp As New DataTable dstemp = dsDatabase.Tables(0).Clone // Clone dataset in order to make two dataset have the same data type // this this if case when user retrieve data from XML If (Me.TextBox1.Text <> "") Then Dim newRow As DataRow For j = 0 To Me.dsXML.Tables(0).Rows.Count - 1 newRow = dstemp.NewRow() For i = 0 To Me.dsDatabase.Tables(0).Columns.Count - 1 newRow(i) = dsXML.Tables(0).Rows(j).Item(i) Next dstemp.Rows.Add(newRow) Next Me.dsDatabase.Merge(dstemp) Me.Button7.Enabled = True End If With DataGridView1 .DataSource = dsDatabase .DataMember = dsDatabase.Tables(0).TableName.ToString End With connMerge.Close()

15. Describe application Add, Update, Delete, Save to xml, Save as Xml, Save to database will be enable after the Load & Merge button is pressed. Modify dataset (Add, Update, Delete) Add button code Dim AddRow As DataRow AddRow = dsDatabase.Tables(0).NewRow() dsDatabase.Tables(0).Rows.Add(AddRow) DataGridView1.Refresh() Update button code DataGridView1.Refresh() Delete button code DataGridView1.Rows.Remove(DataGridView1.CurrentRow) DataGridView1.Refresh()

16. Describe application Add, Update, Delete, Save to xml, Save as Xml, Save to database will be enable after the Load & Merge button is pressed. Save dataset (Save to Xml, Save as Xml, Save to database)(1) Save to xml code If dsDatabase.HasChanges() Then dsDatabase.WriteXml(Me.TextBox1.Text) dsDatabase.AcceptChanges() End If Me.dsDatabase.Reset() Me.ClearScreen() Save as Xml code Me.SaveFileDialog1.Filter = "xml files (*.xml)|*.xml" Me.SaveFileDialog1.FilterIndex = 1 Me.SaveFileDialog1.ShowDialog() Me.dsDatabase.WriteXml(Me.SaveFileDialog1.FileName) Me.dsDatabase.AcceptChanges() Me.dsDatabase.Reset() Me.ClearScreen()

17. Describe application Add, Update, Delete, Save to xml, Save as Xml, Save to database will be enable after the Load & Merge button is pressed. Save dataset (Save to Xml, Save as Xml, Save to database)(1) Save to database code Dim strConn As String strConn = "Initial Catalog =" + Me.ComboBox1.Text + ";Server =" + Me.TextBox2.Text + "; Integrated Security = SSPI;" Dim conn As New SqlClient.SqlConnection(strConn) conn.Open() Dim da As New SqlClient.SqlDataAdapter("select * from " + Me.ComboBox2.Text, conn) Dim command_builder As SqlCommandBuilder command_builder = New SqlCommandBuilder(da) If dsDatabase.HasChanges() Then da.Update(dsDatabase.Tables(0)) dsDatabase.AcceptChanges() End If conn.Close() Me.dsDatabase.Reset() Me.ClearScreen()

18. ???????????????????????????????????????????? ?? ?? ?? Feel free to ask me the question!! ?? ?? ?? ?? ?? ?? ?? ???????????????????????????????????????????? Question?

19. Thank you