本文整理汇总了C#中Dbconn.ExcuteQuerryByTran方法的典型用法代码示例。如果您正苦于以下问题：C# Dbconn.ExcuteQuerryByTran方法的具体用法？C# Dbconn.ExcuteQuerryByTran怎么用？C# Dbconn.ExcuteQuerryByTran使用的例子？那么恭喜您, 这里精选的方法代码示例或许可以为您提供帮助。您也可以进一步了解该方法所在类Dbconn的用法示例。
在下文中一共展示了Dbconn.ExcuteQuerryByTran方法的15个代码示例，这些例子默认根据受欢迎程度排序。您可以为喜欢或者感觉有用的代码点赞，您的评价将有助于系统推荐出更棒的C#代码示例。
示例1: GetData
/// <summary>
/// EnvoTemplates信息的CRUD
/// </summary>
/// <returns></returns>
public DataSet GetData()
{
DataSet ds_Return = new DataSet();
string strXML = m_request.Tables[0].Rows[0]["XML"].ToString();
if (strXML == "")
{
throw new Exception("XML不能为空");
}
string strTemplate_ID = m_request.Tables[0].Rows[0]["Template_ID"].ToString();
if (strTemplate_ID == "")
{
throw new Exception("Template_ID不能为空");
}
DataSet ds = Common.Common.GetDSByExcelXML(strXML);
DataTable dt_OPTYPE = ds.Tables["OPTYPE"];
Dbconn conn = new Dbconn("HZY_SDK");
if (dt_OPTYPE.Rows[0][0].ToString().ToUpper() == "INSERT")
{
bool bResult = true;
DataTable dt = new DataTable();
dt.Columns.Add("Template_ID");
dt.Rows.Add(new object[] { strTemplate_ID });
Commom_Ds.SetUpdateInfo(dt, hzyMessage);
conn.BeginTransaction();
bResult = conn.Insert("Log_ViewTemlpates", dt);
conn.ExcuteQuerryByTran("UPDATE EnvoTemplates set ViewCount=ViewCount+1 where Template_ID=" + strTemplate_ID);
conn.CommitTransaction();
if (bResult)
{
dt_EditResult.Rows.Add(new object[] { true, "修改成功" });
}
else
{
dt_EditResult.Rows.Add(new object[] { false, "修改失败" });
}
ds_Return.Tables.Add(dt_EditResult);
}
return ds_Return;
}示例2: GetData
public DataSet GetData()
{
DataSet ds_Return = null;
Dbconn conn = new Dbconn("WSRR_Log");
conn.BeginTransaction();
conn.LockTableList.Add("Log_CallByXML");
conn.LockTableList.Add("Log_CallByXML_His");
string time = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd HH:mm:ss.fff");
DataSet ds = conn.GetDataSetInTrans(@" SELECT
*,
NULL AS ElapsedTime,
NULL AS WS_ID,
NULL AS Session_ID,
NULL AS AppSN
FROM Log_CallByXML WHERE Log_Time<'" + time + "'");
conn.ExcuteQuerryByTran("INSERT INTO Log_CallByXML_HIS SELECT * FROM Log_CallByXML WHERE Log_Time<'" + time + "'");
conn.CommitTransaction();
//开始分析
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
string strXML = ds.Tables[0].Rows[i]["XmlInput"].ToString();
string strWS_ID = "";
string strSession_ID = "";
string strAppSN = "";
try
{
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.LoadXml(strXML);
XmlNode xml = xmlDoc.DocumentElement;
strAppSN = xml.Attributes["App"].Value;
strWS_ID = xml.Attributes["WSID"].Value;
strSession_ID = xml.Attributes["SessionID"].Value;
xml = null;
xmlDoc = null;
}
catch (Exception)
{
}
//ds.Tables[0].Rows
}
return ds_Return;
}示例3: Execute
/// <summary>
/// 供销售日报使用
/// </summary>
/// <returns></returns>
public bool Execute()
{
Dbconn conn_PLM_AS = new Dbconn("F22GP_ForSalesPlan");
DataSet ds = conn_PLM_AS.GetDataSet(
@" SELECT 94 AS Env_ID , --HZY_Sale_Report
null AS VexSSONewID ,
b.userid AS App_UserName ,
RIGHT(sys.fn_VarBinToHexStr(hashbytes('MD5', CONVERT(VARCHAR(100), b.password))), 32) AS App_Password ,
b.password AS App_PasswordSrc ,
b.username AS App_UserName_CN ,
GETDATE() Chang_Password_Time ,
1 Checked ,
'system' Check_User ,
GETDATE() Check_Time ,
GETDATE() Createtime ,
GETDATE() Updatetime
FROM j_depot a ,
j_user b
WHERE a.depotid = b.depotid
AND a.m_type = 11
");
Dbconn connMDM = new Dbconn("VexSSO");
try
{
ArrayList tableList = new ArrayList();
connMDM.BeginTransaction();
tableList.Add("MDM_System_User");
connMDM.TableLock(tableList);
//删除数据
connMDM.ExcuteQuerryByTran(@"DELETE FROM MDM_System_User WHERE ENV_ID=94");
connMDM.InsertBYSQLBC("MDM_System_User", ds.Tables[0]);
connMDM.CommitTransaction();
return true;
}
catch
{
connMDM.RollbackTransaction();
throw;
}
}示例4: Execute
/// <summary>
/// 对CFG_RequestType的CRUD,为Easyui专用
/// </summary>
/// <returns></returns>
public bool Execute()
{
DateTime time = DateTime.Now;
Dbconn conn = new Dbconn("VexSSO");
conn.LockTableList.Add("SSO_Login");
conn.BeginTransaction();
conn.TableLock();
string strSQL = @"
UPDATE dbo.SSO_Login
SET Logout_Time='" + time.ToString("yyyy-MM-dd HH:mm:ss.fff")+ @"',
MARK='SYSTEM'
WHERE Last_Request_Time<='" + time.AddDays(-1).ToString("yyyy-MM-dd HH:mm:ss.fff") + @"'
AND Logout_Time IS NULL
";
conn.ExcuteQuerryByTran(strSQL);
conn.CommitTransaction();
return true ;
}示例5: GetData
//.........这里部分代码省略.........
,'' as [PRAD_LmDt]
,'0' as [PRAD_UptNo]
FROM B02_Bill.PRA_ArrivalPlan
LEFT JOIN B02_Bill.PR1D_OrderDetail ON PRA_PR1_OrderId=PR1D_PR1_OrderId
LEFT JOIN B02_Bill.PRAD_ArrivalPlanDetail ON PR1D_OrderDetailId=PRAD_PR1D_OrderDetailId
WHERE " + strWHERE1
+ @"GROUP BY [PRAD_CONO],[PRAD_DIVI] ,[PRA_ArrivalPlanId],[PR1D_PD0_ProductId]
,[PR1D_OrderDetailId],[PR1D_UnitID]
,[PR1D_Total]";
}
ds_Return = conn.GetDataSet(strSQL);
// }
}
else
{
ArrayList tableList = new ArrayList();
tableList.Add("B02_Bill.PRAD_ArrivalPlanDetail");
conn.BeginTransaction();
//开启事物
conn.TableLock(tableList);
//锁表
try
{
if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "DELETE")
{
for (int i = 0; i < ds.Tables["LIST"].Rows.Count; i++)
{
strSQL = @"UPDATE B02_Bill.PRAD_ArrivalPlanDetail SET PRAD_Status='E3CA9B7A-8E17-4E52-B667-39689445D4A1'
WHERE PRAD_PRA_ArrivalPlanId='" + ds.Tables["LIST"].Rows[i]["PRAD_PRA_ArrivalPlanId"].ToString() + @"'
AND iusnull(PRA_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1' ";
conn.ExcuteQuerryByTran(strSQL);
}
}
else if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "EDIT")
{
for (int i = 0; i < ds.Tables["LIST"].Rows.Count; i++)
{
DataTable dt = ds.Tables["LIST"].Clone();
dt.ImportRow(ds.Tables["LIST"].Rows[i]);
strSQL = @"SELECT * FROM B02_Bill.PRAD_ArrivalPlanDetail WHERE [email protected]
AND PRAD_Status<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'
";
DataTable dt_SR = conn.GetDataTable(strSQL, new string[1] { ds.Tables["LIST"].Rows[i]["PRAD_ArrivalPlanDetailId"].ToString() });
//update
if (dt_SR.Rows.Count > 0)
{
//处理结果
if (ds.Tables["LIST"].Rows[i]["PRAD_LmUser"].ToString() != "")
{
string strCode = dt_SR.Rows[0]["PRAD_UptNo"].ToString();
if (strCode == "0")
{
strCode = Convert.ToInt32(strCode) + 1.ToString();
}
else
{
strCode = "0";
}
if (!dt.Columns.Contains("PRAD_LmDt"))示例6: Execute
public bool Execute()
{
Dbconn conn = new Dbconn("DBDict");
//取到所有的数据库服务器
DataTable dt_DBServer = conn.GetDataTable("SELECT * FROM DBServer ", new string[0]);
//清空数据
conn.ExcuteQuerryByTran(@"
TRUNCATE TABLE DBDict.dbo.TableDict
TRUNCATE TABLE DBDict.dbo.TableSize
TRUNCATE TABLE DBDict.dbo.DBInfo
TRUNCATE TABLE DBDict.dbo.TableIndex
TRUNCATE TABLE DBDict.dbo.DBOtherInfo
TRUNCATE TABLE DBDict.dbo.LinkServers
");
if (dt_DBServer.Rows.Count > 0)
{
for (int i = 0; i < dt_DBServer.Rows.Count; i++)
{
string strConnString = "DataBase=master;Password=" + dt_DBServer.Rows[i]["sql密码"].ToString() + ";User ID=" + dt_DBServer.Rows[i]["sql用户名"].ToString() + ";Data Source=" + dt_DBServer.Rows[i]["ip"].ToString() + ";";
string strSQL = "SELECT Name FROM Master..SysDatabases ORDER BY Name";
Dbconn conn_AllDB = new Dbconn(strConnString, true);
//所有的数据库名
DataTable dt_AllDB = conn_AllDB.GetDataTable(strSQL, new string[0]);
for (int k = 0; k < dt_AllDB.Rows.Count; k++)
{
if (dt_AllDB.Rows[k][0].ToString() == "tempdb")
{
continue;
}
strConnString = "DataBase=" + dt_AllDB.Rows[k][0].ToString() + ";Password=" + dt_DBServer.Rows[i]["sql密码"].ToString() + ";User ID=" + dt_DBServer.Rows[i]["sql用户名"].ToString() + ";Data Source=" + dt_DBServer.Rows[i]["ip"].ToString() + ";";
Dbconn conn_DB = new Dbconn(strConnString, true);
string str_获取数据库中所有表的字段属性 = @"
SELECT @@SERVERNAME 服务器,db_name() 数据库名,d.name AS 表名,
CASE WHEN a.colorder = 1 THEN isnull(f.value, '') ELSE '' END AS 表说明,
a.colorder AS 字段序号, a.name AS 字段名, CASE WHEN COLUMNPROPERTY(a.id,
a.name, 'IsIdentity') = 1 THEN '√' ELSE '' END AS 标识,
CASE WHEN EXISTS
(SELECT 1
FROM dbo.sysindexes si INNER JOIN
dbo.sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid INNER JOIN
dbo.syscolumns sc ON sc.id = sik.id AND sc.colid = sik.colid INNER JOIN
dbo.sysobjects so ON so.name = si.name AND so.xtype = 'PK'
WHERE sc.id = a.id AND sc.colid = a.colid) THEN '√' ELSE '' END AS 主键,
b.name AS 类型, a.length AS 长度, COLUMNPROPERTY(a.id, a.name, 'PRECISION')
AS 精度, ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS 小数位数,
CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END AS 允许空, ISNULL(e.text, '')
AS 默认值, ISNULL(g.[value], '') AS 字段说明, d.crdate AS 创建时间,
CASE WHEN a.colorder = 1 THEN d.refdate ELSE NULL END AS 更改时间,
GETDATE() 采集时间
FROM dbo.syscolumns a with(nolock) LEFT OUTER JOIN
dbo.systypes b with(nolock) ON a.xtype = b.xusertype INNER JOIN
dbo.sysobjects d with(nolock) ON a.id = d.id AND d.xtype = 'U' AND
d.status >= 0 LEFT OUTER JOIN
dbo.syscomments e with(nolock) ON a.cdefault = e.id LEFT OUTER JOIN
sys.extended_properties g with(nolock) ON a.id = g.major_id AND a.colid = g.minor_id AND
g.name = 'MS_Description' LEFT OUTER JOIN
sys.extended_properties f with(nolock) ON d.id = f.major_id AND f.minor_id = 0 AND
f.name = 'MS_Description'
ORDER BY d.name, 字段序号
";
string str_显示当前数据库中用户表的记录数量及大小 = @"
SELECT @@servername 服务器,db_name() 数据库,OBJECT_NAME(id) AS 表名,
rowcnt AS 记录数量,
8 * reserved / 1024 AS '大小(MB)',
GETDATE() 采集时间
FROM sysindexes a with(nolock)
LEFT JOIN sys.objects b with(nolock)
ON a.name = b.name
WHERE indid < 2
AND OBJECTPROPERTY(id, 'isusertable ') = 1
ORDER BY
rowcnt DESC
";
string str_查询所有表的索引 = @"
SELECT @@SERVERNAME AS 服务器,DB_NAME() AS 数据库,
c.name AS 表名,
a.name AS 索引名称, d.name AS 列名,
b.keyno AS 索引顺序, CASE indexkey_property(c.id, b.indid, b.keyno, 'isdescending')
WHEN 1 THEN '降序' WHEN 0 THEN '升序' END AS 排序, CASE WHEN p.id IS NULL
THEN '' ELSE '√' END AS 主键, CASE INDEXPROPERTY(c.id, a.name, 'IsClustered')
//.........这里部分代码省略.........
示例7: Execute
public bool Execute()
{
Dbconn m_Conn_MDM = new Dbconn("MDM");
DataSet Config = new DataSet();
string strSql = @" SELECT [Com] ,
[Type] ,
[Conn] ,
[SKUTable]
FROM [DRPStlyeInfoSyncConfig] ";
Config = m_Conn_MDM.GetDataSet(strSql);
for (int i = 0; i < Config.Tables[0].Rows.Count; i++)
{
string Com = Config.Tables[0].Rows[i]["Com"].ToString();
string Type = Config.Tables[0].Rows[i]["Type"].ToString();
string SKUTable = Config.Tables[0].Rows[i]["SKUTable"].ToString();
string Conn = Config.Tables[0].Rows[i]["Conn"].ToString();
Dbconn BillConn = new Dbconn(Conn);
DataSet DRPSKU = m_Conn_F22GP_DRP_REPORT.GetDataSetBySP("SP_OnlineStyle", new string[3] { "@COM", "@Type", "@Styleid" },
new string[3] { Com, Type, "" });
try
{
BillConn.BeginTransaction();
if (DRPSKU.Tables[0] == null || DRPSKU.Tables[0].Rows.Count == 0)
{
throw new Exception("请传入需要导入的数据！");
}
BillConn.ExcuteQuerryByTran("TRUNCATE TABLE " + SKUTable);
BillConn.InsertBYSQLBC(SKUTable, DRPSKU.Tables[0]);
BillConn.CommitTransaction();
//HZY.COM.Common.Log.WirteLogWS("成功", null);
//return true;
}
catch (SqlException ex)
{
BillConn.RollbackTransaction();
if (ex.Message.Contains("唯一索引"))
{
dt_EditResult.Rows.Add(new object[] { 1, "插入了重复键值，请检查！" });
m_dsRequturn.Tables.Add(dt_EditResult.Copy());
}
else
{
dt_EditResult.Rows.Add(new object[] { 1, ex.Message });
m_dsRequturn.Tables.Add(dt_EditResult.Copy());
}
HZY.COM.Common.Log.WirteLogWS(ex, null);
return false;
}
catch (Exception ex)
{
BillConn.RollbackTransaction();
dt_EditResult.Rows.Add(new object[] { 1, ex.Message });
m_dsRequturn.Tables.Add(dt_EditResult.Copy());
HZY.COM.Common.Log.WirteLogWS(ex, null);
return false;
}
}
dt_EditResult.Rows.Add(new object[] { 0, "成功！" });
m_dsRequturn.Tables.Add(dt_EditResult.Copy());
return true;
}示例8: GetData
//.........这里部分代码省略.........
LEFT JOIN M02_BPM.WFP_AuditFlow J ON PR0_ContractId=J.WFP_BillId AND J.WFP_WFT_AFDId=E.WFT_AFDId
LEFT JOIN M02_BPM.WFP_AuditFlow K ON PR0_ContractId=K.WFP_BillId AND K.WFP_WFT_AFDId=F.WFT_AFDId
LEFT JOIN M01_CONF.DC0_Dictionary L ON G.WFP_AFResultId=L.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary M ON H.WFP_AFResultId=M.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary N ON I.WFP_AFResultId=N.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary O ON J.WFP_AFResultId=O.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary P ON K.WFP_AFResultId=P.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary Q ON PR0_CurrencyId=Q.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary R ON PR0_Status=R.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary S ON PR0_SettleAccount=S.DC0_DictId
WHERE " + strWHERE
+ @"ORDER BY [PR0_LmDt] DESC ";
ds_Return = conn.GetDataSet(strSQL);
}
else
{
ArrayList tableList = new ArrayList();
tableList.Add("B02_Bill.PR0_Contract");
conn.BeginTransaction();
//开启事物
conn.TableLock(tableList);
//锁表
try
{
if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "DELETE")
{
for (int i = 0; i < ds.Tables["LIST"].Rows.Count; i++)
{
strSQL = @"UPDATE B02_Bill.PR0_Contract SET PR0_Status='E3CA9B7A-8E17-4E52-B667-39689445D4A1'
WHERE PR0_ContractId='" + ds.Tables["LIST"].Rows[i]["PR0_ContractId"].ToString() + @"'
AND ISNULL(PR0_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'
";
conn.ExcuteQuerryByTran(strSQL);
}
}
else if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "EDIT")
{
for (int i = 0; i < ds.Tables["LIST"].Rows.Count; i++)
{
DataTable dt = ds.Tables["LIST"].Clone();
dt.ImportRow(ds.Tables["LIST"].Rows[i]);
strSQL = @"SELECT * FROM B02_Bill.PR0_Contract WHERE [email protected]
AND ISNULL(PR0_Status,'') <>'E3CA9B7A-8E17-4E52-B667-39689445D4A1' ";
DataTable dt_SR = conn.GetDataTable(strSQL, new string[1] { ds.Tables["LIST"].Rows[i]["PR0_ContractId"].ToString() });
//update
if (dt_SR.Rows.Count > 0)
{
strSQL = @"SELECT * FROM M02_BPM.WFP_AuditFlow WHERE [email protected]
AND WFP_AFResultId<>''
";
DataTable dt_CO = conn.GetDataTable(strSQL, new string[1] { ds.Tables["LIST"].Rows[i]["PR0_ContractId"].ToString() });
//update
if (dt_CO.Rows.Count > 0)
{
throw new Exception("合同已审核，不能进行修改！");
}
else
//处理结果
if (ds.Tables["LIST"].Rows[i]["PR0_LmUser"].ToString() != "")示例9: Execute
public bool Execute()
{
Dbconn conn = new Dbconn("AM");
Dbconn connMDM = new Dbconn("VexSSO");
conn.BeginTransaction();
connMDM.BeginTransaction();
try
{
DataSet ds = conn.GetDataSet(
@"
DECLARE @maxid AS BINARY(10);
SELECT t1.[__$start_lsn] AS LogID,
t1.[__$seqval] ,
t1.[__$operation] OprationType ,
t1.CN_ID ,
t1.CN_LOGIN ,
t1.CN_USER_NAME ,
t1.CN_PASSWORD ,
Convert(int,t1.CN_ISDELETE) as CN_ISDELETE ,
Convert(int,t1.CN_DISABLED) as CN_DISABLED
INTO #tmp
FROM [cdc].[dbo_TN_SYS_EM_USER_CT] t1 ,
[M01_CONF].[S01_Sync] t2
WHERE 1=1
AND t1.[__$start_lsn]> ISNULL(t2.MaxStart_lsn,0x00023FE600000FBF0004)
AND t2.SyncName='MDM数据获取'
AND t1.[__$update_mask] !=0x00004000
AND t1.[__$update_mask] !=0x00800000
AND t1.[__$update_mask] !=0x00804000
SELECT @maxid=MAX(LogID)
FROM #tmp
if(@maxid is not null)
begin
UPDATE [M01_CONF].[S01_Sync] SET [email protected]
WHERE SyncName='MDM数据获取'
end
select * from #tmp
order by [LogID] ASC,[__$seqval] ASC ,[OprationType] ASC
");
string strSQL = "";
Byte[] strMaxID = null;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
strMaxID = (Byte[])ds.Tables[0].Rows[i]["LogID"];
string strCN_LOGIN = ds.Tables[0].Rows[i]["CN_LOGIN"].ToString();
string strCN_PASSWORD = ds.Tables[0].Rows[i]["CN_PASSWORD"].ToString();
string strCN_ISDELETE = ds.Tables[0].Rows[i]["CN_ISDELETE"].ToString();
string strOprationType = ds.Tables[0].Rows[i]["OprationType"].ToString();
if (strOprationType == "1" || strOprationType == "4") //添加或者更新
{
if (connMDM.GetDataTableRowCount("select 1 as a from MDM_Employees where [email protected]", new string[] { strCN_LOGIN }) == 0)
{
strSQL = @"INSERT INTO MDM_Employees (Name,SSO_UserName,SSO_Password,Leave_Flag,IsActived)
Values(@param0,@param1,@param2,@param3,'1')";
}
else
{
strSQL = @"update MDM_Employees
SET [email protected],[email protected]
WHERE SSO_UserNa[email protected]
";
}
connMDM.ExcuteQuerryByTran(strSQL, new string[] { strCN_LOGIN, strCN_LOGIN, strCN_PASSWORD, strCN_ISDELETE });
}
else if (strOprationType == "2" || strOprationType == "3") //删除
{
strSQL = @"update MDM_Employees
SET [email protected]
WHERE [email protected]";
connMDM.ExcuteQuerryByTran(strSQL, new string[] { "1", strCN_LOGIN });
}
}
if (strMaxID != null)
{
strSQL = "UPDATE M01_CONF.S01_Sync SET [email protected],LastSyncTime=getdate() WHERE SyncName='MDM数据获取'";
conn.ExcuteQuerryByTran(strSQL, new object[] { strMaxID });
}
conn.CommitTransaction();
connMDM.CommitTransaction();
return true;
}
catch
{
//.........这里部分代码省略.........
示例10: Execute
public bool Execute()
{
Dbconn conn = new Dbconn("AM");
DataSet ds = conn.GetDataSet(
@"WITH cte ( AM_S_Dept_ID, AM_P_Dept_ID, AM_Dept_NAME, AM_Dept_NAME_ALL, OrderIndex,AM_Company,AM_Dept1_ID, AM_Dept2_ID, AM_Dept3_ID, AM_Dept4_ID)
AS (--父项
SELECT cn_id AS CN_S_EMITEM_ID ,
0 AS CN_P_EMITEM_ID ,
CONVERT(VARCHAR(200), CN_VIEW_NAME) AS CN_GROUP_NAME ,
CONVERT(VARCHAR(200), CN_VIEW_NAME) AS CN_GROUP_NAME_ALL,
CONVERT(VARCHAR(100), RIGHT('0000'
+ CONVERT(VARCHAR(10), ROW_NUMBER() OVER ( ORDER BY CN_LEVEL )),
4)) OrderIndex ,
cn_id AS Company ,
NULL AS Dept1 ,
NULL AS Dept2 ,
NULL AS Dept3 ,
NULL AS Dept4
FROM dbo.TN_SYS_EM_VIEW WITH ( NOLOCK )
WHERE 1 = 1
AND CN_VIEW_TYPE = 1
AND CN_OWNERID = 0
UNION ALL
--递归结果集中的下级
SELECT t1.CN_S_EMITEM_ID ,
t1.CN_P_EMITEM_ID ,
CONVERT(VARCHAR(200), t3.CN_GROUP_NAME) ,
CONVERT(VARCHAR(200), t2.AM_Dept_NAME_All + '\'
+ t3.CN_GROUP_NAME) AS CN_GROUP_NAME,
CONVERT(VARCHAR(100),OrderIndex + RIGHT('0000'
+ CONVERT(VARCHAR(4), ROW_NUMBER() OVER ( ORDER BY CN_LEVEL, t3.CN_GROUP_NAME )),
4)) ,
t2.AM_Company ,
CASE WHEN LEN(OrderIndex) = 4 THEN t3.CN_ID
WHEN LEN(OrderIndex) > 4 THEN t2.AM_Dept1_ID
ELSE NULL
END AS Dept1 ,
CASE WHEN LEN(OrderIndex) = 8 THEN t3.CN_ID
WHEN LEN(OrderIndex) > 8 THEN t2.AM_Dept2_ID
ELSE NULL
END AS Dept2 ,
CASE WHEN LEN(OrderIndex) = 12 THEN t3.CN_ID
WHEN LEN(OrderIndex) > 12 THEN t2.AM_Dept3_ID
ELSE NULL
END AS Dept3 ,
CASE WHEN LEN(OrderIndex) = 16 THEN t3.CN_ID
WHEN LEN(OrderIndex) > 16 THEN t2.AM_Dept4_ID
ELSE NULL
END AS Dept4
FROM dbo.TN_SYS_EM_RELATION t1 WITH ( NOLOCK )
INNER JOIN cte AS t2 ON t1.CN_P_EMITEM_ID = t2.AM_S_Dept_ID
INNER JOIN dbo.TN_SYS_EM_GROUP t3 WITH ( NOLOCK ) ON t3.cn_id = t1.CN_S_EMITEM_ID
WHERE cn_s_emitem_type = 1
AND CN_OWNERID = 0 --在陈杰的基础上加了这个条件，经过测试，与小翅膀的组织框架一致
)
SELECT *
FROM cte ");
Dbconn connMDM = new Dbconn("MDM");
try
{
ArrayList tableList = new ArrayList();
connMDM.BeginTransaction();
tableList.Add("AM_Dept_log");
tableList.Add("am_dept");
connMDM.TableLock(tableList);
//存入日志数据
connMDM.ExcuteQuerryByTran(@"INSERT INTO dbo.AM_Dept_log
( Log_datetime ,
AM_S_Dept_ID ,
AM_P_Dept_ID ,
AM_Dept_NAME ,
AM_Dept_NAME_ALL,
AM_Company,
AM_Dept1_ID,
AM_Dept2_ID,
AM_Dept3_ID,
AM_Dept4_ID,
CreateTime ,
UpdateTime ,
Timestamps_old
)
SELECT GETDATE() ,
AM_S_Dept_ID ,
AM_P_Dept_ID ,
AM_Dept_NAME ,
AM_Dept_NAME_ALL,
AM_Company,
//.........这里部分代码省略.........
示例11: Execute
public bool Execute()
{
Dbconn conn = new Dbconn("AM");
DataSet ds = conn.GetDataSet(
@"SELECT cn_id as AM_User_id,
CN_LOGIN as AM_login,
CN_USER_NAME as AM_User_Name,
CN_PASSWORD AM_Password,
CN_EMAIL AM_Email,
CN_TITLE AM_Title,
CN_HANDSET AM_Handset,
CN_ISDELETE AM_IsDelete
FROM dbo.TN_SYS_EM_USER t1 ,
dbo.TN_SYS_EM_USER_PROP t2
WHERE t1.cn_id = t2.CN_USERID
");
Dbconn connMDM = new Dbconn("MDM");
try
{
ArrayList tableList = new ArrayList();
connMDM.BeginTransaction();
tableList.Add("AM_User_log");
tableList.Add("AM_User");
connMDM.TableLock(tableList);
// //存入日志数据
// connMDM.ExcuteQuerryByTran(@"INSERT INTO dbo.AM_User_log
// ( Log_datetime ,
// AM_User_id ,
// AM_LOGIN ,
// AM_USER_NAME,
// AM_PASSWORD,
// AM_EMAIL,
// AM_TITLE,
// AM_HANDSET,
// AM_ISDELETE,
// CreateTime ,
// UpdateTime ,
// Timestamps_old
//
// )
// SELECT GETDATE() ,
// AM_User_id ,
// AM_LOGIN ,
// AM_USER_NAME,
// AM_PASSWORD,
// AM_EMAIL,
// AM_TITLE,
// AM_HANDSET,
// AM_ISDELETE,
// CreateTime ,
// UpdateTime,
// CONVERT(BIGINT,Timestamps)
// FROM AM_User;");
//删除数据
connMDM.ExcuteQuerryByTran("DELETE FROM AM_User");
connMDM.InsertBYSQLBC("AM_User", ds.Tables[0]);
//更新MDM_Employees的用户信息
connMDM.ExcuteQuerryByTran(@"UPDATE MDM_Employees
SET SSO_Password=t2.AM_Password,
Leave_Flag=AM_ISDELETE
FROM dbo.AM_User t2 WHERE MDM_Employees.SSO_UserName = t2.AM_login
"
);
//并添加新入职的数据
connMDM.ExcuteQuerryByTran(@"
INSERT INTO dbo.MDM_Employees
(
Name ,
Sex ,
Phone ,
Moblie ,
SSO_UserName ,
SSO_Password ,
In_Time ,
Leave_Time ,
Leave_Flag ,
IsActived
)
SELECT
AM_login ,
NULL,
NULL,
NULL,
AM_login ,
AM_Password ,
GETDATE(),
//.........这里部分代码省略.........
示例12: GetData
//.........这里部分代码省略.........
,H.[DC0_DictName1] AS PR1_SupplierType
FROM GL_ERP.B02_Bill.PR1_Order WITH ( NOLOCK )
LEFT JOIN B01_MDM.SU0_Supplier A ON PR1_SU0_SupplierId=A.SU0_SupplierId
LEFT JOIN B01_MDM.BD0_Brand B ON PR1_BD0_BrandId=B.BD0_BrandId
LEFT JOIN B02_Bill.PR0_Contract C ON PR1_PR0_ContractId=C.PR0_ContractId
LEFT JOIN M01_CONF.DC0_Dictionary D ON PR1_OrderTypeId=D.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary F ON PR1_Status=F.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary G ON PR1_CurrencyTypeId=G.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary H ON A.SU0_SupplierType=H.DC0_DictId
WHERE " + strWHERE
+ @"ORDER BY [PR1_LmDt] DESC ";
ds_Return = conn.GetDataSet(strSQL);
}
else
{
ArrayList tableList = new ArrayList();
tableList.Add("GL_ERP.B02_Bill.PR1_Order");
conn.BeginTransaction();
//开启事物
conn.TableLock(tableList);
//锁表
try
{
if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "DELETE")
{
for (int i = 0; i < ds.Tables["LIST"].Rows.Count; i++)
{
strSQL = @"UPDATE GL_ERP.B02_Bill.PR1_Order SET PR1_Status='E3CA9B7A-8E17-4E52-B667-39689445D4A1'
WHERE PR1_OrderId='" + ds.Tables["LIST"].Rows[i]["PR1_OrderId"].ToString() + @"'
AND ISNULL(PR1_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'
";
conn.ExcuteQuerryByTran(strSQL);
strSQL = @"UPDATE B02_Bill.PR1D_OrderDetail SET PR1D_Status='E3CA9B7A-8E17-4E52-B667-39689445D4A1'
WHERE PR1D_PR1_OrderId='" + ds.Tables["LIST"].Rows[i]["PR1_OrderId"].ToString() + @"'
AND isnull(PR1D_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'
";
conn.ExcuteQuerryByTran(strSQL);
}
}
else if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "EDIT")
{
for (int i = 0; i < ds.Tables["LIST"].Rows.Count; i++)
{
DataTable dt = ds.Tables["LIST"].Clone();
dt.ImportRow(ds.Tables["LIST"].Rows[i]);
strSQL = @"SELECT * FROM GL_ERP.B02_Bill.PR1_Order WHERE [email protected]
AND ISNULL(PR1_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'
";
DataTable dt_SR = conn.GetDataTable(strSQL, new string[1] { ds.Tables["LIST"].Rows[i]["PR1_OrderId"].ToString() });
//update
if (dt_SR.Rows.Count > 0)
{
//处理结果
if (ds.Tables["LIST"].Rows[i]["PR1_LmUser"].ToString() != "")
{
string strCode = dt_SR.Rows[0]["PR1_UptNo"].ToString();
if (strCode == "0")示例13: GetData
//.........这里部分代码省略.........
LEFT JOIN M01_CONF.DC0_Dictionary E ON PR1D_Status=E.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary F ON B.PD0_SKUSU=F.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary G ON B.PD0_Code=G.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary H ON PR1D_CurrencyTypeId=H.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary I ON B.PD0_ColorId=H.DC0_DictId
WHERE " + strWHERE
+ @"ORDER BY [PR1D_LmDt] DESC ";
// ,PD0_SizeSUId
// ,I.DC0_DictName2 AS ColorBSUIdDictName
// ,I.DC0_DictName1 AS ColorSUIdDictName
// ,K.DC0_DictName1 AS SizeSUIdTypeDictName
ds_Return = conn.GetDataSet(strSQL);
}
else
{
ArrayList tableList = new ArrayList();
tableList.Add("B02_Bill.PR1D_OrderDetail");
conn.BeginTransaction();
//开启事物
conn.TableLock(tableList);
//锁表
try
{
if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "DELETE")
{
for (int i = 0; i < ds.Tables["LIST"].Rows.Count; i++)
{
strSQL = @"UPDATE B02_Bill.PR1D_OrderDetail SET PR1D_Status='E3CA9B7A-8E17-4E52-B667-39689445D4A1'
WHERE PR1D_OrderDetailId='" + ds.Tables["LIST"].Rows[i]["PR1D_OrderDetailId"].ToString() + @"'
AND isnull(PR1D_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'
";
conn.ExcuteQuerryByTran(strSQL);
strSQL = @"SELECT * FROM B02_Bill.PR1D_OrderDetail WHERE [email protected]
AND isnull(PR1D_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'
";
DataTable dt_SR = conn.GetDataTable(strSQL, new string[1] { ds.Tables["LIST"].Rows[i]["PR1D_PR1_OrderId"].ToString() });
//update
if (dt_SR.Rows.Count > 0)
{
strSQL = @"UPDATE AA SET AA.PR1_PurchaseTotal=BB.PR1_PurchaseTotal,
AA.PR1_PurchaseAmount=BB.PR1_PurchaseAmount,AA.PR1_POriginalAmount=BB.PR1_POriginalAmount,
AA.PR1_LmUser='" + ds.Tables["LIST"].Rows[i]["PR1D_LmUser"].ToString() + @"',
AA.PR1_LmDt=CONVERT(VARCHAR(100),GETDATE(),121),
AA.PR1_UptNo=AA.PR1_UptNo+1
FROM
B02_Bill.PR1_Order AA,
(SELECT SUM(PR1D_Total) AS PR1_PurchaseTotal,SUM(PR1D_OriginalAmount) AS PR1_POriginalAmount,
SUM(PR1D_EstimateCost) AS PR1D_EstimateCost,SUM(PR1D_CurrencyAmount) AS PR1_PurchaseAmount,PR1D_PR1_OrderId
FROM B02_Bill.PR1D_OrderDetail WHERE PR1D_PR1_OrderId='" + ds.Tables["LIST"].Rows[i]["PR1D_PR1_OrderId"].ToString() + @"'
AND isnull(PR1D_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'
GROUP BY PR1D_PR1_OrderId)BB
WHERE AA.PR1_OrderId=BB.PR1D_PR1_OrderId";
conn.ExcuteQuerryByTran(strSQL);
}
else
{
strSQL = @"UPDATE AA SET AA.PR1_PurchaseTotal='0',
AA.PR1_PurchaseAmount='0',AA.PR1_POriginalAmount='0',
AA.PR1_LmUser='" + ds.Tables["LIST"].Rows[i]["PR1D_LmUser"].ToString() + @"',
AA.PR1_LmDt=CONVERT(VARCHAR(100),GETDATE(),121),
AA.PR1_UptNo=AA.PR1_UptNo+1
FROM 示例14: GetData
/// <summary>
/// 根据XML内容更新品牌
/// </summary>
/// <returns></returns>
public DataSet GetData()
{
try
{
DataSet ds = new DataSet();
DataSet ds_Return = new DataSet();
DataSet dsRequest = new DataSet();
ArrayList listAMSendList = new ArrayList();
Dbconn conn = new Dbconn("GL_ERP");
string strXML = "";
string strSQL = "";
strXML = m_request.Tables[0].Rows[0]["XML"].ToString();
//XML是前台传入
ds = Common.Common.GetDSByExcelXML(strXML);
if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "GET")
//读取ROWS到/ROWS 中的参数
{
string strWHERE = "ISNULL(BD0_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1'";
if (ds.Tables["LIST"].Columns.Contains("Condition"))
{
if (ds.Tables["LIST"].Rows[0]["Condition"].ToString().Trim() != "")
//读取LIST之间参数
{
strWHERE += " AND (" + ds.Tables["LIST"].Rows[0]["Condition"].ToString().Trim() + ")";
}
}
strSQL = @" SELECT
[BD0_CONO]
,[BD0_DIVI]
,[BD0_BrandId]
,[BD0_BrandCode]
,[BD0_SU0_SupplierId]
,[BD0_BrandName]
,[BD0_CountryId]
,[BD0_StyleId]
,[BD0_CusGrpId]
,[BD0_Status]
,[BD0_RgUser]
,[BD0_RgDt]
,[BD0_LmUser]
,[BD0_LmDt]
,[BD0_UptNo]
,A.[SU0_SupplierCode]
,B.[DC0_DictName1] AS CountryDictName
,C.[DC0_DictName1] AS StyleDictName
,D.[DC0_DictName1] AS CusGrpDictName
FROM B01_MDM.BD0_Brand WITH ( NOLOCK )
LEFT JOIN B01_MDM.SU0_Supplier A ON BD0_SU0_SupplierId=A.SU0_SupplierId
LEFT JOIN M01_CONF.DC0_Dictionary B ON BD0_CountryId=B.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary C ON BD0_StyleId=C.DC0_DictId
LEFT JOIN M01_CONF.DC0_Dictionary D ON BD0_CusGrpId=D.DC0_DictId
WHERE " + strWHERE
+ @"ORDER BY [BD0_LmDt] DESC ";
ds_Return = conn.GetDataSet(strSQL);
}
else
{
ArrayList tableList = new ArrayList();
tableList.Add("B01_MDM.BD0_Brand");
conn.BeginTransaction();
//开启事物
conn.TableLock(tableList);
//锁表
try
{
if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "DELETE")
{
for (int i = 0; i < ds.Tables["LIST"].Rows.Count; i++)
{
strSQL = @"UPDATE B01_MDM.BD0_Brand SET BD0_Status=1
WHERE BD0_BrandId='" + ds.Tables["LIST"].Rows[i]["BD0_BrandId"].ToString() + @"'
AND ISNULL(BD0_Status,'')<>'E3CA9B7A-8E17-4E52-B667-39689445D4A1' ";
conn.ExcuteQuerryByTran(strSQL);
}
}
else if (ds.Tables["OPTYPE"].Rows[0][0].ToString().ToUpper() == "EDIT")
{
for (int i = 0; i < ds.Tables["LIST"].Rows.Count; i++)
{
DataTable dt = ds.Tables["LIST"].Clone();
dt.ImportRow(ds.Tables["LIST"].Rows[i]);
strSQL = @"SELECT * FROM B01_MDM.BD0_Brand WHERE [email protected]
AND ISNULL(BD0_Status,'') <>'E3CA9B7A-8E17-4E52-B667-39689445D4A1' ";
DataTable dt_SR = conn.GetDataTable(strSQL, new string[1] { ds.Tables["LIST"].Rows[i]["BD0_BrandId"].ToString() });
//update
if (dt_SR.Rows.Count > 0)
{
//.........这里部分代码省略.........
示例15: setPhotToSave
/// <summary>
/// 上传图片并保存到数据库中
/// </summary>
/// <returns></returns>
private DataSet setPhotToSave(Dbconn conn, String strType)
{
DataSet ds_Return = new DataSet();
String remoteHost = ConfigurationSettings.AppSettings["remoteHostHR"].ToString();
String remotePath = "";
String remoteUser = ConfigurationSettings.AppSettings["remoteUserHR"].ToString();
String remotePass = ConfigurationSettings.AppSettings["remotePassHR"].ToString() + "i";
String remoteDir = ConfigurationSettings.AppSettings["remotePathHR"].ToString();
int remotePort = 21;
int i = 0;
//获取条件值
string strXML = m_request.Tables[0].Rows[0]["XML"].ToString();
DataSet dsXML = Common.Common.GetDSByExcelXML(strXML);
string strPhotoPath = dsXML.Tables["LIST"].Rows[0]["photoPath"].ToString();
string strPhotoID = dsXML.Tables["LIST"].Rows[0]["photoID"].ToString();
string strActor = dsXML.Tables["LIST"].Rows[0]["actor"].ToString();
string strActorID = dsXML.Tables["LIST"].Rows[0]["actorid"].ToString();
string strUpType = dsXML.Tables["LIST"].Rows[0]["upType"].ToString();
if (strPhotoPath == null || strPhotoPath=="")
{
dt_EditResult.Rows.Add(new object[] { false, "文件不能为空！" });
ds_Return.Tables.Add(dt_EditResult);
return ds_Return;
}
//获取 UNIQUE_KEY
if (strUpType == "new")
{
strPhotoID = getUniqueKey(conn);
if (strPhotoID == "")
{
dt_EditResult.Rows.Add(new object[] { false, "获取 UNIQUE_KEY失败！" });
ds_Return.Tables.Add(dt_EditResult);
return ds_Return;
}
}
//获取原始文件名
string filename = strPhotoPath.Substring(strPhotoPath.LastIndexOf("\\") + 1
, strPhotoPath.Length - strPhotoPath.LastIndexOf("\\") - 1);
filename = filename.Substring(0, filename.LastIndexOf("-"))
+ filename.Substring(filename.LastIndexOf("."), filename.Length - filename.LastIndexOf("."));
//图片另存为.jpg
string newfile = strPhotoPath.Substring(0, strPhotoPath.LastIndexOf("\\")+1) + strPhotoID + ".jpg";
System.IO.File.Copy(strPhotoPath, newfile, true);
//上传文件
var ftcc = new FTP.FTPClient(remoteHost, remotePath, remoteUser, remotePass, remotePort);
ftcc.Connect();
ftcc.ChDir(remoteDir); // 切换到ftp对应目录下的文件夹路径
ftcc.Put(newfile);
//删除图片另存为.jpg
System.IO.File.Delete(newfile);
//保存到数据库
Byte[] byteSQLData = setPhotoByte(strPhotoPath);
string strSQL2 = @" insert into B02_BILL.HR6_Blob (hr6_id, hr6_belongid, hr6_filename, hr6_content, hr6_type)
values( @Param0, @Param1, @Param2, @Param3, @Param4 ) ";
i = conn.ExcuteQuerryByTran(strSQL2, new object[] { strPhotoID, "0", filename, byteSQLData, "0" });
if (i==1)
{
dt_EditResult.Rows.Add(new object[] { true, strPhotoID });
}
else
{
dt_EditResult.Rows.Add(new object[] { false, "上传失败！" });
}
ds_Return.Tables.Add(dt_EditResult);
return ds_Return;
}