C#操作各种执行sql的方法含存储过程操作

using System; using System.Collections.Generic; using System.Text; using System.Data.SqlClient; using System.Xml; using System.Data; namespace MyDbTest { class Program { static void Main(string[] args) { SqlConnection thisConnection = new SqlConnection( @"Data Source=localhost;Initial Catalog=CSGL;Persist Security Info=True;User ID=test;Password=test"); thisConnection.Open(); SqlCommand myCommand = new SqlCommand("P_Test", thisConnection); myCommand.CommandType = CommandType.StoredProcedure; //添加输入查询参数、赋予值 myCommand.Parameters.Add("@id", SqlDbType.Int); myCommand.Parameters["@id"].Value = "120"; //添加输出参数 myCommand.Parameters.Add("@Rowcount", SqlDbType.Int); myCommand.Parameters["@Rowcount"].Direction = ParameterDirection.Output; myCommand.ExecuteNonQuery(); //得到存储过程输出参数 Console.WriteLine(" 存储过程的参数"+ myCommand.Parameters["@Rowcount"].Value.ToString()); thisConnection.Close(); Console.ReadLine(); //SqlCommand thisCommand = thisConnection.CreateCommand(); //thisCommand.CommandText = "select count(*) from stu"; ////ExecuteScalar：执行只返回一个值的SQL命令。 //object countResult = thisCommand.ExecuteScalar(); //Console.WriteLine("Count of Customers={0}", countResult); //thisConnection.Close(); //Console.ReadLine(); //SqlCommand thisCommand = thisConnection.CreateCommand(); //thisCommand.CommandText = "update stu set snm='haha' where id=120"; ////Inset,Update,Delelte的数据修改操作也不返回任何数据， ////我们对这些命令感兴趣的是修改操作影响的行数，可以用ExecuteNonQuery()方法 //int rowsAffected = thisCommand.ExecuteNonQuery(); //Console.WriteLine("Rows Updated={0}", rowsAffected); //thisConnection.Close(); //Console.ReadLine(); //SqlCommand thisCommand = thisConnection.CreateCommand(); //thisCommand.CommandText = "select * from stu FOR XML AUTO,root('RPG')"; ////Inset,Update,Delelte的数据修改操作也不返回任何数据， ////我们对这些命令感兴趣的是修改操作影响的行数，可以用ExecuteNonQuery()方法 //XmlReader xml = thisCommand.ExecuteXmlReader(); //XmlDocument xmltext = new XmlDocument(); //xmltext.Load(xml); //while (xml.Read()) //{ // Console.Write("Element: " + xml.Name); // if (xml.HasAttributes) // { // for (int i = 0; i < xml.AttributeCount; i++) // { // xml.MoveToAttribute(i); // Console.Write(" {0}: {1}", xml.Name, xml.Value); // } // xml.MoveToElement(); // } //} //Console.WriteLine(xmltext.InnerXml); //thisConnection.Close(); //Console.ReadLine(); /*** //// 存储过程------------------------------ SqlCommand thisCommand = thisConnection.CreateCommand(); //命令类型为存储过程 thisCommand.CommandType = CommandType.StoredProcedure; //存储过程名称 thisCommand.CommandText = "Ten Most Expensive Products"; //执行存储过程 SqlDataReader thisReader = thisCommand.ExecuteReader(); //显示结果 while (thisReader.Read()) { Console.WriteLine("/t{0}/t{1}", thisReader["TenMostExpensiveProducts"], thisReader["UnitPrice"]); } thisReader.Close(); thisConnection.Close(); Console.ReadLine(); * ***/ //SqlCommand thisCommand = thisConnection.CreateCommand(); ////命令类型为存储过程 //thisCommand.CommandType = CommandType.StoredProcedure; ////存储过程名称 //thisCommand.CommandText = "GetAll"; ////执行存储过程 //SqlDataReader thisReader = thisCommand.ExecuteReader(); ////显示结果 //while (thisReader.Read()) //{ // Console.WriteLine("/t{0}/t{1}", thisReader["TenMostExpensiveProducts"], thisReader["UnitPrice"]); //} //thisReader.Close(); //thisConnection.Close(); //Console.ReadLine(); } } }