AspNet4YouTop 10sDevelopers WorldForums
Home
About
ASPNet Books
ASPNet Sites
ASPNet Hosters
View Articles
Search Articles
Authors
View Forums
 
Quick Menus
HOME
About
AspNet4You Forums
Articles
Authors
Search
Articles Summary
Articles(RSSFeed)
AspNet Books
AspNet Sites
AspNet Hosters
 Top 10 ASP.NET Books 
Professional ASP.NET 1.1
Essential ASP.NET With Examples in C#
ASP.NET Unleashed
Programming Data Driven Web Applications with ASP.NET
Professional ASP.NET Web Services
Beginning ASP.NET 1.1 with Visual C# .NET 2003
Programming Microsoft ASP.NET
Beginning ASP.NET Databases Using VB.NET
ASP.NET Security
Developing Microsoft ASP.NET Server Controls and Components
More...
 Top 10 ASP.NET Hosters 
WebHost4Life
DiscountASP.NET
MaximumASP
Brinkster
ORCS Web
myhosting.com
ISQSolutions
ASPwebhosting.com, LLC
Active ISP
Aquest Hosting
More...
 Top 10 ASP.NET Sites 
Asp.Net
GotDotNet
4GuysFromRolla.com
123aspx.com
EggHeadCafe.com
CShrp.Net
.NET 247
DevelopersDex.com
Csharp-Corner.com
dotnetspider
More...
Search Articles
Google
ASPNET4YOU      
Category:   Search Type:   Match Type:  
Data Access Layer to Execute Stored Procedures
Author: Surapureddy, SriramPosted: 2/6/2005 4:06:12 PM

This article explains about executing a single stored procedure or batch of stored procedures from the application layer. As Data access layer completely decoupled from Application layer we just need to change the Application layer in case of any change in underlying database schema.

This article explains primarily about calling stored procedures which does not return any result set. This can be extended to handle those stored procedures also.

Data access layer:

This data access layer contains the following key elements.

  • ParamData Structure
  • StoredProcedure class
  • StoredProcedureCollection class
  • Execute class
ParamData structure will contain parameter name, parameter value and data type.
 
struct ParamData
{
public string pName,pValue;
public SqlDbType pDataType;
public ParamData(string pName,SqlDbType pDataType,string pValue)
{
this.pName=pName;
this.pDataType=pDataType;
this.pValue=pValue;
}
}

Stored Procedure class which will have methods to SetParam and Getparam to set and get the parameter list. This will be added to array list which will contain ParamData structures.

public void SetParam(string pName,SqlDbType pDataType,string pValue)
{
ParamData pData=new ParamData(pName,pDataType,pValue);
// adding to array list sParams.
sParams.Add(pData);
}

Similarly to get the parameter list GetParams method was defined which will return array list of ParamData structures.

public ArrayList GetParams()
{
if (!(sParams==null))
{
return sParams;
}
else
{
return null;
}
}

StoredProcedureCollection class contains the collection of stored procedure classes. This class has ADD and Remove methods to add the StroredProcedure class and remove when it is not needed.

public void add(StoredProcedure value)
{
List.Add(value);
}
public void Remove(int index)
{
if (index > Count - 1 || index < 0)
{
Console.WriteLine("No data to remove");
}
else
{
List.RemoveAt(index); 
}
}

This will also have ITEM method to get the StoredProcedure from the List.

public StoredProcedure Item(int Index)
{
return (StoredProcedure) List[Index];
} 

Execute class has a static method ExecuteSps to execute the stored procedures from the collection.

public static bool ExecuteSps(StoredProcedureCollection spCollection,SqlConnection Connection)
{
try
{
foreach(StoredProcedure spData in spCollection)
{
SqlCommand cmd=new SqlCommand();
int i=0;
if (Connection.State!= ConnectionState.Open)
Connection.Open();
cmd.Connection=Connection; cmd.CommandType=CommandType.StoredProcedure;
cmd.CommandText=spData.ProcName;
IEnumerator myEnumerator = spData.GetParams().GetEnumerator();
while (myEnumerator.MoveNext())
{
ParamData pData=(ParamData)myEnumerator.Current;
cmd.Parameters.Add(pData.pName,pData.pDataType);
cmd.Parameters[i].Value=pData.pValue;
i=i+1;
}
cmd.ExecuteNonQuery();
}
return true; 
}
catch(Exception exc)
{
return false;
}
}


Application Layer:

In the application layer we have to add the reference of DataAccessLayer.dll. After adding this reference we can call the data access layer functions as per our need. The advantage here is we can dynamically add/remove the parameters as and whenever there is change stored procedure definition.

private void button1_Click(object sender, System.EventArgs e)
{
SqlConnection connection=new SqlConnection();
//change this connect string as per your environment
string connectString="Persist Security Info=False;Integrated Security=SSPI;database=DB1;server=Server2;Connect Timeout=60";
connection.ConnectionString=connectString;
if (connection.State!=ConnectionState.Open)
connection.Open();
DataAccessLayer.StoredProcedureCollection spCollection=new DataAccessLayer.StoredProcedureCollection();
DataAccessLayer.StoredProcedure spData=new DataAccessLayer.StoredProcedure();
spData.ProcName=txtSpName.Text;
spData.SetParam(txtParam1.Text,SqlDbType.VarChar,txtParamValue1.Text);
spData.SetParam(txtParam2.Text,SqlDbType.VarChar,txtParamValue2.Text);
spCollection.add(spData);
if (DataAccessLayer.Execute.ExecuteSps(spCollection,connection))
MessageBox.Show("Successfully executed");
} 
}
catch(Exception exc)
{
return false;
}
}

Surapureddy Sriram
Surapureddy Sriram is working in Microsoft technologies

Terms and Conditions