public static void SaveBulkForList(this DataTable dt, string connectionNameConfig, int batchSize, params string[] igrozieColumns)
{
string connectionname = DbConnectionManager.GetConnectionString(connectionNameConfig);
if (batchSize < 1)
{
batchSize = dt.Rows.Count;
}
SqlBulkCopy cp = new SqlBulkCopy(connectionname, SqlBulkCopyOptions.UseInternalTransaction)
{
BatchSize = batchSize, DestinationTableName = dt.TableName
};
dt.Columns.ForEach <DataColumn>(c =>
{
if (igrozieColumns.Contains(c.ColumnName) == false)
{
cp.ColumnMappings.Add(c.ColumnName, c.ColumnName);
}
});
cp.WriteToServer(dt, DataRowState.Added);
cp.Close();
}
internal int BulkAddMonthlyReportDetailLisr(List <B_MonthlyReportDetail> list)
{
int result = list.Count();
try
{
string createTableSql = " SELECT * FROM B_MonthlyReportDetail WHERE 1=2";
string sql = string.Format(@"DELETE
FROM B_MonthlyReportDetail
WHERE MonthlyReportID='{0}';", list[0].MonthlyReportID);
list.ConvertAll(v => v.ID = System.Guid.NewGuid());
list.ConvertAll(v => v.ModifyTime = DateTime.Now);
using (TransactionScope scope = TransactionScopeFactory.Create())
{
var con = new SqlConnection(DbConnectionManager.GetConnectionString(base.ConnectionName));
con.Open();
//创建表结构
SqlCommand createTableCmd = new SqlCommand(createTableSql, con);
SqlDataAdapter sdap = new SqlDataAdapter();
sdap.SelectCommand = createTableCmd;
DataTable dt = new DataTable();
sdap.Fill(dt);
//获取数据
ConvertToTable(dt, list);
SqlCommand cmd = new SqlCommand(sql, con);
cmd.ExecuteNonQuery();
SqlBulkCopy bulkCopy = new SqlBulkCopy(con);
bulkCopy.DestinationTableName = "B_MonthlyReportDetail";
if (dt != null && dt.Rows.Count != 0)
{
bulkCopy.BatchSize = dt.Rows.Count;
}
bulkCopy.WriteToServer(dt);
con.Close();
scope.Complete();
}
}
catch (Exception ex)
{
result = 0;
}
return(result);
}
public bool DeleteNoDefaultVersionMonthlyReport(B_MonthlyReport MonthlyReport)
{
var result = false;
try
{
string sql = string.Format(@"
DELETE B
FROM dbo.B_MonthlyReport AS A
INNER JOIN dbo.B_MonthlyReportDetail AS B ON B.MonthlyReportID = A.ID
WHERE A.SystemID = '{0}'
AND A.FinMonth = {1}
AND A.FinYear = {2}
AND A.ID <> '{3}'
AND ISNULL(A.AreaID,'00000000-0000-0000-0000-000000000000') = '{4}'
AND WFStatus<>'Approved';
DELETE dbo.B_MonthlyReport
WHERE SystemID = '{0}'
AND FinMonth = {1}
AND FinYear = {2}
AND ID <> '{3}'
AND ISNULL(AreaID,'00000000-0000-0000-0000-000000000000') = '{4}'
AND WFStatus<>'Approved'; ", MonthlyReport.SystemID,
MonthlyReport.FinMonth, MonthlyReport.FinYear, MonthlyReport.ID, MonthlyReport.AreaID);
using (TransactionScope scope = TransactionScopeFactory.Create())
{
var con = new SqlConnection(DbConnectionManager.GetConnectionString(base.ConnectionName));
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
result = cmd.ExecuteNonQuery() > 0;
con.Close();
scope.Complete();
}
}
catch (Exception ex)
{
result = false;
}
return(result);
}
public static void BatchUpdate(this DataTable dt, string connectionName, Action <List <string> > whereColunmAction, Action <List <string> > updateColumnAction, int batchSize = 1000)
{
dt.Locale = System.Globalization.CultureInfo.InvariantCulture;
string connectionnString = DbConnectionManager.GetConnectionString(connectionName);
using (SqlDataAdapter adapter = new SqlDataAdapter())
{
adapter.SelectCommand = null;
adapter.UpdateCommand = ReadUpdateSql(dt.TableName, whereColunmAction, updateColumnAction);
adapter.UpdateBatchSize = batchSize;
adapter.AcceptChangesDuringUpdate = true;
adapter.ContinueUpdateOnError = true;
using (SqlConnection conon = new SqlConnection(connectionnString))
{
adapter.UpdateCommand.Connection = conon;
SqlTransaction tran = null;
try
{
conon.Open();
tran = conon.BeginTransaction();
adapter.UpdateCommand.Transaction = tran;
adapter.Update(dt);
tran.Commit();
}
catch
{
if (tran != null)
{
tran.Rollback();
}
throw;
}
finally
{
conon.Close();
}
}
}
}
public void GetConnectionStringTest()
{
var config = DbConnectionManager.GetConnectionString("DynamicsEntity");
Assert.IsTrue(config != null);
}
protected override void OnInit(EventArgs e)
{
sqlDataSource.ConnectionString = DbConnectionManager.GetConnectionString("defaultDatabase");;
base.OnInit(e);
}
protected void Page_Load(object sender, EventArgs e)
{
if (!TimePointContext.Current.UseCurrentTime)
{
this.grid.ReadOnly = true;
}
//给隐藏域赋值
Dictionary <string, string> enumValueKey = new Dictionary <string, string>();
Dictionary <string, string> enumKeyValue = new Dictionary <string, string>();
Type fileTypes = typeof(FieldTypeEnum);
foreach (string s in Enum.GetNames(fileTypes))
{
FieldTypeEnum myEnum = (FieldTypeEnum)Enum.Parse(typeof(FieldTypeEnum), s);
enumValueKey.Add(((int)myEnum).ToString(), s);
enumKeyValue.Add(s, ((int)myEnum).ToString());
}
HF_EnumValueKey.Value = JSONSerializerExecute.Serialize(enumValueKey, typeof(object));
HF_EnumKeyValue.Value = JSONSerializerExecute.Serialize(enumKeyValue, typeof(object));
if (this.IsPostBack == false && this.IsCallback == false)
{
ControllerHelper.ExecuteMethodByRequest(this);
}
this.PropertyEditorRegister();
WebUtility.RequiredScript(typeof(ClientGrid));
ETLEntity etlEntity = InitETLEntity(Request.QueryString["ID"], Request.QueryString["CategoryID"]);
this.bindingControl.Data = etlEntity;
if (!IsPostBack)
{
//绑定字段类型
this.ddl_FieldType.BindData(EnumItemDescriptionAttribute.GetDescriptionList(typeof(FieldTypeEnum)), "Name", "Description");
this.gridUepUsers.InitialData = etlEntity.sapInstanceParams;
this.lastUpdateTime.Value = etlEntity.LastUpdateTime == DateTime.MinValue ? Convert.ToDateTime("1900-01-01 00:00:00") : etlEntity.LastUpdateTime;
List <DBInfo> dbBaseInfo = new List <DBInfo>();
//判断数据库登录主键是否存在
if (etlEntity.TargetConnCode.IsNullOrEmpty())
{
//数据库登录信息显示
if (!etlEntity.ServerAddress.IsNullOrEmpty() && !etlEntity.DataBase.IsNullOrEmpty() && !etlEntity.Uid.IsNullOrEmpty())
{
List <DBInfo> result = DBInfoAdapter.Instance.DBInfoIsExit(etlEntity.Uid, etlEntity.ServerAddress, etlEntity.DataBase);
if (result.Count == 0)
{
var dbInfo = new DBInfo();
string code = Guid.NewGuid().ToString();
//数据库登录信息的主键
dbInfo.DBCode = code;
//数据库登录账号
dbInfo.DBLoginID = etlEntity.Uid;
//数据库登录地址
dbInfo.DBAddr = etlEntity.ServerAddress;
//数据库名称
dbInfo.DBName = etlEntity.DataBase;
//数据库登录密码
dbInfo.DBPassword = etlEntity.Pwd;
DBInfoAdapter.Instance.Update(dbInfo);
}
}
//没数据时的显示
var showDbinfo = new DBInfo();
showDbinfo.DBCode = "";
showDbinfo.DBLoginID = "";
showDbinfo.DBAddr = "";
showDbinfo.DBName = "";
showDbinfo.DBPassword = "";
dbBaseInfo.Add(showDbinfo);
}
else
{
//绑定登录数据库信息主键
HF_TargetConnCode.Value = etlEntity.TargetConnCode;
if (DBInfoAdapter.Instance.GetByID(etlEntity.TargetConnCode) != null)
{
dbBaseInfo.Add(DBInfoAdapter.Instance.GetByID(etlEntity.TargetConnCode));
}
else
{
//没数据时的显示
var showDbinfo = new DBInfo();
showDbinfo.DBCode = "";
showDbinfo.DBLoginID = "";
showDbinfo.DBAddr = "";
showDbinfo.DBName = "";
showDbinfo.DBPassword = "";
dbBaseInfo.Add(showDbinfo);
}
}
this.gridDBInfo.InitialData = dbBaseInfo;
string etlConStr = string.Empty;
try
{
etlConStr = etlEntity.ETLConnectionString;
}
catch (Exception)
{ }
//给隐藏域赋值
string connStr = string.Format("权限中心:{0},该ETL实体的数据库地址：{1}",
DbConnectionManager.GetConnectionString("PermissionsCenter"), etlConStr);
this.connStrHidd.Value = connStr;
}
}
