如何使用 SqlCommand 和 DataAdapter 来使用 C# 操作 SQL Server [英] How to use SqlCommand and DataAdapter in order to manipulate SQL Server with C#

问题描述

1. 我创建了一个名为 Department 的表，其中包含 2 列 Department ID 和 Department Name

1. I create table called Department with 2 columns Department ID which is auto increment and Department Name

我创建了 Navigate_Department() 以便遍历部门行

I create Navigate_Department() in order to walk through the department rows

System.Data.SqlClient.SqlConnection con;
DataSet Dep_ds;
System.Data.SqlClient.SqlDataAdapter Dep_da;
int Dep_MaxRows = 0;
int Dep_inc = 0;

private void ILS_Load(object sender, EventArgs e)
{
   con = new System.Data.SqlClient.SqlConnection();
   con.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\ILS_DB.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";
   con.Open();

   Dep_ds = new DataSet();
   string sql2 = "select * from Department order by DepartmentID";
   Dep_da = new System.Data.SqlClient.SqlDataAdapter(sql2, con);
   Dep_da.Fill(Dep_ds, "Department");
   Navigate_Department();
   Dep_MaxRows = Dep_ds.Tables["Department"].Rows.Count;
}

private void Navigate_Department() 
{
    DataRow dRow = Dep_ds.Tables["Department"].Rows[Dep_inc];

    Dep_ID.Text =dRow.ItemArray.GetValue(0).ToString();
    Dep_Name.Text = dRow.ItemArray.GetValue(1).ToString(); 
}

private void Move_Next_Click(object sender, EventArgs e)
{
    if (Dep_inc != Dep_MaxRows-1)
    {
        Dep_inc++;
        Navigate_Department();
     }
    else
    {
        MessageBox.Show("No More Records");
    }
}

private void Move_back_Click(object sender, EventArgs e)
{
    if (Dep_inc > 0)
    {
        Dep_inc--;
        Navigate_Department();
     }
    else
    {
        MessageBox.Show("First Record");
    }
}

private void Dep_Clear_Click(object sender, EventArgs e)
{
    Dep_ID.Clear();
    Dep_Name.Clear();
}

private void Dep_Add_Click(object sender, EventArgs e)
{
    try
    {
        SqlCommand insCmd = new SqlCommand("insert into dbo.Department (DepartmentName) values ('" + Dep_Name.Text + "')", con);
        Dep_da.InsertCommand = insCmd;

        Dep_MaxRows = Dep_MaxRows + 1;
        Dep_inc = Dep_MaxRows - 1;
        Dep_Max.Text = Dep_MaxRows.ToString();
        Dep_Current.Text = (Dep_MaxRows).ToString();
    }
    catch (Exception exceptionObject)
    {
        MessageBox.Show(exceptionObject.Message);
    }

问题是:

单击清除按钮后，将部门名称插入Dep_Name 文本框中，然后单击添加按钮.我插入的名称没有保存在数据库中，如果我单击后移然后下一步以查看我插入的内容，我会在 Index out of range 异常中收到 >Navigate_Department() 方法.

After I click clear button, I insert the department name into Dep_Name textbox then click add button. The name that I inserted didn’t get saved in the database, and if I click move back then move next in order to see what I inserted, I get a Index out of range exception in the Navigate_Department() method.

那我有没有搞错?

推荐答案

与其像这样创建插入语句，不如使用带有插入命令的数据适配器，向表中添加一个新的 DataRow 实例，然后使用数据适配器执行更新.

Rather than create the insert statement like this you should use the data adapter with an insert command, add a new DataRow instance to the table then use the data adapter to execute the update.

或者，您可以通过用insCmd.ExecuteNonQuery"替换对Dep_da.InsertCommand = insCmd"的调用来执行Dep_Add_Click中的插入命令，但这意味着您需要重新运行select语句并重新填充数据集从数据库获取数据到数据集.

Alternatively you could execute the insert command within Dep_Add_Click by replacing the call to "Dep_da.InsertCommand = insCmd" with "insCmd.ExecuteNonQuery", however this would mean that you would need to re-run the select statement and repopulate the dataset from the database to get the data from the database into the dataset.

这篇关于如何使用 SqlCommand 和 DataAdapter 来使用 C# 操作 SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！