[Description("Fill a DataSet and update the source data")] private void FillAndUpdateDataset()
{
string selectSQL = "SELECT Id, Name, Value FROM SampleData WHERE Id > 0";
string addSQL = "INSERT INTO Products (Name, Value) VALUES (@name, @value);";
string updateSQL = "UPDATE SampleData SET Name = @name, Value = @value WHERE Id = @id";
string deleteSQL = "DELETE FROM SampleData WHERE Id = @id";
// Fill a DataSet from the Products table using the simple approach
DataSet simpleDS = db.ExecuteDataSet(CommandType.Text, selectSQL);
//DisplayTableNames(simpleDS, "ExecuteDataSet")
simpleDS = null;
// Fill a DataSet from the Products table using the LoadDataSet method
// This allows you to specify the name(s) for the table(s) in the DataSet
DataSet loadedDS = new DataSet("ProductsDataSet");
db.LoadDataSet(CommandType.Text, selectSQL, loadedDS, new string[] { "Products" });
//DisplayTableNames(loadedDS, "LoadDataSet")
// Update some data in the rows of the DataSet table
DataTable dt = loadedDS.Tables["Products"];
dt.Rows[0].Delete();
object[] rowData = new object[] { -1, "A New Row", "Added to the table at " + DateTime.Now.ToShortTimeString() };
dt.Rows.Add(rowData);
rowData = dt.Rows[1].ItemArray;
rowData[2] = "A new description at " + DateTime.Now.ToShortTimeString();
dt.Rows[1].ItemArray = rowData;
//DisplayRowValues(dt)
// Create the commands to update the original table in the database
DbCommand insertCommand = db.GetSqlStringCommand(addSQL);
db.AddInParameter(insertCommand, "name", DbType.String, "Name", DataRowVersion.Current);
db.AddInParameter(insertCommand, "description", DbType.String, "Description", DataRowVersion.Current);
DbCommand updateCommand = db.GetSqlStringCommand(updateSQL);
db.AddInParameter(updateCommand, "name", DbType.String, "Name", DataRowVersion.Current);
db.AddInParameter(updateCommand, "description", DbType.String, "Description", DataRowVersion.Current);
db.AddInParameter(updateCommand, "id", DbType.String, "Id", DataRowVersion.Original);
DbCommand deleteCommand = db.GetSqlStringCommand(deleteSQL);
db.AddInParameter(deleteCommand, "id", DbType.Int32, "Id", DataRowVersion.Original);
// Apply the updates in the DataSet to the original table in the database
int rowsAffected = db.UpdateDataSet(loadedDS, "Products", insertCommand, updateCommand, deleteCommand, UpdateBehavior.Standard);
Console.WriteLine("Updated a total of {0} rows in the database.", rowsAffected);
}
/// <summary>
/// 获取指定的表(适用含虚体表的T)
/// 示例（指定表获取）：DAOHelp.UniversalizationGetAll<DSSLMSEntering>(CommonApp.DBFactory.CreateDefault(), new List<string>() { "[SLMSDB_I].[dbo].[SampleInfo]", "[SLMSDB_I].[dbo].[SampleResult]" });
/// </summary>
/// <typeparam name="T">数据集</typeparam>
/// <param name="_db"></param>
/// <param name="_tablenames">注意表名要用数据库全称</param>
/// <returns></returns>
public static T UniversalizationGetAll <T>(Microsoft.Practices.EnterpriseLibrary.Data.Database _db, List <string> _tablenames)
{
try
{
var model = Activator.CreateInstance <T>();
var sql = _tablenames.Aggregate("", (current, _tb) => current + ("select * from " + _tb + ";"));
var cmd = _db.GetSqlStringCommand(sql);
//注意：如果需要使返回的table名不含有数据库表的限定名，则如下：
var list = new List <string>();
foreach (var tbn in _tablenames)
{
var ele = "";
if (tbn.Contains("."))
{
ele = (tbn.Split('.'))[(tbn.Split('.')).Length - 1];
}
if (ele.StartsWith("["))
{
ele = ele.Substring(1, ele.Length - 2);
}
list.Add(ele);
}
_db.LoadDataSet(cmd, model as DataSet, list.ToArray());//return回来的dataset表名
return(model);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 限定数据库表前缀查询（要求T所有表均有对应的实体表）
/// 示例（所有表获取）：DAOHelp.UniversalizationGetAll<DSSLMSEntering>(CommonApp.DBFactory.CreateDefault(), "[SLMSDB_I].[dbo].");
/// </summary>
/// <typeparam name="T">数据集</typeparam>
/// <param name="_db"></param>
/// <param name="_tablenameprefix">前缀为空时，则指数据集表名与数据库表名一致</param>
/// <returns></returns>
public static T UniversalizationGetAll <T>(Microsoft.Practices.EnterpriseLibrary.Data.Database _db, string _tablenameprefix)
{
try
{
var type = typeof(T);
var ppt = type.GetProperties();
var model = Activator.CreateInstance <T>();
var sql = "";
//获取表集合
var tbs = new List <string>();
foreach (var t in ppt)
{
var eltype = t.ToString().Split(' ')[0];
if (!eltype.Contains("DataTable"))
{
continue;
}
if (eltype.Substring(eltype.Length - 9) != "DataTable")
{
continue;
}
sql += "select * from " + _tablenameprefix + t.Name + ";"; //注意：xsd中表名应当跟数据库表名一致
tbs.Add(t.Name);
}
var cmd = _db.GetSqlStringCommand(sql);
_db.LoadDataSet(cmd, model as DataSet, tbs.ToArray());//return回来的dataset表名
return(model);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 查询数据库是否有对应记录（是否唯一）
/// </summary>
/// <param name="_db"></param>
/// <param name="_tablename">>注意表名要用数据库全称</param>
/// <param name="_sqlwhere">限定语句，必须and开头，可留空</param>
/// <returns></returns>
public static bool UniversalizationGetUnique(Microsoft.Practices.EnterpriseLibrary.Data.Database _db, string _tablename, string _sqlwhere)
{
try
{
var ds = new DataSet();
var sql = "select * from " + _tablename + " where 1=1 " + _sqlwhere;
var cmd = _db.GetSqlStringCommand(sql);
_db.LoadDataSet(cmd, ds, _tablename);
return(ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 根据条件where获取单表
/// </summary>
/// <param name="_db"></param>
/// <param name="_tablename">注意表名要用数据库全称</param>
/// <param name="_sqlwhere">限定语句，必须and开头，可留空</param>
/// <returns></returns>
public static T UniversalizationGetwhere <T>(Microsoft.Practices.EnterpriseLibrary.Data.Database _db, string _tablename, string _sqlwhere)
{
try
{
var sql = "select * from " + _tablename + " where 1=1 " + _sqlwhere;
var model = Activator.CreateInstance <T>();
var cmd = _db.GetSqlStringCommand(sql);
//注意：如果需要使返回的table名不含有数据库表的限定名，则如下：
if (_tablename.Contains("."))
{
_tablename = (_tablename.Split('.'))[(_tablename.Split('.')).Length - 1];
}
if (_tablename.StartsWith("["))
{
_tablename = _tablename.Substring(1, _tablename.Length - 2);
}
_db.LoadDataSet(cmd, model as DataSet, _tablename);//return回来的dataset表名
return(model);
}
catch (Exception ex)
{
throw ex;
}
}
