一个简单例子
<%@ Page Language="C#" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>
<script runat="server"> void Page_Load(object sender, EventArgs e) {
// TODO: Update the ConnectionString for your application
string ConnectionString = "server=(local);database=Northwind;trusted_connection=true";
// TODO: Updatd the name of the Stored Procedure for your application
string CommandText = "CustOrdersDetail";
SqlConnection myConnection = new SqlConnection(ConnectionString);
SqlCommand myCommand = new SqlCommand(CommandText, myConnection);
SqlParameter workParam;
myCommand.CommandType = CommandType.StoredProcedure;
// TODO: Set the input parameter, if necessary, for your application
myCommand.Parameters.Add("@OrderId", SqlDbType.Int).Value = 11077;
myConnection.Open();
DataGrid1.DataSource = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
DataGrid1.DataBind();
}</script>
<html>
<head>
</head>
<body style="FONT-FAMILY: arial">
<h2>Simple Stored Procedure
</h2>
<hr size="1" />
<form runat="server">
<asp:datagrid id="DataGrid1" runat="server" CellSpacing="1" GridLines="None" CellPadding="3" BackColor="White" ForeColor="Black" EnableViewState="False">
<HeaderStyle font-bold="True" forecolor="white" backcolor="#4A3C8C"></HeaderStyle>
<ItemStyle backcolor="#DEDFDE"></ItemStyle>
</asp:datagrid>
</form>
</body>
</html>
<%@ Page Language="C#" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>
<script runat="server"> void Page_Load(object sender, EventArgs e) {
// TODO: Update the ConnectionString for your application
string ConnectionString = "server=(local);database=Northwind;trusted_connection=true";
// TODO: Updatd the name of the Stored Procedure for your application
string CommandText = "CustOrdersDetail";
SqlConnection myConnection = new SqlConnection(ConnectionString);
SqlCommand myCommand = new SqlCommand(CommandText, myConnection);
SqlParameter workParam;
myCommand.CommandType = CommandType.StoredProcedure;
// TODO: Set the input parameter, if necessary, for your application
myCommand.Parameters.Add("@OrderId", SqlDbType.Int).Value = 11077;
myConnection.Open();
DataGrid1.DataSource = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
DataGrid1.DataBind();
}</script>
<html>
<head>
</head>
<body style="FONT-FAMILY: arial">
<h2>Simple Stored Procedure
</h2>
<hr size="1" />
<form runat="server">
<asp:datagrid id="DataGrid1" runat="server" CellSpacing="1" GridLines="None" CellPadding="3" BackColor="White" ForeColor="Black" EnableViewState="False">
<HeaderStyle font-bold="True" forecolor="white" backcolor="#4A3C8C"></HeaderStyle>
<ItemStyle backcolor="#DEDFDE"></ItemStyle>
</asp:datagrid>
</form>
</body>
</html>
{
SqlConnection MyConn = new SqlConnection(MyConnString);
try{
MyConn.Open();
SqlCommand mdo = new SqlCommand("person_manager_add",MyConn);
mdo.CommandType = CommandType.StoredProcedure;
mdo.Parameters.Add("@employee_id",employee_id.Text.Trim());
mdo.Parameters.Add("@name",name.Text.Trim());
mdo.Parameters.Add("@sch_area",sch_area.SelectedValue.Trim());
mdo.Parameters.Add("@gender",gender.SelectedValue.Trim());
mdo.Parameters.Add("@native",native.Text.Trim());
mdo.Parameters.Add("@birthday",birthday.Text.Trim());
mdo.Parameters.Add("@work_time",work_time.Text.Trim());
mdo.Parameters.Add("@tech_post",tech_post.SelectedValue.Trim());
mdo.Parameters.Add("@engage_time",engage_time.Text.Trim());
mdo.Parameters.Add("@duty",duty.Text.Trim());
mdo.Parameters.Add("@tel",tel.Text.Trim());
mdo.Parameters.Add("@email",email.Text.Trim());
mdo.Parameters.Add("@re",re.Text.Trim());
mdo.ExecuteNonQuery();
}
catch(System.Exception err)
{
Response.Write(err.ToString());
Response.Write("<script>window.alert('开启数据库出现错误!');window.location='person.aspx'</script>");
}
finally{ MyConn.Close();}
Response.Redirect("alert.aspx?msg=添加员工资料成功!&url=person.aspx");
}person_manager_add是存储过程的名字,就ok了啊。
this.sqlConnection1 = new System.Data.SqlClient.SqlConnection();
this.sqlInsertCommand1 = new System.Data.SqlClient.SqlCommand();
this.sqlUpdateCommand1 = new System.Data.SqlClient.SqlCommand();
this.sqlDeleteCommand1 = new System.Data.SqlClient.SqlCommand();
this.sqlDataAdapter1 = new System.Data.SqlClient.SqlDataAdapter();
this.JjstarDataGrid1.DblClick += new System.Web.UI.WebControls.DataGridItemEventHandler(this.dblclick);
this.JjstarDataGrid1.Click += new System.Web.UI.WebControls.DataGridItemEventHandler(this.click);
this.JjstarDataGrid1.Scroll2Top += new System.EventHandler(this.doScrollTop);
this.JjstarDataGrid1.Scroll2Foot += new System.EventHandler(this.doScroll2);
//
// sqlSelectCommand1
//
this.sqlSelectCommand1.CommandText = "SELECT au_id, au_lname, au_fname, phone, address, city, state, zip, contract FROM" +
" authors";
this.sqlSelectCommand1.Connection = this.sqlConnection1;
//
// sqlConnection1
//
this.sqlConnection1.ConnectionString = "data source=LX4600103;initial catalog=pubs;persist security info=False;user id=sa" +
";workstation id=LX4600103;packet size=4096";
//
// sqlInsertCommand1
//
this.sqlInsertCommand1.CommandText = @"INSERT INTO authors(au_id, au_lname, au_fname, phone, address, city, state, zip, contract) VALUES (@au_id, @au_lname, @au_fname, @phone, @address, @city, @state, @zip, @contract); SELECT au_id, au_lname, au_fname, phone, address, city, state, zip, contract FROM authors WHERE (au_id = @au_id)";
this.sqlInsertCommand1.Connection = this.sqlConnection1;
this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@au_id", System.Data.SqlDbType.VarChar, 11, "au_id"));
this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@au_lname", System.Data.SqlDbType.VarChar, 40, "au_lname"));
this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@au_fname", System.Data.SqlDbType.VarChar, 20, "au_fname"));
this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@phone", System.Data.SqlDbType.VarChar, 12, "phone"));
this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@address", System.Data.SqlDbType.VarChar, 40, "address"));
this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@city", System.Data.SqlDbType.VarChar, 20, "city"));
this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@state", System.Data.SqlDbType.VarChar, 2, "state"));
this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@zip", System.Data.SqlDbType.VarChar, 5, "zip"));
this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@contract", System.Data.SqlDbType.Bit, 1, "contract"));
create procedure myProcedure //procedure是存储过程的意思
@myParam varchar(50) //声明存储过程中的变量,并为变量指定类型
as
select * from myTable where id=@myParam 使用存储过程
SqlConnection conn=new SqlConnection(constr);
SqlCommand comd=new SqlCommand("myProcedure",conn);
comd.CommandType=CommandType.StoredProcedure; //告诉程序,你使用的是存储过程SqlParameter param0=comd.Parameters.Add("@myParam",SqlDbType.VarChar,50);
//在comd的参数集合中加入存储过程的参数
param0.Value=xxx; //将一个值赋予这个参数comd.ExecuteNonQuery(); //最后执行就OK了
SqlCommand cmd = CreateCommand(procName, null);
return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
}private SqlCommand CreateCommand(string procName, SqlParameter[] prams) {
// make sure connection is open
Open(); //command = new SqlCommand( sprocName, new SqlConnection( ConfigManager.DALConnectionString ) );
SqlCommand cmd = new SqlCommand(procName, con);
cmd.CommandType = CommandType.StoredProcedure; // add proc parameters
if (prams != null) {
foreach (SqlParameter parameter in prams)
cmd.Parameters.Add(parameter);
}
// return param
cmd.Parameters.Add(
new SqlParameter("ReturnValue", SqlDbType.Int, 4,
ParameterDirection.ReturnValue, false, 0, 0,
string.Empty, DataRowVersion.Default, null)); return cmd;
}
inscmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@id", System.Data.SqlDbType.VarChar, 50, "id"));mycomm.Parameters ["@id"].Value=txtcode.text.tostring();也可以这样updcmd.Parameters.Add("@id",this.txtcode.Text);当然还要有 在存储过程中也要声明
create procedure select1 //procedure是存储过程的意思
@xibie_id int, @nianji_id int //声明存储过程中的变量,并为变量指定类型
as
select total from daima where xibie_id=@xibie_id and nianji_id=@nianji_id
//使用存储过程
SqlConnection conn=new SqlConnection("Data Source=localhost;Initial Catalog=SFXT;User ID=sa;Password=;");
conn.Open(); SqlCommand cmd2 =new SqlCommand("select1 ",conn);
cmd2.Parameters.Add(new SqlParameter("@xibie_id",SqlDbType.Int));
cmd2.Parameters.Add(new SqlParameter("@nianji_id",SqlDbType.Int)); cmd2.Parameters["@xibie_id"].Value=ddlxibie.SelectedItem.ToString();
cmd2.Parameters["@nianji_id"].Value=ddlnianji.SelectedItem.ToString();
txtyingjiao.Text=cmd2.ExecuteScalar().ToString();
conn.Close();