"select name from person");
return (string)db.ExecuteScalar(dbcomm);
}8、事务处理
public void UseTransaction()
{
Database db = DatabaseFactory.CreateDatabase();
DbCommand dbcomm1 = db.GetSqlStringCommand("update person set name=''pw''");
DbCommand dbcomm2 = db.GetSqlStringCommand("delete from person where id=1");
using (DbConnection conn = db.CreateConnection())
{
//打开连接
conn.Open();
//创建事务
DbTransaction trans = conn.BeginTransaction();
try
{
db.ExecuteNonQuery(dbcomm1);
db.ExecuteNonQuery(dbcomm2);
//都执行成功则提交事务
trans.Commit();
}
catch(Exception)
{
//发生异常，事务回滚
trans.Rollback();
}
//关闭连接
conn.Close();
}
}
9、执行ExecuteXmlReader返回XML数据
支持Sql Server 2000 及以后版本的数据库，对微软以外的数据库应该不支持。
public void UseXMLReader()
{
SqlDatabase dbSQL = DatabaseFactory.CreateDatabase("Connection String") as SqlDatabase;
// 要返回XML数据需要在SQL语句后加 FOR XML AUTO
string sqlCommand = "SELECT ProductID, ProductName FROM Products FOR XML AUTO";
DbCommand dbCommand = dbSQL.GetSqlStringCommand(sqlCommand);
XmlReader personReader = null;
StringBuilder personlist = new StringBuilder();
try
{
personReader = dbSQL.ExecuteXmlReader(dbCommand);
// 循环向XML中写入我们查询得到的数据
while (!personReader.EOF)
{
if (personReader.IsStartElement())
{
personlist.Append(personReader.ReadOuterXml());
personlist.Append(Environment.NewLine);
}
}
}
finally
{
// 关闭 Reader.
if (personReader != null)
{
personReader.Close();
}
// 关闭数据库连接
if (dbCommand.Connection != null)
{
dbCommand.Connection.Close();
}
}
}
10、用DataSet批量的添加，修改，删除数据
public void UpdateDataBase()
{
Database db = DatabaseFactory.CreateDatabase();
DataSet personDataSet = new DataSet();
string sqlCommand = "Select * from person";
DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);
string personTable = "person";
// 得到初始化数据
db.LoadDataSet(dbCommand, personDataSet, personTable);
// 得到未修改前的数据集
DataTable table = personDataSet.Tables[personTable];
// 往DataSet中添加一行数据
DataRow addedRow = table.Rows.Add(new object[] { 18, "New person", "男", "654321" });
// 修改
table.Rows[0]["ProductName"] = "Modified product";
// 下面分别创建添加，修改，删除的操作
DbCommand insertCommand = db.GetStoredProcCommand("AddPerson");
db.AddInParameter(insertCommand, "Name", DbType.String, "Name", DataRowVersion.Current);
db.AddInParameter(insertCommand, "Sex", DbType.String, "Sex", DataRowVersion.Current);
db.AddInParameter(insertCommand, "ID", DbType.Int32, "ID", DataRowVersion.Current);
DbCommand deleteCommand = db.GetStoredProcCommand("DeletePerson");
db.AddInParameter(deleteCommand, "ID", DbType.Int32, "ID", DataRowVersion.Current);
DbCommand updateCommand = db.GetStoredProcCommand("UpdatePerson");
db
|
