/// <summary>

/// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)

/// </summary>

/// <param name="connection">数据库连接</param>

/// <param name="storedProcName">存储过程名</param>

/// <param name="parameters">存储过程参数</param>

/// <returns>SqlCommand</returns>

private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)

{

SqlCommand command = new SqlCommand(storedProcName, connection);

command.CommandType = CommandType.StoredProcedure;

foreach (SqlParameter parameter in parameters)

{

if (parameter != null)

{

// 检查未分配值的输出参数,将其分配以DBNull.Value.

if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&

(parameter.Value == null))

{

parameter.Value = DBNull.Value;

}

command.Parameters.Add(parameter);

}

}

return command;

}

一、 //返回阅读器

/// <summary>

/// 执行存储过程，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )

/// </summary>

/// <param name="storedProcName">存储过程名</param>

/// <param name="parameters">存储过程参数</param>

/// <returns>SqlDataReader</returns>

public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)

{

SqlConnection connection = new SqlConnection(ConnectionString);

SqlDataReader returnReader;

connection.Open();

SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);

command.CommandType = CommandType.StoredProcedure;

returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);

return returnReader;

}

二、//返回表

public static DataTable RunProcedure1(string storedProcName, IDataParameter[] parameters)

{

SqlConnection connection = new SqlConnection(ConnectionString);

//SqlDataReader returnReader;

connection.Open();

SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);

command.CommandType = CommandType.StoredProcedure;

SqlDataAdapter da = new SqlDataAdapter(command);//用SqlDataAdapter执行

DataTable table = new DataTable();//创建一张表

da.Fill(table);//填充表

// returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);

return table ;

}

三、//获取返回值

public static string RunProcedure2(string storedProcName, IDataParameter[] parameters)

{

SqlConnection connection = new SqlConnection(ConnectionString);

//SqlDataReader returnReader;

connection.Open();

SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);

command.CommandType = CommandType.StoredProcedure;

command.Parameters.Add(new SqlParameter("@return", SqlDbType.Int));

command.Parameters["@return"].Direction = ParameterDirection.ReturnValue;

command.ExecuteNonQuery();

return command.Parameters["@return"].Value.ToString();

}

四、//获取输出值

public static string RunProcedure3(string storedProcName, IDataParameter[] parameters)

{

SqlConnection connection = new SqlConnection(ConnectionString);

//SqlDataReader returnReader;

connection.Open();

SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);

command.CommandType = CommandType.StoredProcedure;

//SqlParameter p3 = new SqlParameter("@phone", SqlDbType.Int);

command.Parameters["@phone"].Direction = ParameterDirection.Output;

//command.Parameters["@return"].Direction = ParameterDirection.ReturnValue;

command.ExecuteNonQuery();

return command.Parameters["@phone"].Value.ToString();

}

存储过程：

create proc proc_StadiumN

(

@Open_id nvarchar(50),

@Uname nvarchar(50),

@phone nvarchar(50) out

)

as

select @phone=Uname from Stadium_User Where Open_id=@Open_id

return '1'

2.

create proc [dbo].[proc_area](@status nvarchar(50),@Area_name nvarchar(50))

as

select Area_name from Area where Area_name=@Area_name

前台：

<form id="form1" runat="server">

<div>

<asp:GridView ID="GridView1" runat="server"></asp:GridView>

</div>

</form>

后台：

//返回阅读器

//IDataParameter[] dp = new IDataParameter[] { new SqlParameter("@Area_name", "虹口区"), new SqlParameter("@status", "1") };

//SqlDataReader dr = DBHelper.RunProcedure("proc_area", dp);

while(dr.Read())

{

Response.Write(dt["Area_name"]);

}

dr.Close();

//返回表

IDataParameter[] dp1 = new IDataParameter[] { new SqlParameter("@Open_id", "1"), new SqlParameter("@Uname", "段段"), new SqlParameter("@phone", "123") };

DataTable dt = DBHelper.RunProcedure2("proc_StadiumN", dp1);

//GridView1.DataSource = dt;

//GridView1.DataBind();

//获取返回值

IDataParameter[] dp1 = new IDataParameter[] { new SqlParameter("@Open_id", "1"), new SqlParameter("@Uname", "段段"), new SqlParameter("@phone", "123") };

string dt3 = DBHelper.RunProcedure2("proc_StadiumN", dp1);

Response.Write(dt3 );

//获取输出值

IDataParameter[] dp1 = new IDataParameter[] { new SqlParameter("@Open_id", "1"), new SqlParameter("@Uname", "段段"), new SqlParameter("@phone", "123") };

string dt4 = DBHelper.RunProcedure3("proc_StadiumN", dp1);

Response.Write(dt4 );

 

 

 

 

1.using (SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["LocalConnectionString"].ConnectionString))

{

conn.Open();

SqlCommand com = new SqlCommand();

com.Connection = conn;

com.CommandText = "uppoint_all";//这里的"uppoint_all"就是存储过程的名字

com.CommandType = CommandType.StoredProcedure;

com.ExecuteNonQuery();

conn.Close();

}

2.

SqlConnection conn = new SqlConnection(connectionstring);

SqlDataAdapter da = new SqlDataAdapter();

da.SelectCommand = new SqlCommand();

da.SelectCommand.Connection = conn;

da.SelectCommand.CommandText ="NameOfProcedure（’para1’,’para2’,para3）";

da.Selectcommand.CommandType = CommandType.StoredProcedure;

 

3.

SqlCommand cmd = new SqlCommand("proc_3", con);//同上

cmd.CommandType = CommandType.StoredProcedure;//同上

SqlDataAdapter da = new SqlDataAdapter(cmd);//用SqlDataAdapter执行

DataTable table = new DataTable();//创建一张表

da.Fill(table);//填充表

return table;