如何把数据集中的数据导入到数据库?

[TreelightLSH]

我用的是sql server.

[TreelightLSH]

各位帮一下忙,我是刚学的

[luohuanyu]

再说清楚一点嘛，你所谓的数据集是指的什么？你想怎么个导法？是从其他的数据库导入SQL Server么？

[TreelightLSH]

我已经从excel中导入到数据集中，怎样把dataset中的数据导入到数据库？

[bishopliangjun1]

我做过,不过方法可能比较笨了点,
首先用isdbnull(ds.table(0).rows(x).item(y))
后用遍历,将数据存在数组中,在用insert语句将记录加入到数据库,不过这样写需要你对数据库存操作有一定的心得,否则会出现异常.记得给分

[TreelightLSH]

能不能把一个数据集的数据导入到另一个数据集?

[youngzi]

Dim sqlConn As New SqlClient.SqlConnection(strCnn)
Dim dtDataAdapter As New SqlDataAdapter

Dim cmdSelect As New SqlCommand
Dim cmdInsert As New SqlCommand
Dim cmdUpdate As New SqlCommand
Dim cmdDelete As New SqlCommand

' Add the Select command
cmdSelect.CommandText = "SELECT room_no,room_name,water_degree,electricity_degree FROM dbo.rf_room"


' Add the Insert command
cmdInsert.CommandText = "INSERT dbo.table(f1,f2,f3,f4) VALUES(@p1,@p2,@p3,@p4)"
cmdInsert.Parameters.Add("@p1", SqlDbType.Char, 4, "f1")
cmdInsert.Parameters.Add("@p2", SqlDbType.VarChar, 100, "f2")
cmdInsert.Parameters.Add("@p3", SqlDbType.Int, 4, "f3")
cmdInsert.Parameters.Add("@p4", SqlDbType.Int, 4, "f4")


' Add the Update command
cmdUpdate.CommandText = "UPDATE dbo.table SET f2=@p2,f3=@p3,f4=@p4 WHERE f1=@p1"
cmdUpdate.Parameters.Add("@p1", SqlDbType.Char, 4, "f1")
cmdUpdate.Parameters.Add("@p2", SqlDbType.VarChar, 100, "f2")
cmdUpdate.Parameters.Add("@p3", SqlDbType.Int, 4, "f3")
cmdUpdate.Parameters.Add("@p4", SqlDbType.Int, 4, "f4")

' Add the Delete command
cmdDelete.CommandText = "DELETE dbo.table WHERE f1=@p1"
cmdDelete.Parameters.Add("@p1", SqlDbType.Char, 4, "f1")
sqlConn.Open()
Dim dtDataadapter As New SqlDataAdapter
dtDataadapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
dtDataadapter.AcceptChangesDuringFill = True

cmdSelect.Connection = sqlConn
cmdInsert.Connection = sqlConn
cmdDelete.Connection = sqlConn
cmdUpdate.Connection = sqlConn

dtDataadapter.SelectCommand = cmdSelect
dtDataadapter.InsertCommand = cmdInsert
dtDataadapter.DeleteCommand = cmdDelete
dtDataadapter.UpdateCommand = cmdUpdate

dtDataadapter.Update(dsDataset, TableName)

[CMIC]

使用DataSet的Merge()方法：
连接数据库形成DataSet:DS1
从excel中导入到DataSet:DS2
DS1.Merge(DS)
然后向数据库提交DS1就行了。
关于Merge()方法例子：
Private Sub DemonstrateMerge()
' Create a DataSet with one table, two columns, and three rows.
Dim ds As New DataSet("myDataSet")
Dim t As New DataTable("Items")
Dim c1 As New DataColumn("id", Type.GetType("System.Int32"))
c1.AutoIncrement = True
Dim c2 As New DataColumn("Item", Type.GetType("System.Int32"))
' DataColumn array to set primary key.
Dim keyCol(1) As DataColumn
Dim r As DataRow
' Create variable for temporary DataSet.
Dim xSet As DataSet
' Add columns to table, and table to DataSet.
t.Columns.Add(c1)
t.Columns.Add(c2)
ds.Tables.Add(t)
' Set primary key column.
keyCol(0) = c1
t.PrimaryKey = keyCol
' Add ten rows.
Dim i As Integer
For i = 0 To 9
r = t.NewRow()
r("Item") = i
t.Rows.Add(r)
Next i
' Accept changes.
ds.AcceptChanges()
PrintValues(ds, "Original values")
' Change two row values.
t.Rows(0)("Item") = 50
t.Rows(1)("Item") = 111
' Add one row.
r = t.NewRow()
r("Item") = 74
t.Rows.Add(r)
' Insert code for error checking. Here we set one row in error.
t.Rows(1).RowError = "over 100"
PrintValues(ds, "Modified and New Values")
' If the table has changes or errors, create a subset DataSet.
If ds.HasChanges(DataRowState.Modified Or DataRowState.Added) _
And ds.HasErrors Then
' Use GetChanges to extract subset.
xSet = ds.GetChanges(DataRowState.Modified Or DataRowState.Added)
PrintValues(xSet, "Subset values")
' Insert code to reconcile errors. In this case, we'll reject changes.
Dim xTable As DataTable
For Each xTable In xSet.Tables
If xTable.HasErrors Then
Dim xRow As DataRow
For Each xRow In xTable.Rows
'Console.WriteLine(xRow["Item"]);
If CInt(xRow("Item", DataRowVersion.Current)) > 100 Then
xRow.RejectChanges()
xRow.ClearErrors()
End If
Next xRow
End If
Next xTable
PrintValues(xSet, "Reconciled subset values")
' Merge changes back to first DataSet.
ds.Merge(xSet)
PrintValues(ds, "Merged Values")
End If
End Sub

Private Sub PrintValues(ds As DataSet, label As String)
Console.WriteLine(ControlChars.Cr + label)
Dim t As DataTable
For Each t In ds.Tables
Console.WriteLine("TableName: " + t.TableName)
Dim r As DataRow
For Each r In t.Rows
Dim c As DataColumn
For Each c In t.Columns
Console.Write(ControlChars.Tab + " " + r(c).ToString())
Next c
Console.WriteLine()
Next r
Next t
End Sub
[C#]
private void DemonstrateMerge() {
// Create a DataSet with one table, two columns, and three rows.
DataSet ds = new DataSet("myDataSet");
DataTable t = new DataTable("Items");
DataColumn c1 = new DataColumn("id", Type.GetType("System.Int32"));
c1.AutoIncrement=true;
DataColumn c2 = new DataColumn("Item", Type.GetType("System.Int32"));
// DataColumn array to set primary key.
DataColumn[] keyCol= new DataColumn[1];
DataRow r;
// Create variable for temporary DataSet.
DataSet xSet;
// Add columns to table, and table to DataSet.
t.Columns.Add(c1);
t.Columns.Add(c2);
ds.Tables.Add(t);
// Set primary key column.
keyCol[0]= c1;
t.PrimaryKey=keyCol;
// Add ten rows.
for(int i = 0; i <10;i++){
r=t.NewRow();
r["Item"]= i;
t.Rows.Add(r);
}
// Accept changes.
ds.AcceptChanges();
PrintValues(ds, "Original values");
// Change two row values.
t.Rows[0]["Item"]= 50;
t.Rows[1]["Item"]= 111;
// Add one row.
r=t.NewRow();
r["Item"]=74;
t.Rows.Add(r);
// Insert code for error checking. Here we set one row in error.
t.Rows[1].RowError= "over 100";
PrintValues(ds, "Modified and New Values");
// If the table has changes or errors, create a subset DataSet.
if(ds.HasChanges(DataRowState.Modified | DataRowState.Added)& ds.HasErrors){
// Use GetChanges to extract subset.
xSet = ds.GetChanges(DataRowState.Modified|DataRowState.Added);
PrintValues(xSet, "Subset values");
// Insert code to reconcile errors. In this case, we'll reject changes.
foreach(DataTable xTable in xSet.Tables){
if (xTable.HasErrors){
foreach(DataRow xRow in xTable.Rows){
//Console.WriteLine(xRow["Item"]);
if((int)xRow["Item",DataRowVersion.Current ]> 100){
xRow.RejectChanges();
xRow.ClearErrors();
}
}
}
}
PrintValues(xSet, "Reconciled subset values");
// Merge changes back to first DataSet.
ds.Merge(xSet);
PrintValues(ds, "Merged Values");
}
}

private void PrintValues(DataSet ds, string label){
Console.WriteLine("\n" + label);
foreach(DataTable t in ds.Tables){
Console.WriteLine("TableName: " + t.TableName);
foreach(DataRow r in t.Rows){
foreach(DataColumn c in t.Columns){
Console.Write("\t " + r[c] );
}
Console.WriteLine();
}
}
}
联机帮助上例子，也很详细。

[bishopliangjun1]

你是想把数据表导入到另一个数据集吧
就用：ds.tables.add(table)
如果你想导入一个完整的数据集
就用：dim da as dataset
'ds
'da=ds.t
'da.table
da=ds.copy()

[富察咪咪]

收藏