ADO.NET 添加数据2

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace ADO.NET.AddNewRow2
{
    class Program
    {
        static void Main(string[] args)
        {
            /// <summary>
            /// 添加数据的同时检测是否存在相同数据，如果存在，就给出提示并取消数据插入
            /// </summary>

            SqlConnection cnn = new SqlConnection(@"Server=MICHAELYANG\SQLEXPRESS;Integrated Security=True;" + "Database=NorthwindEn");//使用链接字符串创建一个连接对象

            SqlDataAdapter adapter = new SqlDataAdapter("select CustomerID,CompanyName from Customers",cnn);//创建SqlDataAdapter对象

            SqlCommandBuilder builder = new SqlCommandBuilder(adapter);//SqlDataAdapter对象作为参数传递给SqlCommandBuilder构造函数帮我们生成更新的SQL

            adapter.UpdateCommand = builder.GetUpdateCommand();//將SqlcommandBuilder生成的Sql赋给SqldataAdapter对象，在调用Update方法同步数据是执行

            DataSet ds = new DataSet();//创建DataSet方法，用于填充数据

            adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;//通过DataAdapter对象的MissingSchemaAction属性来显示的告诉ADO.NET加载主键信息
           
            adapter.Fill(ds, "Customers");//填充数据

            Console.WriteLine("# rows before change:{0}", ds.Tables["Customers"].Rows.Count);//读取填充好的DataTable数据总行数

            DataRow findRow = ds.Tables["Customers"].Rows.Find("FUCK");//建立一个DataRow对象findRow来获取结果

            //判断DataRow对象findRow返回的结果 Ture就继续 否则就放弃修改

            if (findRow == null)
            {
                Console.WriteLine("FUCK not find,will add to Customers table");

                DataRow row = ds.Tables["Customers"].NewRow();//查找的主键ID不存在，就创建新行对象Row

                row["CustomerID"] = "FUCK";//赋值

                row["CompanyName"] = "clochase.Inc.";//赋值

                ds.Tables["Customers"].Rows.Add(row);//使用Rows集合的Add（）方法添加新行

                if (findRow != null)
                {
                    Console.WriteLine("FUCK Successfully added to Customers table");
                }
            }
            else
            {
                Console.WriteLine("FUCK already present in database.");
            }

            adapter.Update(ds, "Customers");

            Console.WriteLine("# rows after change:{0}", ds.Tables["Customers"].Rows.Count);//当更新到数据库中后再一次查询总行数，检查是否更新成功

            cnn.Close();

            Console.ReadKey();
        }
    }
}

//重点：
//通过SqlDataAdapter对象的MissingSechamAction属性，显式的告诉ADO.NET加载主键信息，并且是在DataSet（数据填充）操作之前