예제 #1
4
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                if (Request["paperid"] != null)
                {
                    string pid = Request["paperid"].ToString();
                    string viewrs = Request["viewrs"].ToString();
                    string viewdata = string.Empty;

                    if (Request["viewdata"] != null)
                    {
                        viewdata = Request["viewdata"].ToString();
                    }

                    if (!string.IsNullOrEmpty(pid))
                    {
                        Database objDB = new SqlDatabase(M_CONSTR);
                        if (objDB != null)
                        {
                            DataSet ds = objDB.ExecuteDataSet(CommandType.Text,
                                    @"SELECT * FROM Table_PAPER WHERE PAPERID=" + pid);
                            if (ds != null && ds.Tables[0].Rows.Count.Equals(1))
                            {
                                string pType = ds.Tables[0].Rows[0]["PaperClassID"].ToString();

                                if (pType.Equals("1"))//完整版问卷
                                {
                                    if (!string.IsNullOrEmpty(viewrs))
                                    {
                                        if (!string.IsNullOrEmpty(viewdata))
                                        {
                                            Response.Redirect("~/report/dbbgdata.aspx?paperid=" + pid + "&viewdata=true");
                                        }
                                        Response.Redirect("~/report/dbbg.aspx?paperid=" + pid);
                                    }
                                    Response.Redirect("wz.aspx?paperid=" + pid);
                                }
                                else if (pType.Equals("2"))//讲师版问卷
                                {
                                    if (!string.IsNullOrEmpty(viewrs))
                                    {
                                        if (!string.IsNullOrEmpty(viewdata))
                                        {
                                            Response.Redirect("~/report/jsbgdata.aspx?paperid=" + pid + "&viewdata=true");
                                        }
                                        Response.Redirect("~/report/NotAllow.aspx");
                                    }
                                    Response.Redirect("js.aspx?paperid=" + pid);
                                }
                            }
                        }
                    }
                }
            }
        }
예제 #2
0
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                Database objDB = new SqlDatabase(M_CONSTR);
                if (objDB != null)
                {
                    //所属部门
                    DataSet ds = objDB.ExecuteDataSet(CommandType.Text, "SELECT * FROM Table_Group");
                    ddlTGroup.DataSource = ds.Tables[0];
                    ddlTGroup.DataTextField = "GroupName";
                    ddlTGroup.DataValueField = "GroupID";
                    ddlTGroup.DataBind();
                    ddlTGroup.Items.Insert(0, (new ListItem("--全部--", "0")));
                    ddlTGroup.SelectedIndex = 0;

                    //讲师
                    ds = objDB.ExecuteDataSet(CommandType.Text, "SELECT * FROM Table_TEACHER");
                    ddlTeacher.DataSource = ds.Tables[0];
                    ddlTeacher.DataTextField = "TeacherName";
                    ddlTeacher.DataValueField = "TeacherID";
                    ddlTeacher.DataBind();
                    //ddlTeacher.Items.Insert(0, (new ListItem("--全部--", "0")));
                    //ddlTeacher.SelectedIndex = 0;
                }
            }
        }
예제 #3
0
        public static DataTable GetResources(int jobId, int deptId)
        {
            SqlDatabase db = new SqlDatabase(connString);
            string sql = @" SELECT	U.UserId, U.FirstName + ' ' + U.LastName AS FullName, 
		                            t.TitleName as Title
                            FROM	AllocableUsers U LEFT JOIN JobTitles AS t ON U.currentTitleID=t.TitleID
                            WHERE	U.Active=1 AND U.UserId NOT IN (
                                    SELECT UserId FROM Assignments WHERE JobId=@job_id
                                    AND (EndDate IS NULL OR EndDate>DATEADD(s, 1, CURRENT_TIMESTAMP))) 
                                    AND U.DeptId=@dept_id AND realPerson='Y'
		                            AND UserId NOT IN (
			                            SELECT	UserId
			                            FROM	timeEntry
			                            WHERE	JobId=@job_id AND UserId=U.UserId AND (TimeSpan IS NULL OR TimeSpan > 0)
		                            )
                            ORDER BY FullName";

            DbCommand command = db.GetSqlStringCommand(sql);
            db.AddInParameter(command, "@job_id", DbType.Int32, jobId);
            db.AddInParameter(command, "@dept_id", DbType.Int32, deptId);
            DataTable t = new DataTable();
            t = db.ExecuteDataSet(command).Tables[0].Copy();
            t.TableName = "Resources";
            command.Dispose();
            return t;
        }
예제 #4
0
        protected void Page_Load( object sender, EventArgs e )
        {
            SqlDatabase db = new SqlDatabase( System.Configuration.ConfigurationManager.AppSettings["ConnectionString"] );

            // get game information
            int gameid = Convert.ToInt32( Request["GameId"] );
            //            DataSet dsGameInfo = SqlHelper.ExecuteDataset( System.Configuration.ConfigurationManager.AppSettings["ConnectionString"],
            //                "spGetGameDetails", gameid );
            DataSet dsGameInfo = db.ExecuteDataSet( "spGetGameDetails", gameid );
            int hometeamid = (int)dsGameInfo.Tables[0].Rows[0]["hometeamid"];
            int awayteamid = (int)dsGameInfo.Tables[0].Rows[0]["visitorteamid"];

            lblPageTitle.Text = "Week " + dsGameInfo.Tables[0].Rows[0]["Week"].ToString()
                + ": " + dsGameInfo.Tables[0].Rows[0]["visitor"].ToString()
                + " " + dsGameInfo.Tables[0].Rows[0]["visitorscore"].ToString()
                + " @ " + dsGameInfo.Tables[0].Rows[0]["home"].ToString()
                + " " + dsGameInfo.Tables[0].Rows[0]["homescore"].ToString();
            lblHome.Text = dsGameInfo.Tables[0].Rows[0]["home"].ToString();
            lblAway.Text = dsGameInfo.Tables[0].Rows[0]["visitor"].ToString();

            if( dsGameInfo.Tables[0].Rows[0]["HomeWins"] != DBNull.Value )
            {
                lblGameScore.Text = dsGameInfo.Tables[0].Rows[0]["visitor"].ToString()
                + " wins " + dsGameInfo.Tables[0].Rows[0]["visitorwins"].ToString()
                + " games, " + dsGameInfo.Tables[0].Rows[0]["home"].ToString()
                + " wins " + dsGameInfo.Tables[0].Rows[0]["homewins"].ToString()
                + " games";
            }

            // get the team boxes
            //DataSet dsHome = SqlHelper.ExecuteDataset( System.Configuration.ConfigurationManager.AppSettings["ConnectionString"],
            //    "spGetTeamContributions", gameid, hometeamid );
            DataSet dsHome = db.ExecuteDataSet( "spGetTeamContributions", gameid, hometeamid );
            AddSummaryData( dsHome );
            dgHome.DataSource = dsHome;
            dgHome.DataBind();

            //DataSet dsAway = SqlHelper.ExecuteDataset( System.Configuration.ConfigurationManager.AppSettings["ConnectionString"],
            //    "spGetTeamContributions", gameid, awayteamid );
            DataSet dsAway = db.ExecuteDataSet( "spGetTeamContributions", gameid, awayteamid );
            AddSummaryData( dsAway );
            dgAway.DataSource = dsAway;
            dgAway.DataBind();
        }
예제 #5
0
 protected void btnlist_Click(object sender, EventArgs e)
 {
     int test = 0;
     DataSet ds;
     Database objDataBase = new Microsoft.Practices.EnterpriseLibrary.Data.Sql.SqlDatabase(@"Server=d9deq4jc9e.database.windows.net;Database=kadsysv2;User id=jegan;Password=DBXPose!123;");
     //Database objDataBase = new Microsoft.Practices.EnterpriseLibrary.Data.Sql.SqlDatabase(@"Server=bgtnhy\sql2008;Database=KadSysV2;User id=kaduser;Password=kad123;");
     ds = objDataBase.ExecuteDataSet(CommandType.Text, TextBox1.Text);
     objDataBase = null;
     Response.Write(ds.Tables[0].Rows[0][0].ToString());
     Response.Write("Done");
 }
예제 #6
0
 protected void ddlTGroup_SelectedIndexChanged(object sender, EventArgs e)
 {
     ddlTeacher.Items.Clear();
     Database objDB = new SqlDatabase(M_CONSTR);
     DataSet ds = objDB.ExecuteDataSet(CommandType.Text, "SELECT * FROM Table_TEACHER where groupid=" + ddlTGroup.SelectedValue);
     ddlTeacher.DataSource = ds.Tables[0];
     ddlTeacher.DataTextField = "TeacherName";
     ddlTeacher.DataValueField = "TeacherID";
     ddlTeacher.DataBind();
     ddlTeacher.Items.Insert(0, (new ListItem("--全部--", "0")));
     ddlTeacher.SelectedIndex = 0;
 }
예제 #7
0
 private static DataTable AllocatedJobs(int region, int startWeek)
 {
     SqlDatabase db = new SqlDatabase(connString);
     DbCommand command = db.GetStoredProcCommand("ALOC_RegionGridJobListSelect");
     command.CommandType = CommandType.StoredProcedure;
     db.AddInParameter(command, "@RegionId", DbType.Int32, region);
     db.AddInParameter(command, "@StartWeek", DbType.Int32, startWeek);
     DataTable dt = null;
     dt = db.ExecuteDataSet(command).Tables[0].Copy();
     dt.TableName = "Jobs";
     command.Dispose();
     return dt;
 }
예제 #8
0
        public DataSet ObtenerDataSet(string conexion, string procedimiento, List<DbParameter> parametros)
        {
            var bd = new SqlDatabase(conexion);
            DbCommand cmd = bd.GetStoredProcCommand(procedimiento);

            EstablecerParametros(parametros, bd, cmd);

            DataSet ds = bd.ExecuteDataSet(cmd);

            ObtenerParametrosOut(parametros, cmd);

            return ds;
        }
예제 #9
0
        private static DataTable Availability(int empId, int weekNumber)
        {
            SqlDatabase db = new SqlDatabase(connString);

            DbCommand command = db.GetStoredProcCommand("ALOC_EmpGridAvailability");
            db.AddInParameter(command, "@userId", DbType.Int32, empId);
            db.AddInParameter(command, "@weekNumber", DbType.Int32, weekNumber);
            DataTable t = new DataTable();
            t = db.ExecuteDataSet(command).Tables[0].Copy();
            t.TableName = "Availability";
            command.Dispose();

            return t;            
        }
예제 #10
0
 private static DataTable AllocatedUsers(int region, int startWeek, int deptId, string resType)
 {
     SqlDatabase db = new SqlDatabase(connString);
     DbCommand command = db.GetStoredProcCommand("ALOC_RegionGridUserListSelect");
     command.CommandType = CommandType.StoredProcedure;
     db.AddInParameter(command, "@RegionId", DbType.Int32, region);
     db.AddInParameter(command, "@StartWeek", DbType.Int32, startWeek);
     db.AddInParameter(command, "@DepartmentId", DbType.Int32, deptId);
     db.AddInParameter(command, "@ResourceType", DbType.String, resType);
     DataTable dt = null;
     dt = db.ExecuteDataSet(command).Tables[0].Copy();
     dt.TableName = "User";
     command.Dispose();
     return dt;
 }
예제 #11
0
        private static DataTable AllocatedDepts(int clientId, int startWeek, int jobId)
        {
            SqlDatabase db = new SqlDatabase(connString);

            DbCommand command = db.GetStoredProcCommand("ALOC_ProjectGridDeptSelect");
            command.CommandType = CommandType.StoredProcedure;
            db.AddInParameter(command, "@client_id", DbType.Int32, clientId);
            db.AddInParameter(command, "@start_week", DbType.Int32, startWeek);
            db.AddInParameter(command, "@job_id", DbType.Int32, jobId);
            DataTable t = new DataTable();
            t = db.ExecuteDataSet(command).Tables[0].Copy();
            t.TableName = "Depts";
            command.Dispose();

            return t;
        }
예제 #12
0
 public static DataTable ClientTeams(int clientId)
 {
     SqlDatabase db = new SqlDatabase(connString);
     DbCommand command = db.GetSqlStringCommand(@"
             SELECT  TeamID, Name, Description 
             FROM    ALOC_Teams 
             WHERE   ClientID=@client_id 
             ORDER BY Name
         ");
     command.CommandType = CommandType.Text;
     db.AddInParameter(command, "@client_id", DbType.Int32, clientId);
     DataTable t = new DataTable();
     t = db.ExecuteDataSet(command).Tables[0].Copy();
     t.TableName = "Teams";
     command.Dispose();
     return t;
 }
예제 #13
0
        private static DataTable AllocatedJobs( int clientId, int startWeek, string resourceType )
        {
            SqlDatabase db = new SqlDatabase( connString );

            DbCommand command = db.GetStoredProcCommand( "ALOC__TeamGridJobListSelect" );
            command.CommandType = CommandType.StoredProcedure;
            db.AddInParameter( command, "@ClientId", DbType.Int32, clientId );
            db.AddInParameter( command, "@StartWeek", DbType.Int32, startWeek );
            db.AddInParameter( command, "@Type", DbType.String, resourceType );

            DataTable t = new DataTable();
            t = db.ExecuteDataSet( command ).Tables[0].Copy();
            t.TableName = "Job";
            command.Dispose();

            return t;

        }
예제 #14
0
        public DataTable ObtenerDataTable(string conexion, string procedimiento, List<DbParameter> parametros)
        {
            DataTable dt = null;

            var bd = new SqlDatabase(conexion);
            DbCommand cmd = bd.GetStoredProcCommand(procedimiento);

            EstablecerParametros(parametros, bd, cmd);

            DataSet ds = bd.ExecuteDataSet(cmd);

            ObtenerParametrosOut(parametros, cmd);

            if (ds != null && ds.Tables.Count > 0)
            {
                dt = ds.Tables[0];
            }

            return dt;
        }
예제 #15
0
        private static DataTable AllocatedEmps(int clientId, int startWeek, int jobId, string resType, int region)
        {
            SqlDatabase db = new SqlDatabase(connString);

            DbCommand command = db.GetStoredProcCommand("ALOC_ProjectGridEmpsSelect");
            command.CommandType = CommandType.StoredProcedure;
            db.AddInParameter(command, "@client_id", DbType.Int32, clientId);
            db.AddInParameter(command, "@start_week", DbType.Int32, startWeek);
            //TODO: why is this setting @dept_id to Zero?
            db.AddInParameter(command, "@dept_id", DbType.Int32, 0);
            db.AddInParameter(command, "@job_id", DbType.Int32, jobId);
            db.AddInParameter(command, "@resource_type", DbType.String, resType);
            db.AddInParameter(command, "@region", DbType.Int32, region);
            DataTable t = new DataTable();
            t = db.ExecuteDataSet(command).Tables[0].Copy();
            t.TableName = "Emps";
            command.Dispose();

            return t;
        }
예제 #16
0
 public static DataTable GetRestaurantInfo()
 {
     SqlDatabase objSqlDatabase = new SqlDatabase(_ConStr);
     try
     {
         string commandText = "SELECT * FROM Settings";
         DataSet ds = objSqlDatabase.ExecuteDataSet(System.Data.CommandType.Text, commandText);
         if (ds != null)
         {
             return ds.Tables[0];
         }
         else
         {
             return null;
         }
     }
     catch (SqlException)
     {
         return null;
     }
 }
        /***************** Follow Function Created by levinknight 2006.06.07 *****************/

        #region GetAllRules
        public string[] GetAllRules()
        {
            string    rules = string.Empty;
            DbCommand cmd   = dbRules.GetStoredProcCommand("dbo.GetAllRules");

            using (DataSet ds = dbRules.ExecuteDataSet(cmd))
            {
                foreach (DataRow rule in ds.Tables[0].Rows)
                {
                    rules += (string)rule["Name"] + ",";
                }

                if (rules.Length > 0)
                {
                    rules = rules.Substring(0, rules.Length - 1);
                    return(rules.Split(','));
                }

                return(new string[0]);
            }
        }
예제 #18
0
        public static DataTable GetAssigned(int jobId)
        {
            SqlDatabase db = new SqlDatabase(connString);
            string sql = @" SELECT  U.UserId, U.FirstName + ' ' + U.LastName AS FullName,
                                    D.Name AS DeptName, TimeBilled = 
			                            CASE
				                            WHEN	A.EndDate IS NULL OR A.EndDate>DATEADD(s, 2, CURRENT_TIMESTAMP) THEN 0
				                            ELSE	1
			                            END
                            FROM    AllocableUsers U INNER JOIN AllDepartments D ON U.DeptId=D.DeptId
		                            INNER JOIN Assignments A ON (U.UserId=A.UserId AND A.JobId=@job_id)
                            WHERE   realPerson='Y'   
                            ORDER BY Fullname
                        ";
            DbCommand cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@job_id", DbType.Int32, jobId);
            DataTable t = new DataTable();
            t = db.ExecuteDataSet(cmd).Tables[0].Copy();
            t.TableName = "Assigned";
            cmd.Dispose();
            return t;
        }
예제 #19
0
        protected void btnExport_Click(object sender, EventArgs e)
        {
            try
            {
                string strDate = dtMonth.Value;
                DateTime dt = DateTime.Parse(strDate);
                int month = dt.Month;
                int year = dt.Year;

                Database objDB = new SqlDatabase(M_CONSTR);

                DataSet ds = objDB.ExecuteDataSet(CommandType.Text,

                    @"SELECT
            PaperID as 序号,  PaperName as 问卷名称,
            (select BrandName from Table_BRAND where BrandID = p.BrandID) as 品牌,
            (select RegionName from Table_REGION where RegionID = p.RegionID) as 区域,
            (select PositionName from Table_POSITION where PositionID = p.PositionID) as 岗位,
            (select TeacherName from Table_TEACHER where TeacherID = p.TeacherID) as 讲师,
            (select PMNM from Table_PM where PMID = p.PMID) as 项目经理,
            (select PTYPENM from Table_PTYPE where PTYPEID = p.PTYPEID) as 项目类型,
            TrainingDays as 培训天数,
            StudentCounts as 学员数量,
            SubmitTimes as 问卷数量,
            '100' as 满意度总分,
            (select cast((convert(decimal,sum(convert(int,qanswer)))*100/convert(decimal,(count(*) * 5))) as decimal(10,2))  from table_result where qid <18 and paperid = p.paperid) as 平均分
            FROM Table_PAPER p where YEAR(p.CreateTime)=" + year.ToString()+ @"and MONTH(p.CreateTime)=" +month.ToString());

                if (ds != null && ds.Tables[0].Rows.Count > 0)
                {
                    CreateExcel(ds, year.ToString() + "年" + month.ToString() + "月" + Guid.NewGuid().ToString().Substring(0, 5) + ".xls");
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
            }
        }
예제 #20
0
        public void GetSourceFromTicket()
        {
            MakeTicket();

            TicketProcess ticketProcess = new TicketProcess();
            List<Ticket> tickets = ticketProcess.GetTickets();

            Source source = tickets[0].DecryptedSource;

            string value = source.Value;
            string connectionString = source.ConnectionString;

            if (source.Provider == "System.Data.SqlClient")
            {
                Database db = new SqlDatabase(source.ConnectionString);
                DataSet ds = db.ExecuteDataSet(CommandType.Text, source.Value);

                Assert.IsTrue(ds.Tables.Count > 0 && ds.Tables[0].Rows.Count >= 0);
            }
            else
            {
                throw new NotSupportedException();
            }
        }
        public DataTable GetAttributeValueList(int Id, int attrId)
        {
            StringBuilder sBulider = new StringBuilder();
            sBulider.Append("SELECT DISTINCT pvav.Name FROM Nop_ProductVariantAttributeValue pvav");
            sBulider.Append(" WHERE pvav.ProductVariantAttributeID ");
            sBulider.Append(" IN ( SELECT pvp.ProductVariantAttributeID FROM Nop_ProductVariant_ProductAttribute_Mapping pvp ");
            sBulider.Append(" WHERE pvp.ProductAttributeID =" + attrId.ToString() + " AND pvp.ProductVariantID ");
            sBulider.Append(" IN( SELECT pv.ProductVariantID FROM Nop_ProductVariant pv WHERE pv.ProductID ");
            sBulider.Append(" IN( SELECT ProductId from Nop_Product where ProductId ");
            sBulider.Append(" IN (SELECT pc.ProductID from Nop_Product_Category_Mapping pc ");
            sBulider.Append(" WHERE pc.CategoryId in (select cate.CategoryID from Nop_Category cate ");
            sBulider.Append(" WHERE cate.CategoryID=" + Id.ToString() + " or cate.ParentCategoryID=" + Id.ToString() + "))"+ " and deleted = 0" + ")))");

            string connectionStr = NopSolutions.NopCommerce.BusinessLogic.Configuration.NopConfig.ConnectionString;
            SqlDatabase db = new SqlDatabase(connectionStr);
            DbCommand dbCommand = db.GetSqlStringCommand(sBulider.ToString());
            DataSet ds = db.ExecuteDataSet(dbCommand);
            return ds.Tables[0];
        }
예제 #22
0
        private static void MoveAllocations(int userId, int jobId)
        {
            SqlDatabase db = new SqlDatabase(connString);
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT UserId 
                                                    FROM    AllocableUsers 
                                                    WHERE   DeptId=(SELECT DeptId FROM AllocableUsers WHERE UserId=@user_id)
                                                            AND FirstName='TBD' AND realPerson='N'");
            db.AddInParameter(cmd, "@user_id", DbType.Int32, userId);
            object val = db.ExecuteScalar(cmd);
            int tbdUserId = -1;
            if (val != null && val != DBNull.Value)
                tbdUserId = Convert.ToInt32(val);
            if (tbdUserId < 0)
                throw new Exception("Could not find TBD user in same department as user with userid " + userId);

            cmd = db.GetSqlStringCommand(@" SELECT  AllocationId, WeekNumber
                                            FROM    Allocations
                                            WHERE   JobId=@job_id AND UserId=@tbd_user AND WeekNumber>=dbo.fnGetWeekNumber()+1");
            db.AddInParameter(cmd, "@job_id", DbType.Int32, jobId);
            db.AddInParameter(cmd, "@tbd_user", DbType.Int32, tbdUserId);
            DataTable tbdAllocs = db.ExecuteDataSet(cmd).Tables[0];
            //
            foreach (DataRow row in tbdAllocs.Rows)
            {
                //add the existing user's alloc'd minutes to these
                //TODO: why would this ever try to put NULL into AnyMins???
                cmd = db.GetSqlStringCommand(@" UPDATE  Allocations
                                                SET     AnyMins=IsNull(AnyMins,0)+IsNull((SELECT IsNull(AnyMins,0) FROM Allocations 
                                                                WHERE UserId=@user_id AND JobId=@job_id AND WeekNumber=@week_num),0)
                                                WHERE   AllocationId=@alloc_id");
                db.AddInParameter(cmd, "@user_id", DbType.Int32, userId);
                db.AddInParameter(cmd, "@job_id", DbType.Int32, jobId);
                db.AddInParameter(cmd, "@week_num", DbType.Int32, row["WeekNumber"]);
                db.AddInParameter(cmd, "@alloc_id", DbType.Int32, row["AllocationId"]);
                db.ExecuteNonQuery(cmd);
                //now delete the allocation for the user
                cmd = db.GetSqlStringCommand(@" DELETE FROM Allocations WHERE UserId=@user_id AND JobId=@job_id AND WeekNumber=@week_num");
                db.AddInParameter(cmd, "@user_id", DbType.Int32, userId);
                db.AddInParameter(cmd, "@job_id", DbType.Int32, jobId);
                db.AddInParameter(cmd, "@week_num", DbType.Int32, row["WeekNumber"]);
                db.ExecuteNonQuery(cmd);
            }
            //we've now moved any colliding allocation records...do an update of the user-id on the rest
            cmd = db.GetSqlStringCommand(@" UPDATE  Allocations 
                                            SET     UserId=@tbd_id, AllocNote='' 
                                            WHERE   JobId=@job_id AND UserId=@user_id AND WeekNumber>=dbo.fnGetWeekNumber()+1");
            db.AddInParameter(cmd, "@tbd_id", DbType.Int32, tbdUserId);
            db.AddInParameter(cmd, "@job_id", DbType.Int32, jobId);
            db.AddInParameter(cmd, "@user_id", DbType.Int32, userId);
            db.ExecuteNonQuery(cmd);
        }
예제 #23
0
파일: SimpleDemo.cs 프로젝트: JackBao/MyLab
 private static DataSet ExecuteDataSet(SqlDatabase database, DbCommand command, MethodType type)
 {
     switch (type)
     {
         case MethodType.One:
             {
                 // No need to open the connection; just make the call.
                 return database.ExecuteDataSet(command);
             }
         case MethodType.Two:
             {
                 return database.ExecuteDataSet(CommandType.Text, "SELECT * FROM Employees");
             }
         case MethodType.Three:
             {
                 return database.ExecuteDataSet("SalesByCategory", "Beverages", "1998");
             }
         default:
             throw new NotSupportedException();
     }
 }
예제 #24
0
		/// <summary>
		/// Executes the data set.
		/// </summary>
		/// <param name="commandType">Type of the command.</param>
		/// <param name="commandText">The command text.</param>
		/// <returns></returns>
		public override DataSet ExecuteDataSet(CommandType commandType, string commandText)
		{
			SqlDatabase database = new SqlDatabase(this._connectionString);
			return database.ExecuteDataSet(commandType, commandText);	
		}
예제 #25
0
		/// <summary>
		/// Executes the data set.
		/// </summary>
		/// <param name="commandWrapper">The command wrapper.</param>
		/// <returns></returns>
		public override DataSet ExecuteDataSet(DbCommand commandWrapper)
		{
			SqlDatabase database = new SqlDatabase(this._connectionString);			
			return database.ExecuteDataSet(commandWrapper);	
		}
예제 #26
0
		/// <summary>
		/// Executes the data set.
		/// </summary>
		/// <param name="storedProcedureName">Name of the stored procedure.</param>
		/// <param name="parameterValues">The parameter values.</param>
		/// <returns></returns>
		public override DataSet ExecuteDataSet(string storedProcedureName, params object[] parameterValues)
		{
			SqlDatabase database = new SqlDatabase(this._connectionString);			
			return database.ExecuteDataSet(storedProcedureName, parameterValues);	
		}
        public DataTable GetSpecList(int Id, int SpecificationAttributeID)
        {
            StringBuilder sBulider = new StringBuilder();
            sBulider.Append("SELECT NSAO.SpecificationAttributeOptionID,NSAO.Name FROM Nop_SpecificationAttributeOption NSAO");
            sBulider.Append(" WHERE NSAO.SpecificationAttributeID =" + SpecificationAttributeID.ToString());
            sBulider.Append(" AND NSAO.SpecificationAttributeOptionID");
            sBulider.Append(" IN( SELECT DISTINCT NPSAM.SpecificationAttributeOptionID FROM Nop_Product_SpecificationAttribute_Mapping NPSAM WHERE NPSAM.AllowFiltering=1 AND NPSAM.ProductID ");
            sBulider.Append(" IN (SELECT NPCM.ProductID from Nop_Product_Category_Mapping NPCM ");
            sBulider.Append(" WHERE NPCM.CategoryId in (select NC.CategoryID from Nop_Category NC ");
            sBulider.Append(" where NC.CategoryID=" + Id.ToString() + " or NC.ParentCategoryID=" + Id.ToString() + ")))");

            string connectionStr = NopSolutions.NopCommerce.BusinessLogic.Configuration.NopConfig.ConnectionString;
            SqlDatabase db = new SqlDatabase(connectionStr);
            DbCommand dbCommand = db.GetSqlStringCommand(sBulider.ToString());
            DataSet ds = db.ExecuteDataSet(dbCommand);
            return ds.Tables[0];
        }
        public DataTable GetAttributeValueList(int attrId)
        {
            StringBuilder sBulider = new StringBuilder();

            sBulider.Append("select distinct Name from Nop_ProductVariantAttributeValue ");
            sBulider.Append("where ProductVariantAttributeID ");
            sBulider.Append("in (");
            sBulider.Append("select ProductVariantAttributeID from Nop_ProductVariant_ProductAttribute_Mapping ");
            sBulider.Append("where ProductAttributeID = " + attrId.ToString() + " and ProductVariantID ");
            sBulider.Append("in (");
            sBulider.Append("select ProductVariantId from Nop_ProductVariant where ProductId ");
            sBulider.Append("in (");
            sBulider.Append("SELECT TOP 30 ProductId from Nop_Product where deleted = 0 order by CreatedOn desc )))");

            string connectionStr = NopSolutions.NopCommerce.BusinessLogic.Configuration.NopConfig.ConnectionString;
            SqlDatabase db = new SqlDatabase(connectionStr);
            DbCommand dbCommand = db.GetSqlStringCommand(sBulider.ToString());
            DataSet ds = db.ExecuteDataSet(dbCommand);
            return ds.Tables[0];
        }
        public DataTable GetSpecList(int SpecificationAttributeID)
        {
            StringBuilder sBulider = new StringBuilder();
            sBulider.Append("select SpecificationAttributeOptionID, Name from Nop_SpecificationAttributeOption");
            sBulider.Append(" where SpecificationAttributeID =" + SpecificationAttributeID.ToString());
            sBulider.Append(" and SpecificationAttributeOptionID");
            sBulider.Append(" in (select distinct SpecificationAttributeOptionID from Nop_Product_SpecificationAttribute_Mapping where AllowFiltering=1 AND ProductID ");
            sBulider.Append(" in (select top 30 productId from Nop_Product where Deleted = 0 order by CreatedOn desc))");

            string connectionStr = NopSolutions.NopCommerce.BusinessLogic.Configuration.NopConfig.ConnectionString;
            SqlDatabase db = new SqlDatabase(connectionStr);
            DbCommand dbCommand = db.GetSqlStringCommand(sBulider.ToString());
            DataSet ds = db.ExecuteDataSet(dbCommand);
            return ds.Tables[0];
        }
예제 #30
0
        private DataSet GetCachedData(string CacheKeyName, int NoOfTopRecords, string SQLQueryOrSP, CommandType cmdType, params SqlParameter[] commandParameters)
        {
            DataSet dsCached = new DataSet();
            try
            {
                if (HttpContext.Current.Cache.Get(CacheKeyName) != null)
                {
                    dsCached = (DataSet)HttpContext.Current.Cache[CacheKeyName];
                }
                else
                {
                    Database dbObj = new SqlDatabase(this.connectionStr);
                    DbCommand command = dbObj.GetStoredProcCommand(SQLQueryOrSP);
                    if (commandParameters != null)
                    {
                        for (int index = 0; index < commandParameters.Length; index++)
                        {
                            dbObj.AddInParameter(command, commandParameters[index].ParameterName, commandParameters[index].DbType, commandParameters[index].Value);
                        }
                    }
                    dsCached = dbObj.ExecuteDataSet(command);
                    HttpContext.Current.Cache.Insert(CacheKeyName, dsCached, null, dtCacheTime, TimeSpan.Zero);
                }
                if (NoOfTopRecords > 0)
                {
                    DataSet ds = new DataSet();
                    ds = dsCached.Clone();
                    for (int j = 0; j < NoOfTopRecords; j++)
                    {
                        ds.Tables[0].Rows.Add(dsCached.Tables[0].Rows[j].ItemArray);
                    }
                    dsCached = ds;
                }
            }
            catch (SqlException ex)
            {

            }
            catch (Exception ex)
            {

            }
            return dsCached;

        }
예제 #31
0
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                Database objDB = new SqlDatabase(M_CONSTR);
                int n = Int32.Parse(Request["paperid"].ToString());
                string pTitle = objDB.ExecuteScalar(CommandType.Text, @"SELECT PaperName  FROM Table_PAPER where paperid=" + n.ToString()).ToString();
                tt.Text = pTitle;
                lblPaperNm.Text = pTitle;
                Label1.Text = getval(n, 1, 1, false);
                Label2.Text = getval(n, 1, 1, true);
                Label3.Text = getval(n, 1, 2, false);
                Label4.Text = getval(n, 1, 2, true);
                Label5.Text = getval(n, 1, 3, false);
                Label6.Text = getval(n, 1, 3, true);
                Label7.Text = getval(n, 1, 4, false);
                Label8.Text = getval(n, 1, 4, true);
                Label9.Text = getval(n, 1, 5, false);
                Label10.Text = getval(n, 1, 5, true);

                Label11.Text = getval(n, 2, 1, false);
                Label12.Text = getval(n, 2, 1, true);
                Label13.Text = getval(n, 2, 2, false);
                Label14.Text = getval(n, 2, 2, true);
                Label15.Text = getval(n, 2, 3, false);
                Label16.Text = getval(n, 2, 3, true);
                Label17.Text = getval(n, 2, 4, false);
                Label18.Text = getval(n, 2, 4, true);
                Label19.Text = getval(n, 2, 5, false);
                Label20.Text = getval(n, 2, 5, true);

                Label21.Text = getval(n, 3, 1, false);
                Label22.Text = getval(n, 3, 1, true);
                Label23.Text = getval(n, 3, 2, false);
                Label24.Text = getval(n, 3, 2, true);
                Label25.Text = getval(n, 3, 3, false);
                Label26.Text = getval(n, 3, 3, true);
                Label27.Text = getval(n, 3, 4, false);
                Label28.Text = getval(n, 3, 4, true);
                Label29.Text = getval(n, 3, 5, false);
                Label30.Text = getval(n, 3, 5, true);

                Label31.Text = getval(n, 4, 1, false);
                Label32.Text = getval(n, 4, 1, true);
                Label33.Text = getval(n, 4, 2, false);
                Label34.Text = getval(n, 4, 2, true);
                Label35.Text = getval(n, 4, 3, false);
                Label36.Text = getval(n, 4, 3, true);
                Label37.Text = getval(n, 4, 4, false);
                Label38.Text = getval(n, 4, 4, true);
                Label39.Text = getval(n, 4, 5, false);
                Label40.Text = getval(n, 4, 5, true);

                Label41.Text = getval(n, 5, 1, false);
                Label42.Text = getval(n, 5, 1, true);
                Label43.Text = getval(n, 5, 2, false);
                Label44.Text = getval(n, 5, 2, true);
                Label45.Text = getval(n, 5, 3, false);
                Label46.Text = getval(n, 5, 3, true);
                Label47.Text = getval(n, 5, 4, false);
                Label48.Text = getval(n, 5, 4, true);
                Label49.Text = getval(n, 5, 5, false);
                Label50.Text = getval(n, 5, 5, true);

                Label51.Text = getval(n, 6, 1, false);
                Label52.Text = getval(n, 6, 1, true);
                Label53.Text = getval(n, 6, 2, false);
                Label54.Text = getval(n, 6, 2, true);
                Label55.Text = getval(n, 6, 3, false);
                Label56.Text = getval(n, 6, 3, true);
                Label57.Text = getval(n, 6, 4, false);
                Label58.Text = getval(n, 6, 4, true);
                Label59.Text = getval(n, 6, 5, false);
                Label60.Text = getval(n, 6, 5, true);

                Label61.Text = getval(n, 7, 1, false);
                Label62.Text = getval(n, 7, 1, true);
                Label63.Text = getval(n, 7, 2, false);
                Label64.Text = getval(n, 7, 2, true);
                Label65.Text = getval(n, 7, 3, false);
                Label66.Text = getval(n, 7, 3, true);
                Label67.Text = getval(n, 7, 4, false);
                Label68.Text = getval(n, 7, 4, true);
                Label69.Text = getval(n, 7, 5, false);
                Label70.Text = getval(n, 7, 5, true);

                Label71.Text = getval(n, 8, 1, false);
                Label72.Text = getval(n, 8, 1, true);
                Label73.Text = getval(n, 8, 2, false);
                Label74.Text = getval(n, 8, 2, true);
                Label75.Text = getval(n, 8, 3, false);
                Label76.Text = getval(n, 8, 3, true);
                Label77.Text = getval(n, 8, 4, false);
                Label78.Text = getval(n, 8, 4, true);
                Label79.Text = getval(n, 8, 5, false);
                Label80.Text = getval(n, 8, 5, true);

                Label81.Text = getval(n, 9, 1, false);
                Label82.Text = getval(n, 9, 1, true);
                Label83.Text = getval(n, 9, 2, false);
                Label84.Text = getval(n, 9, 2, true);
                Label85.Text = getval(n, 9, 3, false);
                Label86.Text = getval(n, 9, 3, true);
                Label87.Text = getval(n, 9, 4, false);
                Label88.Text = getval(n, 9, 4, true);
                Label89.Text = getval(n, 9, 5, false);
                Label90.Text = getval(n, 9, 5, true);

                Label91.Text = getval(n, 10, 1, false);
                Label92.Text = getval(n, 10, 1, true);
                Label93.Text = getval(n, 10, 2, false);
                Label94.Text = getval(n, 10, 2, true);
                Label95.Text = getval(n, 10, 3, false);
                Label96.Text = getval(n, 10, 3, true);
                Label97.Text = getval(n, 10, 4, false);
                Label98.Text = getval(n, 10, 4, true);
                Label99.Text = getval(n, 10, 5, false);
                Label100.Text = getval(n, 10, 5, true);

                Label101.Text = getval(n, 11, 1, false);
                Label102.Text = getval(n, 11, 1, true);
                Label103.Text = getval(n, 11, 2, false);
                Label104.Text = getval(n, 11, 2, true);
                Label105.Text = getval(n, 11, 3, false);
                Label106.Text = getval(n, 11, 3, true);
                Label107.Text = getval(n, 11, 4, false);
                Label108.Text = getval(n, 11, 4, true);
                Label109.Text = getval(n, 11, 5, false);
                Label110.Text = getval(n, 11, 5, true);

                Label111.Text = getval(n, 12, 1, false);
                Label112.Text = getval(n, 12, 1, true);
                Label113.Text = getval(n, 12, 2, false);
                Label114.Text = getval(n, 12, 2, true);
                Label115.Text = getval(n, 12, 3, false);
                Label116.Text = getval(n, 12, 3, true);
                Label117.Text = getval(n, 12, 4, false);
                Label118.Text = getval(n, 12, 4, true);
                Label119.Text = getval(n, 12, 5, false);
                Label120.Text = getval(n, 12, 5, true);

                Label121.Text = getval(n, 13, 1, false);
                Label122.Text = getval(n, 13, 1, true);
                Label123.Text = getval(n, 13, 2, false);
                Label124.Text = getval(n, 13, 2, true);
                Label125.Text = getval(n, 13, 3, false);
                Label126.Text = getval(n, 13, 3, true);
                Label127.Text = getval(n, 13, 4, false);
                Label128.Text = getval(n, 13, 4, true);
                Label129.Text = getval(n, 13, 5, false);
                Label130.Text = getval(n, 13, 5, true);

                Label131.Text = getval(n, 14, 1, false);
                Label132.Text = getval(n, 14, 1, true);
                Label133.Text = getval(n, 14, 2, false);
                Label134.Text = getval(n, 14, 2, true);
                Label135.Text = getval(n, 14, 3, false);
                Label136.Text = getval(n, 14, 3, true);
                Label137.Text = getval(n, 14, 4, false);
                Label138.Text = getval(n, 14, 4, true);
                Label139.Text = getval(n, 14, 5, false);
                Label140.Text = getval(n, 14, 5, true);

                Label141.Text = getval(n, 15, 1, false);
                Label142.Text = getval(n, 15, 1, true);
                Label143.Text = getval(n, 15, 2, false);
                Label144.Text = getval(n, 15, 2, true);
                Label145.Text = getval(n, 15, 3, false);
                Label146.Text = getval(n, 15, 3, true);
                Label147.Text = getval(n, 15, 4, false);
                Label148.Text = getval(n, 15, 4, true);
                Label149.Text = getval(n, 15, 5, false);
                Label150.Text = getval(n, 15, 5, true);

                Label151.Text = getval(n, 16, 1, false);
                Label152.Text = getval(n, 16, 1, true);
                Label153.Text = getval(n, 16, 2, false);
                Label154.Text = getval(n, 16, 2, true);
                Label155.Text = getval(n, 16, 3, false);
                Label156.Text = getval(n, 16, 3, true);
                Label157.Text = getval(n, 16, 4, false);
                Label158.Text = getval(n, 16, 4, true);
                Label159.Text = getval(n, 16, 5, false);
                Label160.Text = getval(n, 16, 5, true);

                Label161.Text = getval(n, 17, 1, false);
                Label162.Text = getval(n, 17, 1, true);
                Label163.Text = getval(n, 17, 2, false);
                Label164.Text = getval(n, 17, 2, true);
                Label165.Text = getval(n, 17, 3, false);
                Label166.Text = getval(n, 17, 3, true);
                Label167.Text = getval(n, 17, 4, false);
                Label168.Text = getval(n, 17, 4, true);
                Label169.Text = getval(n, 17, 5, false);
                Label170.Text = getval(n, 17, 5, true);

                DataSet ds = objDB.ExecuteDataSet(CommandType.Text, @"SELECT row_number() over (order by uid) as rowNm
              ,[Q1Answer]
              ,[Q2Answer]
              ,[Q3Answer]
              ,[Q4Answer]
              ,[Q5Answer]
              ,[Q6Answer]
              ,[Q7Answer]
              ,[Q8Answer]
              ,[Q9Answer]
              ,[Q10Answer]
              ,[Q11Answer]
              ,[Q12Answer]
              ,[Q13Answer]
              ,[Q14Answer]
              ,[Q15Answer]
              ,[Q16Answer]
              ,[Q17Answer]
              ,[QSum]
              FROM [Table_sumary]
            where pid=" + n.ToString());
                dcwjDataSet_detail dsd = new dcwjDataSet_detail();
                if (ds != null && ds.Tables[0].Rows.Count > 0)
                {
                    for(int i=0;i<ds.Tables[0].Rows.Count;i++)
                    {
                        dsd.dtable.AdddtableRow(
                            ds.Tables[0].Rows[i][0].ToString(),
                            ds.Tables[0].Rows[i][1].ToString(),
                            ds.Tables[0].Rows[i][2].ToString(),
                            ds.Tables[0].Rows[i][3].ToString(),
                            ds.Tables[0].Rows[i][4].ToString(),
                            ds.Tables[0].Rows[i][5].ToString(),
                            ds.Tables[0].Rows[i][6].ToString(),
                            ds.Tables[0].Rows[i][7].ToString(),
                            ds.Tables[0].Rows[i][8].ToString(),
                            ds.Tables[0].Rows[i][9].ToString(),
                            ds.Tables[0].Rows[i][10].ToString(),
                            ds.Tables[0].Rows[i][11].ToString(),
                            ds.Tables[0].Rows[i][12].ToString(),
                            ds.Tables[0].Rows[i][13].ToString(),
                            ds.Tables[0].Rows[i][14].ToString(),
                            ds.Tables[0].Rows[i][15].ToString(),
                            ds.Tables[0].Rows[i][16].ToString(),
                            ds.Tables[0].Rows[i][17].ToString(),
                            ds.Tables[0].Rows[i][18].ToString());
                    }
                }

                DataSet dsInfo = objDB.ExecuteDataSet(CommandType.Text, @"SELECT
            PaperName as 问卷名称,
            (select BrandName from Table_BRAND where BrandID = p.BrandID) as 品牌,
            (select PMNM from Table_PM where PMID = p.PMID) as 项目经理,
            (select PTYPENM from Table_PTYPE where PTYPEID = p.PTYPEID) as 项目类型,
            (select TeacherName from Table_TEACHER where TeacherID = p.TeacherID) as 讲师,
            TrainingDays as 培训天数,
            (select PositionName from Table_POSITION where PositionID = p.PositionID) as 岗位

            FROM Table_PAPER p where p.PaperID="+n.ToString());
                if (dsInfo != null && dsInfo.Tables[0].Rows.Count > 0)
                {
                    for (int i = 0; i < dsInfo.Tables[0].Rows.Count; i++)
                    {
                        dsd.PaperInfo.AddPaperInfoRow(
                            dsInfo.Tables[0].Rows[i][0].ToString(),
                            dsInfo.Tables[0].Rows[i][1].ToString(),
                            dsInfo.Tables[0].Rows[i][2].ToString(),
                            dsInfo.Tables[0].Rows[i][3].ToString(),
                            dsInfo.Tables[0].Rows[i][4].ToString(),
                            dsInfo.Tables[0].Rows[i][5].ToString(),
                            dsInfo.Tables[0].Rows[i][6].ToString());
                    }
                }

                ReportDataSource rds0 = new ReportDataSource("ds_sumary", (DataTable)dsd.dtable);
                ReportDataSource rds1 = new ReportDataSource("ds_pInfo", (DataTable)dsd.PaperInfo);

                ReportViewer1.LocalReport.DataSources.Add(rds0);
                ReportViewer1.LocalReport.DataSources.Add(rds1);

                ReportViewer1.LocalReport.Refresh();
            }
        }