可执行个性化存储过程的数据访问层(DAL) (C#实现)
本文意在解释如何从应用层执行单条或者批量的存储过程(主要调用没有任何结果集返回的存储储过程,当然也可以拓展到调用有结果集返回的存储过程).
数据访问层:
本层主要包括有下列成员:
public void SetParam(string pName,SqlDbType pDataType,string pValue)
{
ParamData pData=new ParamData(pName,pDataType,pValue);
// adding to array list sParams.
sParams.Add(pData);
}
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
public ArrayList GetParams()
{
if (!(sParams==null))
{
return sParams;
}
else
{
return null;
}
}
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);
}
}
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
public StoredProcedure Item(int Index)
{
return (StoredProcedure) List[Index];
}
数据访问层:
本层主要包括有下列成员:
- ParamData 结构
- StoredProcedure 类
- StoredProcedureCollection 类
- Execute 类
其中ParamData 含有存储过程参数的名称,值,以及各自的数据类型.
1
struct ParamData
2
{
3
public string pName,pValue;
4
public SqlDbType pDataType;
5
public ParamData(string pName,SqlDbType pDataType,string pValue)
6
{
7
this.pName=pName;
8
this.pDataType=pDataType;
9
this.pValue=pValue;
10
}
11
}
12![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
2
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
3
![](/Images/OutliningIndicators/InBlock.gif)
4
![](/Images/OutliningIndicators/InBlock.gif)
5
![](/Images/OutliningIndicators/InBlock.gif)
6
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
7
![](/Images/OutliningIndicators/InBlock.gif)
8
![](/Images/OutliningIndicators/InBlock.gif)
9
![](/Images/OutliningIndicators/InBlock.gif)
10
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
11
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
12
![](/Images/OutliningIndicators/None.gif)
StoredProcedure 含有SetParam 和 Getparam两大方法,他们分别用来设置和获取存储过程的参数列表的.
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
StoredProcedureCollection类 是一个集合类, 它含有多个Stored Procedure以及Add和Remove,Item方法.
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
Execute 类 包括有一个ExecuteSps 静态方法,它主要执行来自StoredProcedureCollection类的存储过程
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;
}
}
{
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;
}
}
更多实现细节,见 DataAccessLayer.ZIP
应用层:
在应用层我们添加对DataAccessLayer.dll的引用后,我们就可以随意地在需要的地方调用数据层的功能.这样做的一大优势就是:我们可以随着存储过程需求的变化,来添加和删除任意多的参数.
1
private void button1_Click(object sender, System.EventArgs e)
2
{
3
SqlConnection connection=new SqlConnection();
4
//change this connect string as per your environment
5
string connectString="Persist Security Info=False;Integrated Security=SSPI;database=DB1;server=Server2;Connect Timeout=60";
6
connection.ConnectionString=connectString;
7
if (connection.State!=ConnectionState.Open)
8
connection.Open();
9
DataAccessLayer.StoredProcedureCollection spCollection=new DataAccessLayer.StoredProcedureCollection();
10
DataAccessLayer.StoredProcedure spData=new DataAccessLayer.StoredProcedure();
11
spData.ProcName=txtSpName.Text;
12
spData.SetParam(txtParam1.Text,SqlDbType.VarChar,txtParamValue1.Text);
13
spData.SetParam(txtParam2.Text,SqlDbType.VarChar,txtParamValue2.Text);
14
spCollection.add(spData);
15
if (DataAccessLayer.Execute.ExecuteSps(spCollection,connection))
16
MessageBox.Show("Successfully executed");
17
}
18
}
19
catch(Exception exc)
20
{
21
return false;
22
}
23
}
更多实现细节 见 CallingAPP.zip
![](/Images/OutliningIndicators/None.gif)
2
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
3
![](/Images/OutliningIndicators/InBlock.gif)
4
![](/Images/OutliningIndicators/InBlock.gif)
5
![](/Images/OutliningIndicators/InBlock.gif)
6
![](/Images/OutliningIndicators/InBlock.gif)
7
![](/Images/OutliningIndicators/InBlock.gif)
8
![](/Images/OutliningIndicators/InBlock.gif)
9
![](/Images/OutliningIndicators/InBlock.gif)
10
![](/Images/OutliningIndicators/InBlock.gif)
11
![](/Images/OutliningIndicators/InBlock.gif)
12
![](/Images/OutliningIndicators/InBlock.gif)
13
![](/Images/OutliningIndicators/InBlock.gif)
14
![](/Images/OutliningIndicators/InBlock.gif)
15
![](/Images/OutliningIndicators/InBlock.gif)
16
![](/Images/OutliningIndicators/InBlock.gif)
17
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
18
![](/Images/OutliningIndicators/None.gif)
19
![](/Images/OutliningIndicators/None.gif)
20
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
21
![](/Images/OutliningIndicators/InBlock.gif)
22
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
23
![](/Images/OutliningIndicators/None.gif)