远程吗,目标数据库是什么数据库啊?
方案一:(数据库中纪录execl文件地址)
将execl文件上传(socket)到服务器某个目录,服务器数据库中纪录文件在服务器上的地址。
方案二:(数据库中纪录execl中的数据)
提供你一个将用户execl数据上传到远端sql server数据库的解决思路:
oledb:
将execl作为数据源读入到dataset1中;
sqlclient:
连接远端数据库(ip地址);
用sqladapter、sqlcmdbuilder,获得一个数据结构与dataset1完全一致的dataset2(sqlserver);
将dataset1中的每一行添加(dataset2.addrow)到dataset2中;
最后提交sqladapter.uodate().
参考代码(vb的):(access to sqlserver)
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.OleDbPublic Class OleToSql
Private _tableName As String
Private _oleCriteria, _sqlCriteria As String Public Sub New() End Sub Public Sub New(ByVal tableName As String, ByVal oleCriteria As String, ByVal sqlCriteria As String)
_tableName = tableName
_oleCriteria = oleCriteria
_sqlCriteria = sqlCriteria
End Sub Public Function Execute() As String
Return Me.Execute(_tableName, _oleCriteria, _sqlCriteria)
End Function Public Function Execute(ByVal tableName As String, ByVal oleCriteria As String, ByVal sqlCriteria As String) As String
Dim sqlConn As New SqlConnection(ClsConnServer.ConnStr)
Dim oleConn As New OleDbConnection(ClsConnStr.ConnStr)
Dim sqlAdapter As SqlDataAdapter, oleAdapter As OleDbDataAdapter
Dim sqlCmdBuilder As SqlCommandBuilder
Dim sqlQry, oleQry As String
Dim sqlData, oleData As DataSet
Dim row As DataRow, rowInx As Integer, rowCount As Integer
Dim isValid As Boolean
Try
'get updating data sql db
sqlConn.Open()
Dim itime As Integer = sqlConn.ConnectionTimeout sqlQry = "select * from " & tableName & Space(1) & sqlCriteria
sqlAdapter = New SqlDataAdapter(sqlQry, sqlConn)
sqlData = New DataSet()
sqlAdapter.Fill(sqlData, tableName) 'builde ins/del command
sqlCmdBuilder = New SqlCommandBuilder(sqlAdapter)
sqlQry = "delete from " & tableName & Space(1) & sqlCriteria
sqlAdapter.DeleteCommand = New SqlCommand(sqlQry, sqlConn)
'Select Case tableName
' Case "e_yjry", "d_supervisor", "d_superchange"
' Dim DataHelper As New ClsDataHelper()
' DataHelper.F_GrnInsertCom(tableName, sqlAdapter, sqlConn)
' Case Else
sqlAdapter.InsertCommand = sqlCmdBuilder.GetInsertCommand
'End Select 'get source data from ole db
oleConn.Open()
oleQry = "select * from " & tableName & Space(1) & oleCriteria
oleAdapter = New OleDbDataAdapter(oleQry, oleConn)
oleData = New DataSet()
oleAdapter.Fill(oleData, tableName)
isValid = (oleData.Tables(tableName).Rows.Count > 0) If isValid Then
'delete from sql db
rowCount = sqlData.Tables(tableName).Rows.Count
If rowCount > 0 Then
For rowInx = rowCount - 1 To 0 Step -1
'sepecial condition check
Dim DataHelper As New ClsDataHelper()
If DataHelper.FillRowother(sqlData.Tables(tableName), rowInx) = True Then
sqlData.Tables(tableName).Rows(rowInx).Delete()
End If
Next
End If rowCount = oleData.Tables(tableName).Rows.Count
For rowInx = 0 To rowCount - 1
'For Each row In oleData.Tables(tableName).Rows
Dim newRow As DataRow = sqlData.Tables(tableName).NewRow
Dim col As DataColumn
'sepecial condition check
Dim DataHelper As New ClsDataHelper()
If DataHelper.FillRow(oleData.Tables(tableName), rowInx) = True Then
For Each col In sqlData.Tables(tableName).Columns
newRow(col.ColumnName) = oleData.Tables(tableName).Rows(rowInx)(col.ColumnName)
Next
sqlData.Tables(tableName).Rows.Add(newRow)
End If
Next
sqlAdapter.ContinueUpdateOnError = True
sqlAdapter.Update(sqlData, tableName)
End If Catch e As Exception
sqlConn.Close() : oleConn.Close()
ClsReturnData.strError += ClsDataHelper.GetTableRefName(tableName) & " 数据上传失败." + Chr(13)
Finally
sqlConn.Close() : oleConn.Close()
sqlConn.Dispose() : oleConn.Dispose()
End Try
End FunctionEnd Class
方案一:(数据库中纪录execl文件地址)
将execl文件上传(socket)到服务器某个目录,服务器数据库中纪录文件在服务器上的地址。
方案二:(数据库中纪录execl中的数据)
提供你一个将用户execl数据上传到远端sql server数据库的解决思路:
oledb:
将execl作为数据源读入到dataset1中;
sqlclient:
连接远端数据库(ip地址);
用sqladapter、sqlcmdbuilder,获得一个数据结构与dataset1完全一致的dataset2(sqlserver);
将dataset1中的每一行添加(dataset2.addrow)到dataset2中;
最后提交sqladapter.uodate().
参考代码(vb的):(access to sqlserver)
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.OleDbPublic Class OleToSql
Private _tableName As String
Private _oleCriteria, _sqlCriteria As String Public Sub New() End Sub Public Sub New(ByVal tableName As String, ByVal oleCriteria As String, ByVal sqlCriteria As String)
_tableName = tableName
_oleCriteria = oleCriteria
_sqlCriteria = sqlCriteria
End Sub Public Function Execute() As String
Return Me.Execute(_tableName, _oleCriteria, _sqlCriteria)
End Function Public Function Execute(ByVal tableName As String, ByVal oleCriteria As String, ByVal sqlCriteria As String) As String
Dim sqlConn As New SqlConnection(ClsConnServer.ConnStr)
Dim oleConn As New OleDbConnection(ClsConnStr.ConnStr)
Dim sqlAdapter As SqlDataAdapter, oleAdapter As OleDbDataAdapter
Dim sqlCmdBuilder As SqlCommandBuilder
Dim sqlQry, oleQry As String
Dim sqlData, oleData As DataSet
Dim row As DataRow, rowInx As Integer, rowCount As Integer
Dim isValid As Boolean
Try
'get updating data sql db
sqlConn.Open()
Dim itime As Integer = sqlConn.ConnectionTimeout sqlQry = "select * from " & tableName & Space(1) & sqlCriteria
sqlAdapter = New SqlDataAdapter(sqlQry, sqlConn)
sqlData = New DataSet()
sqlAdapter.Fill(sqlData, tableName) 'builde ins/del command
sqlCmdBuilder = New SqlCommandBuilder(sqlAdapter)
sqlQry = "delete from " & tableName & Space(1) & sqlCriteria
sqlAdapter.DeleteCommand = New SqlCommand(sqlQry, sqlConn)
'Select Case tableName
' Case "e_yjry", "d_supervisor", "d_superchange"
' Dim DataHelper As New ClsDataHelper()
' DataHelper.F_GrnInsertCom(tableName, sqlAdapter, sqlConn)
' Case Else
sqlAdapter.InsertCommand = sqlCmdBuilder.GetInsertCommand
'End Select 'get source data from ole db
oleConn.Open()
oleQry = "select * from " & tableName & Space(1) & oleCriteria
oleAdapter = New OleDbDataAdapter(oleQry, oleConn)
oleData = New DataSet()
oleAdapter.Fill(oleData, tableName)
isValid = (oleData.Tables(tableName).Rows.Count > 0) If isValid Then
'delete from sql db
rowCount = sqlData.Tables(tableName).Rows.Count
If rowCount > 0 Then
For rowInx = rowCount - 1 To 0 Step -1
'sepecial condition check
Dim DataHelper As New ClsDataHelper()
If DataHelper.FillRowother(sqlData.Tables(tableName), rowInx) = True Then
sqlData.Tables(tableName).Rows(rowInx).Delete()
End If
Next
End If rowCount = oleData.Tables(tableName).Rows.Count
For rowInx = 0 To rowCount - 1
'For Each row In oleData.Tables(tableName).Rows
Dim newRow As DataRow = sqlData.Tables(tableName).NewRow
Dim col As DataColumn
'sepecial condition check
Dim DataHelper As New ClsDataHelper()
If DataHelper.FillRow(oleData.Tables(tableName), rowInx) = True Then
For Each col In sqlData.Tables(tableName).Columns
newRow(col.ColumnName) = oleData.Tables(tableName).Rows(rowInx)(col.ColumnName)
Next
sqlData.Tables(tableName).Rows.Add(newRow)
End If
Next
sqlAdapter.ContinueUpdateOnError = True
sqlAdapter.Update(sqlData, tableName)
End If Catch e As Exception
sqlConn.Close() : oleConn.Close()
ClsReturnData.strError += ClsDataHelper.GetTableRefName(tableName) & " 数据上传失败." + Chr(13)
Finally
sqlConn.Close() : oleConn.Close()
sqlConn.Dispose() : oleConn.Dispose()
End Try
End FunctionEnd Class
oracle同样支持远程连接啊,采用类似的方法不就行了吗。
然后再读取excel内容,写到数据库里
如果程序在客户端运行,就直接读取excel内容,连到数据库。写到数据库里。