Menu
Home
About Me
Articles
Hobbies
Code Library
Flash Library
3d stuido max

 
//Sample values
int iSomeID = 4;
string sSomeValue = "JN";

//Sample connection
string connectionStr = 	
"Provider=SQLOLEDB;" + 
"Data Source=localhost;" +
"Integrated Security=SSPI;" +
"Initial Catalog=northwind";
OleDbConnection ActiveConnection = new OleDbConnection(connectionStr);

DBA oDBA = new DBA(ActiveConnection);
oDBA.SetSP("dbo.myStoredProcudureSelect");
oDBA.AddSPParam("@SomeID",  iSomeID);
oDBA.RunSP(true); //There is a returned dataset
DataSet ds  = oDBA.GetDataSet();
oDBA.Nothing();

DBA oDBA2 = new DBA(ActiveConnection);
oDBA2.SetSP("dbo.myStoredProcudureUpdate");
oDBA2.AddSPParam("@SomeID",iSomeID );
oDBA2.AddSPParam("@SomeValue",sSomeValue );
oDBA2.AddSPOutput(); //there a returned "OUTPUT" value
oDBA2.RunSP(false); //there is no returned dataset
int returnvalue = oDBA2.GetReturn(); 
oDBA2.Nothing();

I compiled the DBA class into a seperate DLL, as part of a common functions library

public class DBA
{
	private OleDbConnection oConn = new OleDbConnection();
	private OleDbCommand oCmd = new OleDbCommand();
	int iReturn = 0;
	System.Data.DataSet ds = new DataSet();

	//Instance constructor
	public DBA(OleDbConnection DSN) //default instance Constructor
	{
		this.oConn = DSN;	
	}

	public void SetSP(string sSP)
	{
		this.oCmd.CommandType = CommandType.StoredProcedure;
		this.oCmd.CommandText  = sSP;
		this.oCmd.Connection = oConn;
	}

	public void AddSPParam(string sParam,  string sData)
	{
		if (sData==null)
		{
			this.oCmd.Parameters.Add(sParam, OleDbType.VarChar);
			this.oCmd.Parameters[sParam].Value = DBNull.Value;
		}
		else
		{
			this.oCmd.Parameters.Add(sParam, OleDbType.VarChar, sData.Length );
			this.oCmd.Parameters[sParam].Value = sData;
		}
	}

	public void AddSPParam(string sParam, int iData)
	{
		this.oCmd.Parameters.Add(sParam, OleDbType.Integer, 4);
		this.oCmd.Parameters[sParam].Value = iData;
	}
	
	public void AddSPParam(string sParam, System.Int16 iData)
	{
		this.oCmd.Parameters.Add(sParam, OleDbType.SmallInt, 2);
		this.oCmd.Parameters[sParam].Value = iData;
	}

	public void AddSPParam(string sParam, System.Decimal iData)
	{
		this.oCmd.Parameters.Add(sParam, OleDbType.Decimal, 8);
		this.oCmd.Parameters[sParam].Value = iData;
	}

	public void AddSPParam(string sParam, bool bData )
	{
		this.oCmd.Parameters.Add(sParam, OleDbType.Boolean);
		this.oCmd.Parameters[sParam].Value = bData;
	}

	public void AddSPOutput()
	{
		this.oCmd.Parameters.Add("@ReturnID", OleDbType.Integer, 4);
		this.oCmd.Parameters["@ReturnID"].Direction = ParameterDirection.Output;
	}


	public void RunSP(bool bResults) 
	{
		this.oConn.Open();
		if (bResults) 
		{
			try 
			{
				OleDbDataAdapter adapter = new OleDbDataAdapter(this.oCmd);
				adapter.Fill(this.ds);
				adapter.Dispose();
			}
			catch 
			{
				ds.Tables.Add("blank");
			}
		}
		else 
		{
			this.oCmd.ExecuteNonQuery();
			this.oCmd.UpdatedRowSource = UpdateRowSource.None;
		}
		this.oConn.Close();
	}

	public int GetReturn()
	{
		return (int)this.oCmd.Parameters["@ReturnID"].Value;
	}

	public System.Data.DataSet GetDataSet()
	{
		return this.ds;
	}

	public void Nothing()
	{
		this.ds.Dispose();
		this.oCmd.Dispose();
	}
	~DBA()
	{
		oConn.Dispose();
	}
}