存储过程
------------------------------------------------------------------------------------------------
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
CREATE PROCEDURE dbo.sptestList
![](/Images/OutliningIndicators/None.gif)
@strOptions varchar(
200
)
=
NULL,
@intID
int
=
NULL,
@strOut nvarchar(
50
)
=
NULL OUTPUT
![](/Images/OutliningIndicators/None.gif)
AS
![](/Images/OutliningIndicators/None.gif)
SET NOCOUNT ON
--
不返回影响的行数
SET ANSI_WARNINGS OFF
--
不返警告
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**/
/* 信息列表 */
IF @strOptions
=
'
LIST
'
BEGIN
![](/Images/OutliningIndicators/None.gif)
SELECT
id,
name,
subject,
Source
FROM test
WHERE ID
<=
@intID
![](/Images/OutliningIndicators/None.gif)
SET @strOut
=
@strOut
+
'
out
'
RETURN
50
END
GO
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
-------------------------------------------------------------------------
![](/Images/OutliningIndicators/None.gif)
//
StoredProcedure 调用存储过程
private
void
BindData2()
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
SqlConnection MyConn = new SqlConnection("Server=.;Database=mine;User=sa;Pwd=123;");
MyConn.Open();
![](/Images/OutliningIndicators/InBlock.gif)
SqlCommand MyCmd = new SqlCommand();
MyCmd.Connection = MyConn;
MyCmd.CommandType = CommandType.StoredProcedure;
MyCmd.CommandText = "sptestList";
string strOut = "in---";
int intReturn = 0;
![](/Images/OutliningIndicators/InBlock.gif)
SqlParameter[] paras =
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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;//设置 返回值
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
foreach (SqlParameter par in paras)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
MyCmd.Parameters.Add(par);
}
![](/Images/OutliningIndicators/InBlock.gif)
SqlDataAdapter MySda = new SqlDataAdapter(MyCmd);
DataTable MyDtb = new DataTable();
MySda.Fill(MyDtb);
![](/Images/OutliningIndicators/InBlock.gif)
strOut = paras[2].Value.ToString();//接收传出
intReturn = (int)paras[3].Value;//接收返回值
Response.Write("Out=" + strOut + "; Return=" + intReturn.ToString() + "<br>");
![](/Images/OutliningIndicators/InBlock.gif)
this.Repeater1.DataSource = MyDtb;
this.Repeater1.DataBind();
![](/Images/OutliningIndicators/InBlock.gif)
MyConn.Close();
![](/Images/OutliningIndicators/InBlock.gif)
}
![](/Images/OutliningIndicators/None.gif)
//
SqlDataAdapter 执行查询语句
private
void
BindData()
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
SqlConnection MyConn = new SqlConnection("Server=.;Database=mine;User=sa;Pwd=123;");
![](/Images/OutliningIndicators/InBlock.gif)
SqlCommand MyCmd = new SqlCommand("SELECT TOP 100 * FROM Test", MyConn);
SqlDataAdapter MySda = new SqlDataAdapter(MyCmd);
DataTable MyDtb = new DataTable();
MySda.Fill(MyDtb);
![](/Images/OutliningIndicators/InBlock.gif)
this.Repeater1.DataSource = MyDtb;
this.Repeater1.DataBind();
![](/Images/OutliningIndicators/InBlock.gif)
}
![](/Images/OutliningIndicators/None.gif)
//
ExecuteNonQuery 执行插入语句
protected
void
FInsert()
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
SqlConnection MyConn = new SqlConnection("Server=.;DataBase=mine;Uid=sa;Pwd=123;");
![](/Images/OutliningIndicators/InBlock.gif)
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();
![](/Images/OutliningIndicators/InBlock.gif)
}
![](/Images/OutliningIndicators/None.gif)
//
SqlDataReader
protected
void
FReader()
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
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);
![](/Images/OutliningIndicators/InBlock.gif)
SqlDataReader MyRead = MyCmd.ExecuteReader();
![](/Images/OutliningIndicators/InBlock.gif)
while (MyRead.Read())
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
Response.Write(MyRead["StNo"].ToString() + "--" + MyRead["StName"].ToString() + "<br>");
}
![](/Images/OutliningIndicators/InBlock.gif)
MyConn.Close();
![](/Images/OutliningIndicators/InBlock.gif)
}
![](/Images/OutliningIndicators/None.gif)
//
ExecuteScalar
protected
void
FScalar()
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
![](/Images/OutliningIndicators/InBlock.gif)
//<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);
![](/Images/OutliningIndicators/InBlock.gif)
string MyS = MyCmd.ExecuteScalar().ToString();
![](/Images/OutliningIndicators/InBlock.gif)
Response.Write(MyS + "<br>");
![](/Images/OutliningIndicators/InBlock.gif)
MyConn.Close();
![](/Images/OutliningIndicators/InBlock.gif)
}
![](/Images/OutliningIndicators/None.gif)
//
连接Access
private
void
BindAccess()
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
//<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);
![](/Images/OutliningIndicators/InBlock.gif)
this.Repeater1.DataSource = Dtb;
this.Repeater1.DataBind();
![](/Images/OutliningIndicators/InBlock.gif)
//----------------------二-----------------------------
//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();
}
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
//
OleDbCommand 执行语句
protected
void
Button1_Click(
object
sender, EventArgs e)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source="
+ Server.MapPath(ConfigurationManager.AppSettings["OleDbConnPathStr"]));
con.Open();
![](/Images/OutliningIndicators/InBlock.gif)
OleDbCommand cmd = new OleDbCommand("Insert Into Products(Product_Id,Title,Spec,Content) Values('ww123','成功添加01','100*1','真的成功了')", con);
![](/Images/OutliningIndicators/InBlock.gif)
cmd.ExecuteNonQuery();
![](/Images/OutliningIndicators/InBlock.gif)
con.Close();
con.Dispose();
![](/Images/OutliningIndicators/InBlock.gif)
BindData();
![](/Images/OutliningIndicators/InBlock.gif)
}
![](/Images/OutliningIndicators/None.gif)
//
OleDbDataAdapter 读取数据
protected
void
Button2_Click(
object
sender, EventArgs e)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source="
+ Server.MapPath(ConfigurationManager.AppSettings["OleDbConnPathStr"]));
con.Open();
![](/Images/OutliningIndicators/InBlock.gif)
OleDbDataAdapter odad = new OleDbDataAdapter("Select * From Products Where ID=123", con);
![](/Images/OutliningIndicators/InBlock.gif)
DataTable dtb = new DataTable();
odad.Fill(dtb);
![](/Images/OutliningIndicators/InBlock.gif)
if (dtb.Rows.Count > 0)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
DataRow row = dtb.Rows[0];
![](/Images/OutliningIndicators/InBlock.gif)
this.Label.Text = row["ID"].ToString() + "---" + row["Title"].ToString();
}
![](/Images/OutliningIndicators/InBlock.gif)
con.Close();
con.Dispose();
}
![](/Images/OutliningIndicators/None.gif)
//
OleDbCommand 执行语句(参数化)
protected
void
Button3_Click(
object
sender, EventArgs e)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source="
+ Server.MapPath(ConfigurationManager.AppSettings["OleDbConnPathStr"]));
con.Open();
![](/Images/OutliningIndicators/InBlock.gif)
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)";
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
OleDbParameter[] paras =
{
new OleDbParameter("@Product_Id",OleDbType.VarChar),
new OleDbParameter("@Title",OleDbType.VarChar),
new OleDbParameter("@Spec",OleDbType.VarChar),
new OleDbParameter("@Content",OleDbType.VarChar)
};
![](/Images/OutliningIndicators/InBlock.gif)
paras[0].Value = "qq123";
paras[1].Value = "qq成功添加";
paras[2].Value = "10*10";
paras[3].Value = "qq真的成功了";
![](/Images/OutliningIndicators/InBlock.gif)
foreach (OleDbParameter para in paras)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
cmd.Parameters.Add(para);
}
![](/Images/OutliningIndicators/InBlock.gif)
cmd.ExecuteNonQuery();
![](/Images/OutliningIndicators/InBlock.gif)
con.Close();
con.Dispose();
![](/Images/OutliningIndicators/InBlock.gif)
BindData();
}
转载于:https://www.cnblogs.com/84ww/archive/2008/04/09/1144410.html