命令行用SQL文本字符串和调用存储过程

App.config:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings >
   <add name="connstr" connectionString="Data Source=192.168.1.1;uid=sa;pwd=sa"Initial Catalog=mydb"/>
  </connectionStrings>
  
</configuration>

C# Code:

 

 static void Main(string[] args)
        {


            //检索记录操作

            //String connstr = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;

            //using (SqlConnection conn = new SqlConnection(connstr))
            //{

            //    conn.Open();

            //    using (SqlCommand cmd = new SqlCommand("GetStudentName1", conn))
            //    {

            //        cmd.CommandType = CommandType.StoredProcedure;
            //        cmd.Parameters.AddWithValue("@name", "丽丽");
            //        object i = cmd.ExecuteScalar();
            //        Console.WriteLine(i);
                  
                
                  
            //    }
            //}

 

            //插入记录操作
         
            //String connstr = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;

            //using (SqlConnection conn = new SqlConnection(connstr))
            //{

            //    conn.Open();

            //    using (SqlCommand cmd = new SqlCommand("PersonInsert", conn))
            //    {

            //        cmd.CommandType = CommandType.StoredProcedure;
            //        cmd.Parameters.AddWithValue("@ID", 23);
            //        cmd.Parameters.AddWithValue("@name", "lofd");
                 
            //        cmd.Parameters.AddWithValue("@age", 23);
            //        cmd.Parameters.AddWithValue("@salary", 3454);

            //        //SqlDataReader reader = cmd.ExecuteReader();
            //        //while (reader.Read())
            //        //{

            //        //    Console.WriteLine("ID:{0} \t名字：{1}\t 年龄：{2}\t 工资：{3}\t", reader[0], reader[1], reader[2], reader[3]);

            //        //}
                    
            //        //object i=cmd.ExecuteScalar();

                    
            //        cmd.ExecuteNonQuery();
            //        int i = (int)cmd.Parameters["@ID"].Value;
            //        Console.WriteLine(i);

                    

                   


                   
            //    }
            //}

 

            //}
            //更新记录操作
            //String connstr = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;

            //using (SqlConnection conn = new SqlConnection(connstr))
            //{

            //    conn.Open();

            //    using (SqlCommand cmd = new SqlCommand("GetPersonName", conn))
            //    {

            //        cmd.CommandType = CommandType.StoredProcedure;
            //        cmd.Parameters.AddWithValue("@number", 6);
            //        cmd.Parameters.AddWithValue("@name", "傅涌钦");
            //        cmd.ExecuteNonQuery();
            //    }
            //}


            //删除记录操作
            //String connstr = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;

            //using (SqlConnection conn = new SqlConnection(connstr))
            //{

            //    conn.Open();

            //    using (SqlCommand cmd = new SqlCommand("PersonDele", conn))
            //    {

            //        cmd.CommandType = CommandType.StoredProcedure;
            //        cmd.Parameters.AddWithValue("@number", 6);

            //        //{cmd.Parameters.Add(new SqlParameter("@number", SqlDbType.Int, 0, "number"));
            //        //cmd.UpdatedRowSource = UpdateRowSource.None;
            //        //cmd.Parameters["@number"].Value = 6;}

            //        cmd.ExecuteNonQuery();
            //    }
            //}

 


            //命令使用SQL字符串而不是存储过程进行ExcuteReader()
            //String connstr = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;

            //using (SqlConnection conn = new SqlConnection(connstr))
            //{

            //    conn.Open();

            //    using (SqlCommand cmd = conn.CreateCommand())
            //    {
            //        cmd.CommandText = "select * from person";
            //        SqlDataReader reader = cmd.ExecuteReader();
            //        while (reader.Read())
            //        {
            //            Console.WriteLine("ID:{0} \t名字：{1}\t 年龄：{2}\t 工资：{3}\t", reader[0], reader[1], reader[2], reader[3]);
            //        }
            //    }
            //}

 

            //使用ExcuteXmlReader()
            String connstr = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;

            using (SqlConnection conn = new SqlConnection(connstr))
            {

                conn.Open();

                using (SqlCommand cmd = conn.CreateCommand())
                {

                    cmd.CommandText = "select * " + "from [person] for XML AUTO";
                    XmlReader xr = cmd.ExecuteXmlReader();
                    xr.Read();
                    string data;
                    do
                    {
                        data = xr.ReadOuterXml();
                        if (!string.IsNullOrEmpty(data))
                            Console.WriteLine(data);
                    }
                    while (!string.IsNullOrEmpty(data));
                    conn.Close();

 


                }
            }

            
                          
                    
                    

                    
           
            Console.ReadKey();
        }