C#连接SqlServer并执行sql

SQLserver2008中创建存储过程，在查询试图（sql语句）中创建，系统会将sp存放在指定数据库的“可编程性”-〉“存储过程”中
建立存储过程searchDate
CREATE PROCEDURE [dbo].[searchDate]
@id varchar(255),
@name varchar(255)
AS
select * from table1
where id=@id And name=@name

public class CommonFileUtil   
   {   
      string coonString="server=.;database=MySchool(你的数据库名称);uid=sa(登录数据库时的名称);pwd=123(密码可为空)";
SqlConnection coon=new SqlConnection(coonString);
  
       private void CreateConnection(object sender, EventArgs e)   
       {   
           string rs = "";   
           SqlConnection conn = CreateConnection();   
           //创建SqlCommand的实例   
           SqlCommand cmd = conn.CreateCommand();   
           //sql语句   
           cmd.CommandText = "select id,name from table1";   
           //打开连接   
           conn.Open();   
           //执行sql   
           cmd.ExecuteNonQuery();   
           //若要创建 SqlDataReader，必须调用 SqlCommand 对象的 ExecuteReader 方法，而不要直接使用构造函数   
           SqlDataReader dr = cmd.ExecuteReader();   
           //有点类似于java的arrylist方式   
           while (dr.Read())   
           {   
               rs += dr["id"] + " " + dr["name"] + "\\n";   
           }   
           //关闭连接   
           conn.Close();   
       }   
  
       private void CallSP()   
       {   
           string rs = "";   
           SqlConnection conn = CreateConnection();   
           SqlCommand cmd = conn.CreateCommand();   
           //sp的名称   
           cmd.CommandText = "searchDate";   
           //sp的参数   
           cmd.Parameters.Add("@id", "234");   
           cmd.Parameters.Add("@name", "bcd");   
           //指定如何解释命令字符串，这里是存储过程   
           cmd.CommandType = CommandType.StoredProcedure;   
           conn.Open();   
           cmd.ExecuteNonQuery();   
           SqlDataReader dr = cmd.ExecuteReader();   
           while (dr.Read())   
           {   
               rs += dr["id"] + " " + dr["name"] + "\\n";   
           }   
           conn.Close();   
       }   
  
       private SqlConnection CreateConnection()   
       {   
           //连接本地数据库的连接字符窜   
           string str = "server=(local);database=testdb;user id=sa;pwd=00000000";   
           //如果是远程连接,则例:server=192.168.0.1;   
           //创建连接   
           SqlConnection conn = new SqlConnection(str);   
           return conn;   
       }   
   }  

 public class CommonFileUtil
    {

        private void CreateConnection(object sender, EventArgs e)
        {
            string rs = "";
            SqlConnection conn = CreateConnection();
            //创建SqlCommand的实例
            SqlCommand cmd = conn.CreateCommand();
            //sql语句
            cmd.CommandText = "select id,name from table1";
            //打开连接
            conn.Open();
            //执行sql
            cmd.ExecuteNonQuery();
            //若要创建 SqlDataReader，必须调用 SqlCommand 对象的 ExecuteReader 方法，而不要直接使用构造函数
            SqlDataReader dr = cmd.ExecuteReader();
            //有点类似于java的arrylist方式
            while (dr.Read())
            {
                rs += dr["id"] + " " + dr["name"] + "\\n";
            }
            //关闭连接
            conn.Close();
        }

        private void CallSP()
        {
            string rs = "";
            SqlConnection conn = CreateConnection();
            SqlCommand cmd = conn.CreateCommand();
            //sp的名称
            cmd.CommandText = "searchDate";
            //sp的参数
            cmd.Parameters.Add("@id", "234");
            cmd.Parameters.Add("@name", "bcd");
            //指定如何解释命令字符串，这里是存储过程
            cmd.CommandType = CommandType.StoredProcedure;
            conn.Open();
            cmd.ExecuteNonQuery();
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                rs += dr["id"] + " " + dr["name"] + "\\n";
            }
            conn.Close();
        }

        private SqlConnection CreateConnection()
        {
            //连接本地数据库的连接字符窜
            string str = "server=(local);database=testdb;user id=sa;pwd=00000000";
            //如果是远程连接,则例:server=192.168.0.1;
            //创建连接
            SqlConnection conn = new SqlConnection(str);
            return conn;
        }
    }

此外还可以采用xml保存连接信息 
建立过程如下: 
1.右击工程名->添加->新建项 
2.选择"应用程序配置文件",App.config文件 
3.在其中添加连接信息,如: 
Java代码 
<?xml version="1.0" encoding="utf-8" ?>   
<configuration>   
  <connectionStrings>   
    <add name="DB_Connection" connectionString="Data Source=(local);Initial Catalog=DB_NAME;Integrated Security=SSPI" providerName="System.Data.SqlClient"/>   
    <add name="REMOTE_DB_Connection" connectionString="Data Source=192.168.1.1;Initial Catalog=DB_DIS_BASE;" providerName="System.Data.SqlClient"/>   
  </connectionStrings>   
</configuration>  

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name="DB_Connection" connectionString="Data Source=(local);Initial Catalog=DB_NAME;Integrated Security=SSPI" providerName="System.Data.SqlClient"/>
    <add name="REMOTE_DB_Connection" connectionString="Data Source=192.168.1.1;Initial Catalog=DB_DIS_BASE;" providerName="System.Data.SqlClient"/>
  </connectionStrings>
</configuration>
4.注意其中远程连接不要用SSPI 
5.读取配置信息的代码: 
Java代码 
//strDataBaseName为需要连接的数据库连接字,即App.config中的DB_Connection或REMOTE_DB_Connection   
 private static SqlConnection CreateConnection(string strDataBaseName)   
        {   
            SqlConnection con = null;   
  
            try  
            {   
                //连接远程数据库的配置信息   
                string connectDBStr = System.Configuration.ConfigurationManager.ConnectionStrings[strDataBaseName].ConnectionString.ToString();   
                   
                //创建连接对象   
                con = new SqlConnection(connectDBStr);   
                   
                //返回对象   
                return con;   
            }   
            catch(Exception ex)    
            {   
                //抛出异常   
                throw new Exception(string.Format("创建数据库连接处理中发生异常,获取{0}的配置信息出错", strDataBaseName));   
            }   
        }  

//strDataBaseName为需要连接的数据库连接字,即App.config中的DB_Connection或REMOTE_DB_Connection
 private static SqlConnection CreateConnection(string strDataBaseName)
        {
            SqlConnection con = null;

            try
            {
                //连接远程数据库的配置信息
                string connectDBStr = System.Configuration.ConfigurationManager.ConnectionStrings[strDataBaseName].ConnectionString.ToString();
                
                //创建连接对象
                con = new SqlConnection(connectDBStr);
                
                //返回对象
                return con;
            }
            catch(Exception ex) 
            {
                //抛出异常
                throw new Exception(string.Format("创建数据库连接处理中发生异常,获取{0}的配置信息出错", strDataBaseName));
            }
        }

最后一点初学的心得,在连接数据库后如果反复使用SqlDataReader dr = cmd.ExecuteReader()来读取不同表的数据时,在读取完第一个表dr.Read()后记得将dr关闭,即:dr.Close(),再读取下一张表