[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; } }