/// <summary> /// Write vols back to DB - update if there already /// </summary> /// <param name="ins"></param> /// <param name="PriceDate"></param> internal void SaveTickerData_Volatility(List <BloombergDataInstrument> instruments, int Reference) { string sql = string.Format("OptionValueManagerVolatilityInsert {0}", Reference); string ret = db.ExecSql(sql); if (ret != "") { throw new Exception(string.Format("Error in {0}.\r\n{1}", "SaveTickerData_Volatility", ret)); } }
internal string RunCommand(string Server, string cmd) { string ret = ""; string server = Server; if (cmd.Length > 8 && cmd.Substring(0, 9).ToLower() == ":connect ") { // This is targeted against a specific server server = cmd.Substring(9, cmd.IndexOfAny(new Char[] { '\r', '\n' }) - 9); cmd = cmd.Substring(9 + server.Length); } SQLServer db = new SQLServer(server, "master", "", ""); try { db.Timeout = 300; // Some processes are long // Split commands that are seperated by a GO string[] lines = cmd.Split(new Char[] { '\r', '\n' }, StringSplitOptions.RemoveEmptyEntries); string sql = ""; foreach (string line in lines) { if (line.Trim().ToUpper() == "GO") { ret = db.ExecSql(sql); sql = ""; if (ret != "") { break; } } else { sql += line + "\r\n"; } } if (sql != "") { ret = db.ExecSql(sql); } } catch (Exception ex) { ret = "Error: " + ex.Message; } finally { db.Close(); } return(ret); }
internal static void RemoveWorkItemsSince(DateTime workSince) { GetDbConn(); string sql = "Delete WorkItem Where WorkDate >= '{0}'".Args(workSince.ToString("yyyyMMdd")); SQLServer.ExecSql(conn, sql); }
public static int EditAccountData(string PostData) { JObject jo = (JObject)JsonConvert.DeserializeObject(PostData); int intResult = 99; bool bol = false; string Msg = ""; string str = "select id from t_AccountStatus where year=" + jo["Year"] + " and month=" + jo["Month"]; DataTable dt = SQLServer.GetDataTable(str, ref Msg); if (dt != null && dt.Rows.Count > 0) { string id = dt.Rows[0][0].ToString(); str = "update t_AccountStatus set status=" + jo["Switch"] + " where id=" + id + ""; } else if (dt != null && dt.Rows.Count == 0) { str = "insert into t_AccountStatus values(" + jo["Year"] + "," + jo["Month"] + "," + jo["Switch"] + ")"; } bol = SQLServer.ExecSql(str, ref Msg); if (bol == true) { intResult = 100; } return(intResult); }
public static bool SaveCPXLDataSup(JArray ja) { string sql = ""; sql = "delete from t_chanpinxilie where xilieID=(select FItemID from t_Item where FName='" + ja[0]["cpxl"] + "')"; bol = SQLServer.ExecSql(sql, ref Msg); if (bol == false) { return(bol); } for (int i = 0; i < ja.Count; i++) { sql = @"insert into t_chanpinxilie(xilieID,childmatID,supID) select it.FItemID,ic.FItemID,per.FItemID from (select FItemID from t_item where FName= '" + ja[i]["cpxl"] + "' ) it join (select FItemID from t_icitem where FNumber='" + ja[i]["mat"] + "') ic on 1=1 left join (select FItemID from t_Supplier where FNumber='" + ja[i]["sup"] + "') per on 1=1"; bol = SQLServer.ExecSql(sql, ref Msg); if (bol == false) { return(bol); } } return(true); }
public void ExecSql(string sql, string failMessage, string DSN = "", int timeout = 0) { SqlCommand cmd = new SqlCommand(sql); if (DSN == "") { DSN = Settings.Default.DSN; } SQLServer db = new SQLServer(DSN); Utils.Log(string.Format("Calling DSN: {0} {1}", DSN, sql)); db.ConnectionTimeout = timeout; db.Timeout = timeout; //SQLServer db = new SQLServer(Settings.Default.DSN); try { string ret = db.ExecSql(cmd); if (ret != "") { Utils.Log(failMessage); throw new Exception(failMessage + " " + ret); } } finally { db.Close(); } }
public static bool CPXLEditSup(string id, string sup, ref string msg) { string sql = "update t_chanpinxilie set supID=per.FItemID from(select*from t_Supplier where FNumber ='" + sup + "')per where id=" + id + ""; bol = SQLServer.ExecSql(sql, ref Msg); msg = Msg; return(bol); }
/// <summary> /// call the dm.ExecSQL method and raise an error if an error string is returned /// </summary> /// <param name="sqlCommand"></param> public void ExecSql(SqlCommand sqlCommand) { string err = db.ExecSql(sqlCommand); if (err != "") { throw new System.Exception("Could not execute command: " + err); } }
public static bool ChangePlugRole(string username, string rolename, ref string msg) { sql = "begin tran begin try update t_MyUserAccount set BelongToRole=ur.id from (select *from t_PlugUserRole where RoleName='" + rolename + "')ur where username='******'"; sql += " end try begin catch rollback tran select 1 / 0 return end catch commit tran"; bol = SQLServer.ExecSql(sql, ref Msg); msg = Msg; return(bol); }
/// <summary> /// 删除 /// </summary> /// <param name="cpxl"></param> /// <param name="msg"></param> /// <returns></returns> public static bool CPXLProductionDelete(string cpxl, ref string msg) { string sql = @"declare @delid int,@cpxl nvarchar(50) select @cpxl=FItemID from t_Item where FItemClassID=(select FItemClassID from t_ItemClass where FName='产品系列分类') and FName='" + cpxl + @"' select @delid=FID from t_xiliefenlei fl join t_Item it on fl.Series=it.FItemID where it.FItemID=@cpxl delete from t_xiliefenlei where FID=@delid delete from t_xiliefenleiEntry where FID=@delid"; bol = SQLServer.ExecSql(sql, ref Msg); msg = Msg; return(bol); }
public static bool EditPlugRolePermission(string rolename, JArray rows) { sql = "begin tran begin try declare @id int select @id=id from t_PlugUserRole where RoleName='" + rolename + "' delete from t_PlugUserPermission where RoleID=@id"; for (int i = 0; i < rows.Count; i++) { sql += " insert into t_PlugUserPermission select @id," + rows[i]["id"] + "," + rows[i]["ResourceTypeID"]; } sql += " end try begin catch rollback tran select 1 / 0 return end catch commit tran"; bol = SQLServer.ExecSql(sql, ref Msg); return(bol); }
/// <summary> /// 批量删除产品系列供应商关系 /// </summary> /// <param name="id"></param> /// <param name="msg"></param> /// <returns></returns> public static bool BatchDeleteCPXLSup(string[] id, ref string msg) { string sql = "delete from t_chanpinxilie where id in("; sql += id[0]; for (int i = 1; i < id.Length; i++) { sql += "," + id[i]; } sql += ")"; bol = SQLServer.ExecSql(sql, ref Msg); msg = Msg; return(bol); }
public static bool CPXLCancelReview(string[] id, ref string msg) { string sql = "update t_chanpinxilie set status=0 where id in("; sql += id[0]; for (int i = 1; i < id.Length; i++) { sql += "," + id[i]; } sql += ")"; bol = SQLServer.ExecSql(sql, ref Msg); msg = Msg; return(bol); }
internal static void AddInWorkItemPlaceHolder() { GetDbConn(); string sql = "EXEC UpdateWorkItem {0},'{1}','{2}','{3}',{4},'{5}',".Args(0, "n/a", DateTime.Now.ToString("yyyyMMdd"), "n/a", 0, "LastUpdateTime"); sql += "'{0}','{1}',{2},{3},{4},'{5}'".Args("Last update placeholder", "", 0, 0, 0, DateTime.Now.AddMinutes(-5.0).ToString("yyyyMMdd HH:mm:ss")); SQLServer.ExecSql(conn, sql); }
public static bool SaveCPXLData(bool repeat, string xilie, string mat, string sup) { string sql = ""; if (repeat == true) { sql = @"update t_chanpinxilie set supID=a.supid from t_chanpinxilie cp join(select it.FItemID as itid,ic.FItemID as icid,per.FItemID as supid from t_item it , t_icitem ic,t_Supplier per where it.FName= '" + xilie + "' and ic.FNumber='" + mat + "' and per.FNumber='" + sup + "')a on cp.xilieID=itid and cp.childmatID=a.icid where xilieID=itid and childmatID=icid"; } else { sql = @"insert into t_chanpinxilie(xilieID,childmatID,supID) select it.FItemID,ic.FItemID,per.FItemID from t_item it , t_icitem ic,t_Supplier per where it.FName= '" + xilie + "' and ic.FNumber='" + mat + "' and per.FNumber='" + sup + "'"; } return(SQLServer.ExecSql(sql, ref Msg)); }
internal static void UpdateWorkItem(Model.WorkItem item) { GetDbConn(); string sql = "EXEC UpdateWorkItem {0},'{1}','{2}','{3}',{4},'{5}',".Args(item.ID, item.BusinessOwner, item.WorkDate, item.Employee, item.Hours, item.Issue); sql += "'{0}','{1}',{2},{3},{4},'{5}'".Args(item.IssueSummary.Replace("'", "''"), item.WorkDescription.Replace("'", "''"), item.LondonTrading == "Y" ? 1 : 0, item.StockLoan == "Y" ? 1 : 0, item.CreditTrading == "Y" ? 1 : 0, item.UpdateTime.ToString("yyyyMMdd HH:mm:ss")); SQLServer.ExecSql(conn, sql); }
public static bool AddChanPinXiLieData(string PostData) { string Msg = ""; JArray ja = (JArray)JsonConvert.DeserializeObject(PostData); JObject o = (JObject)ja[0]; bool bol = false; if (ja != null && ja.Count > 0) { for (int i = 0; i < ja.Count; i++) { string str = "insert into t_chanpinxilie values(" + ja[i]["xilie"] + "," + ja[i]["material"] + "," + ja[i]["supplier"] + ")"; bol = SQLServer.ExecSql(str, ref Msg); } } return(bol); }
public static bool ImportLeaveData(string year, string month, DataTable exceltable) { string sql = ""; bool bol = false; sql = "delete from t_ImportLeaveData where year=" + year + "and month= " + month + ""; for (int i = 0; i < exceltable.Rows.Count; i++) { string str1 = exceltable.Rows[i]["工号"].ToString().Trim(); string str2 = exceltable.Rows[i]["请假小时"].ToString().Trim(); sql += @" insert into t_ImportLeaveData(year,month,EmpID,LeaveHours) select " + year + "," + month + ",emp.FItemID," + str2 + " from t_Emp emp where emp.FNumber='" + str1 + "'"; bol = SQLServer.ExecSql(sql, ref Msg); } return(bol); }
public static bool DeletePlugRole(string rolename, ref string msg) { sql = @"select 1 from t_PlugUserRole ur join t_MyUserAccount ua on ur.id=ua.BelongToRole where RoleName='" + rolename + "'"; DataTable dt = SQLServer.GetDataTable(sql, ref Msg); if (dt != null && dt.Rows.Count > 0) { msg = "角色已被用户使用!"; return(false); } sql = "begin tran begin try delete from t_PlugUserRole where RoleName='" + rolename + "'"; sql += " end try begin catch rollback tran select 1 / 0 return end catch commit tran"; bol = SQLServer.ExecSql(sql, ref Msg); msg = Msg; return(bol); }
private static void ExecSql(SqlCommand cmd, string failMessage) { #if DEBUG SQLServer db = new SQLServer(Settings.Default.Test_FtpDSN); #else SQLServer db = new SQLServer(Settings.Default.FtpDSN); #endif try { string ret = db.ExecSql(cmd); if (ret != "") { throw new Exception(failMessage + " " + ret + "\r\n\r\n" + cmd.CommandText); } } finally { db.Close(); } }
/// <summary> /// 保存 /// </summary> /// <param name="cpxl"></param> /// <param name="prd"></param> /// <param name="msg"></param> /// <returns></returns> public static bool CPXLProductionSave(string cpxl, string[] prd, ref string msg) { string sql = @"declare @delid int,@maxid int,@cpxl nvarchar(50) select @cpxl=FItemID from t_Item where FItemClassID=(select FItemClassID from t_ItemClass where FName='产品系列分类') and FName='" + cpxl + @"' select @delid=FID from t_xiliefenlei fl join t_Item it on fl.Series=it.FItemID where it.FItemID=@cpxl delete from t_xiliefenlei where FID=@delid delete from t_xiliefenleiEntry where FID=@delid select @maxid=max(FID) from t_xiliefenlei if @maxid is null set @maxid=0 insert into t_xiliefenlei(FID,Series) select @maxid+1,it.FItemID from t_Item it where it.FItemID=@cpxl insert into t_xiliefenleiEntry(FID,FIndex,Material) select @maxid+1,ROW_NUMBER() OVER(ORDER BY FNumber),ic.FItemID from t_ICItem ic where ic.FItemID in("; sql += prd[0]; for (int i = 1; i < prd.Length; i++) { sql += "," + prd[i]; } sql += ")"; bol = SQLServer.ExecSql(sql, ref Msg); msg = Msg; return(bol); }
public static bool AddPlugRole(string rolename, string remark, string creator, JArray rows, ref string msg) { sql = @"select 1 from t_PlugUserRole where RoleName='" + rolename + "'"; DataTable dt = SQLServer.GetDataTable(sql, ref Msg); if (dt != null && dt.Rows.Count > 0) { msg = "用户名已存在"; return(false); } sql = "begin tran begin try declare @id int insert into t_PlugUserRole select '" + rolename + "',GETDATE(),u.id,'" + remark + "' from t_MyUserAccount u where u.username='******' select @id=max(id) from t_PlugUserRole"; for (int i = 0; i < rows.Count; i++) { sql += " insert into t_PlugUserPermission select @id," + rows[i]["id"] + "," + rows[i]["ResourceTypeID"]; } sql += " end try begin catch rollback tran select 1 / 0 return end catch commit tran"; bol = SQLServer.ExecSql(sql, ref Msg); msg = Msg; return(bol); }
internal string AlterObject(DbObject Ob, string ObjectText) { string ret = ""; // In order to avoid changing the CREATE keyword to ALTER we grab the permissions, drop the object and recreate it with the // same text that was produced by sp_helptext string permissionSql = GetPermissions(Ob); string dropSql = ""; switch (Ob.ObjectType) { case "P": dropSql = "DROP PROC [" + Ob.ObjectName + "]"; break; case "V": dropSql = "DROP VIEW [" + Ob.ObjectName + "]"; break; case "FN": case "IF": dropSql = "DROP FUNCTION [" + Ob.ObjectName + "]"; break; default: throw new Exception(string.Format("Unhandled object type of {0} in AlterObject method.", Ob.ObjectType)); } // Maybe a temporary entry, waiting for a decision on this bool ok = true; if (ObjectText.ToLower().Contains("openquery") && ObjectText.ToLower().Contains("remotedb.dbo.execkondorlive")) { ok = false; } if (ok) { SQLServer db = new SQLServer(Ob.Server, Ob.Database, "", ""); try { string err = db.ExecSql("BEGIN TRAN"); if (err == "") { err = db.ExecSql(dropSql); } if (err == "") { err = db.ExecSql(ObjectText); } if (err == "") { err = db.ExecSql("COMMIT TRAN"); } else { err += "\r\n" + db.ExecSql("IF @@trancount > 0 RollBack Tran"); } if (err != "") { ret += err; } else { if (permissionSql != "") { ret += db.ExecSql(permissionSql); } } } finally { db.Close(); } } return(ret); }