Importing .NET data Grid to the SQL server database


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Importing .NET data Grid to the SQL server database

Hybrid View

  1. #1
    Join Date
    Oct 2005
    Posts
    95

    Importing .NET data Grid to the SQL server database

    Hi

    Thanks in advance for all your help . I need some help with importing my datagrid information to a SQL table . I can not save the data that came to the data grid from CSV file to a SQL table . I can import CSV file to a .NET data grid and modify that . But I can not save it to the data base . I like to have client export the data from the CSV file to the data grid and make changes in the data grid and save that back to the SQL server table . How do I do that . Also is that possible to import .CSV data to data grid using SQL Adapter . I can do the import using oledb connection but not SQL connection .

    Thanks

  2. #2
    Join Date
    May 2005
    Location
    UK
    Posts
    278
    When the client exports the data on to the data grid you must be creating a datatable whose schema will be the same as the schema in your SQL server table
    Why dont you add this table to a dataset and then use a SqlDataAdapter to update the changes in the datatable to the SQL table should be straight forward only thing you have to bear in mind is all these rows need to be inserted so loop through the datatable bound to the grid then add each of these rows to the dataset and update changes on to the db, other wise use a command object and insert each row into the database
    Does that help
    Sri

  3. #3
    Join Date
    Oct 2005
    Posts
    95

    Exporting data grid to SQL server table

    Hi

    Thanks for the quick reply . I did not understand fully what you meant . I think I should provide some of my code that might help you to understand what I like to have . Please help

    Imports System.Data.SqlClient.SqlException
    Imports System
    Imports System.Data
    Imports System.Data.SqlClient
    Imports System.Xml
    Imports System.IO
    Imports System.Data.OleDb
    Public Class Form1
    Inherits System.Windows.Forms.Form

    #Region " Windows Form Designer generated code "

    Public Sub New()
    MyBase.New()

    'This call is required by the Windows Form Designer.
    InitializeComponent()

    'Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
    If disposing Then
    If Not (components Is Nothing) Then
    components.Dispose()
    End If
    End If
    MyBase.Dispose(disposing)
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer


    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.
    'Do not modify it using the code editor.
    Friend WithEvents DataGrid1 As System.Windows.Forms.DataGrid
    Friend WithEvents Button1 As System.Windows.Forms.Button
    Friend WithEvents Button2 As System.Windows.Forms.Button
    Friend WithEvents Button3 As System.Windows.Forms.Button
    Friend WithEvents SqlSelectCommand1 As System.Data.SqlClient.SqlCommand
    Friend WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection
    Friend WithEvents SqlDataAdapter1 As System.Data.SqlClient.SqlDataAdapter
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
    Me.DataGrid1 = New System.Windows.Forms.DataGrid
    Me.Button1 = New System.Windows.Forms.Button
    Me.Button2 = New System.Windows.Forms.Button
    Me.Button3 = New System.Windows.Forms.Button
    Me.SqlSelectCommand1 = New System.Data.SqlClient.SqlCommand
    Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection
    Me.SqlDataAdapter1 = New System.Data.SqlClient.SqlDataAdapter
    CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).BeginInit()
    Me.SuspendLayout()
    '
    'DataGrid1
    '
    Me.DataGrid1.DataMember = ""
    Me.DataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText
    Me.DataGrid1.Location = New System.Drawing.Point(144, 112)
    Me.DataGrid1.Name = "DataGrid1"
    Me.DataGrid1.Size = New System.Drawing.Size(384, 184)
    Me.DataGrid1.TabIndex = 0
    '
    'Button1
    '
    Me.Button1.Location = New System.Drawing.Point(632, 208)
    Me.Button1.Name = "Button1"
    Me.Button1.Size = New System.Drawing.Size(160, 23)
    Me.Button1.TabIndex = 1
    Me.Button1.Text = "Import from Database"
    '
    'Button2
    '
    Me.Button2.Location = New System.Drawing.Point(640, 264)
    Me.Button2.Name = "Button2"
    Me.Button2.TabIndex = 2
    Me.Button2.Text = "UPDATE"
    '
    'Button3
    '
    Me.Button3.Location = New System.Drawing.Point(632, 160)
    Me.Button3.Name = "Button3"
    Me.Button3.Size = New System.Drawing.Size(168, 23)
    Me.Button3.TabIndex = 3
    Me.Button3.Text = "Import from CSV "
    '
    'SqlSelectCommand1
    '
    Me.SqlSelectCommand1.CommandText = "SELECT Column1, Column2 FROM Test_Table"
    Me.SqlSelectCommand1.Connection = Me.SqlConnection1
    '
    'SqlConnection1
    '
    Me.SqlConnection1.ConnectionString = "workstation id=""LHP-LXP4LF3H81"";packet size=4096;integrated security=SSPI;initial" & _
    " catalog=CReditMemoDemo;persist security info=False"
    '
    'SqlDataAdapter1
    '
    Me.SqlDataAdapter1.SelectCommand = Me.SqlSelectCommand1
    '
    'Form1
    '
    Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
    Me.ClientSize = New System.Drawing.Size(872, 366)
    Me.Controls.Add(Me.Button3)
    Me.Controls.Add(Me.Button2)
    Me.Controls.Add(Me.Button1)
    Me.Controls.Add(Me.DataGrid1)
    Me.Name = "Form1"
    Me.Text = "Form1"
    CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).EndInit()
    Me.ResumeLayout(False)

    End Sub

    #End Region

    Private Const SELECT_STRING As String = _
    "SELECT * FROM test_table "
    Private Const CONNECT_STRING As String = _
    "Data Source=localhost;Initial " & _
    "Catalog=creditmemodemo;User Id=sa"

    ' The DataSet that holds the data.
    Private mDataSet As DataSet

    ' Load the data.
    Private Sub Form1_Load(ByVal sender As Object, ByVal e As _
    System.EventArgs) Handles MyBase.Load
    'Dim data_adapter As SqlDataAdapter

    '' Create the SqlDataAdapter.
    'data_adapter = New SqlDataAdapter(SELECT_STRING, _
    ' CONNECT_STRING)

    '' Map Table to Contacts.
    'data_adapter.TableMappings.Add("Table", "TBL_creditMemo")

    '' Fill the DataSet.
    'mDataSet = New DataSet
    ''data_adapter.Fill(mDataSet)

    '' Bind the DataGrid control to the Contacts DataTable.
    'DataGrid2.SetDataBinding(mDataSet, "Contacts")
    End Sub


    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim data_adapter As SqlDataAdapter

    ' Create the SqlDataAdapter.
    data_adapter= New SqlDataAdapter(SELECT_STRING, _
    CONNECT_STRING)

    ' Map Table to Contacts.
    data_adapter.TableMappings.Add("Table", "test_table")

    ' Fill the DataSet.
    mDataSet = New DataSet
    ' data_adapter.Fill(mDataSet, "Test_table")
    SqlDataAdapter1.Fill(mDataSet, "Test_table")

    ' Bind the DataGrid control to the Contacts DataTable.
    DataGrid1.SetDataBinding(mDataSet, "Test_table")

    End Sub

    Private Sub SqlConnection1_InfoMessage(ByVal sender As System.Object, ByVal e As System.Data.SqlClient.SqlInfoMessageEventArgs)

    End Sub

    Private Sub SqlDataAdapter1_RowUpdated(ByVal sender As System.Object, ByVal e As System.Data.SqlClient.SqlRowUpdatedEventArgs)


    End Sub

    Private Sub Form1_Closing(ByVal sender As Object, ByVal e _
    As System.ComponentModel.CancelEventArgs) Handles _
    MyBase.Closing
    'If mDataSet.HasChanges() Then
    ' Dim data_adapter As SqlDataAdapter
    ' Dim command_builder As SqlCommandBuilder

    ' ' Create the DataAdapter.
    ' data_adapter = New SqlDataAdapter(SELECT_STRING, _
    ' CONNECT_STRING)

    ' ' Map Table to Contacts.
    ' data_adapter.TableMappings.Add("Table", "test_table")

    ' ' Make the CommandBuilder generate the
    ' ' insert, update, and delete commands.
    ' command_builder = New _
    ' SqlCommandBuilder(data_adapter)

    ' ' Uncomment this code to see the INSERT,
    ' ' UPDATE, and DELETE commands.
    ' 'Debug.WriteLine("*** INSERT ***")
    ' 'Debug.WriteLine(command_builder.GetInsertCommand.CommandText)
    ' 'Debug.WriteLine("*** UPDATE ***")
    ' 'Debug.WriteLine(command_builder.GetUpdateCommand.CommandText)
    ' 'Debug.WriteLine("*** DELETE ***")
    ' 'Debug.WriteLine(command_builder.GetDeleteCommand.CommandText)

    ' ' Save the changes.
    ' SqlDataAdapter1.Update(mDataSet)
    'End If
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
    If mDataSet.HasChanges() Then
    Dim data_adapter As SqlDataAdapter
    Dim command_builder As SqlCommandBuilder

    ' Create the DataAdapter.
    data_adapter = New SqlDataAdapter(SELECT_STRING, _
    CONNECT_STRING)

    ' Map Table to Contacts.
    data_adapter.TableMappings.Add("Table", "test_table")

    ' Make the CommandBuilder generate the
    ' insert, update, and delete commands.
    command_builder = New _
    SqlCommandBuilder(data_adapter)

    ' Uncomment this code to see the INSERT,
    ' UPDATE, and DELETE commands.
    'Debug.WriteLine("*** INSERT ***")
    'Debug.WriteLine(command_builder.GetInsertCommand.CommandText)
    'Debug.WriteLine("*** UPDATE ***")
    'Debug.WriteLine(command_builder.GetUpdateCommand.CommandText)
    'Debug.WriteLine("*** DELETE ***")
    'Debug.WriteLine(command_builder.GetDeleteCommand.CommandText)

    ' Save the changes.
    SqlDataAdapter1.Update(mDataSet)
    End If
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
    Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\;Extended Properties=Text;"

    Dim objConn As New OleDbConnection(sConnectionString)

    objConn.Open()

    Dim objCmdSelect As New OleDbCommand("SELECT * FROM test.csv", objConn)

    Dim objAdapter1 As New OleDbDataAdapter

    objAdapter1.SelectCommand = objCmdSelect
    Dim mdataset As New DataSet

    'Dim objDataset1 As New DataSet

    objAdapter1.Fill(mdataset, "Test")

    DataGrid1.DataSource = mdataset.Tables(0).DefaultView

    objConn.Close()

    End Sub
    End Class

Similar Threads

  1. Importing SQL Table to the Data Grid
    By software_develo in forum .NET
    Replies: 5
    Last Post: 11-18-2005, 05:11 PM
  2. Replies: 3
    Last Post: 07-25-2005, 09:11 AM
  3. Opening for a SQL Server DBA
    By Bruce Steele in forum Careers
    Replies: 0
    Last Post: 09-24-2002, 11:35 AM
  4. SQL Server Developer in Calgary Canada
    By Dan Staggs in forum Careers
    Replies: 0
    Last Post: 08-07-2002, 12:04 PM
  5. Can Sql Server do this?
    By DavidR in forum Database
    Replies: 5
    Last Post: 02-15-2001, 12:46 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center