在我的代码中将 dataTable 中的 200 条记录插入到数据库文件中时出现混乱

Question

我是 ADO.NET C# 编程的初学者。我的目的是将所有记录从 dt（我的 DataTable）转换为驱动程序 C 中的数据库文件（C:\datamining.mdf）。然而，我对此感到困惑，因为我无法使用已知的 SQL 插入语句 {INSERT datatable VALUES (,,,,) } 因为我的数据表有 200 条记录。这是我的代码：

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.IO;

namespace TextToDataBase
{
publicpartialclassForm2 : Form

{
public Form2()
{
InitializeComponent();
}
DataTable dt;
DataTable dt2;
privatevoid button1_Click(object sender, EventArgs e)
{
string str;
SqlConnection myConn = newSqlConnection("Server=(local);Integrated security=SSPI;database=master");
str = "CREATE DATABASE datamining_fourth ON PRIMARY" +
"(NAME=dataming_dat, FILENAME='C:\\dataminingforth.mdf', SIZE=10MB,MAXSIZE=50MB,FILEGROWTH=10%)" +
"LOG ON (NAME=datamining_log, FILENAME='C:\\dataminingforth.ldf', SIZE=1MB,MAXSIZE=5MB,FILEGROWTH=10%)";
SqlCommand myCommand = newSqlCommand(str, myConn); 
SqlConnection tableConn = newSqlConnection ("Server=(local);Integrated security=SSPI;database=datamining");
string str2;
str2= "CREATE TABLE drugdata ("+
"[Age] [int] IDENTITY(1,1) NOT NULL,"+
"[Sex] [bit] NOT NULL"+
"[BP] [int] NULL"+
"[Cholestrol] [int] NULL"+
"[NA] [int] NULL"+
"[K] [int] NULL"+
"[Drug] [string] NULL"; 
SqlCommand tablecmd = newSqlCommand(str2,tableConn);
try

{
myConn.Open();
myCommand.ExecuteNonQuery();
MessageBox.Show("پایگاه داده با موفقیت ایجاد شد!", "پیغام", MessageBoxButtons.OK, MessageBoxIcon.Information);
tableConn.Open();
tablecmd.ExecuteNonQuery();
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message, "خطا", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally

{
if (myConn.State == ConnectionState.Open)
myConn.Close();
if (tableConn.State == ConnectionState.Open)
tableConn.Close();
}
}
privatevoid Form2_Load(object sender, EventArgs e)
{
dt = newDataTable();
dt.Columns.Add("Age");
dt.Columns.Add("Sex");
dt.Columns.Add("BP");
dt.Columns.Add("Cholestrol");
dt.Columns.Add("Na");
dt.Columns.Add("K");
dt.Columns.Add("Drug");
StreamReader fg = newStreamReader("c:\\drug data mining.txt");
while (!fg.EndOfStream)
{
string val = fg.ReadLine();
string[] cells = val.Split(',');
for (int j = 0; j < cells.Length - 6; j++)
dt.Rows.Add(cells[0], cells[1], cells[2], cells[3], cells[4], cells[5], cells[6]);
}
dataGridView1.DataSource = dt;
dt2 = dt.Copy();
}
privatevoid button2_Click(object sender, EventArgs e)
{

SqlConnection tableConn = newSqlConnection("Server=(local);Integrated security=SSPI;database=datamining");
string str2;
str2 = "CREATE TABLE drugdata (" +
"[Age] [int] IDENTITY(1,1) NOT NULL," +
"[Sex] [nvarchar] (50) NOT NULL," +
"[BP] [int] NULL," +
"[Cholestrol] [int] NULL," +
"[NA] [int] NULL," +
"[K] [int] NULL," +
"[Drug] [nvarchar] (50) NULL)";
SqlCommand tablecmd = newSqlCommand(str2, tableConn);
try

{
tableConn.Open();
tablecmd.ExecuteNonQuery();
MessageBox.Show("جدول با موفقیت ایجاد شد!","پیغام", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message, "خطا", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally

{
if (tableConn.State == ConnectionState.Open)
tableConn.Close();
}
}
privatevoid button3_Click(object sender, EventArgs e)
{
DataSet miningSet = newDataSet();
miningSet.Tables.Add(dt);
SqlConnection insrConn = newSqlConnection("Server=(local);Integrated security=SSPI;database=datamining");
SqlDataAdapter sda = newSqlDataAdapter("Select * FROM dt", insrConn);
sda.Fill(miningSet.Tables["dt"]);
/*


*/
try

{
insrConn.Open();
insrcmd.ExecuteNonQuery();
MessageBox.Show("عمل درج با موفقیت ایجاد شد!", "پیغام", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message, "خطا", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally

{
if (insrConn.State == ConnectionState.Open)
insrConn.Close();
}
}
}
}

有一个充满 */ /* 的空间需要执行上述插入。我怎样才能进行这样的操作？您至少可以用伪代码指导我吗？

Answer 1

如果我理解正确，您有兴趣将数据加载到数据集并将更改保存回数据库吗？以下是您编辑数据集中的数据并保存更改的示例。

    dataAdapter = new SqlDataAdapter(sqlQuery, databaseConnectionString);
    SqlCommandBuilder builder = new SqlCommandBuilder(dataAdapter);
    builder.GetUpdateCommand();
    dataSet = new DataSet();
    DataAdapter.Fill(dataSet);
    //now you edit dataSet

如果您想将 dataSet 中所做的更改更新回 SqlDatabase，则应使用 SqlCommandBuilder！比你可以写：

    dataAdapter.Update(dataSet);

Answer 2

如果您不想处理 DataAdapter （这是更好的方法），请尝试如下操作：

string insertStatement;
foreach (DataRow dRow in dt.Rows)
{
     insertStatement = "INSERT datatable VALUES (";
     foreach (object objItem in dRow.ItemArray)
     {
         insertStatement += objItem.ToString() + ",";
     }
     insertStatement = insertStatement.Substring(0, insertStatement.LastIndexOf(','));
     insertStatement += ")";
     // Than execute the insert statement
}