public static ExecuteDataset ( SqlConnection connection, CommandType commandType, string commandText ) : DataSet | ||
connection | SqlConnection | a valid SqlConnection |
commandType | CommandType | the CommandType (stored procedure, text, etc.) |
commandText | string | the stored procedure name or T-SQL command |
Результат | DataSet |
public static DataSet SelectAllPageWidgets() { return(SqlHelper.ExecuteDataset(conn, CommandType.Text, "Select w.UID, m.Name + ' - ' + w.Name as Name from CMSModuleWidget w join CMSModule m on w.ModuleID = m.UID where TemplateOrContent in (0,2) order by m.name, w.name ")); }
public DataTable DL_SlidersData(ML_Default objML_Default) { return(SqlHelper.ExecuteDataset(con, "FBNPC_Sliders_Info").Tables[0]); }
public DataTable DL_ShowSingleProgramsData(ML_Default objML_Default) { SqlParameter[] par = { new SqlParameter("@ID", objML_Default.ID), }; return(SqlHelper.ExecuteDataset(con, "FBNPC_OurPrograms_Detail", par).Tables[0]); }
public static DataSet getSelectedForManageSessionScreenByType(int intSessionId, int intUrlId, int intType) { return(SqlHelper.ExecuteDataset(strConnection, "WEBSCANNER_payload_getSelectedForManageSessionScreenByType", intSessionId.ToString(), intUrlId.ToString(), intType)); }
public static DataSet getSelectedForManageSessionScreenExcludeFormElement(int intSessionId, int intUrlId) { return(SqlHelper.ExecuteDataset(strConnection, "WEBSCANNER_payload_getSelectedForManageSessionScreenExcludeFormElement", intSessionId.ToString(), intUrlId.ToString())); }
public static DataSet getDetail(int intId) { return(SqlHelper.ExecuteDataset(strConnection, "WEBSCANNER_payload_getDetail", intId.ToString())); }
public static DataSet getAllByTypeAndUrl(int intType, int intUrlId) { return(SqlHelper.ExecuteDataset(strConnection, "WEBSCANNER_payload_getAllByTypeAndUrl", intType, intUrlId)); }
public DataSet GetPatientList(string SearchCriteria) { //USP_PatientManagement_PatientList_Today return(SqlHelper.ExecuteDataset(Base_BLL_CommonSettings.ConnectionString, "[USP_PM_Patient_AutoComplete_GET]", FK_HospitalID, SearchCriteria)); }
protected void Page_Load(object sender, EventArgs e) { Response.Write("test3ed"); Response.End(); // since we are using Crystal to do everything, the web page is built here if (!IsPostBack) { Session["dtPrint"] = null; if (Session["UserName"] == null) { UserPrefix = "NoUser"; } else { UserPrefix = Session["UserName"].ToString().Trim(); } if (Request.QueryString["ShipperNo"] != null) { ShipperNo = Request.QueryString["ShipperNo"].ToString(); // clear out any previous exports first DirectoryInfo OldPages = new DirectoryInfo(Server.MapPath(@"SOEShipper")); // Get a reference to each file in that directory. FileInfo[] AllOldPages = OldPages.GetFiles(); // go through the files foreach (FileInfo OldPage in AllOldPages) { if (OldPage.Name.Substring(0, UserPrefix.Length) == UserPrefix) { OldPage.Delete(); } } // load the crystal report using the path in an app pref record DataSet dsAppPref = new DataSet(); dsAppPref = SqlHelper.ExecuteDataset(connectionString, "[UGEN_SP_Select]", new SqlParameter("@tableName", "AppPref with (NOLOCK)"), new SqlParameter("@displayColumns", "AppOptionValue"), new SqlParameter("@whereCondition", " (ApplicationCd = 'SOE') AND (AppOptionType = 'InvoiceLocation')")); CrystalPath = dsAppPref.Tables[0].Rows[0]["AppOptionValue"].ToString(); Response.Write("Windows Account which runs ASP.NET is: " + Environment.Username); Response.Write("test3"); Response.End(); try { ShipperDoc.Load(CrystalPath + "SOEShipper.rpt", OpenReportMethod.OpenReportByTempCopy); } catch (Exception ex) { throw ex; } // set the database connections for the report SqlConnectionStringBuilder ConnectBuilder = new SqlConnectionStringBuilder(); ConnectBuilder.ConnectionString = connectionString; ConnectionInfo ConnectInfo = new ConnectionInfo(); ConnectInfo.DatabaseName = ConnectBuilder["Initial Catalog"].ToString(); ConnectInfo.UserID = ConnectBuilder["User Id"].ToString(); ConnectInfo.Password = ConnectBuilder["Password"].ToString(); ConnectInfo.ServerName = ConnectBuilder["Data Source"].ToString(); // AutoDataBind="true" foreach (CrystalDecisions.CrystalReports.Engine.Table ShipperTable in ShipperDoc.Database.Tables) { TableLogOnInfo ShipperTableLogonInfo = ShipperTable.LogOnInfo; ShipperTableLogonInfo.ConnectionInfo = ConnectInfo; ShipperTable.ApplyLogOnInfo(ShipperTableLogonInfo); } ShipperDoc.RecordSelectionFormula = "{SOHeaderRel.OrderNo}=" + ShipperNo; // we are ready to go ShipperDoc.Refresh(); // Now set up the export DiskFileDestinationOptions diskOpts = ExportOptions.CreateDiskFileDestinationOptions(); ExportOptions exportOpts = new ExportOptions(); exportOpts.ExportFormatType = ExportFormatType.HTML40; exportOpts.ExportDestinationType = ExportDestinationType.DiskFile; exportOpts.ExportDestinationOptions = diskOpts; HTMLFormatOptions htmlFormatOpts = new HTMLFormatOptions(); htmlFormatOpts.FirstPageNumber = 1; htmlFormatOpts.HTMLEnableSeparatedPages = true; htmlFormatOpts.HTMLHasPageNavigator = false; htmlFormatOpts.HTMLBaseFolderName = Server.MapPath(""); htmlFormatOpts.HTMLFileName = UserPrefix + "ShipperExport.htm"; exportOpts.ExportFormatOptions = htmlFormatOpts; ShipperDoc.Export(exportOpts); ShipperDoc.Close(); // now that we have created the individual pages, make one big page DirectoryInfo PageFiles = new DirectoryInfo(Server.MapPath(@"SOEShipper")); // Get a reference to each file in that directory. FileInfo[] AllPages = PageFiles.GetFiles(); // start the HTML page Response.Write("<head>\n"); // write the styles Response.Write("<style type=\"text/css\">\n"); Response.Write(".barcode{height:30px;font-size:15pt;color:#000000;font-family:IDAutomationC39M;font-weight:normal;overflow:hidden;}\n"); Response.Write("</style>\n"); // finish the header start the body Response.Write("\n<title>Shipper Document</title>\n</head>\n<body LEFTMARGIN=31 TOPMARGIN=31 >\n"); PagePosition = (decimal)0.0; foreach (FileInfo InvPage in AllPages) { // get only the files for the current user if (InvPage.Name.Substring(0, UserPrefix.Length) == UserPrefix) { WriteOK = false; Response.Write("<DIV style=\"position:absolute; left:0px; top:" + PagePosition.ToString() + "pt; height:590px \">\n"); //Response.Write(""); using (StreamReader sr = new StreamReader(InvPage.FullName)) { String line; // Read and display lines from the file until the end of // the file is reached. while ((line = sr.ReadLine()) != null) { if (line.Contains("</BODY>")) { WriteOK = false; } if (line.Contains("<style>")) { WriteOK = true; } if (line.Contains("images/")) { line = line.Replace("images/", ConfigurationManager.AppSettings["SOESiteURL"].ToString() + "SOEShipper/images/"); } // write the line if (WriteOK) { Response.Write(line + "\n"); } if (line.Contains("</style>")) { WriteOK = false; } if (line.Contains("<BODY")) { WriteOK = true; } } } Response.Write("</DIV>\n"); if (PagePosition == (decimal)0.0) { PagePosition += (decimal)590; } } } Response.Write("</body>\n</html>\n"); } } }
public DataSet ShowFunctionalArea() { ds = new DataSet(); ds = SqlHelper.ExecuteDataset(con, CommandType.StoredProcedure, "Sp_Show_FunctionalArea"); return(ds); }
public DataSet GetPatientList() { //USP_PatientManagement_PatientList_Today return(SqlHelper.ExecuteDataset(Base_BLL_CommonSettings.ConnectionString, "[USP_PM_Patient_List]", FK_HospitalID)); }
public static DataSet GetDatosParaValidacion() { return(SqlHelper.ExecuteDataset("SUP_DATOSVALIDACIONCONSUCONTA")); }
private void GenMaVachTuDong() { string sql = String.Empty; DataSet dsKho = null; DataSet dsTonDauKy = null; DataSet dsHangHoaChiTiet = null; DMKhoInfo dmKho; try { ConnectionUtil.Instance.BeginTransaction(); sql = "Select distinct MaKho from tbl_tmp_mavach_chuan_t3 order by MaKho"; dsKho = SqlHelper.ExecuteDataset(ConnectionUtil.Instance.GetConnection(), CommandType.Text, sql); sql = "Select MaKho, MaSanPham, SUM(SoLuong) as SoLuong from tbl_tmp_mavach_chuan_t3 group by MaKho, MaSanPham order by MaKho"; dsTonDauKy = SqlHelper.ExecuteDataset(ConnectionUtil.Instance.GetConnection(), CommandType.Text, sql); //frmProgress.Instance.Description = "Đang xóa dữ liệu kho..."; //frmProgress.Instance.MaxValue = dsKho.Tables[0].Rows.Count; //frmProgress.Instance.Value = 0; //foreach (DataRow dataRow in dsKho.Tables[0].Rows) //{ // dmKho = DMKhoDataProvider.Instance.GetKhoInfoByCode(Convert.ToString(dataRow["MaKho"]), 0); // frmProgress.Instance.Description =dmKho.MaKho+ ":Đang xóa dữ liệu kho..."; // frmProgress.Instance.Value += 1; // if (dmKho != null) // { // sql = "select * from tbl_hanghoa_dudauky_t2 where idkho=" + dmKho.IdKho; // object dado = SqlHelper.ExecuteScalar(ConnectionUtil.Instance.GetConnection(), CommandType.Text, sql); // //if(dado != null) throw new Exception("Kho này đã đổ tồn."); // //KhoThongKeHangTonDataProvider.Instance.DeleteData(dmKho.IdKho, 0); // } //} frmProgress.Instance.MaxValue = dsTonDauKy.Tables[0].Rows.Count; frmProgress.Instance.Value = 0; foreach (DataRow dataRow in dsTonDauKy.Tables[0].Rows) { dmKho = DMKhoDataProvider.Instance.GetKhoInfoByCode(Convert.ToString(dataRow["MaKho"]), 0); if (dmKho == null) { continue; } frmProgress.Instance.Description = dmKho.MaKho + ":Đang cập nhật tồn đầu kỳ ..."; //int soluong = (Convert.ToInt32(dataRow["SoLuong"]) < 100 ? 100 : Convert.ToInt32(dataRow["SoLuong"])); int soluong = Convert.ToInt32(dataRow["SoLuong"]); int idKho = dmKho.IdKho; int idSanPham = DmSanPhamProvider.GetSanPhamBriefByMa(Convert.ToString(dataRow["MaSanPham"])).IdSanPham; frmProgress.Instance.Value += 1; //sql = "delete tbl_hanghoa_dudauky_t2 where idkho=" + // idKho + " and idsanpham=" + // idSanPham; //SqlHelper.ExecuteNonQuery(ConnectionUtil.Instance.GetConnection(), CommandType.Text, sql); //sql = "insert into tbl_hanghoa_dudauky_t2 (idkho, idsanpham, soluong, thoigian) values(" + // idKho + "," + // idSanPham + "," + // soluong + ", "+ // "to_date('28/02/2013','dd/mm/yyyy')"+ //")"; SqlHelper.ExecuteNonQuery(ConnectionUtil.Instance.GetConnection(), CommandType.Text, sql); //sql = "delete tbl_hangtonkho_t2 where idkho=" + // idKho + " and idsanpham=" + // idSanPham; //SqlHelper.ExecuteNonQuery(ConnectionUtil.Instance.GetConnection(), CommandType.Text, sql); //sql = "insert into tbl_hangtonkho_t2 (idkho, idsanpham, soluong, tonao) values(" + // idKho + "," + // idSanPham + "," + // soluong + "," + // soluong + // ")"; SqlHelper.ExecuteNonQuery(ConnectionUtil.Instance.GetConnection(), CommandType.Text, sql); sql = "select iddudauky from tbl_hanghoa_dudauky_t2 where idsanpham= " + idSanPham + "and idkho=" + idKho; int idDuDauKy = Convert.ToInt32(SqlHelper.ExecuteScalar(ConnectionUtil.Instance.GetConnection(), CommandType.Text, sql)); //sql = "delete tbl_hanghoa_chitiet_t2 where idkho=" + // idKho + " and idsanpham=" + // idSanPham; //SqlHelper.ExecuteNonQuery(ConnectionUtil.Instance.GetConnection(), CommandType.Text, sql); sql = String.Format( "Select MaKho, MaSanPham, Serial, SoLuong from tbl_tmp_mavach_chuan_t2 where MaKho='{0}' and MaSanPham='{1}'", dataRow["MaKho"], dataRow["MaSanPham"]); dsHangHoaChiTiet = SqlHelper.ExecuteDataset(ConnectionUtil.Instance.GetConnection(), CommandType.Text, sql); frmProgress.Instance.PushStatus(); frmProgress.Instance.MaxValue = dsHangHoaChiTiet.Tables[0].Rows.Count; frmProgress.Instance.Value = 0; foreach (DataRow drHangHoaChiTiet in dsHangHoaChiTiet.Tables[0].Rows) { frmProgress.Instance.Description = dmKho.MaKho + ":Đang cập nhật tồn mã vạch ..."; sql = "select sp.trungmavach from tbl_sanpham sp where sp.idsanpham =" + idSanPham; int trungMaVach = Convert.ToInt32(SqlHelper.ExecuteScalar(ConnectionUtil.Instance.GetConnection(), CommandType.Text, sql)); if (trungMaVach == 0) { sql = "select 1 from tbl_hanghoa_chitiet_t2 hhct "; sql += "where hhct.mavach='" + drHangHoaChiTiet["Serial"] + "'"; object exists = SqlHelper.ExecuteScalar(ConnectionUtil.Instance.GetConnection(), CommandType.Text, sql); if (exists != null) { throw new Exception("Trùng mã vạch"); } } sql = "insert into tbl_hanghoa_chitiet_t2 (iddudauky, idkho, idsanpham, mavach, soluong) values(" + idDuDauKy + "," + idKho + "," + idSanPham + ",'" + drHangHoaChiTiet["Serial"] + "'," + //"to_date('" + BaoHanhHangTu.ToString("dd/MM/yyyy") + "','dd/mm/yyyy')," + //"to_date('" + BaoHanhHangTu.AddMonths(ThoiHanBaoHanh).ToString("dd/MM/yyyy") + "','dd/mm/yyyy')," + drHangHoaChiTiet["SoLuong"] + ")"; SqlHelper.ExecuteNonQuery(ConnectionUtil.Instance.GetConnection(), CommandType.Text, sql); frmProgress.Instance.Value += 1; } frmProgress.Instance.PopStatus(); } ConnectionUtil.Instance.CommitTransaction(); frmProgress.Instance.Description = "Đã hoàn thành"; frmProgress.Instance.Value = frmProgress.Instance.MaxValue; frmProgress.Instance.IsCompleted = true; } catch (Exception exception) { ConnectionUtil.Instance.RollbackTransaction(); Debug.Print(exception.ToString()); Debug.Print(sql); frmProgress.Instance.Description = "Không hoàn thành"; frmProgress.Instance.Value = frmProgress.Instance.MaxValue; frmProgress.Instance.IsCompleted = true; #if DEBUG MessageBox.Show(exception.ToString()); #else MessageBox.Show(exception.Message); #endif return; } }
//ConfigurationManager.ConnectionStrings["CMSConn"].ToString(); public static DataSet SelectAllPages() { return(SqlHelper.ExecuteDataset(conn, CommandType.Text, "Select * from CMSPage")); }
/// <summary> /// Cari bulma fonksiyonu /// </summary> /// <param name="cari"></param> /// <returns></returns> public List <cari_kart> DepoBul(long carikart_id = 0, string cari_unvan = "", string ozel_kod = "", byte carikart_tipi_id = 0) { short parameterControl = 0; #region Query string orStatement = ""; if (carikart_id > 0) { parameterControl++; orStatement += "c.carikart_id = @carikart_id OR "; } if (!string.IsNullOrEmpty(cari_unvan.TrimEnd())) { parameterControl++; orStatement += "c.cari_unvan like @unvan OR "; } if (!string.IsNullOrEmpty(ozel_kod.TrimEnd())) { parameterControl++; orStatement += "c.ozel_kod like @ozelkod OR "; } if (carikart_tipi_id > 0) { parameterControl++; orStatement += "c.carikart_tipi_id = @carikart_tipi_id OR "; } if (!string.IsNullOrEmpty(orStatement)) { orStatement = "(" + orStatement.TrimEnd(new char[] { 'O', 'R', ' ' }) + ")"; } orStatement += ""; string query = @" SELECT c.carikart_id,cari_unvan,ozel_kod,giz_yazilim_kodu,c.statu, ct.carikart_tipi_id,ct.carikart_tipi_adi,ctur.carikart_turu_id ,ctur.carikart_turu_adi,c.fiyattipi, ca.adres,ca.tel1,ca.email,ca.websitesi,f.pb,ana_carikart_id,cf.odeme_tipi, o.satin_alma_sorumlu_carikart_id,o.satis_sorumlu_carikart_id, cf.finans_sorumlu_carikart_id, (CASE WHEN f.finans_sorumlu_carikart_id > 0 THEN (SELECT cari_unvan FROM carikart WHERE carikart_id = f.finans_sorumlu_carikart_id) END) as 'finans_sorumlu_cari_unvan', (CASE WHEN f.ilgili_sube_carikart_id > 0 THEN (SELECT cari_unvan FROM carikart WHERE carikart_id = f.ilgili_sube_carikart_id) END) as 'ilgili_sube_cari_unvan', (CASE WHEN o.satin_alma_sorumlu_carikart_id > 0 THEN (SELECT cari_unvan FROM carikart WHERE carikart_id = o.satin_alma_sorumlu_carikart_id) END) as 'satin_alma_sorumlu_cari_unvan', (CASE WHEN o.satis_sorumlu_carikart_id > 0 THEN (SELECT cari_unvan FROM carikart WHERE carikart_id = o.satis_sorumlu_carikart_id) END) as 'satis_sorumlu_cari_unvan', (CASE WHEN c.ana_carikart_id > 0 THEN (SELECT cari_unvan FROM carikart WHERE carikart_id = c.ana_carikart_id) END) as 'ana_cari_unvan' --(CASE WHEN c.ana_carikart_id > 0 THEN (SELECT cari_unvan FROM carikart WHERE ana_carikart_id = c.carikart_id) END) as 'ilgili_sube' FROM carikart c LEFT JOIN carikart_finans f on f.carikart_id=c.carikart_id LEFT JOIN carikart_firma_ozel o on o.carikart_id=c.carikart_id LEFT JOIN giz_sabit_carikart_turu ctur on ctur.carikart_turu_id = c.carikart_turu_id INNER join giz_sabit_carikart_tipi ct on c.carikart_tipi_id=ct.carikart_tipi_id and ct.carikart_tipi_id in(2,3) LEFT JOIN carikart_genel_adres ca on c.carikart_id=ca.carikart_id LEFT JOIN carikart_finans cf on cf.carikart_id=c.carikart_id WHERE " + orStatement + " "; #endregion #region Parameters SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("@carikart_id", carikart_id), new SqlParameter("@unvan", "%" + cari_unvan + "%"), new SqlParameter("@ozelkod", "%" + ozel_kod + "%"), new SqlParameter("@carikart_tipi_id", carikart_tipi_id) }; #endregion if (parameterControl > 0) { dt = SqlHelper.ExecuteDataset(ConnectionStrings.SqlConn, CommandType.Text, query, parameters).Tables[0]; if (dt != null && dt.Rows.Count > 0) { carikartlar = new List <cari_kart>(); for (int i = 0; i < dt.Rows.Count; i++) { carikart = new cari_kart(); carikart.carikart_id = dt.Rows[i]["carikart_id"].acekaToLong(); carikart.statu = dt.Rows[i]["statu"].acekaToBool(); carikart.carikart_turu_id = dt.Rows[i]["carikart_turu_id"].acekaToByte(); carikart.carikart_tipi_id = dt.Rows[i]["carikart_tipi_id"].acekaToByte(); carikart.cari_unvan = dt.Rows[i]["cari_unvan"].ToString(); carikart.ozel_kod = dt.Rows[i]["ozel_kod"].ToString(); carikart.fiyattipi = dt.Rows[i]["fiyattipi"].ToString(); carikart.giz_yazilim_kodu = dt.Rows[i]["giz_yazilim_kodu"].acekaToShort(); carikart.ana_cari_unvan = dt.Rows[i]["ana_cari_unvan"].ToString(); carikart.ana_carikart_id = dt.Rows[i]["ana_carikart_id"].acekaToLong(); carikart.giz_sabit_carikart_turu = new giz_sabit_carikart_turu(); carikart.giz_sabit_carikart_turu.carikart_turu_id = dt.Rows[i]["carikart_turu_id"].acekaToByte(); carikart.giz_sabit_carikart_turu.carikart_turu_adi = dt.Rows[i]["carikart_turu_adi"].ToString(); carikart.giz_sabit_carikart_tipi = new giz_sabit_carikart_tipi(); carikart.giz_sabit_carikart_tipi.carikart_tipi_id = dt.Rows[i]["carikart_tipi_id"].acekaToByte(); carikart.giz_sabit_carikart_tipi.carikart_tipi_adi = dt.Rows[i]["carikart_tipi_adi"].ToString(); carikart.carikart_genel_adres = new List <carikart_genel_adres>(); carikart.carikart_genel_adres.Add(new carikart_genel_adres { adres = dt.Rows[i]["adres"].ToString(), tel1 = dt.Rows[i]["tel1"].ToString(), email = dt.Rows[i]["email"].ToString(), websitesi = dt.Rows[i]["websitesi"].ToString(), }); //Cari finans carikart.carikart_finans = new carikart_finans(); carikart.carikart_finans.finans_sorumlu_carikart_id = dt.Rows[i]["finans_sorumlu_carikart_id"].acekaToLong(); carikart.carikart_finans.finans_sorumlu_cari_unvan = dt.Rows[i]["finans_sorumlu_cari_unvan"].ToString(); carikart.carikart_finans.odeme_tipi = dt.Rows[i]["odeme_tipi"].acekaToByte(); //Cari Kart Firma Özel carikart.carikart_firma_ozel = new carikart_firma_ozel(); carikart.carikart_firma_ozel.ozel = dt.Rows[i]["ozel_kod"].ToString(); carikart.carikart_firma_ozel.carikart_id = dt.Rows[i]["carikart_id"].acekaToLong(); carikart.carikart_firma_ozel.satin_alma_sorumlu_carikart_id = dt.Rows[i]["satin_alma_sorumlu_carikart_id"].acekaToLong(); carikart.carikart_firma_ozel.satin_alma_sorumlu_cari_unvan = dt.Rows[i]["satin_alma_sorumlu_cari_unvan"].ToString(); carikart.carikart_firma_ozel.satis_sorumlu_carikart_id = dt.Rows[i]["satis_sorumlu_carikart_id"].acekaToLong(); carikart.carikart_firma_ozel.satis_sorumlu_cari_unvan = dt.Rows[i]["satis_sorumlu_cari_unvan"].ToString(); // carikart.carikart_finans = new carikart_finans(); //carikart.carikart_finans.pb = dt.Rows[i]["pb"].ToString(); //carikart.carikart_finans.finans_sorumlu_carikart_id = dt.Rows[i]["finans_sorumlu_carikart_id"].acekaToLong(); //carikart.carikart_firma_ozel = new carikart_firma_ozel(); //carikart.carikart_firma_ozel.satin_alma_sorumlu_carikart_id = dt.Rows[i]["satin_alma_sorumlu_carikart_id"].acekaToLong(); //carikart.carikart_firma_ozel.satis_sorumlu_carikart_id = dt.Rows[i]["satis_sorumlu_carikart_id"].acekaToLong(); carikartlar.Add(carikart); carikart = null; //carikart = new cari_kart(); //carikart.carikart_id = dt.Rows[i]["carikart_id"].acekaToLong(); //carikart.statu = dt.Rows[i]["statu"].acekaToBool(); //carikart.carikart_turu_id = dt.Rows[i]["carikart_turu_id"].acekaToByte(); //carikart.carikart_tipi_id = dt.Rows[i]["carikart_tipi_id"].acekaToByte(); //carikart.cari_unvan = dt.Rows[i]["cari_unvan"].ToString(); //carikart.ozel_kod = dt.Rows[i]["ozel_kod"].ToString(); //carikart.fiyattipi = dt.Rows[i]["fiyattipi"].ToString(); //carikart.giz_yazilim_kodu = dt.Rows[i]["giz_yazilim_kodu"].acekaToShort(); //carikart.giz_sabit_carikart_turu = new giz_sabit_carikart_turu(); //carikart.giz_sabit_carikart_turu.carikart_turu_id = dt.Rows[i]["carikart_turu_id"].acekaToByte(); //carikart.giz_sabit_carikart_turu.carikart_turu_adi = dt.Rows[i]["carikart_turu_adi"].ToString(); //carikart.giz_sabit_carikart_tipi = new giz_sabit_carikart_tipi(); //carikart.giz_sabit_carikart_tipi.carikart_tipi_id = dt.Rows[i]["carikart_tipi_id"].acekaToByte(); //carikart.giz_sabit_carikart_tipi.carikart_tipi_adi = dt.Rows[i]["carikart_tipi_adi"].ToString(); //carikart.carikart_finans = new carikart_finans(); //carikart.carikart_finans.pb = dt.Rows[i]["pb"].ToString(); //carikartlar.Add(carikart); //carikart = null; } } } return(carikartlar); }
public static string DeleteReturn(string id) { string returnData = "success"; var returnDetailList = new List <ReturnDetail>(); var dsReturnData = new DataSet(); var returnNo = string.Empty; var refNo = string.Empty; var returnType = string.Empty; using (SqlConnection conn = new SqlConnection(SPlanetUtil.GetConnectionString())) { conn.Open(); using (SqlTransaction tran = conn.BeginTransaction()) { try { //Create array of Parameters List <SqlParameter> arrParm = new List <SqlParameter> { new SqlParameter("@id", SqlDbType.Int) { Value = id }, }; dsReturnData = SqlHelper.ExecuteDataset(tran, "sp_return_list_data", arrParm.ToArray()); if (dsReturnData.Tables.Count > 0) { #region Header var header = (from t in dsReturnData.Tables[0].AsEnumerable() select t).FirstOrDefault(); if (header != null) { refNo = Convert.IsDBNull(header["ref_no"]) ? string.Empty : Convert.ToString(header["ref_no"]); returnNo = Convert.IsDBNull(header["return_no"]) ? string.Empty : Convert.ToString(header["return_no"]); returnType = Convert.IsDBNull(header["return_type"]) ? string.Empty : Convert.ToString(header["return_type"]); } #endregion #region Detail var detail = (from t in dsReturnData.Tables[1].AsEnumerable() select t).ToList(); if (detail != null) { returnDetailList = new List <ReturnDetail>(); foreach (var row in detail) { returnDetailList.Add(new ReturnDetail() { id = Convert.IsDBNull(row["id"]) ? 0 : Convert.ToInt32(row["id"]), ref_id = Convert.IsDBNull(row["ref_id"]) ? 0 : Convert.ToInt32(row["ref_id"]), is_delete = Convert.IsDBNull(row["is_delete"]) ? false : Convert.ToBoolean(row["is_delete"]), product_id = Convert.IsDBNull(row["product_id"]) ? 0 : Convert.ToInt32(row["product_id"]), product_name = Convert.IsDBNull(row["product_name"]) ? string.Empty : Convert.ToString(row["product_name"]), product_no = Convert.IsDBNull(row["product_no"]) ? string.Empty : Convert.ToString(row["product_no"]), product_unit = Convert.IsDBNull(row["product_unit"]) ? string.Empty : Convert.ToString(row["product_unit"]), qty = Convert.IsDBNull(row["qty"]) ? 0 : Convert.ToInt32(row["qty"]), ref_qty = Convert.IsDBNull(row["ref_qty"]) ? 0 : Convert.ToInt32(row["ref_qty"]), return_no = Convert.IsDBNull(row["return_no"]) ? string.Empty : Convert.ToString(row["return_no"]), mfg_no = Convert.IsDBNull(row["mfg_no"]) ? string.Empty : Convert.ToString(row["mfg_no"]), product_type = Convert.IsDBNull(row["product_type"]) ? string.Empty : Convert.ToString(row["product_type"]), old_qty = Convert.IsDBNull(row["old_qty"]) ? 0 : Convert.ToInt32(row["old_qty"]), }); } } #endregion } using (SqlCommand cmd = new SqlCommand("sp_return_header_delete", conn, tran)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@id", SqlDbType.Int).Value = Convert.ToInt32(id); cmd.Parameters.Add("@updated_by", SqlDbType.Int).Value = Convert.ToInt32(ConstantClass.SESSION_USER_ID); cmd.ExecuteNonQuery(); } #region Transection var transNo = string.Empty; using (SqlCommand cmd = new SqlCommand("sp_gen_transection_no", conn, tran)) { cmd.CommandType = CommandType.StoredProcedure; transNo = Convert.ToString(cmd.ExecuteScalar()); } string stock_type = string.Empty; foreach (var row in returnDetailList) { using (SqlCommand cmd = new SqlCommand("sp_stock_movement_add", conn, tran)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@transection_no", SqlDbType.VarChar, 20).Value = transNo; cmd.Parameters.Add("@product_type", SqlDbType.VarChar, 1).Value = row.product_type; cmd.Parameters.Add("@product_id", SqlDbType.Int).Value = row.product_id; cmd.Parameters.Add("@transection_type", SqlDbType.VarChar, 50).Value = "RETURN"; cmd.Parameters.Add("@movement_type", SqlDbType.VarChar, 3).Value = returnType == "IS" ? "OUT" : "IN"; cmd.Parameters.Add("@checking_type", SqlDbType.NVarChar, 1).Value = "W"; cmd.Parameters.Add("@qty", SqlDbType.Int).Value = row.qty; // คืนค่า cmd.Parameters.Add("@stock_balance", SqlDbType.Int).Value = 0; // คำนวนจาก Store cmd.Parameters.Add("@remark", SqlDbType.VarChar, 500).Value = "คืนค่าจาก Return"; //row.remark; cmd.Parameters.Add("@created_by", SqlDbType.Int).Value = Convert.ToInt32(ConstantClass.SESSION_USER_ID); cmd.Parameters.Add("@ref_doc_id", SqlDbType.Int).Value = id; // คำนวนจาก Store cmd.Parameters.Add("@ref_doc_no", SqlDbType.VarChar, 20).Value = returnNo; cmd.ExecuteNonQuery(); } } #endregion #region Detail foreach (var row in returnDetailList) { using (SqlCommand cmd = new SqlCommand("sp_return_detail_edit", conn, tran)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@id", SqlDbType.Int).Value = row.id; cmd.Parameters.Add("@return_no", SqlDbType.VarChar, 20).Value = returnNo; cmd.Parameters.Add("@return_id", SqlDbType.Int).Value = id; cmd.Parameters.Add("@product_id", SqlDbType.Int).Value = row.product_id; cmd.Parameters.Add("@product_no", SqlDbType.VarChar, 20).Value = row.product_no; cmd.Parameters.Add("@product_name", SqlDbType.VarChar, 200).Value = row.product_name; cmd.Parameters.Add("@mfg_no", SqlDbType.VarChar, 20).Value = row.mfg_no; cmd.Parameters.Add("@qty", SqlDbType.Int).Value = row.qty; cmd.Parameters.Add("@product_unit", SqlDbType.VarChar, 5).Value = row.product_unit; cmd.Parameters.Add("@ref_qty", SqlDbType.Int).Value = row.ref_qty; cmd.Parameters.Add("@ref_id", SqlDbType.Int).Value = row.ref_id; cmd.Parameters.Add("@ref_type", SqlDbType.VarChar, 2).Value = returnType; cmd.Parameters.Add("@is_delete", SqlDbType.Bit).Value = 1; cmd.Parameters.Add("@updated_by", SqlDbType.Int).Value = Convert.ToInt32(ConstantClass.SESSION_USER_ID); cmd.ExecuteNonQuery(); } } #endregion if (returnType == "IS") #region Issue Update Status { using (SqlCommand cmd = new SqlCommand("sp_issue_update_status", conn, tran)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@ref_no", SqlDbType.VarChar, 20).Value = returnType; cmd.Parameters.Add("@ref_type", SqlDbType.VarChar, 2).Value = "RE"; cmd.ExecuteNonQuery(); } } #endregion else if (returnType == "BR") { #region Borrow Update Status using (SqlCommand cmd = new SqlCommand("sp_borrow_update_status", conn, tran)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@ref_no", SqlDbType.VarChar, 20).Value = returnType; cmd.Parameters.Add("@ref_type", SqlDbType.VarChar, 2).Value = "RE"; cmd.ExecuteNonQuery(); } #endregion } else if (returnType == "PR") { using (SqlCommand cmd = new SqlCommand("sp_purchase_request_update_status", conn, tran)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@ref_no", SqlDbType.VarChar, 20).Value = returnType; cmd.Parameters.Add("@ref_type", SqlDbType.VarChar, 2).Value = "RE"; cmd.ExecuteNonQuery(); } } } catch (Exception ex) { tran.Rollback(); tran.Dispose(); conn.Close(); //throw ex; return(ex.Message.ToString()); } finally { if (!conn.State.Equals(ConnectionState.Closed)) { tran.Commit(); tran.Dispose(); conn.Close(); } } return(returnData); } } }
public cari_kart Getir(long carikart_id) { #region Query string query = @" --Table[0] SELECT c.carikart_id,c.statu,c.cari_unvan,tur.carikart_turu_adi ,c.transfer_depo_id,c.kayit_silindi,c.ozel_kod, c.giz_yazilim_kodu,ea.earsiv_seri,ef.efatura_seri,m.muh_kod,m.sirket_id,masraf.masraf_merkezi_adi,sy.acilis_tarihi,sy.kapanis_tarihi,sy.kapali, c.ana_carikart_id,c.cari_unvan as baglistokyeri_unvan, --ck.cari_unvan as baglistokyeri_unvan,tip.carikart_tipi_id,tur.carikart_turu_id,masraf.masraf_merkezi_id, sy.transfer_depo_kullan --,c.ana_carikart_id --CK.ana_carikart_id ,gs.sirket_adi,gs.sirket_id, tip.carikart_tipi_adi as stokyeri_tipi_adi, --ana_carikart_id > 0 ise sorguya ana_cari_unvan adında bir alan ekleniyor (CASE WHEN c.ana_carikart_id > 0 THEN (Select cari_unvan from carikart where carikart_id = C.ana_carikart_id) END) as 'ana_cari_unvan',c.carikart_turu_id,c.carikart_tipi_id,masraf.masraf_merkezi_id FROM carikart c --LEFT join carikart ck on ck.ana_carikart_id= c.carikart_id LEFT join carikart_earsiv ea on ea.carikart_id=c.carikart_id INNER join giz_sabit_carikart_tipi tip on tip.carikart_tipi_id=c.carikart_tipi_id --and tip.carikart_tipi_id in(2,3) INNER join giz_sabit_carikart_turu tur on tur.carikart_turu_id=c.carikart_turu_id and tur.carikart_turu_id = 3 LEFT join carikart_efatura ef on ef.carikart_id=c.carikart_id LEFT join carikart_muhasebe m on m.carikart_id=c.carikart_id LEFT join muhasebe_tanim_masrafmerkezleri masraf on masraf.masraf_merkezi_id = m.masraf_merkezi_id LEFT join giz_sirket gs on gs.sirket_id=m.sirket_id LEFT join carikart_stokyeri sy on sy.carikart_id=c.carikart_id WHERE c.carikart_id= @carikart_id --AND C.statu=1 "; #endregion #region Parameters SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("@carikart_id", carikart_id) }; #endregion ds = SqlHelper.ExecuteDataset(ConnectionStrings.SqlConn, CommandType.Text, query, parameters); if (ds != null && ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0) { carikart = new cari_kart(); carikart.carikart_id = ds.Tables[0].Rows[0]["carikart_id"].acekaToLong(); carikart.statu = ds.Tables[0].Rows[0]["statu"].acekaToBool(); carikart.cari_unvan = ds.Tables[0].Rows[0]["cari_unvan"].ToString(); carikart.ozel_kod = ds.Tables[0].Rows[0]["ozel_kod"].ToString(); carikart.ana_carikart_id = ds.Tables[0].Rows[0]["ana_carikart_id"].acekaToLong(); carikart.ana_cari_unvan = ds.Tables[0].Rows[0]["ana_cari_unvan"].ToString(); carikart.giz_sabit_carikart_turu = new giz_sabit_carikart_turu(); carikart.giz_sabit_carikart_turu.carikart_turu_adi = ds.Tables[0].Rows[0]["carikart_turu_adi"].ToString(); carikart.giz_sabit_carikart_turu.carikart_turu_id = ds.Tables[0].Rows[0]["carikart_turu_id"].acekaToByte(); carikart.giz_sabit_carikart_tipi = new giz_sabit_carikart_tipi(); carikart.giz_sabit_carikart_tipi.carikart_tipi_adi = ds.Tables[0].Rows[0]["stokyeri_tipi_adi"].ToString(); carikart.giz_sabit_carikart_tipi.carikart_tipi_id = ds.Tables[0].Rows[0]["carikart_tipi_id"].acekaToByte(); carikart.transfer_depo_id = ds.Tables[0].Rows[0]["transfer_depo_id"].acekaToLong(); carikart.giz_yazilim_kodu = ds.Tables[0].Rows[0]["giz_yazilim_kodu"].acekaToShort(); carikart.carikart_earsiv = new carikart_earsiv(); carikart.carikart_earsiv.earsiv_seri = ds.Tables[0].Rows[0]["earsiv_seri"].ToString(); carikart.carikart_efatura = new carikart_efatura(); carikart.carikart_efatura.efatura_seri = ds.Tables[0].Rows[0]["efatura_seri"].ToString(); carikart.carikart_muhasebe = new carikart_muhasebe(); carikart.carikart_muhasebe.muh_kod = ds.Tables[0].Rows[0]["muh_kod"].ToString(); carikart.carikart_muhasebe.sirket_id = ds.Tables[0].Rows[0]["sirket_id"].acekaToByte(); carikart.muhasebe_tanim_masrafmerkezleri = new muhasebe_tanim_masrafmerkezleri(); carikart.muhasebe_tanim_masrafmerkezleri.masraf_merkezi_adi = ds.Tables[0].Rows[0]["masraf_merkezi_adi"].ToString(); carikart.muhasebe_tanim_masrafmerkezleri.masraf_merkezi_id = ds.Tables[0].Rows[0]["masraf_merkezi_id"].acekaToInt(); carikart.carikart_stokyeri = new carikart_stokyeri(); carikart.carikart_stokyeri.acilis_tarihi = ds.Tables[0].Rows[0]["acilis_tarihi"].acekaToDateTimeWithNullable(); carikart.carikart_stokyeri.kapanis_tarihi = ds.Tables[0].Rows[0]["kapanis_tarihi"].acekaToDateTimeWithNullable(); carikart.carikart_stokyeri.kapali = ds.Tables[0].Rows[0]["kapali"].acekaToBoolWithNullable(); carikart.carikart_stokyeri.transfer_depo_kullan = ds.Tables[0].Rows[0]["transfer_depo_kullan"].acekaToBoolWithNullable(); carikart.giz_sirket = new giz_sirket(); carikart.giz_sirket.sirket_adi = ds.Tables[0].Rows[0]["sirket_adi"].ToString(); carikart.giz_sirket.sirket_id = ds.Tables[0].Rows[0]["sirket_id"].acekaToByte(); } return(carikart); }
public DataSet SelectCandidateByClientIdByAll() { return(SqlHelper.ExecuteDataset(ConnectionString, "USP_Job_SelectCandidateByClientIdByAll", null)); }
public static DataSet getAll() { return(SqlHelper.ExecuteDataset(strConnection, "WEBSCANNER_payload_getAll")); }
public DataSet SelectCandidateByClientId(int clientId) { SqlParameter[] objprmArray = new SqlParameter[1]; objprmArray[0] = new SqlParameter("@ClientId", clientId); return(SqlHelper.ExecuteDataset(ConnectionString, "USP_Job_SelectCandidateByClientId", objprmArray)); }
public static DataSet getAllForManageSessionScreen(int intSessionId, int intUrlId) { return(SqlHelper.ExecuteDataset(strConnection, "WEBSCANNER_payload_getAllForManageSessionScreen", intSessionId.ToString(), intUrlId.ToString())); }
public virtual List <SystemProcess> GetPagedSystemProcess(string orderByClause, int pageSize, int startIndex, out int count, List <SearchColumn> searchColumns, string SelectClause = null) { string whereClause = base.GetAdvancedWhereClauseByColumn(searchColumns, GetSearchColumns()); if (!String.IsNullOrEmpty(orderByClause)) { KeyValuePair <string, string> parsedOrderByClause = base.ParseOrderByClause(orderByClause); orderByClause = base.GetBasicSearchOrderByClauseByColumn(parsedOrderByClause.Key, parsedOrderByClause.Value, this.SearchColumns); } count = GetSystemProcessCount(whereClause, searchColumns); if (count > 0) { if (count < startIndex) { startIndex = (count / pageSize) * pageSize; } int PageLowerBound = startIndex; int PageUpperBound = PageLowerBound + pageSize; string sql = @"CREATE TABLE #PageIndex ( [IndexId] int IDENTITY (1, 1) NOT NULL, [SystemProcessID] int );" ; //Insert into the temp table string tempsql = "INSERT INTO #PageIndex ([SystemProcessID])"; tempsql += " SELECT "; if (pageSize > 0) { tempsql += "TOP " + PageUpperBound.ToString(); } tempsql += " [SystemProcessID] "; tempsql += " FROM [SystemProcess] AllRecords with (NOLOCK)"; if (!string.IsNullOrEmpty(whereClause) && whereClause.Length > 0) { tempsql += " WHERE " + whereClause; } if (orderByClause.Length > 0) { tempsql += " ORDER BY " + orderByClause; if (!orderByClause.Contains("SystemProcessID")) { tempsql += " , (AllRecords.[SystemProcessID])"; } } else { tempsql += " ORDER BY (AllRecords.[SystemProcessID])"; } // Return paged results string pagedResultsSql = (string.IsNullOrEmpty(SelectClause)? GetSystemProcessSelectClause():(string.Format("Select {0} ", SelectClause))) + @" FROM [SystemProcess] , #PageIndex PageIndex WHERE "; pagedResultsSql += " PageIndex.IndexId > " + PageLowerBound.ToString(); pagedResultsSql += @" AND [SystemProcess].[SystemProcessID] = PageIndex.[SystemProcessID] ORDER BY PageIndex.IndexId;" ; pagedResultsSql += " drop table #PageIndex"; sql = sql + tempsql + pagedResultsSql; sql = string.Format(sql, whereClause, pageSize, startIndex, orderByClause); DataSet ds = SqlHelper.ExecuteDataset(this.ConnectionString, CommandType.Text, sql, GetSQLParamtersBySearchColumns(searchColumns)); if (ds == null || ds.Tables.Count != 1 || ds.Tables[0].Rows.Count == 0) { return(null); } return(CollectionFromDataSet <SystemProcess>(ds, SystemProcessFromDataRow)); } else { return(null); } }
public static DataSet getAutoPayloads(int intSessionId) { return(SqlHelper.ExecuteDataset(strConnection, "WEBSCANNER_payload_getAutoPayloads", intSessionId.ToString())); }
/// <summary> /// Retorna datos para dibujado de la página de contacto /// </summary> /// <param name="_datos">Objeto con datos de conexión y parámetros </param> public CH_Contacto ObtenerDatosPagContacto(CH_Contacto _datos) { try { CH_Contacto datos = new CH_Contacto(); DataSet ds = SqlHelper.ExecuteDataset(_datos.Conexion, "CH_spCSLDB_get_DatosContacto"); if (ds != null) { if (ds.Tables.Count == 4) { DataTableReader dr = ds.Tables[0].CreateDataReader(); List <CH_Textos> listaTextos = new List <CH_Textos>(); CH_Textos itemTexto; while (dr.Read()) { itemTexto = new CH_Textos(); itemTexto.Texto = dr.GetString(dr.GetOrdinal("Texto")); itemTexto.NumPosition = dr.GetInt32(dr.GetOrdinal("NumPosition")); listaTextos.Add(itemTexto); } datos.ListaTextos = listaTextos; DataTableReader drImg = ds.Tables[1].CreateDataReader(); List <CH_Imagen> listaImagenes = new List <CH_Imagen>(); CH_Imagen itemImagen; while (drImg.Read()) { itemImagen = new CH_Imagen(); itemImagen.Alt = drImg.GetString(drImg.GetOrdinal("TextoAlternativo")); itemImagen.Title = drImg.GetString(drImg.GetOrdinal("TituloImagen")); itemImagen.UrlImagen = drImg.GetString(drImg.GetOrdinal("UrlImagen")); itemImagen.NumPosition = drImg.GetInt32(drImg.GetOrdinal("NumPosition")); listaImagenes.Add(itemImagen); } datos.ListaImagenes = listaImagenes; DataTableReader drContacto = ds.Tables[2].CreateDataReader(); while (drContacto.Read()) { string correos = drContacto.GetString(drContacto.GetOrdinal("Telefonos")); string telefonos = drContacto.GetString(drContacto.GetOrdinal("Correos")); datos.Direccion = drContacto.GetString(drContacto.GetOrdinal("Direccion")); datos.Latitud = drContacto.GetDouble(drContacto.GetOrdinal("Latitud")); datos.Longitud = drContacto.GetDouble(drContacto.GetOrdinal("Longitud")); List <string> listaCorreos = new List <string>(); string[] arregloAux = correos.Split(','); for (int i = 0; i < arregloAux.Length; i++) { listaCorreos.Add(arregloAux[i]); } List <string> listaTelefonos = new List <string>(); arregloAux = telefonos.Split(','); for (int i = 0; i < arregloAux.Length; i++) { listaTelefonos.Add(arregloAux[i]); } datos.Correos = listaCorreos; datos.Telefonos = listaTelefonos; break; } DataTableReader drRS = ds.Tables[3].CreateDataReader(); List <CH_RedesSociales> listaRS = new List <CH_RedesSociales>(); CH_RedesSociales itemRS; while (drRS.Read()) { itemRS = new CH_RedesSociales(); itemRS.UrlBase = drRS.GetString(drRS.GetOrdinal("UrlBase")); itemRS.CssClass = drRS.GetString(drRS.GetOrdinal("CssClass")); itemRS.CssClassIcon = drRS.GetString(drRS.GetOrdinal("ClassIcon")); itemRS.Cuenta = drRS.GetString(drRS.GetOrdinal("Cuenta")); itemRS.Nombre = drRS.GetString(drRS.GetOrdinal("Nombre")); listaRS.Add(itemRS); } datos.ListaRedesSociales = listaRS; datos.Completado = true; } } return(datos); } catch (Exception ex) { throw ex; } }
public DataSet GetMarketingEmailID() { return(SqlHelper.ExecuteDataset(conn, CommandType.StoredProcedure, "spsGetMarketingEmailIDList")); }
public DataSet GetStatus(int _requestid) { arParams = new SqlParameter[1]; arParams[0] = new SqlParameter("@requestid", _requestid); return(SqlHelper.ExecuteDataset(dsn, CommandType.StoredProcedure, "pr_getServiceRequestStatus", arParams)); }
public DataTable DL_TestimonialsData(ML_Default objML_Default) { SqlParameter[] par = { new SqlParameter("@ID", objML_Default.SliderCode), }; return(SqlHelper.ExecuteDataset(con, "FBNPC_Testimonials_Bind", par).Tables[0]); }
public DataSet GetMine(int _userid) { arParams = new SqlParameter[1]; arParams[0] = new SqlParameter("@userid", _userid); return(SqlHelper.ExecuteDataset(dsn, CommandType.StoredProcedure, "pr_getServiceRequestsMine", arParams)); }
public DataTable DL_DLMenu(ML_Default objML_Default) { SqlParameter[] par = { new SqlParameter("@ID", objML_Default.SliderCode), }; return(SqlHelper.ExecuteDataset(con, "FBNPC_OurPrograms_Bind", par).Tables[0]); }
/// <summary> /// 警区列表 /// </summary> /// <param name="dic"></param> /// <returns></returns> public PagedList <ScenicInfo> GetScenicInfoList(Dictionary <string, string> dic) { string sql = ""; try { int pageIndex = Convert.ToInt32(dic["pageIndex"]); int pageSize = Convert.ToInt32(dic["pageSize"]); string txtName = dic["txtName"]; string selName = dic["selName"].ToString(); string txtMobile = dic["txtMobile"]; string selMobile = dic["selMobile"].ToString(); string txtAddress = dic["txtAddress"]; string selAddress = dic["selAddress"].ToString(); string txtContact = dic["txtContact"]; string selContact = dic["selContact"].ToString(); int pid = Convert.ToInt32(dic["PId"]); List <SqlParameter> listParam = new List <SqlParameter>(); string whereSql = " WHERE s.Valid=1 "; if (selName == "1" && !string.IsNullOrEmpty(txtName)) { whereSql += " AND s.SName LIKE '%" + txtName + "%'"; } if (selName == "2" && !string.IsNullOrEmpty(txtName)) { whereSql += " AND s.SName=@Name"; listParam.Add(new SqlParameter("@Name", txtName)); } if (selMobile == "1" && !string.IsNullOrEmpty(txtMobile)) { whereSql += " AND s.Mobile LIKE '%" + txtMobile + "%'"; } if (selMobile == "2" && !string.IsNullOrEmpty(txtMobile)) { whereSql += " AND s.Mobile=@Mobile"; listParam.Add(new SqlParameter("@Mobile", txtMobile)); } if (selAddress == "1" && !string.IsNullOrEmpty(txtAddress)) { whereSql += " AND s.Address LIKE '%" + txtAddress + "%'"; } if (selAddress == "2" && !string.IsNullOrEmpty(txtAddress)) { whereSql += " AND s.Address=@Address"; listParam.Add(new SqlParameter("@Address", txtAddress)); } if (selContact == "1" && !string.IsNullOrEmpty(txtContact)) { whereSql += " AND s.Contact LIKE '%" + txtContact + "%'"; } if (selContact == "2" && !string.IsNullOrEmpty(txtContact)) { whereSql += " AND s.Contact=@Contact"; listParam.Add(new SqlParameter("@Contact", txtContact)); } whereSql += " AND s.PID=@PID"; listParam.Add(new SqlParameter("@PID", pid)); SqlParameter[] param = listParam.ToArray(); sql = "SELECT * FROM ( SELECT ROW_NUMBER () OVER (ORDER BY s.CreateTime DESC) PageNum, s.* FROM ScenicInfo s " + whereSql + " ) temp WHERE temp.PageNum BETWEEN " + ((pageIndex - 1) * pageSize + 1) + " AND " + pageIndex * pageSize; sql += " SELECT COUNT(0) FROM ScenicInfo s " + whereSql; DataSet ds = SqlHelper.ExecuteDataset(SqlHelper.DBConnStr, CommandType.Text, sql, param); DataSetConvert convert = new DataSetConvert(ds); List <ScenicInfo> list = convert.Get_ListModel <ScenicInfo>(); return(new PagedList <ScenicInfo>(list, pageIndex, pageSize, Convert.ToInt32(ds.Tables[1].Rows[0][0]))); } catch (Exception ex) { Logger.ErrorLog(ex, new Dictionary <string, string>() { { "Function", "SystemDAL.GetScenicInfoList(Dictionary<string,string> dic)" }, { "SQL", sql } }); throw ex; } }