ADO.NET-dataset操作[网摘]

 class basicOperation
    {
        string source = @"Data source=CMD-PC;Initial Catalog=mytest;Integrated Security=SSPI;";
/*解释一下：
* Data source=CMD-PC     是数据源，也就是数据库所在的服务器
* Initial Catalog=mytest      是要操作是数据库（这里是mytest）
* Integrated Security=SSP   安全性选择的是SSPI  （针对windows系统的）也可以把SSPI换成true
*/
        string squry = "SELECT [id],[name] FROM [mytest].[dbo].[depart]";
        public void selectquery()
        {
            //*******************************************************************************
            SqlConnection conn=new SqlConnection(source);
            SqlDataAdapter adp = new SqlDataAdapter(squry,conn);
            DataSet dataset = new DataSet();
            adp.Fill(dataset ," [mytest].[dbo].[depart]");
            //*******************************************************************************
            foreach (DataRow row in dataset.Tables[" [mytest].[dbo].[depart]"].Rows)/*注意这里：如果用的是sqlserver2008时，数据库字要填写完整，否则会告诉你找不到什么实例之类的错误*/
                Console.WriteLine(row[0]);
            conn.Close();
          }
 
        public void addItems()
        {
            SqlConnection conn = new SqlConnection(source);
 
            SqlDataAdapter adp = new SqlDataAdapter(squry, conn);
            SqlCommandBuilder cmd = new SqlCommandBuilder(adp);
            DataSet dataset = new DataSet();
            adp.Fill(dataset, "[mytest].[dbo].[depart]");
 
            //*************添加新行****************
            DataRow newrow = dataset.Tables["[mytest].[dbo].[depart]"].NewRow();
 
            newrow["id"] = 7;
            newrow["name"] = "商旅学院";
 
            dataset.Tables["[mytest].[dbo].[depart]"].Rows.Add(newrow);
            adp.Update(dataset, "[mytest].[dbo].[depart]");
 
            conn.Close();
        }
 
        public void modifyIterms()
        {
            SqlConnection conn = new SqlConnection(source);
            SqlDataAdapter apt = new SqlDataAdapter(squry,conn);
            SqlCommandBuilder cmd = new SqlCommandBuilder(apt);
            DataSet dataset = new DataSet();
            apt.Fill(dataset,"[mytest].[dbo].[depart]");
 
            //**********更新数据**************************
            Console.WriteLine("当前行是{0}", dataset.Tables["[mytest].[dbo].[depart]"].Rows[5]["name"]);
 
            dataset.Tables["[mytest].[dbo].[depart]"].Rows[5]["name"] = "城建学院";
            apt.Update(dataset, "[mytest].[dbo].[depart]");
 
            Console.WriteLine("修改后的结果是 {0}", dataset.Tables["[mytest].[dbo].[depart]"].Rows[5]["name"]);
            conn.Close();          
        }
 
        public void deleteIterms()
        {
            SqlConnection conn = new SqlConnection(source);
            SqlDataAdapter adp = new SqlDataAdapter(squry, conn);
            SqlCommandBuilder cmd = new SqlCommandBuilder(adp);
            DataSet dataset = new DataSet();
            adp.Fill(dataset,"[mytest].[dbo].[depart]");
 
            //*****************删除数据
             Console.WriteLine("当前有{0}行", dataset.Tables["[mytest].[dbo].[depart]"].Rows.Count);
            
            DataColumn[] keys=new DataColumn[1];
            keys[0]=dataset.Tables["[mytest].[dbo].[depart]"].Columns["id"];
            dataset.Tables["[mytest].[dbo].[depart]"].PrimaryKey=keys;
            DataRow deleteRow=dataset.Tables["[mytest].[dbo].[depart]"].Rows.Find(6);
 
            if(deleteRow!=null){
                deleteRow.Delete();
                adp.Update(dataset,"[mytest].[dbo].[depart]");
            }
 
            Console.WriteLine("删除当前行，结果是{0}行",dataset.Tables["[mytest].[dbo].[depart]"].Rows.Count);
            conn.Close();
        }
    }