SqlDataAdapter与DataSet绑定跟新数据库

 

 

 

首先这里使用绑定控件是DataGridView

绑定语句：

DataGridView1.DataSource=my_dataset.Tables["userInfo"];

但是这个my_dataset怎么来的，又是怎么跟SqlDataAdapter 绑定到一起实现更新的呢？

初始化定义 
         private DataSet my_dataset = new DataSet();
        private string sql = "select userId as '用户名',pwd as '密码',userRole as '角色' from UserInfo";
        SqlDataAdapter my_adapter = null;
        SqlCommandBuilder myscmd = null;
        loginclass sqlClass = new loginclass();
 
//类的定义
//下面提供了多种连接字符串的方法
//还包括了局域网内远程连接方法 下面是通过配置参数文件*ini 保存服务器信息
//该IniFileAccess类提供了读取流文件信息接口　　　　
 public class loginclass
    {
        private static string constr = "Integrated Security=SSPI;Persist Security Info=true;Initial Catalog=NJTT;Data Source=(local) ";
        //private static string constr = "Data Source=(local);Initial Catalog=NJTT;User Id=sa;Password=19861126;";
        //private static string constr = "Password=123;Persist Security Info=true;User ID=sa;Initial Catalog=NJTT;Data Source=125.216.242.25";
        //private static string constr = "server=" + IniFileAccess.GetString("DATABASE", "NJTT_ServerName", "") +
        //                                    ";database=" + IniFileAccess.GetString("DATABASE", "NJTT_Database", "") +
        //                                    ";uid=" + IniFileAccess.GetString("DATABASE", "DB_user", "") +
        //                                    ";pwd=" + IniFileAccess.GetString("DATABASE", "DB_password", "");
        public SqlConnection con;
        
        public bool Open()
        {
            con = new SqlConnection(constr);
            if (con.State != ConnectionState.Open)
            {
                try
                {
                    con.Open();
                }
                catch (Exception es)
                {
                    MessageBox.Show(es.ToString());
                    return false;
                }
            }
            return true;
        }
        public bool Close()
        {
            if (con.State != ConnectionState.Closed)
            {
                try
                {
                    con.Close();
                }
                catch (Exception es)
                {
                    MessageBox.Show(es.ToString());
                    return false;
                }
            }
            return true;
        }
        public DataSet SelectDataSet(string sql)
        {
            DataSet ds = new DataSet();
            try
            {
                SqlDataAdapter da = new SqlDataAdapter(sql, con);
                ds = new DataSet();
                da.Fill(ds, "ds");
            }
            catch (Exception es)
            {
                MessageBox.Show(es.ToString(), "数据库操作失败");
                return null;
            }
            return ds;
        }
        public SqlDataAdapter SqlDAreturn(string sql)
        {
            con = new SqlConnection(constr);
            SqlCommand mycmd = con.CreateCommand();
            mycmd.CommandText = sql;
            SqlDataAdapter myda = new SqlDataAdapter(mycmd);
            return myda;
        } 
    }

连接数据库获得sqlDataAdapter

 

            my_adapter=sqlClass.SqlDAreturn(sql);      
            my_adapter.Fill(my_dataset, "UserInfo");
            DataGridView1.DataSource = my_dataset.Tables["UserInfo"];

 

上面是实现绑定数据库到控件，那么如何在控件中修改数据然后返回给数据库呢！

下面是实现方法

 

//添加一个委托  datagridview行修改的委托
DataGridView1.CellValueChanged += ChangeDataSet;

private void ChangeDataSet(object sender,DataGridViewCellEventArgs e)
        {
            m_dataset.Tables["UserInfo"].Rows[e.RowIndex][e.ColumnIndex]=DataGridView1.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString();
        }
    
// 更新到数据库按钮
        private void btn_add_Click(object sender, EventArgs e)
        {
            myscmd = new SqlCommandBuilder(my_adapter);
            my_adapter.Update(my_dataset, "UserInfo");
            DataGridView1.DataSource = m_dataset.Tables["UserInfo"];
        }

 

上面即可大功告成！值得注意的是在sql语句中不能使用distinct 关键字，否则会出现如下错误：

对于不返回任何键列信息的 SelectCommand，不支持 UpdateCommand 的动态 SQL 生成。

 

期待牛人指点。。。QQ:382333514