class AdoDemo
    {
        static string strConn = @"Data Source=server1;Initial Catalog=NameCard;Integrated Security=False;User ID=sa;Password=******;Connect Timeout=15;Encrypt=False;TrustServerCertificate=False";
        static void Main(string[] args)
        {
            //InsertData();//1.1插入新数据练习
            //InsertDataParameters();//1.2插入新数据练习,Parameters方法
            //updataData();//更新数据
            //DelData();//删除数据
            //QueryDataByDs();//使用dataadapter,dataset查询数据
            //QueryByDr();//使用dataread查询数据
            //querybyDt();//使用datatable查询数据
            Console.ReadKey();
        }

        #region 1.1插入新数据
        private static void InsertData()
        {
            using (SqlConnection connection = new SqlConnection(strConn))
            {
                connection.Open();
                using (SqlCommand sqlcmd = connection.CreateCommand())
                {
                    string strCmd = "insert into Customer (region,Name,Department) values (‘jinan‘,‘li‘,‘shandong‘)";
                    sqlcmd.CommandText = strCmd;
                    if (sqlcmd.ExecuteNonQuery() > 0)
                    {
                        Console.WriteLine("插入成功");
                    }
                    else
                    {
                        Console.WriteLine("插入失败");
                    }
                }
            }
        }
        #endregion

        #region 1.2插入新数据,Parameters方法
        private static void InsertDataParameters()
        {
            using (SqlConnection connection = new SqlConnection(strConn))
            {
                connection.Open();
                using (SqlCommand sqlCmd = connection.CreateCommand())
                {
                    string sqlStr = "insert into Customer (region,Name,Department) values (@region,@name,@depart)";
                    sqlCmd.CommandText = sqlStr;
                    sqlCmd.Parameters.Add("@region", SqlDbType.NVarChar);
                    sqlCmd.Parameters["@region"].Value = "Sichuan";
                    sqlCmd.Parameters.AddWithValue("@name", "Chong");
                    sqlCmd.Parameters.AddWithValue("@depart", "qing");
                    if (sqlCmd.ExecuteNonQuery() > 0)
                    {
                        Console.WriteLine("插入成功");
                    }
                    else
                    {
                        Console.WriteLine("插入失败");
                    }
                }
            }
        }
        #endregion

        #region 2 修改数据练习
        private static void updataData()
        {
            using (SqlConnection connection = new SqlConnection(strConn))
            {
                connection.Open();
                using (SqlCommand sqlCmd = connection.CreateCommand())
                {
                    string cmdStr = "update Customer set Name=‘@name‘ where id=‘2845‘";
                    sqlCmd.CommandText = cmdStr;
                    if (sqlCmd.ExecuteNonQuery() > 0)
                    {
                        Console.WriteLine("插入成功");
                    }
                    else
                    {
                        Console.WriteLine("插入失败");
                    }
                }
            }
        }
        #endregion

        #region 3 删除数据 练习
        private static void DelData()
        {
            using (SqlConnection connection = new SqlConnection(strConn))
            {
                connection.Open();
                using (SqlCommand sqlcmd = connection.CreateCommand())
                {
                    string cmdStr = "delete from Customer where id >=‘2840‘";
                    sqlcmd.CommandText = cmdStr;
                    if (sqlcmd.ExecuteNonQuery() > 0)
                    {
                        Console.WriteLine("删除成功");
                    }
                    else
                    {
                        Console.WriteLine("删除失败");
                    }
                }
            }
        }
        #endregion

        #region 4.1 使用dataadapter,dataset查询数据
        private static void QueryDataByDs()
        {
            using (SqlConnection connection = new SqlConnection(strConn))
            {
                connection.Open();
                string cmdStr = "select top 20 * from Customer";
                SqlDataAdapter sqlda = new SqlDataAdapter(cmdStr, strConn);
                DataSet ds = new DataSet();
                sqlda.Fill(ds, "customer");
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    Console.WriteLine("ID={0},region={1},department={2},name={3},phone1={4}", dr[0], dr[1], dr[2], dr[3], dr[4]);
                }
            }
        }
        #endregion

        #region 4.2 使用datareader查询数据
        private static void QueryByDr()
        {
            using (SqlConnection connection = new SqlConnection(strConn))
            {
                connection.Open();
                using (SqlCommand sqlCmd = connection.CreateCommand())
                {
                    string cmdStr = "select top 10 * from Customer";
                    sqlCmd.CommandText = cmdStr;
                    SqlDataReader sqlDr = sqlCmd.ExecuteReader();
                    while (sqlDr.HasRows)//是否返回数据
                    {
                        while (sqlDr.Read())//从第一行开始顺序读取数据集到最后一行
                        {
                            Console.WriteLine("ID={0},region={1},department={2},name={3},phone1={4}", sqlDr[0].ToString(), sqlDr[1].ToString(), sqlDr[2].ToString(), sqlDr[3].ToString(), sqlDr[4].ToString());
                        }
                    }
                }
            }
        }
        #endregion

        #region 4.3 使用datatable查询数据
        private static void querybyDt()
        {
            SqlConnection connection = new SqlConnection(strConn);
            connection.Open();
            string cmdStr = "select top 10 * from Customer";
            SqlDataAdapter sqlda = new SqlDataAdapter(cmdStr,connection);
            DataTable dt = new DataTable();
            sqlda.Fill(dt);
            foreach (DataRow item in dt.Rows)
            {
                Console.WriteLine("{0},{1},{2}", item[0].ToString(), item[1].ToString(), item[2].ToString());
            }
            connection.Close();
        }
        #endregion
    }