ADO.NET 使用通用数据库操作类Database (SQL Server)

<connectionStrings>
<add name="constr_name" connectionString="server=192.168.1.139;uid=sa;pwd=123456;Trusted_Connection=no;Database=dabasename" providerName="system.data.sqlclient"/>
</connectionStrings>
注：constr_name(自定义名)，server（sqlserver服务器地址），uid（登录名），pwd（密码），Trusted_Connection（是否使用windows账户登录），Database（数据库名），providerName（数据库类型，sqlserver的为system.data.sqlclient）

using Microsoft.Practices.EnterpriseLibrary.Data;

private Database _database;
this._database = DatabaseFactory.CreateDatabase();//在DAL类的构造函数中创建

User user = null;
List<User> list = new List<User>();
//读取数据
string sql = "SELECT * FROM [User] WHERE id > @id;";
DbCommand command = _database.GetSqlStringCommand(sql);
_database.AddInParameter(command, "id", DbType.Int32, );//添加参数
DataTable dt = _database.ExecuteDataSet(command).Tables[];

//将数据写入对象
for (int i = ; i < dt.Rows.Count; i++)
{
user = new User();
if (dt.Rows[i]["id"] != DBNull.Value)
user.id = Convert.ToInt32(dt.Rows[i]["id"]);
if (dt.Rows[i]["name"] != DBNull.Value)
user.name = dt.Rows[i]["name"].ToString(); list.Add(user);
}

int result = ;
string sql = "INSERT INTO [User] VALUES(@Id,@Name);";using (DbConnection conn = _database.CreateConnection())
{
conn.Open();
DbTransaction tran = conn.BeginTransaction();//开始事务
try
{
command = _database.GetSqlStringCommand(sql);
_database.AddInParameter(command, "Id", DbType.Int32, );
_database.AddInParameter(command, "Name", DbType.String, 'name1');
result += _database.ExecuteNonQuery(command, tran);//插入第一条数据 _database.AddInParameter(command, "Id", DbType.Int32, );
_database.AddInParameter(command, "Name", DbType.String, 'name2');
result += _database.ExecuteNonQuery(command, tran);//插入第二条数据 if (result == )
tran.Commit();//两条数据都插入成功则提交事务
else
{
tran.Rollback();//否则回滚事务，取消所有插入的数据
}
}
catch (System.Exception ex)
{
tran.Rollback();//出错,回滚事务
}
finally
{
if (conn != null)
conn.Close();
}
}