/// <summary> /// Delete 1 event /// </summary> /// <param name="AppName"></param> /// <param name="item"></param> /// <param name="eventname"></param> /// <returns></returns> public bool DeleteEvent(string AppName, string item, string eventname) { List <string> lstSql = new List <string>(); string sqlString = string.Format(@"select ID from [" + Config._DBNameFrontEnd + "].dbo.SGC_DocumentIssue where AppName = N'{0}' AND Item = N'{1}' AND Event = N'{2}'", AppName, item, eventname); DataTable kq = ConnectDb.ExcuteQuery(sqlString); foreach (DataRow row in kq.Rows) { string sqlDeleteDocument = string.Format(@"delete from [" + Config._DBNameFrontEnd + "].dbo.SGC_DocumentDetail where ID = '{0}'", row["ID"]); string sqlDeleteAtt = string.Format(@"delete from [" + Config._DBNameFrontEnd + "].dbo.SGC_Attachment where DocumentID = '{0}'", row["ID"]); string sqlDeleteDocIssue = string.Format(@"delete from [" + Config._DBNameFrontEnd + "].dbo.SGC_DocumentIssue where ID = '{0}'", row["ID"]); lstSql.Add(sqlDeleteDocument); lstSql.Add(sqlDeleteAtt); lstSql.Add(sqlDeleteDocIssue); } try { ConnectDb.ExcuteNonQuery_WithTransaction(lstSql); } catch (Exception ex) { return(false); } return(true); }
/// <summary> /// Rename Event /// </summary> /// <param name="oldEvent"></param> /// <param name="newEvent"></param> /// <param name="appName"></param> /// <param name="Item"></param> public void UpdateEventName(string oldEvent, string newEvent, string appName, string Item) { // update 1 bảng string sql_SGCDoc = string.Format(@"Update [" + Config._DBNameFrontEnd + "].dbo.SGC_DocumentIssue set Event = N'{0}' where AppName = N'{2}' AND Item = N'{3}' AND Event = N'{1}' ", newEvent, oldEvent, appName, Item); List <string> lst = new List <string>(); lst.Add(sql_SGCDoc); ConnectDb.ExcuteNonQuery_WithTransaction(lst); }
/// <summary> /// Update item /// </summary> /// <param name="oldItem"></param> /// <param name="newItem"></param> /// <param name="appName"></param> public void UpdateItemName(string oldItem, string newItem, string appName) { // update 2 bảng string sql_AppItem = string.Format(@"Update [" + Config._DBNameFrontEnd + "].dbo.SGC_ApplicationItem set Item = N'{0}' where ApplicationName = N'{2}' AND Item = N'{1}'", newItem, oldItem, appName); string sql_SGCDoc = string.Format(@"Update [" + Config._DBNameFrontEnd + "].dbo.SGC_DocumentIssue set Item = N'{0}' where AppName = N'{2}' AND Item = N'{1}'", newItem, oldItem, appName); List <string> lst = new List <string>(); lst.Add(sql_AppItem); lst.Add(sql_SGCDoc); ConnectDb.ExcuteNonQuery_WithTransaction(lst); }
bool DeleteinDB(string AttName, string DocumentID) { string sql = string.Format(@"select Title from [" + Config._DBNameFrontEnd + "].dbo.SGC_DocumentDetail where ID = '{0}'", DocumentID); object a = ConnectDb.ExcuteScalar(sql); if (a == null || a is DBNull) { return(false); } List <string> lstString = new List <string>(); List <string> lstSql = new List <string>(); string[] lst_db = a.ToString().Split(';'); foreach (string s in lst_db) { if (s.Length > 0) { int index = s.IndexOf('-') + 1; int length = s.Length - index; if (s.Substring(index, length) == AttName) { string id = s.Substring(0, index - 1); string sqlDelete = string.Format(@"delete from [" + Config._DBNameFrontEnd + "].dbo.SGC_Attachment where ID = '{0}'", id); lstSql.Add(sqlDelete); } else { lstString.Add(s); } } } string newTitle = MakeTitle(lstString); string sqlUpdate = string.Format(@"update [" + Config._DBNameFrontEnd + "].dbo.SGC_DocumentDetail set Title = N'{0}' where ID='{1}'", newTitle, DocumentID); lstSql.Add(sqlUpdate); try { ConnectDb.ExcuteNonQuery_WithTransaction(lstSql); } catch (Exception ex) { return(false); } return(true); }
/// <summary> /// Add new 1 event /// </summary> /// <param name="appName"></param> /// <param name="item"></param> /// <param name="Event"></param> /// <param name="idNhanVien"></param> /// <param name="issueContent"></param> /// <param name="Version"></param> /// <param name="reference"></param> /// <param name="fileNames"></param> /// <param name="names"></param> /// <returns></returns> public string AddNewEvent(string appName, string item, string Event, string idNhanVien, string issueContent, string Version, string reference, List <string> fileNames, List <string> names) { string ID = Guid.NewGuid().ToString(); string sqlString_DocumentIssue = string.Format(@"INSERT INTO [" + Config._DBNameFrontEnd + @"].dbo.SGC_DocumentIssue ( ID, AppName, Item, [Event], Creator, DateCreated, UpdateBy, DateModify ) VALUES ( '{0}', N'{1}', N'{2}', N'{3}', N'{4}', @Date, N'{5}', {6} )", ID, appName, item, Event, idNhanVien, "NULL", "NULL"); SqlParameter param = new SqlParameter("@Date", SqlDbType.DateTime, 8, ParameterDirection.Input, true, 0, 0, "DateCreated", DataRowVersion.Current, DateTime.Now); string title = ""; int num = GetMaxNumber(); foreach (string name in names) { title += (num + 1) + "-" + name + ";"; num++; } issueContent = issueContent.Replace("'", "''"); string sqlString_DocumentDetail = string.Format(@"INSERT INTO [" + Config._DBNameFrontEnd + @"].dbo.SGC_DocumentDetail ( ID, [Version], Reference, Title, IssueContent ) VALUES ( '{0}', N'{1}', N'{2}', N'{3}', N'{4}' )", ID, Version, reference, title, issueContent); List <string> lstSql = new List <string>(); lstSql.Add(sqlString_DocumentIssue); lstSql.Add(sqlString_DocumentDetail); int nextID = GetMaxNumber(); List <SqlParameter> sqlParams = new List <SqlParameter>(); sqlParams.Add(param); foreach (string s in fileNames) { FileInfo fi = new FileInfo(s); byte[] b = ConvertFileToByte(fi); string attName = "@Att" + (nextID + 1).ToString(); string sqlAtt = string.Format(@"INSERT INTO [" + Config._DBNameFrontEnd + @"].dbo.SGC_Attachment ( ID, Attachment, DocumentID ) VALUES ( '{0}', " + attName + @", '{1}' )", (nextID + 1), ID); SqlParameter param_Byte = new SqlParameter(attName, SqlDbType.Binary, 232000, ParameterDirection.Input, true, 0, 0, "Attachment", DataRowVersion.Current, b); sqlParams.Add(param_Byte); nextID++; lstSql.Add(sqlAtt); } if (ConnectDb.ExcuteNonQuery_WithTransaction(lstSql, sqlParams) == 0) { return(null); } return(ID); }
/// <summary> /// Thêm attachment /// </summary> /// <param name="s"></param> /// <param name="DocumentID"></param> /// <param name="detail"></param> /// <param name="fileName"></param> /// <param name="version"></param> /// <param name="reference"></param> /// <param name="title_Att"></param> /// <returns></returns> public bool AddNewAtt(string s, string DocumentID, string detail, string fileName, string version, string reference, string title_Att) { string sqlString_Title = string.Format(@"select Title from [" + Config._DBNameFrontEnd + "].dbo.SGC_DocumentDetail where ID= '{0}'", DocumentID); object tt = ConnectDb.ExcuteScalar(sqlString_Title); string title = ""; if (tt is DBNull || tt == null) { title_Att = ""; } else { title_Att = tt.ToString(); } int nextID = GetMaxNumber(); FileInfo fi = new FileInfo(s); byte[] b = ConvertFileToByte(fi); string attName = "@Att" + (nextID + 1).ToString(); string sqlAtt = string.Format(@"INSERT INTO [" + Config._DBNameFrontEnd + @"].dbo.SGC_Attachment ( ID, Attachment, DocumentID ) VALUES ( '{0}', " + attName + @", '{1}' )", (nextID + 1), DocumentID); SqlParameter param_Byte = new SqlParameter(attName, SqlDbType.Binary, b.Length, ParameterDirection.Input, true, 0, 0, "Attachment", DataRowVersion.Current, b); int num = GetMaxNumber(); title += (num + 1) + "-" + fileName + ";"; detail = detail.Replace("'", "''"); string sqlString = string.Format(@"UPDATE [" + Config._DBNameFrontEnd + @"].dbo.SGC_DocumentDetail SET IssueContent = N'{0}', [Version] = N'{2}', Reference = N'{3}', Title = N'{4}' WHERE ID = '{1}'", detail, DocumentID, version, reference, title_Att + title); List <string> sqlStrings = new List <string>(); sqlStrings.Add(sqlAtt); sqlStrings.Add(sqlString); List <SqlParameter> lstParam = new List <SqlParameter>(); lstParam.Add(param_Byte); try { if (ConnectDb.ExcuteNonQuery_WithTransaction(sqlStrings, lstParam) > 0) { return(true); } } catch (Exception ex) { return(false); } return(true); }