asp.net执行无参数与有参数的存储过程

/// <summary>

/// 得到数据表department满足查询条件的记录数

/// </summary>

/// <param name="where">查询条件</param>

/// <param name="recordCount">记录数</param>

public void Select(string where, out int recordCount)

{

string sqlCommand = "departmentCountByWhere";

SqlParameter[] param ={

new SqlParameter("@where",SqlDbType.VarChar,8000),

new SqlParameter("@recordCount",SqlDbType.Int)

};

param[0].Value = where;

param[1].Direction = ParameterDirection.Output;

SqlHelper.ExecuteNonQuery(Conn.SqlConn, CommandType.StoredProcedure, sqlCommand, param);

recordCount = Convert.ToInt32(param[1].Value);

}

执行存储过程返回值

SqlParameter[] para = new SqlParameter[2]; //建立参数

para[0] = new SqlParameter("@type", "深圳");

para[1] = new SqlParameter("@result", SqlDbType.Int, 1);

para[1].Direction = ParameterDirection.Output; //输出返回值

SqlHelper.ExecuteNonQuery(SqlHelper.dbconn(), CommandType.StoredProcedure, "testPro", para);

Response.Write(para[1].Value.ToString());

返回DataTable(执行SQL语句)

DataTable dt = SqlHelper.ExecuteProDataTable(SqlHelper.dbconn(), CommandType.Text, "select top 10 * from dyj_client", null);

返回DataTable(执行存储过程方式)

SqlParameter[] para = new SqlParameter[1];

// para[0] = new SqlParameter("@type", "深圳");

para[0] = new SqlParameter("@type",SqlDbType.VarChar, 20);

para[0].Value = "深圳";

DataTable dt = SqlHelper.ExecuteProDataTable(SqlHelper.dbconn(), CommandType.StoredProcedure, "testPro", para);

foreach (DataRow dr in dt.Rows)

{

Response.Write(dr[0].ToString() + "<br>");

}

SqlHelper中的函数

public static DataTable ExecuteProDataTable(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)//(DbConnection connection, CommandType commandType, string commandText, params DbParameter[] commandParameters)

{

if (connection == null) throw new ArgumentNullException("connection");

// 预处理

SqlCommand cmd = new SqlCommand();

isconnection(connection);

PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);

// 创建DbDataAdapter与ataTable

using (SqlDataAdapter da = new SqlDataAdapter(cmd))

{

DataTable dt = new DataTable();

da.Fill(dt);

connection.Close();

return dt;

}

}

文章来自学IT网：http://www.xueit.com/html/2008-10/117_3948_00.html