Importing .NET data Grid to the SQL server database

[software_develo]

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

[srinivas_s]

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

[software_develo]

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