.Net调用存储过程(有传入、传出、返回值、记录集)及SqlDataAdapter、ExecuteNonQuery、SqlDataReader、ExecuteScalar...

 

存储过程 ------------------------------------------------------------------------------------------------


CREATE PROCEDURE dbo.sptestList

@strOptions varchar( 200 )  =  NULL,
@intID  int   =  NULL,
@strOut nvarchar( 50 )  =  NULL   OUTPUT

AS

SET NOCOUNT ON -- 不返回影响的行数
SET ANSI_WARNINGS OFF -- 不返警告

/**/ /* 信息列表 */
IF @strOptions = ' LIST '  BEGIN

SELECT
   id,
   name,
   subject,
   Source
FROM test 
WHERE ID  <= @intID

SET @strOut  =  @strOut  +   ' out '
RETURN  50
END
GO


-------------------------------------------------------------------------

// StoredProcedure 调用存储过程
     private   void  BindData2()
     {
        SqlConnection MyConn = new SqlConnection("Server=.;Database=mine;User=sa;Pwd=123;");
        MyConn.Open();

        SqlCommand MyCmd = new SqlCommand();
        MyCmd.Connection = MyConn;
        MyCmd.CommandType = CommandType.StoredProcedure;
        MyCmd.CommandText = "sptestList";
        string strOut = "in---";
        int intReturn = 0;

        SqlParameter[] paras = 
            {
                new SqlParameter("@strOptions","LIST"),
                new SqlParameter("@intID",100),
                //长度一定需要
                new SqlParameter("@strOut",SqlDbType.VarChar,200),
                //用来存返回值，ReturnValue随便写都可以，但类型一定是整型（因为数据库只能返回整型）
                new SqlParameter("ReturnValue",SqlDbType.Int)
            
            };
      
        paras[2].Direction = ParameterDirection.InputOutput;//设置 传入并传出
        paras[2].Value = strOut;
        paras[3].Direction = ParameterDirection.ReturnValue;//设置 返回值


        foreach (SqlParameter par in paras)
        {
            MyCmd.Parameters.Add(par);
        }

        SqlDataAdapter MySda = new SqlDataAdapter(MyCmd);
        DataTable MyDtb = new DataTable();
        MySda.Fill(MyDtb);

        strOut = paras[2].Value.ToString();//接收传出
        intReturn = (int)paras[3].Value;//接收返回值
        Response.Write("Out=" + strOut + "； Return=" + intReturn.ToString() + "<br>");

        this.Repeater1.DataSource = MyDtb;
        this.Repeater1.DataBind();

        MyConn.Close();

    }

     // SqlDataAdapter 执行查询语句
     private   void  BindData()
     {
        SqlConnection MyConn = new SqlConnection("Server=.;Database=mine;User=sa;Pwd=123;");

        SqlCommand MyCmd = new SqlCommand("SELECT TOP 100 * FROM Test", MyConn);
        SqlDataAdapter MySda = new SqlDataAdapter(MyCmd);
        DataTable MyDtb = new DataTable();
        MySda.Fill(MyDtb);

        this.Repeater1.DataSource = MyDtb;
        this.Repeater1.DataBind();

    }

     // ExecuteNonQuery 执行插入语句
     protected   void  FInsert()
     {
        SqlConnection MyConn = new SqlConnection("Server=.;DataBase=mine;Uid=sa;Pwd=123;");

        SqlCommand MyCmd = new SqlCommand("INSERT INTO StudentInfo(StNo,StName) VALUES(124532,'A111ww')", MyConn);
        MyConn.Open();
        int i = MyCmd.ExecuteNonQuery();
        Response.Write(i.ToString() + "<br>");
        MyConn.Close();

    }

     // SqlDataReader
     protected   void  FReader()
     {
        SqlConnection MyConn = new SqlConnection("Server=.;Database=mine;Uid=sa;Pwd=123");
        MyConn.Open();
        SqlCommand MyCmd = new SqlCommand("Select Top 10 StNo,StName from StudentInfo", MyConn);

        SqlDataReader MyRead = MyCmd.ExecuteReader();

        while (MyRead.Read())
        {
            Response.Write(MyRead["StNo"].ToString() + "--" + MyRead["StName"].ToString() + "<br>");
        }

        MyConn.Close();

    }

     // ExecuteScalar
     protected   void  FScalar()
     {

        //<appSettings>
        //<add key ="SqlConnStr" value="Server=.;DataBase=mine;Uid=sa;Pwd=123"/>
        //</appSettings>
        //SqlConnection MyConn = new SqlConnection(ConfigurationManager.AppSettings["SqlConnStr"]);
        SqlConnection MyConn = new SqlConnection("Server=.;Database=mine;Uid=sa;Pwd=123");
        MyConn.Open();
        SqlCommand MyCmd = new SqlCommand("Select Total=Count(1) from StudentInfo", MyConn);

        string MyS = MyCmd.ExecuteScalar().ToString();

        Response.Write(MyS + "<br>");

        MyConn.Close();

    }

// 连接Access
     private   void  BindAccess()
     {
        //<add key="AccessConString" value="App_Data/db.mdb" />
        string strAccessPath = ConfigurationManager.AppSettings["AccessConString"].ToString();
        OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(strAccessPath));
        con.Open();
        OleDbDataAdapter oda = new OleDbDataAdapter("Select * From Result", con);
        DataTable Dtb = new DataTable();
        oda.Fill(Dtb);

        this.Repeater1.DataSource = Dtb;
        this.Repeater1.DataBind();

        //----------------------二-----------------------------
        //string constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("App_Data/db.mdb");
        //OleDbConnection con = new OleDbConnection(constr);
        //string cmdstr = "SELECT * FROM Result";
        //con.Open();
        //OleDbCommand cmd = new OleDbCommand(cmdstr, con);
        //OleDbDataReader dr = cmd.ExecuteReader();
    }


// OleDbCommand　执行语句
     protected   void  Button1_Click( object  sender, EventArgs e)
     {
        OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source="
            + Server.MapPath(ConfigurationManager.AppSettings["OleDbConnPathStr"]));
        con.Open();

        OleDbCommand cmd = new OleDbCommand("Insert Into Products(Product_Id,Title,Spec,Content) Values('ww123','成功添加01','100*1','真的成功了')", con);

        cmd.ExecuteNonQuery();

        con.Close();
        con.Dispose();

        BindData();

    }

     // OleDbDataAdapter　读取数据
     protected   void  Button2_Click( object  sender, EventArgs e)
     {
        OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source="
            + Server.MapPath(ConfigurationManager.AppSettings["OleDbConnPathStr"]));
        con.Open();

        OleDbDataAdapter odad = new OleDbDataAdapter("Select * From Products Where ID=123", con);

        DataTable dtb = new DataTable();
        odad.Fill(dtb);

        if (dtb.Rows.Count > 0)
        {
            DataRow row = dtb.Rows[0];

            this.Label.Text = row["ID"].ToString() + "---" + row["Title"].ToString();
        }

        con.Close();
        con.Dispose();
    }

     // OleDbCommand　执行语句(参数化)
     protected   void  Button3_Click( object  sender, EventArgs e)
     {
        OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source="
            + Server.MapPath(ConfigurationManager.AppSettings["OleDbConnPathStr"]));
        con.Open();

        OleDbCommand cmd = new OleDbCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "Insert Into Products(Product_Id,Title,Spec,Content) Values(@Product_Id,@Title,@Spec,@Content)";

        OleDbParameter[] paras = {
            new OleDbParameter("@Product_Id",OleDbType.VarChar),
            new OleDbParameter("@Title",OleDbType.VarChar),
            new OleDbParameter("@Spec",OleDbType.VarChar),
            new OleDbParameter("@Content",OleDbType.VarChar)
        };

        paras[0].Value = "qq123";
        paras[1].Value = "qq成功添加";
        paras[2].Value = "10*10";
        paras[3].Value = "qq真的成功了";

        foreach (OleDbParameter para in paras)
        {
            cmd.Parameters.Add(para);
        }

        cmd.ExecuteNonQuery();

        con.Close();
        con.Dispose();

        BindData();
    }

转载于:https://www.cnblogs.com/84ww/archive/2008/04/09/1144410.html