private void Filldatatable(string itemnumber) { _acountsTb.Clear(); string sql = "SELECT * FROM [SPM_Database].[dbo].[Inventory] WHERE [ItemNumber]='" + itemnumber + "'"; try { if (connectapi.cn.State == ConnectionState.Closed) { connectapi.cn.Open(); } SqlDataAdapter _adapter = new SqlDataAdapter(sql, connectapi.cn); _adapter?.Fill(_acountsTb); if (_acountsTb.Rows.Count > 0) { Fillinfo(); } else { connectapi.Addcpoieditemtosqltablefromgenius(itemnumber, itemnumber); Fillinfo(); } } catch (SqlException ex) { MessageBox.Show(ex.Message, "SPM Connect New Item - Fill Data Table", MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { connectapi.cn.Close(); } }
internal KensaIraiTblDataSet.KensaIraiTblKensakuDataTable GetDataBySearchCond(string hokenjoCd, string kensaIraiNendo, string kensaIraiSetchishaNm, string kensaIraiShisetsuNm, string kensaIraiDtFrom, string kensaIraiDtTo, string kensaDtFrom, string kensaDtTo, string kensaIraiHoteiKbn) { SqlCommand command = CreateSqlCommand(hokenjoCd, kensaIraiNendo, kensaIraiSetchishaNm, kensaIraiShisetsuNm, kensaIraiDtFrom, kensaIraiDtTo, kensaDtFrom, kensaDtTo, kensaIraiHoteiKbn); SqlDataAdapter adpt = new SqlDataAdapter(command); adpt.SelectCommand.Connection = this.Connection; KensaIraiTblDataSet.KensaIraiTblKensakuDataTable dataTable = new KensaIraiTblDataSet.KensaIraiTblKensakuDataTable(); adpt.Fill(dataTable); return dataTable; }
protected void Fill_User_Header() { DataView view = null; SqlConnection con; SqlCommand cmd = new SqlCommand(); DataSet ds = new DataSet(); DataTable dt = new DataTable(); System.Configuration.Configuration rootWebConfig = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration("/BitOp"); System.Configuration.ConnectionStringSettings connString; connString = rootWebConfig.ConnectionStrings.ConnectionStrings["BopDBConnectionString"]; con = new SqlConnection(connString.ToString()); cmd.Connection = con; con.Open(); string sql = @"SELECT Fecha_Desde, Inicio_Nombre, Region, Supervisor FROM Criterios WHERE Criterio_ID = " + @Criterio_ID; SqlDataAdapter da = new SqlDataAdapter(sql, con); da.Fill(ds); dt = ds.Tables[0]; view = new DataView(dt); foreach (DataRowView row in view) { Lbl_Fecha_Desde.Text = row["Fecha_Desde"].ToString("dd-MM-yyyy"); Lbl_Inicio_Descrip.Text = row["Inicio_Nombre"].ToString(); Lbl_Region.Text = row["Region"].ToString(); Lbl_Supervisor.Text = row["Supervisor"].ToString(); } con.Close(); }
//use this for SELECT statements public static DataTable GetData(string SelectStatement) { SqlDataAdapter da = new SqlDataAdapter(SelectStatement, ConnectionString); DataTable dt = new DataTable(); da.Fill(dt); return dt; }
internal YoshiZaikoTblDataSet.YoshiZaikoTblKensakuDataTable GetDataBySearchCond(string shishoCd, string yoshiCdFrom, string yoshiCdTo, string yoshiNm) { SqlCommand command = CreateSqlCommand(shishoCd, yoshiCdFrom, yoshiCdTo, yoshiNm); SqlDataAdapter adpt = new SqlDataAdapter(command); adpt.SelectCommand.Connection = this.Connection; YoshiZaikoTblDataSet.YoshiZaikoTblKensakuDataTable dataTable = new YoshiZaikoTblDataSet.YoshiZaikoTblKensakuDataTable(); adpt.Fill(dataTable); return dataTable; }
internal KenchikuyotoMstDataSet.KenchikuyotoMstKensakuDataTable GetDataBySearchCond(string kenchikuyotoDaibunrui, string kenchikuyotoNm) { SqlCommand command = CreateSqlCommand(kenchikuyotoDaibunrui, kenchikuyotoNm); SqlDataAdapter adpt = new SqlDataAdapter(command); adpt.SelectCommand.Connection = this.Connection; KenchikuyotoMstDataSet.KenchikuyotoMstKensakuDataTable dataTable = new KenchikuyotoMstDataSet.KenchikuyotoMstKensakuDataTable(); adpt.Fill(dataTable); return dataTable; }
internal YubinNoAdrMstDataSet.YubinNoAdrMstKensakuDataTable GetDataBySearchCond(string zipCd, string address) { SqlCommand command = CreateSqlCommand(zipCd, address); SqlDataAdapter adpt = new SqlDataAdapter(command); adpt.SelectCommand.Connection = this.Connection; YubinNoAdrMstDataSet.YubinNoAdrMstKensakuDataTable dataTable = new YubinNoAdrMstDataSet.YubinNoAdrMstKensakuDataTable(); adpt.Fill(dataTable); return dataTable; }
private void Consultar() { try { //Crio a variável que irá armazenar a string de conexão string strConn = @""; //Crio a conexão por meio do using, que me garante que após o uso, a conexão será fechada using (SqlConnection objConn = new SqlConnection(strConn)) { //Passo a instrução SQL por meio do SqlCommand e concateno //meu Where com o que o usuário digitar no TextBox SqlCommand objCmd = new SqlCommand("SELECT CODIGO, NOME FROM REGISTROS WHERE CODIGO = " + txtConsultar.Text, objConn); //Instancio o DataTable passando como parâmetro o SqlCommand SqlDataAdapter objDtAdapter = new SqlDataAdapter(objCmd); //Instancio o DataSet DataSet ds = new DataSet(); //Uso o método Fill do DataAdapter, passando como parâmetro o DataSet e minha Tabela objDtAdapter.Fill(ds, "REGISTROS"); //Uso o método DataSource de meu GridView, que receberá meu DataSet e chamo o DataBind dgvResultado.DataSource = ds; dgvResultado.DataBind(); } } catch (Exception ex) { throw new Exception(ex.Message.ToString()); } }
public static DataTable GetList(string sql, params SqlParameter[] ps) { using (SqlConnection connection = new SqlConnection(connStr)) { SqlDataAdapter adapter = new SqlDataAdapter(sql, connection); if (ps.Length > 0) { adapter.SelectCommand.Parameters.AddRange(ps); } DataTable dataTable = new DataTable(); adapter.Fill(dataTable); return dataTable; } }
internal ChikuMstDataSet.ChikuMstKensakuDataTable GetDataBySearchCond( String chikuCdFrom, String chikuCdTo, String chikuNm) { SqlCommand command = CreateSqlCommand(chikuCdFrom, chikuCdTo, chikuNm); SqlDataAdapter adpt = new SqlDataAdapter(command); adpt.SelectCommand.Connection = this.Connection; ChikuMstDataSet.ChikuMstKensakuDataTable dataTable = new ChikuMstDataSet.ChikuMstKensakuDataTable(); adpt.Fill(dataTable); return dataTable; }
internal ShishoMstDataSet.ShishoMstDataTable GetDataBySearchCond( string shishoCdFrom, string shishoCdTo, string shishoNm ) { SqlCommand command = CreateSqlCommand(shishoCdFrom, shishoCdTo, shishoNm); SqlDataAdapter adpt = new SqlDataAdapter(command); adpt.SelectCommand.Connection = this.Connection; ShishoMstDataSet.ShishoMstDataTable dataTable = new ShishoMstDataSet.ShishoMstDataTable(); adpt.Fill(dataTable); return dataTable; }
internal FileKanriTblDataSet.FileKanriTblKensakuDataTable GetDataBySearchCond( string fileName, bool torokuDtUse, DateTime torokuDtFrom, DateTime torokuDtTo) { SqlCommand command = CreateSqlCommand(fileName,torokuDtUse, torokuDtFrom, torokuDtTo); SqlDataAdapter adpt = new SqlDataAdapter(command); adpt.SelectCommand.Connection = this.Connection; FileKanriTblDataSet.FileKanriTblKensakuDataTable dataTable = new FileKanriTblDataSet.FileKanriTblKensakuDataTable(); adpt.Fill(dataTable); return dataTable; }
internal NameMstDataSet.NameMstKensakuDataTable GetDataBySearchCond( String nameKbn, String nameCdFrom, String nameCdTo, String name) { SqlCommand command = CreateNameMstKensakuSqlCommand(nameKbn, nameCdFrom, nameCdTo, name); SqlDataAdapter adpt = new SqlDataAdapter(command); adpt.SelectCommand.Connection = this.Connection; NameMstDataSet.NameMstKensakuDataTable dataTable = new NameMstDataSet.NameMstKensakuDataTable(); adpt.Fill(dataTable); return dataTable; }
internal MemoMstDataSet.MemoMstKensakuDataTable GetDataBySearchCond( string memoDaibunruiCd, string memo, bool juyo, bool hutsu, bool sentakuKa, bool sentakuHuka) { SqlCommand command = CreateSqlCommand(memoDaibunruiCd, memo, juyo, hutsu, sentakuKa, sentakuHuka); SqlDataAdapter adpt = new SqlDataAdapter(command); adpt.SelectCommand.Connection = this.Connection; MemoMstDataSet.MemoMstKensakuDataTable dataTable = new MemoMstDataSet.MemoMstKensakuDataTable(); adpt.Fill(dataTable); return dataTable; }
internal KatashikiMstDataSet.KatashikiMstKensakuDataTable GetDataBySearchCond( String katashikiMakerCdFrom, String katashikiMakerCdTo, String gyoshaNm, String katashikiCdFrom, String katashikiCdTo, String katashikiNm) { SqlCommand command = CreateSqlCommand(katashikiMakerCdFrom, katashikiMakerCdTo, gyoshaNm, katashikiCdFrom, katashikiCdTo, katashikiNm ); SqlDataAdapter adpt = new SqlDataAdapter(command); adpt.SelectCommand.Connection = this.Connection; KatashikiMstDataSet.KatashikiMstKensakuDataTable dataTable = new KatashikiMstDataSet.KatashikiMstKensakuDataTable(); adpt.Fill(dataTable); return dataTable; }
protected void Page_Load(object sender, EventArgs e) { if (!X.IsAjaxRequest) { this.TreePanelMain.Listeners.ItemClick.Handler = "addTab1(App.Pages, record);"; /////////////DB ENABLE/DISABLE USING USER TASKS////////////////// SqlConnection con; SqlCommand cmd; SqlDataAdapter myAdapter; DataSet ds; con = new SqlConnection("server=local;uid=sa;pwd=abc@123;Database=TNT;Connect Timeout=10000;"); try { con.Open(); cmd = new SqlCommand("select * from VuAllUsersTasks Where UserID=2 order by TaskGroup", con); cmd.CommandType = CommandType.Text; myAdapter = new SqlDataAdapter(); myAdapter.SelectCommand = cmd; cmd.CommandTimeout = 1000; ds = new DataSet(); myAdapter.Fill(ds); if (ds.Tables.Count != 0) { dTuserTasks = ds.Tables[0]; } else { dTuserTasks = null; } } catch (Exception EX) { throw EX; } /////////////DB ENABLE/DISABLE USING USER TASKS////////////////// //add userName at menubar //if (dTuserTasks != null && dTuserTasks.Rows.Count > 0) //{ // lblUserName.Text = dTuserTasks.Rows[0]["UserName"].ToString(); //} //////////////////Add Nods at runtime for (int i = 0; i < dTuserTasks.Rows.Count; i++) { creatNodeRuntime(dTuserTasks.Rows[i]["TaskName"].ToString(), dTuserTasks.Rows[i]["TaskKey"].ToString(), dTuserTasks.Rows[i]["TaskGroup"].ToString(), dTuserTasks.Rows[i]["MenuIcon"].ToString(), dTuserTasks.Rows[i]["PageURL"].ToString() ); } //remove group nodes, those who dont have leaf nodes foreach (string str in NodeIdsOfGroups) { //dont delete Parents nodes those who hava sub parents in them. if (dTuserTasks.Select("TaskGroup = '" + str + "'").Length < 1 && !NodeIdsDontRemove.Contains(str)) { //TreePanelMain.GetNodeById(str).Set("hidden", true); TreePanelMain.GetNodeById(str).Remove(); //also remove parents or leafs, influence for front end. var list = new List <string>(NodeIDsFinance); list.Remove(str); NodeIDsFinance = list.ToArray(); list = new List <string>(NodeIDsMis); list.Remove(str); NodeIDsMis = list.ToArray(); list = new List <string>(NodeIDsSecurity); list.Remove(str); NodeIDsSecurity = list.ToArray(); list = new List <string>(NodesToCollapse); list.Remove(str); NodesToCollapse = list.ToArray(); } } //////////////////Add Nods at runtime //////allowd tasks str array object for javascript string arrayString = string.Empty; foreach (DataRow row in dTuserTasks.Rows) { arrayString += "new String('" + row["TaskKey"].ToString() + "'),"; } // arrayString = "new String('word1'),new String('word2'), ...etc"; ClientScript.RegisterArrayDeclaration("AllowedTasksKeys", arrayString); //////allowd tasks str array object for javascript //////hideForMis str array object for javascript arrayString = string.Empty; foreach (string str in NodeIDsMis) { arrayString += "new String('" + str + "'),"; } ClientScript.RegisterArrayDeclaration("NodeIDsMis", arrayString); //////hideForMis str array object for javascript //////hideForSec str array object for javascript arrayString = string.Empty; foreach (string str in NodeIDsSecurity) { arrayString += "new String('" + str + "'),"; } ClientScript.RegisterArrayDeclaration("NodeIDsSecurity", arrayString); //////hideForSec str array object for javascript //////hideForFinance str array object for javascript arrayString = string.Empty; foreach (string str in NodeIDsFinance) { arrayString += "new String('" + str + "'),"; } ClientScript.RegisterArrayDeclaration("NodeIDsFinance", arrayString); //////hideForFinance str array object for javascript //////// nodesToColapse str array object for javascript arrayString = string.Empty; foreach (string str in NodesToCollapse) { arrayString += "new String('" + str + "'),"; } ClientScript.RegisterArrayDeclaration("NodesToCollapse", arrayString); //////// nodesToColapse str array object for javascript /////EXPEND THEN COLLAPSE TO MAKE THEM HIDE for (int i = 0; i < NodesToCollapse.Length; i++) { TreePanelMain.GetNodeById(NodesToCollapse[i]).Expand(true); TreePanelMain.GetNodeById(NodesToCollapse[i]).Collapse(true); } /////EXPEND THEN COLLAPSE TO MAKE THEM HIDE ///////////////////////////////NOW HANDELING AT CLIENT SIDE///// SEARCH FOR <AfterRender>/////////////////////////////// /////set Active Menu Item until right frontend event is not found //MIFinance.AddCls("setActiveTab"); //MISecurity.RemoveCls("setActiveTab"); //MIMIS.RemoveCls("setActiveTab"); /////set Active Menu Item until right frontend event is not found //////////handeling at backend until find apropirate client load event //MISNodesHide(true); //SecurityNodesHide(true); //FinanceNodesHide(false); //////////handeling at backend until find apropirate client load event ///////////////////////////////NOW HANDELING AT CLIENT SIDE///// SEARCH FOR <AfterRender>/////////////////////////////// this.TreePanelMain.Title = "Welcome Finance"; //TreePanelMain.GetNodeById("OpeningBalances").Remove(); //TreePanelMain.GetNodeById("ChartofAccounts").Remove(); ////////////test btnid.Text = dTuserTasks.Rows[0]["UserName"].ToString(); ////////////test } }
public (CharacterAbilityListWithFilterCount, Character, RuleSet) SP_CharacterAbility_GetByCharacterId_Old(int characterId, int rulesetId, int page, int pageSize, int sortType = 1) { CharacterAbilityListWithFilterCount result = new CharacterAbilityListWithFilterCount(); List <CharacterAbility> _CharacterAbilityList = new List <CharacterAbility>(); RuleSet ruleset = new RuleSet(); Character character = new Character(); int FilterAplhabetCount = 0; int FilterEnabledCount = 0; int FilterLevelCount = 0; result.AbilityList = _CharacterAbilityList; result.FilterAplhabetCount = FilterAplhabetCount; result.FilterEnabledCount = FilterEnabledCount; result.FilterLevelCount = FilterLevelCount; short num = 0; string connectionString = _configuration.GetSection("ConnectionStrings").GetSection("DefaultConnection").Value; //string qry = "EXEC CharacterAbility_GetByCharacterId @CharacterId='" + characterId + "',@RulesetID='" + rulesetId + "',@page='" + page + "',@size='" + pageSize + "'"; SqlConnection connection = new SqlConnection(connectionString); SqlCommand command = new SqlCommand(); SqlDataAdapter adapter = new SqlDataAdapter(); DataSet ds = new DataSet(); try { connection.Open(); command = new SqlCommand("CharacterAbility_GetByCharacterId", connection); // Add the parameters for the SelectCommand. command.Parameters.AddWithValue("@CharacterId", characterId); command.Parameters.AddWithValue("@RulesetID", rulesetId); command.Parameters.AddWithValue("@page", page); command.Parameters.AddWithValue("@size", pageSize); command.Parameters.AddWithValue("@SortType", sortType); command.CommandType = CommandType.StoredProcedure; adapter.SelectCommand = command; adapter.Fill(ds); command.Dispose(); connection.Close(); } catch (Exception ex) { command.Dispose(); connection.Close(); } if (ds.Tables[1].Rows.Count > 0) { ruleset = _repo.GetRuleset(ds.Tables[1], num); } if (ds.Tables[2].Rows.Count > 0) { character = _repo.GetCharacter(ds.Tables[2]); } if (ds.Tables[0].Rows.Count > 0) { foreach (DataRow row in ds.Tables[0].Rows) { CharacterAbility _characterAbility = new CharacterAbility(); _characterAbility.CharacterAbilityId = row["CharacterAbilityId"] == DBNull.Value ? 0 : Convert.ToInt32(row["CharacterAbilityId"].ToString()); _characterAbility.CharacterId = characterId; _characterAbility.AbilityId = row["AbilityId"] == DBNull.Value ? 0 : Convert.ToInt32(row["AbilityId"].ToString()); _characterAbility.CurrentNumberOfUses = row["CharacterCurrentNumberOfUses"] == DBNull.Value ? 0 : Convert.ToInt32(row["CharacterCurrentNumberOfUses"].ToString()); _characterAbility.MaxNumberOfUses = row["CharacterMaxNumberOfUses"] == DBNull.Value ? 0 : Convert.ToInt32(row["CharacterMaxNumberOfUses"].ToString()); _characterAbility.IsEnabled = row["CharacterIsEnabled"] == DBNull.Value ? false : Convert.ToBoolean(row["CharacterIsEnabled"]); Ability _ability = new Ability(); _ability.Name = row["Name"] == DBNull.Value ? null : row["Name"].ToString(); _ability.Stats = row["Stats"] == DBNull.Value ? null : row["Stats"].ToString(); _ability.Metatags = row["Metatags"] == DBNull.Value ? null : row["Metatags"].ToString(); _ability.Command = row["Command"] == DBNull.Value ? null : row["Command"].ToString(); _ability.CommandName = row["CommandName"] == DBNull.Value ? null : row["CommandName"].ToString(); _ability.Description = row["Description"] == DBNull.Value ? null : row["Description"].ToString(); _ability.gmOnly = row["gmOnly"] == DBNull.Value ? null : row["gmOnly"].ToString(); _ability.ImageUrl = row["ImageUrl"] == DBNull.Value ? null : row["ImageUrl"].ToString(); _ability.Level = row["Level"] == DBNull.Value ? null : row["Level"].ToString(); _ability.IsDeleted = row["IsDeleted"] == DBNull.Value ? false : Convert.ToBoolean(row["IsDeleted"]); _ability.IsEnabled = row["IsEnabled"] == DBNull.Value ? false : Convert.ToBoolean(row["IsEnabled"]); _ability.AbilityId = row["AbilityId"] == DBNull.Value ? 0 : Convert.ToInt32(row["AbilityId"].ToString()); _ability.ParentAbilityId = row["ParentAbilityId"] == DBNull.Value ? 0 : Convert.ToInt32(row["ParentAbilityId"].ToString()); _ability.RuleSetId = row["RuleSetId"] == DBNull.Value ? 0 : Convert.ToInt32(row["RuleSetId"].ToString()); _ability.CurrentNumberOfUses = row["CurrentNumberOfUses"] == DBNull.Value ? 0 : Convert.ToInt32(row["CurrentNumberOfUses"].ToString()); _ability.MaxNumberOfUses = row["MaxNumberOfUses"] == DBNull.Value ? 0 : Convert.ToInt32(row["MaxNumberOfUses"].ToString()); _ability.RuleSet = ruleset; _characterAbility.Ability = _ability; //_characterAbility.Character = character; _CharacterAbilityList.Add(_characterAbility); } } if (ds.Tables[3].Rows.Count > 0) { FilterAplhabetCount = ds.Tables[3].Rows[0][0] == DBNull.Value ? 0 : Convert.ToInt32(ds.Tables[3].Rows[0][0]); FilterLevelCount = ds.Tables[3].Rows[0][1] == DBNull.Value ? 0 : Convert.ToInt32(ds.Tables[3].Rows[0][1]); } if (ds.Tables[4].Rows.Count > 0) { FilterEnabledCount = ds.Tables[4].Rows[0][0] == DBNull.Value ? 0 : Convert.ToInt32(ds.Tables[4].Rows[0][0]); } if (ds.Tables[5].Rows.Count > 0) { FilterLevelCount = ds.Tables[5].Rows[0][0] == DBNull.Value ? 0 : Convert.ToInt32(ds.Tables[5].Rows[0][0]); } result.AbilityList = _CharacterAbilityList; result.FilterAplhabetCount = FilterAplhabetCount; result.FilterEnabledCount = FilterEnabledCount; result.FilterLevelCount = FilterLevelCount; //return result; return(result, character, ruleset); }
public string drawMenu(string UTID) { SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Con"].ConnectionString); //PageURL = Request.Url.AbsolutePath; PageURL = HttpContext.Current.Request.Url.AbsolutePath; int i = 0; int j = 0; string InnerHTML = ""; PageURL = PageURL.ToLowerInvariant(); string str = "select distinct FormID As id, FormTitle As name , PID As parentid , Path As path from VW_MENU where PID=0 and UTID='" + UTID + "' order by FormID"; SqlDataAdapter sqladp = new SqlDataAdapter(str, Conn); DataTable dt = new DataTable(); sqladp.Fill(dt); InnerHTML = InnerHTML + "<ul>"; for (i = 0; i < dt.Rows.Count; i++) { string hasChild = getchildes(Convert.ToInt32(dt.Rows[i]["id"].ToString()), UTID); if (hasChild == "") { string ActiveChild = ""; string path = dt.Rows[i]["path"].ToString(); path = path.ToLowerInvariant(); if (PageURL.IndexOf(path) != -1) { ActiveChild = " class=active"; } InnerHTML = InnerHTML + "<li>"; InnerHTML = InnerHTML + "<a href='" + "../" + dt.Rows[i]["path"].ToString() + "" + "'>"; InnerHTML = InnerHTML + "<span class=menu-icon'><i class='fa fa-envelope'></i></span> "; InnerHTML = InnerHTML + "<span class='menu-text'>" + dt.Rows[i]["name"].ToString() + "</span></a>"; //<span class="menu-badge"><span class="badge vd_bg-red">78</span></span> InnerHTML = InnerHTML + "</li>"; } else { string ActiveParent = ""; if (hasChild.IndexOf("class=active") != -1) { ActiveParent = " active"; } string TotalChild = hasChild; TotalChild = TotalChild.Substring(0, TotalChild.IndexOf("`")); hasChild = hasChild.Substring(hasChild.IndexOf("`") + 1); InnerHTML = InnerHTML + "<li>"; InnerHTML = InnerHTML + "<a href='javascript:void(0);' data-action='click-trigger'>"; if (i == 0) { InnerHTML = InnerHTML + "<span class='menu-icon'><i class='fa fa-dashboard'></i></span> "; } if (i == 1) { InnerHTML = InnerHTML + "<span class='menu-icon'><i class='fa fa-envelope'></i></span> "; } if (i == 2) { InnerHTML = InnerHTML + "<span class='menu-icon'><i class='icon-palette'></i></span> "; } if (i == 3) { InnerHTML = InnerHTML + "<span class='menu-icon'><i class='icon-bookmark'></i></span> "; } if (i == 4) { InnerHTML = InnerHTML + "<span class='menu-icon'><i class='fa fa-sitemap'></i></span> "; } if (i == 5) { InnerHTML = InnerHTML + "<span class='menu-icon'><i class='fa fa-code'></i></span> "; } if (i == 6) { InnerHTML = InnerHTML + "<span class='menu-icon'><i class='fa fa-th-list'></i></span> "; } if (i == 7) { InnerHTML = InnerHTML + "<span class='menu-icon'><i class='icon-tools'></i></span> "; } if (i == 8) { InnerHTML = InnerHTML + "<span class='menu-icon'><i class='fa fa-table'></i></span> "; } if (i == 9) { InnerHTML = InnerHTML + "<span class='menu-icon'><i class='fa fa-signal'></i></span> "; } if (i == 10) { InnerHTML = InnerHTML + "<span class='menu-icon'><i class='fa fa-map-marker'></i></span> "; } if (i == 11) { InnerHTML = InnerHTML + "<span class='menu-icon'><i class='icon-newspaper'></i></span> "; } if (i == 12) { InnerHTML = InnerHTML + "<span class='menu-icon'><i class='fa fa-desktop'></i></span> "; } if (i == 13) { InnerHTML = InnerHTML + "<span class='menu-icon'><i class='fa fa-shopping-cart'></i></span> "; } if (i == 14) { InnerHTML = InnerHTML + "<span class='menu-icon'><i class='fa fa-dashboard'></i></span> "; } if (i == 15) { InnerHTML = InnerHTML + "<span class='menu-icon'><i class='fa fa-dashboard'></i></span> "; } if (i == 16) { InnerHTML = InnerHTML + "<span class='menu-icon'><i class='fa fa-dashboard'></i></span> "; } if (i == 17) { InnerHTML = InnerHTML + "<span class='menu-icon'><i class='fa fa-dashboard'></i></span> "; } if (i == 18) { InnerHTML = InnerHTML + "<span class='menu-icon'><i class='fa fa-dashboard'></i></span> "; } if (i == 19) { InnerHTML = InnerHTML + "<span class='menu-icon'><i class='fa fa-dashboard'></i></span> "; } InnerHTML = InnerHTML + "<span class='menu-text'>" + dt.Rows[i]["name"].ToString() + "</span> "; InnerHTML = InnerHTML + "<span class='menu-badge'><span class='badge vd_bg-black-30'><i class='fa fa-angle-down' style='color: red;font-size: 17px;'></i></span></span>"; InnerHTML = InnerHTML + "</a>"; InnerHTML = InnerHTML + "<div class='child-menu' data-action='click-target'><ul>"; InnerHTML = InnerHTML + hasChild; InnerHTML = InnerHTML + "</ul></div>"; InnerHTML = InnerHTML + "</li> "; } } InnerHTML = InnerHTML + "</ul>"; //InnerHTML = InnerHTML + "<li><a href='javascript:;' class='sidebar-minify-btn' data-click='sidebar-minify'><i class='fa fa-angle-double-left'></i></a></li>"; //InnerHTML = InnerHTML + "</ul>"; //dvMenu.InnerHtml = InnerHTML; return(InnerHTML); }
/* * --> BOTON ELIMINAR ENCARGADOS TIENDAS SPLT */ private void RegistrarEncargadoTienda_Click(object sender, EventArgs e) { if (txtIDEmpleadoSPLT.Text.Length == 0 || txtNombreEmpleadoSPLT.Text.Length == 0 || txtApellidoEmpleadoSPLT.Text.Length == 0 || txtidencargadotienda.Text.Length == 0 || txtcodigoUtiendas.Text.Length == 0 || txtdireccionCtiendas.Text.Length == 0 || txttelefonotiendas.Text.Length == 0) { // CREANDO MENSAJE EN VENTANA FLOTANTE PERSONALIZADO if (MessageBox.Show("Lo sentimos pero ha ocurrido un error, tome en cuenta lo siguiente:\n- No puede dejar campos vacíos.\n- Por favor sea cuidadoso a la hora de eliminar un encargado de tienda, respetando las métricas establecidas por la empresa.", "Error al Intentar Eliminar Registro", MessageBoxButtons.OK, MessageBoxIcon.Error) == DialogResult.OK) { ; } } else { // CREANDO VARIABLE TIPO QUERY QUE ALMACENA LA CADENA DE ELIMINAR X REGISTRO CON SU CONDICION string query = "DELETE FROM EncargadosTiendas WHERE ID_encargado=@ID_encargado"; // --> WHERE [ID_encargado]=@[ID_encargado]" IMPORTANTE: SI NO SE REFERENCIA POR DEFECTO NO MUESTRA ERROR PERO SE PRODUCE UN ERROR LOGICO, AL ELIMINAR ABSOLUTAMENTE // TODOS LOS DATOS DE LA TABLA REFERENCIADA EN LA BASE DE DATOS. con.Open(); // APERTURANDO CONEXION SqlCommand comando = new SqlCommand(query, con); // CREANDO COMPONENTE DE COMUNICACION HACIA LA BASE DE DATOS // ENVIO DE PARAMETROS CON LOS DATOS SELECCIONADOS PREVIAMENTE POR EL USUARIO HACIA LA BASE DE DATOS comando.Parameters.AddWithValue("@ID_encargado", txtidencargadotienda.Text); // REFERENCIA DEL ID UNICO DE CADA ENCARGADO DE TIENDA comando.ExecuteNonQuery(); // ENVIANDO COMPONENTE QUERY HACIA LA BASE DE DATOS CON NUEVO REGISTRO ACTUALIZADO // CREANDO MENSAJE EN VENTANA FLOTANTE PERSONALIZADO // VALIDANDO QUE NO EXISTAN CAMPOS VACIOS Y QUE AL MENOS EL USUARIO SELECCIONE UN REGISTRO A ELIMINAR if (txtIDEmpleadoSPLT.Text.Length == 0 || txtNombreEmpleadoSPLT.Text.Length == 0 || txtApellidoEmpleadoSPLT.Text.Length == 0 || txtidencargadotienda.Text.Length == 0 || txtcodigoUtiendas.Text.Length == 0 || txtdireccionCtiendas.Text.Length == 0 || txttelefonotiendas.Text.Length == 0) { // CREANDO MENSAJE EN VENTANA FLOTANTE PERSONALIZADO if (MessageBox.Show("Lo sentimos pero ha ocurrido un error, tome en cuenta lo siguiente:\n- No puede dejar campos vacíos.\n- Por favor sea cuidadoso a la hora de eliminar una tienda, respetando las métricas establecidas por la empresa.", "Error al Intentar Eliminar Registro", MessageBoxButtons.OK, MessageBoxIcon.Error) == DialogResult.OK) { ; } con.Close();// AL FINALIZAR CADA NUEVO REGISTRO, SE PROCEDE A CERRAR LA CONEXION A ESPERA DE UNA NUEVA PETICION } else { // CREANDO MENSAJE EN VENTANA FLOTANTE PERSONALIZADO if (MessageBox.Show("Encargado de Tienda Eliminado Con Exito", "Eliminando Encargado De Tienda Existente", MessageBoxButtons.OK, MessageBoxIcon.Information) == DialogResult.OK) { ; } /*------------------------------------------------------------------------------------------ * -> INICIALIZANDO TEXTBOX A SUS VALORES PREDETERMINADOS ANTES DE CERRAR CONEXION UNA VEZ * REALIZADO EL NUEVO REGISTRO A LA BASE DE DATOS {CONSTRUCTORES} * -----------------------------------------------------------------------------------------*/ txtApellidoEmpleadoSPLT.Text = " "; txtNombreEmpleadoSPLT.Text = " "; txtIDEmpleadoSPLT.Text = " "; txtidencargadotienda.Text = " "; txtcodigoUtiendas.Text = " "; txtdireccionCtiendas.Text = " "; txttelefonotiendas.Text = " "; txtIDEmpleadoSPLT.Text = " "; txtNombreEmpleadoSPLT.Text = " "; txtApellidoEmpleadoSPLT.Text = " "; /*--------------------------------------------------------------------------------------------- * AL MOMENTO QUE EL USUARIO INGRESA LOS DATOS, ESTE VUELVE A EJECUTAR EL PROCEDIMIENTO PARA * * REFRESCAR LA TABLA CONTENEDORA CON LOS N USUARIOS REGISTRADOS EN EL SISTEMA... * * -------------------------------------------------------------------------------------------- */ // REFERENCIANDO EL NOMBRE DE LA TABLA A MOSTRAR LOS N DATOS CONTENIDOS EN ELLA SqlCommand cmd = new SqlCommand("Select ID_encargado, Id_empleado, Nombre_empleado, Apellido_empleado, Codigo_tienda, Direccion_tienda, Tefefono_tienda from EncargadosTiendas", con); // CREANDO ADAPTADOR DE COMUNICACION HACIA LA BASE DE DATOS PARA EL LLENADO DE REGISTROS... SqlDataAdapter MostrarRegistros = new SqlDataAdapter(); MostrarRegistros.SelectCommand = cmd; // REFERENCIANDO LA INVOCACION DE UN NUEVO OBJETO PARA MANEJO DE DATAGRIDVIEW DataTable TablaRegistros = new DataTable(); // LLENANDO CON TODOS LOS REGISTROS CONTENIDOS EN LA TABLA DE EMPLEADOS MostrarRegistros.Fill(TablaRegistros); // INVOCANDO LA REFERENCIA QUE SEA IGUAL AL ADAPTADOR DE LLENADO DE DATOS, // ES DECIR, TODOS LOS REGISTROS CONTENIDOS DENTRO DE LA TABLA {Empleados} DetallesEncargadosTiendasSpeedLimit.DataSource = TablaRegistros; con.Close();// AL FINALIZAR CADA NUEVO REGISTRO, SE PROCEDE A CERRAR LA CONEXION A ESPERA DE UNA NUEVA PETICION } } }
public List <PharmaBusinessObjects.Transaction.PurchaseSaleBookLineItem> GetPurchaseSaleBookLineItemForModify(long purchaseSaleBookHeaderID) { List <PharmaBusinessObjects.Transaction.PurchaseSaleBookLineItem> lineitems = new List <PharmaBusinessObjects.Transaction.PurchaseSaleBookLineItem>(); string ConnString = ConfigurationManager.ConnectionStrings["PharmaDBConn"].ConnectionString; using (SqlConnection connection = new SqlConnection(ConnString)) { SqlCommand cmd = new SqlCommand("GetPurchaseSaleBookLineItemsForModify", connection); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter() { ParameterName = "@PurchaseSaleBookHeaderID", Value = purchaseSaleBookHeaderID }); SqlDataAdapter sda = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); sda.Fill(dt); if (dt != null && dt.Rows.Count > 0) { foreach (DataRow row in dt.Rows) { PharmaBusinessObjects.Transaction.PurchaseSaleBookLineItem obj = new PharmaBusinessObjects.Transaction.PurchaseSaleBookLineItem() { PurchaseSaleBookHeaderID = Convert.ToInt64(row["PurchaseSaleBookHeaderID"]), PurchaseSaleBookLineItemID = Convert.ToInt64(row["PurchaseSaleBookLineItemID"]), FifoID = Convert.ToInt64(row["FifoID"] == DBNull.Value ? 0 : row["FifoID"]), ItemCode = Convert.ToString(row["ItemCode"]), ItemName = Convert.ToString(row["ItemName"]), Batch = Convert.ToString(row["Batch"]), Quantity = Convert.ToDecimal(row["Quantity"] == DBNull.Value ? 0 : row["Quantity"]), FreeQuantity = Convert.ToDecimal(row["FreeQuantity"] == DBNull.Value ? 0 : row["FreeQuantity"]), PurchaseSaleRate = Convert.ToDecimal(row["PurchaseSaleRate"] == DBNull.Value ? 0 : row["PurchaseSaleRate"]), OldPurchaseSaleRate = Convert.ToDecimal(row["PurchaseSaleRate"] == DBNull.Value ? 0 : row["PurchaseSaleRate"]), EffecivePurchaseSaleRate = Convert.ToDecimal(row["EffecivePurchaseSaleRate"] == DBNull.Value ? 0 : row["EffecivePurchaseSaleRate"]), PurchaseSaleTypeCode = Convert.ToString(row["PurchaseSaleTypeCode"]), PurchaseSaleTax = Convert.ToDecimal(row["PurchaseSaleTax"] == DBNull.Value ? 0 : row["PurchaseSaleTax"]), SurCharge = Convert.ToDecimal(row["SurCharge"] == DBNull.Value ? 0 : row["SurCharge"]), LocalCentral = Convert.ToString(row["LocalCentral"]), SGST = Convert.ToDecimal(row["SGST"] == DBNull.Value ? 0 : row["SGST"]), IGST = Convert.ToDecimal(row["IGST"] == DBNull.Value ? 0 : row["IGST"]), CGST = Convert.ToDecimal(row["CGST"] == DBNull.Value ? 0 : row["CGST"]), Amount = Convert.ToDecimal(row["Amount"] == DBNull.Value ? 0 : row["Amount"]), Discount = Convert.ToDecimal(row["Discount"] == DBNull.Value ? 0 : row["Discount"]), SpecialDiscount = Convert.ToDecimal(row["SpecialDiscount"] == DBNull.Value ? 0 : row["SpecialDiscount"]), DiscountQuantity = Convert.ToDecimal(row["DiscountQuantity"] == DBNull.Value ? 0 : row["DiscountQuantity"]), VolumeDiscount = Convert.ToDecimal(row["VolumeDiscount"] == DBNull.Value ? 0 : row["VolumeDiscount"]), Scheme1 = Convert.ToDecimal(row["Scheme1"] == DBNull.Value ? 0 : row["Scheme1"]), Scheme2 = Convert.ToDecimal(row["Scheme2"] == DBNull.Value ? 0 : row["Scheme2"]), IsHalfScheme = Convert.ToBoolean(row["IsHalfScheme"] == DBNull.Value ? false : row["IsHalfScheme"]), HalfSchemeRate = Convert.ToDecimal(row["HalfSchemeRate"] == DBNull.Value ? 0 : row["HalfSchemeRate"]), CostAmount = Convert.ToDecimal(row["CostAmount"] == DBNull.Value ? 0 : row["CostAmount"]), GrossAmount = Convert.ToDecimal(row["GrossAmount"] == DBNull.Value ? 0 : row["GrossAmount"]), SchemeAmount = Convert.ToDecimal(row["SchemeAmount"] == DBNull.Value ? 0 : row["SchemeAmount"]), DiscountAmount = Convert.ToDecimal(row["DiscountAmount"] == DBNull.Value ? 0 : row["DiscountAmount"]), SurchargeAmount = Convert.ToDecimal(row["SurchargeAmount"] == DBNull.Value ? 0 : row["SurchargeAmount"]), ConversionRate = Convert.ToDecimal(row["ConversionRate"] == DBNull.Value ? 0 : row["ConversionRate"]), MRP = Convert.ToDecimal(row["MRP"] == DBNull.Value ? 0 : row["MRP"]), ExpiryDate = Convert.ToDateTime(row["ExpiryDate"]), SaleRate = Convert.ToDecimal(row["SaleRate"] == DBNull.Value ? 0 : row["SaleRate"]), WholeSaleRate = Convert.ToDecimal(row["WholeSaleRate"] == DBNull.Value ? 0 : row["WholeSaleRate"]), SpecialRate = Convert.ToDecimal(row["SpecialRate"] == DBNull.Value ? 0 : row["SpecialRate"]), TaxAmount = Convert.ToDecimal(row["TaxAmount"] == DBNull.Value ? 0 : row["TaxAmount"]), SpecialDiscountAmount = Convert.ToDecimal(row["SpecialDiscountAmount"] == DBNull.Value ? 0 : row["SpecialDiscountAmount"]), VolumeDiscountAmount = Convert.ToDecimal(row["VolumeDiscountAmount"] == DBNull.Value ? 0 : row["VolumeDiscountAmount"]), TotalDiscountAmount = Convert.ToDecimal(row["TotalDiscountAmount"] == DBNull.Value ? 0 : row["TotalDiscountAmount"]), OldPurchaseSaleBookLineItemID = Convert.ToInt64(row["OldPurchaseSaleBookLineItemID"] == DBNull.Value ? 0 : row["OldPurchaseSaleBookLineItemID"]), BalanceQuantity = Convert.ToDecimal(row["BalanceQuantity"] == DBNull.Value ? 0 : row["BalanceQuantity"]), UsedQuantity = Convert.ToDecimal(row["UsedQuantity"] == DBNull.Value ? 0 : row["UsedQuantity"]), PurchaseBillDate = row["PurchaseBillDate"] != DBNull.Value ? Convert.ToDateTime(row["PurchaseBillDate"]) : (DateTime?)null, PurchaseSrlNo = row["PurchaseSrlNo"] != null?Convert.ToInt32(row["PurchaseSrlNo"]) : (int?)null, PurchaseVoucherNumber = row["PurchaseVoucherNumber"] != null?Convert.ToString(row["PurchaseVoucherNumber"]) : null, MfgDate = row["MfgDate"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(row["MfgDate"]) }; lineitems.Add(obj); } } } return(lineitems); }
//获取相应桌台类别的桌台记录 public static DataTable LoadTableList(string TableTypeName) { System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(); conn.ConnectionString = ConnStr; try { SqlCommand selectCMD = new SqlCommand(); selectCMD.Connection = conn; selectCMD.CommandText = "SELECT tableno,tablename,status,begintime FROM TableStatus"; if (TableTypeName != "全部") { selectCMD.CommandText = selectCMD.CommandText + " where TabletypeCode='" + GetTableTypeCode(TableTypeName) + "'"; } selectCMD.CommandTimeout = 30; SqlDataAdapter dbDA = new SqlDataAdapter(); dbDA.SelectCommand = selectCMD; conn.Open(); DataSet dbDS = new DataSet(); dbDA.Fill(dbDS, "TableListView"); return dbDS.Tables[0]; } catch (Exception ex) { MessageBox.Show(ex.ToString()); } finally { conn.Close(); } return null; }
//检测桌台使用状态 //0=空闲,1=使用,2=预定中,-1=不存在该桌台 与桌台列表单击事件的返回值不同! public static int GetTableStatus(string tableno) { System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(); conn.ConnectionString = ConnStr; try { SqlCommand selectCMD = new SqlCommand("SELECT status FROM Tablestatus where TableNo='" + tableno + "'", conn); selectCMD.CommandTimeout = 30; SqlDataAdapter dbDA = new SqlDataAdapter(); dbDA.SelectCommand = selectCMD; conn.Open(); DataSet dbDS = new DataSet(); dbDA.Fill(dbDS, "t"); if (! (dbDS.Tables[0].Rows.Count > 0)) //没有该桌台 { return -1; } else { //0=空闲,1=使用,2=预定中 return Convert.ToInt32(dbDS.Tables[0].Rows[0]["status"]); } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } finally { conn.Close(); } return 0; }
public CancelLoadingkResponse CancelLoadingProcess(CancelLoading CL) { CancelLoadingkResponse SM = new CancelLoadingkResponse(); try { if (CL.UserId == "" || CL.UserId == null) { SM.Status = "Failure"; SM.Message = "Invalid UserId"; return(SM); } if (CL.LPID == "" || CL.LPID == null) { SM.Status = "Failure"; SM.Message = "Invalid LoadingId"; return(SM); } query = "Sp_LadingPlanWebApi"; dbcommand = new SqlCommand(query, conn); dbcommand.CommandType = CommandType.StoredProcedure; dbcommand.CommandTimeout = 0; dbcommand.Parameters.AddWithValue("@QueryType", "FetchStillagesId"); dbcommand.Parameters.AddWithValue("@LPID", CL.LPID); dbcommand.Parameters.AddWithValue("@UserId", CL.UserId); SqlDataAdapter daGetData1 = new SqlDataAdapter(dbcommand); DataSet dsGetData1 = new DataSet(); daGetData1.Fill(dsGetData1); foreach (DataRow row in dsGetData1.Tables[0].Rows) { query = "Sp_LadingPlanWebApi"; dbcommand = new SqlCommand(query, conn); dbcommand.CommandType = CommandType.StoredProcedure; dbcommand.CommandTimeout = 0; dbcommand.Parameters.AddWithValue("@QueryType", "FetchStillagesData"); dbcommand.Parameters.AddWithValue("@StillageID", row["StickerID"].ToString()); dbcommand.Parameters.AddWithValue("@UserId", CL.UserId); dbcommand.Parameters.AddWithValue("@LoadingId", CL.LPID); SqlDataAdapter daGetData = new SqlDataAdapter(dbcommand); DataSet dsGetData = new DataSet(); daGetData.Fill(dsGetData); query = "Sp_AxWebserviceIntegration"; dbcommand = new SqlCommand(query, conn); // dbcommand.Connection.Open(); dbcommand.CommandType = CommandType.StoredProcedure; dbcommand.CommandTimeout = 0; SqlDataAdapter da1 = new SqlDataAdapter(dbcommand); DataSet ds = new DataSet(); da1.Fill(ds); // DataSet ds = CommonManger.FillDatasetWithParam("Sp_AxWebserviceIntegration"); AXWebServiceRef1.Iace_FinishedGoodServiceClient obj = new AXWebServiceRef1.Iace_FinishedGoodServiceClient(); obj.ClientCredentials.Windows.ClientCredential.Domain = Convert.ToString(ds.Tables[0].Rows[0]["Domain"]); obj.ClientCredentials.Windows.ClientCredential.UserName = Convert.ToString(ds.Tables[0].Rows[0]["Username"]); obj.ClientCredentials.Windows.ClientCredential.Password = Convert.ToString(ds.Tables[0].Rows[0]["Password"]); AXWebServiceRef1.CallContext Cct = new AXWebServiceRef1.CallContext(); Cct.Company = Convert.ToString(ds.Tables[0].Rows[0]["Company"]); Cct.Language = Convert.ToString(ds.Tables[0].Rows[0]["Language"]); string value = obj.InsertHistoryHeaderData(Cct, Convert.ToString(dsGetData.Tables[1].Rows[0]["StillageID"]), Convert.ToString(dsGetData.Tables[0].Rows[0]["SiteID"]), Convert.ToString(dsGetData.Tables[0].Rows[0]["WorkOrderNo"]), Convert.ToString(dsGetData.Tables[0].Rows[0]["ItemId"]), Convert.ToDecimal(dsGetData.Tables[0].Rows[0]["WorkOrderQty"])); // obj.InsertHistoryDetailData(Cct, Convert.ToString(dsGetData.Tables[1].Rows[0]["StillageID"]), "", Convert.ToString(dsGetData.Tables[1].Rows[0]["ActivityName"]), Convert.ToString(dsGetData.Tables[1].Rows[0]["ActivityDesc"]), Convert.ToString(dsGetData.Tables[1].Rows[0]["StillageLocation"]), "", "", "", "", "", "", "No", 0, "", 0, 0, Convert.ToDecimal(dsGetData.Tables[1].Rows[0]["StillageQty"]), Convert.ToString(dsGetData.Tables[1].Rows[0]["UserName"]),"QC Release",0,"","","", Convert.ToString(dsGetData.Tables[1].Rows[0]["WareHouseID"]),0); obj.InsertHistoryDetailData(Cct, Convert.ToString(dsGetData.Tables[1].Rows[0]["StillageID"]), "", Convert.ToString(dsGetData.Tables[1].Rows[0]["ActivityName"]), Convert.ToString(dsGetData.Tables[1].Rows[0]["ActivityDesc"]), Convert.ToString(dsGetData.Tables[1].Rows[0]["StillageLocation"]), "", "", "", "", "", "", "No", 0, "", 0, 0, Convert.ToDecimal(dsGetData.Tables[1].Rows[0]["StillageQty"]), Convert.ToString(dsGetData.Tables[1].Rows[0]["UserName"]), "QC Release", 0, "", "", "", Convert.ToString(dsGetData.Tables[1].Rows[0]["WareHouseID"])); } query = "Sp_LadingPlanWebApi"; dbcommand = new SqlCommand(query, conn); dbcommand.Connection.Open(); dbcommand.CommandType = CommandType.StoredProcedure; dbcommand.Parameters.AddWithValue("@QueryType", "CancelLoadingProcess"); dbcommand.Parameters.AddWithValue("@LoadingId", CL.LPID); dbcommand.Parameters.AddWithValue("@UserId", CL.UserId); dbcommand.CommandTimeout = 0; SqlDataAdapter da = new SqlDataAdapter(dbcommand); DataTable dt = new DataTable(); da.Fill(dt); if (dt.Rows[0]["value"].ToString() == "1") { SM.Status = "Success"; SM.Message = "Loading Cancel Successfully"; } else { SM.Status = "Failure"; SM.Message = "Loading Cancel Failure"; } } catch (Exception Ex) { SM.Status = "Failure"; SM.Message = Ex.Message; } //finally //{ // //dbcommand.Connection.Close(); //} return(SM); }
internal KensaIraiTblDataSet.KensaJokyoListDataTable GetDataBySearchCond( List<string> kensaIraiHoteiKbn, string jokasoSetchishaNm, string jokasoShisetsuNm, string settisyaCd, string kensaIraiDtFrom, string kensaIraiDtTo, string kensaDtFrom, string kensaDtTo) { SqlCommand command = CreateSqlCommand(kensaIraiHoteiKbn, jokasoSetchishaNm, jokasoShisetsuNm, settisyaCd, kensaIraiDtFrom, kensaIraiDtTo, kensaDtFrom, kensaDtTo); SqlDataAdapter adpt = new SqlDataAdapter(command); adpt.SelectCommand.Connection = this.Connection; KensaIraiTblDataSet.KensaJokyoListDataTable dataTable = new KensaIraiTblDataSet.KensaJokyoListDataTable(); adpt.Fill(dataTable); return dataTable; }
//返回时间段编码 public static string GetTimeBlockCode(string timeblock) { System.Data.SqlClient.SqlConnection conn1 = new System.Data.SqlClient.SqlConnection(); conn1.ConnectionString = ConnStr; try { SqlCommand selectCMD1 = new SqlCommand("SELECT timeblockCode FROM timeblock where name='" + timeblock + "'", conn1); selectCMD1.CommandTimeout = 30; SqlDataAdapter dbDA1 = new SqlDataAdapter(); dbDA1.SelectCommand = selectCMD1; conn1.Open(); DataSet dbDS1 = new DataSet(); dbDA1.Fill(dbDS1, "t1"); return dbDS1.Tables[0].Rows[0][0].ToString(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } finally { conn1.Close(); } return ""; }
internal KensaIraiTblDataSet.EnkabutsuIonNodoHikakuListDataTable GetDataBySearchCond( string saisuiDtFrom, string saisuiDtTo, string saisuiinNm) { SqlCommand command = CreateSqlCommand(saisuiDtFrom, saisuiDtTo, saisuiinNm); SqlDataAdapter adpt = new SqlDataAdapter(command); adpt.SelectCommand.Connection = this.Connection; KensaIraiTblDataSet.EnkabutsuIonNodoHikakuListDataTable dataTable = new KensaIraiTblDataSet.EnkabutsuIonNodoHikakuListDataTable(); adpt.Fill(dataTable); return dataTable; }
public void GetFoodTastes() { if (FoodTastesBtnList != null) { FoodTastesBtnList.RemoveAll(); FoodTastesBtnList = null; } FoodTastesBtnList = new FoodTastesButtonList(Panel2, 75, 45, Color.LightBlue, Color.FromArgb(255, 128, 128), 3); System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(); conn.ConnectionString = rms_var.ConnStr; try { SqlCommand selectCMD = new SqlCommand(); selectCMD.Connection = conn; selectCMD.CommandText = "SELECT name FROM foodtaste"; selectCMD.CommandTimeout = 30; SqlDataAdapter dbDA = new SqlDataAdapter(); dbDA.SelectCommand = selectCMD; conn.Open(); DataSet dbDS = new DataSet(); dbDA.Fill(dbDS, "t"); for (int i = 0; i <= dbDS.Tables[0].Rows.Count - 1; i++) { FoodTastesBtnList.AddNewButton(dbDS.Tables[0].Rows[i]["name"].ToString()); } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } finally { conn.Close(); } }
private void LoadFoodTaste() { System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(); conn.ConnectionString = rms_var.ConnStr; try { SqlCommand selectCMD = new SqlCommand("SELECT name FROM foodtaste", conn); selectCMD.CommandTimeout = 30; SqlDataAdapter dbDA = new SqlDataAdapter(); dbDA.SelectCommand = selectCMD; conn.Open(); DataSet dbDS = new DataSet(); dbDA.Fill(dbDS, "t"); //clbTaste.Items.Clear() //For i As Integer = 0 To dbDS.Tables(0).Rows.Count - 1 //clbTaste.Items.Add(dbDS.Tables(0).Rows(i).Item(0), False) //Next } catch (Exception ex) { MessageBox.Show(ex.ToString()); } finally { conn.Close(); } }
private void buttonSearch_Click(object sender, EventArgs e) { SqlConnection sqlconn = new SqlConnection(); sqlconn.ConnectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\pc\source\repos\ManagementSystemSolution\StudentMS\DatabaseStudent.mdf;Integrated Security=True;Connect Timeout=30"; if (textBoxSearch.Text == "") { string sqlquery = "SELECT * FROM [dbo].[Student]"; try { sqlconn.Open(); SqlDataAdapter sda = new SqlDataAdapter(sqlquery, sqlconn); DataSet ds = new DataSet(); sda.Fill(ds, "Student"); dataGridViewSearch.DataSource = ds.Tables["Student"]; } catch (SqlException error) { MessageBox.Show(error.Message, "操作資料庫出错!", MessageBoxButtons.OK, MessageBoxIcon.Error); textBoxSearch.Focus(); } } else { string sqlquery; switch (comboBox1.SelectedItem.ToString()) { case "學號": sqlquery = String.Format("select * from [dbo].[Student] where studentNo = '{0}' ", textBoxSearch.Text); try { sqlconn.Open(); DataSet ds = new DataSet(); SqlDataAdapter sda = new SqlDataAdapter(sqlquery, sqlconn); sda.Fill(ds, "Student"); dataGridViewSearch.DataSource = ds.Tables["Student"]; if (ds.Tables[0].Rows.Count == 0) { MessageBox.Show("沒有查到相關訊息,請檢查查詢條件!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } } catch (SqlException error) { MessageBox.Show(error.Message, "操作資料庫出错!", MessageBoxButtons.OK, MessageBoxIcon.Error); textBoxSearch.Focus(); } finally { sqlconn.Close(); } break; case "姓名": sqlquery = String.Format("select * from [dbo].[Student] where StudentName LIKE N'%{0}%' ", textBoxSearch.Text); try { sqlconn.Open(); DataSet ds = new DataSet(); SqlDataAdapter sda = new SqlDataAdapter(sqlquery, sqlconn); sda.Fill(ds, "Student"); dataGridViewSearch.DataSource = ds.Tables["Student"]; if (ds.Tables[0].Rows.Count == 0) { MessageBox.Show("沒有查到相關訊息,請檢查查詢條件!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } } catch (SqlException error) { MessageBox.Show(error.Message, "操作資料庫出错!", MessageBoxButtons.OK, MessageBoxIcon.Error); textBoxSearch.Focus(); } finally { sqlconn.Close(); } break; case "班級": sqlquery = String.Format("select * from [dbo].[Student] where ClassID = '{0}' ", textBoxSearch.Text); try { sqlconn.Open(); DataSet ds = new DataSet(); SqlDataAdapter sda = new SqlDataAdapter(sqlquery, sqlconn); sda.Fill(ds, "Student"); dataGridViewSearch.DataSource = ds.Tables["Student"]; if (ds.Tables[0].Rows.Count == 0) { MessageBox.Show("沒有查到相關訊息,請檢查查詢條件!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } } catch (SqlException error) { MessageBox.Show(error.Message, "操作資料庫出错!", MessageBoxButtons.OK, MessageBoxIcon.Error); textBoxSearch.Focus(); } finally { sqlconn.Close(); } break; case "科系": sqlquery = String.Format("select * from [dbo].[Student] where StudentDep LIKE N'%{0}%' ", textBoxSearch.Text); try { sqlconn.Open(); DataSet data = new DataSet(); SqlDataAdapter sda = new SqlDataAdapter(sqlquery, sqlconn); sda.Fill(data, "Student"); dataGridViewSearch.DataSource = data.Tables["Student"]; if (data.Tables[0].Rows.Count == 0) { MessageBox.Show("沒有查到相關訊息,請檢查查詢條件!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } } catch (SqlException error) { MessageBox.Show(error.Message, "操作資料庫出错!", MessageBoxButtons.OK, MessageBoxIcon.Error); textBoxSearch.Focus(); } finally { sqlconn.Close(); } break; } } }
public static bool EmpIdExists(string empid) { System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(); conn.ConnectionString = ConnStr; try { SqlCommand selectCMD = new SqlCommand("SELECT * FROM Employee where " + "EmpID=" + "'" + empid + "'", conn); selectCMD.CommandTimeout = 30; SqlDataAdapter dbDA = new SqlDataAdapter(); dbDA.SelectCommand = selectCMD; conn.Open(); DataSet dbDS = new DataSet(); dbDA.Fill(dbDS, "t"); if (dbDS.Tables[0].Rows.Count > 0) //该雇员存在 { return true; } else //雇员不存在 { return false; } } catch (Exception ex) { MessageBox.Show(ex.ToString()); return false; } finally { conn.Close(); } }
protected void btnLogin_Click(object sender, EventArgs e) { try { DataTable dt = new DataTable(); SqlCommand cmd = new SqlCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "usp_RegistrationLogin"; cmd.Parameters.AddWithValue("@UserName", txtUserName.Text); cmd.Parameters.AddWithValue("@Password", crpt.Encrypt(txtPassword.Text)); cmd.Connection = conn; if (conn.State == ConnectionState.Closed) { conn.Open(); } SqlDataAdapter adp = new SqlDataAdapter(cmd); adp.Fill(dt); conn.Close(); if (dt.Rows.Count > 0) { if (Convert.ToBoolean(dt.Rows[0]["IsVerified"]) == true) { Session["LoginUser"] = dt.Rows[0]["FirstName"]; Session["UserId"] = dt.Rows[0]["ID"]; if (Session["Favourite"] != null) { int UserID = Convert.ToInt32(dt.Rows[0]["ID"]); string MLSID = Convert.ToString(Session["Favourite"]); int result = clsobj.Insert_Favourite(UserID, MLSID); if ((Session["FeatureType"]) != null) { Response.Redirect("~/featureListing.aspx", false); } else { Response.Redirect("~/Search.aspx?PropertyType=" + Convert.ToString(Session["Type"]), false); } } else if (Convert.ToString(Session["MLSID"]) != "" && Convert.ToString(Session["Type"]) != "") { Response.Redirect("PropertyDetails.aspx?MLSID=" + Convert.ToString(Session["MLSID"]) + "&PropertyType=" + Convert.ToString(Session["Type"]), false); } else { Response.Redirect("Home.aspx", false); } } else { lblerror.Text = "Verify your Email to Login"; } } else { txtUserName.Text = ""; lblerror.Text = "Incorrect Username or Password"; } } catch (Exception ex) { throw ex; } }
public DataTable GetDatasetTable(string p_sqlCommand) { SqlDataAdapter daRecords = new SqlDataAdapter(p_sqlCommand, RMSGlobal.m_connectionString); DataTable dsRecords = new DataTable(); daRecords.Fill(dsRecords); return dsRecords; }
private void button1_Click(object sender, EventArgs e) { int be; bool bil; bool bil1; bool bil2; bool bil3; int count = 0; Regex Sample = new Regex("^.*[^A-zА-яiЁё].*$"); Regex Sample_1 = new Regex("^[0-9]+$"); Match res1 = Sample.Match(textBox2.Text); Match res2 = Sample.Match(textBox3.Text); Match res3 = Sample.Match(textBox4.Text); Match res4 = Sample_1.Match(textBox5.Text); Match res5 = Sample_1.Match(textBox1.Text); int a; int b; string query = ""; string res = ""; if (radioButton1.Checked) { res = "row(s) selected"; query = "select Picture.IDPicture, Picture.Place,Picture.Money,Picture.Author,Picture.Name,Name_Jenre from dbo.Picture join dbo.Jenre On ID_Jenre=ID_J"; if (checkBox1.Checked && int.TryParse(textBox1.Text, out a)) { query += " where Picture.IDPicture = " + textBox1.Text; } else if (checkBox2.Checked && comboBox1.Text != "") { query += " where Name_Jenre=N'" + comboBox1.Text + "'"; } } if (radioButton2.Checked && textBox2.Text != "" && textBox3.Text != "" && textBox4.Text != "" && textBox5.Text != "" && comboBox1.Text != "" && int.TryParse(textBox3.Text, out a)) { res = "1 row(s) inserted"; query = "INSERT INTO dbo.Picture ([Place],[Money],[Author],[Name],[ID_J]) VALUES (N'" + textBox2.Text + "',N'" + textBox3.Text + "',N'" + textBox4.Text + "',N'" + textBox5.Text + "',(select ID_Jenre from dbo.Jenre where Name_Jenre = N'" + comboBox1.Text + "'))"; bil = true; } else { bil = false; } if (radioButton3.Checked && textBox1.Text != "" && textBox2.Text != "" && textBox3.Text != "" && textBox4.Text != "" && textBox5.Text != "" && comboBox1.Text != "" && int.TryParse(textBox3.Text, out a) && int.TryParse(textBox1.Text, out be)) { bil1 = true; res = "1 row(s) updated"; query = "UPDATE dbo.Picture SET Place = N'" + textBox2.Text + "',ID_J = (select ID_Jenre from dbo.Jenre where Name_Jenre = N'" + comboBox1.Text + "'), Money = N'" + textBox3.Text + "',Author = N'" + textBox4.Text + "',Name = N'" + textBox5.Text + "' WHERE IDPicture =" + textBox1.Text; } else { bil1 = false; } if (radioButton4.Checked && textBox1.Text != "" && int.TryParse(textBox1.Text, out a)) { bil2 = true; res = "1 row(s) deleted"; query = "Delete from dbo.Picture where IDPicture =" + textBox1.Text; } else { bil2 = false; } if (bil||bil1||bil2) { using (SqlConnection conn = new SqlConnection(connect)) { conn.Open(); SqlDataAdapter adapt = new SqlDataAdapter(query, conn); DataSet data = new DataSet(); adapt.Fill(data); dataGridView1.Visible = true; if (radioButton1.Checked) { dataGridView1.DataSource = data.Tables[0]; res = res.Insert(0, data.Tables[0].Rows.Count.ToString()); textBox6.Text = res; } else { adapt = new SqlDataAdapter("select Picture.IDPicture, Picture.Place, Picture.Money, Picture.Author, Picture.Name, Name_Jenre from dbo.Picture join dbo.Jenre On ID_Jenre = ID_J;", connect); data = new DataSet(); adapt.Fill(data); dataGridView1.DataSource = data.Tables[0]; textBox6.Text = res; } conn.Close(); } Categ(); textBox1.Text = ""; textBox2.Text = ""; textBox3.Text = ""; textBox4.Text = ""; textBox5.Text = ""; comboBox1.Text = ""; }else { MessageBox.Show("Помилка"); } }
/// <summary> /// Returns the dataset of the query. /// </summary> /// <param name="p_sqlCommand"></param> /// <returns></returns> public DataSet GetDataset(string p_sqlCommand) { SqlDataAdapter daRecords = new SqlDataAdapter(p_sqlCommand, RMSGlobal.m_serverConnectionString); DataSet dsRecords = new DataSet(); daRecords.Fill(dsRecords); return dsRecords; }
//未结账是否允许删单据和菜品 df public static bool AllowDeleteBillFood() { System.Data.SqlClient.SqlConnection conn1 = new System.Data.SqlClient.SqlConnection(); conn1.ConnectionString = ConnStr; try { SqlCommand selectCMD1 = new SqlCommand(); selectCMD1.Connection = conn1; selectCMD1.CommandText = "SELECT count(*) FROM op_acs where operatorid='" + opinfo.OpID + "' and access='df'"; selectCMD1.CommandTimeout = 30; SqlDataAdapter dbDA1 = new SqlDataAdapter(); dbDA1.SelectCommand = selectCMD1; conn1.Open(); DataSet dbDS1 = new DataSet(); dbDA1.Fill(dbDS1, "t"); if (Convert.ToInt32(dbDS1.Tables[0].Rows[0][0]) > 0) { return true; } else { return false; } } catch (Exception ex) { MessageBox.Show(ex.ToString()); return false; } finally { conn1.Close(); } }
public void rightmain() { string config = System.Web.Configuration.WebConfigurationManager .ConnectionStrings["ProjectConnection"].ConnectionString; SqlConnection connectionID = new SqlConnection(config); SqlCommand commandID = new SqlCommand($"Select * FROM Country ", connectionID); SqlDataAdapter loginAdapterID = new SqlDataAdapter(commandID); DataTable userID = new DataTable(); loginAdapterID.Fill(userID); //判斷Request.QueryString["id"]有沒有資料,因為一開始進網頁時要直接抓第一筆id,但網址一開始載入會沒有id資料 if (string.IsNullOrEmpty($"{Request.QueryString["id"]}")) { //抓名字(標題) string id = Request.QueryString["id"] ?? $"{userID.Rows[0]["id"].ToString()}"; SqlConnection rconnection = new SqlConnection(config); SqlCommand Rcommand = new SqlCommand($"Select * FROM Country where id =@id ", rconnection); Rcommand.Parameters.Add("@id", SqlDbType.NVarChar); //設定參數資料型態 Rcommand.Parameters["@id"].Value = id; //接id的值變成參數的值 SqlDataAdapter loginAdapter = new SqlDataAdapter(Rcommand); DataTable user = new DataTable(); loginAdapter.Fill(user); rightCountryName.InnerHtml = user.Rows[0]["name"].ToString(); rightCountryName2.InnerHtml = user.Rows[0]["name"].ToString(); //內容 SqlConnection connectionMain = new SqlConnection(config); SqlCommand commandMain = new SqlCommand($"Select * FROM Dealers where Cid=@id ", connectionMain); commandMain.Parameters.Add("@id", SqlDbType.NVarChar); //設定參數資料型態 commandMain.Parameters["@id"].Value = id; //接id的值變成參數的值 connectionMain.Open(); SqlDataReader rereader = commandMain.ExecuteReader(); Repeater_Main.DataSource = rereader;//repeater的資料來源是從rereader來 Repeater_Main.DataBind();//執行繫結 connectionMain.Close(); } else { //抓名字(標題) SqlConnection rconnection = new SqlConnection(config); SqlCommand Rcommand = new SqlCommand($"Select * FROM Country where id =@id ", rconnection); Rcommand.Parameters.Add("@id", SqlDbType.NVarChar); //設定參數資料型態 Rcommand.Parameters["@id"].Value = Request.QueryString["id"]; //接id的值變成參數的值 SqlDataAdapter loginAdapter = new SqlDataAdapter(Rcommand); DataTable user = new DataTable(); loginAdapter.Fill(user); rightCountryName.InnerHtml = user.Rows[0]["name"].ToString(); rightCountryName2.InnerHtml = user.Rows[0]["name"].ToString(); // 繫結內容資料 SqlConnection connectionMain = new SqlConnection(config); SqlCommand commandMain = new SqlCommand($"Select * FROM Dealers where Cid=@id ", connectionMain); commandMain.Parameters.Add("@id", SqlDbType.NVarChar); //設定參數資料型態 commandMain.Parameters["@id"].Value = Request.QueryString["id"]; //接id的值變成參數的值 connectionMain.Open(); SqlDataReader rereader = commandMain.ExecuteReader(); Repeater_Main.DataSource = rereader;//repeater的資料來源是從rereader來 Repeater_Main.DataBind();//執行繫結 connectionMain.Close(); } }
public string getSubchildes(int x, string UTID) { SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Con"].ConnectionString); string SubString = ""; int k = 0; string str = "select * from VW_MENU where PID=" + x + " order by FormID"; SqlDataAdapter da = new SqlDataAdapter(str, Conn); DataTable dtA = new DataTable(); da.Fill(dtA); int noOfChild = dtA.Rows.Count; for (int i = 0; i < dtA.Rows.Count; i++) { if (dtA.Rows.Count == 0) { SubString = ""; } else { string hasChild = getchildes(Convert.ToInt32(dtA.Rows[i]["id"].ToString()), UTID); if (hasChild == "") { string ActiveChild = ""; string path = dtA.Rows[i]["Path"].ToString(); path = path.ToLowerInvariant(); if (PageURL.IndexOf(path) != -1) { ActiveChild = " class=active"; } SubString = SubString + "<li" + ActiveChild + ">"; SubString = SubString + "<a href='" + "../" + dtA.Rows[i]["Path"].ToString() + "" + "'>"; SubString = SubString + "" + dtA.Rows[i]["FormTitle"].ToString() + "</a>"; SubString = SubString + "</li>"; } else { string ActiveParent = ""; if (hasChild.IndexOf("class=active") != -1) { ActiveParent = " active"; } string TotalChild = hasChild; TotalChild = TotalChild.Substring(0, TotalChild.IndexOf("`")); hasChild = hasChild.Substring(hasChild.IndexOf("`") + 1); SubString = SubString + "<li class='has-sub" + ActiveParent + "'>"; SubString = SubString + "<a href='javascript:;'>"; SubString = SubString + "<b class='caret pull-right'></b>"; SubString = SubString + "<span class='badge pull-right'>" + TotalChild + "</span>"; SubString = SubString + "<i class='fa " + dtA.Rows[i]["ICON_CSS"].ToString() + "'></i>"; SubString = SubString + "<span>" + dtA.Rows[i]["FormTitle"].ToString() + "</span>"; SubString = SubString + "</a>"; SubString = SubString + "<ul class='sub-menu'>"; SubString = SubString + hasChild; SubString = SubString + "</ul></li>"; } } } if (noOfChild > 0) { SubString = noOfChild + "`" + SubString; } return(SubString); }
public DataTable Driverlist(driverdetails d) { SqlConnection conn = new SqlConnection(); try { conn.ConnectionString = ConfigurationManager.ConnectionStrings["btposdb"].ToString(); SqlCommand cmd = new SqlCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "HVInsUpddrivers"; cmd.Connection = conn; SqlParameter ff = new SqlParameter("@flag", SqlDbType.VarChar); ff.Value = d.flag; cmd.Parameters.Add(ff); SqlParameter i = new SqlParameter("@DId", SqlDbType.Int); i.Value = d.DId; cmd.Parameters.Add(i); SqlParameter di = new SqlParameter("@CountryId", SqlDbType.VarChar, 50); di.Value = d.Country; cmd.Parameters.Add(di); SqlParameter n = new SqlParameter("@NAme", SqlDbType.VarChar, 50); n.Value = d.Name; cmd.Parameters.Add(n); SqlParameter r = new SqlParameter("@Address", SqlDbType.VarChar, 50); r.Value = d.Address; cmd.Parameters.Add(r); SqlParameter f = new SqlParameter("@PAddress", SqlDbType.VarChar, 50); f.Value = d.PermanentAddress; cmd.Parameters.Add(f); SqlParameter s = new SqlParameter("@Pin", SqlDbType.VarChar, 50); s.Value = d.Pin; cmd.Parameters.Add(s); SqlParameter g = new SqlParameter("@PPin", SqlDbType.VarChar, 50); g.Value = d.PermanentPin; cmd.Parameters.Add(g); SqlParameter j = new SqlParameter("@PMobNo", SqlDbType.VarChar, 255); j.Value = d.Mobilenumber; cmd.Parameters.Add(j); SqlParameter fr = new SqlParameter("@FirstName", SqlDbType.VarChar, 50); fr.Value = d.FirstName; cmd.Parameters.Add(fr); SqlParameter lk = new SqlParameter("@LastName", SqlDbType.VarChar, 50); lk.Value = d.LastName; cmd.Parameters.Add(lk); SqlParameter ed = new SqlParameter("@EmailId", SqlDbType.VarChar, 50); ed.Value = d.EmailId; cmd.Parameters.Add(ed); SqlParameter dc = new SqlParameter("@DriverCode", SqlDbType.VarChar); dc.Value = d.drivercode; cmd.Parameters.Add(dc); SqlParameter pr = new SqlParameter("@Photo", SqlDbType.VarChar); pr.Value = d.Photo; cmd.Parameters.Add(pr); SqlParameter bg = new SqlParameter("@BloodGroup", SqlDbType.VarChar); bg.Value = d.BloodGroup; cmd.Parameters.Add(bg); SqlParameter df = new SqlParameter("@DOB", SqlDbType.DateTime); df.Value = d.DOB; cmd.Parameters.Add(df); SqlParameter dfs = new SqlParameter("@DOJ", SqlDbType.DateTime); dfs.Value = d.DOJ; cmd.Parameters.Add(dfs); SqlParameter sd = new SqlParameter("@Status", SqlDbType.Int); sd.Value = d.Status; cmd.Parameters.Add(sd); SqlParameter vf = new SqlParameter("@VehicleGroupId", SqlDbType.Int); vf.Value = d.VehicleGroup; cmd.Parameters.Add(vf); SqlParameter gf = new SqlParameter("@IsVerified", SqlDbType.Int); gf.Value = d.IsVerified; cmd.Parameters.Add(gf); SqlParameter hd = new SqlParameter("@IsApproved", SqlDbType.Int); hd.Value = d.IsApproved; cmd.Parameters.Add(hd); SqlParameter cdd = new SqlParameter("@CurrentStateId", SqlDbType.Int); cdd.Value = d.CurrentStateId; cmd.Parameters.Add(cdd); SqlParameter ptt = new SqlParameter("@PaymentTypeId", SqlDbType.Int); ptt.Value = d.PaymentTypeId; cmd.Parameters.Add(ptt); DataTable dt = new DataTable(); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(dt); return dt; } catch (Exception ex) { if (conn != null && conn.State == ConnectionState.Open) { conn.Close(); } throw ex; } }
public void loaddl() { SqlConnection kn = new SqlConnection(@"Data Source=DESKTOP-RGHAECC;Initial Catalog=SucKhoeNhanVien_PNT;Integrated Security=True"); kn.Open(); string sql = "SELECT * from NhanVien, Khoa where NhanVien.Khoa = Khoa.IdKhoa And NhanVien.MaNV like N'" + txtmanv.Text + "' "; // Lấy dữ liệu hành chính SqlCommand commandsql = new SqlCommand(sql, kn); //thực thi các chức năng câu lệnh trong sql SqlDataAdapter com = new SqlDataAdapter(commandsql); // vận chuyển dữ liệu DataTable table = new DataTable(); //tạo một bảng ảo trong hện thống com.Fill(table); // đổ dữ liệu vào bảng ảo int idnhanvien = (int)table.Rows[0].ItemArray[0]; txtidnv.Text = idnhanvien.ToString(); txttennv.Text = (string)table.Rows[0]["TenNV"]; txtphongban.Text = (string)table.Rows[0]["TenKhoa"]; txtmanv.Text = (string)table.Rows[0]["MaNV"]; //Lấy dữ liệu tiền sử bệnh //string sqlkhamtheluc = "SELECT * from LichSuBenh,TienSuBenh where LichSuBenh.Id_LichSuBenh = TienSuBenh.Id_LichSuBenh And LichSuBenh.IdNhanVien = '" + txtidnv.Text + "' And LichSuBenh.NgayKham = '" + dateTimePickerSKNV.Value.ToString("yyyy-MM-dd") + "' "; string sqlkhamtheluc = "SELECT * from LichSuBenh,TienSuBenh where LichSuBenh.Id_LichSuBenh = TienSuBenh.Id_LichSuBenh And LichSuBenh.IdNhanVien = '" + txtidnv.Text + "'"; SqlCommand cmdsqlkhamtheluc = new SqlCommand(sqlkhamtheluc, kn); SqlDataAdapter comsqlkhamtheluc = new SqlDataAdapter(cmdsqlkhamtheluc); DataTable tableTSB = new DataTable(); comsqlkhamtheluc.Fill(tableTSB); txtchieucao.Text = tableTSB.Rows[0]["ChieuCao"].ToString(); txtcannang.Text = tableTSB.Rows[0]["CanNang"].ToString(); txtBMI.Text = tableTSB.Rows[0]["ChisoBMI"].ToString(); txtmach.Text = (string)tableTSB.Rows[0]["Mach"].ToString(); txthuyetap.Text = (string)tableTSB.Rows[0]["HuyetAp"]; txtPLTL.Text = (string)tableTSB.Rows[0]["PhanLoai"]; txtTSB.Text = (string)tableTSB.Rows[0]["TienSuBenh"]; // Lấy dữ liệu Lâm sàng nội khoa string sqlLSnoikhoa = "SELECT * from LichSuBenh,LamSang_NoiKhoa where LichSuBenh.Id_LichSuBenh = LamSang_NoiKhoa.Id_LichSuBenh And LichSuBenh.IdNhanVien = '" + txtidnv.Text + "' And LichSuBenh.NgayKham = '" + dateTimePickerSKNV.Value.ToString("yyyy-MM-dd") + "' "; SqlCommand cmdsqlLSnoikhoa = new SqlCommand(sqlLSnoikhoa, kn); SqlDataAdapter comsqlLSnoikhoa = new SqlDataAdapter(cmdsqlLSnoikhoa); DataTable tableLSNK = new DataTable(); comsqlLSnoikhoa.Fill(tableLSNK); txtPLtuanhoan.Text = (string)tableLSNK.Rows[0]["PL_TuanHoan"]; txtPLhohap.Text = (string)tableLSNK.Rows[0]["PL_HoHap"]; txtPLtieuhoa.Text = (string)tableLSNK.Rows[0]["PL_TieuHoa"]; txtPLthan.Text = (string)tableLSNK.Rows[0]["PL_ThanTietNieu"]; txtPLNoiTiet.Text = (string)tableLSNK.Rows[0]["PL_NoiTiet"]; txtBSKham.Text = (string)tableLSNK.Rows[0]["GhiChu"]; // Lấy dữ liệu lâm sàng da liễu string sqlLSDL = "SELECT * from LamSang_DaLieu, LichSuBenh where LichSuBenh.Id_LichSuBenh = LamSang_DaLieu.Id_LichSuBenh And LichSuBenh.IdNhanVien = '" + txtidnv.Text + "' And LichSuBenh.NgayKham = '" + dateTimePickerSKNV.Value.ToString("yyyy-MM-dd") + "' "; SqlCommand cmdsqlLSDL = new SqlCommand(sqlLSDL, kn); SqlDataAdapter comsqlLSDL = new SqlDataAdapter(cmdsqlLSDL); DataTable tblLSDL = new DataTable(); comsqlLSDL.Fill(tblLSDL); txtPLxuongkhop.Text = (string)tblLSDL.Rows[0]["PL_CoXuongKhop"]; txtPLthankinh.Text = (string)tblLSDL.Rows[0]["PL_ThanKinh"]; txtPLtamthan.Text = (string)tblLSDL.Rows[0]["PL_TamThan"]; txtPLdalieu.Text = (string)tblLSDL.Rows[0]["PL_DaLieu"]; txtBSkhamDL.Text = (string)tblLSDL.Rows[0]["TenBS"]; // Lấy dữ liệu lâm sàng tai mũi họng string sqlLSTMH = "SELECT * from LichSuBenh,LamSang_TaiMuiHong where LichSuBenh.Id_LichSuBenh = LamSang_TaiMuiHong.Id_LichSuBenh And LichSuBenh.IdNhanVien = '" + txtidnv.Text + "' And LichSuBenh.NgayKham = '" + dateTimePickerSKNV.Value.ToString("yyyy-MM-dd") + "' "; SqlCommand cmdsqlLSTMH = new SqlCommand(sqlLSTMH, kn); SqlDataAdapter comsqlLSTMH = new SqlDataAdapter(cmdsqlLSTMH); DataTable tblLSTMH = new DataTable(); comsqlLSTMH.Fill(tblLSTMH); txttraithuong.Text = (string)tblLSTMH.Rows[0]["TaiTrai_NoiThuong"]; txtphaithuong.Text = (string)tblLSTMH.Rows[0]["TaiPhai_NoiThuong"]; txtphaitham.Text = (string)tblLSTMH.Rows[0]["TaiPhai_NoiTham"]; txttraitham.Text = (string)tblLSTMH.Rows[0]["TaiTrai_NoiTham"]; txtBenhMHkhac.Text = (string)tblLSTMH.Rows[0]["BenhTMH"]; txtPLmuihong.Text = (string)tblLSTMH.Rows[0]["PhanLoai"]; txtBSmuihong.Text = (string)tblLSTMH.Rows[0]["BSKham"]; // Lấy dữ liệu lâm sàng răng hàm mặt string sqlLSRHM = "SELECT * from LamSang_RangHam,LichSuBenh where LichSuBenh.Id_LichSuBenh = LamSang_RangHam.Id_LichSuBenh And LichSuBenh.IdNhanVien = '" + txtidnv.Text + "' And LichSuBenh.NgayKham = '" + dateTimePickerSKNV.Value.ToString("yyyy-MM-dd") + "' "; SqlCommand cmdsqlLSRHM = new SqlCommand(sqlLSRHM, kn); SqlDataAdapter comsqlLSRHM = new SqlDataAdapter(cmdsqlLSRHM); DataTable tblLSRHM = new DataTable(); comsqlLSRHM.Fill(tblLSRHM); txthamtren.Text = (string)tblLSRHM.Rows[0]["HamTren"]; txthamduoi.Text = (string)tblLSRHM.Rows[0]["HamDuoi"]; txtBenhrangkhac.Text = (string)tblLSRHM.Rows[0]["BenhVeRang"]; txtPLrang.Text = (string)tblLSRHM.Rows[0]["PhanLoai"]; txtBSRang.Text = (string)tblLSRHM.Rows[0]["BSKham"]; // Lấy dữ liệu lâm sàng mắt string sqlLSMat = "Select * from LichSuBenh,LamSang_Mat where LichSuBenh.Id_LichSuBenh = LamSang_Mat.Id_LichSuBenh And LichSuBenh.IdNhanVien = '" + txtidnv.Text + "' And LichSuBenh.NgayKham = '" + dateTimePickerSKNV.Value.ToString("yyyy-MM-dd") + "' "; SqlCommand cmdsqlMat = new SqlCommand(sqlLSMat, kn); SqlDataAdapter comsqlMat = new SqlDataAdapter(cmdsqlMat); DataTable tblLSMat = new DataTable(); comsqlMat.Fill(tblLSMat); txtkokinhphai.Text = (string)tblLSMat.Rows[0]["KhongKinhPhai"]; txtkokinhtrai.Text = (string)tblLSMat.Rows[0]["KhongKinhTrai"]; txtcokinhphai.Text = (string)tblLSMat.Rows[0]["KinhPhai"]; txtcokinhtrai.Text = (string)tblLSMat.Rows[0]["KinhTrai"]; txtbenhmatkhac.Text = (string)tblLSMat.Rows[0]["BenhMat"]; txtPLmat.Text = (string)tblLSMat.Rows[0]["PhanLoai"]; txtBSmat.Text = (string)tblLSMat.Rows[0]["BacSiKham"]; // Lấy dữ liệu phụ khoa string sqlLSPK = "Select * from LichSuBenh,LamSang_PhuKhoa where LichSuBenh.Id_LichSuBenh = LamSang_PhuKhoa.Id_LichSuBenh And LichSuBenh.IdNhanVien = '" + txtidnv.Text + "' And LichSuBenh.NgayKham = '" + dateTimePickerSKNV.Value.ToString("yyyy-MM-dd") + "' "; SqlCommand cmdsqlLSPK = new SqlCommand(sqlLSPK, kn); SqlDataAdapter comsqlLSPK = new SqlDataAdapter(cmdsqlLSPK); DataTable tblLSPK = new DataTable(); comsqlLSPK.Fill(tblLSPK); txtpara.Text = (string)tblLSPK.Rows[0]["SanPK"]; txtPLphukhoa.Text = (string)tblLSPK.Rows[0]["PhanLoai"]; //txtghichu.Text = (string)tblLSPK.Rows[0]["GhiChu"]; txtghichu.Text = (tblLSPK.Rows[0]["GhiChu"] == null) ? string.Empty : tblLSPK.Rows[0]["GhiChu"].ToString(); txtBSphukhoa.Text = (string)tblLSPK.Rows[0]["BSKham"]; //Lấy dữ liệu cận lâm sàng string sqlCLS = "select* from LichSuBenh, CanLamSang where LichSuBenh.Id_LichSuBenh = CanLamSang.Id_LichSuBenh And LichSuBenh.IdNhanVien = '" + txtidnv.Text + "' And LichSuBenh.NgayKham = '" + dateTimePickerSKNV.Value.ToString("yyyy-MM-dd") + "' "; SqlCommand cmdsqlCLS = new SqlCommand(sqlCLS, kn); SqlDataAdapter comsqlCLS = new SqlDataAdapter(cmdsqlCLS); DataTable tblCLS = new DataTable(); comsqlCLS.Fill(tblCLS); txtkqCLS.Text = (string)tblCLS.Rows[0]["KetQua"]; txtdanhgia.Text = (string)tblCLS.Rows[0]["DanhGia"]; txtBSCLS.Text = (string)tblCLS.Rows[0]["BsKham"]; // Lấy dữ liệu lịch sử bệnh string sqlLSB = "select* from LichSuBenh where LichSuBenh.IdNhanVien = '" + txtidnv.Text + "' And LichSuBenh.NgayKham = '" + dateTimePickerSKNV.Value.ToString("yyyy-MM-dd") + "' "; SqlCommand cmdsqlLSB = new SqlCommand(sqlLSB, kn); SqlDataAdapter comsqlLSB = new SqlDataAdapter(cmdsqlLSB); DataTable tblLSB = new DataTable(); comsqlLSB.Fill(tblLSB); //var date = (DateTime)tblLSB.Rows[0]["NgayKham"]; //dateTimePickerSKNV.Value = date; txtPLSucKhoe.Text = (string)tblLSB.Rows[0]["PhanLoaiSK"]; txtbenhkhac.Text = (string)tblLSB.Rows[0]["BenhKhac"]; txtBSKetLuan.Text = (string)tblLSB.Rows[0]["GhiChu"]; }
public DataTable SaveDriverDoc(DriverDocuments a) { //connect to database SqlConnection conn = new SqlConnection(); DataTable dt = new DataTable(); try { //connetionString="Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password" conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["btposdb"].ToString(); SqlCommand cmd = new SqlCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "HVInsUpdDelDriverDocs"; cmd.Connection = conn; SqlParameter id = new SqlParameter("@Id", SqlDbType.Int); id.Value = a.Id; cmd.Parameters.Add(id); SqlParameter AssetId = new SqlParameter("@DriverId", SqlDbType.Int); AssetId.Value = a.DriverId; cmd.Parameters.Add(AssetId); SqlParameter Gid = new SqlParameter("@FileName", SqlDbType.VarChar, 100); Gid.Value = a.docName; cmd.Parameters.Add(Gid); SqlParameter rootassetid = new SqlParameter("@DocTypeId", SqlDbType.Int); rootassetid.Value = a.docTypeId; cmd.Parameters.Add(rootassetid); SqlParameter AsstMDLHierarID = new SqlParameter("@UpdatedById", SqlDbType.Int); AsstMDLHierarID.Value = a.UpdatedById; cmd.Parameters.Add(AsstMDLHierarID); SqlParameter assetModelId = new SqlParameter("@ExpiryDate", SqlDbType.Date); assetModelId.Value = a.expiryDate; cmd.Parameters.Add(assetModelId); SqlParameter LocationId = new SqlParameter("@DueDate", SqlDbType.Date); LocationId.Value = a.dueDate; cmd.Parameters.Add(LocationId); SqlParameter parentid = new SqlParameter("@FileContent", SqlDbType.VarChar); parentid.Value = a.docContent; cmd.Parameters.Add(parentid); SqlParameter flag = new SqlParameter("@change", SqlDbType.VarChar); flag.Value = a.insupddelflag; cmd.Parameters.Add(flag); SqlParameter loggedinUserId1 = new SqlParameter("@loggedinUserId", SqlDbType.Int); loggedinUserId1.Value = a.UpdatedById; cmd.Parameters.Add(loggedinUserId1); SqlParameter doc = new SqlParameter("@DocumentNo", SqlDbType.VarChar, 50); doc.Value = a.DocumentNo; cmd.Parameters.Add(doc); SqlParameter doc2 = new SqlParameter("@DocumentNo2", SqlDbType.VarChar, 50); doc2.Value = a.DocumentNo2; cmd.Parameters.Add(doc2); SqlParameter ver = new SqlParameter("@IsVerified", SqlDbType.Int); ver.Value = a.isVerified; cmd.Parameters.Add(ver); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(dt); return dt; } catch (Exception ex) { if (conn != null && conn.State == ConnectionState.Open) { conn.Close(); } throw ex; } }
protected void Page_Load(object sender, EventArgs e) { string var_id = Request.QueryString["id"]; string action_id = Request.QueryString["action"]; //manage page content if (var_id != null) { LabsList_Form.Visible = false; if (var_id == "new") { if (Session["teacher"] == null) { Response.Redirect("~/Content/Labs.aspx"); } NewLab_Form.Visible = true; LabsList.Visible = false; DisplayLab_Content.Visible = true; } else { if (Session["teacher"] != null) { DisplayLab_AddLab.Visible = true; DisplayLab_DeleteLab.Visible = true; DisplayLab_AddPage.Visible = true; DisplayLab_EditPage.Visible = true; DisplayLab_DeletePage.Visible = true; } string SQL_SELECT = "SELECT * FROM " + LabsDB + " WHERE LabIndex=" + var_id; SqlCommand CMD_SELECT = new SqlCommand(SQL_SELECT, DB_Connection); CMD_SELECT.CommandType = CommandType.Text; string SQL_SELECT_A = "SELECT count(*) FROM " + LabsDB + " WHERE LabIndex=" + var_id; SqlCommand CMD_SELECT_A = new SqlCommand(SQL_SELECT_A, DB_Connection); CMD_SELECT_A.CommandType = CommandType.Text; string SQL_SELECT_P = "SELECT LabIndex FROM " + LabsDB + " WHERE LabIndex=" + (Convert.ToInt32(var_id) - 1); SqlCommand CMD_SELECT_P = new SqlCommand(SQL_SELECT_P, DB_Connection); CMD_SELECT_P.CommandType = CommandType.Text; string SQL_SELECT_N = "SELECT LabIndex FROM " + LabsDB + " WHERE LabIndex=" + (Convert.ToInt32(var_id) + 1); SqlCommand CMD_SELECT_N = new SqlCommand(SQL_SELECT_N, DB_Connection); CMD_SELECT_P.CommandType = CommandType.Text; DB_Connection.Open(); using (SqlDataReader Reader = CMD_SELECT.ExecuteReader()) { if (Reader.Read() && !NewLab_Form.Visible) //2nd check correct [?] { DisplayLab_Form.Visible = true; DisplayLab_Title.Text = Reader["Title"].ToString(); DisplayLab_Subtitle.Text = Reader["Subtitle"].ToString(); DisplayLab_DataSource.SelectCommand = "SELECT Content FROM " + LabsDB + " WHERE LabIndex=" + var_id; } } // disable "Delete Page" button if only 1 page found if (Convert.ToInt32(CMD_SELECT_A.ExecuteScalar().ToString()) < 2) { DisplayLab_DeletePage.Enabled = false; DisplayLab_DeletePage.ImageUrl = "~/Source/img/document-remove-disabled.png"; DisplayLab_DeletePage.ToolTip = ""; } // display previous Lab button using (SqlDataReader Reader2 = CMD_SELECT_P.ExecuteReader()) { if (Reader2.Read() && Reader2.HasRows) { DisplayLab_Previous.NavigateUrl = "~/Content/Labs.aspx?id=" + Reader2["LabIndex"].ToString(); } else { DisplayLab_Previous.Visible = false; } Reader2.Close(); } // display next Lab button using (SqlDataReader Reader3 = CMD_SELECT_N.ExecuteReader()) { if (Reader3.Read() && Reader3.HasRows) { DisplayLab_Next.NavigateUrl = "~/Content/Labs.aspx?id=" + Reader3["LabIndex"].ToString(); } else { DisplayLab_Next.Visible = false; } Reader3.Close(); } LabsList.Visible = false; DB_Connection.Close(); } } else //display list of Labs { //need to prevent postback callback reimplementation if (Page.IsPostBack) { return; } LabsList.Visible = true; Button_Return.Visible = false; if (Session["teacher"] == null) { LabsList.Columns[4].Visible = false; LabsList.ShowFooter = false; } string SQL_SELECT = "SELECT Surname, Name, LabIndex, DateCreated, Title FROM " + LabsDB + " INNER JOIN " + UsersDB + " ON " + LabsDB + ".AuthorID=" + UsersDB + ".Username WHERE Page=1"; SqlDataAdapter da = new SqlDataAdapter(SQL_SELECT, DB_Connection); DataTable dt = new DataTable(); DB_Connection.Open(); da.Fill(dt); LabsList.DataSource = dt; LabsList.DataBind(); DB_Connection.Close(); } }
public DataTable Bankingdetails(bankdetails b) { SqlConnection conn = new SqlConnection(); try { conn.ConnectionString = ConfigurationManager.ConnectionStrings["btposdb"].ToString(); SqlCommand cmd = new SqlCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "HVInsUpdBankingdetails"; cmd.Connection = conn; SqlParameter ff = new SqlParameter("@flag", SqlDbType.VarChar); ff.Value = b.flag; cmd.Parameters.Add(ff); SqlParameter i = new SqlParameter("@Id", SqlDbType.Int); i.Value = b.Id; cmd.Parameters.Add(i); SqlParameter n = new SqlParameter("@Accountnumber", SqlDbType.VarChar, 50); n.Value = b.Accountnumber; cmd.Parameters.Add(n); SqlParameter r = new SqlParameter("@BankName", SqlDbType.VarChar, 50); r.Value = b.BankName; cmd.Parameters.Add(r); SqlParameter a = new SqlParameter("@BankCode", SqlDbType.VarChar, 50); a.Value = b.Bankcode; cmd.Parameters.Add(a); SqlParameter s = new SqlParameter("@BranchAddress", SqlDbType.VarChar, 50); s.Value = b.BranchAddress; cmd.Parameters.Add(s); SqlParameter j2 = new SqlParameter("@CountryId", SqlDbType.VarChar,50); j2.Value = b.Country; cmd.Parameters.Add(j2); SqlParameter f = new SqlParameter("@IsActive", SqlDbType.Int); f.Value = b.IsActive; cmd.Parameters.Add(f); SqlParameter ddd = new SqlParameter("@DriverId", SqlDbType.Int); ddd.Value = b.DriverId; cmd.Parameters.Add(ddd); SqlParameter er = new SqlParameter("@QRCode", SqlDbType.VarChar); er.Value = b.qrcode; cmd.Parameters.Add(er); SqlParameter Code1 = new SqlParameter("@Code", SqlDbType.VarChar); Code1.Value = b.DriverCode; cmd.Parameters.Add(Code1); DataTable dt = new DataTable(); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(dt); //update the qr code if (dt.Rows.Count > 0) { i.Value = dt.Rows[0]["Id"].ToString(); var code = dt.Rows[0]["Code"].ToString(); if (code != "") { ff.Value = "U"; var qrcode = GetQRCode(code); er.Value = qrcode; Code1.Value = code; dt = new DataTable(); da.Fill(dt); } } return dt; } catch (Exception ex) { if (conn != null && conn.State == ConnectionState.Open) { conn.Close(); } throw ex; } }
protected void Page_Load(object sender, EventArgs e) { base.AllowHttpMethod("POST"); base.DisableTop(true); int pgNow; int pgSize; string SearchDateS; string SearchDateE; int SearchKind = 0; int UserID = 0; SearchDateS = Request.Form["SearchDateS"] ?? ""; SearchDateE = Request.Form["SearchDateE"] ?? ""; if (SearchDateS != "") { try { SearchDateS = Convert.ToDateTime((Convert.ToInt32(SearchDateS.Split('-')[0]) + 1911).ToString() + "/" + SearchDateS.Split('-')[1] + "/01").ToString("yyyy/MM/dd"); } catch { SearchDateS = ""; } } if (SearchDateE != "") { try { SearchDateE = Convert.ToDateTime((Convert.ToInt32(SearchDateE.Split('-')[0]) + 1911).ToString() + "/" + SearchDateE.Split('-')[1] + "/01").ToString("yyyy/MM/dd"); SearchDateE = Convert.ToDateTime(SearchDateE).AddMonths(1).AddDays(-1).ToString("yyyy/MM/dd"); } catch { SearchDateE = ""; } } int.TryParse(Request.Form["UserID"], out UserID); int.TryParse(Request.Form["pgNow"], out pgNow); int.TryParse(Request.Form["pgSize"], out pgSize); int.TryParse(Request.Form["SearchKind"], out SearchKind); List <SearchCheckListVM> list = new List <SearchCheckListVM>(); PageVM rtn = new PageVM(); DataSet ds = new DataSet(); try { using (SqlConnection sc = new SqlConnection(WebConfigurationManager.ConnectionStrings["ConnDB"].ToString())) { using (SqlCommand cmd = new SqlCommand("dbo.usp_SearchCheck_xGetHistoryAuditList", sc)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@pgNow", pgNow == 0 ? 1 : pgNow); cmd.Parameters.AddWithValue("@pgSize", pgSize == 0 ? 10 : pgSize); cmd.Parameters.AddWithValue("@SearchDateS", SearchDateS); cmd.Parameters.AddWithValue("@SearchDateE", SearchDateE); cmd.Parameters.AddWithValue("@SearchKind", SearchKind); cmd.Parameters.AddWithValue("@UserID", UserID); using (SqlDataAdapter da = new SqlDataAdapter(cmd)) { sc.Open(); da.Fill(ds); } } } EntityS.FillModel(list, ds.Tables[0]); EntityS.FillModel(rtn, ds.Tables[1]); } catch (Exception ex) { Response.Write(ex.Message + ex.StackTrace); } rtn.message = list; Response.ContentType = "application/json; charset=utf-8"; Response.Write(Newtonsoft.Json.JsonConvert.SerializeObject(rtn)); Response.End(); }
//获取时间段,返回dataview public static DataTable LoadTimeBlock() { System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(); conn.ConnectionString = ConnStr; try { SqlCommand selectCMD = new SqlCommand("SELECT name FROM timeblock", conn); selectCMD.CommandTimeout = 30; SqlDataAdapter dbDA = new SqlDataAdapter(); dbDA.SelectCommand = selectCMD; conn.Open(); DataSet dbDS = new DataSet(); dbDA.Fill(dbDS, "TimeBlockList"); return dbDS.Tables[0]; } catch (Exception ex) { MessageBox.Show(ex.ToString()); } finally { conn.Close(); } return null; }
public List <sosportalconfiguracaocliente_Dommain> ListarTodos() { try { var _connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString(); /* Criar conexão com banco de dados */ var _connection = new SqlConnection(_connectionString); /* Abrir conexão */ _connection.Open(); /* Criar comando que vai enviar informações para o banco */ SqlCommand _command = _connection.CreateCommand(); /* Adicionar as informações dentro do comando que vai enviar para o banco */ _command.CommandType = CommandType.StoredProcedure; /* Recebe o nome da procedure que esta sendo executada */ _command.CommandText = "Usp_ListarTodos_ConfigurcaoCliente"; /* Defini o tempo que a conexão ficará aberta (Em Segundos [7200] = 2 horas) */ _command.CommandTimeout = 7200; /* Criar um adptador */ var sqlDataAdapter = new SqlDataAdapter(_command); /* Criar datatable vasia aonde vou adicionar os valores que serão retornados do banco */ var datatable = new DataTable(); /* Mandar comando ir ate o banco buscar os daods e o o adptador preencher a datatable */ sqlDataAdapter.Fill(datatable); /* Finaliza Conexão com banco de Dados */ _connection.Dispose(); _connection.Close(); var retorno = new List <sosportalconfiguracaocliente_Dommain>(); /* O foreach varre o datarow e adiciona cada linha em uma lista ate acabar coleção */ foreach (DataRow linha in datatable.Rows) { //Adiciona as informações namespace entidade var entity = new sosportalconfiguracaocliente_Dommain { IdConfiguracaoCliente = int.Parse(linha["IdConfiguracaoCliente"].ToString()), IdPerfil = int.Parse(linha["IdPerfil"].ToString()), IdCliente = int.Parse(linha["IdCliente"].ToString()), IdConfiguracao = int.Parse(linha["IdCOnfiguracao"].ToString()), Status = bool.Parse(linha["Status"].ToString()), }; retorno.Add(entity); } /* Retorna a tabela preenchida */ return(retorno); } catch (System.Data.SqlClient.SqlException exception) { throw new Exception(message: exception.Number.ToString() + " - " + exception.Message.ToString() + " - " + exception.InnerException.ToString()); } catch (Exception exception) { throw new Exception(exception.ToString()); } }
private void lgnbtn_Click(object sender, EventArgs e) { SqlConnection MyConnection = new SqlConnection(@"Data Source=DESKTOP-S9RU5TT\USMAN;Initial Catalog=LMS;Integrated Security=True"); MyConnection.Open(); SqlCommand cmd; cmd = MyConnection.CreateCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = "SELECT User_ID,Username,Password,Type from [dbo].[User]"; cmd.ExecuteNonQuery(); DataTable dt = new DataTable(); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(dt); int x = 0; String s = ""; String usrID = ""; foreach (DataRow dr in dt.Rows) { s = dr["Username"].ToString(); String t = dr["Password"].ToString(); String us = dr["Type"].ToString(); usrID = dr["User_ID"].ToString(); if (s == username.Text && t == password.Text && us == "admin") { x = 1; break; } else if (s == username.Text && t == password.Text && us == "staff") { x = 2; break; } } if (x == 1) { User u = new User(); u.UserName = s; users = s; userID = usrID; // session["userName"] = u.UserName; MessageBox.Show("Welcome " + users); openchildform(new Formmain()); } else if (x == 2) { User u = new User(); u.UserName = s; users = s; userID = usrID; // session["userName"] = u.UserName; MessageBox.Show("Welcome " + users); openchildform(new Formstf()); } else { MessageBox.Show("Incorrect Username or Password"); } }
protected void Page_Load(object sender, EventArgs e) { string sr = Request.QueryString["type"]; string rep = ""; sr = sr.ToLower(); string s = sr.Trim('\''); string ans = "no"; if (s != "") { SqlCommand cmd = new SqlCommand("Insert into Ques(Question,Date,Time) values('" + s + "','" + DateTime.Now.ToShortDateString() + "','" + DateTime.Now.ToShortTimeString() + "')", con); con.Open(); cmd.ExecuteNonQuery(); con.Close(); Session["action"] = "added"; if (s.Equals("hi") || s.Equals("hii") || s.Equals("hiii") || s.Equals("hiiii") || s.Equals("hhii") || s.Equals("hello") || s.Equals("helloo") || s.Equals("helo") || s.Equals("heello") || s.Equals("helllo") || s.Equals("helloooo")) { ans = "OK"; SqlDataAdapter da = new SqlDataAdapter("Select top 1 * from generalreply order by NEWID()", con); DataSet ds = new DataSet(); da.Fill(ds); rep = Convert.ToString(ds.Tables[0].Rows[0][1]); string del = "delete from Answer"; SqlCommand dcmd = new SqlCommand(del, con); con.Open(); dcmd.ExecuteNonQuery(); con.Close(); SqlCommand cmd1 = new SqlCommand("Insert into Answer(Question,Answer) values('" + s + "','" + rep + "')", con); con.Open(); cmd1.ExecuteNonQuery(); con.Close(); Response.Redirect("bot.aspx?reply=" + rep + ""); } else if (s.Contains("i") && s.Contains("fine") || s.Contains("i") && s.Contains("good")) { ans = "OK"; SqlDataAdapter da = new SqlDataAdapter("Select top 1 * from addqueries order by NEWID()", con); DataSet ds = new DataSet(); da.Fill(ds); rep = Convert.ToString(ds.Tables[0].Rows[0][1]); string del = "delete from Answer"; SqlCommand dcmd = new SqlCommand(del, con); con.Open(); dcmd.ExecuteNonQuery(); con.Close(); SqlCommand cmd1 = new SqlCommand("Insert into Answer(Question,Answer) values('" + s + "','" + rep + "')", con); con.Open(); cmd1.ExecuteNonQuery(); con.Close(); Response.Redirect("bot.aspx?reply=" + rep + ""); } //if (s.Contains("you") && s.Contains("how") || s.Contains("you") && s.Contains("how") && s.Contains("do")) //{ // ans = "OK"; // rep = "I am Fine..How Are You ?"; // string del = "delete from Answer"; // SqlCommand dcmd = new SqlCommand(del, con); // con.Open(); // dcmd.ExecuteNonQuery(); // con.Close(); // SqlCommand cmd1 = new SqlCommand("Insert into Answer(Question,Answer) values('" + s + "','" + rep + "')", con); // con.Open(); // cmd1.ExecuteNonQuery(); // con.Close(); // Response.Redirect("bot.aspx?reply=" + rep + ""); //} else if (s.Contains("timetable") || s.Contains("time")) { ans = "OK"; String s2, k1, k2, k3, rep1 = "s", link = ""; int count; SqlDataAdapter da4 = new SqlDataAdapter("Select * from timetable where keywords = '2'", con); DataSet ds4 = new DataSet(); da4.Fill(ds4); count = Convert.ToInt32(ds4.Tables[0].Rows.Count); for (int i = 0; i < count; i++) { k1 = Convert.ToString(ds4.Tables[0].Rows[i][2]).ToLower(); k2 = Convert.ToString(ds4.Tables[0].Rows[i][3]).ToLower(); k3 = Convert.ToString(ds4.Tables[0].Rows[i][4]).ToLower(); if (s.Contains(k1) && s.Contains(k2) && s.Contains(k3)) { rep1 = Convert.ToString(ds4.Tables[0].Rows[i][7]); rep = rep1; link = Convert.ToString(ds4.Tables[0].Rows[i][8]); string del = "delete from Answer"; SqlCommand dcmd = new SqlCommand(del, con); con.Open(); dcmd.ExecuteNonQuery(); con.Close(); SqlCommand cmd1 = new SqlCommand("Insert into Answer(Question,Answer,Link) values('" + s + "','" + rep + "','" + link + "')", con); con.Open(); cmd1.ExecuteNonQuery(); con.Close(); Response.Redirect("bot.aspx?reply=" + rep + "&link=" + link + ""); } } if (rep1 == "s") { da4 = new SqlDataAdapter("Select * from timetable where keywords = '2' order by newid()", con); ds4 = new DataSet(); da4.Fill(ds4); rep = Convert.ToString(ds4.Tables[0].Rows[0][7]); link = Convert.ToString(ds4.Tables[0].Rows[0][8]); string del = "delete from Answer"; SqlCommand dcmd = new SqlCommand(del, con); con.Open(); dcmd.ExecuteNonQuery(); con.Close(); SqlCommand cmd1 = new SqlCommand("Insert into Answer(Question,Answer,Link) values('" + s + "','" + rep + "','" + link + "')", con); con.Open(); cmd1.ExecuteNonQuery(); con.Close(); Response.Redirect("bot.aspx?reply=" + rep + "&link=" + link + ""); } } else if (s.Contains("question papers") || s.Contains("question paper")) { ans = "OK"; String s2, k1, k2, rep1 = "s", link = ""; int count; SqlDataAdapter da3 = new SqlDataAdapter("Select * from QuestionPapers where keywords = '2'", con); DataSet ds3 = new DataSet(); da3.Fill(ds3); count = Convert.ToInt32(ds3.Tables[0].Rows.Count); for (int i = 0; i < count; i++) { k1 = Convert.ToString(ds3.Tables[0].Rows[i][1]).ToLower(); k2 = Convert.ToString(ds3.Tables[0].Rows[i][2]).ToLower(); if (s.Contains(k1) && s.Contains(k2)) { rep1 = Convert.ToString(ds3.Tables[0].Rows[i][6]); rep = rep1; link = Convert.ToString(ds3.Tables[0].Rows[i][7]); string del = "delete from Answer"; SqlCommand dcmd = new SqlCommand(del, con); con.Open(); dcmd.ExecuteNonQuery(); con.Close(); SqlCommand cmd1 = new SqlCommand("Insert into Answer(Question,Answer,Link) values('" + s + "','" + rep + "','" + link + "')", con); con.Open(); cmd1.ExecuteNonQuery(); con.Close(); Response.Redirect("bot.aspx?reply=" + rep + "&link=" + link + ""); } } if (rep1 == "s") { da3 = new SqlDataAdapter("Select * from QuestionPapers where keywords = '2' order by newid()", con); ds3 = new DataSet(); da3.Fill(ds3); rep = Convert.ToString(ds3.Tables[0].Rows[0][6]); link = Convert.ToString(ds3.Tables[0].Rows[0][7]); string del = "delete from Answer"; SqlCommand dcmd = new SqlCommand(del, con); con.Open(); dcmd.ExecuteNonQuery(); con.Close(); SqlCommand cmd1 = new SqlCommand("Insert into Answer(Question,Answer,Link) values('" + s + "','" + rep + "','" + link + "')", con); con.Open(); cmd1.ExecuteNonQuery(); con.Close(); Response.Redirect("bot.aspx?reply=" + rep + "&link=" + link + ""); } } else if (s.Contains("reference book")) { ans = "OK"; String s2, k1, k2, rep1 = "s"; int count; SqlDataAdapter da3 = new SqlDataAdapter("Select * from ReferenceBook where keywords = '2'", con); DataSet ds3 = new DataSet(); da3.Fill(ds3); count = Convert.ToInt32(ds3.Tables[0].Rows.Count); for (int i = 0; i < count; i++) { k1 = Convert.ToString(ds3.Tables[0].Rows[i][1]).ToLower(); k2 = Convert.ToString(ds3.Tables[0].Rows[i][2]).ToLower(); // k3 = Convert.ToString(ds3.Tables[0].Rows[i][2]).ToLower(); if (s.Contains(k1) && s.Contains(k2)) { rep1 = Convert.ToString(ds3.Tables[0].Rows[i][6]); rep = rep1; string del = "delete from Answer"; SqlCommand dcmd = new SqlCommand(del, con); con.Open(); dcmd.ExecuteNonQuery(); con.Close(); SqlCommand cmd1 = new SqlCommand("Insert into Answer(Question,Answer) values('" + s + "','" + rep + "')", con); con.Open(); cmd1.ExecuteNonQuery(); con.Close(); Response.Redirect("bot.aspx?reply=" + rep + ""); } } if (rep1 == "s") { da3 = new SqlDataAdapter("Select * from ReferenceBook where keywords = '2' order by newid()", con); ds3 = new DataSet(); da3.Fill(ds3); rep = Convert.ToString(ds3.Tables[0].Rows[0][6]); string del = "delete from Answer"; SqlCommand dcmd = new SqlCommand(del, con); con.Open(); dcmd.ExecuteNonQuery(); con.Close(); SqlCommand cmd1 = new SqlCommand("Insert into Answer(Question,Answer) values('" + s + "','" + rep + "')", con); con.Open(); cmd1.ExecuteNonQuery(); con.Close(); Response.Redirect("bot.aspx?reply=" + rep + ""); } } else if (s.Contains("subjects") || s.Contains("subject")) { ans = "OK"; String s2, k1, k2, k3, k4, rep1 = "s"; int count; SqlDataAdapter da3 = new SqlDataAdapter("Select * from Subjects where keywords = '2'", con); DataSet ds3 = new DataSet(); da3.Fill(ds3); count = Convert.ToInt32(ds3.Tables[0].Rows.Count); for (int i = 0; i < count; i++) { k1 = Convert.ToString(ds3.Tables[0].Rows[i][2]).ToLower(); k2 = Convert.ToString(ds3.Tables[0].Rows[i][3]).ToLower(); k3 = Convert.ToString(ds3.Tables[0].Rows[i][4]).ToLower(); k4 = Convert.ToString(ds3.Tables[0].Rows[i][5]).ToLower(); // k5 = Convert.ToString(ds3.Tables[0].Rows[i][5]).ToLower(); if (s.Contains(k1) && (s.Contains(k2) || s.Contains(k4)) && s.Contains(k3)) { rep1 = Convert.ToString(ds3.Tables[0].Rows[i][9]); rep = rep1; string del = "delete from Answer"; SqlCommand dcmd = new SqlCommand(del, con); con.Open(); dcmd.ExecuteNonQuery(); con.Close(); SqlCommand cmd1 = new SqlCommand("Insert into Answer(Question,Answer) values('" + s + "','" + rep + "')", con); con.Open(); cmd1.ExecuteNonQuery(); con.Close(); Response.Redirect("bot.aspx?reply=" + rep + ""); } } if (rep1 == "s") { da3 = new SqlDataAdapter("Select * from Subjects where keywords = '2' order by newid()", con); ds3 = new DataSet(); da3.Fill(ds3); rep = Convert.ToString(ds3.Tables[0].Rows[0][9]); string del = "delete from Answer"; SqlCommand dcmd = new SqlCommand(del, con); con.Open(); dcmd.ExecuteNonQuery(); con.Close(); SqlCommand cmd1 = new SqlCommand("Insert into Answer(Question,Answer) values('" + s + "','" + rep + "')", con); con.Open(); cmd1.ExecuteNonQuery(); con.Close(); Response.Redirect("bot.aspx?reply=" + rep + ""); } } else if (s.Contains("cabin")) { ans = "OK"; String s2, k1, k2, rep1 = "s"; int count; SqlDataAdapter da3 = new SqlDataAdapter("Select * from Cabin where keywords = '2'", con); DataSet ds3 = new DataSet(); da3.Fill(ds3); count = Convert.ToInt32(ds3.Tables[0].Rows.Count); for (int i = 0; i < count; i++) { k1 = Convert.ToString(ds3.Tables[0].Rows[i][1]).ToLower(); k2 = Convert.ToString(ds3.Tables[0].Rows[i][2]).ToLower(); if (s.Contains(k1) && s.Contains(k2)) { rep1 = Convert.ToString(ds3.Tables[0].Rows[i][6]); rep = rep1; string del = "delete from Answer"; SqlCommand dcmd = new SqlCommand(del, con); con.Open(); dcmd.ExecuteNonQuery(); con.Close(); SqlCommand cmd1 = new SqlCommand("Insert into Answer(Question,Answer) values('" + s + "','" + rep + "')", con); con.Open(); cmd1.ExecuteNonQuery(); con.Close(); Response.Redirect("bot.aspx?reply=" + rep + ""); } } if (rep1 == "s") { da3 = new SqlDataAdapter("Select * from Cabin where keywords = '2' order by newid()", con); ds3 = new DataSet(); da3.Fill(ds3); rep = Convert.ToString(ds3.Tables[0].Rows[0][6]); string del = "delete from Answer"; SqlCommand dcmd = new SqlCommand(del, con); con.Open(); dcmd.ExecuteNonQuery(); con.Close(); SqlCommand cmd1 = new SqlCommand("Insert into Answer(Question,Answer) values('" + s + "','" + rep + "')", con); con.Open(); cmd1.ExecuteNonQuery(); con.Close(); Response.Redirect("bot.aspx?reply=" + rep + ""); } } else if (s.Contains("contact") || s.Contains("mobile")) { ans = "OK"; String s2, k1, k3, k4, k5, k6, rep1 = "s"; int count; SqlDataAdapter da3 = new SqlDataAdapter("Select * from Contact where keywords = '3'", con); DataSet ds3 = new DataSet(); da3.Fill(ds3); count = Convert.ToInt32(ds3.Tables[0].Rows.Count); for (int i = 0; i < count; i++) { k1 = Convert.ToString(ds3.Tables[0].Rows[i][1]).ToLower(); // k2 = Convert.ToString(ds3.Tables[0].Rows[i][2]).ToLower(); k3 = Convert.ToString(ds3.Tables[0].Rows[i][3]).ToLower(); k4 = Convert.ToString(ds3.Tables[0].Rows[i][4]).ToLower(); k5 = Convert.ToString(ds3.Tables[0].Rows[i][5]).ToLower(); // k6 = Convert.ToString(ds3.Tables[0].Rows[i][6]).ToLower(); // k7 = Convert.ToString(ds3.Tables[0].Rows[i][7]).ToLower(); if (s.Contains(k1) && s.Contains(k3) && (s.Contains(k4) || s.Contains(k5))) { rep1 = Convert.ToString(ds3.Tables[0].Rows[i][9]); rep = rep1; string del = "delete from Answer"; SqlCommand dcmd = new SqlCommand(del, con); con.Open(); dcmd.ExecuteNonQuery(); con.Close(); SqlCommand cmd1 = new SqlCommand("Insert into Answer(Question,Answer) values('" + s + "','" + rep + "')", con); con.Open(); cmd1.ExecuteNonQuery(); con.Close(); Response.Redirect("bot.aspx?reply=" + rep + ""); } } if (rep1 == "s") { da3 = new SqlDataAdapter("Select * from Contact where keywords = '3' order by newid()", con); ds3 = new DataSet(); da3.Fill(ds3); rep = Convert.ToString(ds3.Tables[0].Rows[0][9]); string del = "delete from Answer"; SqlCommand dcmd = new SqlCommand(del, con); con.Open(); dcmd.ExecuteNonQuery(); con.Close(); SqlCommand cmd1 = new SqlCommand("Insert into Answer(Question,Answer) values('" + s + "','" + rep + "')", con); con.Open(); cmd1.ExecuteNonQuery(); con.Close(); Response.Redirect("bot.aspx?reply=" + rep + ""); } } else if (s.Contains("specialization") || s.Contains("specialization") || s.Contains("specialisation")) { ans = "OK"; String s2, k1, k2, rep1 = "s"; int count; SqlDataAdapter da3 = new SqlDataAdapter("Select * from specialization where keywords = '2'", con); DataSet ds3 = new DataSet(); da3.Fill(ds3); count = Convert.ToInt32(ds3.Tables[0].Rows.Count); for (int i = 0; i < count; i++) { k1 = Convert.ToString(ds3.Tables[0].Rows[i][1]).ToLower(); k2 = Convert.ToString(ds3.Tables[0].Rows[i][2]).ToLower(); if (s.Contains(k1) && s.Contains(k2)) { rep1 = Convert.ToString(ds3.Tables[0].Rows[i][6]); rep = rep1; string del = "delete from Answer"; SqlCommand dcmd = new SqlCommand(del, con); con.Open(); dcmd.ExecuteNonQuery(); con.Close(); SqlCommand cmd1 = new SqlCommand("Insert into Answer(Question,Answer) values('" + s + "','" + rep + "')", con); con.Open(); cmd1.ExecuteNonQuery(); con.Close(); Response.Redirect("bot.aspx?reply=" + rep + ""); } } if (rep1 == "s") { da3 = new SqlDataAdapter("Select * from specialization where keywords = '2' order by newid()", con); ds3 = new DataSet(); da3.Fill(ds3); rep = Convert.ToString(ds3.Tables[0].Rows[0][6]); string del = "delete from Answer"; SqlCommand dcmd = new SqlCommand(del, con); con.Open(); dcmd.ExecuteNonQuery(); con.Close(); SqlCommand cmd1 = new SqlCommand("Insert into Answer(Question,Answer) values('" + s + "','" + rep + "')", con); con.Open(); cmd1.ExecuteNonQuery(); con.Close(); Response.Redirect("bot.aspx?reply=" + rep + ""); } } else if (s.Contains("faculties")) { ans = "OK"; String s2, k1, k2, k3, rep1 = "s"; int count; SqlDataAdapter da3 = new SqlDataAdapter("Select * from faculaties where keywords = '3'", con); DataSet ds3 = new DataSet(); da3.Fill(ds3); count = Convert.ToInt32(ds3.Tables[0].Rows.Count); for (int i = 0; i < count; i++) { k1 = Convert.ToString(ds3.Tables[0].Rows[i][1]).ToLower(); k2 = Convert.ToString(ds3.Tables[0].Rows[i][2]).ToLower(); k3 = Convert.ToString(ds3.Tables[0].Rows[i][3]).ToLower(); if (s.Contains(k1) && s.Contains(k2) && s.Contains(k3)) { rep1 = Convert.ToString(ds3.Tables[0].Rows[i][6]); rep = rep1; string del = "delete from Answer"; SqlCommand dcmd = new SqlCommand(del, con); con.Open(); dcmd.ExecuteNonQuery(); con.Close(); SqlCommand cmd1 = new SqlCommand("Insert into Answer(Question,Answer) values('" + s + "','" + rep + "')", con); con.Open(); cmd1.ExecuteNonQuery(); con.Close(); Response.Redirect("bot.aspx?reply=" + rep + ""); } } if (rep1 == "s") { da3 = new SqlDataAdapter("Select * from faculaties where keywords = '3' order by newid()", con); ds3 = new DataSet(); da3.Fill(ds3); rep = Convert.ToString(ds3.Tables[0].Rows[0][6]); string del = "delete from Answer"; SqlCommand dcmd = new SqlCommand(del, con); con.Open(); dcmd.ExecuteNonQuery(); con.Close(); SqlCommand cmd1 = new SqlCommand("Insert into Answer(Question,Answer) values('" + s + "','" + rep + "')", con); con.Open(); cmd1.ExecuteNonQuery(); con.Close(); Response.Redirect("bot.aspx?reply=" + rep + ""); } } else if (s.Contains("classroom")) { ans = "OK"; String s2, k1, k2, rep1 = "s"; int count; SqlDataAdapter da3 = new SqlDataAdapter("Select * from classroom where keywords = '2'", con); DataSet ds3 = new DataSet(); da3.Fill(ds3); count = Convert.ToInt32(ds3.Tables[0].Rows.Count); for (int i = 0; i < count; i++) { k1 = Convert.ToString(ds3.Tables[0].Rows[i][1]).ToLower(); k2 = Convert.ToString(ds3.Tables[0].Rows[i][2]).ToLower(); if (s.Contains(k1) && s.Contains(k2)) { rep1 = Convert.ToString(ds3.Tables[0].Rows[i][6]); rep = rep1; string del = "delete from Answer"; SqlCommand dcmd = new SqlCommand(del, con); con.Open(); dcmd.ExecuteNonQuery(); con.Close(); SqlCommand cmd1 = new SqlCommand("Insert into Answer(Question,Answer) values('" + s + "','" + rep + "')", con); con.Open(); cmd1.ExecuteNonQuery(); con.Close(); Response.Redirect("bot.aspx?reply=" + rep + ""); } } if (rep1 == "s") { da3 = new SqlDataAdapter("Select * from classroom where keywords = '2' order by newid()", con); ds3 = new DataSet(); da3.Fill(ds3); rep = Convert.ToString(ds3.Tables[0].Rows[0][6]); string del = "delete from Answer"; SqlCommand dcmd = new SqlCommand(del, con); con.Open(); dcmd.ExecuteNonQuery(); con.Close(); SqlCommand cmd1 = new SqlCommand("Insert into Answer(Question,Answer) values('" + s + "','" + rep + "')", con); con.Open(); cmd1.ExecuteNonQuery(); con.Close(); Response.Redirect("bot.aspx?reply=" + rep + ""); } } else if (s.Contains("how")) { ans = "OK"; String s2, query1, query2, query3, rep1 = "s"; int count; SqlDataAdapter da3 = new SqlDataAdapter("Select * from doublereply where keywords = '3'", con); DataSet ds3 = new DataSet(); da3.Fill(ds3); count = Convert.ToInt32(ds3.Tables[0].Rows.Count); for (int i = 0; i < count; i++) { query1 = Convert.ToString(ds3.Tables[0].Rows[i][1]).ToLower(); query2 = Convert.ToString(ds3.Tables[0].Rows[i][2]).ToLower(); query3 = Convert.ToString(ds3.Tables[0].Rows[i][3]).ToLower(); if (s.Contains(query1) && s.Contains(query2) && s.Contains(query3)) { rep1 = Convert.ToString(ds3.Tables[0].Rows[i][6]); rep = rep1; string del = "delete from Answer"; SqlCommand dcmd = new SqlCommand(del, con); con.Open(); dcmd.ExecuteNonQuery(); con.Close(); SqlCommand cmd1 = new SqlCommand("Insert into Answer(Question,Answer) values('" + s + "','" + rep + "')", con); con.Open(); cmd1.ExecuteNonQuery(); con.Close(); Response.Redirect("bot.aspx?reply=" + rep + ""); } } if (rep1 == "s") { da3 = new SqlDataAdapter("Select * from doublereply where keywords = '3' order by newid()", con); ds3 = new DataSet(); da3.Fill(ds3); rep = Convert.ToString(ds3.Tables[0].Rows[0][6]); string del = "delete from Answer"; SqlCommand dcmd = new SqlCommand(del, con); con.Open(); dcmd.ExecuteNonQuery(); con.Close(); SqlCommand cmd1 = new SqlCommand("Insert into Answer(Question,Answer) values('" + s + "','" + rep + "')", con); con.Open(); cmd1.ExecuteNonQuery(); con.Close(); Response.Redirect("bot.aspx?reply=" + rep + ""); } } else if (s.Contains("what")) { ans = "OK"; String s2, query1, query2, query3, rep1 = "s"; int count; SqlDataAdapter da3 = new SqlDataAdapter("Select * from whatdoublereply where keywords = '3'", con); DataSet ds3 = new DataSet(); da3.Fill(ds3); count = Convert.ToInt32(ds3.Tables[0].Rows.Count); for (int i = 0; i < count; i++) { query1 = Convert.ToString(ds3.Tables[0].Rows[i][1]).ToLower(); query2 = Convert.ToString(ds3.Tables[0].Rows[i][2]).ToLower(); query3 = Convert.ToString(ds3.Tables[0].Rows[i][3]).ToLower(); if (s.Contains(query1) && s.Contains(query2) && s.Contains(query3)) { rep1 = Convert.ToString(ds3.Tables[0].Rows[i][6]); rep = rep1; string del = "delete from Answer"; SqlCommand dcmd = new SqlCommand(del, con); con.Open(); dcmd.ExecuteNonQuery(); con.Close(); SqlCommand cmd1 = new SqlCommand("Insert into Answer(Question,Answer) values('" + s + "','" + rep + "')", con); con.Open(); cmd1.ExecuteNonQuery(); con.Close(); Response.Redirect("bot.aspx?reply=" + rep + ""); } } if (rep1 == "s") { da3 = new SqlDataAdapter("Select * from whatdoublereply where keywords = '3' order by newid()", con); ds3 = new DataSet(); da3.Fill(ds3); rep = Convert.ToString(ds3.Tables[0].Rows[0][6]); string del = "delete from Answer"; SqlCommand dcmd = new SqlCommand(del, con); con.Open(); dcmd.ExecuteNonQuery(); con.Close(); SqlCommand cmd1 = new SqlCommand("Insert into Answer(Question,Answer) values('" + s + "','" + rep + "')", con); con.Open(); cmd1.ExecuteNonQuery(); con.Close(); Response.Redirect("bot.aspx?reply=" + rep + ""); } } else if (s.Contains("where")) { ans = "OK"; String s2, query1, query2, query3, rep1 = "s"; int count; SqlDataAdapter da3 = new SqlDataAdapter("Select * from wheredoublereply where keywords = '3'", con); DataSet ds3 = new DataSet(); da3.Fill(ds3); count = Convert.ToInt32(ds3.Tables[0].Rows.Count); for (int i = 0; i < count; i++) { query1 = Convert.ToString(ds3.Tables[0].Rows[i][1]).ToLower(); query2 = Convert.ToString(ds3.Tables[0].Rows[i][2]).ToLower(); query3 = Convert.ToString(ds3.Tables[0].Rows[i][3]).ToLower(); if (s.Contains(query1) && s.Contains(query2) && s.Contains(query3)) { rep1 = Convert.ToString(ds3.Tables[0].Rows[i][6]); rep = rep1; string del = "delete from Answer"; SqlCommand dcmd = new SqlCommand(del, con); con.Open(); dcmd.ExecuteNonQuery(); con.Close(); SqlCommand cmd1 = new SqlCommand("Insert into Answer(Question,Answer) values('" + s + "','" + rep + "')", con); con.Open(); cmd1.ExecuteNonQuery(); con.Close(); Response.Redirect("bot.aspx?reply=" + rep + ""); } } if (rep1 == "s") { da3 = new SqlDataAdapter("Select * from wheredoublereply where keywords = '3' order by newid()", con); ds3 = new DataSet(); da3.Fill(ds3); rep = Convert.ToString(ds3.Tables[0].Rows[0][6]); string del = "delete from Answer"; SqlCommand dcmd = new SqlCommand(del, con); con.Open(); dcmd.ExecuteNonQuery(); con.Close(); SqlCommand cmd1 = new SqlCommand("Insert into Answer(Question,Answer) values('" + s + "','" + rep + "')", con); con.Open(); cmd1.ExecuteNonQuery(); con.Close(); Response.Redirect("bot.aspx?reply=" + rep + ""); } } else if (s.Contains("who")) { ans = "OK"; String s2, query1, query2, query3, rep1 = "s"; int count; SqlDataAdapter da3 = new SqlDataAdapter("Select * from whodoublereply where keywords = '3'", con); DataSet ds3 = new DataSet(); da3.Fill(ds3); count = Convert.ToInt32(ds3.Tables[0].Rows.Count); for (int i = 0; i < count; i++) { query1 = Convert.ToString(ds3.Tables[0].Rows[i][1]).ToLower(); query2 = Convert.ToString(ds3.Tables[0].Rows[i][2]).ToLower(); query3 = Convert.ToString(ds3.Tables[0].Rows[i][3]).ToLower(); if (s.Contains(query1) && s.Contains(query2) && s.Contains(query3)) { rep1 = Convert.ToString(ds3.Tables[0].Rows[i][6]); rep = rep1; string del = "delete from Answer"; SqlCommand dcmd = new SqlCommand(del, con); con.Open(); dcmd.ExecuteNonQuery(); con.Close(); SqlCommand cmd1 = new SqlCommand("Insert into Answer(Question,Answer) values('" + s + "','" + rep + "')", con); con.Open(); cmd1.ExecuteNonQuery(); con.Close(); Response.Redirect("bot.aspx?reply=" + rep + ""); } } if (rep1 == "s") { da3 = new SqlDataAdapter("Select * from whodoublereply where keywords = '3' order by newid()", con); ds3 = new DataSet(); da3.Fill(ds3); rep = Convert.ToString(ds3.Tables[0].Rows[0][6]); string del = "delete from Answer"; SqlCommand dcmd = new SqlCommand(del, con); con.Open(); dcmd.ExecuteNonQuery(); con.Close(); SqlCommand cmd1 = new SqlCommand("Insert into Answer(Question,Answer) values('" + s + "','" + rep + "')", con); con.Open(); cmd1.ExecuteNonQuery(); con.Close(); Response.Redirect("bot.aspx?reply=" + rep + ""); } } else if (ans != "OK") { rep = Convert.ToString("No related Answer Found"); string del = "delete from Answer"; SqlCommand dcmd = new SqlCommand(del, con); con.Open(); dcmd.ExecuteNonQuery(); con.Close(); SqlCommand cmd1 = new SqlCommand("Insert into Answer(Question,Answer) values('" + s + "','" + rep + "')", con); con.Open(); cmd1.ExecuteNonQuery(); con.Close(); Response.Redirect("bot.aspx?reply=" + rep + ""); } } else { Response.Redirect("bot.aspx?reply=none"); } }
public DataSet fetch(string query) { using (this.sqlconnection = new SqlConnection(this.connstring)) { this.sqlconnection.Open(); //SqlTransaction trans = this.conn.BeginTransaction(); try { SqlDataAdapter da = new SqlDataAdapter(query, this.conn); DataSet ds = new DataSet(); da.Fill(ds); //trans.Commit(); conn.Close(); return ds; } catch (Exception ex) { try { if (this.sqlconnection.State != ConnectionState.Open) throw new Exception("Connection Is Not Open" + ex.Message); //else //trans.Rollback(); } catch (Exception ex1) { throw new Exception("Connection Is Not Open" + ex1.Message); } return (DataSet)null; } finally { if (this.sqlconnection.State != ConnectionState.Open) this.conn.Close(); } } }
/// <summary> /// 尋找任務 /// </summary> /// <returns></returns> private DataTable RefreshgvMain() { string search = ""; if (!string.IsNullOrEmpty(txtDOC_NBR.Text)) { search += " AND DOC_NBR = @DOC_NBR"; // 表單單號 } if (dateStart.SelectedDate != null) { search += string.Format(" AND CONVERT(datetime,{0}) >= @START2", FROM_TYPE == "LEAVE" ? "STARTTIME" : "OT_START"); // 時間(起) } if (dateEnd.SelectedDate != null) { search += string.Format(" AND CONVERT(datetime,{0}) <= @END2", FROM_TYPE == "LEAVE" ? "ENDTIME" : "OT_END"); // 時間(迄) } DataTable dt = new DataTable(); if (FROM_TYPE == "LEAVE") // 請假單 { using (SqlDataAdapter sda = new SqlDataAdapter(@" -- SELECT '===請選擇===' AS 'SHOW', '' AS 'DOC_NBR' -- UNION ALL SELECT DOC_NBR AS 'SHOW', DOC_NBR, STARTTIME, ENDTIME FROM Z_SCSHR_LEAVE WHERE (CANCEL_DOC_NBR = '' OR CANCEL_DOC_NBR IS NULL) AND TASK_STATUS = 2 AND TASK_RESULT = 0 AND APPLICANTGUID = @APPLICANTGUID " + search, ConnectionString)) using (DataSet ds = new DataSet()) { sda.SelectCommand.Parameters.AddWithValue("@DOC_NBR", txtDOC_NBR.Text); sda.SelectCommand.Parameters.AddWithValue("@APPLICANTGUID", USER_GUID); if (dateStart.SelectedDate != null) { sda.SelectCommand.Parameters.AddWithValue("@START2", ((DateTime)dateStart.SelectedDate).ToString("yyyy-MM-dd HH:mm")); } if (dateEnd.SelectedDate != null) { sda.SelectCommand.Parameters.AddWithValue("@END2", ((DateTime)dateEnd.SelectedDate).ToString("yyyy-MM-dd HH:mm")); } try { sda.Fill(ds); dt = ds.Tables[0]; if (dt.Rows.Count > 0) { dt.Columns.Add(new DataColumn("START_TIME", typeof(string))); dt.Columns.Add(new DataColumn("END_TIME", typeof(string))); foreach (DataRow dr in dt.Rows) { dr["START_TIME"] = dr["STARTTIME"]; dr["END_TIME"] = dr["ENDTIME"]; } } } catch (Exception ex) { KYTUtilLibs.KYTDebugLog.Log(KYTLog.DebugLog.LogLevel.Error, "SearchForm::RefreshgvMain()::L請假單::錯誤:{0}", ex.Message); } } } else if (FROM_TYPE == "OVER_TIME") // 加班單 { using (SqlDataAdapter sda = new SqlDataAdapter(@" -- SELECT '===請選擇===' AS 'SHOW', '' AS 'DOC_NBR' -- UNION ALL SELECT DOC_NBR AS 'SHOW', DOC_NBR, OT_START, OT_END FROM Z_SCSHR_OVERTIME WHERE (CANCEL_DOC_NBR = '' OR CANCEL_DOC_NBR IS NULL) AND TASK_STATUS = 2 AND TASK_RESULT = 0 AND APPLICANTGUID = @APPLICANTGUID AND ((OT_START BETWEEN @START AND @END) OR (OT_END BETWEEN @START AND @END)) -- AND ((OT_START BETWEEN DATEADD(mm, DATEDIFF(mm,0,GETDATE()), 0) -- 加班時間(起)在該月(月底23:59:59.997) -- AND DATEADD(ms, -2, DATEADD(mm, DATEDIFF(m, 0, GETDATE()) + 1, 0))) -- OR (OT_END BETWEEN DATEADD(mm, DATEDIFF(mm,0,GETDATE()), 0) -- 或加班時間(迄)在該月(月底23:59:59.997) -- AND DATEADD(ms, -2, DATEADD(mm, DATEDIFF(m, 0, GETDATE()) + 1, 0))) ) " + search, ConnectionString)) using (DataSet ds = new DataSet()) { sda.SelectCommand.Parameters.AddWithValue("@DOC_NBR", txtDOC_NBR.Text); sda.SelectCommand.Parameters.AddWithValue("@APPLICANTGUID", USER_GUID); sda.SelectCommand.Parameters.AddWithValue("@START", DateTime.Now.AddMonths(-1)); sda.SelectCommand.Parameters.AddWithValue("@END", DateTime.Now); if (dateStart.SelectedDate != null) { sda.SelectCommand.Parameters.AddWithValue("@START2", ((DateTime)dateStart.SelectedDate).ToString("yyyy-MM-dd HH:mm")); } if (dateEnd.SelectedDate != null) { sda.SelectCommand.Parameters.AddWithValue("@END2", ((DateTime)dateEnd.SelectedDate).ToString("yyyy-MM-dd HH:mm")); } try { sda.Fill(ds); dt = ds.Tables[0]; if (dt.Rows.Count > 0) { dt.Columns.Add(new DataColumn("START_TIME", typeof(string))); dt.Columns.Add(new DataColumn("END_TIME", typeof(string))); foreach (DataRow dr in dt.Rows) { dr["START_TIME"] = dr["OT_START"]; dr["END_TIME"] = dr["OT_END"]; } } } catch (Exception ex) { KYTUtilLibs.KYTDebugLog.Log(KYTLog.DebugLog.LogLevel.Error, "SearchForm::RefreshgvMain()::O加班單::錯誤:{0}", ex.Message); } } } ViewState[gvMain.ID] = dt; return(dt); }
//返回客户类别编码 public static string GetCustTypeCode(string CustTypeName) { System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(); conn.ConnectionString = ConnStr; try { SqlCommand selectCMD = new SqlCommand("SELECT typecode FROM custtype where typename='" + CustTypeName + "'", conn); selectCMD.CommandTimeout = 30; SqlDataAdapter dbDA = new SqlDataAdapter(); dbDA.SelectCommand = selectCMD; conn.Open(); DataSet dbDS = new DataSet(); dbDA.Fill(dbDS, "t"); return dbDS.Tables[0].Rows[0][0].ToString().Trim(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } finally { conn.Close(); } return ""; }
protected void BindSchemeChart() { DataTable dsGoalChartData = new DataTable(); StringBuilder strGoalSchChart = new StringBuilder(); string xCode = Session["userSessionID"].ToString(); //try //{ strGoalSchChart.Append(@"<script type='text/javascript'> AmCharts.ready(function () { generateChartData(); createStockChart(); }); var chartData1 = []; var chartData2 = []; var chartData3 = []; function generateChartData() { "); //Select No. of Months int xMonth = 0; string xSIPDt = ""; string xProjDate = ""; double xPrin = 0; //double xCumAmount = 0; double sipamt = Convert.ToDouble(ViewState["xSIPAmt"]); double actamt = Convert.ToDouble(ViewState["xActInv"]); double sUnit = 0; double aUnit = 0; double sCumUnit = 0; double aCumUnit = 0; double sProjectedAmt = 0; double aProjectedAmt = 0; double SuggestedAmt = 0; string xSIPDate = ""; string dd = ""; string mm = ""; string yy = ""; // Query for selecting SIP start Month and No. of month for SIP SqlDataAdapter NoMonDA = new SqlDataAdapter(" " + " select SIPStartDate=Convert(varchar(10),EntryDate,120), NoMonth=datediff(M,EntryDate,Startdate) from fp_GoalDetails " + " where GroupCode=(Select Code from fp_prospects where userid='" + ViewState["userid"] + "') and Code=" + ViewState["xGoalCode"] + " " + " ", Con); DataSet NoMonDS = new DataSet(); Con.Open(); NoMonDA.Fill(NoMonDS); Con.Close(); if (NoMonDS.Tables[0].Rows.Count > 0) { xMonth = Convert.ToInt32(NoMonDS.Tables[0].Rows[0]["NoMonth"]); xSIPDt = NoMonDS.Tables[0].Rows[0]["SIPStartDate"].ToString(); } CultureInfo culture = new CultureInfo("en-GB"); DateTime SIPDate; SIPDate = Convert.ToDateTime(xSIPDt, culture); for (int j = 1; j <= xMonth; j++) { double sNAV = 0; string xSIPStartDt = SIPDate.ToString(); dd = SIPDate.Day.ToString().PadLeft(2, '0'); mm = SIPDate.Month.ToString().PadLeft(2, '0'); yy = SIPDate.Year.ToString(); xSIPDate = yy + "-" + mm + "-" + dd; // Query for selecting NAV of SIP start Month SqlDataAdapter NavDA = new SqlDataAdapter(" " + " select NAV, SIPDate=Convert(varchar(10),NAVDate,120) from navexlc where exlcode=" + ViewState["xExlCode"] + " " + " and NavDate=(select Min(NAVDate) from navexlc where exlcode=" + ViewState["xExlCode"] + " and NavDate >= '" + xSIPDate + "') " + " ", Con); DataSet NavDS = new DataSet(); Con.Open(); NavDA.Fill(NavDS); Con.Close(); if (NavDS.Tables[0].Rows.Count > 0) { sNAV = Convert.ToDouble(NavDS.Tables[0].Rows[0]["NAV"]); xProjDate = NavDS.Tables[0].Rows[0]["SIPDate"].ToString(); // getting unit for Suggested SIP Amount based on every months SIP sUnit = (sipamt / sNAV); // getting unit for Actual Investment Amount based on every months SIP aUnit = (actamt / sNAV); if (j == 1) { sCumUnit = sUnit; aCumUnit = aUnit; sProjectedAmt = sCumUnit * sNAV; aProjectedAmt = aCumUnit * sNAV; } else { sCumUnit = sCumUnit + sUnit; aCumUnit = aCumUnit + aUnit; sProjectedAmt = sCumUnit * sNAV; aProjectedAmt = aCumUnit * sNAV; } strGoalSchChart.Append("chartData2.push({ date: new Date(" + yy + "," + mm + "," + dd + "), value: " + sProjectedAmt + " });"); strGoalSchChart.Append("chartData3.push({ date: new Date(" + yy + "," + mm + "," + dd + "), value: " + aProjectedAmt + " });"); } // Calculation for Suggested Amount with Risk if (j == 1) { xPrin = sipamt; SuggestedAmt = sipamt; } else { xPrin = SuggestedAmt + sipamt; SuggestedAmt = Math.Round(xPrin * Math.Pow((1 + Convert.ToDouble(xRiskPer) * 1.0000 / 100), 1), 0); } strGoalSchChart.Append("chartData1.push({ date: new Date(" + yy + "," + mm + "," + dd + "), value: " + SuggestedAmt + " });"); SIPDate = SIPDate.AddMonths(j); } strGoalSchChart.Append(@"} function createStockChart() { var chartScheme = new AmCharts.AmStockChart(); chartScheme.pathToImages = '../ChartNew/amcharts/images/'; // DATASETS ////////////////////////////////////////// // create data sets first var dataSet1 = new AmCharts.DataSet(); dataSet1.title = 'Inv. on suggested amt'; dataSet1.fieldMappings = [{ fromField: 'value', toField: 'value' }, { fromField: 'volume', toField: 'volume' }]; dataSet1.dataProvider = chartData1; dataSet1.categoryField = 'date'; dataSet1.lineThickness = 1; var dataSet2 = new AmCharts.DataSet(); dataSet2.title = 'Suggested Amt. Performance'; dataSet2.fieldMappings = [{ fromField: 'value', toField: 'value' }, { fromField: 'volume', toField: 'volume' }]; dataSet2.dataProvider = chartData2; dataSet2.categoryField = 'date'; dataSet2.lineThickness = 1; var dataSet3 = new AmCharts.DataSet(); dataSet3.title = 'Actual Amt. Performance'; dataSet3.fieldMappings = [{ fromField: 'value', toField: 'value' }, { fromField: 'volume', toField: 'volume' }]; dataSet3.dataProvider = chartData3; dataSet3.categoryField = 'date'; dataSet3.lineThickness = 1; // set data sets to the chart chartScheme.dataSets = [dataSet1, dataSet2, dataSet3]; // PANELS /////////////////////////////////////////// // first stock panel var stockPanel1 = new AmCharts.StockPanel(); stockPanel1.showCategoryAxis = false; stockPanel1.title = 'Value'; stockPanel1.percentHeight = 70; // graph of first stock panel var graph1 = new AmCharts.StockGraph(); graph1.valueField = 'value'; graph1.comparable = true; graph1.compareField = 'value'; graph1.lineThickness = 1; stockPanel1.addStockGraph(graph1); // create stock legend stockPanel1.stockLegend = new AmCharts.StockLegend(); // second stock panel var stockPanel2 = new AmCharts.StockPanel(); stockPanel2.percentHeight = 10; stockPanel2.backgroundColor = '#ff0000'; var graph2 = new AmCharts.StockGraph(); //graph2.valueField = 'volume'; //graph2.type = 'column'; //graph2.showBalloon = false; graph2.fillAlphas = 1; //stockPanel2.addStockGraph(graph2); stockPanel2.stockLegend = new AmCharts.StockLegend(); // set panels to the chart chartScheme.panels = [stockPanel1, stockPanel2]; // OTHER SETTINGS //////////////////////////////////// sbsettings = new AmCharts.ChartScrollbarSettings(); sbsettings.graph = graph1; chartScheme.chartScrollbarSettings = sbsettings; // PERIOD SELECTOR /////////////////////////////////// var periodSelector = new AmCharts.PeriodSelector(); periodSelector.position = 'left'; periodSelector.inputFieldsEnabled = false; periodSelector.periods = [{ period: 'DD', count: 10, label: '10 days' }, { period: 'MM', count: 1, label: '1 month' }, { period: 'YYYY', count: 1, label: '1 year' }, { period: 'YTD', label: 'YTD' }, { period: 'MAX', selected: true, label: 'MAX' }]; chartScheme.periodSelector = periodSelector; // DATA SET SELECTOR var dataSetSelector = new AmCharts.DataSetSelector(); dataSetSelector.position = 'left'; dataSetSelector.width = 200; chartScheme.dataSetSelector = dataSetSelector; chartScheme.write('retirementChartDiv'); } </script> " ); ltGoalSchChart.Text = strGoalSchChart.ToString(); //} //catch //{ //} //finally //{ dsGoalChartData.Dispose(); strGoalSchChart.Clear(); //} }
private void button2_Click(object sender, EventArgs e) { String Querry; if (this.richTextBox1.Text == "") { Querry = "select * from _User"; try { if (dataGridView1.Rows.Count == 0) { SqlCommand prt = new SqlCommand(Querry, Important.conex); SqlDataAdapter da = new SqlDataAdapter(prt); DataSet ds = new DataSet(); da.Fill(ds); dataGridView1.DataSource = ds.Tables[0]; } else { dataGridView1.DataSource = null; dataGridView1.Refresh(); } } catch (Exception ex) { Important.message = ex.GetType().ToString(); DialogMessageP dial = new DialogMessageP(); dial.ShowDialog(); } } else { Querry = "select * from _User where " + this.richTextBox1.Text; try { SqlCommand prt = new SqlCommand(Querry, Important.conex); SqlDataAdapter da = new SqlDataAdapter(prt); DataSet ds = new DataSet(); da.Fill(ds); dataGridView1.DataSource = ds.Tables[0]; } catch (Exception ex) { Important.message = ex.GetType().ToString(); DialogMessageP dial = new DialogMessageP(); dial.ShowDialog(); } } this.textBox1.Text = ""; this.textBox6.Text = ""; this.textBox2.Text = ""; this.textBox3.Text = ""; this.textBox4.Text = ""; this.textBox5.Text = ""; this.richTextBox1.Text = ""; }
private void comboBox_SelectionChanged(object sender, SelectionChangedEventArgs e) { da.SelectCommand.CommandText = "select * from StudentTable where StuNo='" + comboBox.SelectedItem.ToString() + "'"; ds.Clear(); da.Fill(ds, "StudentTable"); }
private void btn_trie_Click(object sender, EventArgs e) { DA = new SqlDataAdapter("Select * from clients where ville ='" + cbville.Text + "'", cnx); DA.Fill(DS, "clients"); dataGridView1.DataSource = DS.Tables["clients"]; }
//填充操作员信息 LogOperatorID public static void ReceiveOperatorInfo(string userid) { //根据LogOperator返回雇员信息 opinfo.OpID = userid; System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(); conn.ConnectionString = ConnStr; try { SqlCommand selectCMD = new SqlCommand("SELECT * FROM Operator where OperatorID='" + userid + "'", conn); selectCMD.CommandTimeout = 30; SqlDataAdapter dbDA = new SqlDataAdapter(); dbDA.SelectCommand = selectCMD; conn.Open(); DataSet dbDS = new DataSet(); dbDA.Fill(dbDS, "t"); //获得 EmployeeID->empno string empno; empno = dbDS.Tables[0].Rows[0]["EmpID"].ToString(); opinfo.EmpID = empno; //根据EmpNo返回EmployeeName 和 Department selectCMD.Dispose(); selectCMD.Connection = conn; selectCMD.CommandText = "SELECT * FROM Employee where EmpID='" + empno + "'"; dbDA.SelectCommand = selectCMD; dbDS.Clear(); dbDA.Fill(dbDS, "t"); opinfo.EmployeeName = dbDS.Tables[0].Rows[0]["name"].ToString(); //返回部门名称 string depno; depno = dbDS.Tables[0].Rows[0]["depcode"].ToString(); selectCMD.Dispose(); selectCMD.Connection = conn; selectCMD.CommandText = "SELECT * FROM Department where depcode='" + depno + "'"; dbDA.SelectCommand = selectCMD; dbDS.Clear(); dbDA.Fill(dbDS, "t"); opinfo.Department = dbDS.Tables[0].Rows[0]["deptname"].ToString(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } finally { conn.Close(); } }
public string getchildes(int x, string UTID) { SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Con"].ConnectionString); string SubString = ""; int k = 0; string str = "select distinct FormID As id, FormTitle As name , PID As parentid , Path As path from VW_MENU where PID=" + x + " and UTID='" + UTID + "' order by FormID"; SqlDataAdapter da = new SqlDataAdapter(str, Conn); DataTable dtA = new DataTable(); da.Fill(dtA); int noOfChild = dtA.Rows.Count; for (int i = 0; i < dtA.Rows.Count; i++) { if (dtA.Rows.Count == 0) { SubString = ""; } else { string hasChild = getSubchildes(Convert.ToInt32(dtA.Rows[i]["id"].ToString()), UTID); if (hasChild == "") { string ActiveChild = ""; string path = dtA.Rows[i]["path"].ToString(); path = path.ToLowerInvariant(); if (PageURL.IndexOf(path) != -1) { ActiveChild = " class=active"; } SubString = SubString + "<li>"; SubString = SubString + "<a href='" + "../" + dtA.Rows[i]["path"].ToString() + "" + "'>"; SubString = SubString + "<span class='menu-text'>" + dtA.Rows[i]["name"].ToString() + "</span>"; // SubString = SubString + "<span class='menu-badge'><span class='badge vd_bg-yellow'>NEW</span></span>"; SubString = SubString + "</a>"; SubString = SubString + "</li>"; } else { string ActiveParent = ""; if (hasChild.IndexOf("class=active") != -1) { ActiveParent = " active"; } string TotalChild = hasChild; TotalChild = TotalChild.Substring(0, TotalChild.IndexOf("`")); hasChild = hasChild.Substring(hasChild.IndexOf("`") + 1); //SubString = SubString + "<li><a><i class='fa fa-edit'></i>" + dtA.Rows[i]["name"].ToString() + ""; //SubString = SubString + "<span class='fa fa-chevron-down'></span></a>"; //SubString = SubString + "</a>"; //SubString = SubString + "<ul class='nav child_menu'>"; //SubString = SubString + hasChild; //SubString = SubString + "</ul></li>"; SubString = SubString + "<li>"; SubString = SubString + "<a href='" + "../" + dtA.Rows[i]["path"].ToString() + "" + "'>"; SubString = SubString + "<span class='menu-text'>" + dtA.Rows[i]["name"].ToString() + "</span>"; //SubString = SubString + "<span class='menu-badge'><span class='badge vd_bg-yellow'>NEW</span></span>"; SubString = SubString + "</a>"; SubString = SubString + "</li>"; } } } if (noOfChild > 0) { SubString = noOfChild + "`" + SubString; } return(SubString); }
protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e) { if (Session["company_id"] != null) { company_id = Convert.ToInt32(Session["company_id"].ToString()); } SqlConnection con1 = new SqlConnection(ConfigurationManager.AppSettings["connection"]); SqlCommand CMD = new SqlCommand("select * from product_stock where Com_Id='" + DropDownList1.SelectedItem.Value + "' ORDER BY purchase_invoice asc", con1); DataTable dt1 = new DataTable(); con1.Open(); SqlDataAdapter da1 = new SqlDataAdapter(CMD); da1.Fill(dt1); GridView1.DataSource = dt1; GridView1.DataBind(); if (Session["company_id"] != null) { company_id = Convert.ToInt32(Session["company_id"].ToString()); } SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings["connection"]); SqlCommand cmd = new SqlCommand("Select * from category where Com_Id='" + DropDownList1.SelectedItem.Value + "' ORDER BY category_id asc", con); con.Open(); DataSet ds = new DataSet(); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(ds); DropDownList3.DataSource = ds; DropDownList3.DataTextField = "categoryname"; DropDownList3.DataValueField = "category_id"; DropDownList3.DataBind(); DropDownList3.Items.Insert(0, new ListItem("All", "0")); con.Close(); if (Session["company_id"] != null) { company_id = Convert.ToInt32(Session["company_id"].ToString()); } SqlConnection con2 = new SqlConnection(ConfigurationManager.AppSettings["connection"]); SqlCommand cmd1 = new SqlCommand("Select * from product_stock where Com_Id='" + DropDownList1.SelectedItem.Value + "' ORDER BY no asc", con2); con2.Open(); DataSet ds1 = new DataSet(); SqlDataAdapter da2 = new SqlDataAdapter(cmd1); da2.Fill(ds1); DropDownList2.DataSource = ds1; DropDownList2.DataTextField = "barcode"; DropDownList2.DataValueField = "no"; DropDownList2.DataBind(); DropDownList2.Items.Insert(0, new ListItem("All", "0")); con2.Close(); company_id1 = Convert.ToInt32(DropDownList1.SelectedItem.Value); }
//检测当前客户是否允许签单 public static bool AllowSignPay(string customercode) { System.Data.SqlClient.SqlConnection conn1 = new System.Data.SqlClient.SqlConnection(); conn1.ConnectionString = ConnStr; try { SqlCommand selectCMD1 = new SqlCommand(); selectCMD1.Connection = conn1; selectCMD1.CommandText = "SELECT signed FROM customer where customercode='" + customercode + "'"; selectCMD1.CommandTimeout = 30; SqlDataAdapter dbDA1 = new SqlDataAdapter(); dbDA1.SelectCommand = selectCMD1; conn1.Open(); DataSet dbDS1 = new DataSet(); dbDA1.Fill(dbDS1, "t1"); if (dbDS1.Tables[0].Rows[0][0].ToString() == "1") { return true; } else { return false; } } catch (Exception ex) { MessageBox.Show(ex.ToString()); return false; } finally { conn1.Close(); } }
public DataTable SaveDriverApprovals(Approvals a) { //connect to database SqlConnection conn = new SqlConnection(); DataTable dt = new DataTable(); LogTraceWriter traceWriter = new LogTraceWriter(); try { //connetionString="Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password" conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["btposdb"].ToString(); SqlCommand cmd = new SqlCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "PSinsupddriverApprovals"; cmd.Connection = conn; SqlParameter LocationId = new SqlParameter("@Change", SqlDbType.VarChar); LocationId.Value = a.change; cmd.Parameters.Add(LocationId); SqlParameter parentid = new SqlParameter("@IsApproved", SqlDbType.Int); parentid.Value = a.IsApproved; cmd.Parameters.Add(parentid); SqlParameter flag = new SqlParameter("@MobileNo", SqlDbType.VarChar); flag.Value = a.MobileNo; cmd.Parameters.Add(flag); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(dt); #region email opt string eotp = dt.Rows[0]["Email"].ToString(); if (eotp != null) { try { MailMessage mail = new MailMessage(); string emailserver = System.Configuration.ConfigurationManager.AppSettings["emailserver"].ToString(); string username = System.Configuration.ConfigurationManager.AppSettings["username"].ToString(); string pwd = System.Configuration.ConfigurationManager.AppSettings["password"].ToString(); string fromaddress = System.Configuration.ConfigurationManager.AppSettings["fromaddress"].ToString(); string port = System.Configuration.ConfigurationManager.AppSettings["port"].ToString(); SmtpClient SmtpServer = new SmtpClient(emailserver); mail.From = new MailAddress(fromaddress); mail.To.Add(fromaddress); mail.Subject = "Driver Approval"; mail.IsBodyHtml = true; string verifcodeMail = @"<table> <tr> <td> <h2>Thank you for registering with PaySmart APP</h2> <table width=\""760\"" align=\""center\""> <tbody style='background-color:#F0F8FF;'> <tr> <td style=\""font-family:'Zurich BT',Arial,Helvetica,sans-serif;font-size:15px;text-align:left;line-height:normal;background-color:#F0F8FF;\"" > <div style='padding:10px;border:#0000FF solid 2px;'> <br /><br /> Driver has been Approved for:<h3>" + eotp + @" </h3> If you didn't make this request, <a href='http://154.120.237.198:52800'>click here</a> to cancel. <br/> <br/> Warm regards,<br> PAYSMART Customer Service Team<br/><br /> </div> </td> </tr> </tbody> </table> </td> </tr> </table>"; mail.Body = verifcodeMail; //SmtpServer.Port = 465; //SmtpServer.Port = 587; SmtpServer.Port = Convert.ToInt32(port); SmtpServer.UseDefaultCredentials = false; SmtpServer.Credentials = new System.Net.NetworkCredential(username, pwd); SmtpServer.EnableSsl = true; //SmtpServer.TargetName = "STARTTLS/smtp.gmail.com"; SmtpServer.Send(mail); } catch (Exception ex) { traceWriter.Trace(Request, "0", TraceLevel.Error, "{0}", "Register driver Email otp failed...." + ex.Message); throw ex; } } //send mobile otp // return dt; #endregion email otp } catch (Exception ex) { if (conn != null && conn.State == ConnectionState.Open) { conn.Close(); } throw ex; } return dt; }
private void button4_Click(object sender, EventArgs e) { string Update; string Select; string Check; try { Check = "select FullName from _User where " + this.richTextBox1.Text + ";"; SqlCommand comCheck1 = new SqlCommand(Check, Important.conex); if (this.textBox1.Text == "") { this.textBox1.Text = comCheck1.ExecuteScalar().ToString(); } Check = "select Username from _User where " + this.richTextBox1.Text + ";"; SqlCommand comCheck2 = new SqlCommand(Check, Important.conex); if (this.textBox2.Text == "") { this.textBox2.Text = comCheck2.ExecuteScalar().ToString(); } Check = "select Email from _User where " + this.richTextBox1.Text + ";"; SqlCommand comCheck3 = new SqlCommand(Check, Important.conex); if (this.textBox3.Text == "") { this.textBox3.Text = comCheck3.ExecuteScalar().ToString(); } Check = "select Password from _User where " + this.richTextBox1.Text + ";"; SqlCommand comCheck4 = new SqlCommand(Check, Important.conex); if (this.textBox4.Text == "") { this.textBox4.Text = comCheck4.ExecuteScalar().ToString(); } Check = "select UserType from _User where " + this.richTextBox1.Text + ";"; SqlCommand comCheck5 = new SqlCommand(Check, Important.conex); if (this.textBox5.Text == "") { this.textBox5.Text = comCheck5.ExecuteScalar().ToString(); } Check = "select Charge from _User where " + this.richTextBox1.Text + ";"; SqlCommand comCheck6 = new SqlCommand(Check, Important.conex); if (this.textBox6.Text == "") { this.textBox6.Text = comCheck6.ExecuteScalar().ToString(); } Update = "update _User set FullName = '" + this.textBox1.Text + "', Username = '******', Email = '" + this.textBox3.Text + "', Password = '******', UserType = '" + this.textBox5.Text + "', Charge = " + Convert.ToDouble(this.textBox6.Text) + " where " + this.richTextBox1.Text + ";"; SqlCommand upd = new SqlCommand(Update, Important.conex); upd.ExecuteNonQuery(); } catch (Exception ex) { Important.message = ex.GetType().ToString(); DialogMessageP dial = new DialogMessageP(); dial.ShowDialog(); } Select = "select * from _User"; SqlCommand sel = new SqlCommand(Select, Important.conex); SqlDataAdapter da = new SqlDataAdapter(sel); DataSet ds = new DataSet(); da.Fill(ds); dataGridView1.DataSource = ds.Tables[0]; this.textBox1.Text = ""; this.textBox6.Text = ""; this.textBox2.Text = ""; this.textBox3.Text = ""; this.textBox4.Text = ""; this.textBox5.Text = ""; this.richTextBox1.Text = ""; }