[C#]ADO.Net执行存储过程

存储过程————————————————————————————————


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();
    }

赞(0) 打赏
分享到: 更多 (0)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏