C# SQLDB.Select方法代码示例

 //查詢標準型態資料
 protected void lnkbtn_calStandard_Click(object sender, EventArgs e)
 {
     string vd = ddlst_Device.SelectedValue;
     SQLDB _operator = new SQLDB("VD_STANDARD", "KPT");
     DataSet vdStandard = _operator.Select("Vdid = '" + vd + "' AND Vsrdir = '" + ddlst_Vsrdir.SelectedValue + "'", "Vdid , TypeName,Week,Hours", "VD_STANDARD");
     RunChart(vdStandard);
 }

 protected override void InitData()
 {
     //BindDropDownListData(ddl_department, (DataSet)Application["App_Systm_Department"], "DepartmentName", "DepartmentID");
     SQLDB db = new SQLDB();
     DataSet ds = db.Select("", "", "role");
     this.gv.DataSource = ds;
     this.gv.DataBind();
 }

 protected void SearchData()
 {
     SQLDB _operator = new SQLDB("DeviceKind");
     string conditionName = ddlst_SearchType.SelectedValue;
     string condition = (txt_Query_Reason.Text.Trim().Length > 0 ? " " + conditionName + " like '%" + txt_Query_Reason.Text.Trim() + "%' " : "");
     //condition += query_company;
     DataSet ds = _operator.Select(condition);
     Session["DS_MIS"] = ds;
     gv.DataSource = ds;
     gv.DataBind();
     if (ds.Tables[0].Rows.Count == 0)
     {
         ShowMsg2(UpdatePanel1, "查詢無資料");
     }
 }

 private bool LoadData()
 {
     bool suc = false;
     SQLDB db = new SQLDB("CD_MaterialOfDevice");
     DataSet ds = new DataSet();
     ds = db.Select("Device_ID = '"+hidden_DeviceID.Value+"'","","CD_MaterialOfDevice");
     if (ds.Tables[0].Rows.Count > 0)
     {
         suc = true;
         gv_Material.DataSource = ds;
         gv_Material.DataBind();
         Session["MIS_Material"] = ds;
     }
     return suc;
 }

    protected void File_Upload(object sender, AjaxControlToolkit.AjaxFileUploadEventArgs e)
    {

        string filename = e.FileName;
        string strDestPath = Server.MapPath("../Temp/");
        //將資料存到網站
        AjaxFileUpload1.SaveAs(@strDestPath + filename);
        //將資料存到DB
        SQLDB _operator = new SQLDB();
        DataSet ds = _operator.Select("1=0", "", "StudyFileUpload");
        DataRow dr = ds.Tables[0].NewRow();
        dr["FileName"] =  filename;
        dr["UpdateTime"] = DateTime.Now;
        ds.Tables[0].Rows.Add(dr);
        DataSet DSChange = ds.GetChanges();
        _operator.Insert(DSChange);
    }

    //修改模式載入資料
    private bool LoadData()
    {
        bool suc = false;

        SQLDB _operator = new SQLDB();
        if (hidden_Caseid.Value != null )
        {
            DataSet ds = new DataSet();
            ds = _operator.Select("caseid = '" + hidden_Caseid.Value + "'", "", "WarrantyNotify");
            if (ds.Tables[0].Rows.Count > 0)
            {
                suc = true;
                DataRow dr = ds.Tables[0].Rows[0];
                DropDownList_DeviceID.SelectedValue = dr["DeviceID"].ToString();
                TextBox_DeviceID_add.Text = dr["DeviceID"].ToString();
                //找出保固廠商的廠商編號，因為資料庫只有紀錄廠商名稱
                if (dr["WarrantyCompany"].ToString().Length > 0)
                {
                    DataSet ds_company = (DataSet)Application["App_Mis_Company"];
                    DataRow[] dr_company = ds_company.Tables[0].Select("CompanyName = '" + dr["WarrantyCompany"].ToString() + "'");
                    DropDownList_WarrantyCompany_add.SelectedValue = dr_company[0]["CompanyID"].ToString();
                }
                DropDownList_WarrantyContract_add.SelectedValue = dr["WarrantyContract"].ToString();
                DropDownList_FaultModel_add.SelectedValue = dr["FaultModel"].ToString();
                TextBox_FaultDescribe_add.Text = dr["FaultDescribe"].ToString();
                DropDownList_ContractCombineNum.SelectedValue = dr["ContractCombineNum"].ToString();
                if (dr["NotifyDate"].ToString().Length > 0)
                    TextBox_NotifyDate_add.Text = Convert.ToDateTime(dr["NotifyDate"].ToString()).ToString("yyyy-MM-dd HH:mm:ss");
                if (dr["RepairDeadline"].ToString().Length > 0)
                {
                    TextBox_RepairDeadline_add.Text = Convert.ToDateTime(dr["RepairDeadline"].ToString()).ToString("yyyy-MM-dd HH:mm:ss");
                }
                DropDownList_RepairDateOption_add.SelectedValue = dr["RepairDateOption"].ToString();
                //併案編號
                DropDownList_ContractCombineNum.SelectedValue = dr["ContractCombineNum"].ToString();
                //重複通知說明
                TextBox_RepeatNotify_add.Text = dr["RepeatNotify"].ToString();

            }
        }
        return suc;
    }

    protected void SearchData()
    {
        SQLDB _operator = new SQLDB("VD_DOORSILL", "KPT");
        string conditionName = ddlst_SearchType.SelectedValue;
        string condition = (txt_Query_Reason.Text.Trim().Length > 0 ? " " + conditionName + " like '%" + txt_Query_Reason.Text.Trim() + "%' " : "");
        //condition += query_company;
        DataSet ds = _operator.Select(condition);

        ////LINQ 測試
        //DataSet ds_std = _operator.Select("", "", "VD_DOORSILL");
        //DataTable dt = ds_std.Tables[0];
        //var std = (
        //    from std_table in dt.AsEnumerable()
        //    where std_table.Field<string>("DeviceID").ToString().Equals("V012441")
        //    select std_table);

        //std.Single().SetField("DeviceID", "test");

        //if (std.Any())
        //{
        //    DataTable dt_door = std.CopyToDataTable();
        //    gv.DataSource = dt_door;
        //    gv.DataBind();
        //}
        //else
        //{
        //    ShowMsg2(UpdatePanel1, "查詢無資料");
        //}

        Session["DS_MIS"] = ds;
        if (ds.Tables[0].Rows.Count > 0)
        {
            gv.DataSource = ds;
            gv.DataBind();
        }
        else
        {
            ShowMsg2(UpdatePanel1, "查詢無資料");
        }

    }

    protected void SearchData()
    {
        SQLDB _operator = new SQLDB("VD_ERRORRECORD", "KPT");
        string conditionName = ddlst_SearchType.SelectedValue;
        StringBuilder condition = new StringBuilder();
        if (conditionName.Equals("RecordTime") || conditionName.Equals("UpdateTime"))
        {
            if (txt_StartDate.Text.Length > 0 && txt_EndDate.Text.Length > 0)
            {
                condition.Append(conditionName + ">  '" + txt_StartDate.Text + "' AND " + conditionName + "< '" + txt_EndDate.Text + "' ");
            }
            else
            {
                if (txt_StartDate.Text.Length > 0)
                {
                    condition.Append(conditionName + "> '" + txt_StartDate.Text + "' ");

                }
                else
                {
                    condition.Append(conditionName + "< '" + txt_EndDate.Text + "' ");
                }
            }
        }
        else
        {
            condition.Append(txt_Query_Reason.Text.Trim().Length > 0 ? " " + conditionName + " like '%" + txt_Query_Reason.Text.Trim() + "%'  " : "");
        }
        DataSet ds = _operator.Select(condition.ToString());
        Session["DS_MIS"] = ds;
        gv.DataSource = ds;
        gv.DataBind();
        if (ds.Tables[0].Rows.Count == 0)
        {
            ScriptManager.RegisterStartupScript(this, this.GetType(), "", "alert(\"查詢無資料\");", true);
            //ShowMsg2(UpdatePanel1, "查詢無資料");
        }
    }

    protected override void InitData()
    {
        SQLDB companyDB = new SQLDB("Company");
        DataSet companyDS = companyDB.Select("1=1");
        DataTable companyDT = companyDS.Tables[0];
        foreach (DataRow companyData in companyDT.Rows) {
            ListItem company = new ListItem();
            company.Value = companyData["CompanyID"].ToString();
            company.Text = companyData["CompanyName"].ToString();
            ddl_company.Items.Add(company);
        }

    }

    protected void btn_StockOutSave_Click(object sender, EventArgs e)
    {
        SQLDB db = new SQLDB("ICS_StockOut_Item");
        if (ddl_MateriName.SelectedIndex == 0)
        {
            ShowPageMsg("請選擇物料名稱");
            return;
        }
        if (txt_ConsumeQuantity.Text.Length  == 0)
        {
            ShowPageMsg("請輸入領用數量");
            return;
        }
        //載入即時料價資料與庫存量資料
        DataSet ds_ICSInventory = new DataSet();
        ds_ICSInventory = db.Select("Materialid = '" + ddl_MateriName.SelectedValue + "'", "", "View_ICS_Inventory");
        DataSet ds = new DataSet();
        DataTable dt = new DataTable();

        if (gv_stockOut_Item.Rows.Count > 0)
        {
            ds = (DataSet)Session["ICS_StockOut_Item"];
        }
        else
        {
            ds = db.Select("1 =0", "", "ICS_StockOut_Item");
            //加入流水號欄位，用在刪除gridview項目用的
            ds.Tables[0].Columns.Add("rowindex");
            ds.Tables[0].Columns.Add("Quantity");
        }
        DataRow dr = ds.Tables[0].NewRow();
        dr["MaterialName"] = ddl_MateriName.SelectedItem.Text;
        dr["MaterialID"] = ddl_MateriName.SelectedValue;
        dr["ConsumeQuantity"] = Convert.ToInt32(txt_ConsumeQuantity.Text);
        //塞入料價與庫存數量
        if (ds_ICSInventory.Tables[0].Rows.Count > 0)
        {
            DataRow dr_inventory = ds_ICSInventory.Tables[0].Rows[0];
            double materialcost = Convert.ToDouble(dr_inventory["MaterialCost"]);
            dr["StockOutCost"] = (materialcost * Convert.ToDouble(txt_ConsumeQuantity.Text)).ToString("0.000");
            dr["Quantity"] = dr_inventory["Quantity"].ToString().Split('.')[0];
            dr["MaterialCost"] = Convert.ToDouble(dr_inventory["MaterialCost"]).ToString("0.000");
        }
        ds.Tables[0].Rows.Add(dr);
        //塞流水號
        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
        {
            ds.Tables[0].Rows[i]["rowindex"] = i + 1;
        }
        gv_stockOut_Item.DataSource = ds;
        gv_stockOut_Item.DataBind();
        Session["ICS_StockOut_Item"] = ds;
    }

    protected void btn_saveAll_Click(object sender, EventArgs e)
    {
        DataSet ds_StockOut_Item = (DataSet)Session["ICS_StockOut_Item"];
        #region 驗證入庫物料與計畫編號是否有輸入資料
        if (ds_StockOut_Item == null || ddl_ProjectName.SelectedIndex == 0)
        {
            ShowMsg2(UpdatePanel1, "請輸入資料");
            return;
        }
        #endregion

        if (hidden_Action.Value == "add")
        {
           

            string userid = (string)Session["UserID"];
            string stockoutid = "";
            SQLDB db = new SQLDB();
            DateTime now = DateTime.Now;
            string Stockout_date = now.ToString("yyyy/MM/dd HH:mm:ss");

            #region 新增出庫單
            DataSet ds_stockOut_record = db.Select(" 1=0", "", "ICS_StockOut_Record");
            DataRow dr_record = ds_stockOut_record.Tables[0].NewRow();
            if (ddl_WarrantyNotify.SelectedIndex != 0)
            {
                dr_record["CaseID"] = ddl_WarrantyNotify.SelectedValue;
            }
            dr_record["ProjectID"] = ddl_ProjectName.SelectedValue;
            dr_record["ProjectName"] = ddl_ProjectName.SelectedItem.Text;
            if (ddl_Region.SelectedIndex != 0)
            {
                dr_record["RegionID"] = ddl_Region.SelectedValue;
            }
            dr_record["StockOutDate"] = txt_StockOutTime.Text;
            dr_record["StockOutUser"] = userid;
            dr_record["IsOut"] = false;
            dr_record["UpdateTime"] = Stockout_date;
            ds_stockOut_record.Tables[0].Rows.Add(dr_record);
            DataSet DSChange = ds_stockOut_record.GetChanges();
            if (!db.Insert(DSChange))
            {
                ShowMsg(UpdatePanel1, "新增出庫單失敗");
                return;
            }
            #endregion

            #region 新增出庫物料
            //將出庫單時間紀錄起來，之後利用出庫單時間與登入者帳號抓取入庫單編號
            DataSet ds = db.Select("UpdateTime ='" + Stockout_date + "'and StockOutUser ='" + userid + "' and ProjectID = '" + ddl_ProjectName.SelectedValue + "' ", "", "ICS_StockOut_Record");
            if (ds.Tables[0].Rows.Count > 0)
            {
                //抓到入庫單編號
                stockoutid = ds.Tables[0].Rows[0]["StockOutID"].ToString();

                //將入庫單編號塞到StockOutItem裡
                for (int i = 0; i < ds_StockOut_Item.Tables[0].Rows.Count; i++)
                {
                    DataRow dr = ds_StockOut_Item.Tables[0].Rows[i];
                    dr["StockOutID"] = stockoutid;
                    dr["UpdateTime"] = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
                    dr["UpdateUser"] = userid;
                }

                //刪除rowindex欄位
                ds_StockOut_Item.Tables[0].Columns.Remove("rowindex");
                DataSet DSChangeItem = ds_StockOut_Item.GetChanges();
                if (db.Insert(DSChangeItem))
                    ShowMsgAndRedirect(UpdatePanel1, "新增成功", "StockOut_QD.aspx");
                else
                    ShowMsg2(UpdatePanel1, "新增失敗");


            } 
            #endregion
        }
           
    

    }

    protected void gv_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        SQLDB db = new SQLDB();
        if (e.CommandName == "singleMapView")
        {
            int index = Convert.ToInt32(e.CommandArgument);
            GridViewRow row = gv.Rows[index];
            string id = row.Cells[2].Text;
            Response.Redirect("mapViewer.aspx?type=Device&id=" + id);
        }

        if (e.CommandName == "Search")
        {
            int index = Convert.ToInt32(e.CommandArgument);
            DataSet DS = db.Select("device_id = '" + gv.Rows[index].Cells[2].Text.Replace(" ", "") + "'", " CheckDate desc", "CD_DeviceCheckDate");
            //hid_deviceid.Value = gv2.Rows[index].Cells[0].Text.Replace(" ", "");
            if (DS.Tables[0].Rows.Count > 0)
            {
                Session["MIS_DeviceHeckDate"] = DS;
                gv3.DataSource = DS;
                gv3.DataBind();
            }
            else
            {
                gv3.DataSource = null;
                gv3.DataBind();
                ShowMsg2(UpdatePanel2, "無資料");
            }
        }
    }

 //取得案件通報資料
 public static DataSet GetWarrantyNotify()
 {
     SQLDB db = new SQLDB("WarrantyNotify");
     return db.Select();
 }

    protected void Button1_Click(object sender, EventArgs e)
    {
        
        dataType.Value = Request.QueryString["type"].Trim();
        string table;
        SQLDB db;
        DataSet ds;
        DataTable dt;
        string sqlStr = "";
        if (dataType.Value.Equals("Device"))
        {
            table = "View_ParkingDeviceOnMap";

            if (DropDownList1.SelectedValue != "null")
                sqlStr += "Location LIKE '%%" + DropDownList1.SelectedValue + "%%' ";
   
            db = new SQLDB(table);
            ds = db.Select(sqlStr);
            if (ds.Tables.Count == 1)
            {
                dt = ds.Tables[0];
                JSONWebService jsonString = new JSONWebService();
                jsonData.Value = jsonString.GetJson(dt);
            }
            else
            {
                jsonData.Value = "null";
            }

        }
        else if (dataType.Value.Equals("Case"))
        {
            table = "View_ParkingCaseOnMap";

            if (DropDownList1.SelectedValue != "null")
                sqlStr += "Location LIKE '%%" + DropDownList1.SelectedValue + "%%' ";
            if (TextBox1.Text.Length != 0)
            {
                if (sqlStr.Length != 0)
                    sqlStr += "AND ";
                sqlStr += "RepairDeadline>'" + TextBox1.Text + "' ";
            }
            if (TextBox1.Text.Length != 0)
            {
                if (sqlStr.Length != 0)
                    sqlStr += "AND ";
                sqlStr += "RepairDeadline<'" + TextBox2.Text + "' ";
            }
            db = new SQLDB(table);
            ds = db.Select(sqlStr);
            if (ds.Tables.Count == 1)
            {
                dt = ds.Tables[0];
                JSONWebService jsonString = new JSONWebService();
                jsonData.Value = jsonString.GetJson(dt);
            }
            else
            {
                jsonData.Value = "null";
            }
        }
        
    }

    protected void gv_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        if (e.CommandName == "singleMapView")
        {
            int index = Convert.ToInt32(e.CommandArgument);
            GridViewRow row = gv.Rows[index];
            string id = row.Cells[1].Text;
            Response.Redirect("../MIS/mapViewer.aspx?type=Case&id=" + id);
        }
        //使用零件查詢
        if (e.CommandName == "cmd_materialQuery")
        {
            gv2.DataSource = null;
            gv2.DataBind();
            int index = Convert.ToInt32(e.CommandArgument);
            GridViewRow row = gv.Rows[index];
            string caseid = row.Cells[1].Text;
            
            SQLDB _operator = new SQLDB();
            DataSet ds = _operator.Select("caseid = '" + caseid + "'", "", "ICS_MaterialRepaired");
            if (ds.Tables[0].Rows.Count > 0)
            {
                Session["DS_Material"] = ds;
                gv2.DataSource = ds;
                gv2.DataBind();
            }
            else
            {
                ShowMsg(UpdatePanel1, "無資料");
            }

        }
    }