public static void SaveAttachments(List <Attachment> attachments) { ScrumQueue.IOLogic.DBAccess dba = ScrumQueue.IOLogic.DBAccess.CreateConnection(); List <Attachment> itemsout = new List <Attachment>(attachments.Count); foreach (Attachment a in attachments) { // insert the item if (a.ItemID != -1) { System.Data.SqlClient.SqlDataReader reader = dba.execSP("InsertAttachment", new string[] { "@ItemID", "@Name", "@CreateDate", "@FileName", "@FileType", "@fileSize", "@FileOriginalLocation", "@data" }, new System.Data.SqlDbType[] { System.Data.SqlDbType.Int, System.Data.SqlDbType.VarChar, System.Data.SqlDbType.DateTime, System.Data.SqlDbType.VarChar, System.Data.SqlDbType.VarChar, System.Data.SqlDbType.Int, System.Data.SqlDbType.VarChar, System.Data.SqlDbType.VarBinary }, new object[] { a.ItemID.ToString(), a.Name, DateTime.Now.ToString(), a.FileName, a.FileType, a.size.ToString(), a.OriginalLocation.ToString(), a.data }); if (reader.Read()) { int iid = int.Parse(reader["ItemID"].ToString()); a.AttachmentID = iid; itemsout.Add(a); } // History.Insert(a, 1); } dba.CloseConn(); } }
public static Item SaveItem(Item itemIn) { Item itemOut = itemIn; ScrumQueue.IOLogic.DBAccess dba = ScrumQueue.IOLogic.DBAccess.CreateConnection(); if (itemIn.itemID == -1) { // INSERT INTO [dbo].[Items] ([Title],[Note],[CreateDate],[CreatedBy],[EditDate],[EditedBy],[StatusID],[ProjectID]) // VALUES(@Title,@Note,getDate(),@CreatedBy ,getDate(), @EditedBy,@StatusID, @ProjectID) System.Data.SqlClient.SqlDataReader reader = dba.execSP("InsertItem", new string[] { "@Title", "@Note", "@CreatedBy", "@EditedBy", "@StatusID", "@ProjectID" }, new string[] { itemIn.Title, itemIn.Note, itemIn.CreatedByID.ToString(), itemIn.EditedByID.ToString(), itemIn.StatusID.ToString(), itemIn.ProjectID.ToString() }); if (reader.Read()) { int iid = int.Parse(reader["ItemID"].ToString()); itemOut.itemID = iid; } History.Insert(itemOut, 1); } else { // [dbo].[UpdateItem] // @ID int, @Title varchar(200),@Note varchar(200),@EditedBy int,@StatusID int,@ProjectID int System.Data.SqlClient.SqlDataReader reader = dba.execSP("UpdateItem", new string[] { "@ItemID", "@Title", "@Note", "@EditedBy", "@StatusID", "@ProjectID" }, new string[] { itemIn.ItemID.ToString(), itemIn.Title, itemIn.Note, itemIn.EditedByID.ToString(), itemIn.StatusID.ToString(), itemIn.ProjectID.ToString() }); reader.Read(); History.Insert(itemOut, 2); } dba.CloseConn(); return(null); }
/// <summary> /// This will get the list of all attachments based on the attachement id /// </summary> /// <param name="inItemID"></param> /// <returns></returns> public static List <Attachment> GetAttachments(int inItemID) { ScrumQueue.IOLogic.DBAccess dba = ScrumQueue.IOLogic.DBAccess.CreateConnection(); System.Data.SqlClient.SqlDataReader reader = dba.execSP("GetAllAttachmentsBasedOnItemID", new string[] { "@ItemID" }, new int[] { inItemID }); List <Attachment> listOfItems = new List <Attachment>(); while (reader.Read()) { // [AttachmentID],[ItemID],[Name],[CreateDate],[FileName],[FileType],[FileSize],[FileOriginalLocation],[FileData] int id = int.Parse(reader["AttachmentID"].ToString()); // ok int iid = int.Parse(reader["ItemID"].ToString()); string name = reader["Name"].ToString(); // ok DateTime createdOn = DateTime.Parse(reader["CreateDate"].ToString()); //ok string fileName = reader["FileName"].ToString(); string fileType = reader["FileType"].ToString(); int fileSize = int.Parse(reader["FileSize"].ToString());//ok string fileOriginalLocation = reader["FileOriginalLocation"].ToString(); byte[] buffer = (byte[])reader["FileData"]; Attachment a = new Attachment(id, iid, name, fileName, fileType, fileSize, fileOriginalLocation, buffer); listOfItems.Add(a); } dba.CloseConn(); return(listOfItems); }
public static List <Item> GetAllItemsBasedOnStatus(int statusIDin, int projectID) { ScrumQueue.IOLogic.DBAccess dba = ScrumQueue.IOLogic.DBAccess.CreateConnection(); System.Data.SqlClient.SqlDataReader reader = dba.execSP("GetAllItemsBasedOnStatusType", new string[] { "@StatusID", "@ProjectID" }, new int[] { statusIDin, projectID }); List <Item> listOfItems = new List <Item>(); while (reader.Read()) { // ItemID, Title, Note, CreateDate, I.CreatedBy, U1.FirstName+' '+U1.LastName 'CreatedByName', EditDate, I.EditedBy, U2.FirstName+' '+U2.LastName 'EditedByName', I.StatusID, S.Name int id = int.Parse(reader["ItemID"].ToString()); // ok string title = reader["Title"].ToString(); // ok string note = reader["Note"].ToString(); // ok DateTime createdOn = DateTime.Parse(reader["CreateDate"].ToString()); //ok DateTime editedOn = DateTime.Parse(reader["EditDate"].ToString()); //ok int createdBy = int.Parse(reader["CreatedBy"].ToString()); //ok string createdByName = reader["CreatedByName"].ToString(); //ok int editedBy = int.Parse(reader["EditedBy"].ToString()); string editedByName = reader["EditedByName"].ToString(); int statusID = int.Parse(reader["StatusID"].ToString()); string statusName = reader["Name"].ToString(); listOfItems.Add(new Item(id, title, note, createdOn, editedOn, editedBy, createdBy, editedByName, createdByName, statusID, statusName)); } dba.CloseConn(); return(listOfItems); }
public static Item GetItemBasedOnIndex(int itemIdIn) { ScrumQueue.IOLogic.DBAccess dba = ScrumQueue.IOLogic.DBAccess.CreateConnection(); System.Data.SqlClient.SqlDataReader reader = dba.execSP("GetItemBasedOnItemID", new string[] { "@ItemID" }, new int[] { itemIdIn }); Item dbItem = null; if (reader.Read()) { // ItemID, Title, Note, CreateDate, I.CreatedBy, U1.FirstName+' '+U1.LastName 'CreatedByName', EditDate, I.EditedBy, U2.FirstName+' '+U2.LastName 'EditedByName', I.StatusID, S.Name int id = int.Parse(reader["ItemID"].ToString()); // ok string title = reader["Title"].ToString(); // ok string note = reader["Note"].ToString(); // ok DateTime createdOn = DateTime.Parse(reader["CreateDate"].ToString()); //ok DateTime editedOn = DateTime.Parse(reader["EditDate"].ToString()); //ok int createdBy = int.Parse(reader["CreatedBy"].ToString()); //ok string createdByName = reader["CreatedByName"].ToString(); //ok int editedBy = int.Parse(reader["EditedBy"].ToString()); string editedByName = reader["EditedByName"].ToString(); int statusID = int.Parse(reader["StatusID"].ToString()); string statusName = reader["Name"].ToString(); dbItem = new Item(id, title, note, createdOn, editedOn, editedBy, createdBy, editedByName, createdByName, statusID, statusName); } dba.CloseConn(); return(dbItem); }
public static List <History> GetHistoryOfItem(Item item) { ScrumQueue.IOLogic.DBAccess dba = ScrumQueue.IOLogic.DBAccess.CreateConnection(); System.Data.SqlClient.SqlDataReader reader = dba.execSP("GetHistoryBasedOnItemID", new string[] { "@ItemID" }, new string[] { item.ItemID.ToString() }); List <History> l = new List <History>(20); while (reader.Read()) { //HistoryID, ItemID, Title, Note, CreateDate, U1.username 'CreatedBy', EditDate, U2.username 'EditedBy', ST.Name 'Status', HistoryType int hid = int.Parse(reader["HistoryID"].ToString()); string title = reader["Title"].ToString(); string note = reader["Note"].ToString(); DateTime editDate = DateTime.Parse(reader["EditDate"].ToString()); string editedUser = reader["EditedBy"].ToString(); string status = reader["Status"].ToString(); string historyType = reader["HistoryType"].ToString(); History h = new History(hid, item.ItemID, title, note, editDate, editedUser, status, historyType); l.Add(h); } dba.CloseConn(); return(l); }
public static int SaveStatusType(StatusType st) { int id = st.StatusID; ScrumQueue.IOLogic.DBAccess dba = ScrumQueue.IOLogic.DBAccess.CreateConnection(); System.Data.SqlClient.SqlDataReader reader = null; if (st.StatusID == -1) { reader = dba.execSP("InsertStatusType", new string[] { "@Name", "@Description" }, new System.Data.SqlDbType[] { System.Data.SqlDbType.VarChar, System.Data.SqlDbType.VarChar }, new object[] { st.StatusName, st.StatusDescription }); if (reader.Read()) { id = int.Parse(reader["StatusID"].ToString()); } } else { reader = dba.execSP("UpdateStatusType", new string[] { "@ID", "@Name", "@Description" }, new System.Data.SqlDbType[] { System.Data.SqlDbType.Int, System.Data.SqlDbType.VarChar, System.Data.SqlDbType.VarChar }, new object[] { st.StatusID, st.StatusName, st.StatusDescription }); if (reader.Read()) { id = int.Parse(reader["StatusID"].ToString()); } } dba.CloseConn(); return(id); }
public Login(string username, string password) { if (username == null || password == null) { return; } if (username.Trim().Length == 0) { return; } ScrumQueue.IOLogic.DBAccess dba = ScrumQueue.IOLogic.DBAccess.CreateConnection(); System.Data.SqlClient.SqlDataReader reader = dba.execSP("LoginUser", new string[] { "@Username", "@Password" }, new string[] { username, password }); this.loginStatus = false; // THIS NEEDS TO BE COMING FROM THE DATABASE BUT WILL NOT RIGHT NOW UNTIL FIELDS IN DB ARE SET this.userStatus = true; if (reader.Read()) { //UserID, Username, FirstName, LastName, DateCreated this.User.UserID = int.Parse(reader["UserID"].ToString()); this.User.Username = reader["Username"].ToString(); this.User.FirstName = reader["FirstName"].ToString(); this.User.LastName = reader["LastName"].ToString(); this.User.IsAdmin = bool.Parse(reader["IsAdmin"].ToString()); this.loginStatus = true; } reader.Close(); dba.CloseConn(); }
public static void Insert(Item item, int HistoryTypeID) { ScrumQueue.IOLogic.DBAccess dba = ScrumQueue.IOLogic.DBAccess.CreateConnection(); System.Data.SqlClient.SqlDataReader reader = dba.execSP("InsertHistory", new string[] { "@ItemID", "@HistoryTypeID" }, new string[] { item.ItemID.ToString(), HistoryTypeID.ToString() }); reader.Read(); dba.CloseConn(); }
public static List <Project> GetAllProjects() { ScrumQueue.IOLogic.DBAccess dba = ScrumQueue.IOLogic.DBAccess.CreateConnection(); System.Data.SqlClient.SqlDataReader reader = dba.execSP("GetAllProjects"); List <Project> projectList = new List <Project>(); while (reader.Read()) { //[ProjectID],[ProjectName],[ProjectDescription] int id = int.Parse(reader["ProjectID"].ToString()); string projectName = reader["ProjectName"].ToString(); string projectDesc = reader["ProjectDescription"].ToString(); projectList.Add(new Project(id, projectName, projectDesc)); } dba.CloseConn(); return(projectList); }
public static int SaveProject(Project p) { ScrumQueue.IOLogic.DBAccess dba = ScrumQueue.IOLogic.DBAccess.CreateConnection(); System.Data.SqlClient.SqlDataReader reader = dba.execSP("SaveProject", new string[] { "@ProjectID", "@ProjectName", "@ProjectDescription" }, new System.Data.SqlDbType[] { System.Data.SqlDbType.Int, System.Data.SqlDbType.VarChar, System.Data.SqlDbType.VarChar }, new object[] { p.ProjectID.ToString(), p.ProjectName, p.ProjectDescription }); int id = -1; if (reader.Read()) { id = int.Parse(reader["projectID"].ToString()); } dba.CloseConn(); return(id); }
public static void DeleteAttachments(Attachment attachment) { ScrumQueue.IOLogic.DBAccess dba = ScrumQueue.IOLogic.DBAccess.CreateConnection(); // insert the item if (attachment.ItemID != -1) { System.Data.SqlClient.SqlDataReader reader = dba.execSP("DeleteAttachment", new string[] { "@AttachmentID" }, new System.Data.SqlDbType[] { System.Data.SqlDbType.Int }, new object[] { attachment.AttachmentID }); reader.Read(); // History.Insert(a, 1); } dba.CloseConn(); }
public static List <StatusType> GetAllStatusTypes() { ScrumQueue.IOLogic.DBAccess dba = ScrumQueue.IOLogic.DBAccess.CreateConnection(); System.Data.SqlClient.SqlDataReader reader = dba.execSP("GetAllStatusTypes"); List <StatusType> listOfStatus = new List <StatusType>(4);// Default to save perfomance while (reader.Read()) { int id = int.Parse(reader["StatusID"].ToString()); string nm = reader["Name"].ToString(); string desc = reader["Description"].ToString(); listOfStatus.Add(new StatusType(id, nm, desc)); } dba.CloseConn(); return(listOfStatus); }