public void CreateGetClothingProcedureIfNull() { using (SqlConnection con = new SqlConnection(Settings.AdventureWorksConStr)) { SqlCommand checkIfProcExists = new SqlCommand(); checkIfProcExists.CommandText = "IF (OBJECT_ID('GetProductsByGender') IS NOT NULL) " + "DROP PROCEDURE GetProductsByGender " + ""; checkIfProcExists.Connection = con; con.Open(); using (SqlDataReader dr = checkIfProcExists.ExecuteReader()) { } con.Close(); SqlCommand command = new SqlCommand(); command.CommandText = "CREATE PROCEDURE GetProductsByGender (@strGender varchar(5)) " + "AS " + "SELECT ProductModelID, Name " + "FROM Production.ProductModel " + "WHERE Name LIKE @strGender"; command.Connection = con; con.Open(); using (SqlDataReader dr = command.ExecuteReader()) { } } }
public requirements Insert(requirements id) { string ConnectionString = IDManager.connection(); SqlConnection con = new SqlConnection(ConnectionString); try { con.Open(); SqlCommand cmd = new SqlCommand("SP_DMCS_INSERT_REQUIREMENTS", con); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@requirement_desc", id.requirement_desc); cmd.ExecuteReader(); con.Close(); con.Open(); cmd = new SqlCommand("SP_DMCS_GET_REQUIREMENTS", con); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@requirement_desc", id.requirement_desc); SqlDataReader rdr = cmd.ExecuteReader(); if (rdr.HasRows) { rdr.Read(); id.req_id = rdr.GetInt32(0); } } catch (Exception ex) { id.SetColumnDefaults(); } finally { con.Close(); } return id; }
public void ProcessRequest(HttpContext context) { context.Response.ContentType = "text/plain"; string flag = context.Request["flag"].ToString(); string struid = context.Session["ID"].ToString(); string strSQL = "update dbo.tabUsers set "; string strDataConn = ConfigurationManager.ConnectionStrings["SQLDataConnStr"].ConnectionString; SqlConnection dataConn = new SqlConnection(strDataConn); if (flag == "1") { string strusername = context.Request["username"].ToString(); string strutell = context.Request["utell"].ToString(); string struemail = context.Request["uemail"].ToString(); strSQL += "UserName = '******',"+" UserTel = '"+strutell+"',"+"UserEmail = '"+struemail+"' where ID = '"+struid+"'"; SqlCommand command = new SqlCommand(strSQL, dataConn); dataConn.Open(); command.ExecuteNonQuery(); dataConn.Close(); context.Response.Write("OK"); } else if (flag == "2") { string strpw = context.Request["upassword"].ToString(); strSQL += "UserPW = '" + strpw + "' where UserID = '" + struid + "'"; SqlCommand command = new SqlCommand(strSQL, dataConn); dataConn.Open(); command.ExecuteNonQuery(); dataConn.Close(); context.Response.Write("OK"); } }
public void RegisterDiscussMessages() { using (var connection = new SqlConnection(_connString)) { connection.Open(); using (var command = new SqlCommand(@"SELECT [Id] ,[Username] ,[Message] ,[FilePath] ,[Status] ,[CreatedDate] FROM [dbo].[Discuss] ORDER BY [dbo].[Discuss].CreatedDate desc", connection)) { command.Notification = null; var dependency = new SqlDependency(command); dependency.OnChange += new OnChangeEventHandler(dependency_OnChange); if (connection.State == ConnectionState.Closed) connection.Open(); var reader = command.ExecuteReader(); } } }
public static string ConfirmQrCode(int qrCode, string newUserOpenId) { string originalUserOpenId = ""; SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["constr"].Trim()); SqlCommand cmd = new SqlCommand(" select qr_invite_list_owner from qr_invite_list where qr_invite_list_id = " + qrCode.ToString(), conn); conn.Open(); SqlDataReader drd = cmd.ExecuteReader(); if (drd.Read()) { originalUserOpenId = drd.GetString(0); } drd.Close(); conn.Close(); cmd.CommandText = " insert into qr_invite_list_detail ( qr_invite_list_detail_id , qr_invite_list_detail_openid ) " + " values ('" + qrCode.ToString() + "' , '" + newUserOpenId.Replace("'", "").Trim() + "' ) "; conn.Open(); try { cmd.ExecuteNonQuery(); } catch { } conn.Close(); return originalUserOpenId.Trim(); }
protected void ButtonLog_Click(object sender, EventArgs e) { SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["RegConnectionString"].ConnectionString); con.Open(); string checkUser = "******" + TextBoxUser.Text + "'"; SqlCommand com = new SqlCommand(checkUser, con); int temp = Convert.ToInt32(com.ExecuteScalar().ToString()); con.Close(); if (temp == 1) { con.Open(); string checkPass = "******" + TextBoxUser.Text + "'"; SqlCommand passCom = new SqlCommand(checkPass, con); string password = passCom.ExecuteScalar().ToString().Replace(" ",""); if (password == TextBoxPW.Text) { Session["new"] = TextBoxUser.Text; Response.Write("Password correct"); Response.Redirect("Claim.aspx"); } else { Response.Write("Password incorrect"); } } else { Response.Write("Username is incorrect"); } }
protected void btnSubmit_Click(object sender, EventArgs e) { DataTable dt = new DataTable(); DataTable dtNew = new DataTable(); using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString)) { using (SqlCommand cmd = new SqlCommand("select * from all_triathlon_timing", con)) { con.Open(); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(dt); con.Close(); int count = dt.Rows.Count; if (count != 0) { using (SqlCommand cmdNew = new SqlCommand("select GUID from all_triathlon_timing where BIBNumber='" + txt123Bib.Text.Trim() + "'", con)) { con.Open(); SqlDataAdapter daNew = new SqlDataAdapter(cmdNew); daNew.Fill(dtNew); string abc = dtNew.Rows[0][0].ToString(); Response.Redirect("GetCertificate.aspx?abc=" + abc); } } } } }
protected void btnChk_Click(object sender, EventArgs e) { string constr = WebConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString; SqlConnection conn = new SqlConnection(constr); conn.Open(); String Checkuser = "******" + TextUser.Text + "'"; SqlCommand com = new SqlCommand(Checkuser, conn); int temp = Convert.ToInt32(com.ExecuteScalar().ToString()); conn.Close(); if (temp == 1) { conn.Open(); string checkPasswordQuery = "select passWord from person where Username ='******'"; string test = "select Fname from person where Username ='******'"; string id = "select PersID from person where Username ='******'"; SqlCommand passComm = new SqlCommand(checkPasswordQuery, conn); SqlCommand testComm = new SqlCommand(test, conn); SqlCommand idComm = new SqlCommand(id,conn); string password = passComm.ExecuteScalar().ToString().Replace(" ", ""); string testtest = testComm.ExecuteScalar().ToString().Replace(" ", ""); string persid = idComm.ExecuteScalar().ToString().Replace(" ", ""); if (password == TextPass.Text) { Session["New"] = testtest; Session["status"] = true; Session["ID"] = persid; Response.Redirect("~/form"); } } }
protected void ButtonEnviar_Click(object sender, EventArgs e) { try { SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["RegistroConnectionString"].ConnectionString); conn.Open(); String contar_mensajes = "select count(*) from Mensaje_privado"; SqlCommand command = new SqlCommand(contar_mensajes, conn); int contador = Convert.ToInt32(command.ExecuteScalar().ToString()); conn.Close(); conn.Open(); String enviar_mensaje = "insert into Mensaje_privado (id_mensaje, id_remitente, id_buzon, leido, mensaje, asunto, fecha_de_envio) values (" + contador + ", " + Bandeja_Entrada.id_usuario + ", " + Bandeja_Entrada.id_destino + ", " + 0 + ", '" + TextBoxEM.Text + "', '" + LabelAsunto.Text + "', CURRENT_TIMESTAMP)"; command = new SqlCommand(enviar_mensaje, conn); command.ExecuteNonQuery(); Response.Write("Mensaje enviado!"); Panel1.Visible = false; ButtonVolver.Visible = true; conn.Close(); } catch (Exception ex) { } }
protected void Button4_Click(object sender, EventArgs e)//开启或关闭聊天室 { if (!DataAccess.chat.IsOpen()) { SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["HomeChatConnectionString"].ToString()); SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = "UPDATE [chat] SET isopen=0;UPDATE [chat] SET IsOpen=1 WHERE id=@id"; cmd.Parameters.AddWithValue("@id", chatnum); conn.Open(); cmd.ExecuteNonQuery(); conn.Close(); Application["IsOpen"] = 1; Response.Redirect("manage.aspx"); } else { SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["HomeChatConnectionString"].ToString()); SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = "UPDATE [chat] SET IsOpen=0 delete from KickedUser"; cmd.Parameters.AddWithValue("@id", chatnum); conn.Open(); cmd.ExecuteNonQuery(); conn.Close(); cmd.CommandText = "UPDATE [user] SET IsOnline=0 WHERE UserID<>1";//UserID=1的是用户名为‘大家’的用户,将永远开启 conn.Open(); cmd.ExecuteNonQuery(); conn.Close(); Application["IsOpen"] = 0; Response.Redirect("manage.aspx"); } }
private void onload() { string strSQLconnection = @"Data Source=(LocalDb)\v11.0;AttachDbFilename=|DataDirectory|\aviation.mdf; Trusted_Connection=True;User Instance=True"; SqlConnection sqlConnection = new SqlConnection(strSQLconnection); SqlCommand sqlCommand = new SqlCommand("SELECT p.username,p.nama,p.jawatan,p.bahagian,p.telefon_bimbit,p.telefon_pejabat,p.email FROM profil_pegawai p, supervisor s WHERE p.username = s.ic ORDER BY p.nama", sqlConnection); try { if (sqlConnection.State.Equals(ConnectionState.Open)) sqlConnection.Close(); if (sqlConnection.State.Equals(ConnectionState.Closed)) sqlConnection.Open(); SqlDataAdapter mySqlAdapter = new SqlDataAdapter(sqlCommand); DataSet myDataSet = new DataSet(); mySqlAdapter.Fill(myDataSet); gridpenyelia.DataSource = myDataSet; gridpenyelia.DataBind(); if (sqlConnection.State.Equals(ConnectionState.Open)) sqlConnection.Close(); if (sqlConnection.State.Equals(ConnectionState.Closed)) sqlConnection.Open(); } catch (SqlException ex) { } finally { } }
public List<DiscussMessageModel> RegisterDiscussMessages() { var messageChat = new List<DiscussMessageModel>(); using (var connection = new SqlConnection(_connString)) { connection.Open(); using (var command = new SqlCommand(@"SELECT * FROM [dbo].[Comments]", connection)) { command.Notification = null; var dependency = new SqlDependency(command); dependency.OnChange += new OnChangeEventHandler(dependency_OnChange); if (connection.State == ConnectionState.Closed) connection.Open(); var reader = command.ExecuteReader(); while (reader.Read()) { messageChat.Add(item: new DiscussMessageModel { Id = reader["Id"].ToString(), Username = reader["Username"].ToString(), Message = reader["Message"].ToString(), FilePath = reader["FilePath"].ToString(), Avatar = "dist/img/avatar.png", Status = reader["Status"].ToString(), CreatedDate = DateTime.Parse(reader["CreatedDate"].ToString()) }); } } } return messageChat; }
protected void Button_Login_Click(object sender, EventArgs e) { SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["RegistrationConnectionString"].ConnectionString); conn.Open(); string checkuser = "******" + TextBoxUserName.Text + "'"; SqlCommand con = new SqlCommand(checkuser, conn); int temp = Convert.ToInt32(con.ExecuteScalar().ToString()); conn.Close(); if (temp == 1) { conn.Open(); string checkPasswordQuery = "select password from User_Data where UserName='******'"; SqlCommand passComm = new SqlCommand(checkPasswordQuery, conn); string password = passComm.ExecuteScalar().ToString().Replace(" ",""); if (password == TextBoxPassword.Text) { Session["New"] = TextBoxUserName.Text; Response.Write("Login Successful"); Response.Redirect("ReportACrime.aspx"); } else { Response.Write("Password incorrect"); } } else { Response.Write("User does not exist "); } }
public IEnumerable<Message> GetData() { using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString)) { connection.Open(); using (SqlCommand command = new SqlCommand("SELECT [ID], [Date], [Message] FROM [dbo].[Messages]", connection)) { command.Notification = null; SqlDependency dependency = new SqlDependency(command); dependency.OnChange += DependencyOnChange; if (connection.State == ConnectionState.Closed) connection.Open(); using (var reader = command.ExecuteReader()) return reader.Cast<IDataRecord>() .Select(x => new Message() { ID = x.GetInt32(0), Date = x.GetDateTime(1), Text = x.GetString(2) }).ToList(); } } }
public job_category Insert(job_category id) { string ConnectionString = IDManager.connection(); SqlConnection con = new SqlConnection(ConnectionString); try { con.Open(); SqlCommand cmd = new SqlCommand("SP_DMCS_INSERT_JOB_CATEGORY", con); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@job_category_name", id.job_category_name); cmd.ExecuteReader(); con.Close(); con.Open(); cmd = new SqlCommand("SP_DMCS_GET_JOB_CATEGORY", con); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@job_category_name", id.job_category_name); SqlDataReader rdr = cmd.ExecuteReader(); if (rdr.HasRows) { rdr.Read(); id.job_cat_id = rdr.GetInt32(0); } } catch (Exception ex) { id.SetColumnDefaults(); } finally { con.Close(); } return id; }
//The DAL managers contain methods that assign parameters to Procedures Stored //in the Database. each method return relevant infromation - dataSets that binds //to grids, objects and values to be assigned and used and true/false values so //show if information in the DB has been entered/replaced/updated public bool CreateAccount(Customer newCustomer, Account newAccount) { bool userCreated = false, accountCreated = false; using (cxn = new SqlConnection(this.ConnectionString)) { using (cmd = new SqlCommand("spCreateCustomer", cxn)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@FirstName", SqlDbType.NVarChar, 50).Value = newCustomer.FirstName; cmd.Parameters.Add("@Surname", SqlDbType.NVarChar, 50).Value = newCustomer.Surname; cmd.Parameters.Add("@Email", SqlDbType.NVarChar, 50).Value = newCustomer.Email; cmd.Parameters.Add("@Phone", SqlDbType.NVarChar, 50).Value = newCustomer.Phone; cmd.Parameters.Add("@AddressLine1", SqlDbType.NVarChar, 50).Value = newCustomer.AddressLine1; cmd.Parameters.Add("@AddressLine2", SqlDbType.NVarChar, 50).Value = newCustomer.AddressLine2; cmd.Parameters.Add("@City", SqlDbType.NVarChar, 50).Value = newCustomer.City; cmd.Parameters.Add("@County", SqlDbType.NVarChar, 50).Value = newCustomer.County; cmd.Parameters.Add("@CustomerID", SqlDbType.Int, 4).Direction = ParameterDirection.Output; cmd.Parameters.Add("@OnlineCustomer", SqlDbType.Bit).Value = newCustomer.OnlineCustomer; cxn.Open(); int rows = cmd.ExecuteNonQuery(); cxn.Close(); if (rows > 0) { newCustomer.CustomerID = Convert.ToInt32(cmd.Parameters["@CustomerID"].Value); newAccount.CustomerID = newCustomer.CustomerID; userCreated = true; } } using (cmd = new SqlCommand("spCreateAccount", cxn)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@AccountType", SqlDbType.NVarChar, 50).Value = newAccount.AccountType; cmd.Parameters.Add("@SortCode", SqlDbType.Int, 8).Value = newAccount.SortCode; cmd.Parameters.Add("@InitialBalance", SqlDbType.Int, 8).Value = newAccount.Balance; cmd.Parameters.Add("@OverDraft", SqlDbType.Int, 8).Value = newAccount.OverDraftLimit; cmd.Parameters.Add("@CustomerID", SqlDbType.Int, 4).Value = newAccount.CustomerID; cmd.Parameters.Add("@AccountNumber", SqlDbType.Int, 8).Direction = ParameterDirection.Output; cxn.Open(); int rows = cmd.ExecuteNonQuery(); cxn.Close(); if (rows > 0) { newAccount.AccountNumber = Convert.ToInt32(cmd.Parameters["@AccountNumber"].Value); accountCreated = true; } } if (userCreated && accountCreated) return true; else return false; } }
/// <summary> /// Добавление наград /// </summary> /// <param name="newR">Новая награда</param> /// <returns></returns> public bool AddReward(Reward newR) { using (var connection = new SqlConnection(ConnectionString())) { if (newR.Image == null) { var command = new SqlCommand( "INSERT INTO dbo.Rewards (Title, Description) " + "VALUES (@Title, @Description)", connection); command.Parameters.AddWithValue("@Title", newR.Title); command.Parameters.AddWithValue("@Description", newR.Description); connection.Open(); return command.ExecuteNonQuery() == 1; } else { var command = new SqlCommand( "INSERT INTO dbo.Rewards (Title, Description, Image) " + "VALUES (@Title, @Description, @Image)", connection); command.Parameters.AddWithValue("@Title", newR.Title); command.Parameters.AddWithValue("@Description", newR.Description); command.Parameters.AddWithValue("@Image", newR.Image); connection.Open(); return command.ExecuteNonQuery() == 1; } } }
protected void loginButton_Click(object sender, EventArgs e) { SqlConnection connection = new SqlConnection(connectionString); connection.Open(); string checkUser = "******"+usernameTextBox.Text+"' "; SqlCommand command = new SqlCommand(checkUser,connection); int temp = Convert.ToInt32(command.ExecuteScalar().ToString()); connection.Close(); if (temp==1) { connection.Open(); string checkPass = "******" + usernameTextBox.Text + "'"; SqlCommand passCommand = new SqlCommand(checkPass,connection); string password = passCommand.ExecuteScalar().ToString(); if (password==passTextBox.Text) { Session["loginSession"] = usernameTextBox.Text; msgLabel.Text="Login Successfull"; Response.Redirect("Home.aspx"); } else { msgLabel.Text = "Incorrect password !"; } } else { msgLabel.Text="UserName Is not Correct"; } }
protected void btn_Aemp_Click(object sender, EventArgs e) { string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString; using (SqlConnection con = new SqlConnection(CS)) { if (rd_Male.Checked == true) { SqlCommand cmd1 = new SqlCommand("select count(*) from Retail1_emp where Eid ='" + txt_Eid.Text + "'and Store='" + storeName + "'", con); SqlCommand cmd = new SqlCommand("insert into Retail1_emp(Eid,Ssn,Name,Sex,Title,Edate,Salary,Store) values ('" + txt_Eid.Text + "','" + txt_Ssn.Text + "','" + txt_Name.Text + "','" + rd_Male.Text + "','" + txt_Title.Text + "','" + txt_Edate.Text + "','" + txt_Salary.Text + "','" + storeName +"')", con); con.Open(); int count = Convert.ToInt32(cmd1.ExecuteScalar().ToString()); if (count == 1) { lbl_Aemp.Text = "EMPLOYEE ALREADY EXISTS!!"; } else { cmd.ExecuteNonQuery(); lbl_Aemp.Text = "EMPLOYEE ADDED SUCCESSFULLY!!"; } //cmd.ExecuteNonQuery(); // MultiView1.ActiveViewIndex = 5; txt_Eid.Text = ""; txt_Name.Text = ""; txt_Title.Text = ""; txt_Salary.Text = ""; txt_Edate.Text = ""; txt_Ssn.Text = ""; } else if (rd_Female.Checked == true) { SqlCommand cmd2 = new SqlCommand("select count(*) from Retail1_emp where Eid ='" + txt_Eid.Text + "'and Store='" + storeName + "'", con); SqlCommand cmd = new SqlCommand("insert into Retail1_emp(Eid,Ssn,Name,Sex,Title,Edate,Salary,Store) values ('" + txt_Eid.Text + "','" + txt_Ssn.Text + "','" + txt_Name.Text + "','" + rd_Female.Text + "','" + txt_Title.Text + "','" + txt_Edate.Text + "','" + txt_Salary.Text + "','" + storeName + "')", con); con.Open(); int count = Convert.ToInt32(cmd2.ExecuteScalar().ToString()); if (count == 1) { lbl_Aemp.Text = "EMPLOYEE ALREADY EXISTS!!"; } else { cmd.ExecuteNonQuery(); lbl_Aemp.Text = "EMPLOYEE ADDED SUCCESSFULLY!!"; } // cmd.ExecuteNonQuery(); // MultiView1.ActiveViewIndex = 5; txt_Eid.Text = ""; txt_Name.Text = ""; txt_Title.Text = ""; txt_Salary.Text = ""; txt_Edate.Text = ""; txt_Ssn.Text = ""; } } MultiView1.ActiveViewIndex = 5; lbl_Aemp.Visible = true; }
protected void LoginButton_Click(object sender, EventArgs e) { PasswordErrorLB.Text = ""; UserNameErrorLB.Text = ""; SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["RegistrationConnectionString"].ConnectionString); conn.Open(); string checkUser = "******" + UserNameTB.Text + "'"; SqlCommand com = new SqlCommand(checkUser, conn); int numUsers = Convert.ToInt32(com.ExecuteScalar().ToString()); conn.Close(); if (numUsers == 1) { conn.Open(); string checkPassword = "******" + UserNameTB.Text + "'"; SqlCommand com2 = new SqlCommand(checkPassword, conn); string password = com2.ExecuteScalar().ToString().Replace(" ", ""); conn.Close(); if(password == PasswordTB.Text) { Session["New"] = UserNameTB.Text; Response.Redirect("MainMenuPage.aspx"); } else { PasswordErrorLB.Text = "You Must Enter a Correct Password"; } } else { UserNameErrorLB.Text = "You Must Enter a Correct Username"; } }
/// <summary> /// Kiem tra ket noi DataBase /// </summary> /// <returns></returns> public bool CheckConnection() { // Adapter and DataSet conn = Utils.getConnection(); strSQL = "SELECT Count(EmployeeID) FROM tblEmployee"; sqlCommand = new SqlCommand(strSQL, conn); try { if (conn.State == ConnectionState.Broken) { conn.Close(); conn.Open(); } else if(conn.State == ConnectionState.Closed) { conn.Open(); } sqlCommand.ExecuteNonQuery(); return true; } catch(Exception) { return false; } finally { conn.Close(); } }
public demobilization Insert(demobilization id) { string ConnectionString = IDManager.connection(); SqlConnection con = new SqlConnection(ConnectionString); try { con.Open(); SqlCommand cmd = new SqlCommand("SP_DMCS_INSERT_DEMOBILIZATION", con); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.ExecuteReader(); con.Close(); con.Open(); cmd = new SqlCommand("SP_DMCS_GET_DEMOBILIZATION", con); cmd.CommandType = System.Data.CommandType.StoredProcedure; SqlDataReader rdr = cmd.ExecuteReader(); if (rdr.HasRows) { rdr.Read(); id.demo_id = rdr.GetInt32(0); } } catch (Exception ex) { id.SetColumnDefaults(); } finally { con.Close(); } return id; }
protected void Button2_Click(object sender, EventArgs e) { SqlConnection con = new SqlConnection(constr); con.Open(); SqlCommand cmd2 = new SqlCommand(" UPDATE CPE02 SET Approve='1' WHERE ProjectID = '" + DropDownList2.SelectedValue.ToString() + "' ", con); cmd2.ExecuteNonQuery(); con.Close(); Button2.Visible = true; string text = "select * from StudentProject s join CPE02 c on s.ProjectID = c.ProjectID where s.Advisorusername='******' and s.ProjectID = @ProjectID"; if (DropDownList1.SelectedValue.ToString() == "approved") { Button2.Visible = false; text = "select * from StudentProject s join CPE02 c on s.ProjectID = c.ProjectID where s.Advisorusername='******' and s.ProjectID = @ProjectID and c.Approve = '1'"; } else if (DropDownList1.SelectedValue.ToString() == "waiting") { text = "select * from StudentProject s join CPE02 c on s.ProjectID = c.ProjectID where s.Advisorusername='******' and s.ProjectID = @ProjectID and c.Approve = '0'"; } if (DropDownList2.SelectedValue != "choose") { con.Open(); SqlCommand cmd = new SqlCommand(text, con); cmd.Parameters.AddWithValue("@ProjectID", DropDownList2.SelectedValue.ToString()); SqlDataReader dr = cmd.ExecuteReader(); GridView1.DataSource = dr; GridView1.DataBind(); } else { Button2.Visible = false; } }
public void run() { string liveConString = ConfigurationManager.AppSettings["dailyconstring"]; string reportConString = ConfigurationManager.AppSettings["dailyconstringReportingServer"]; string EngageConString = ConfigurationManager.AppSettings["engageconstring"]; string VidConString = ConfigurationManager.AppSettings["dailyconstringReportingServer"]; string targetMonth = ConfigurationManager.AppSettings["TargetMonth"]; string previousMonth = ConfigurationManager.AppSettings["PreviousMonth"]; string monthEndSubsUpdateQueries = ConfigurationManager.AppSettings["MonthEndSubsUpdateQueries"];//'|' delimited, string replace targetmonth,previousmonth //string NowConString = ConfigurationManager.AppSettings[""]; try { SqlConnection con = new SqlConnection(); DataSet dsResult = new DataSet(); con.ConnectionString = reportConString; con.Open(); SqlCommand cmd = new SqlCommand(); cmd.CommandTimeout = 1200; //cmd.CommandText = @"select case when count(*) =0 then 0 else sum(t.numbers) end from (select isnull(count(distinct u.userid),0) as 'numbers' from users u inner join country c on u.countrycode=c.code inner join gomssubsidiary g on c.gomssubsidiaryid=g.gomssubsidiaryid where cast(registrationdate as date) = '" + traverserDate.Date.ToString("yyyy-MM-dd") + @"' and case when g.gomssubsidiaryid=6 or g.gomssubsidiaryid=8 or g.gomssubsidiaryid=9 then 'GLB : EU' else g.code end = 'GLB : " + region + @"' group by case when g.gomssubsidiaryid=6 or g.gomssubsidiaryid=8 or g.gomssubsidiaryid=9 then 'GLB : EU' else g.code end) as t union all select case when count(*) =0 then 0 else sum(t.numbers) end from (select isnull(count(distinct er.userid),0) as 'numbers' from entitlementrequests er inner join users u on u.userid=er.userid inner join country c on u.countrycode=c.code inner join gomssubsidiary g on c.gomssubsidiaryid=g.gomssubsidiaryid inner join transactions t on er.userid=t.userid left outer join ppctype ppc on left(t.reference,2)=ppc.ppcproductcode left join purchaseitems pu on pu.entitlementrequestid = er.entitlementrequestid where er.productid in (1,3,4,5,6,7,8,9) and er.source <>'TFC.tv Everywhere' and cast(er.enddate as date)>='" + traverserDate.AddDays(1).ToString("yyyy-MM-dd") + @"' and er.daterequested<='" + traverserDate.AddDays(1).ToString("yyyy-MM-dd") + @"' and (subscriptionppctypeid is null or subscriptionppctypeid not in (2,3,4)) and case when g.gomssubsidiaryid=6 or g.gomssubsidiaryid=8 or g.gomssubsidiaryid=9 then 'GLB : EU' else g.code end = 'GLB : " + region + @"' group by case when g.gomssubsidiaryid=6 or g.gomssubsidiaryid=8 or g.gomssubsidiaryid=9 then 'GLB : EU' else g.code end) as t union all select case when count(*) =0 then 0 else sum(t.numbers) end from (select isnull(count(distinct er.userid),0) as 'numbers' from entitlementrequests er inner join users u on u.userid=er.userid inner join country c on u.countrycode=c.code inner join gomssubsidiary g on c.gomssubsidiaryid=g.gomssubsidiaryid inner join transactions t on er.userid=t.userid left outer join ppctype ppc on left(t.reference,2)=ppc.ppcproductcode left join purchaseitems pu on pu.entitlementrequestid = er.entitlementrequestid where er.productid in (1,3,4,5,6,7,8,9) and er.source <>'TFC.tv Everywhere' and cast(er.enddate as date)='" + traverserDate.ToString("yyyy-MM-dd") + @"' and er.daterequested<='" + traverserDate.AddDays(1).ToString("yyyy-MM-dd") + @"' and (subscriptionppctypeid is null or subscriptionppctypeid not in (2,3,4)) and case when g.gomssubsidiaryid=6 or g.gomssubsidiaryid=8 or g.gomssubsidiaryid=9 then 'GLB : EU' else g.code end = 'GLB : " + region + @"' group by case when g.gomssubsidiaryid=6 or g.gomssubsidiaryid=8 or g.gomssubsidiaryid=9 then 'GLB : EU' else g.code end) as t union all select case when count(*) =0 then 0 else sum(t.numbers) end from (select isnull(count(distinct userid),0) as 'numbers' from ( select u.email, er.userid,u.firstname,u.lastname,er.productid,er.enddate,u.countrycode from entitlementrequests er inner join users u on u.userid=er.userid inner join transactions t on er.userid=t.userid left outer join ppctype ppc on left(t.reference,2)=ppc.ppcproductcode left outer join purchaseitems pu on pu.entitlementrequestid = er.entitlementrequestid where er.productid in (1,3,4,5,6,7,8,9) and er.source <>'TFC.tv Everywhere' and (subscriptionppctypeid is null or subscriptionppctypeid not in (2,3,4)) and cast(er.daterequested as date)= '" + traverserDate.ToString("yyyy-MM-dd") + @"' and er.UserId not in( select er.userid from entitlementrequests er inner join users u on u.userid=er.userid inner join transactions t on er.userid=t.userid left outer join ppctype ppc on left(t.reference,2)=ppc.ppcproductcode left outer join purchaseitems pu on pu.entitlementrequestid = er.entitlementrequestid where er.productid in (1,3,4,5,6,7,8,9) and er.source <>'TFC.tv Everywhere' and (subscriptionppctypeid is null or subscriptionppctypeid not in (2,3,4)) and cast(er.daterequested as date)< '" + traverserDate.ToString("yyyy-MM-dd") + @"' ) ) as temp inner join country c on c.code=temp.countrycode inner join gomssubsidiary g on c.gomssubsidiaryid=g.gomssubsidiaryid and case when g.gomssubsidiaryid=6 or g.gomssubsidiaryid=8 or g.gomssubsidiaryid=9 then 'GLB : EU' else g.code end = 'GLB : " + region + @"' group by case when g.gomssubsidiaryid=6 or g.gomssubsidiaryid=8 or g.gomssubsidiaryid=9 then 'GLB : EU' else g.code end) as t union all select case when count(*) =0 then 0 else sum(t.numbers) end from (select isnull(count(distinct userid),0) as 'numbers' from ( select u.email, er.userid,u.firstname,u.lastname,er.productid,er.enddate,u.countrycode from entitlementrequests er inner join users u on u.userid=er.userid inner join transactions t on er.userid=t.userid left outer join ppctype ppc on left(t.reference,2)=ppc.ppcproductcode left outer join purchaseitems pu on pu.entitlementrequestid = er.entitlementrequestid where er.productid in (1,3,4,5,6,7,8,9) and er.source <>'TFC.tv Everywhere' and (subscriptionppctypeid is null or subscriptionppctypeid not in (2,3,4)) and cast(er.daterequested as date)= '" + traverserDate.ToString("yyyy-MM-dd") + @"' and er.UserId in( select er.userid from entitlementrequests er inner join users u on u.userid=er.userid inner join transactions t on er.userid=t.userid left outer join ppctype ppc on left(t.reference,2)=ppc.ppcproductcode left outer join purchaseitems pu on pu.entitlementrequestid = er.entitlementrequestid where er.productid in (1,3,4,5,6,7,8,9) and er.source <>'TFC.tv Everywhere' and (subscriptionppctypeid is null or subscriptionppctypeid not in (2,3,4)) and cast(er.daterequested as date)< '" + traverserDate.ToString("yyyy-MM-dd") + @"' ) ) as temp inner join country c on c.code=temp.countrycode inner join gomssubsidiary g on c.gomssubsidiaryid=g.gomssubsidiaryid and case when g.gomssubsidiaryid=6 or g.gomssubsidiaryid=8 or g.gomssubsidiaryid=9 then 'GLB : EU' else g.code end = 'GLB : " + region + @"' group by case when g.gomssubsidiaryid=6 or g.gomssubsidiaryid=8 or g.gomssubsidiaryid=9 then 'GLB : EU' else g.code end) as t union all select case when count(*) =0 then 0 else sum(t.numbers) end from (select isnull(count(distinct er.userid),0) as 'numbers' from entitlementrequests er inner join users u on u.userid=er.userid inner join country c on c.code=u.countrycode inner join gomssubsidiary g on c.gomssubsidiaryid=g.gomssubsidiaryid inner join products p on p.productid=er.productid inner join transactions t on er.userid=t.userid left outer join ppctype ppc on left(t.reference,2)=ppc.ppcproductcode left join purchaseitems pu on pu.entitlementrequestid = er.entitlementrequestid where (p.alacartesubscriptiontypeid is not null or er.productid=2) and er.source <>'TFC.tv Everywhere' and cast(er.enddate as date)>='" + traverserDate.AddDays(1).ToString("yyyy-MM-dd") + @"' and er.daterequested<='" + traverserDate.AddDays(1).ToString("yyyy-MM-dd") + @"' and (subscriptionppctypeid is null or subscriptionppctypeid not in (2,3,4)) and case when g.gomssubsidiaryid=6 or g.gomssubsidiaryid=8 or g.gomssubsidiaryid=9 then 'GLB : EU' else g.code end = 'GLB : " + region + @"' group by case when g.gomssubsidiaryid=6 or g.gomssubsidiaryid=8 or g.gomssubsidiaryid=9 then 'GLB : EU' else g.code end) as t union all select case when count(*) =0 then 0 else sum(t.numbers) end from (select isnull(count(distinct userid),0) as 'numbers' from ( select u.email, er.userid,u.firstname,u.lastname,er.productid,er.enddate,u.countrycode from entitlementrequests er inner join users u on u.userid=er.userid inner join products p on p.productid=er.productid inner join transactions t on er.userid=t.userid left outer join ppctype ppc on left(t.reference,2)=ppc.ppcproductcode left outer join purchaseitems pu on pu.entitlementrequestid = er.entitlementrequestid where (p.alacartesubscriptiontypeid is not null or er.productid=2) and er.source <>'TFC.tv Everywhere' and (subscriptionppctypeid is null or subscriptionppctypeid not in (2,3,4)) and cast(er.daterequested as date)= '" + traverserDate.ToString("yyyy-MM-dd") + @"' ) as temp inner join country c on c.code=temp.countrycode inner join gomssubsidiary g on c.gomssubsidiaryid=g.gomssubsidiaryid where case when g.gomssubsidiaryid=6 or g.gomssubsidiaryid=8 or g.gomssubsidiaryid=9 then 'GLB : EU' else g.code end = 'GLB : " + region + @"' group by case when g.gomssubsidiaryid=6 or g.gomssubsidiaryid=8 or g.gomssubsidiaryid=9 then 'GLB : EU' else g.code end) as t union all select case when count(*) =0 then 0 else sum(t.numbers) end from (select isnull(count(distinct er.userid),0) as 'numbers' from entitlementrequests er inner join users u on u.userid=er.userid inner join country c on c.code=u.countrycode inner join gomssubsidiary g on c.gomssubsidiaryid=g.gomssubsidiaryid inner join products p on p.productid=er.productid inner join transactions t on er.userid=t.userid left outer join ppctype ppc on left(t.reference,2)=ppc.ppcproductcode left outer join purchaseitems pu on pu.entitlementrequestid = er.entitlementrequestid where (p.alacartesubscriptiontypeid is not null or er.productid=2) and er.source <>'TFC.tv Everywhere' and cast(er.enddate as date)='" + traverserDate.ToString("yyyy-MM-dd") + @"' and er.daterequested<='" + traverserDate.AddDays(1).ToString("yyyy-MM-dd") + @"' and (subscriptionppctypeid is null or subscriptionppctypeid not in (2,3,4)) and case when g.gomssubsidiaryid=6 or g.gomssubsidiaryid=8 or g.gomssubsidiaryid=9 then 'GLB : EU' else g.code end = 'GLB : " + region + @"' group by case when g.gomssubsidiaryid=6 or g.gomssubsidiaryid=8 or g.gomssubsidiaryid=9 then 'GLB : EU' else g.code end) as t"; cmd.Connection = con; con.Open(); cmd.ExecuteNonQuery(); } catch (Exception e) { Console.WriteLine(e.Message); } }
protected void BindDDL2() { SqlConnection con = new SqlConnection(constr); con.Open(); SqlCommand cmd1 = new SqlCommand("select DISTINCT s.ProjectID,s.ProjectnameTH from StudentProject s join CPE02 c on s.ProjectID = c.ProjectID where s.Advisorusername='******'", con); SqlDataReader reader1 = cmd1.ExecuteReader(); int count = 0; while (reader1.Read()) { count++; } con.Close(); if (count == 0) { DropDownList2.Items.Add(new ListItem("ไม่มีข้อมูล", "none")); DropDownList2.Items.FindByValue("none").Selected = true; } else { DataTable dt = new DataTable(); SqlCommand cmdxx = new SqlCommand("select DISTINCT s.ProjectID,s.ProjectnameTH from StudentProject s join CPE02 c on s.ProjectID = c.ProjectID where s.Advisorusername='******'", con); con.Open(); dt.Load(cmdxx.ExecuteReader()); con.Close(); DropDownList2.DataSource = dt; DropDownList2.DataTextField = "ProjectnameTH"; DropDownList2.DataValueField = "ProjectID"; DropDownList2.DataBind(); DropDownList2.Items.Add(new ListItem("Choose", "choose")); DropDownList2.Items.FindByValue("choose").Selected = true; } }
private void listBox1_Click(object sender, EventArgs e) { string strTableName = this.listBox1.SelectedValue.ToString();//获取数据表名称 using (SqlConnection con = new SqlConnection(//创建数据库连接对象 @"server=(local);uid=sa;Pwd=6221131;database='" + strDatabase + "'")) { string strSql =//创建SQL字符串 @"select name 字段名, xusertype 类型编号, length 长度 into hy_Linshibiao from syscolumns where id=object_id('" + strTableName + "') "; strSql +=//添加字符串信息 @"select name 类型,xusertype 类型编号 into angel_Linshibiao from systypes where xusertype in (select xusertype from syscolumns where id=object_id('" + strTableName + "'))"; con.Open();//打开数据库连接 SqlCommand cmd = new SqlCommand(strSql, con);//创建命令对象 cmd.ExecuteNonQuery();//执行SQL命令 con.Close();//关闭数据库连接 SqlDataAdapter da = new SqlDataAdapter(//创建数据适配器 @"select 字段名,类型,长度 from hy_Linshibiao t,angel_Linshibiao b where t.类型编号=b.类型编号", con); DataTable dt = new DataTable();//创建数据表 da.Fill(dt);//填充数据表 this.dataGridView1.DataSource = dt.DefaultView;//设置数据源 SqlCommand cmdnew = new SqlCommand(//创建命令对象 "drop table hy_Linshibiao,angel_Linshibiao", con); con.Open();//打开数据库连接 cmdnew.ExecuteNonQuery();//执行SQL命令 con.Close();//关闭数据库连接 } }
// Lấy Messages cần gửi public List<DiscussMessage> GetMessagesChat() { var messageChat = new List<DiscussMessage>(); using (var connection = new SqlConnection(_connString)) { connection.Open(); using (var command = new SqlCommand(@"SELECT dbo.Messages.Id, dbo.Messages.[Content], dbo.Messages.SenderId, dbo.Messages.SendDate, dbo.Messages.Type FROM dbo.Messages WHERE dbo.Messages.Sent = 'N' ORDER BY dbo.Messages.SendDate ASC", connection)) { command.Notification = null; if (connection.State == ConnectionState.Closed) connection.Open(); var reader = command.ExecuteReader(); while (reader.Read()) { messageChat.Add(item: new DiscussMessage { Id = reader["Id"].ToString(), Content = reader["Content"].ToString(), SenderId = reader["SenderId"].ToString(), UserName = "", SendDate = reader["SendDate"].ToString(), Type = reader["Type"].ToString(), GroupId = "" }); } } } return messageChat; }
public mobile_employees Insert(mobile_employees id) { string ConnectionString = IDManager.connection(); SqlConnection con = new SqlConnection(ConnectionString); try { con.Open(); SqlCommand cmd = new SqlCommand("SP_DMCS_INSERT_MOBILE_EMPLOYEES", con); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@mobile_unit_id", id.mobile_unit_id); cmd.Parameters.AddWithValue("@employee_id", id.employee_id); cmd.ExecuteReader(); con.Close(); con.Open(); cmd = new SqlCommand("SP_DMCS_GET_MOBILE_EMPLOYEES", con); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@mobile_unit_id", id.mobile_unit_id); cmd.Parameters.AddWithValue("@employee_id", id.employee_id); SqlDataReader rdr = cmd.ExecuteReader(); if (rdr.HasRows) { rdr.Read(); id.mobile_employee_id = rdr.GetInt32(0); } } catch (Exception ex) { id.SetColumnDefaults(); } finally { con.Close(); } return id; }
protected void Page_Load(object sender, EventArgs e) { con = new SqlConnection(ConfigurationManager.ConnectionStrings["devi"].ToString()); if (Request.QueryString["t"] != null) { temp = Convert.ToInt16(Request.QueryString["t"].ToString()); uid = Convert.ToInt16(Request.QueryString["id"].ToString()); if (temp == 1) { con.Open(); cmd = new SqlCommand("update user1_reg set userkey='1' where id='"+uid+"'",con); cmd.ExecuteNonQuery(); con.Close(); } if (temp == 2) { con.Open(); cmd = new SqlCommand("update user1_reg set userkey='2' where id='" + uid + "'", con); cmd.ExecuteNonQuery(); con.Close(); } } }
static void Main(string[] args) { String sc = @"Data Source=.\sqlexpress;Initial Catalog=MASTER;Integrated Security=true;Connect Timeout=1;Pooling=false"; SqlConnection c = null; try { c = new SqlConnection(sc); c.Open(); c.Open(); } catch (Exception ex) { Console.WriteLine(ex.GetType().Name); Console.WriteLine(ex.Message); Console.WriteLine(ex.Source); } finally { if (/*c != null &&*/ c.State != ConnectionState.Closed) c.Close(); Console.ReadKey(); } }
public static void TryOpen(this sql.SqlConnection connection) { if (connection?.State == System.Data.ConnectionState.Closed) { connection?.Open(); } }
public IEnumerable GetListD(ReportSearchViewModel model) { var List = new List <ReportInforViewModel>(); try { using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString)) { using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand()) { cmd.CommandText = "usp_BaoCaoDoanhThuTheoNhanVien"; cmd.Parameters.AddWithValue("@StoreId", model.StoreId); cmd.Parameters.AddWithValue("@StaffId", model.StaffId); cmd.Parameters.AddWithValue("@CashierUserId", model.CashierUserId); cmd.Parameters.AddWithValue("@CurrentSaleId", currentEmployee.EmployeeId); cmd.Parameters.AddWithValue("@FromDate", model.FromDate); cmd.Parameters.AddWithValue("@ToDate", model.ToDate); cmd.Connection = conn; cmd.CommandType = CommandType.StoredProcedure; conn.Open(); using (var dr = cmd.ExecuteReader()) { while (dr.Read()) { var v = ListView(dr); List.Add(v); } } conn.Close(); } } return(List); } catch { return(List); } }
} //========== end of the mtd public IEnumerable <Models.OPEX_DETAIL_SHARE_BUD> GetAllOPEXDetailShareBUDMtd_2(string svalue) { List <OPEX_DETAIL_SHARE_BUD> oDetailList = new List <OPEX_DETAIL_SHARE_BUD>(); using (var con = new System.Data.SqlClient.SqlConnection(connectionString)) { var cmd = new System.Data.SqlClient.SqlCommand("spp_proc_sharedcost_2", con); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.CommandTimeout = 0; cmd.Parameters.Add(new SqlParameter { ParameterName = "SearchValue", Value = svalue, }); con.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { var op = new OPEX_DETAIL_SHARE_BUD(); op.TEAM_CODE = reader["TEAM_CODE"] != DBNull.Value ? reader["TEAM_CODE"].ToString() : "null"; op.BranchName = reader["BranchName"] != DBNull.Value ? reader["BranchName"].ToString() : "null"; op.RegionName = reader["RegionName"] != DBNull.Value ? reader["RegionName"].ToString() : "null"; op.DIRECTORATENAME = reader["DIRECTORATENAME"] != DBNull.Value ? reader["DIRECTORATENAME"].ToString() : "null"; op.CAPTION = reader["CAPTION"] != DBNull.Value ? reader["CAPTION"].ToString() : "null"; op.AMOUNT = reader["AMOUNT"] != DBNull.Value ? double.Parse(reader["AMOUNT"].ToString()) : 0; oDetailList.Add(op); } con.Close(); //System.Web.HttpContext.Current.Session["opexstaffexpdiff"] = opexstaffexpdiffList; } return(oDetailList); } //========== end of the mtd
public String insertRegistrationDetails(String details) { String str = "xyz"; stringmessage message = new stringmessage(); String cs = System.Configuration.ConfigurationManager.ConnectionStrings["dbString"].ConnectionString; System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(cs); System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(); //SqlDataReader rdr = null; str = str + "1"; try { con.Open(); str = str + "2"; cmd.CommandType = System.Data.CommandType.Text; cmd.CommandText = "INSERT REGISTRATION (FIRSTNAME,LASTNAME,USERNAME,PASSWORD,EMAILADDRESS,PHONENUMBER,DATEOFBIRTH,ADDRESS,ZIPCODE) VALUES (" + details + ")"; //cmd.CommandText = "INSERT login (email,password) VALUES (" + userDetails + ")"; str = str + "3"; /*cmd.CommandText = "INSERT UserDetails (email,password,firstName,lastName,phno,address,city,state,zipCode) VALUES ('" + userDetails.getEmail() + "'," + * userDetails.getZipCode() + ")"; */ cmd.Connection = con; cmd.ExecuteNonQuery(); str = str + "4"; con.Close(); str = str + "Inserted data"; } catch (Exception e) { str = str + "Exception in insert" + e; } message.msg = str; return(str); }
private void button1_Click(object sender, EventArgs e) { System.Data.SqlClient.SqlConnection conn = konn.GetConn(); try { conn.Open(); SqlDataReader reader = null; string sql = "SELECT * FROM Customer WHERE Nomor_Telepon_Customer = '" + textBox1.Text + "'"; SqlCommand command = new SqlCommand(sql, conn); command.ExecuteNonQuery(); reader = command.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { telp = reader["Nomor_Telepon_Customer"].ToString(); } reader.Close(); } if (textBox1.Text.Equals(telp)) { idCus = textBox1.Text; list_Restoran a = new list_Restoran(); a.Show(); this.Hide(); } } catch (Exception ex) { MessageBox.Show("Customer Tidak Ada Pada Database"); } finally { conn.Close(); } }
public void verificarBloqueo() { DataTable dt = new DataTable(); DataSet ds = new DataSet(); ds.Tables.Add(dt); System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(); // TODO: Modify the connection string and include any // additional required properties for your database. conn.ConnectionString = "integrated security=SSPI;data source=DESKTOP-5NFCGTG\\RONELCRUZ;" + "persist security info=False;initial catalog=sisfax"; try { String Consulta = "select estado from usuarios where contraseña='" + label4.Text + "' and usuario ='" + label2.Text + "' and tipo = '" + label3.Text + "' "; conn.Open(); SqlCommand Comando = new SqlCommand(Consulta, conn); SqlDataReader LectorDatos; LectorDatos = Comando.ExecuteReader(); if (LectorDatos.Read()) { label7.Text = Convert.ToString(LectorDatos["estado"]); string cc = label6.Text; if (label7.Text == "bloqueada") { notificacionBloqueo(); } } conn.Close(); // MessageBox.Show("La lista de usuarios se ha actualizado "); } catch (Exception ex) { MessageBox.Show(ex.Message, "Error message", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
public IEnumerable <IncomeAccountsTreeMisCodesTEMP> IncomeAccountsTreeMisCodesTEMPusingparams(string status, string search) { List <IncomeAccountsTreeMisCodesTEMP> obuList = new List <IncomeAccountsTreeMisCodesTEMP>(); using (var con = new System.Data.SqlClient.SqlConnection(connectionString)) { var cmd = new System.Data.SqlClient.SqlCommand("", con); //cmd.CommandText = "select * from Names where Id=@Id"; //cmd.Parameters.AddWithValue("@Id", id); con.Open(); //cmd.CommandText = "select top 500 Id, accountnumber, mis, AccountOfficer_Code, ApprovalStatus from Income_accountMIS_Override_TEMP " + // "where ApprovalStatus=@STATUS and (accountnumber like @searchval OR mis like @searchval OR AccountOfficer_Code like @searchval)"; cmd.CommandText = "select top 1000 * from Income_AccountsTree_MISCodes_TEMP " + "where ApprovalStatus=@STATUS and (accountnumber like @searchval OR AccountOfficerName like @searchval OR AccountOfficer_Code like @searchval OR Team_Code like @searchval)"; cmd.Parameters.AddWithValue("@STATUS", status); cmd.Parameters.AddWithValue("@searchval", "%" + search + "%"); System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { var obu = new IncomeAccountsTreeMisCodesTEMP(); obu.ID = reader["Id"] != DBNull.Value ? int.Parse(reader["Id"].ToString()) : 0; obu.AccountNumber = reader["AccountNumber"] != DBNull.Value ? reader["AccountNumber"].ToString() : ""; obu.AccountOfficer_Code = reader["AccountOfficer_Code"] != DBNull.Value ? reader["AccountOfficer_Code"].ToString() : ""; obu.AccountOfficerName = reader["AccountOfficerName"] != DBNull.Value ? reader["AccountOfficerName"].ToString() : ""; obu.ShareRatio = reader["ShareRatio"] != DBNull.Value ? decimal.Parse(reader["ShareRatio"].ToString()) : 0; obu.Team_Code = reader["Team_Code"] != DBNull.Value ? reader["Team_Code"].ToString() : ""; obu.ApprovalStatus = reader["ApprovalStatus"] != DBNull.Value ? reader["ApprovalStatus"].ToString() : ""; obuList.Add(obu); } con.Close(); } return(obuList); }
protected void btnUpdate_Click(object sender, EventArgs e) { try { System.Data.SqlClient.SqlConnection sc = new System.Data.SqlClient.SqlConnection(); sc.ConnectionString = ConfigurationManager.ConnectionStrings["RoomMagnet"].ConnectionString; sc.Open(); SqlCommand update = new SqlCommand(); update.Connection = sc; update.CommandText = "update RMUser set FirstName = @FirstName, " + "LastName = @LastName, " + "Email = @Email, " + "PhoneNumber = @PhoneNumber," + "HouseNumber = @HouseNumber," + "Street = @Street," + "City = @City," + "State = @State," + "Zip = @Zip where UserID = " + Session["USERID"]; update.Parameters.Add(new SqlParameter("@FirstName", HttpUtility.HtmlEncode(tbName.Text))); update.Parameters.Add(new SqlParameter("@LastName", HttpUtility.HtmlEncode(tbName1.Text))); update.Parameters.Add(new SqlParameter("@PhoneNumber", HttpUtility.HtmlEncode(tbPhone.Text))); update.Parameters.Add(new SqlParameter("@Email", HttpUtility.HtmlEncode(tbUname.Text))); update.Parameters.Add(new SqlParameter("@HouseNumber", HttpUtility.HtmlEncode(tbHouseNumber.Text))); update.Parameters.Add(new SqlParameter("@Street", HttpUtility.HtmlEncode(tbAddress.Text))); update.Parameters.Add(new SqlParameter("@City", HttpUtility.HtmlEncode(tbCity.Text))); update.Parameters.Add(new SqlParameter("@Zip", HttpUtility.HtmlEncode(tbZip.Text))); update.Parameters.Add(new SqlParameter("@State", HttpUtility.HtmlEncode(ddlState.SelectedValue))); update.ExecuteNonQuery(); ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('Update Successful!');", true); } catch { ClientScript.RegisterStartupScript(this.GetType(), "myalert", "alert('Failed to update');", true); } }
public void updateRestoran() { String query = "select * from Restoran"; SqlDataReader reader = null; System.Data.SqlClient.SqlConnection conn = konn.GetConn(); listView1.Items.Clear(); try { conn.Open(); SqlCommand command = new SqlCommand(query, conn); command.ExecuteNonQuery(); reader = command.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { if (!reader["ID_Restoran"].ToString().Equals("0")) { ListViewItem listViewItem = new ListViewItem(reader["ID_Restoran"].ToString()); listViewItem.SubItems.Add(reader["Nama_Restoran"].ToString()); listViewItem.SubItems.Add(reader["Alamat_Resto"].ToString()); listViewItem.SubItems.Add(reader["Jadwal_Restoran"].ToString()); listView1.Items.Add(listViewItem); } } reader.Close(); } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } finally { conn.Close(); } }
public void keranjang() { String query = "select * from Mengambil_Data m join Menu_Makanan e on e.ID_Makanan = m.ID_Makanan where ID_Transaksi = '" + list_Restoran.id + "'"; SqlDataReader reader = null; System.Data.SqlClient.SqlConnection conn = konn.GetConn(); try { conn.Open(); SqlCommand command = new SqlCommand(query, conn); command.ExecuteNonQuery(); reader = command.ExecuteReader(); listView9.Items.Clear(); if (reader.HasRows) { while (reader.Read()) { ListViewItem listViewItem = new ListViewItem(reader["ID_Makanan"].ToString()); listViewItem.SubItems.Add(reader["Nama_Makanan"].ToString()); listViewItem.SubItems.Add(reader["Harga"].ToString()); listViewItem.SubItems.Add(reader["Jumlah_Makanan"].ToString()); listView9.Items.Add(listViewItem); jum += int.Parse(reader["Harga"].ToString()) * int.Parse(reader["Jumlah_Makanan"].ToString()); } reader.Close(); label2.Text = jum.ToString(); } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } finally { conn.Close(); } }
public static void UpdateProduct(string theID, string newName, double newPrice, string newDesc) { prodCmd.CommandText = "Update Product " + "Set ProductName = '" + newName + "', " + "UnitPrice = " + newPrice + ", " + "Description = '" + newDesc + "' " + "Where ProductID = '" + theID + "'"; try { con.Open(); prodCmd.ExecuteNonQuery(); } catch (Exception ex) { throw new Exception(ex.Message); } finally { con.Close(); } }
public IEnumerable <IncomeRetailProductOverrideTEMP> IncomeRetailProductOverrideTEMPusingparams(string status, string search) { List <IncomeRetailProductOverrideTEMP> obuList = new List <IncomeRetailProductOverrideTEMP>(); using (var con = new System.Data.SqlClient.SqlConnection(connectionString)) { var cmd = new System.Data.SqlClient.SqlCommand("", con); //cmd.CommandText = "select * from Names where Id=@Id"; //cmd.Parameters.AddWithValue("@Id", id); con.Open(); cmd.CommandText = "select * from Income_RetailProduct_Override_TEMP " + "where ApprovalStatus=@STATUS and (Customerid like @searchval OR Mis_code like @searchval OR AccountOfficer_Code like @searchval)"; //cmd.CommandText = "select Id, Customerid, Bank, Mis_code, AccountOfficer_Code, ApprovalStatus from Income_RetailProduct_Override_TEMP " + // "where ApprovalStatus=@STATUS and (Customerid like @searchval OR Mis_code like @searchval OR AccountOfficer_Code like @searchval)"; cmd.Parameters.AddWithValue("@STATUS", status); cmd.Parameters.AddWithValue("@searchval", "%" + search + "%"); System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { var obu = new IncomeRetailProductOverrideTEMP(); obu.Id = reader["Id"] != DBNull.Value ? int.Parse(reader["Id"].ToString()) : 0; obu.Customerid = reader["Customerid"] != DBNull.Value ? int.Parse(reader["Customerid"].ToString()) : 0; obu.Bank = reader["Bank"] != DBNull.Value ? reader["Bank"].ToString() : ""; obu.Mis_code = reader["Mis_code"] != DBNull.Value ? reader["Mis_code"].ToString() : ""; obu.AccountOfficer_Code = reader["AccountOfficer_Code"] != DBNull.Value ? reader["AccountOfficer_Code"].ToString() : ""; obu.ApprovalStatus = reader["ApprovalStatus"] != DBNull.Value ? reader["ApprovalStatus"].ToString() : ""; obuList.Add(obu); } con.Close(); } return(obuList); }
public Manager_client() { InitializeComponent(); FileInfo fi1 = new FileInfo("bank.txt"); using (StreamReader sr = fi1.OpenText()) { string s = ""; s = sr.ReadLine(); sr.Close(); Id_bank = Convert.ToInt32(s); } using (SqlConnection cn = new System.Data.SqlClient.SqlConnection()) { cn.ConnectionString = address; try { cn.Open(); } catch { MessageBox.Show(@"Нет соединения с базой данных. Повторите запрос позднее!", @"Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } //string strSql = "SELECT Surname, Name, Patronymic, Age, Id_bank, Id_manager FROM Manager" string strSql = String.Format(@"SELECT Surname, Name, Patronymic, Age, Id_manager FROM Manager WHERE Id_bank = '{0}'", Id_bank); SqlCommand cmd = new SqlCommand(strSql, cn); SqlDataAdapter adapter = new SqlDataAdapter(cmd); adapter.Fill(dataset); dataGridView1.AutoGenerateColumns = true; bind.DataSource = dataset.Tables[0]; dataGridView1.DataSource = bind; cn.Close(); dataGridView1.Columns[4].Visible = false; } }
// string linksev = Convert.ToString(System.Configuration.ConfigurationManager.AppSettings["linkserver"].Trim()); //string linksev = @"[127.0.0.1\dell].fintrak.dbo.volume_analysis_rundates"; public List <VolumeAnalysisRundatesModel> GetAllVolumeAnalysisRundates() { List <VolumeAnalysisRundatesModel> obuList = new List <VolumeAnalysisRundatesModel>(); using (var con = new System.Data.SqlClient.SqlConnection(connectionString)) { var cmd = new System.Data.SqlClient.SqlCommand("", con); //string ls = linksev + ".volume_analysis_rundates"; con.Open(); //cmd.CommandText = "SELECT * from [10.1.14.49\fintrack].fintrakreport.dbo.volume_analysis_rundates"; //cmd.CommandText = " SELECT * from @LINKSERVER "; //var sqlString = string.Format("insert into {0} (time, date, pin) values (@time, @date, @pin)", linksev); //var sqlString = string.Format("SELECT * from {0} ", linksev); //cmd.CommandText = sqlString; // cmd.Parameters.AddWithValue("@LINKSERVER", linksev + ".dbo.volume_analysis_rundates"); cmd.CommandText = "SELECT * from volume_analysis_rundates"; System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { var ob = new VolumeAnalysisRundatesModel(); ob.Id = reader["Id"] != DBNull.Value ? Int32.Parse(reader["Id"].ToString()) : 0; ob.rundate = reader["rundate"] != DBNull.Value ? Convert.ToDateTime(reader["rundate"].ToString()).Date : Convert.ToDateTime("2001-01-01").Date; ob.visible = reader["visible"] != DBNull.Value ? Convert.ToString(reader["visible"]) : ""; obuList.Add(ob); } con.Close(); } return(obuList); }
public void loadmenu() { String query = "select * from Menu_Makanan s join Kategori_Makanan k on s.Kategori= k.Kategori where ID_Restoran = (select ID_Restoran from Restoran where ID_Restoran = '" + list_Restoran.idres + "') "; SqlDataReader reader = null; System.Data.SqlClient.SqlConnection conn = konn.GetConn(); try { conn.Open(); SqlCommand command = new SqlCommand(query, conn); command.ExecuteNonQuery(); reader = command.ExecuteReader(); listView8.Items.Clear(); if (reader.HasRows) { while (reader.Read()) { if (!reader["ID_Makanan"].ToString().Equals("0")) { ListViewItem listViewItem = new ListViewItem(reader["ID_Makanan"].ToString()); listViewItem.SubItems.Add(reader["Nama_Makanan"].ToString()); listViewItem.SubItems.Add(reader["Harga"].ToString()); listViewItem.SubItems.Add(reader["Nama_Kategori"].ToString()); listView8.Items.Add(listViewItem); } } reader.Close(); } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } finally { conn.Close(); } }
public BO.AssetInventoryTracking.workorder GetByIDworkorder(int workorderID) { BO.AssetInventoryTracking.workorder xworkorder = new BO.AssetInventoryTracking.workorder(); string query = "SELECT [workorderID],[date_created],[date_completed],[status],[inventoryID] FROM dbo.[workorder] WHERE workorderID=@workorderID"; using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["db_AssetInventoryTracking"].ConnectionString)) { using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(query, conn)) { cmd.Parameters.AddWithValue("@workorderID", workorderID); conn.Open(); using (System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { if (reader.Read()) { if (!object.ReferenceEquals(reader["workorderID"], DBNull.Value)) { xworkorder.workorderID = int.Parse(reader["workorderID"].ToString()); } if (!object.ReferenceEquals(reader["date_created"], DBNull.Value)) { xworkorder.date_created = DateTime.Parse(reader["date_created"].ToString()); } if (!object.ReferenceEquals(reader["date_completed"], DBNull.Value)) { xworkorder.date_completed = DateTime.Parse(reader["date_completed"].ToString()); } if (!object.ReferenceEquals(reader["status"], DBNull.Value)) { xworkorder.status = reader["status"].ToString(); } if (!object.ReferenceEquals(reader["inventoryID"], DBNull.Value)) { xworkorder.inventoryID = int.Parse(reader["inventoryID"].ToString()); } } } } } return(xworkorder); }
public List <Podcast> getAllPodcasts() { System.Data.SqlClient.SqlConnection conn = getConnectionData(); List <Podcast> aPodcastsList = new List <Podcast>(); String requete = ""; try { conn.Open(); requete = "SELECT PodcastId " + "FROM Podcasts"; SqlCommand command = new SqlCommand(requete, conn); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { Podcast oCurrentPodcast = new Podcast(); oCurrentPodcast.initWithId(Convert.ToInt32(reader["PodcastId"])); aPodcastsList.Add(oCurrentPodcast); } reader.Close(); } catch (Exception ex) { MessageBox.Show("Erreur de connexion à la base de donnée!"); log.Error("--------------------\n" + "Requete: " + requete + "\n" + ex.ToString()); } finally { conn.Close(); } return(aPodcastsList); }
protected void OnRowUpdating(object sender, GridViewUpdateEventArgs e) { GridViewRow row = GridView1.Rows[e.RowIndex]; int ID = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Values[0]); string firstName = (row.FindControl("txtFirstName") as TextBox).Text; string lastname = (row.FindControl("txtLastName") as TextBox).Text; DateTime InfractionDate = Convert.ToDateTime((row.FindControl("txtInfractionDate") as TextBox).Text); string Description = (row.FindControl("txtDescription") as TextBox).Text; string county = (row.FindControl("txtCounty") as TextBox).Text; string court = (row.FindControl("txtCourt") as TextBox).Text; DateTime HearingDate = Convert.ToDateTime((row.FindControl("txtHearingDate") as TextBox).Text); System.Data.SqlClient.SqlConnection sc = new System.Data.SqlClient.SqlConnection(); sc.ConnectionString = @"Server =Localhost;Database=TeamProject;Trusted_Connection=Yes;"; using (SqlCommand cmd = new SqlCommand("InfractionGroup6_CRUD")) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@Action", "UPDATE"); cmd.Parameters.AddWithValue("@ID", ID); cmd.Parameters.AddWithValue("@FirstName", firstName); cmd.Parameters.AddWithValue("@LastName", lastname); cmd.Parameters.AddWithValue("@InfractionDate", InfractionDate); cmd.Parameters.AddWithValue("@Description", Description); cmd.Parameters.AddWithValue("@County", county); cmd.Parameters.AddWithValue("@Court", court); cmd.Parameters.AddWithValue("@HearingDate", HearingDate); cmd.Connection = sc; sc.Open(); cmd.ExecuteNonQuery(); sc.Close(); } GridView1.EditIndex = -1; this.BindGrid(); }
public bool LogIn(string username, string password) { bool result = false; DataSet ds = new DataSet(); try { System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(); connection.ConnectionString = ConfigurationManager.ConnectionStrings["DadabaseNameConnectionString"].ConnectionString; connection.Open(); string strSQL = "SELECT * FROM (tabel Name) WHERE UserName = '******' AND Password = '******'"; System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand(); command.CommandText = strSQL; command.Connection = connection; SqlDataAdapter adapter = new SqlDataAdapter(command); adapter.Fill(ds, "userExist"); if (ds.Tables[0].Rows.Count > 0) { result = true; } else { result = false; } connection.Close(); return(result); } catch (System.Data.SqlClient.SqlException oleExp) { return(false); } }
private void LoadTableNames(string connection, ComboBox comboBox) { using (SqlConnection c = new System.Data.SqlClient.SqlConnection(connection)) { SqlDataAdapter da = new SqlDataAdapter("sp_databases", c); DataTable t = new DataTable(); Exception err = null; try { if (c.State != ConnectionState.Open) { c.Open(); } Version sqlServerVersion = new Version(c.ServerVersion); if (sqlServerVersion.Major > 8) { da.SelectCommand.CommandText = "SELECT * FROM information_schema.tables"; } da.Fill(t); } catch (Exception e) { err = e; } List <string> names = new List <string>(); foreach (DataRow r in t.Rows) { names.Add(r["TABLE_NAME"] as string); } RefreshItems(comboBox, names.OrderBy(p => p).ToList()); } }
private void Buy(object sender, EventArgs e) { con = new System.Data.SqlClient.SqlConnection(); con.ConnectionString = "Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\\properties.mdf;Integrated Security=True;Connect Timeout=30"; con.Open(); if (garage.Text == "Yes") { garage.Text = "1"; } else if (garage.Text == "No") { garage.Text = "0"; } SqlDataAdapter sda = new SqlDataAdapter("SELECT Id,Price FROM [Table01] where 1=1" + "And (Price >= '" + Minprice.Text + "' OR '" + Minprice.Text + "' =' ')" + "And (Price <= '" + Maxprice.Text + "'OR'" + Maxprice.Text + "'=' ')" + "And (Bedrooms >='" + Minbed.Text + "'OR'" + Minbed.Text + "' =' ')" + "And (Bedrooms <='" + Maxbed.Text + "'OR'" + Maxbed.Text + "' =' ')" + "And (Isparking ='" + garage.Text + "'OR'" + garage.Text + "' =' ')" + "And (Bathrooms >= '" + bathroom.Text + "'OR'" + bathroom.Text + "'=' ')" + "And (LotSize >= '" + Minlot.Text + "'OR '" + Minlot.Text + "' =' ')" + "And (Zipcode = '" + zipcode.Text + "'OR'" + zipcode.Text + "' =' ')" + "And (HouseType ='" + type.Text + "'OR'" + type.Text + "' =' ')" + "And (Action ='Buy')", con); //Search properties base on value user enter. DataTable dt = new DataTable(); sda.Fill(dt); dt.DefaultView.Sort = "Price"; dt = dt.DefaultView.ToTable(); dataGridView1.DataSource = dt; con.Close(); }
private void btnInfoCnn_Click(object sender, EventArgs e) { var cryp = new CCryptorEngine(); var cnn_str = cryp.Desencriptar(Properties.Settings.Default[Config.Key_Cnn_DB_Molinetes].ToString()); var array_items = cnn_str.Split(";".ToCharArray()); for (int i = 0; i < array_items.Length; i++) { if (array_items[i].ToLower().Contains("password")) { array_items[i] = "Password=*****"; } } if (MessageBox.Show("La cadena de conexión es:\n\n" + string.Join(";", array_items) + "\n\n¿Desea modificarla?", "Pregunta:", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.Yes) { var result = Microsoft.VisualBasic.Interaction.InputBox("Ingrese la nueva cadena de conexión:", ""); var cnn = new System.Data.SqlClient.SqlConnection(); try { cnn.ConnectionString = result; cnn.Open(); if (cnn.State != ConnectionState.Open) { throw new Exception("No fue posible abrir la conexión de la base de datos."); } } catch (Exception ex) { MessageBox.Show("Se produjo un error en la configuración:\n" + ex.Message + "\nVuelva a intentar.", "Atención:", MessageBoxButtons.OK, MessageBoxIcon.Warning); return; } Properties.Settings.Default[Config.Key_Cnn_DB_Molinetes] = cryp.Encriptar(result); Properties.Settings.Default.Save(); MessageBox.Show("La conexión con la base de datos fue modificada.", "Atención:", MessageBoxButtons.OK, MessageBoxIcon.Information); cnn.Dispose(); cnn = null; } }
public void DeliveriesForm_Load(object sender, EventArgs e) { btnCancelAdd.Visible = false; //On form load loads the connection to the database and gets all the data from the Clients Tbl in order to display the records newCon = new System.Data.SqlClient.SqlConnection(); newCon.ConnectionString = "Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=C:\\Mitko Bozhilov\\Work Year 6\\Software Eng\\Ass\\MyDataBase\\MyDataBase\\BookingSystemDataBase.mdf;Integrated Security=True; Connect Timeout=30"; dsCustomer = new DataSet(); //a private string to pass the data from the database to. String sqlGetWhat; //in order to read the dataabse sql code is used as shown below sqlGetWhat = "SELECT * From DeliveriesTbl "; try { //open the database connection newCon.Open(); //setting the data adapter and filling it with the information daCustomer = new System.Data.SqlClient.SqlDataAdapter(sqlGetWhat, newCon); daCustomer.Fill(dsCustomer, "Deliveries"); //calling move records method created above to display the data inot the text boxes MoveRecords(); countrec = dsCustomer.Tables["Deliveries"].Rows.Count; } catch { //message if there is no connection with the database MessageBox.Show("no connection "); } newCon.Close(); }
public void addPart(Part part) { //Need to get correct inHouse or Outsourced information Console.WriteLine(inHouseID); Console.WriteLine(outSourcedID); Console.WriteLine(machineID); Console.WriteLine(companyName); SqlConnection con = new System.Data.SqlClient.SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB; AttachDbFilename=" + Application.StartupPath + "\\DB.mdf; Integrated Security=True"); SqlCommand command = con.CreateCommand(); con.Open(); SqlTransaction transaction; // Start a local transaction. transaction = con.BeginTransaction(); // Must assign both transaction object and connection // to Command object for a pending local transaction command.Connection = con; command.Transaction = transaction; command.CommandText = "INSERT INTO [dbo].[partTable] ([partID], [name], [price], [inStock], [min], [max], [inHouse], [outsourced] ,[companyName],[machineID]) VALUES (@partID, @name, @price, @inStock, @min, @max, @inHouseID,@outSourcedID,@companyName,@machineID); SELECT partID, name, price, inStock, min, max, inHouse, outsourced ,companyName,machineID FROM partTable WHERE(partID = @partID)"; command.Parameters.AddWithValue("@partID", part.getParttID()); command.Parameters.AddWithValue("@name", part.getName()); command.Parameters.AddWithValue("@price", part.getPrice()); command.Parameters.AddWithValue("@inStock", part.getInStock()); command.Parameters.AddWithValue("@min", part.getMin()); command.Parameters.AddWithValue("@max", part.getMax()); command.Parameters.AddWithValue("@inHouseID", inHouseID); command.Parameters.AddWithValue("@outSourcedID", outSourcedID); command.Parameters.AddWithValue("@companyName", companyName); command.Parameters.AddWithValue("@machineID", machineID); command.ExecuteNonQuery(); transaction.Commit(); con.Close(); }
public IEnumerable GetList(DateTime Fromdate, DateTime Todate, decimal?WarehouseId) { // var lst = _context.Database.SqlQuery<ProductInfoViewModel>( //"usp_BaoCaoXuatNhapTon").ToList(); var List = new List <ProductInfoViewModel>(); try { using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString)) { using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand()) { cmd.CommandText = "usp_BaoCaoXuatNhapTonDay"; cmd.Parameters.AddWithValue("@FromDate", Fromdate); cmd.Parameters.AddWithValue("@ToDate", Todate); cmd.Parameters.AddWithValue("@WarehouseId", WarehouseId); cmd.Connection = conn; cmd.CommandType = CommandType.StoredProcedure; conn.Open(); // do using (var dr = cmd.ExecuteReader()) { while (dr.Read()) { var v = ListViewDay(dr); List.Add(v); } } conn.Close(); } } return(List); } catch { return(List); } }
bool Completed; //did the translation complete? public DataTranslator(DataSet ds, ImportTemplateSettings ImportSettings, List <String> tl, string gb) { Data2Translate = ds; TemplateSettings = ImportSettings; List2Translate = tl; GrowerBlock = gb; Completed = false; //Did tranlation complete? Not yet. try { //get Commodity data from database string connString = Properties.Settings.Default.ConnectionString; string query = "select [Data_Column_Name],[Description],[Value], [Custom_Value] FROM Translation_Validation_Table"; // WHERE Famous_Validate = 1"; System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connString); System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(query, conn); conn.Open(); DataSet commodityDataSet = new DataSet(); // create data adapter System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd); // this will query the database and return the result to your datatable da.Fill(commodityDataSet); conn.Close(); da.Dispose(); CommodityTable = commodityDataSet.Tables[0]; //set the translation table commodityDataSet.Dispose(); } catch (Exception e) { MessageBox.Show("There was an error while trying to load the Commodity data for translation. \n" + " Note what happened and contact administrator for help or see error log. \n"); Error_Logging el = new Error_Logging("There was an error while trying to load theCommodity data for translation. \n" + e); ds.Dispose(); return; } }
private void button1_Click(object sender, EventArgs e) { name = textBox1.Text; if (name == "") { MessageBox.Show(@"Не заполнено поле 'Название банка'!", @"Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error); label3.Visible = false; dataGridView1.Visible = false; return; } label3.Visible = true; dataGridView1.Visible = true; using (SqlConnection cn = new System.Data.SqlClient.SqlConnection()) { cn.ConnectionString = address; try { cn.Open(); } catch { MessageBox.Show(@"Нет соединения с базой данных. Повторите запрос позднее!", @"Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } string strSql = String.Format(@"SELECT t1.Name as 'NameService', t1.MinRequirement as 'MinRequirement', t1.Description as 'Description', t2.Name as 'NameBank' FROM Bank t2 INNER JOIN Service t1 ON t1.Id_bank=t2.Id_bank WHERE t2.Name = '{0}'", name); SqlCommand cmd = new SqlCommand(strSql, cn); SqlDataAdapter adapter = new SqlDataAdapter(cmd); dataset.Reset(); adapter.Fill(dataset); cn.Close(); dataGridView1.AutoGenerateColumns = true; bind.DataSource = dataset.Tables[0]; dataGridView1.DataSource = bind; } }
private static void LogToDatabase(string message, Enums.LogTypes logTypeId) { System.Data.SqlClient.SqlConnection cnn = null; try { cnn = new System.Data.SqlClient.SqlConnection(Configurations.Database.ConnectionString); var cmd = new System.Data.SqlClient.SqlCommand { CommandText = "SP_ErroLogs_Insert", CommandType = System.Data.CommandType.StoredProcedure, Connection = cnn }; cmd.Parameters.AddWithValue("@ApplicationName", ApplicationName); cmd.Parameters.AddWithValue("@LogTypeId", logTypeId); cmd.Parameters.AddWithValue("@ErrorDescription", message); cnn.Open(); cmd.ExecuteScalar(); } catch (Exception) { // ignored } finally { try { if (cnn != null) { cnn.Close(); } } catch (Exception) { // ignored } } }
protected void TextBox4_TextChanged(object sender, EventArgs e) { try { { System.Data.DataTable lsp = new System.Data.DataTable(); { string sql = "select id_loai_san_pham, ten_loai_san_pham from loai_san_pham where cap_do_loai_san_pham = " + (int.Parse(TextBox4.Text) - 1); WebApplication2.YNNSHOP56131778.CONGFIG.connect cnt = new WebApplication2.YNNSHOP56131778.CONGFIG.connect(); System.Data.SqlClient.SqlConnection ketnoi = new System.Data.SqlClient.SqlConnection(connect.getconnect()); System.Data.SqlClient.SqlCommand lenh = new System.Data.SqlClient.SqlCommand(sql, ketnoi); ketnoi.Open(); System.Data.SqlClient.SqlDataAdapter data = new System.Data.SqlClient.SqlDataAdapter(lenh); data.Fill(lsp); ketnoi.Close(); for (int i = 0; i < lsp.Rows.Count; i++) { lsp.Rows[i][1] = mH.Base64Decode(lsp.Rows[i][1].ToString()); } if ((int.Parse(TextBox4.Text)) == 0) { System.Data.DataRow row; row = lsp.NewRow(); row[0] = 0; row[1] = "cao nhất"; lsp.Rows.Add(row); } DropDownList1.DataSource = lsp; DropDownList1.DataTextField = "ten_loai_san_pham"; DropDownList1.DataValueField = "id_loai_san_pham"; DropDownList1.DataBind(); } } } catch (Exception x) { } }
public ActionResult Cancel(int id) { OrderReturnModel model = _context.OrderReturnModel .Where(p => p.OrderReturnMasterId == id) .FirstOrDefault(); var Resuilt = ""; if (model == null) { Resuilt = "Không tìm thấy đơn hàng yêu cầu !"; } else { using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString)) { using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand()) { cmd.CommandText = "usp_SellReturnCanceled"; cmd.Parameters.AddWithValue("@OrderReturnMasterId", model.OrderReturnMasterId); cmd.Parameters.AddWithValue("@DeletedDate", DateTime.Now); AccountModel Account = _context.AccountModel.Where(p => p.UserName == model.CreatedAccount).FirstOrDefault(); model.DeletedEmployeeId = Account.EmployeeId; cmd.Parameters.AddWithValue("@DeletedAccount", currentAccount.UserName); cmd.Parameters.AddWithValue("@DeletedEmployeeId", model.DeletedEmployeeId); cmd.Connection = conn; cmd.CommandType = CommandType.StoredProcedure; conn.Open(); cmd.ExecuteNonQuery(); conn.Close(); } } //_context.Entry(model).State = System.Data.Entity.EntityState.Modified; //_context.SaveChanges(); Resuilt = "success"; } return(Json(Resuilt, JsonRequestBehavior.AllowGet)); }