public string[] createSerialDataBase(CreateStaffUpgradeSeries StaffUpgrade) { string[] returnValue = new string[2]; returnValue[0] = "0"; returnValue[1] = "0"; DataBase Base = new DataBase(); using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString())) { try { List<string> CreateFileName = this.getStaffDataName(HttpContext.Current.User.Identity.Name); List<string> UserFileName = this.getStaffDataName(StaffUpgrade.author); Sqlconn.Open(); string sql = "INSERT INTO StaffUpgradeBehave (Unit, StaffID, PublicationDate, Amount, PublicationName, Period, ArticleTitle, Category, CreateFileBy, CreateFileDate, UpFileBy, UpFileDate ) " + "VALUES (@Unit,@StaffID,@PublicationDate,@Amount,@PublicationName,@Period,@ArticleTitle,@Category, @CreateFileBy, getDate(), @UpFileBy, getDate())"; SqlCommand cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@Unit", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(UserFileName[2]); cmd.Parameters.Add("@StaffID", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(StaffUpgrade.author); cmd.Parameters.Add("@PublicationDate", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(StaffUpgrade.articleDate); cmd.Parameters.Add("@Amount", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(StaffUpgrade.serialNumber); cmd.Parameters.Add("@PublicationName", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffUpgrade.seriesTitle); cmd.Parameters.Add("@Period", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(StaffUpgrade.volume); cmd.Parameters.Add("@ArticleTitle", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(StaffUpgrade.articleTitle); cmd.Parameters.Add("@Category", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(StaffUpgrade.articleType); cmd.Parameters.Add("@CreateFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]); cmd.Parameters.Add("@UpFileBy", SqlDbType.Int).Value = Chk.CheckStringtoIntFunction(CreateFileName[0]); returnValue[0] = cmd.ExecuteNonQuery().ToString(); Sqlconn.Close(); } catch (Exception e) { returnValue[0] = "-1"; returnValue[1] = e.Message.ToString(); } } return returnValue; }
public string[] createSerialDataBase(CreateStaffUpgradeSeries StaffUpgradeData) { StaffDataBase sDB = new StaffDataBase(); sDB.personnelFunction(); if (int.Parse(sDB._StaffhaveRoles[2]) == 1) { return sDB.createSerialDataBase(StaffUpgradeData); } else { return new string[2] { _noRole, _errorMsg }; } }
public List<CreateStaffUpgradeSeries> SearchStaffBehaveDataBase(int indexpage, SearchStaffBehave SearchStaffUpgrade) { List<CreateStaffUpgradeSeries> returnValue = new List<CreateStaffUpgradeSeries>(); DataBase Base = new DataBase(); using (SqlConnection Sqlconn = new SqlConnection(Base.GetConnString())) { try { string[] ConditionReturn = this.SearchStaffBehaveConditionReturn(SearchStaffUpgrade); Sqlconn.Open(); string sql = "SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY StaffUpgradeBehave.BehaveID DESC) " + "AS RowNum,StaffUpgradeBehave.*,StaffDatabase.StaffName FROM StaffUpgradeBehave INNER JOIN StaffDatabase ON StaffUpgradeBehave.StaffID=StaffDatabase.StaffID " + ConditionReturn[0] + "WHERE StaffUpgradeBehave.isDeleted=0 " + ConditionReturn[1] + " ) " + "AS NewTable " + "WHERE RowNum >= (@indexpage-" + PageMinNumFunction() + ") AND RowNum <= (@indexpage)"; SqlCommand cmd = new SqlCommand(sql, Sqlconn); cmd.Parameters.Add("@indexpage", SqlDbType.Int).Value = indexpage; cmd.Parameters.Add("@txtstaffName", SqlDbType.NVarChar).Value = Chk.CheckStringFunction(SearchStaffUpgrade.txtstaffName1) + "%"; cmd.Parameters.Add("@sDateStart", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStaffUpgrade.txtDateStart1); cmd.Parameters.Add("@sDateEnd", SqlDbType.Date).Value = Chk.CheckStringtoDateFunction(SearchStaffUpgrade.txtDateEnd1); cmd.Parameters.Add("@txtType", SqlDbType.TinyInt).Value = Chk.CheckStringtoIntFunction(SearchStaffUpgrade.txtType); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { CreateStaffUpgradeSeries addValue = new CreateStaffUpgradeSeries(); addValue.ID = dr["BehaveID"].ToString(); addValue.articleDate = DateTime.Parse(dr["PublicationDate"].ToString()).ToString("yyyy-MM-dd"); addValue.author = dr["StaffName"].ToString(); addValue.serialNumber = dr["Amount"].ToString(); addValue.seriesTitle = dr["PublicationName"].ToString(); addValue.volume = dr["Period"].ToString(); addValue.articleTitle = dr["ArticleTitle"].ToString(); addValue.articleType = dr["Category"].ToString(); returnValue.Add(addValue); } dr.Close(); Sqlconn.Close(); } catch (Exception e) { CreateStaffUpgradeSeries addValue = new CreateStaffUpgradeSeries(); addValue.checkNo = "-1"; addValue.errorMsg = e.Message; returnValue.Add(addValue); } } return returnValue; }