定义公共的类调用存储过程获取数据:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace jsfw
{
/// <summary>
/// DataAccessBase 的摘要说明。
/// </summary>
public class DataAccessBase
{
public DataAccessBase()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
public DataSet Runprocedure(string strConn,string strSpname,SqlParameter[] arrayParameters,string strTablename)
{
SqlConnection myconnection = new SqlConnection(strConn);
DataSet dsData = new DataSet();
SqlDataAdapter mycommand = new SqlDataAdapter(strSpname, myconnection);
mycommand.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter arrayParameter = new SqlParameter();
foreach(SqlParameter sq in arrayParameters)
{
mycommand.SelectCommand.Parameters.Add(sq);
}
mycommand.Fill(dsData, strTablename);
myconnection.Close();
return dsData;
}
public void Runprocedure(string strConn,string strSpname,SqlParameter[] arrayParameters)
{
SqlConnection myconnection = new SqlConnection(strConn);
myconnection.Open();
SqlCommand mycommand = new SqlCommand(strSpname, myconnection);
mycommand.CommandType = CommandType.StoredProcedure;
SqlParameter arrayParameter = new SqlParameter();
foreach(SqlParameter sq in arrayParameters)
{
mycommand.Parameters.Add(sq);
}
mycommand.ExecuteNonQuery();
myconnection.Close();
}
public int Runprocedure(string strConn,string strSpname,SqlParameter[] arrayParameters,int nEffectedrows)
{
SqlConnection myconnection = new SqlConnection(strConn);
myconnection.Open();
SqlCommand mycommand = new SqlCommand(strSpname, myconnection);
mycommand.CommandType = CommandType.StoredProcedure;
SqlParameter arrayParameter = new SqlParameter();
foreach(SqlParameter sq in arrayParameters)
{
mycommand.Parameters.Add(sq);
}
nEffectedrows = mycommand.ExecuteNonQuery();
myconnection.Close();
return nEffectedrows;
}
public DataSet Runprocedure(string strConn,string strSpname,string strTablename)
{
DataSet dsData = new DataSet();
SqlConnection myconnection = new SqlConnection(strConn);
myconnection.Open();
SqlDataAdapter mycommand = new SqlDataAdapter(strSpname, myconnection);
mycommand.SelectCommand.CommandType = CommandType.StoredProcedure;
mycommand.Fill(dsData,strTablename);
myconnection.Close();
return dsData;
}
public bool CheckEOF(DataSet dsData)
{
bool bReturn = false;
if(dsData != null)
{
if(dsData.Tables.Count > 0)
{
for(int i = 0 ; i < dsData.Tables.Count ; i++)
{
if(dsData.Tables[i].Rows.Count >0)
{
bReturn = true;
break;
}
}
}
}
return bReturn;
}
}
}
调用时:
public class clsAds:DataAccessBase
{
public string strConn = ConfigurationSettings.AppSetting["connString"].ToString();
public DataSet GetAds(int nPositionID)
{
DataSet dsData = new DataSet();
SqlParameter[] sql = {new SqlParameter("@PositionID", SqlDbType.Int)};
sql[0].Value = nPositionID;
dsData = this.Runprocedure(strConn, "sp_GetAds", sql, "ads");
return dsData;
}
}