datagridview直接更新数据库

Question

SqlConnection conn = new SqlConnection("server=.;database=student;integrated security=true");
conn.Open();
DataSet ds = new DataSet();
DataTable dt = new DataTable();            
SqlDataAdapter da = new SqlDataAdapter("select * from users", conn);
SqlCommandBuilder cb = new SqlCommandBuilder(da);
da.Fill(ds,"users");            
dt = ds.Tables["users"];
dt.PrimaryKey = new DataColumn[] { dt.Columns["userID"] };
for (int i = 0; i < dt.Rows.Count;i++ )
{ 
     DataRow dr = dt.Rows.Find(Int32.Parse(dt.Rows[i][0].ToString()));              
     for (int j = 1; j < dt.Columns.Count; j++)
     {                    
         dr[j] = dataGridView1.Rows[i].Cells[j].Value.ToString();
     }
     da.Update(ds,"users");
}
dataGridView1.Update();
conn.Close();
我的数据表中没有主键，但在Table里添加了，但怎么还是报错了？这用datagridview更新没有主键的数据表到底怎么样做的啊？哪位能帮我解决一下？谢谢

Answer 1

主键影响不大，控件的更新主要是绑定的datatable更新。datatable有了新变化后，调用datatable的acceptchange和对应的更新方法就可以了。

Answer 2

这是我的一个练习例子，希望对你有用
        protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
        {
            this.GridView1.EditIndex = e.RowIndex;
            //sqlcon = new SqlConnection(strCon);
            //string sqlstr = "update 表 set 字段1='"
            //    + ((TextBox)(GridView1.Rows[e.RowIndex].Cells[1].Controls[0])).Text.ToString().Trim() + "',字段2='"
            //    + ((TextBox)(GridView1.Rows[e.RowIndex].Cells[2].Controls[0])).Text.ToString().Trim() + "',字段3='"
            //    + ((TextBox)(GridView1.Rows[e.RowIndex].Cells[3].Controls[0])).Text.ToString().Trim() + "' where id='"
            //    + GridView1.DataKeys[e.RowIndex].Value.ToString() + "'";
            //sqlcom = new SqlCommand(sqlstr, sqlcon);
            //sqlcon.Open();
            //sqlcom.ExecuteNonQuery();
            //sqlcon.Close();
            User user = new User();
            user.Name = ((TextBox)(GridView1.Rows[e.RowIndex].Cells[1].Controls[0])).Text.Trim().ToString();
            user.Age = Convert.ToInt32(((TextBox)(GridView1.Rows[e.RowIndex].Cells[2].Controls[0])).Text.Trim().ToString());
            user.IsMarried = (((TextBox)(GridView1.Rows[e.RowIndex].Cells[3].Controls[0])).Text.Trim().ToString() == "是") ? "true" : "false";
            user.Introduce = ((TextBox)(GridView1.Rows[e.RowIndex].Cells[4].Controls[0])).Text.Trim().ToString();
            user.Sex = (((DropDownList)(GridView1.Rows[e.RowIndex].FindControl("droplst"))).SelectedItem.ToString() == "男") ? "true" : "false";
            user.UserId = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Value.ToString());
            db.UpDate(user);            GridView1.EditIndex = -1;
            bind();
        } public void bind()
        {
            DataSet myds = new DataSet();
            myds = getselect();
            GridView1.DataSource = myds;
            GridView1.DataKeyNames = new string[] { "UserId" };//主键
            GridView1.DataBind();            for (int i = 0; i <= GridView1.Rows.Count - 1; i++)
            {
                DataRowView mydrv = myds.Tables[0].DefaultView[i];
                if (mydrv["Sex"].ToString() == "true")
                {
                    ddl = (DropDownList)GridView1.Rows[i].FindControl("droplst");
                    //ddl.SelectedIndex = 0;
                    ddl.SelectedItem.Text = "女";
                }
                if (mydrv["Sex"].ToString() == "false")
                {
                    ddl = (DropDownList)GridView1.Rows[i].FindControl("droplst");
                    //ddl.SelectedIndex =1;
                    ddl.SelectedItem.Text = "男";
                }
                GridView1.Rows[i].Cells[4].Text = substring(mydrv["Introduce"].ToString(), 4);
            }  
        }public DataSet getselect()
        {
            string sqlstr = @"select UserId,Name,Age,Introduce,Sex,
            (case when cast(IsMarried as varchar(50))= 1 then '是' else '否' end) as IsMarried 
            from myUser";
            sqlcon = new SqlConnection(strCon);
            SqlDataAdapter myda = new SqlDataAdapter(sqlstr, sqlcon);
            DataSet myds = new DataSet();
            sqlcon.Open();
            myda.Fill(myds);
            sqlcon.Close();
            return myds;        }

Answer 3

1#的jinyuttt仁兄，能具体说说怎么做吗？有代码就更好了，谢谢

Answer 4

要使用SqlDataAdapter进行数据更新，数据库表中要有主键，否则其自动生成的DeleteCommand和UpdateCommand无法使用
建议在数据库表中添加主键
否则只有在更新时，先删除数据库表的全部旧数据，再插入全部新数据

Answer 5

msdn代码：
using System;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;public class Form1 : System.Windows.Forms.Form
{
    private DataGridView dataGridView1 = new DataGridView();
    private BindingSource bindingSource1 = new BindingSource();
    private SqlDataAdapter dataAdapter = new SqlDataAdapter();
    private Button reloadButton = new Button();
    private Button submitButton = new Button();    [STAThreadAttribute()]
    public static void Main()
    {
        Application.Run(new Form1());
    }    // Initialize the form.
    public Form1()
    {
        dataGridView1.Dock = DockStyle.Fill;        reloadButton.Text = "reload";
        submitButton.Text = "submit";
        reloadButton.Click += new System.EventHandler(reloadButton_Click);
        submitButton.Click += new System.EventHandler(submitButton_Click);        FlowLayoutPanel panel = new FlowLayoutPanel();
        panel.Dock = DockStyle.Top;
        panel.AutoSize = true;
        panel.Controls.AddRange(new Control[] { reloadButton, submitButton });        this.Controls.AddRange(new Control[] { dataGridView1, panel });
        this.Load += new System.EventHandler(Form1_Load);
        this.Text = "DataGridView databinding and updating demo";
    }    private void Form1_Load(object sender, System.EventArgs e)
    {
        // Bind the DataGridView to the BindingSource
        // and load the data from the database.
        dataGridView1.DataSource = bindingSource1;
        GetData("select * from Customers");
    }    private void reloadButton_Click(object sender, System.EventArgs e)
    {
        // Reload the data from the database.
        GetData(dataAdapter.SelectCommand.CommandText);
    }    private void submitButton_Click(object sender, System.EventArgs e)
    {
        // Update the database with the user's changes.
        dataAdapter.Update((DataTable)bindingSource1.DataSource);
    }    private void GetData(string selectCommand)
    {
        try
        {
            // Specify a connection string. Replace the given value with a 
            // valid connection string for a Northwind SQL Server sample
            // database accessible to your system.
            String connectionString =
                "Integrated Security=SSPI;Persist Security Info=False;" +
                "Initial Catalog=Northwind;Data Source=localhost";            // Create a new data adapter based on the specified query.
            dataAdapter = new SqlDataAdapter(selectCommand, connectionString);            // Create a command builder to generate SQL update, insert, and
            // delete commands based on selectCommand. These are used to
            // update the database.
            SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);            // Populate a new data table and bind it to the BindingSource.
            DataTable table = new DataTable();
            table.Locale = System.Globalization.CultureInfo.InvariantCulture;
            dataAdapter.Fill(table);
            bindingSource1.DataSource = table;            // Resize the DataGridView columns to fit the newly loaded content.
            dataGridView1.AutoResizeColumns( 
                DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);
        }
        catch (SqlException)
        {
            MessageBox.Show("To run this example, replace the value of the " +
                "connectionString variable with a connection string that is " +
                "valid for your system.");
        }
    }}