// ExecuteTransaction（使用事务执行指定的SQL）
public void ExecuteTransaction()
{
_dber.StartTransaction();
try
{
for (int i = 0; i < 3; i++)
{
var _parameters = new List<DbParameter>()
{
_dber.CreateParameter("ID",i.ToString()),
_dber.CreateParameter("Name","Name123"),
_dber.CreateParameter("CreateTime",DateTime.Now),
};
var _sql = @"insert table1 (ID,Name,CreateTime) values (@ID,@Name,@CreateTime);";
var _isSucceed = _dber.ExecuteTransaction(_sql, _parameters);
}
}
catch (Exception ex)
{
_dber.RollbackTransaction();
return;
}
_dber.FinishTransaction();
}
ExecuteTransaction（使用指定的事务执行指定的SQL）
// ExecuteTransaction（使用指定的事务执行指定的SQL）
public void ExecuteTransaction()
{
for (int i = 0; i < 3; i++)
{
var _key = "Transaction" + i.ToString();
_dber.StartTransaction(_key);
var _id = i.ToString() + DateTime.Now.ToLongTimeString();
try
{
var _parameters = new List<DbParameter>()
{
_dber.CreateParameter("ID",_id),
_dber.CreateParameter("Name","Name123"),
_dber.CreateParameter("CreateTime",DateTime.Now),
};
var _sql = @"insert table1 (ID,Name,CreateTime) values (@ID,@Name,@CreateTime);";
var _isSucceed = _dber.ExecuteTransaction(_key, _sql, _parameters);
}
catch (Exception ex)
{
_dber.RollbackTransaction(_key);
return;
}
if (!_dber.FinishTransaction(_key))
{
return;
}
}
}
RollbackTransaction（回滚指定的事务）
// RollbackTransaction（回滚指定的事务）
public void RollbackTransaction()
{
var _key = "Transaction";
_dber.StartTransaction(_key);
var _id = nameof(RollbackTransaction);
try
{
var _parameters = new List<DbParameter>()
{
_dber.CreateParameter("ID",_id),
_dber.CreateParameter("Name","Name123"),
_dber.CreateParameter("CreateTime",DateTime.Now),
};
var _sql = @"insert table1 (ID,Name,CreateTime) values (@ID,@Name,@CreateTime);";
var _isSucceed = _dber.ExecuteTransaction(_key, _sql, _parameters);
}
catch (Exception ex)
{
_dber.RollbackTransaction(_key);
return;
}
_dber.RollbackTransaction(_key);
}
QueryTable（查询数据，并返回DataTable）
public DataTable QueryTable()
{
var _parameters = new List<DbParameter>()
{
_dber.CreateParameter("ID",123),
};
var _sql = "select * from table1 where ID=@ID;";
return _dber.QueryTable(_sql, _parameters);
}
QueryTables（查询数据，并返回DataSet）
public DataSet QueryTables()
{
var _parameters = new List<DbParameter>()
{
_dber.CreateParameter("ID",123),
};
var _sql = "select * from table1 where ID=@ID;";
_sql += "select * from table2 where ID=@ID;";
return _dber.QueryTables(_sql, _parameters);
}
QueryReader（查询数据，并返回DbDataReader）
public QueryReader QueryReader()
{
var _parameters = new List<DbParameter>()
{
_dber.CreateParameter("ID",123),
};
var _sql = "select * from table1 where ID=@ID;";
return _dber.QueryReader(_sql, _parameters);
}
QueryDictionary (将查询结果的首两列数据转换为Dictionary)
public Dictionary<string, string> QueryDictionary()
{
var _sql = "select Key,Value from table1 ;";
return _dber.QueryDictionary<string, string>(_sql);
}
// ToEntity1st（查询数据，并将首条数据转换为数据实例）
using JCoder.Db4Net;
using JCoder.Db4Net;
IDbOperator _operator = new MsSqlFactoryOperator()
{
ConnectionString = "Server=127.0.0.1;Database=test;Uid = user; Pwd=testpassword;"
};
return this.Operator.ToEntity1st<T>("select top 1 * from table1;");
2)ToEntities（查询数据，并转换为数据实体列表）
// ToEntities（查询数据，并转换为数据实体列表）
using JCoder.Db4Net;
IDbOperator _operator = new MsSqlFactoryOperator()
{
ConnectionString = "Server=127.0.0.1;Database=test;Uid = user; Pwd=testpassword;"
};
return this.Operator.ToEntities<T>("select top 10 * from table1;");
3)CRUD（增删查改）
using JCoder.Db4Net;
// Test Entity Class
[DbTable("t_table_test")]
public class TestEntity
{
[DbPrimaryKey]
[DbAutoIncrement]
public int Key001 { get; set; }
[DbPrimaryKey]
[DbField(IsUnique=true)]
public string Key002 { get; set; }
[DbIndex("idx01")]
public string C003 { get; set; }
}
IDbOperator _operator = new MsSqlFactoryOperator()
{
ConnectionString = "Server=127.0.0.1;Database=test;Uid = user; Pwd=testpassword;"
};
TestEntity x = new TestEntity()
{
Key001 = 101,
Key002 = "Test PK",
};
// Insert Data（新增数据）
this.Operator.Insert<TestEntity>(x);
// Update Data（更新数据）
x.C003 = "Update Test";
this.Operator.Update<TestEntity>(x);
// Delete Data（删除数据）
this.Operator.Delete<TestEntity>(x);
// Select Data（查询数据）
this.Operator.Select1st<TEntity>(" and Key001=101 and Key002-'Test PK'");
4)Create\Drop Table From Entity（由实体类创建\删除数据库的数据表）
using JCoder.Db4Net;
// Test Entity Class
[DbTable("t_table_test")]
public class TestEntity
{
[DbPrimaryKey]
[DbAutoIncrement]
public int Key001 { get; set; }
[DbPrimaryKey]
[DbField(IsUnique =true)]
public string Key002 { get; set; }
[DbIgnore]
public string Key003 { get; set; }
[DbIndex]
[DbField(AllowNull = false, Default = "", Description = "Test Comment")]
public string C001 { get; set; }
[DbIndex("idx01")]
public string C002 { get; set; }
[DbIndex("idx01")]
public string C003 { get; set; }
[DbIgnore]
public int C004 { get; set; }
[DbField(AllowNull = false, Default = "0", Description = "Test Comment2")]
public int C005 { get; set; }
[DbNumberFieldExt(Precision = 11, Scale = 2)]
public double C006 { get; set; }
[DbField(AllowNull = false, Default = "", Description = "Test Comment2")]
public string C007 { get; set; }
public string C008 { get; set; }
[DbTimeFieldExt(UpdateTimeOnCreate = true, UpdateTimeOnUpdate = true)]
public DateTime C009 { get; set; }
}
IDbOperator _operator = new MsSqlFactoryOperator()
{
ConnectionString = "Server=127.0.0.1;Database=test;Uid = user; Pwd=testpassword;"
};
this.Operator.CreateTable<TestEntity>();
this.Operator.DropTable<TestEntity>();
3.5 Db工具类使用
1)转换首条数据为实体对象(支持DbDataReader、DataTable)
using JCoder.Db4Net;
// data source
var _table = new DataTable();
// Property only.
var _result = JCoder.Db4Net.Db.ToObject1st<T>(_table);
var _result = _table.ToObject1st<T>();
// Attributes first
var _result = JCoder.Db4Net.Db.ToEntity1st<T>(_table);
var _result = _table.ToEntity1st<T>();
using JCoder.Db4Net;
// data source
var _dict = new Dictionary<string, string>();
// Attributes first
var _result = JCoder.Db4Net.Db.ToEntities<T>(_dict);
var _result = _dict.ToEntities<T>();
// Property only.
var _result = JCoder.Db4Net.Db.ToObject<T>(_dict);
var _result = _dict.ToObject<T>();
3)将数据源转换为实体对象列表(支持DbDataReader、DataTable)
using JCoder.Db4Net;
// data source
var _table = new DataTable();
// Attributes first
var _result = JCoder.Db4Net.Db.ToEntities<T>(_table);
var _result = _table.ToEntities<T>();
// Property only.
var _result = JCoder.Db4Net.Db.ToObjects<T>(_table);
var _result = _table.ToObjects<T>();
4)将数据源转换为实体对象字典(支持DbDataReader、DataTable)
using JCoder.Db4Net;
// data source
var _table = new DataTable();
_table.Columns.Add(new DataColumn("ID", typeof(string)));
// Property only.
var _result = JCoder.Db4Net.Db.ToObjectDict<T>(_table, "ID");
var _result = _table.ToObjectDict<T>("ID");
// Attributes first
var _result = JCoder.Db4Net.Db.ToEntityDict<T>(_table, "ID");
var _result = _table.ToEntityDict<T>("ID");
using JCoder.Db4Net;
var _target = new T();
// data source
var _source = new DbDataReader();
// Property only.
var _result = JCoder.Db4Net.Db.FillInObject<T>(_source, _target);
var _result = _source.FillInObject<T>(_target);
// Attributes first
var _result = JCoder.Db4Net.Db.FillInEntity<T>(_source, _target);
var _result = _source.FillInEntity<T>(_target);
6)将实体对象转换为DataTable
using JCoder.Db4Net;
var _list = new List<T>();
var t = new T();
// Property only.
// 实体对象转换为DataTable
DataTable _table = JCoder.Db4Net.Db.ObjectToDataTable(t);
DataTable _table = t.ObjectToDataTable();
// 实体对象列表转换为DataTable
DataTable _table = JCoder.Db4Net.Db.ObjectsToDataTable(_list);
DataTable _table = _list.ObjectsToDataTable();
// Attributes first
// 实体对象转换为DataTable
DataTable _table = JCoder.Db4Net.Db.EntityToDataTable(t);
DataTable _table = t.EntityToDataTable();
// 实体对象列表转换为DataTable
DataTable _table = JCoder.Db4Net.Db.EntitiesToDataTable(_list);
DataTable _table = _list.EntitiesToDataTable();
7)将List转换为DataTable
using JCoder.Db4Net;
// 将List转换为DataTable（使用特性进行排序，自定义名称等）
public class BoTest
{
[DbSubField("Width", "S001-Width", Sort = 2120)]//获取Size对象里的Width属性值，并显示为"S001-Width"
[DbSubField("Height", "S001-Height", Sort = 2121)]//获取Size对象里的Height属性值，并显示为"S001-Height"
public Size S001 { get; set; }
public string Key { get; set; }
[DbEnumName]//枚举显示为文本
public AttributeTargets AT { get; set; }
public AttributeTargets AT2 { get; set; }
[DbSort(100)]
public string P100 { get; set; }
[DbSort(111)]//设置排序值（从小到大依次排序）
public string P111 { get; set; }
public DateTime CreateTime { get; set; }
[DbIgnoreConvertion]//转换时忽略
public int Z001 { get; set; }
}
var _list = new List<BoTest>();
for (int i = 0; i < 100; i++)
{
_list.Add(new()
{
S001 = new(i * 100, i * 1000),
Key = $"Key_{i}",
P100 = $"P100_{i}",
P111 = $"P111_{i}",
CreateTime = DateTime.Now,
Z001 = i,
AT = AttributeTargets.Interface,
AT2 = AttributeTargets.Delegate,
});
}
var _table = JCoder.Db4Net.Db.EntitiesToDataTable(_list);
8)将实体对象转换为Dictionary<string,string>
using JCoder.Db4Net;
var t = new T();
// Property only.
// 实体对象转换为Dictionary<string,string>
Dictionary<string, string> _dict = JCoder.Db4Net.Db.ObjectToDict(t);
Dictionary<string, string> _dict = t.ObjectToDict();
// Attributes first
// 实体对象转换为Dictionary<string,string>
Dictionary<string, string> _dict = JCoder.Db4Net.Db.EntityToDict(t);
Dictionary<string, string> _dict = t.EntityToDict();