public List<string> consultarMarca(string id) { List<string> marcas = new List<string>(); SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename='|DataDirectory|DBTag.mdf';Integrated Security=True;User Instance=True"); // Abre a conexão conn.Open(); try { SqlCommand select = new SqlCommand("SELECT * from TbMarca where id=@id", conn); SqlParameter pID = new SqlParameter("id", id); select.Parameters.Add(pID); // Lê, linha a linha a tabela SqlDataReader dr = select.ExecuteReader(); while (dr.Read()) { marcas.Add(dr["id"].ToString()); marcas.Add(dr["nome_marca"].ToString()); } } catch (Exception ex) { throw new Exception(ex.ToString(), ex); } return marcas; }
protected string generate_forum_name(string group_id) { string conn = System.Configuration.ConfigurationManager.ConnectionStrings["connstring"].ConnectionString; SqlConnection connection = new SqlConnection(conn); string sqlqry = "SELECT group_name FROM forum_group WHERE group_id=@p1"; SqlCommand command = new SqlCommand(sqlqry, connection); SqlParameter param1 = new SqlParameter(); param1.SqlDbType = System.Data.SqlDbType.Int; param1.ParameterName = "@p1"; param1.Value = group_id; command.Parameters.Add(param1); SqlDataReader Reader = null; command.Connection.Open(); Reader = command.ExecuteReader(); Reader.Read(); string name = Reader[0].ToString(); command.Connection.Close(); return name; }
//method to open or close the database connection public SqlConnection ManageDatabaseConnection(string actionToPerform) { string connectionString = "Data Source=ph0ibk90ya.database.windows.net;Initial Catalog=ParkingLot;Integrated Security=False;User ID=samara;Password=s4m4r4DEV;Connect Timeout=100;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"; SqlConnection sqlConnection = new SqlConnection(connectionString); try { //desicion to whether open or close the database connection if (actionToPerform.Equals("Open")) { sqlConnection.Open(); } else { sqlConnection.Close(); } } catch (SqlException sqlException) { //throw the exception to upper layers throw sqlException; } return sqlConnection; }
protected int getTotalCount() { SqlConnection connection = new SqlConnection(GetConnectionString()); DataTable dt = new DataTable(); try { connection.Open(); string sqlStatement = "SELECT * FROM tblContact"; SqlCommand sqlCmd = new SqlCommand(sqlStatement, connection); SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd); sqlDa.Fill(dt); } catch (System.Data.SqlClient.SqlException ex) { string msg = "Fetch Error:"; msg += ex.Message; throw new Exception(msg); } finally { connection.Close(); } return dt.Rows.Count; }
protected void Submit_Click1(object sender, EventArgs e) { try { //generate a new GUID ID Guid newGUID = Guid.NewGuid(); SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["RegistrationConnectionString"].ConnectionString); conn.Open(); string insertQuery = "insert into UserData (ID,UserName,Email,Password,Country) values (@ID ,@Uname ,@email ,@password, @country)"; SqlCommand com = new SqlCommand(insertQuery, conn); com.Parameters.AddWithValue("@ID", newGUID.ToString()); com.Parameters.AddWithValue("@Uname", IDSUaserName.Text); com.Parameters.AddWithValue("@email", IDSEmail.Text); com.Parameters.AddWithValue("@password", IDSPassword.Text); com.Parameters.AddWithValue("@country", IDSCountry.SelectedItem.ToString()); com.ExecuteNonQuery(); Response.Redirect("Manager.aspx"); Response.Write("Registration is sucessful"); conn.Close(); } catch(Exception ex) { Response.Write("Error:"+ex.ToString()); } }
public static DataTable GetAllCidades(int estado_id) { DataTable retorno = new DataTable(); StringBuilder SQL = new StringBuilder(); SQL.Append(@"SELECT CidadeId, Nome FROM Cidade WHERE EstadoId = @ESTADO_ID"); try { using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Mendes_varejo"].ConnectionString)) { connection.Open(); SqlCommand command = new SqlCommand(SQL.ToString(), connection); command.Parameters.AddWithValue("@ESTADO_ID", estado_id); command.ExecuteNonQuery(); SqlDataAdapter adapter = new SqlDataAdapter(command); adapter.Fill(retorno); } } catch (Exception erro) { throw erro; } return retorno; }
static void Main() { SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(); builder["Data Source"] = "aad-managed-demo.database.windows.net"; // replace with your server name builder["Initial Catalog"] = "demo"; // replace with your database name builder["Authentication"] = SqlAuthenticationMethod.ActiveDirectoryPassword; builder["Connect Timeout"] = 30; string username = "******"; // replace with your username using (SqlConnection connection = new SqlConnection(builder.ConnectionString)) { try { connection.Credential = CreateCredential(username); connection.Open(); using (SqlCommand cmd = new SqlCommand(@"SELECT SUSER_SNAME()", connection)) { Console.WriteLine("You have successfully logged on as: " + (string)cmd.ExecuteScalar()); } } catch (Exception ex) { Console.WriteLine(ex.Message); } } Console.WriteLine("Please press any key to stop"); Console.ReadKey(); }
protected void btnOK_Click(object sender, EventArgs e) { string ID; SqlConnection mycon = new SqlConnection(ConfigurationManager.AppSettings["conStr"]); mycon.Open(); DataSet mydataset = new DataSet(); SqlDataAdapter mydataadapter = new SqlDataAdapter("select * from tb_Blog where UserName='******'", mycon); mydataadapter.Fill(mydataset, "tb_Blog"); DataRowView rowview = mydataset.Tables["tb_Blog"].DefaultView[0]; ID = rowview["BlogID"].ToString(); string P_str_Com = "Insert into tb_Message(FriendName,Sex,HomePhone,MobilePhone,QQ,ICQ,Address,Birthday,Email,PostCode,BlogID,IP)" +" values ('"+this.txtName.Text+"','"+this.DropDownList1.SelectedValue+"','"+this.txtHphone.Text+"'" +",'"+this.txtMphone.Text+"','"+this.txtQQ.Text+"','"+this.txtICQ.Text+"','"+this.txtAddress.Text+"'" +",'"+this.txtBirthday.Text+"','"+this.txtEmail.Text+"','"+this.txtPostCode.Text+"','"+ID+"','"+Request.UserHostAddress+"')"; SqlData da = new SqlData(); if (!ValidateDate1(txtBirthday.Text) && !ValidateDate2(txtBirthday.Text) && !ValidateDate3(txtBirthday.Text)) { Response.Write("<script language=javascript>alert('输入的日期格式有误!');location='javascript:history.go(-1)'</script>"); } else { bool add = da.ExceSQL(P_str_Com); if (add == true) { Response.Write("<script language=javascript>alert('添加成功!');location='AddLinkMan.aspx'</script>"); } else { Response.Write("<script language=javascript>alert('添加失败!');location='javascript:history.go(-1)'</script>"); } } }
public SqlConnection GetConn() { string constr = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["sqlconnstr"].ConnectionString; SqlConnection conn = new SqlConnection(constr); conn.Open(); return conn; }
protected void LinkButton1_Click(object sender, EventArgs e) { SqlConnection con = new SqlConnection("Data Source=DIP\\SQLEXPRESS;Initial Catalog=UVPCE_DB;Integrated Security=True"); SqlCommand cmd = new SqlCommand("insert into [user](username,subject,comment,posteddate) values(@username,@subject,@comment,@postedate)", con); cmd.Parameters.AddWithValue("@username", Textname.Text); cmd.Parameters.AddWithValue("@subject", txtSubject.Text); cmd.Parameters.AddWithValue("@comment", txtComment.Text); cmd.Parameters.AddWithValue("@postedate", DateTime.Now); con.Open(); //SqlCommand cmd = new SqlCommand("insert into (username,subject,comment,posteddate) values('"+Textname.Text+"','"+txtSubject.Text+"','"+txtComment+"')",con); cmd.ExecuteNonQuery(); // con.Close(); SqlCommand cmd1 = new SqlCommand("select no from [user] where username='******' and subject='" + txtSubject.Text + "' and comment='" + txtSubject.Text + "'", con); SqlDataReader dr = cmd1.ExecuteReader(); while (dr.Read()) { Label1.Text = dr["no"].ToString(); } Textname.Text = string.Empty; txtSubject.Text = string.Empty; txtComment.Text = string.Empty; BindRepeaterData(); }
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(); }
public static List<string> SearchEmployees(string prefixText, int count) { using (SqlConnection conn = new SqlConnection()) { conn.ConnectionString = ConfigurationManager.ConnectionStrings["rateMyMPConnectionString"].ConnectionString; using (SqlCommand cmd = new SqlCommand()) { cmd.CommandText = "SELECT dbo.mpDetails.constituencyId,dbo.userMaster.profilePic, dbo.constituency.constituency, dbo.state.state, dbo.userMaster.firstName, dbo.userMaster.middleName, dbo.userMaster.lastName,dbo.userMaster.profilePic FROM dbo.constituency INNER JOIN dbo.state ON dbo.constituency.stateId = dbo.state.stateId INNER JOIN dbo.mpDetails ON dbo.state.stateId = dbo.mpDetails.permanentStateId AND dbo.constituency.constituencyId = dbo.mpDetails.constituencyId INNER JOIN dbo.userMaster ON dbo.mpDetails.guid = dbo.userMaster.guid where dbo.userMaster.firstName like '%' + @search+'%' or dbo.userMaster.middleName like '%'+ @search+'%' or dbo.userMaster.lastName like '%' + @search+'%' or dbo.state.state like '%'+ @search+'%' or dbo.constituency.constituency like '%'+ @search+'%'"; cmd.Parameters.AddWithValue("@search", prefixText); cmd.Connection = conn; conn.Open(); List<string> employees = new List<string>(); using (SqlDataReader sdr = cmd.ExecuteReader()) { while (sdr.Read()) { employees.Add(AjaxControlToolkit.AutoCompleteExtender .CreateAutoCompleteItem(string.Format("{0}{1}{2}{3}{4}{5} ", sdr["firstName"] + " ", sdr["middleName"] + " ", sdr["lastName"] + " ,", sdr["constituency"] + " ,", sdr["state"] + " ,", sdr["profilePic"].ToString()), sdr["constituencyId"].ToString())); } } conn.Close(); return employees; } } }
public building(int id) { try{ SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["mainconn"].ConnectionString); conn.Open(); SqlCommand cmd = new SqlCommand("select * FROM building WHERE id = @id", conn); cmd.Parameters.AddWithValue("id", id); SqlDataReader dr = cmd.ExecuteReader(); dr.Read(); ID = new dataObject(id); Name = new dataObject(dr["Name"].ToString()); Address1 = new dataObject(dr["Address1"].ToString()); Address2 = new dataObject(dr["Address2"].ToString()); Address3 = new dataObject(dr["Address3"].ToString()); City = new dataObject(dr["City"].ToString()); State = new dataObject(dr["State"].ToString()); ZipCode = new dataObject(dr["ZipCode"].ToString()); PhoneNumber = new dataObject(dr["PhoneNumber"].ToString()); PhoneNumberExtension = new dataObject(dr["PhoneNumberExtension"].ToString()); FaxNumber = new dataObject(dr["FaxNumber"].ToString()); MobilePhoneNumber = new dataObject(dr["MobilePhoneNumber"].ToString()); EmailAddress = new dataObject(dr["EmailAddress"].ToString()); Contact = new dataObject(dr["Contact"].ToString()); Active = new dataObject(Convert.ToBoolean(dr["Active"])); Comment = new dataObject(dr["Comment"].ToString()); InputDate = new dataObject(Convert.ToDateTime(dr["InputDate"])); InputEmploee = new dataObject(new employee(Convert.ToInt32(dr["InputEmployeeID"]))); }catch(Exception e){ throw e; } }
SqlConnection connect; //database object public LoginClass(string usr, string pass) { username = usr; password = pass; connect = new SqlConnection(ConfigurationManager.ConnectionStrings["CommunityAssistConnectionString"].ConnectionString); }
void GetMedicalHistory(int ID) { using (SqlConnection con = new SqlConnection(Helper.GetCon())) using (SqlCommand cmd = new SqlCommand()) { con.Open(); cmd.Connection = con; cmd.CommandText = "SELECT Operation, Details, StartDate, EndDate " + "FROM MedicalHistory WHERE DispatchID=@DispatchID"; cmd.Parameters.AddWithValue("@DispatchID", ID); using (SqlDataReader data = cmd.ExecuteReader()) { if (data.HasRows) { while (data.Read()) { DateTime sDate = Convert.ToDateTime(data["StartDate"].ToString()); DateTime eDate = Convert.ToDateTime(data["EndDate"].ToString()); txtOperation.Text = data["Operation"].ToString(); txtStartDate.Text = sDate.ToString("MM/dd/yyyy"); txtEndDate.Text = eDate.ToString("MM/dd/yyyy"); txtDetails.Text = data["Details"].ToString(); } con.Close(); } else { con.Close(); Response.Redirect("View.aspx"); } } } }
protected void Button4_Click(object sender, EventArgs e) { con = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\Users\\Shubhdip\\Downloads\\job portal\\job portal\\Jobadda\\App_Data\\JobportalDB.mdf;Integrated Security=True;User Instance=True"); con.Open(); int cid=0; cid = Convert.ToInt32(Session["Cid"].ToString()); int rws = 0; rws = Convert.ToInt32(GridView1.Rows.Count.ToString()); // Label22.Text = GridView1.Rows.Count.ToString(); for (int i = 0; i < rws ; i++) { CheckBox chk = (CheckBox)GridView1.Rows[i].FindControl("CheckBox3"); if (chk.Checked == true) { String a = GridView1.Rows[i].Cells[2].Text; string query = "insert into Saved_jobs (candidate_id,jobpost_id) values (" + cid + "," + a + ")"; cmd = new SqlCommand(query, con); cmd.ExecuteNonQuery(); } } }
protected void Button1_Click(object sender, EventArgs e) { Label1.Text = Server.HtmlEncode(this.elm1.Value); //Label1.Text = this.elm1.Value; //String QueryString = "INSERT INTO Article (Id, auteur, date, categorie) VALUES ('7', 'FCW', '', 'Informatique')"; String QueryString = "INSERT INTO Article_Blog (ID, Auteur, Content, Tag) VALUES ('" + TextBox1.Text + "', '" + TextBox2.Text + "', '" + this.elm1.Value + "','" + TextBox3.Text + "')"; SqlConnection Cn = new SqlConnection("server=WAZZUP-PC\\SQLEXPRESS; initial catalog=TestDB; integrated security=true"); try { Cn.Open(); if (Cn != null) { Label1.Text = "CONNECTER"; Console.WriteLine("CONNECTER"); } SqlCommand Cmd = new SqlCommand(); Cmd.CommandText = QueryString; //Cmd.CommandType = CommandType.Text; Cmd.Connection = Cn; Cmd.ExecuteNonQuery(); Cn.Close(); } catch (Exception Ex) { Label1.Text = Ex.ToString(); Cn.Close(); }; }
public Category[] GetAllCategories() { List<Category> myCategories = new List<Category>(); SqlConnection myConn = new SqlConnection(connstring); myConn.Open(); SqlCommand mySqlCommand = new SqlCommand("select * from Category", myConn); SqlDataReader reader = mySqlCommand.ExecuteReader(); while (reader.Read()) { Category myCategory = new Category(); object id = reader["Id"]; if (id != null) { int categoryId = -1; if (!int.TryParse(id.ToString(), out categoryId)) { throw new Exception("Failed to parse Id of video."); } myCategory.Id = categoryId; } myCategory.Name = reader["Name"].ToString(); myCategories.Add(myCategory); } myConn.Close(); return myCategories.ToArray(); }
public Video[] GetAllVideo() { List<Video> myVideos = new List<Video>(); SqlConnection myConn = new SqlConnection(connstring); myConn.Open(); SqlCommand mySqlCommand = new SqlCommand("select * from video", myConn); SqlDataReader reader = mySqlCommand.ExecuteReader(); while (reader.Read()) { Video myVideo = new Video(); object id = reader["Id"]; if(id != null) { int videoId = -1; if (!int.TryParse(id.ToString(), out videoId)) { throw new Exception("Failed to parse Id of video."); } myVideo.Id = videoId; } myVideo.Name = reader["Name"].ToString(); myVideo.Url = reader["Url"].ToString(); myVideos.Add(myVideo); } myConn.Close(); return myVideos.ToArray(); }
void GetCorporateMembership() { if (CheckCorporateMembership()) { using (SqlConnection con = new SqlConnection(Helper.GetCon())) using (SqlCommand cmd = new SqlCommand()) { con.Open(); cmd.Connection = con; cmd.CommandText = "SELECT CorporateID FROM Users " + "WHERE UserID=@UserID"; cmd.Parameters.AddWithValue("@UserID", Session["userid"].ToString()); int corporateID = (int)cmd.ExecuteScalar(); cmd.CommandText = "SELECT Name, Length FROM CorporateAccounts " + "INNER JOIN CorporatePayments ON CorporateAccounts.CorporateID=" + "CorporatePayments.CorporateID WHERE CorporateAccounts.CorporateID=" + "@CorporateID"; cmd.Parameters.AddWithValue("@CorporateID", corporateID); SqlDataReader da = cmd.ExecuteReader(); while (da.Read()) { txtEmployerName.Text = da["Name"].ToString(); txtCorporateLength.Text = da["Length"].ToString(); txtCorporateType.Text = "Corporate/Bulk"; } } } else { panelCorporate.Visible = false; } }
protected void Page_Load(object sender, EventArgs e) { if (Page.IsPostBack) { Page.Validate(); if (Page.IsValid) { SqlConnection dbConnection = new SqlConnection("Data Source=cscsql2.carrollu.edu;Initial Catalog=csc319mcrowell;Persist Security Info=True;User ID=csc319mcrowell;Password=393160"); try { dbConnection.Open(); string SQLString = "SELECT * FROM students WHERE studentID=" + studentID.Text; SqlCommand checkIDTable = new SqlCommand(SQLString, dbConnection); SqlDataReader idRecords = checkIDTable.ExecuteReader(); if (idRecords.Read()) { Response.Redirect("ReturningStudent.aspx?studentID=" + studentID.Text); idRecords.Close(); } else { validateMessage.Text = "<p>**Invalid student ID**</P>"; idRecords.Close(); } } catch (SqlException exception) { Response.Write("<p>Error code " + exception.Number + ": " + exception.Message + "</p>"); } } } }
public DBAccounts() { string constr = ConfigurationManager.ConnectionStrings["Accounts"].ConnectionString; Con = new SqlConnection(constr); Com = new SqlCommand(); Com.Connection = Con; }
protected void btnOpen_Click(object sender, EventArgs e) { string dbName = tbDBName.Text; SqlConnection con = new SqlConnection(); con.StateChange += con_StateChange; con.InfoMessage += con_InfoMessage; try { con.ConnectionString = ConfigurationManager.ConnectionStrings[dbName].ConnectionString; con.Open(); } catch (SqlException ex) { lbLog.Items.Add(ex.Message); } catch (Exception ex) { lbLog.Items.Add(ex.Message); } finally { con.Dispose(); } }
protected void Button1_Click(object sender, EventArgs e) { SqlTransaction myTransaction = null; { try { SqlConnection conn = new SqlConnection(@"Data Source=ajjpsqlserverdb.db.4338448.hostedresource.com; database=ajjpsqlserverdb; User ID=ajjpsqlserverdb; Password= Devry2010;"); conn.Open(); SqlCommand command = conn.CreateCommand(); string strSQL; string txtBoxText = TextBox1.Text; txtBoxText = txtBoxText.Replace("'", "''"); myTransaction = conn.BeginTransaction(); command.Transaction = myTransaction; strSQL = "UPDATE aspnet_Membership SET Password = '******' WHERE UserID = '" + DropDownList1.SelectedValue + "'"; command.CommandType = System.Data.CommandType.Text; command.CommandText = strSQL; command.ExecuteNonQuery(); myTransaction.Commit(); command.Connection.Close(); Response.Redirect("~/User/Main.aspx"); } catch (Exception ex) { lblErr.Text = ex.Message; } } }
// To insert 'Group' record in database by stored procedure public int InsertGroup(string GroupName, string GroupDescription,bool IsActive, int LoginUser,string Ret) { SqlConnection Conn = new SqlConnection(ConnStr); Conn.Open(); // 'uspInsertGroup' stored procedure is used to insert record in Group table SqlCommand DCmd = new SqlCommand("uspInsertGroup", Conn); DCmd.CommandType = CommandType.StoredProcedure; try { DCmd.Parameters.AddWithValue("@GroupName", GroupName); DCmd.Parameters.AddWithValue("@GroupDescription", GroupDescription); DCmd.Parameters.AddWithValue("@LoggedInUser", LoginUser); DCmd.Parameters.AddWithValue("@IsActive", IsActive); DCmd.Parameters.AddWithValue("@RetMsg", Ret); return DCmd.ExecuteNonQuery(); } catch { throw; } finally { DCmd.Dispose(); Conn.Close(); Conn.Dispose(); } }
private void AddNewRecord(string contactid, string name, string phone, string fileName) { SqlConnection connection = new SqlConnection(GetConnectionString()); string sqlStatement = string.Empty; sqlStatement = "INSERT INTO tblContact" + " VALUES (@contactid,@name,@phone,@image)"; try { connection.Open(); SqlCommand cmd = new SqlCommand(sqlStatement, connection); cmd.Parameters.AddWithValue("@contactid", contactid); cmd.Parameters.AddWithValue("@name", name); cmd.Parameters.AddWithValue("@phone", phone); cmd.Parameters.AddWithValue("@image", fileName); cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); } catch (System.Data.SqlClient.SqlException ex) { string msg = "Insert Error:"; msg += ex.Message; throw new Exception(msg); } finally { connection.Close(); } }
public static List<ReporteNotificacionGeocerca> ObtenerNotificacionesGeocerca(List<string> unidades, List<string> geocercas, DateTime fechaInicial, DateTime fechaFinal, int accion, string cliente) { List<ReporteNotificacionGeocerca> reporteFinal = new List<ReporteNotificacionGeocerca>(); List<Vehiculo> vehiculos = AutentificacionBD.VehiculosCliente(cliente); string query = "SELECT Unidad, accion, fechahora, geocerca, nombre, id_reportegeocerca " + "FROM vReporteGeocercaUTC " + "WHERE (Unidad IN ("; foreach (string unidad in unidades) query += "'" + unidad + "', "; //quitar la última coma y espacio query = query.Remove(query.Length - 2); //agregar los dos paréntesis finales. query += ")) AND (fechahora between @fechainicial AND @fechafinal) "; if (accion == ReporteNotificacionGeocerca.AccionFuera) { query += "AND accion = 'Fuera' "; } else if(accion == ReporteNotificacionGeocerca.AccionDentro) { query += "AND accion = 'Dentro' "; } else if (accion == ReporteNotificacionGeocerca.AccionDentroFuera) { query += " AND (accion = 'Fuera' OR accion = 'Dentro') "; } query +="ORDER BY fechahora DESC"; using (SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["connectionStringBD"].ConnectionString)) { sqlConnection.Open(); using (SqlCommand sqlCommand = new SqlCommand(query,sqlConnection)) { sqlCommand.Parameters.AddWithValue("@fechainicial", fechaInicial); sqlCommand.Parameters.AddWithValue("@fechafinal", fechaFinal); Hashtable nombresGeocercas = GeocercasBD.ConsultarGeocercasNombreBD(cliente); using (SqlDataReader reader = sqlCommand.ExecuteReader()) { while (reader.Read()){ string geocercaID= (string)reader["geocerca"]; if (geocercas.Where(x=>x.Equals(geocercaID)).ToList().Count == 1) { ReporteNotificacionGeocerca reporte = new ReporteNotificacionGeocerca(); string sVehiculo = (string)reader["Unidad"]; string sAccionBD = (string)reader["accion"]; reporte.VehiculoID = sVehiculo; reporte.GeocercaID = geocercaID; reporte.Fecha = (DateTime)reader["fechahora"]; reporte.Vehiculo = vehiculos.Find(x=> x.Unidad.Equals(sVehiculo)).Descripcion; //reporte.Accion = sAccionBD == "Dentro"?Resources.Reportes.aspx.Dentro:Resources.Reportes.aspx.Fuera; reporte.iAccion = sAccionBD == "Dentro" ? ReporteNotificacionGeocerca.AccionDentro : ReporteNotificacionGeocerca.AccionFuera; reporte.Geocerca = nombresGeocercas[geocercaID].ToString(); reporteFinal.Add(reporte); } } } } } return reporteFinal.OrderBy(x => x.Fecha).ToList(); }
public static string PublishActivity(int ClubId, string ActivityContent) { // 将新增活动存入数据库,并从数据库返回信息及数据 string connString = System.Configuration.ConfigurationManager.ConnectionStrings["CZConnectionString"].ConnectionString; SqlConnection conn = new SqlConnection(connString); conn.Open(); // 存储 string PublishDate = DateTime.Now.ToString(); string queryString1 = "Insert Into Activity Values (" + ClubId + ",N'" + ActivityContent + "','" + PublishDate + "')"; SqlCommand cmd = new SqlCommand(queryString1, conn); cmd.ExecuteNonQuery(); // 查询最后插入的数据,就是新的数据 string queryString2 = "Select Top 1 * From Activity Where ClubId=" + ClubId + " Order By PublishDate Desc"; cmd = new SqlCommand(queryString2, conn); SqlDataAdapter adapter = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); adapter.Fill(ds); int Id = Convert.ToInt32(ds.Tables[0].Rows[0]["Id"].ToString()); string Content = ds.Tables[0].Rows[0]["Content"].ToString(); string Date = ds.Tables[0].Rows[0]["PublishDate"].ToString(); conn.Close(); // 通过判断前后时间知是否查入成功 if (PublishDate == Date) { return "{status:1,id:" + Id + ",content:'" + Content + "',date:'" + Date + "'}"; } else { return "{status:-1}"; } }
// To Change status of 'Group' record of specific GroupId from database by stored procedure public int ChangeGroupStatus(int GroupId, int LoggedInUser, string returnmsg, bool IsActive) { SqlConnection Conn = new SqlConnection(ConnStr); Conn.Open(); // 'uspUpdateGroupStatus' stored procedure is used to Chnage Status of record in Group table SqlCommand DCmd = new SqlCommand("uspUpdateGroupStatus", Conn); DCmd.CommandType = CommandType.StoredProcedure; DCmd.Parameters.AddWithValue("@GroupId", GroupId); DCmd.Parameters.AddWithValue("@LoggedInUser", LoggedInUser); DCmd.Parameters.AddWithValue("@IsActive", IsActive); DCmd.Parameters.AddWithValue("@RetMsg", returnmsg); try { return DCmd.ExecuteNonQuery(); } catch { throw; } finally { DCmd.Dispose(); Conn.Close(); Conn.Dispose(); } }
public dapperHelper() { sqlConn = ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString; db = new SqlConnection(sqlConn); }
private void btnDelete_Click(object sender, EventArgs e) { string err = DataValidate(); if (!string.IsNullOrEmpty(err)) { MessageBox.Show(err, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } GrpMain.Enabled = false; Cursor.Current = Cursors.WaitCursor; using (SqlConnection cn = new SqlConnection(Utils.Helper.constr)) { try { cn.Open(); } catch (Exception ex) { MessageBox.Show(err, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } SqlTransaction tr = cn.BeginTransaction("DeleteEmp"); try { using (SqlCommand cmd = new SqlCommand()) { cmd.Connection = cn; cmd.Transaction = tr; string sql = "insert into MastEmpHistory " + " select 'Before Delete Master Data, Action By " + Utils.User.GUserID + "',GetDate(),* from MastEmp where CompCode = '" + txtCompCode.Text.Trim() + "' " + " and EmpUnqID ='" + txtEmpUnqID.Text.Trim() + "' "; cmd.CommandType = CommandType.Text; cmd.CommandText = sql; cmd.ExecuteNonQuery(); cmd.CommandText = "Delete from AttdData where EmpUnqID = '" + txtEmpUnqID.Text.Trim() + "'"; cmd.ExecuteNonQuery(); cmd.CommandText = "Delete from MastEmpBio where EmpUnqID = '" + txtEmpUnqID.Text.Trim() + "'"; cmd.ExecuteNonQuery(); cmd.CommandText = "Delete from LeaveEntry where EmpUnqID = '" + txtEmpUnqID.Text.Trim() + "'"; cmd.ExecuteNonQuery(); cmd.CommandText = "Delete from MastEmpFamily where EmpUnqID = '" + txtEmpUnqID.Text.Trim() + "'"; cmd.ExecuteNonQuery(); cmd.CommandText = "Delete from MastEmpExp where EmpUnqID = '" + txtEmpUnqID.Text.Trim() + "'"; cmd.ExecuteNonQuery(); cmd.CommandText = "Delete from MastEmpEDU where EmpUnqID = '" + txtEmpUnqID.Text.Trim() + "'"; cmd.ExecuteNonQuery(); cmd.CommandText = "Delete from MastEmpPPE where EmpUnqID = '" + txtEmpUnqID.Text.Trim() + "'"; cmd.ExecuteNonQuery(); cmd.CommandText = "Delete from MastLeaveSchedule where EmpUnqID = '" + txtEmpUnqID.Text.Trim() + "'"; cmd.ExecuteNonQuery(); cmd.CommandText = "Delete from MastShiftSchedule where EmpUnqID = '" + txtEmpUnqID.Text.Trim() + "'"; cmd.ExecuteNonQuery(); //cmd.CommandText = "Delete from ATTDLOG where EmpUnqID = '" + txtEmpUnqID.Text.Trim() + "'"; //cmd.ExecuteNonQuery(); cmd.CommandText = "Delete from LeaveBal where EmpUnqID = '" + txtEmpUnqID.Text.Trim() + "'"; cmd.ExecuteNonQuery(); cmd.CommandText = "Delete from MastEmp where EmpUnqID = '" + txtEmpUnqID.Text.Trim() + "'"; cmd.ExecuteNonQuery(); tr.Commit(); MessageBox.Show("Record Deleted Sucessfull...", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information); } } catch (Exception ex) { tr.Rollback(); MessageBox.Show(err + ex.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } } GrpMain.Enabled = true; Cursor.Current = Cursors.Default; ResetCtrl(); SetRights(); }
public static СправочникиСсылка.Склады НайтиПоКоду(string Код) { using (var Подключение = new SqlConnection(СтрокаСоединения)) { Подключение.Open(); using (var Команда = Подключение.CreateCommand()) { Команда.CommandText = @"Select top 1 _IDRRef [Ссылка] ,_Version [Версия] ,_Marked [ПометкаУдаления] ,_IsMetadata [Предопределенный] ,_ParentIDRRef [Родитель] ,_Folder [ЭтоГруппа] ,_Code [Код] ,_Description [Наименование] ,_Fld1744 [Комментарий] ,_Fld1745RRef [ТипЦенРозничнойТорговли] ,_Fld1746RRef [Подразделение] ,_Fld1747RRef [ВидСклада] ,_Fld1748 [НомерСекции] ,_Fld1749 [РасчетРозничныхЦенПоТорговойНаценке] From _Reference147(NOLOCK) Where _Code=@Код" ; Команда.Parameters.AddWithValue("Код", Код); using (var Читалка = Команда.ExecuteReader()) { if (Читалка.Read()) { var Ссылка = new СправочникиСсылка.Склады(); //ToDo: Читать нужно через GetValues() Ссылка.Ссылка = new Guid((byte[])Читалка.GetValue(0)); var ПотокВерсии = ((byte[])Читалка.GetValue(1)); Array.Reverse(ПотокВерсии); Ссылка.Версия = BitConverter.ToInt64(ПотокВерсии, 0); Ссылка.ВерсияДанных = Convert.ToBase64String(ПотокВерсии); Ссылка.ПометкаУдаления = ((byte[])Читалка.GetValue(2))[0] == 1; Ссылка.Предопределенный = ((byte[])Читалка.GetValue(3))[0] == 1; Ссылка.одитель = V82.СправочникиСсылка.Склады.ВзятьИзКэша((byte[])Читалка.GetValue(4)); Ссылка.ЭтоГруппа = ((byte[])Читалка.GetValue(5))[0] == 0; Ссылка.Код = Читалка.GetString(6); Ссылка.Наименование = Читалка.GetString(7); if (!Ссылка.ЭтоГруппа) { Ссылка.Комментарий = Читалка.GetString(8); Ссылка.ТипЦенРозничнойТорговли = V82.СправочникиСсылка.ТипыЦенНоменклатуры.ВзятьИзКэша((byte[])Читалка.GetValue(9)); Ссылка.Подразделение = V82.СправочникиСсылка.Подразделения.ВзятьИзКэша((byte[])Читалка.GetValue(10)); Ссылка.ВидСклада = V82.Перечисления /*Ссылка*/.ВидыСкладов.ПустаяСсылка.Получить((byte[])Читалка.GetValue(11)); Ссылка.НомерСекции = Читалка.GetDecimal(12); Ссылка.асчетРозничныхЦенПоТорговойНаценке = ((byte[])Читалка.GetValue(13))[0] == 1; } return(Ссылка); } else { return(null); } } } } }
private static void PrepareTransSqlCommand(string ProcedureName, SqlCommand cmd, SqlConnection conn, SqlTransaction trans) { cmd.Transaction = trans; cmd.Connection = conn; cmd.CommandText = ProcedureName; cmd.CommandType = CommandType.StoredProcedure; }
public SqlBaseRepo(string connectionString) { _connectionString = connectionString; _connection = new SqlConnection(connectionString); TableName = typeof(T).Name; }
public SolutionService() { _connection = new SqlConnection("Data Source=b9wyaqyyrn.database.windows.net;Initial Catalog=MobileApp;User ID=laxmanrapolu;Password=Lucky_123;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;"); }
public EmployeeRepository(SqlConnection sqlconn) { _sqlconn = sqlconn; }
/// <summary> /// 快取 LINQ Query 的結果(僅適用於 LINQ to SQL 環境) /// 使用的的限制跟使用 SqlCacheDependency 的限制一樣 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="q"></param> /// <param name="dc">你的 LINQ to SQL DataContext</param> /// <param name="CacheId">Cache ID,需為每一個不同的 IQueryable 物件設定一個唯一的 ID</param> /// <returns></returns> public static List <T> LinqCache <T>(this IQueryable <T> q, DataContext dc, string CacheId) { List <T> objCache = (List <T>)System.Web.HttpRuntime.Cache.Get(CacheId); if (objCache == null) { #region 从数据库查找 ///////// 尚未快取,實做 new SqlCacheDependeny ////////// // 1. 透過 DataContext 取得連線字串 string connStr = dc.Connection.ConnectionString; // 2. 透過 DataContext 與 IQueryable 物件取得 SqlCommand 物件 SqlCommand sqlCmd = dc.GetCommand(q) as SqlCommand; // 3. 建立要給 SqlCacheDependency 使用的 SqlConnection 物件 using (SqlConnection conn = new SqlConnection(connStr)) { conn.Open(); // 4. 建立要給 SqlCacheDependency 使用的 SqlCommand 物件 using (SqlCommand cmd = new SqlCommand(sqlCmd.CommandText, conn)) { // 5.0 將 sqlCmd 中的所有參數傳遞給 cmd 物件 foreach (System.Data.Common.DbParameter dbp in sqlCmd.Parameters) { cmd.Parameters.Add(new SqlParameter(dbp.ParameterName, dbp.Value)); } // 5.1 啟用資料庫的 Query notifications 功能 SqlCacheDependencyAdmin.EnableNotifications(connStr); // 5.2 取得要進行異動通知的表格名稱(ElementType) string NotificationTable = q.ElementType.Name; // 5.3 將取得的 NotificationTable 啟用通知功能 if (!SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(connStr).Contains(NotificationTable)) { SqlCacheDependencyAdmin.EnableTableForNotifications(connStr, NotificationTable); } // 6. 建立 SqlCacheDependency物件 SqlCacheDependency sqlDep = new SqlCacheDependency(cmd); // 7. 刷新 LINQ to SQL 的值(取得資料庫中的最新資料) dc.Refresh(RefreshMode.OverwriteCurrentValues, q); // 8. 執行 SqlCacheDepency 查詢 cmd.ExecuteNonQuery(); // 9. 執行 LINQ to SQL 的查詢,並將結果轉成 List<T> 型別,避免延遲查詢(Delayed Query)立即將資料取回 objCache = q.ToList(); //10. 將結果插入到 System.Web.HttpRuntime.Cache 物件中,並且指定 SqlCacheDependency 物件 System.Web.HttpRuntime.Cache.Insert(CacheId, objCache, sqlDep); } } #endregion } // 回傳查詢結果(或快取的結果) return(objCache); }
public SqlCommand getCommand(string sql, SqlConnection connection) { return new SqlCommand(sql, connection); }
public ManejadorCliente(SqlConnection pConnection, SqlTransaction pTransaction) : base(pConnection, pTransaction) { }
private void btnAdd_Click(object sender, EventArgs e) { string err = DataValidate(); if (!string.IsNullOrEmpty(err)) { MessageBox.Show(err, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (mode == "NEW" && dupadhar) { string msg = "This Adhar No is Already Registered with " + dupadharemp + Environment.NewLine + " Are you sure to Insert this as New Employee ?"; DialogResult qdr = MessageBox.Show(msg, "Question", MessageBoxButtons.YesNo, MessageBoxIcon.Question); if (qdr == DialogResult.No) { return; } } Cursor.Current = Cursors.WaitCursor; GrpMain.Enabled = false; using (SqlConnection cn = new SqlConnection(Utils.Helper.constr)) { using (SqlCommand cmd = new SqlCommand()) { try { if (string.IsNullOrEmpty(txtBasic.Text.Trim())) { txtBasic.Text = "0"; } cn.Open(); cmd.Connection = cn; string sql = "Insert into MastEmp (CompCode,WrkGrp,EmpUnqID,EmpName,FatherName," + " UnitCode,MessCode,MessGrpCode,BirthDt,JoinDt,ValidFrom,ValidTo," + " ADHARNO,IDPRF3,IDPRF3No,Sex,ContractFlg,PayrollFlg,OTFLG,Weekoff,Active,AddDt,AddID,Basic) Values (" + "'{0}','{1}','{2}','{3}','{4}' ," + " '{5}',{6},{7},'{8}','{9}',{10},{11}," + " '{12}','ADHARCARD','{13}','{14}','{15}','{16}','{17}','{18}','1',GetDate(),'{19}','{20}')"; sql = string.Format(sql, txtCompCode.Text.Trim().ToString(), txtWrkGrpCode.Text.Trim().ToString(), txtEmpUnqID.Text.Trim().ToString(), txtEmpName.Text.Trim().ToString(), txtFatherName.Text.Trim(), txtUnitCode.Text.ToString(), ((txtMessCode.Text.Trim() == "")? "null" :"'" + txtMessCode.Text.Trim() + "'"), ((txtMessGrpCode.Text.Trim() == "")? "null" :"'" + txtMessGrpCode.Text.Trim() + "'"), txtBirthDT.DateTime.ToString("yyyy-MM-dd"), txtJoinDt.DateTime.ToString("yyyy-MM-dd"), ((txtWrkGrpCode.Text.Trim() == "COMP") ? "null" : "'" + txtValidFrom.DateTime.ToString("yyyy-MM-dd") + "'"), ((txtWrkGrpCode.Text.Trim() == "COMP") ? "null" : "'" + txtValidTo.DateTime.ToString("yyyy-MM-dd") + "'"), txtAdharNo.Text.Trim(), txtAdharNo.Text.Trim(), ((Convert.ToBoolean(txtGender.EditValue))?1:0), ((chkCont.Checked)?1:0), ((chkComp.Checked)?1:0), ((chkOTFlg.Checked)?1:0), txtWeekOff.Text.Trim(), Utils.User.GUserID, txtBasic.Text.Trim()); cmd.CommandText = sql; cmd.ExecuteNonQuery(); //createmuster clsEmp t = new clsEmp(); string err2 = string.Empty; if (t.GetEmpDetails(txtCompCode.Text.Trim(), txtEmpUnqID.Text.Trim())) { DateTime sFromDt, sToDt, sCurDt; sCurDt = Convert.ToDateTime(Utils.Helper.GetDescription("Select GetDate()", Utils.Helper.constr)); if (txtJoinDt.DateTime.Year < sCurDt.Year) { sFromDt = Convert.ToDateTime(Utils.Helper.GetDescription("Select CalendarStartOfYearDate from dbo.F_TABLE_DATE(GetDate(),GetDate())", Utils.Helper.constr)); sToDt = Convert.ToDateTime(Utils.Helper.GetDescription("Select CalendarEndOfYearDate from dbo.F_TABLE_DATE(GetDate(),GetDate())", Utils.Helper.constr)); } else { sFromDt = txtJoinDt.DateTime; sToDt = Convert.ToDateTime(Utils.Helper.GetDescription("Select CalendarEndOfYearDate from dbo.F_TABLE_DATE('" + sFromDt.ToString("yyyy-MM-dd") + "','" + sFromDt.ToString("yyyy-MM-dd") + "')", Utils.Helper.constr)); } if (!t.CreateMuster(sFromDt, sToDt, out err2)) { MessageBox.Show(err, "Error While Creating Muster Table", MessageBoxButtons.OK, MessageBoxIcon.Error); } } if (string.IsNullOrEmpty(err2)) { MessageBox.Show("Record saved...", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information); } else { MessageBox.Show("Record saved with error please check muster table created...", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information); } ResetCtrl(); }catch (Exception ex) { MessageBox.Show(ex.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } } } GrpMain.Enabled = true; Cursor.Current = Cursors.Default; }
private void btnUpdate_Click(object sender, EventArgs e) { string err = DataValidate(); bool WrkGrpChange = false; if (!string.IsNullOrEmpty(err)) { MessageBox.Show(err, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } if (dupadhar) { string msg = "This Adhar No is Already Registered with " + dupadharemp + Environment.NewLine + " Are you sure to Update this Adhar No ?"; DialogResult qdr = MessageBox.Show(msg, "Question", MessageBoxButtons.YesNo, MessageBoxIcon.Question); if (qdr == DialogResult.No) { return; } } GrpMain.Enabled = false; Cursor.Current = Cursors.WaitCursor; using (SqlConnection cn = new SqlConnection(Utils.Helper.constr)) { using (SqlCommand cmd = new SqlCommand()) { try { cn.Open(); cmd.Connection = cn; string sql = "insert into MastEmpHistory " + " select 'Before Update Master Data, Action By " + Utils.User.GUserID + "', GetDate(), * from MastEmp where CompCode = '" + txtCompCode.Text.Trim() + "' " + " and EmpUnqID ='" + txtEmpUnqID.Text.Trim() + "'"; cmd.CommandType = CommandType.Text; cmd.CommandText = sql; cmd.ExecuteNonQuery(); clsEmp t = new clsEmp(); t.CompCode = txtCompCode.Text.Trim(); t.EmpUnqID = txtEmpUnqID.Text.Trim(); t.GetEmpDetails(t.CompCode, t.EmpUnqID); //WrkGrp is Changed.. need to process in all tables.. if (t.WrkGrp != txtWrkGrpCode.Text.Trim()) { try { sql = "Exec ChangeWrkGrp '" + t.EmpUnqID + "','" + txtWrkGrpCode.Text.Trim() + "';"; cmd.CommandText = sql; cmd.ExecuteNonQuery(); WrkGrpChange = true; } catch (Exception ex) { WrkGrpChange = false; GrpMain.Enabled = true; Cursor.Current = Cursors.Default; MessageBox.Show("Kindly Clear the Job Profile First,(EmpTypeCode,CatCode,GradeCode,DesgCode,DeptCode,StatCode)" + Environment.NewLine + "and try again..." , "WrkGrp Change Error", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } } else { WrkGrpChange = false; } if (string.IsNullOrEmpty(txtBasic.Text.Trim())) { txtBasic.Text = "0"; } sql = "Update MastEmp set WrkGrp ='{0}',EmpName='{1}',FatherName = '{2}'," + " UnitCode = '{3}',MessCode={4},MessGrpCode = {5},BirthDt ='{6}',JoinDt ='{7}',ValidFrom = {8},ValidTo = {9}," + " ADHARNO = '{10}',IDPRF3No = '{11}',Sex='{12}',ContractFlg='{13}',PayrollFlg='{14}',OTFLG='{15}',Weekoff='{16}',UpdDt=GetDate(),UpdID ='{17}', Basic='{18}' Where " + " CompCode ='{19}' and EmpUnqID = '{20}'"; sql = string.Format(sql, txtWrkGrpCode.Text.Trim().ToString(), txtEmpName.Text.Trim().ToString(), txtFatherName.Text.Trim(), txtUnitCode.Text.ToString(), ((txtMessCode.Text.Trim() == "") ? "null" : "'" + txtMessCode.Text.Trim() + "'"), ((txtMessGrpCode.Text.Trim() == "") ? "null" : "'" + txtMessGrpCode.Text.Trim() + "'"), txtBirthDT.DateTime.ToString("yyyy-MM-dd"), txtJoinDt.DateTime.ToString("yyyy-MM-dd"), ((txtWrkGrpCode.Text.Trim() == "COMP")? "null" :"'" + txtValidFrom.DateTime.ToString("yyyy-MM-dd") + "'"), ((txtWrkGrpCode.Text.Trim() == "COMP")? "null" :"'" + txtValidTo.DateTime.ToString("yyyy-MM-dd") + "'"), txtAdharNo.Text.Trim(), txtAdharNo.Text.Trim(), ((Convert.ToBoolean(txtGender.EditValue))?1:0), ((chkCont.Checked) ? 1 : 0), ((chkComp.Checked) ? 1 : 0), ((chkOTFlg.Checked) ? 1 : 0), txtWeekOff.Text.Trim(), Utils.User.GUserID, txtBasic.Text.Trim(), txtCompCode.Text.Trim(), txtEmpUnqID.Text.Trim() ); cmd.CommandText = sql; cmd.ExecuteNonQuery(); if (WrkGrpChange) { MessageBox.Show("Employee Job Profile is Discarded.." + Environment.NewLine + "Please Fill the Employee Job Profile Again.." , "Information", MessageBoxButtons.OK, MessageBoxIcon.Information); } MessageBox.Show("Record Updated...", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information); ResetCtrl(); } catch (Exception ex) { MessageBox.Show(ex.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } } } GrpMain.Enabled = true; Cursor.Current = Cursors.Default; }
public Cls_SQL() { // // TODO: 在此加入建構函式的程式碼 conn = sqlConnection(); }
protected void Button1_Click(object sender, EventArgs e) { int courseID, assignmentNumber, fullGrade; decimal weight; DateTime deadline; try { courseID = Int16.Parse(TextBox1.Text); assignmentNumber = Int16.Parse(TextBox2.Text); fullGrade = Int16.Parse(TextBox4.Text); weight = decimal.Parse(TextBox5.Text); } catch { Label1.Text = "Please enter a number in course ID, Assignment number, full grade, and weight"; return; } try { deadline = Convert.ToDateTime(TextBox6.Text); } catch { Label1.Text = "Enter a valid date (DD/MM/YYYY)"; return; } string content = TextBox7.Text; string type = TextBox3.Text; string connStr = WebConfigurationManager.ConnectionStrings["GUCera"].ToString(); SqlConnection conn = new SqlConnection(connStr); SqlCommand addAssignment = new SqlCommand("DefineAssignmentOfCourseOfCertianType", conn); addAssignment.CommandType = CommandType.StoredProcedure; addAssignment.Parameters.Add(new SqlParameter("@cid", courseID)); addAssignment.Parameters.Add(new SqlParameter("@number", assignmentNumber)); addAssignment.Parameters.Add(new SqlParameter("@type", type)); addAssignment.Parameters.Add(new SqlParameter("@fullGrade", fullGrade)); addAssignment.Parameters.Add(new SqlParameter("@weight", weight)); addAssignment.Parameters.Add(new SqlParameter("@deadline", deadline)); addAssignment.Parameters.Add(new SqlParameter("@content", content)); addAssignment.Parameters.Add(new SqlParameter("@instId", Session["userId"])); conn.Open(); try { addAssignment.ExecuteNonQuery(); Label1.Text = "Success"; } catch { Label1.Text = "Error"; } conn.Close(); }
protected void Page_Load(object sender, EventArgs e) { if (Session["UserId"] == null) { Response.Redirect("login.aspx"); } if (Session["SESSION_USERTYPE"].ToString() != ((int)Constant.USERTYPE.VMOFFICER).ToString()) { Response.Redirect("login.aspx"); } if (IsPostBack) { if (Request["__EVENTTARGET"].ToString() == "Details" && Request["__EVENTARGUMENT"].ToString() != "") { VendorIdstr = Request["__EVENTARGUMENT"].ToString().Trim(); string sArg = VendorIdstr.Trim(); char[] mySeparator = new char[] { '|' }; string[] Arr = sArg.Split(mySeparator); string VendorAlias = ""; int SendToSAP_Status = 0; query = "SELECT CompanyName FROM tblVendor WHERE VendorId=@VendorId"; using (conn = new SqlConnection(connstring)) { using (cmd = new SqlCommand(query, conn)) { cmd.Parameters.AddWithValue("@VendorId", Convert.ToInt32(Arr[0].ToString())); conn.Open(); oReader = cmd.ExecuteReader(); if (oReader.HasRows) { while (oReader.Read()) { VendorAlias = oReader["CompanyName"].ToString().Replace(" ", "").Substring(0, 4).ToLower(); } SendMail(Arr[0].ToString(), Arr[1].ToString()); if (Arr[1].ToString() == "6") { SendToSAP_Status = 1; } query = @"UPDATE tblVendor SET NotificationSent=NULL, SendToSAP_Status=NULL, Status=0, IsAuthenticated = NULL, AuthenticationTicket = NULL, approvedbyDnb = NULL, approvedbyDnbDate=NULL, approvedbyVMOfficer = NULL, approvedbyVMOfficerDate = NULL, approvedbyVMReco = NULL, approvedbyVMRecoDate = NULL, approvedbyFAALogistics = NULL, approvedbyFAALogisticsDate = NULL, approvedbyFAAFinance = NULL, approvedbyFAAFinanceDate = NULL WHERE VendorId=@VendorId"; using (conn = new SqlConnection(connstring)) { using (cmd = new SqlCommand(query, conn)) { cmd.Parameters.AddWithValue("@VendorId", Convert.ToInt32(Arr[0].ToString())); conn.Open(); cmd.ExecuteNonQuery(); } } } } } GridView1.DataBind(); } } }
public DataProvider() { connection = new SqlConnection(connString); }
public UserRepository(SqlConnection connection) { _connection = connection; }
private string CreateNotificationBody(string cfromName, string ctoName, string cAuthenticationNumber, string cVendorName, string VendorIdx) { SqlDataReader oReader; string connstring = ConfigurationManager.ConnectionStrings["AVAConnectionString"].ConnectionString; string cCeo = "", cCeoEmail = "", cAddress = "", cServices = "", cAccreDuration = ""; query = "SELECT * FROM tblVendorInformation WHERE VendorId = @VendorId"; using (conn = new SqlConnection(connstring)) { using (cmd = new SqlCommand(query, conn)) { cmd.Parameters.AddWithValue("@VendorId", Convert.ToInt32(VendorIdx)); conn.Open(); //Process results oReader = cmd.ExecuteReader(); if (oReader.HasRows) { while (oReader.Read()) { cCeo = oReader["conBidName"].ToString(); cCeoEmail = oReader["conBidEmail"].ToString(); cAddress = oReader["regBldgCode"].ToString() != "" ? cAddress + "Bldg. " + oReader["regBldgCode"].ToString() + ", " : cAddress + ""; cAddress = oReader["regBldgRoom"].ToString() != "" ? cAddress + "Rm. " + oReader["regBldgRoom"].ToString() + ", " : cAddress + ""; cAddress = oReader["regBldgFloor"].ToString() != "" ? cAddress + oReader["regBldgFloor"].ToString() + " Fr, " : cAddress + ""; cAddress = oReader["regBldgHouseNo"].ToString() != "" ? cAddress + "No. " + oReader["regBldgHouseNo"].ToString() + " " : cAddress + ""; cAddress = oReader["regStreetName"].ToString() != "" ? cAddress + oReader["regStreetName"].ToString() + ", " : cAddress + ""; cAddress = cAddress + "<br>"; cAddress = oReader["regCity"].ToString() != "" ? cAddress + oReader["regCity"].ToString() + ", " : cAddress + ""; cAddress = oReader["regProvince"].ToString() != "" ? cAddress + oReader["regProvince"].ToString() + ", " : cAddress + ""; cAddress = cAddress + "<br>"; cAddress = oReader["regCountry"].ToString() != "" ? cAddress + oReader["regCountry"].ToString() + ", " : cAddress + ""; cAddress = oReader["regPostal"].ToString() != "" ? cAddress + oReader["regPostal"].ToString() + " " : cAddress + ""; } } } } query = "SELECT t1.*, t2.CategoryName, t3.SubCategoryName FROM tblVendorProductsAndServices t1, rfcProductCategory t2, rfcProductSubcategory t3 WHERE t2.CategoryId = t1.CategoryId AND t3.SubCategoryId = t1.SubCategoryId AND t1.VendorId = @VendorId"; using (conn = new SqlConnection(connstring)) { using (cmd = new SqlCommand(query, conn)) { cmd.Parameters.AddWithValue("@VendorId", Convert.ToInt32(VendorIdx)); conn.Open(); //Process results oReader = cmd.ExecuteReader(); if (oReader.HasRows) { while (oReader.Read()) { cServices = cServices + "• " + oReader["CategoryName"].ToString() + " - " + oReader["SubCategoryName"].ToString() + "<br>"; } } } } query = "SELECT * FROM tblVendorApprovalbyVmReco WHERE VendorId = @VendorId"; using (conn = new SqlConnection(connstring)) { using (cmd = new SqlCommand(query, conn)) { cmd.Parameters.AddWithValue("@VendorId", Convert.ToInt32(VendorIdx)); conn.Open(); //Process results oReader = cmd.ExecuteReader(); if (oReader.HasRows) { while (oReader.Read()) { cAccreDuration = oReader["AccreDuration"].ToString(); } } } } StringBuilder sb = new StringBuilder(); string sTxt = "<table border='1' style='font-size:12px'>"; sTxt = sTxt + "<tr>"; sTxt = sTxt + "<td><strong> Vendor ID</strong></td>"; sTxt = sTxt + "<td> " + VendorIdx + " </td>"; sTxt = sTxt + "</tr>"; sTxt = sTxt + "<tr>"; sTxt = sTxt + "<td><strong> Company Name</strong></td>"; sTxt = sTxt + "<td> " + cVendorName + " </td>"; sTxt = sTxt + "</tr>"; sTxt = sTxt + "</table>"; sb.Append("<tr><td>"); sb.Append("<p>"); sb.Append("Date: " + DateTime.Now.ToLongDateString() + "<br><br>"); sb.Append(cCeo + "<br>"); sb.Append("<b>" + cVendorName + "</b><br>"); sb.Append(cAddress + "<br><br>"); sb.Append("</p>"); sb.Append("<tr><td>"); sb.Append("<p>"); sb.Append("Dear " + cCeo + ":<br><br>"); sb.Append("Please consider this email as reminders to renew/update your accreditation status with Trans-Asia.<br><br>"); sb.Append("Looking forward for your compliance on this request before your accreditation expired. Failure to comply will temporarily suspend your company to participate on any upcoming bid events.<br><br>"); sb.Append("We encourage you to renew your accreditation and continue to be a business partner of Trans-Asia.<br><br>"); sb.Append("Hope to receive positive response from your end.<br><br>"); sb.Append("Please get in touch with Marife Pablo of Vendor Management for any clarifications.<br><br>"); sb.Append(sTxt); sb.Append("</p>"); sb.Append("<br><br><br>"); sb.Append("Sincerely,<br><br>"); sb.Append("Trans-Asia<br><br>"); sb.Append("</td></tr>"); sb.Append("<tr><td>"); sb.Append("<p> </p><span style='font-size:10px; font-style:italic;'>Please do not reply to this auto-generated message. </span>"); sb.Append("</td></tr>"); //Response.Write(sb.ToString()); return(MailTemplate.IntegrateBodyIntoTemplate(sb.ToString())); }
protected void BUpdate_Click(object sender, EventArgs e) { if (IsPostBack) { string sql = null; char gen; if (rbMale.Checked == true) { gen = 'M'; } else if (rbFemale.Checked == true) { gen = 'F'; } else { gen = default(char); } using (SqlConnection cnn = new SqlConnection("Data Source = JARVIS; Initial Catalog = HousingMSdb; User ID = sa; Password = 2411")) { if (fuImage.HasFile == true) { sql = "UPDATE Users SET [RCNumber]=@RCNumber, [Bdate]=@Bdate, [Gender]=@Gender, [Email]=@Email, [Mobile]=@Mobile, [AMobile]=@AMobile, [Telephone]=@Telephone, [Username]=@Username, [Image]=@Image WHERE Username=@Username"; } else sql = "UPDATE Users SET [RCNumber]=@RCNumber, [Bdate]=@Bdate, [Gender]=@Gender, [Email]=@Email, [Mobile]=@Mobile, [AMobile]=@AMobile, [Telephone]=@Telephone, [Username]=@Username WHERE Username=@Username"; try { using (SqlCommand cmd = new SqlCommand(sql, cnn)) { var cardnumber = cmd.Parameters.AddWithValue("@RCNumber", tbCardNumber.Text); cardnumber.SqlDbType = SqlDbType.NVarChar; var BDate = cmd.Parameters.AddWithValue("@Bdate", Convert.ToDateTime(tbDOB.Text)); BDate.SqlDbType = SqlDbType.DateTime; if (gen != default(char)) cmd.Parameters.AddWithValue("@Gender", gen); else cmd.Parameters.AddWithValue("@Gender", DBNull.Value); var email = cmd.Parameters.AddWithValue("@Email", tbEmail.Text); email.SqlDbType = SqlDbType.NVarChar; var mobile = cmd.Parameters.AddWithValue("@Mobile", tbMobile.Text); mobile.SqlDbType = SqlDbType.NVarChar; var AMobile = cmd.Parameters.AddWithValue("@AMobile", tbAlternateMobile.Text); AMobile.SqlDbType = SqlDbType.NVarChar; var telephone = cmd.Parameters.AddWithValue("@Telephone", tbTelephone.Text); telephone.SqlDbType = SqlDbType.NVarChar; var username = cmd.Parameters.AddWithValue("@Username", tbUsername.Text); username.SqlDbType = SqlDbType.NVarChar; if (fuImage.HasFile == true) { int length = fuImage.PostedFile.ContentLength; byte[] displaypicture = new byte[length]; fuImage.PostedFile.InputStream.Read(displaypicture, 0, length); cmd.Parameters.AddWithValue("@Image", displaypicture); } cnn.Open(); cmd.ExecuteNonQuery(); if (cmd.ExecuteNonQuery() == 1) { Notification(cnn, UID); } } } catch (System.Data.SqlClient.SqlException sqlException) { System.Windows.Forms.MessageBox.Show(sqlException.Message); } finally { cnn.Close(); cnn.Dispose(); } } } }
public DbResponse<int> Inserir(Funcionario func) { int idInserida = -1; string connectionString = Parametros.GetConnectionString(); SqlConnection connection = new SqlConnection(connectionString); SqlCommand command = new SqlCommand(); command.CommandText = @"INSERT INTO FUNCIONARIOS (NOME, CPF, RG, ENDERECO, TELEFONE, EMAIL, SENHA, EHADMIN, EHATIVO) VALUES (@NOME, @CPF, @RG, @ENDERECO, @TELEFONE, @EMAIL, @SENHA, @EHADMIN, @EHATIVO); select scope_identity()"; command.Parameters.AddWithValue("@NOME", func.Nome); command.Parameters.AddWithValue("@CPF", func.CPF); command.Parameters.AddWithValue("@RG", func.RG); command.Parameters.AddWithValue("@ENDERECO", func.Endereco); command.Parameters.AddWithValue("@TELEFONE", func.Telefone); command.Parameters.AddWithValue("@EMAIL", func.Email); command.Parameters.AddWithValue("@SENHA", func.Senha); command.Parameters.AddWithValue("@EHADMIN", func.EhAdmin); command.Parameters.AddWithValue("@EHATIVO", func.EhAtivo); command.Connection = connection; try { connection.Open(); idInserida = Convert.ToInt32(command.ExecuteNonQuery()); } catch (Exception EX) { if (EX.Message.Contains("UNIQUE") || EX.Message.Contains("FK")) { return new DbResponse<int> { Sucesso = false, Mensagem = "Funcionario já cadastrado.", Excessao = EX }; } return new DbResponse<int> { Sucesso = false, Mensagem = "Erro no Bando de Dados, favor contactar o suporte", Excessao = EX }; } finally { connection.Dispose(); } return new DbResponse<int> { Sucesso = true, Mensagem = "Funcionário cadastrado com sucesso", Dados = idInserida }; }
public void BasicIncrementalBackupScenario2() { var dbName = "basic_scenario_two"; var backupFile = ""; var testConfigFileName = tmpFolder + "BasicScenario2.ini"; var parser = new FileIniDataParser(); var config = parser.ReadFile(defaultConfigIni); config["sqlserver"].SetKeyData(new KeyData("database") { Value = dbName }); parser.WriteFile(testConfigFileName, config); var conn = new SqlConnection(string.Format("Server={0};Database=master;Trusted_Connection=True;", config["sqlserver"]["server"])); conn.Open(); var logBackups = new List <string>(); try { // Create the database and insert some data conn.Execute("create database " + dbName); conn.Execute("alter database " + dbName + " set recovery full"); conn.ChangeDatabase(dbName); conn.Execute("create table test1 (test1 varchar(50))"); conn.Execute("insert into test1 values ('data_full')"); // Backup the database var backupManager = new BackupManager(); var backupResult = backupManager.Backup(new string[2] { "full", testConfigFileName }); Assert.AreEqual(0, backupResult.ReturnCode); Assert.IsFalse(File.Exists(backupResult.BackupName)); // Do incremental stuff for (int i = 1; i <= 3; i++) { // The minimum increment is a second between log backups System.Threading.Thread.Sleep(1000); conn.Execute(string.Format("insert into test1 values ('data_log_{0}')", i)); var backupLogResult = backupManager.Backup(new string[2] { "incremental", testConfigFileName }); Assert.AreEqual(0, backupLogResult.ReturnCode); Assert.IsFalse(File.Exists(backupLogResult.BackupName)); logBackups.Add(backupLogResult.BackupName); } // Drop the database conn.ChangeDatabase("master"); conn.Execute("drop database " + dbName); // Restore the backup using the restore manager var restoreMan = new RestoreManager(); var argList = new List <string>() { testConfigFileName, backupResult.BackupName }; argList.AddRange(logBackups); restoreMan.Restore(argList.ToArray()); // Verify that the restore worked conn.ChangeDatabase(dbName); var result = conn.Query("select test1 from test1 where test1 in ('data_full', 'data_log_1', 'data_log_2', 'data_log_3')"); Assert.IsTrue(result.Count() == 4); } finally { // Cleanup our mess // S3 var awsProfile = config["aws"]["profile"]; Amazon.Util.ProfileManager.RegisterProfile(awsProfile, config["aws"]["access_key"], config["aws"]["secret_key"]); var creds = Amazon.Util.ProfileManager.GetAWSCredentials(awsProfile); var awsClient = new AmazonS3Client(creds, Amazon.RegionEndpoint.USEast1); var listReq = new ListObjectsRequest() { BucketName = config["aws"]["bucket"] }; var objects = awsClient.ListObjects(listReq); foreach (var obj in objects.S3Objects) { if (obj.Key.IndexOf(dbName) != -1) { var delReq = new DeleteObjectRequest() { BucketName = config["aws"]["bucket"], Key = obj.Key }; awsClient.DeleteObject(delReq); } } // Testing database server conn.ChangeDatabase("master"); conn.Execute("drop database " + dbName); if (File.Exists(backupFile)) { File.Delete(backupFile); } } }
protected void Button1_Click(object sender, EventArgs e) { string strcon = @"Server=LAPTOP-68A09JRH ;Initial Catalog=Agriculture; Integrated Security=true;"; SqlConnection con = new SqlConnection(strcon); con.Open(); SqlCommand sqlCmd = new SqlCommand("Login", con); sqlCmd.CommandType = CommandType.StoredProcedure; sqlCmd.Parameters.AddWithValue("@Email", TextBox1.Text.Trim()); sqlCmd.Parameters.AddWithValue("@Password", TextBox2.Text.Trim()); sqlCmd.ExecuteNonQuery(); SqlDataReader rd = sqlCmd.ExecuteReader(); if (rd.HasRows) { rd.Close(); if (gender.SelectedItem.Value == "Buyer") { SqlCommand sqlCmd1 = new SqlCommand("getUserid", con); sqlCmd1.CommandType = CommandType.StoredProcedure; sqlCmd1.Parameters.AddWithValue("@Email", TextBox1.Text.Trim()); sqlCmd1.Parameters.AddWithValue("@Type", "Buyer"); sqlCmd1.ExecuteNonQuery(); SqlDataReader rd1 = sqlCmd1.ExecuteReader(); if (rd1.HasRows) { rd1.Read(); Application["name1"] = rd1["UserId"].ToString(); Response.Redirect("Buyer.aspx"); } else { Label4.Text = "Email does not exists as a buyer"; Label4.Visible = true; HyperLink2.Visible = true; } } else if (gender.SelectedItem.Value == "Seller") { SqlCommand sqlCmd1 = new SqlCommand("getUserid", con); sqlCmd1.CommandType = CommandType.StoredProcedure; sqlCmd1.Parameters.AddWithValue("@Email", TextBox1.Text.Trim()); sqlCmd1.Parameters.AddWithValue("@Type", "Seller"); sqlCmd1.ExecuteNonQuery(); SqlDataReader rd1 = sqlCmd1.ExecuteReader(); if (rd1.HasRows) { rd1.Read(); Application["name1"] = rd1["UserId"].ToString(); Response.Redirect("Seller.aspx"); } else { Label4.Text = "Email does not exists as a seller"; Label4.Visible = true; HyperLink2.Visible = true; } } else { Label4.Text = "Error"; Label4.Visible = true; } } else { // Label3.Visible = true; Label4.Text = "Invalid username or password."; Label4.Visible = true; } }
public Personnel() { this.sqlConn = DBConnection.JSqlDBConn; }
public ExecuteQuery(string configNameOfConnectionString = "dblocal") { _connection = new SqlConnection(); _connection.ConnectionString = ConfigurationManager.ConnectionStrings[configNameOfConnectionString].ConnectionString; _da = new SqlDataAdapter(); }
private static void PrepareProcedureCommand(string ProcedureName, SqlCommand salesCommand, SqlConnection conn) { salesCommand.Connection = conn; salesCommand.CommandText = ProcedureName; salesCommand.CommandType = CommandType.StoredProcedure; conn.Open(); }
public static СправочникиВыборка.Склады ИерархияВыбратьПоСсылке(Guid одитель, int ежим, int Первые, Guid Мин, Guid Макс) { using (var Подключение = new SqlConnection(СтрокаСоединения)) { Подключение.Open(); using (var Команда = Подключение.CreateCommand()) { Команда.CommandText = string.Format(@"Select top {0} _IDRRef [Ссылка] ,_Version [Версия] ,_Marked [ПометкаУдаления] ,_IsMetadata [Предопределенный] ,_ParentIDRRef [Родитель] ,_Folder [ЭтоГруппа] ,_Code [Код] ,_Description [Наименование] ,_Fld1744 [Комментарий] ,_Fld1745RRef [ТипЦенРозничнойТорговли] ,_Fld1746RRef [Подразделение] ,_Fld1747RRef [ВидСклада] ,_Fld1748 [НомерСекции] ,_Fld1749 [РасчетРозничныхЦенПоТорговойНаценке] From _Reference147(NOLOCK) Where _IDRRef between @Мин and @Макс -- and _Folder = 0x01 AND _ParentIDRRef = @Родитель Order by _IDRRef" , Первые); Команда.Parameters.AddWithValue("Родитель", одитель); Команда.Parameters.AddWithValue("Мин", Мин); Команда.Parameters.AddWithValue("Макс", Макс); var Выборка = new V82.СправочникиВыборка.Склады(); using (var Читалка = Команда.ExecuteReader()) { while (Читалка.Read()) { var Ссылка = new СправочникиСсылка.Склады(); //ToDo: Читать нужно через GetValues() Ссылка.Ссылка = new Guid((byte[])Читалка.GetValue(0)); var ПотокВерсии = ((byte[])Читалка.GetValue(1)); Array.Reverse(ПотокВерсии); Ссылка.Версия = BitConverter.ToInt64(ПотокВерсии, 0); Ссылка.ВерсияДанных = Convert.ToBase64String(ПотокВерсии); Ссылка.ПометкаУдаления = ((byte[])Читалка.GetValue(2))[0] == 1; Ссылка.Предопределенный = ((byte[])Читалка.GetValue(3))[0] == 1; Ссылка.одитель = V82.СправочникиСсылка.Склады.ВзятьИзКэша((byte[])Читалка.GetValue(4)); Ссылка.ЭтоГруппа = ((byte[])Читалка.GetValue(5))[0] == 0; Ссылка.Код = Читалка.GetString(6); Ссылка.Наименование = Читалка.GetString(7); if (!Ссылка.ЭтоГруппа) { Ссылка.Комментарий = Читалка.GetString(8); Ссылка.ТипЦенРозничнойТорговли = V82.СправочникиСсылка.ТипыЦенНоменклатуры.ВзятьИзКэша((byte[])Читалка.GetValue(9)); Ссылка.Подразделение = V82.СправочникиСсылка.Подразделения.ВзятьИзКэша((byte[])Читалка.GetValue(10)); Ссылка.ВидСклада = V82.Перечисления /*Ссылка*/.ВидыСкладов.ПустаяСсылка.Получить((byte[])Читалка.GetValue(11)); Ссылка.НомерСекции = Читалка.GetDecimal(12); Ссылка.асчетРозничныхЦенПоТорговойНаценке = ((byte[])Читалка.GetValue(13))[0] == 1; } Выборка.Add(Ссылка); } return(Выборка); } } } }
private void btn_Dangnhap_Click(object sender, EventArgs e) { selectedCNValue = (int)cbo_Chinhanh.SelectedValue; using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); using (SqlCommand cmd = new SqlCommand("spDangnhap", connection)) { cmd.CommandType = CommandType.StoredProcedure; SqlParameter email = new SqlParameter("@email", SqlDbType.VarChar); email.Direction = ParameterDirection.Input; email.Value = txt_email.Text; cmd.Parameters.Add(email); SqlParameter matkhau = new SqlParameter("@matkhau", SqlDbType.VarChar); matkhau.Direction = ParameterDirection.Input; matkhau.Value = txt_matkhau.Text; cmd.Parameters.Add(matkhau); cmd.Parameters.Add(new SqlParameter("@macuahang", selectedCNValue)); using (SqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { if (reader.IsDBNull(reader.GetOrdinal("MaCuaHang"))) { Thongtindangnhap.MaCH = 0; } else { Thongtindangnhap.MaCH = (int)reader["MaCuaHang"]; } Thongtindangnhap.TenNV = reader["Ten"].ToString(); Thongtindangnhap.role = (int)reader["ChucVu"]; Thongtindangnhap.MaNV=(int)reader["MaNV"]; } if (Thongtindangnhap.TenNV == null) { MessageBox.Show("Đăng nhập thất bại"); } else { DangNhapNV.Thongtindangnhap.dnnv = true; Form chucnang = new HuongvietChucNang(); chucnang.Show(); } } } } }
public ExecuteQuery() { _connection = new SqlConnection(); _connection.ConnectionString = ConfigurationManager.ConnectionStrings["dblocal"].ConnectionString; _da = new SqlDataAdapter(); }
/// <summary> /// 用于执行数据库transcation,需要把相关的connection传入 /// </summary> public static void TransExecuteNonQueryReturnPKID(string ProcedureName, SqlCommand cmd, SqlConnection conn, SqlTransaction trans, out int PKID) { PrepareTransSqlCommand(ProcedureName, cmd, conn, trans); cmd.ExecuteNonQuery(); PKID = Convert.ToInt32(cmd.Parameters["@PKID"].Value); cmd.Parameters.Clear(); }
public void BasicFullBackupScenario1() { var dbName = "basic_scenario_one"; var backupFile = ""; var testConfigFileName = tmpFolder + "BasicScenario1.ini"; var parser = new FileIniDataParser(); var config = parser.ReadFile(defaultConfigIni); config["sqlserver"].SetKeyData(new KeyData("database") { Value = dbName }); parser.WriteFile(testConfigFileName, config); var conn = new SqlConnection(string.Format("Server={0};Database=master;Trusted_Connection=True;", config["sqlserver"]["server"])); conn.Open(); try { // Create the database and insert some data conn.Execute("create database " + dbName); conn.ChangeDatabase(dbName); conn.Execute("create table test1 (test1 varchar(50))"); conn.Execute("insert into test1 values ('data')"); // Backup the database var backupManager = new BackupManager(); var backupResult = backupManager.Backup(new string[2] { "full", testConfigFileName }); Assert.AreEqual(0, backupResult.ReturnCode); Assert.IsFalse(File.Exists(backupFile)); // Drop the database conn.ChangeDatabase("master"); conn.Execute("drop database " + dbName); // Restore the backup from S3 var restoreMan = new RestoreManager(); restoreMan.Restore(new string[2] { testConfigFileName, backupResult.BackupName }); var awsProfile = config["aws"]["profile"]; Amazon.Util.ProfileManager.RegisterProfile(awsProfile, config["aws"]["access_key"], config["aws"]["secret_key"]); var creds = Amazon.Util.ProfileManager.GetAWSCredentials(awsProfile); var awsClient = new AmazonS3Client(creds, Amazon.RegionEndpoint.USEast1); conn.ChangeDatabase(dbName); var result = conn.Query("select test1 from test1 where test1 = 'data'"); Assert.IsTrue(result.Count() == 1); var listReq = new ListObjectsRequest() { BucketName = config["aws"]["bucket"] }; var objects = awsClient.ListObjects(listReq); foreach (var obj in objects.S3Objects) { if (obj.Key.IndexOf(dbName) != -1) { var delReq = new DeleteObjectRequest() { BucketName = config["aws"]["bucket"], Key = obj.Key }; awsClient.DeleteObject(delReq); } } } finally { conn.ChangeDatabase("master"); conn.Execute("drop database " + dbName); if (File.Exists(backupFile)) { File.Delete(backupFile); } } }