Mikel
存储过程————————————————————————————————
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();
}
