- 存储过程------------------------------------------------------------------------------------------------
- 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();
- }
.Net调用存储过程(有传入、传出、返回值、记录集)及SqlDataAdapter、ExecuteNonQuery、SqlDataReader、ExecuteScalar
最新推荐文章于 2012-12-25 17:02:56 发布