一、配置Data block所需参数
1,应用程序的配置文件(*.exe.config或者*.dll.config或者Web.config)
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<configSections>
<section name="enterpriselibrary.configurationSettings" type="Microsoft.Practices.EnterpriseLibrary.Configuration.ConfigurationManagerSectionHandler, Microsoft.Practices.EnterpriseLibrary.Configuration" />
</configSections>
<enterpriselibrary.configurationSettings xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" defaultSection="" applicationName="Application" xmlns="http://www.microsoft.com/practices/enterpriselibrary/08-31-2004/configuration">
<configurationSections>
<configurationSection name="dataConfiguration" encrypt="false">
<storageProvider xsi:type="XmlFileStorageProviderData" name="XML File Storage Provider" path="dataConfiguration.config" />
<dataTransformer xsi:type="XmlSerializerTransformerData" name="Xml Serializer Transformer">
<includeTypes />
</dataTransformer>
</configurationSection>
</configurationSections>
<keyAlgorithmStorageProvider xsi:nil="true" />
</enterpriselibrary.configurationSettings>
</configuration>
2,配置数据库连接串(dataConfiguration.config)
<?xml version="1.0" encoding="utf-8"?>
<dataConfiguration>
<xmlSerializerSection type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null">
<enterpriseLibrary.databaseSettings xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" defaultInstance="InstanceWebinpuy" xmlns="http://www.microsoft.com/practices/enterpriselibrary/08-31-2004/data">
<databaseTypes>
<databaseType name="Sql Server" type="Microsoft.Practices.EnterpriseLibrary.Data.Sql.SqlDatabase, Microsoft.Practices.EnterpriseLibrary.Data, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null" />
</databaseTypes>
<instances>
<instance name="InstanceWebinpuy" type="Sql Server" connectionString="Sql Connection String" />
</instances>
<connectionStrings>
<connectionString name="Sql Connection String">
<parameters>
<parameter name="database" value="BMS_Webinput" isSensitive="false" />
<parameter name="Integrated Security" value="False" isSensitive="false" />
<parameter name="server" value="192.168.1.28" isSensitive="false" />
</parameters>
</connectionString>
</connectionStrings>
</enterpriseLibrary.databaseSettings>
</xmlSerializerSection>
</dataConfiguration>
二、执行Sql语句
public string GetCustomerList()
{
// DataReader that will hold the returned results
// Create the Database object, using the default database service. The
// default database service is determined through configuration.
Database db = DatabaseFactory.CreateDatabase();
string sqlCommand = "Select CustomerID, Name, Address, City, Country, PostalCode " +
"From Customers";
DBCommandWrapper dbCommandWrapper = db.GetSqlStringCommandWrapper(sqlCommand);
StringBuilder readerData = new StringBuilder();
// The ExecuteReader call will request the connection to be closed upon
// the closing of the DataReader. The DataReader will be closed
// automatically when it is disposed.
using (IDataReader dataReader = db.ExecuteReader(dbCommandWrapper))
{
// Iterate through DataReader and put results to the text box.
// DataReaders cannot be bound to Windows Form controls (e.g. the
// resultsDataGrid), but may be bound to Web Form controls.
while (dataReader.Read())
{
// Get the value of the 'Name' column in the DataReader
readerData.Append(dataReader["Name"]);
readerData.Append(Environment.NewLine);
}
}
return readerData.ToString();
}
三、调用存储过程
1、插入新记录并从存储过程获取返回值
存储过程：
Create PROCEDURE usp_AddGroup
@StaffID VARCHAR(36),
@GroupName VARCHAR(40),
@Count INT
AS
IF EXISTS(SELECT * FROM StaffGroup WHERE StaffID=@StaffID AND GroupName=@GroupName)
RETURN 1
ELSE
INSERT StaffGroup (GroupName,StaffID,MaxCount) VALUES(@GroupName,@StaffID,@Count)
RETURN @@ERROR
GO
调用代码：
private void button4_Click(object sender, System.EventArgs e)
{
//@RETURN_VALUE,RETURN_VALUE,
Database db = DatabaseFactory.CreateDatabase();
DBCommandWrapper cmd = db.GetStoredProcCommandWrapper("usp_AddGroup");
cmd.AddInParameter("@StaffID",DbType.String,"3290F849-031F-49B5-8CEE-0F98AA789731");
cmd.AddInParameter("@GroupName",DbType.String,"yyyooo");
cmd.AddInParameter("@Count",DbType.Int32,10);
cmd.AddParameter("RetVal7",DbType.Int32,ParameterDirection.ReturnValue,"",DataRowVersion.Current,null);
db.ExecuteNonQuery(cmd);
int a = (int)cmd.GetParameterValue("RetVal7");
MessageBox.Show(a.ToString());
}
2、返回记录集并获取存储过程返回值
存储过程：
CREATE procedure usp_GetValidStaffs
AS
Select * from staff where Isdelete = 0 and ShowOnHomePage=1
RETURN 8
GO
调用代码：
private void button5_Click(object sender, System.EventArgs e)
{
Database db = DatabaseFactory.CreateDatabase();
DBCommandWrapper cmd = db.GetStoredProcCommandWrapper("usp_GetValidStaffs");
cmd.AddParameter("RetVal7",DbType.Int32,ParameterDirection.ReturnValue,"",DataRowVersion.Current,null);
DataSet ds = db.ExecuteDataSet(cmd);
dataGrid1.SetDataBinding(ds,"Table");
int a = (int)cmd.GetParameterValue("RetVal7");
MessageBox.Show(a.ToString());
}
3、返回记录集并通过输出参数获取返回值
存储过程：
CREATE procedure usp_GetValidStaffs
@Count INT OUTPUT
AS
Select * from staff where Isdelete = 0 and ShowOnHomePage=1
SET @Count = 8
GO
调用代码：
private void button6_Click(object sender, System.EventArgs e)
{
Database db = DatabaseFactory.CreateDatabase();
DBCommandWrapper cmd = db.GetStoredProcCommandWrapper("usp_GetValidStaffs");
cmd.AddOutParameter("@Count",DbType.Int32,4);
DataSet ds = db.ExecuteDataSet(cmd);
dataGrid1.SetDataBinding(ds,"Table");
int a = (int)cmd.GetParameterValue("@Count");
MessageBox.Show(a.ToString());
}
4、DataRearder与输出参数
存储过程：
CREATE procedure usp_GetValidStaffs
@Count INT OUTPUT
AS
Select * from staff where Isdelete = 0 and ShowOnHomePage=1
SET @Count = 8
GO
调用代码：
private void button8_Click(object sender, System.EventArgs e)
{
Database db = DatabaseFactory.CreateDatabase();
DBCommandWrapper cmd = db.GetStoredProcCommandWrapper("usp_GetValidStaffs");
cmd.AddOutParameter("@Count",DbType.Int32,4);
using(IDataReader dr = db.ExecuteReader(cmd))
{
while (dr.Read())
{
MessageBox.Show(dr.GetString (1));
};
}
object o = cmd.GetParameterValue("@Count");
MessageBox.Show(o.ToString());
}
5、DataRearder与返回值
存储过程：
CREATE procedure usp_GetValidStaffs
AS
Select * from staff where Isdelete = 0 and ShowOnHomePage=1
RETURN 8
GO
调用代码：
private void button7_Click(object sender, System.EventArgs e)
{
Database db = DatabaseFactory.CreateDatabase();
DBCommandWrapper cmd = db.GetStoredProcCommandWrapper("usp_GetValidStaffs");
cmd.AddParameter("RetVal7",DbType.Int32,ParameterDirection.ReturnValue,"",DataRowVersion.Current,null);
using(IDataReader dr = db.ExecuteReader(cmd))
{
while (dr.Read())
{
MessageBox.Show(dr.GetString (1));
};
}
object o = cmd.GetParameterValue("RetVal7");
MessageBox.Show(o.ToString());
}
四、事务处理：
public static bool OpretRapportFraskabelon (string CVR, int maanedValoer)
{
try
{
db = DatabaseFactory.CreateDatabase();
}
catch(Exception ex)
{
throw new PensamDBException("Fejl i opret databasen",ex);
}
using (IDbConnection connection = db.GetConnection())
{
connection.Open();
IDbTransaction transaction = connection.BeginTransaction();
try
{
int year = maanedValoer / 12 + 1800;
int month = maanedValoer % 12 + 1;
DBCommandWrapper cmdWrapper = db.GetStoredProcCommandWrapper("sp_OpretRapportFraSkabelon");
cmdWrapper.AddInParameter("@CVR", DbType.String,CVR);
cmdWrapper.AddInParameter("@Year", DbType.Int32,year);
cmdWrapper.AddInParameter("@Month", DbType.Int32,month);
cmdWrapper.AddParameter ("RetVal",DbType.Int32,ParameterDirection.ReturnValue,"",DataRowVersion.Current,null);
db.ExecuteNonQuery(cmdWrapper);
int result = (int)cmdWrapper.GetParameterValue("RetVal");
return (result == 0);
}
catch(PensamDBException ex)
{
throw ex;
}
catch(Exception ex)
{
// Rollback transaction
transaction.Rollback();
throw new PensamDBException("Fejl i opret ny indebertning fra a older one",ex);
}
finally
{
connection.Close();
}
}
}
