C# SqlDataSource.Select方法代码示例

 protected void Login1_Authenticate(object sender, AuthenticateEventArgs e)
 {
     string conString = "Data Source=(LocalDB)\\v11.0;AttachDbFilename=|DataDirectory|\\Yummy.mdf;Integrated Security=True";
     string selectString = "SELECT * FROM tb_Userinfo WHERE Username='" + Login1.UserName + "'";//select string for search currency name correspond with the input
     SqlDataSource dsrc = new SqlDataSource(conString, selectString);
     DataView DV = (DataView)dsrc.Select(DataSourceSelectArguments.Empty);
     if (DV.Table.Rows.Count > 0)
     {
         string password = (string)DV.Table.Rows[0][7];
         if (password.Equals(Login1.Password))
         {
             //Label1.Text = "Congratulations! Login succeed! Please wait for redirect to Homepage!";
             Session["Username"] = Login1.UserName;
             Session["Call"] = DV.Table.Rows[0][1].ToString();
             Server.Transfer("~/WebSite2/Default.aspx");
             //Response.Write("<script language=javascript>alert('Congratulations! Login succeed!')</script>");
             //System.Threading.Thread.Sleep(5000);
             //Response.Redirect("~/WebSite2/Default.aspx");
         }
         else
             e.Authenticated = false;
     }
     else
         e.Authenticated = false;
 }

 protected void Page_Load(object sender, EventArgs e)
 {
     try
     {
         String userName = Session["Username"].ToString();
         string conString = "Data Source=(LocalDB)\\v11.0;AttachDbFilename=|DataDirectory|\\Yummy.mdf;Integrated Security=True";
         string selectString = "SELECT * FROM [tb_Userinfo] WHERE Username='" + userName + "'";//select string for search currency name correspond with the input
         SqlDataSource dsrc = new SqlDataSource(conString, selectString);
         DataView DV = (DataView)dsrc.Select(DataSourceSelectArguments.Empty);
         username.Text = DV.Table.Rows[0][6].ToString();
         Call.Text = DV.Table.Rows[0][1].ToString();
         email.Text = DV.Table.Rows[0][3].ToString();
         Phone.Text = DV.Table.Rows[0][5].ToString();
         if(DV.Table.Rows[0][2].ToString()=="Male")
             RadioGenderList.Items[0].Selected=true;
         else
             RadioGenderList.Items[1].Selected = true;
         switch (DV.Table.Rows[0][4].ToString())
         {
             case "BuCentral": RadioAreaList.Items[1].Selected = true; break;
             case "BuWest": RadioAreaList.Items[0].Selected = true; break;
             case "BuEast": RadioAreaList.Items[2].Selected = true; break;
             case "BuOther": RadioAreaList.Items[3].Selected = true; break;
         }
         Register.Text = "Update";
     }
     catch (Exception ex)
     {
         Console.WriteLine(ex.ToString());
     }
 }

    private void executeQuery()
    {
        x = new SqlDataSource();
        x.ConnectionString = @"Data Source=iii0.database.windows.net;Initial Catalog=prjTRK;Persist Security Info=True;User ID=iii;[email protected]";
        x.SelectCommand = "Select * from Company where companyID='" + companyID + "'";
        DataView dv = x.Select(DataSourceSelectArguments.Empty) as DataView;

        txtCounterInput.Text = dv.Table.Rows[0]["name"].ToString();
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        try
        {
            RestaurantName = Session["Restaurantname"].ToString();
            LabelRname.Text = RestaurantName;
            //Session.Remove("Restaurantname");
            Image1.ImageUrl = "~/WebSite2/Image/BU Central/" + RestaurantName + ".jpg";
            string conString = "Data Source=(LocalDB)\\v11.0;AttachDbFilename=|DataDirectory|\\Yummy.mdf;Integrated Security=True";
            string selectString = "SELECT * FROM tb_Restaurant WHERE Name='" + RestaurantName +"'";//select string for search currency name correspond with the input
            SqlDataSource dsrc = new SqlDataSource(conString, selectString);
            DataView DV = (DataView)dsrc.Select(DataSourceSelectArguments.Empty);
            LabelAddress.Text = DV.Table.Rows[0][2].ToString();
            LabelContact.Text = DV.Table.Rows[0][3].ToString();
            LabelCuisine.Text = DV.Table.Rows[0][4].ToString();
            float a = float.Parse(DV.Table.Rows[0][5].ToString());

            float b = (float)(Math.Round(a * 100)) / 100;
            LabelRate.Text = b.ToString();

            LabelDescription.Text = DV.Table.Rows[0][7].ToString();

        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.ToString());
        }
        try
        {
            UserName = Session["Username"].ToString();
            UserCall = Session["Call"].ToString();
            string conString = "Data Source=(LocalDB)\\v11.0;AttachDbFilename=|DataDirectory|\\Yummy.mdf;Integrated Security=True";
            string selectString = "SELECT * FROM tb_Review WHERE Uname='" + UserCall + "' AND Rname='" + RestaurantName + "'";//select string for search currency name correspond with the input
            SqlDataSource dsrc = new SqlDataSource(conString, selectString);
            DataView DV = (DataView)dsrc.Select(DataSourceSelectArguments.Empty);
            if (DV.Table.Rows.Count > 0)
            {
                LabelReviewRate.Visible = false;
                LabelReviewContent.Visible = false;
                TextBoxRate.Visible = false;
                TextBox1.Visible = false;
                Button1.Visible = false;
            }
        }
        catch (Exception ex)
        {
            LabelReviewRate.Visible = false;
            LabelReviewContent.Visible = false;
            TextBoxRate.Visible = false;
            TextBox1.Visible = false;
            Button1.Visible = false;
            Console.WriteLine(ex.ToString());
        }
    }

    protected void SubmitButton_Click1(object sender, EventArgs e)
    {
        string conString = "Data Source=(LocalDB)\\v11.0;AttachDbFilename=|DataDirectory|\\Yummy.mdf;Integrated Security=True";
        string selectString = "SELECT * FROM tb_Userinfo WHERE Username='" + PasswordRecovery1.UserName + "'";//select string for search currency name correspond with the input
        SqlDataSource dsrc = new SqlDataSource(conString, selectString);
        DataView DV = (DataView)dsrc.Select(DataSourceSelectArguments.Empty);
        if (DV.Table.Rows.Count > 0)
        {

            Session["UserForgetName"] = PasswordRecovery1.UserName;
            Server.Transfer("ForgetPassword.aspx");
        }
        else
            PasswordRecovery1.UserNameFailureText = "Username not exists! ";
    }

    public ronUtil2(int id)
    {
        DaysAvailable = getDaysAvailable(id);
        SqlDataSource SqlDataSource3 = new SqlDataSource();
        SqlDataSource3.ConnectionString = ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString();
        SqlDataSource3.SelectCommand = "Select First_Name,Last_Name,Employee_ID,Dept_id,Image From Advisor Where Employee_ID='" + id.ToString() + "'";

        DataView view3 = (DataView)SqlDataSource3.Select(DataSourceSelectArguments.Empty);
        DataTable table3 = view3.ToTable();

        if (table3.Rows.Count > 0)
            FullName = table3.Rows[0][0].ToString() + " " + table3.Rows[0][1].ToString();
        First_Name = table3.Rows[0][0].ToString();
        Last_Name = table3.Rows[0][1].ToString();
        Employee_ID = table3.Rows[0][2].ToString();
        Dept_id = table3.Rows[0][2].ToString();
        Image = table3.Rows[0][3].ToString();
    }

    protected void btnNewsUpdate_Click(object sender, EventArgs e)
    {
        //actID 已知的活動代碼
        //用where查狀態是否還為"待審中"
        //是待審中再進行更新動作
        //是上架就進行Question 警告使用者 更新後, 將重新送審
        x = new SqlDataSource();
        x.ConnectionString = @"Data Source=iii0.database.windows.net;Initial Catalog=prjTRK;Persist Security Info=True;User ID=iii;[email protected]";
        x.SelectCommand = "Select *from Promotions where activityID ='" + activity.Text + "'";
        DataView dv = x.Select(DataSourceSelectArguments.Empty) as DataView;
        statusID = dv.Table.Rows[0]["statusID"].ToString();

        if (statusID == "3" || statusID == "4")
        {
            //壓縮圖片並存檔
            CImageProess.get().UploadImage(FileUpload1, 資料夾路徑, CDictionary.SET_PROMOTION_PX);

            x = new SqlDataSource();
            x.ConnectionString = @"Data Source=iii0.database.windows.net;Initial Catalog=prjTRK;Persist Security Info=True;User ID=iii;[email protected]";

            string sql = "UPDATE Promotions set ";
            if (!string.IsNullOrEmpty(titleInput.Text))
            {
                sql += " title=N'" + titleInput.Text + "', ";
                if (FileUpload1.HasFile)
                    sql += " picPath='" + CImageProess.imageName + "', ";
                sql += " startDate='" + startDate.Text + "', ";
                sql += " endDate='" + endDate.Text + "', ";
                sql += " onShelfDate = convert(varchar, DATEADD(hour, 8, GETUTCDATE()), 113)," ;
                sql += " statusID = 3 ,";
                sql += " content=N'" + txtNewsContent.Text + "' ";
                sql += " WHERE activityID='" + activity.Text + "'";
                x.UpdateCommand = sql;
                x.Update();
                Response.Redirect("~/windowCompany/companyCheck.aspx");
            }
            else
                return;
        }
        else
            return;
    }

 protected void Page_Load(object sender, EventArgs e)
 {
     try
     {
         string username = Session["Username"].ToString();
         string conString = "Data Source=(LocalDB)\\v11.0;AttachDbFilename=|DataDirectory|\\Yummy.mdf;Integrated Security=True";
         string selectString = "SELECT * FROM tb_Userinfo WHERE Username='" + username + "'";//select string for search currency name correspond with the input
         SqlDataSource dsrc = new SqlDataSource(conString, selectString);
         DataView DV = (DataView)dsrc.Select(DataSourceSelectArguments.Empty);
         Call.Text = DV.Table.Rows[0][1].ToString();
         Gender.Text = DV.Table.Rows[0][2].ToString();
         email.Text = DV.Table.Rows[0][3].ToString();
         phone.Text = DV.Table.Rows[0][5].ToString();
         area.Text = DV.Table.Rows[0][4].ToString();
     }
     catch (Exception ex)
     {
         Console.WriteLine(ex.ToString());
     }
 }

    protected void Button1_Click(object sender, EventArgs e)
    {
        DateTime date = DateTime.Now;
        string content = TextBox1.Text.Trim();
        float rate=float.Parse(TextBoxRate.Text.Trim());
        string conString = "Data Source=(LocalDB)\\v11.0;AttachDbFilename=|DataDirectory|\\Yummy.mdf;Integrated Security=True";
        SqlConnection conn = new SqlConnection(conString);
        conn.Open();
        SqlCommand cmd = conn.CreateCommand();
        cmd.CommandText = "INSERT INTO tb_Review (Rname, Uname, Content, Date, Rate)VALUES ('" + RestaurantName + "', '" + Session["Call"] + "', '" + content + "', '" + date + "','"+rate+"')";
        cmd.ExecuteNonQuery();

        //Complete Insertion then update new rate
        string conString2 = "Data Source=(LocalDB)\\v11.0;AttachDbFilename=|DataDirectory|\\Yummy.mdf;Integrated Security=True";
        string selectString = "SELECT * FROM tb_Review WHERE Rname='" + RestaurantName + "'";//select string for search currency name correspond with the input
        SqlDataSource dsrc = new SqlDataSource(conString2, selectString);
        DataView DV = (DataView)dsrc.Select(DataSourceSelectArguments.Empty);
        int rowsCount=DV.Table.Rows.Count;
        float Ratesum=0;
        float newRate = 0;
        if (rowsCount > 0)
        {
            for(int i=0;i<rowsCount;i++)
                Ratesum+=float.Parse(DV.Table.Rows[i][5].ToString());
            newRate = Ratesum / rowsCount;
            cmd.CommandText = "Update tb_Restaurant set Rate='"+newRate+"',Ratenum='"+rowsCount+"' where Name='"+RestaurantName+"'";
            cmd.ExecuteNonQuery();
        }
        conn.Close();
        TextBox1.Visible = false;
        Button1.Visible = false;

        Session["Restaurantname"] = RestaurantName;
        Session["Username"] = UserName;
        Server.Transfer("Restaurant.aspx");
        //Response.Redirect(Request.Url.ToString());
        //string selectString = "INSERT INTO tb_Review (Rname, Uname, Content, Date)VALUES ('RestaurantName', 'Username', 'content', 'date')";//select string for search currency name correspond with the input
        //SqlDataSource dsrc = new SqlDataSource(conString, selectString);
    }

    public ronUtil(int id)
    {
        DaysAvailable = getDaysAvailable(id);

        SqlDataSource SqlDataSource2 = new SqlDataSource();
        SqlDataSource2.ConnectionString = ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString();

        SqlDataSource2.SelectCommand = "Select * From AdvisorSchedule Where Advisor_Id='" + id.ToString() + "'";
        DataView view = (DataView)SqlDataSource2.Select(DataSourceSelectArguments.Empty);
        DataTable table = view.ToTable();

        DayStart = table.Rows[0][2].ToString();
        DayFinish = table.Rows[0][3].ToString();
        StartDate = table.Rows[0][4].ToString();
        FinishDate = table.Rows[0][5].ToString();
        SqlDataSource SqlDataSource3 = new SqlDataSource();
        SqlDataSource3.ConnectionString = ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString();
        SqlDataSource3.SelectCommand = "Select * From Advisor_Master Where Advisor_Id='" + id.ToString() + "'";

        DataView view3 = (DataView)SqlDataSource3.Select(DataSourceSelectArguments.Empty);
        DataTable table3 = view3.ToTable();

        FullName = table3.Rows[0][1].ToString() + " " + table3.Rows[0][2].ToString();
    }

    //For Calendar ------------------------------------
    public string[] getDaysAvailable(int id)
    {
        SqlDataSource SqlDataSource2 = new SqlDataSource();
        SqlDataSource2.ConnectionString = ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString();

        SqlDataSource2.SelectCommand = "Select Distinct(DayAvailable) From Availability Where Employee_Id='" + id + "'";
        DataView view = (DataView)SqlDataSource2.Select(DataSourceSelectArguments.Empty);
        DataTable table = view.ToTable();

        string[] days = new string[table.Rows.Count];
        for (int ii = 0; ii < table.Rows.Count; ii++)
        { days[ii] = table.Rows[ii][0].ToString().Trim();

        }
        return days;
    }

    //-------------------------------------Get Advisors By Date -----------------------------------------------------------
    public string[] getAdvisorNames(DateTime date)
    {
        SqlDataSource SqlDataSource2 = new SqlDataSource();
        SqlDataSource2.ConnectionString = ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString();

        SqlDataSource2.SelectCommand = "Select Dept_Name,(Last_Name+', ' + First_Name) As Advisors From AdvisorSchedule,Advisor_Master,Department_Master Where AdvisorSchedule.Advisor_Id=Advisor_Master.Advisor_Id AND Advisor_Master.Dept_Id=Department_Master.Dept_Id AND StartDate < '" + date.ToShortDateString() + "' AND FinishDate > '" + date.ToShortDateString() + "' AND DaysAvailable Like '%" + date.DayOfWeek.ToString() + "%'";
        DataView view = (DataView)SqlDataSource2.Select(DataSourceSelectArguments.Empty);
        DataTable table = view.ToTable();

        string[] advisorNames = new string[table.Rows.Count];
        for (int i = 0; i < table.Rows.Count; i++)
        {
            advisorNames[i] = table.Rows[i][0].ToString();
        }

        return advisorNames;
        //Label1.Text = Session["date"].ToString();
    }

    public bool getCheck(string stID)
    {
        int Student_Id = Convert.ToInt32(stID);
        DateTime date = DateTime.Now;
        SqlDataSource SqlDataSource3 = new SqlDataSource();
        SqlDataSource3.ConnectionString = ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString();
        SqlDataSource3.SelectCommand = "Select Time From Scheduling  Where Student_Id='" + Student_Id + "' AND Date>'" + date + "'";

        DataView view = (DataView)SqlDataSource3.Select(DataSourceSelectArguments.Empty);
        DataTable table = view.ToTable();

        //   string[] slotsTaken = new string[table.Rows.Count];
        bool check=false;
        if (table.Rows.Count==0)
        {check=true;}

        return check;
    }

    private void executeQuery()
    {
        x = new SqlDataSource();
        x.ConnectionString = @"Data Source=iii0.database.windows.net;Initial Catalog=prjTRK;Persist Security Info=True;User ID=iii;[email protected]";
        x.SelectCommand = "Select *,convert(varchar(100),startDate,111) as SDate,convert(varchar(100),endDate,111) as EDate from Promotions where activityID ='" + activity.Text + "'";
        DataView dv = x.Select(DataSourceSelectArguments.Empty) as DataView;

        //查完一定只有一筆, 所以Rows[0]選第一筆資料即可
        titleInput.Text = dv.Table.Rows[0]["title"].ToString();
        startDate.Text = dv.Table.Rows[0]["SDate"].ToString();
        endDate.Text = dv.Table.Rows[0]["EDate"].ToString();
        txtNewsContent.Text = dv.Table.Rows[0]["content"].ToString();
        txtRemark.Text = dv.Table.Rows[0]["comment"].ToString();
        picUP.ImageUrl = "~/pic/" + dv.Table.Rows[0]["picPath"].ToString();
    }

    public int[] getAdvisorId(DateTime date)
    {
        string container;
        SqlDataSource SqlDataSource2 = new SqlDataSource();
        SqlDataSource2.ConnectionString = ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString();

        SqlDataSource2.SelectCommand = "Select AdvisorSchedule.Advisor_Id From AdvisorSchedule,Advisor_Master Where AdvisorSchedule.Advisor_Id=Advisor_Master.Advisor_Id AND StartDate < '" + date.ToShortDateString() + "' AND FinishDate > '" + date.ToShortDateString() + "' AND DaysAvailable Like '%" + date.DayOfWeek.ToString() + "%'";
        DataView view = (DataView)SqlDataSource2.Select(DataSourceSelectArguments.Empty);
        DataTable table = view.ToTable();

        int[] advisorId = new int[table.Rows.Count];
        for (int i = 0; i < table.Rows.Count; i++)
        {
            container = table.Rows[i][0].ToString();
            advisorId[i] = Convert.ToInt16(container);
        }

        return advisorId;
        //Label1.Text = Session["date"].ToString();
    }