本文详细介绍了从连接数据库,到增删改查的实例,详细介绍了dataset的应用。 class Connect {//定义连接字符串,连接对象,命令对象 private String connectionstr; private SqlConnection connection; private SqlCommand command; private DataSet dataset; private SqlDataAdapter da; private SqlCommandBuilder commandbuild; public Connect() { //connectionstr = "Server=192.168.88.85//SQLExpress;Initial Catalog=aa; User ID=sa;Password=sasa"; connectionstr = "Server=192.168.88.85//SQLExpress;Initial Catalog=ENVIRONMENTBUSINESSYS; User ID=sa;Password=sasa; Max Pool Size=10; Min Pool Size=5;Connect Timeout=10;Connection Lifetime=10;Asynchronous Processing=true"; connection = new SqlConnection(connectionstr); dataset = new DataSet(); //command = new SqlCommand(); //command.Connection = connection; command = connection.CreateCommand(); connection.Open(); } public DataSet query(String sql) { //connection.Open(); da= new SqlDataAdapter(sql, connection); da.Fill(dataset,"para");//将数据填充到Dataset return dataset ; } public int add(string tablename,params Object[] args) { string sql = "select * from " + tablename; da = new SqlDataAdapter(sql,connection); commandbuild = new SqlCommandBuilder(da); da.Fill(dataset); Console.WriteLine("rows before change:{0}",dataset.Tables[0].Rows.Count); //添加之前先进行查找,相同的行不再添加,先定义主键列的数组 DataColumn[] keys=new DataColumn[1]; keys[0]=dataset.Tables[0].Columns["id"]; dataset.Tables[0].PrimaryKey = keys; //查找主键所在的行是否已存在 DataRow findrow = dataset.Tables[0].Rows.Find(args[1]); if (findrow == null) { DataRow newrow = dataset.Tables[0].NewRow(); for (int i = 0; i < args.Length;i++ ) newrow[i] = args[i]; dataset.Tables[0].Rows.Add(newrow); da.Update(dataset); return 1; } else { return 0; } } public int delete(string tablename, object[] key) { string sql = "select * from " + tablename; da = new SqlDataAdapter(sql, connection); commandbuild = new SqlCommandBuilder(da); da.Fill(dataset); //设置查询主键,及查询条件 DataColumn[] keys = new DataColumn[2]; keys[0] = dataset.Tables[0].Columns["name"]; keys[1] = dataset.Tables[0].Columns["id"]; dataset.Tables[0].PrimaryKey = keys; //查找该行是否存在,传过来的参数必须和定义的主键顺序相同 DataRow findrow = dataset.Tables[0].Rows.Find(key); if (findrow != null) { findrow.Delete(); return 1; } else return 0; } public int deletestudent(string studentname) { string sql = string.Format("select * from student"); da = new SqlDataAdapter(sql, connection); commandbuild = new SqlCommandBuilder(da); da.Fill(dataset); //设置查询条件 DataColumn[] keys = new DataColumn[1]; keys[0] = dataset.Tables[0].Columns["name"]; dataset.Tables[0].PrimaryKey = keys; //查找该行是否存在 DataRow findrow = dataset.Tables[0].Rows.Find(studentname); if (findrow != null) Console.WriteLine("exist"); //删除该学生的选课记录 int sid = System.Convert.ToInt32(findrow["id"]); Console.WriteLine(sid); int j= delectselectcourse(sid); //删除学生 if (findrow != null) { findrow.Delete(); da.Update(dataset); return 1; } else return 0; } public int delectselectcourse(int sid) { //根据学生id找该学生的选课记录 string query ="select selectcourse. * from selectcourse where studentid ="+sid; SqlDataAdapter da = new SqlDataAdapter(query, connection); SqlCommandBuilder commandbuild = new SqlCommandBuilder(da); DataSet dataset = new DataSet(); da.Fill(dataset); if (dataset != null) { int j = dataset.Tables[0].Rows.Count; Console.WriteLine(j); DataRowCollection dac = dataset.Tables[0].Rows; for (int i = 0; i < j; i++) { dac[i].Delete(); } try { da.Update(dataset.Tables[0]); dataset.Tables[0].AcceptChanges(); } catch (Exception ex) { Console.WriteLine(ex.Message); } return 1; } else return 0; }