public RequestsProcessedByEnum GetLeaveStatus(int leaveRequestId) { SqlConnection conn = null; SqlCommand cmd = null; try { conn = DALHelper.CreateSqlDbConnection(); cmd = new SqlCommand("usp_GetLeaveStatus", conn); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@LeaveRequestId", leaveRequestId); if (cmd.ExecuteScalar() != DBNull.Value) { return (RequestsProcessedByEnum)Convert.ToInt32(cmd.ExecuteScalar()); } else { return RequestsProcessedByEnum.NotDefined; } } catch (Exception) { throw; } finally { conn.Close(); cmd.Dispose(); conn.Dispose(); } }
protected void Tmbtn_Click(object sender, EventArgs e) { SqlCommand cmd = new SqlCommand("select MAX(wjh) from wj", cn); cn.Open(); cmd.ExecuteScalar(); //cn.Close(); SqlCommand com = new SqlCommand("INSERT INTO Tm(Wjh,Tm,Tixing) VALUES (" +cmd.ExecuteScalar()+ ",'" + Tmtxt.Text.Trim() + "','" + TXDropDownList.Text.Trim() + "')", cn); //com.ExecuteNonQuery(); //cn.Close(); try { //cn.Open(); int val = com.ExecuteNonQuery(); cn.Close(); this.NRListBox.Items.Add(this.Tmtxt.Text); if (val <= 0) ClientScript.RegisterStartupScript(this.GetType(), "", "alert('插入数据失败!')"); else ClientScript.RegisterStartupScript(this.GetType(), "", "alert('插入数据成功!')"); } //捕获异常 catch (Exception exp) { //处理异常....... ClientScript.RegisterStartupScript(this.GetType(), "", "alert('插入数据失败! 详情:" + exp.Message + "')"); } }
protected void Button3_Click(object sender, EventArgs e) { String s = ConfigurationManager.ConnectionStrings["MediDB"].ConnectionString; SqlConnection con = new SqlConnection(s); SqlCommand cmd = new SqlCommand(" Select Count(*) from Admin where name='" + TextBox1.Text + "' and Password='******'", con); con.Open(); int m = (int)cmd.ExecuteScalar(); if (m == 1) { Button1.Enabled = true ; Button2.Enabled = true ; Button4.Enabled = true ; Button5.Enabled = true ; } else { Response.Write("<Script>alert('Name or the password entered by you is incorrect. please try again!!')</Script>"); } cmd.ExecuteScalar(); con.Close(); TextBox1.Text = ""; TextBox2.Text = ""; }
public List<TableInfo> LoadTableList(string databaseConnectionString) { List<TableInfo> tables; using (var conn = new SqlConnection(databaseConnectionString)) { conn.Open(); var cmd = new SqlCommand(SqlQueries.QUERY_ALL_TABLES, conn); using (var reader = cmd.ExecuteReader()) { tables = new List<TableInfo>(); while (reader.Read()) { tables.Add(new TableInfo(reader.GetString(0), reader.GetString(1))); } } int tableCount = 0; foreach (var tableInfo in tables) { cmd = new SqlCommand(string.Format(SqlQueries.QUERY_DATA_SIZE, tableInfo.Name, tableInfo.Schema), conn); cmd.CommandTimeout = 60000; tableInfo.DataSizeBytes = (double) cmd.ExecuteScalar(); cmd.CommandText = string.Format(SqlQueries.QUERY_INDEX_SIZE, tableInfo.Name, tableInfo.Schema); tableInfo.IndexSizeBytes = (double) cmd.ExecuteScalar(); cmd.CommandText = string.Format(SqlQueries.QUERY_ROW_COUNT, tableInfo.Name, tableInfo.Schema); tableInfo.RowCount = (long) cmd.ExecuteScalar(); OnTableLoadProgressChanged(new ProgressEventArgs(tableCount++*100 / tables.Count)); } } return tables; }
public string generatestudentcode() { string regcode = ""; string query = "select RegistrationCode from StudentPersonalInformation where ID =(select Max(ID) from StudentPersonalInformation)"; try { SqlCommand cmd = new SqlCommand(query, con); if (con.State == ConnectionState.Closed) con.Open(); if (cmd.ExecuteScalar() != null) { //regcode = cmd.ExecuteScalar().ToString(); //regcode= regcode.Substring(0,regcode.LastIndexOf('/')-1) + Convert.ToInt32(regcode.Substring(regcode.LastIndexOf('/'), (regcode.Length - (regcode.LastIndexOf('/'))) + 1)) +1; regcode = (Convert.ToInt32(cmd.ExecuteScalar()) + 1).ToString(); } else regcode = "1"; // regcode = "ASTM/" + DropDownListCenter.SelectedValue + "/" + DateTime.Now.Year + "/1"; // regcode = "ASTM/" + DropDownListCenter.Items[0].Text + "/" + DropDownListCourse.SelectedValue + "/" + DateTime.Now.Year + "/1"; } catch (Exception ex) { } finally { con.Close(); } return regcode; }
public static int? getMax() { int? id = null; string conexionCadena = ConfigurationManager.ConnectionStrings["ConexionComplejo"].ConnectionString; SqlConnection con = new SqlConnection(); try { con.ConnectionString = conexionCadena; con.Open(); string sql = "SELECT MAX(id_fact) from facturas"; SqlCommand cmd = new SqlCommand(); cmd.CommandText = sql; cmd.Connection = con; if(!cmd.ExecuteScalar().Equals(null)) { id =(int) cmd.ExecuteScalar(); } else { id = null; } } catch (SqlException ex) { throw new ApplicationException("Error al traer max id cliente" + ex.Message); } finally { con.Close(); } return id; }
/// <summary> /// Authenticates the user /// </summary> /// <param name="user">user from login form</param> /// <param name="password">password from login form</param> public static bool Authenticate(String user,String password) { var positiveIntRegex = new Regex(@"^\w+$"); if (!positiveIntRegex.IsMatch(user)) { return false; } if (!positiveIntRegex.IsMatch(password)) { return false; } String encryptedPass = Encrypt(password); string constr = Settings.Default.UserDbConnectionString; SqlConnection con = new SqlConnection(constr); SqlCommand command = new SqlCommand(); command.Connection = con; command.Parameters.AddWithValue("@Username", user); command.CommandText = "SELECT Password FROM Users WHERE Name = @Username"; command.CommandType = CommandType.Text; con.Open(); string _password = ""; if (command.ExecuteScalar() != null) _password = command.ExecuteScalar().ToString(); else return false; con.Close(); if (encryptedPass.Equals(_password)) { return true; } return false; }
protected void TxtAccountName_TextChanged(object sender, EventArgs e) { try { System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString()); con.Open(); System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("select ACODE from ACCOUNTS where ANAME = '" + TxtAccountName.Text + "' and COMP_CODE = " + Session["COMP_CODE"].ToString(), con); int x = Convert.ToInt32(cmd.ExecuteScalar()); if (x == 0) { TxtAccountName.BackColor = Color.Red; } else { HfACODE.Value = cmd.ExecuteScalar().ToString(); TxtAccountName.BackColor = Color.White; con.Close(); getAccountGroupName(); FillProductDetailsGrid(); } } catch (Exception) { throw; } }
protected void login_Click(object sender, EventArgs e) { SqlConnection con = new SqlConnection("server=(localdb)\\v11.0;Initial Catalog=WebApplication2;Integrated Security=true"); con.Open(); string str = "select count(*) from Users where userName='******'"; SqlCommand command = new SqlCommand(str, con); int temp = Convert.ToInt32(command.ExecuteScalar().ToString()); if (temp == 1) { string str2 = "select password from Users where userName='******'"; SqlCommand command2 = new SqlCommand(str2, con); string tempPass = command2.ExecuteScalar().ToString().ToLower().Trim(); if (tempPass==pass.Text.ToLower().Trim()) { string str3 = "select user_id from Users where userName='******'"; SqlCommand command3 = new SqlCommand(str3, con); temp = Convert.ToInt32(command.ExecuteScalar().ToString()); con.Close(); Session["new"] = temp; Response.Redirect("Home.aspx"); } else { Label1.Visible = true; Label1.Text = "Wrong password!"; } } else { Label1.Visible = true; Label1.Text = "Invalid user name!"; } }
public static string ExecuteScalar(string strSql) { try { iniCon(); SqlConnection sCon=new SqlConnection(m_strCon); sCon.Open(); SqlCommand sCmd=new SqlCommand(); sCmd.Connection =sCon ; sCmd.CommandText =strSql; if(sCmd.ExecuteScalar()!=null) { string strRet=sCmd.ExecuteScalar().ToString(); sCon.Close(); return strRet; } else { sCon.Close(); return ""; } }catch(Exception ex) { MessageBox.Show(ex.ToString()); Application.Exit(); //MessageBox.Show(ex.ToString()); return ""; } }
void GetCurrentMembership() { string userID = Session["UserID"].ToString(); if (CheckMembership()) { using (SqlConnection con = new SqlConnection(Helper.GetCon())) using (SqlCommand cmd = new SqlCommand()) { con.Open(); cmd.Connection = con; cmd.CommandText = "SELECT SUM(Length) FROM Memberships INNER JOIN Payments ON " + "Memberships.MembershipID=Payments.MembershipID WHERE UserID=@UserID " + "AND MembershipStatus='Active' AND PaymentStatus='Paid'"; cmd.Parameters.AddWithValue("@UserID", userID); int totalYears = (int)cmd.ExecuteScalar(); cmd.CommandText = "SELECT TOP 1 StartDate FROM Memberships INNER JOIN Payments ON " + "Memberships.MembershipID=Payments.MembershipID WHERE UserID=@UserID " + "AND MembershipStatus='Active' AND PaymentStatus='Paid'"; cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("@UserID", userID); DateTime endDate = (DateTime)cmd.ExecuteScalar(); DateTime totalEndDate = endDate.AddYears(totalYears); txtEndDate.Text = totalEndDate.ToString("D"); } } else { txtEndDate.Text = "N/A"; nomem.Visible = true; btnDisable.Visible = false; btnEnable.Visible = false; } }
/// <summary> /// Check Employee whether exist /// </summary> /// <param name="employeeId"></param> /// <returns></returns> public bool IsEmployeeIdExist(string employeeId) { using (SqlConnection conn = new SqlConnection(DBHelper.GetConnection())) { SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandText = @"select EmployeeId from dbo.Employee where EmployeeId = @EmployeeId"; cmd.Parameters.AddWithValue("@EmployeeId", employeeId); bool result = false; try { conn.Open(); cmd.ExecuteScalar(); if (cmd.ExecuteScalar() != null) { result = true; } } catch (Exception ex) { } finally { cmd.Dispose(); conn.Close(); } return result; } }
/// <summary> /// This method Returns the object after executing the execute scalar method /// </summary> /// <param name="cmdClass"></param> /// <param name="objExp"></param> public int ExecuteScalar(SqlCommand cmdClass, out Object obj, out Exception objExp) { int intStatus = 0; obj = null; objExp = null; try { SqlConnection conClass = new SqlConnection(ConfigurationManager.ConnectionStrings["Con"].ConnectionString); cmdClass.Connection = conClass; if (conClass.State == ConnectionState.Closed) conClass.Open(); if (cmdClass.ExecuteScalar() != DBNull.Value) obj = cmdClass.ExecuteScalar(); else obj = null; conClass.Close(); conClass.Dispose(); cmdClass.Dispose(); if (obj != null) intStatus = 1; } catch (Exception exp) { objExp = exp; } return (intStatus); }
public CanUploadStatus CanUpload(string filename, int size) { bool extAllowed = FileExtensionAllowed(filename); if (!extAllowed) return CanUploadStatus.FileTypeNotAllowed; else { int uploaded = 0; using (var dbContext = _dbService.GetDatabaseContext(false)) { SqlCommand cmd = new SqlCommand(@" select SUM(f.Size) from [File] f inner join [File_User_Access] as fua ON fua.lid = f.Id inner join [User] u on u.Id = fua.rid where u.Id = @userId", dbContext.Connection); cmd.Parameters.AddWithValue("userId", _securityService.CurrentUser.Id); var raw = cmd.ExecuteScalar(); if (raw != DBNull.Value) uploaded = (int)cmd.ExecuteScalar(); else uploaded = 0; } if (uploaded > _securityService.CurrentUser.GetData<int>("DiskUsageLimit")) return CanUploadStatus.DiskUsageLimitExceeded; else return CanUploadStatus.Yes; } }
public int GetInformationAssistantCount(string employeeId) { using (SqlConnection conn = new SqlConnection(DBHelper.GetConnection())) { SqlCommand cmd = new SqlCommand(); int result = 0; cmd.Connection = conn; cmd.CommandText = @"select count(*) as InformationAssistantCount, e.Name as EmployeeName from Employee e ,InformationAssistant i where e.EmployeeId = i.EmployeeId and e.EmployeeId = @EmployeeId and CONVERT(varchar(100), i.RecordDate, 111)=CONVERT(varchar(100), getdate(), 111) group by e.Name"; cmd.Parameters.AddWithValue("@EmployeeId", employeeId); try { conn.Open(); result = DBNull.Value == cmd.ExecuteScalar() ? 0 : Convert.ToInt32(cmd.ExecuteScalar()); } catch (Exception ex) { throw ex; } finally { conn.Close(); cmd.Dispose(); } return result; } }
public static bool Grabar(Entidades.Distrito pEntidad) { using (var cn = new SqlConnection(conexion.LeerCC)) { // Contamos cuantos distritos existen segun el coddistrito o nomdistrito using (var cmd = new SqlCommand(@"select isnull(count(coddistrito),0) from distritos where coddistrito=@cod or nomdistrito=@nom", cn)) { cmd.Parameters.AddWithValue("cod", pEntidad.coddistrito); cmd.Parameters.AddWithValue("nom", pEntidad.nomdistrito); cn.Open(); // Ejecutamos el comando y verificamos si el resultado es mayor a cero actualizar, caso contrario insertar if (Convert.ToInt32(cmd.ExecuteScalar()) > 0) { // Si es mayor a cero, quiere decir que existe al menos un registro con los datos ingresados // Entonces antes de actualizar, hacer las siguientes comprobaciones if (pEntidad.coddistrito == 0) throw new Exception("El distrito ya esta registrado en el sistema, verifique los datos por favor!..."); // Verifica si ya existe un registro con el mismo nombre del distrito cmd.CommandText = @"select isnull(count(coddistrito),0) from distritos where coddistrito<>@cod and nomdistrito=@nom"; if (Convert.ToInt32(cmd.ExecuteScalar()) > 0) throw new Exception("No se puede grabar un valor duplicado, verifique los datos por favor!..."); // Si las comprobaciones anteriores resultaron ser falsa, entonces actualizar cmd.CommandText = @"update distritos set nomdistrito=@nom where coddistrito=@cod"; } else cmd.CommandText = @"insert into distritos (nomdistrito) values (@nom)"; // Ejecutamos el comando que puede ser para update o insert return Convert.ToBoolean(cmd.ExecuteNonQuery()); } } }
protected void Button1_Click(object sender, EventArgs e) { SqlCommand cmd=new SqlCommand("select password from consumer where email='"+TextBox1.Text+"'",con); con.Open(); if (cmd.ExecuteScalar()!=null) { string s = cmd.ExecuteScalar().ToString(); MailMessage mail = new MailMessage(); mail.To.Add(TextBox1.Text); mail.From = new MailAddress("*****@*****.**"); mail.Subject = "Remember Mail"; string Body = "Password is " + s; mail.Body = Body; SmtpClient smtp = new SmtpClient(); smtp.Host = "smtp.gmail.com"; smtp.Port = 587; smtp.UseDefaultCredentials = false; smtp.Credentials = new System.Net.NetworkCredential("*****@*****.**", "9232663223"); smtp.EnableSsl = true; smtp.Send(mail); Label1.Text = "PASSWORD SENT TO YOUR EMAIL ADDRESS"; con.Close(); } else Label1.Text = "No such email exists "; }
protected void btnEdit_Click(object sender, EventArgs e) { string connectionstring = WebConfigurationManager.ConnectionStrings["SCSDataBase"].ConnectionString; SqlConnection con = new SqlConnection(connectionstring); try { con.Open(); SqlCommand cmd = new SqlCommand("exec getUserControlledPupilId @lg", con); cmd.Parameters.Add("@lg", login); string pId = ((int)cmd.ExecuteScalar()).ToString(); cmd.CommandText = "exec getUserAccess @lg"; int access = (int)cmd.ExecuteScalar(); if (access >= 2) { cmd.CommandText = "exec getPupilFLF @pid"; cmd.Parameters.Add("@pid", pId); string flf = (string)cmd.ExecuteScalar(); con.Close(); Session["SCSTitle"] = flf; Session["SCSParameter1"] = pId; Response.Redirect("Redactor.aspx"); } else { Response.Write("<div align=center>Вашего уровня допуска недостаточно для этой операции <BR> <a href='ChangeAccess.aspx'>Как изменить уровень допуска?</A> " + "<BR>Ваш текущий уровень допуска : " + access.ToString() + " Требуемый : 2</div>"); } } catch (Exception err) { Response.Write(err.Message); } }
/// <summary> /// Attempts to purchase a movie for a user /// </summary> /// <param name="movieId">The movie to purchase</param> /// <param name="userId">The user purchasing the movie</param> /// <returns>true if the movie is bought, false if the movie could not be bought (f.ex. due to insufficient funds)</returns> public bool PurchaseMovie(int movieId, int userId) { //Ensure that the id is valid for a movie SqlCommand command = new SqlCommand("SELECT id FROM Movie WHERE id=" + movieId, connection); if (command.ExecuteScalar() == null) return false; //Get the price of the movie command.CommandText = "SELECT buyPrice FROM Files WHERE id =" + movieId; Object pric = command.ExecuteScalar(); if (pric == null) return false; int price = (int)pric; //Get the balance of the user command.CommandText = "SELECT balance FROM Users WHERE id =" + userId; Object bal = command.ExecuteScalar(); if (bal == null) return false; int balance = (int)bal; if (balance - price >= 0) { //Withdraw the amount from the users balance and only continue if it is successful command.CommandText = "UPDATE Users " + "SET balance = balance - " + price + "WHERE id = " + userId; if (command.ExecuteNonQuery() > 0) { command.CommandText = "INSERT INTO UserFile " + "VALUES(" + userId + ", " + movieId + ", '" + DateTime.MaxValue.ToString("yyyy-MM-dd HH:mm:ss") + "' )"; return command.ExecuteNonQuery() > 0; } } return false; }
protected void Button1_Click(object sender, EventArgs e) { if(TextBox3.Text==TextBox4.Text && TextBox1.Text!=""&&TextBox2.Text!=""&&TextBox5.Text!="") { cmd=new SqlCommand("select did from cities where city=@city",con); con.Open(); cmd.Parameters.AddWithValue("@city",TextBox5.Text); if(cmd.ExecuteScalar()==null) Label2.Text="City not available"; else { int did=int.Parse(cmd.ExecuteScalar().ToString()); cmd=new SqlCommand("insert into distributorapplication values(@d,@did,@u,@p,@c)",con); cmd.Parameters.AddWithValue("@d",TextBox1.Text); cmd.Parameters.AddWithValue("@did",did); cmd.Parameters.AddWithValue("@u",TextBox2.Text); cmd.Parameters.AddWithValue("@p",TextBox3.Text); cmd.Parameters.AddWithValue("@c", TextBox5.Text); cmd.ExecuteNonQuery(); Label2.Text="Request for registration sent to administrator. You will be notified within few dayd"; TextBox1.Text = TextBox2.Text = TextBox5.Text = ""; } con.Close(); } else if(TextBox3.Text!=TextBox4.Text) Label2.Text="Password doesn't match"; else { Label2.Text="Please fill in all the details"; } }
public int GetCustomerId() { using (SqlConnection conn = new SqlConnection(DBHelper.GetConnection())) { int result = 0; SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandText = @"select max(CustomerId) from Customer"; try { conn.Open(); result = cmd.ExecuteScalar() == DBNull.Value ? 0 : Convert.ToInt32(cmd.ExecuteScalar()); } catch (Exception ex) { throw ex; } finally { conn.Close(); cmd.Dispose(); } return result + 1; } }
/// <summary> /// Performs a scalar select and returns the value as a string /// </summary> /// <param name="qS"></param> /// <returns></returns> public static String ScalarString(String qS) { object returnValue = ""; SqlConnection con = new SqlConnection(ConnectionString); SqlCommand cmd = new SqlCommand(qS, con); using (con) { if (con.State == ConnectionState.Open) { returnValue = cmd.ExecuteScalar(); con.Close(); } else { con.Open(); returnValue = cmd.ExecuteScalar(); con.Close(); } } if (returnValue == null) { return ""; } else return returnValue.ToString(); }
//----------------------------------- public void favoritecontrol() { try { if (Session["userid"] != null) { myconnection.Open(); SqlCommand com = new SqlCommand("SPfavoritecontrol", myconnection); com.CommandType = CommandType.StoredProcedure; com.Parameters.Add("@movieid", SqlDbType.Int); com.Parameters["@movieid"].Value = Convert.ToInt32(Request.QueryString["movieid"]); com.Parameters.Add("@userid", SqlDbType.Int); com.Parameters["@userid"].Value = Convert.ToInt32(Session["userid"]); if (com.ExecuteScalar() != null) { if (Convert.ToInt32(com.ExecuteScalar().ToString()) != 0) { LinkButtonaddtofavorites.Text = "Added to favorites"; } } } } catch (Exception ex) { Labelerror.Text = "error in controlling favorites" + ex.Message; } finally { myconnection.Close(); } }
/// <inheritdoc/> public bool CorrelativeConfigurationExists(string facilityId, string correlativeId) { if (string.IsNullOrWhiteSpace(facilityId)) return false; if (string.IsNullOrWhiteSpace(correlativeId)) return false; if (string.IsNullOrWhiteSpace(_connectionString)) return false; int count; using (var conexionSp = new SqlConnection(_connectionString)) { const string query = "SELECT COUNT(PaqueteId) FROM Comunes.CorrelativosPaquetes " + "WHERE PaqueteId = @correlativeId AND PlantaId = @facilityId"; using (var comandoSp = new SqlCommand(query, conexionSp)) { comandoSp.CommandType = CommandType.Text; comandoSp.Parameters.AddWithValue("@correlativeId", correlativeId).Direction = ParameterDirection.Input; comandoSp.Parameters.AddWithValue("@facilityId", facilityId).Direction = ParameterDirection.Input; conexionSp.Open(); count = comandoSp.ExecuteScalar() is int ? (int)comandoSp.ExecuteScalar() : 0; conexionSp.Close(); } } return count > 0; }
protected void btnAccept_Click(object sender, EventArgs e) { string constr = WebConfigurationManager.ConnectionStrings["SCSDataBaseAdminLogin"].ConnectionString; constr = constr.Replace("ID=admin;Password=;", "ID=SCSAdmin;Password="******";"); SqlConnection con = new SqlConnection(constr); try { con.Open(); SqlCommand cmd = new SqlCommand("exec ChangeAccess @lg, @na", con); cmd.Parameters.Add("@lg", lblLogin.Text); cmd.Parameters.Add("@na", Convert.ToInt32(lblNeedAccess.Text)); cmd.ExecuteScalar(); cmd.CommandText = "exec CreateNewMessage @lgself, @lg, 'Запрос выполнен'"; cmd.Parameters.Add("@lgself", login); cmd.ExecuteScalar(); con.Close(); Response.Redirect("Cabinet.aspx"); } catch (Exception err) { //Response.Clear(); Response.Write(err.Message); } finally { con.Close(); } }
public static void insertarReserva(Reserva res, DetalleReserva det) { string connStr = ConfigurationManager.ConnectionStrings["ConexionComplejo"].ConnectionString; SqlConnection cn = new SqlConnection(); SqlTransaction tran = null; try { cn.ConnectionString = connStr; cn.Open(); tran = cn.BeginTransaction(); string sql = "INSERT INTO RESERVAS (fecha_res,cli_id,estado,monto) values (@FechaRes,@Cliente,@Estado,@Monto); SELECT @@Identity as ID;"; SqlCommand cmd = new SqlCommand(); cmd.CommandText = sql; cmd.Connection = cn; cmd.Transaction = tran; cmd.Parameters.AddWithValue("@FechaRes",res.Fecha); cmd.Parameters.AddWithValue("@Cliente", res.Cli.IdCliente); cmd.Parameters.AddWithValue("@Estado", res.Estado); cmd.Parameters.AddWithValue("@Monto", res.Monto); res.Id = Convert.ToInt32(cmd.ExecuteScalar()); //int idCabania = Convert.ToInt32(cmd.ExecuteScalar()); //Recupero Id de a cabania insertada det.Res.Id = res.Id; string sql2 = "INSERT INTO DETALLE_RESERVA (res_id,cab_id,precio,subtotal,fecha_desde,fecha_hasta,cant_dias,facturada) values (@ResId, @CabId,@Precio,@Subtotal,@FechaDesde,@FechaHasta,@CantDias,@Facturada); SELECT @@Identity as ID;"; cmd.CommandText = sql2; cmd.Parameters.AddWithValue("@ResId", det.Res.Id); cmd.Parameters.AddWithValue("@CabId", det.Cab.IdCabania); cmd.Parameters.AddWithValue("@Precio", det.Precio); cmd.Parameters.AddWithValue("@Subtotal", det.Subtotal); cmd.Parameters.AddWithValue("@FechaDesde", det.FechaDesde); cmd.Parameters.AddWithValue("@FechaHasta", det.FechaHasta); cmd.Parameters.AddWithValue("@CantDias", det.CantDias); cmd.Parameters.AddWithValue("@Facturada", det.Facturada); det.Id = Convert.ToInt32(cmd.ExecuteScalar()); tran.Commit(); //cabania.IdCabania = idCabania; //Seteo Id de la cabania insertada } catch (SqlException ex) { if (cn.State == ConnectionState.Open) tran.Rollback(); //Vuelvo atras los cambios throw new ApplicationException("Error al guardar la cabaña." + ex.Message); } finally { if (cn.State == ConnectionState.Open) cn.Close(); } }
public string Scalar(string sqlstr) { string msg = ""; object result = ""; try { if (CONN.State != ConnectionState.Open) { CONN.Open(); } CMD.Connection = CONN; CMD.CommandText = sqlstr; CMD.CommandType = CommandType.Text; result = CMD.ExecuteScalar(); CONN.Close(); } catch (Exception ex) { result = ""; msg = ex.Message; } finally { if (CONN.State == ConnectionState.Open) { CONN.Close(); } } try //log用另外的try防止檔案被咬 { if (msg != "") { WriteCMD(); WriteLog("[" + DateTime.Now.ToLongTimeString() + "][Main.Scalar.Error]:" + msg + sqlstr); } else { if (LogStat >= 1) { WriteCMD(); } WriteLogselect(sqlstr); } } catch { } if (result == null) { return(""); } else { return(result.ToString()); } }
private void CreateCapacities(int id, int userId, System.Data.SqlClient.SqlCommand command) { List <int> capacities = new List <int>(); // get capacities to create that not already exists string sql = "SELECT PORProductClassCapacity.ProductCapacityId as CapacityId " + "FROM PORProductClassCapacity WITH (NOLOCK) LEFT OUTER JOIN " + "PORCapacity WITH (NOLOCK) ON PORProductClassCapacity.PORId = PORCapacity.PORId AND PORProductClassCapacity.ProductCapacityId = PORCapacity.CapacityId " + "WHERE (PORCapacity.CapacityId IS NULL) AND (PORProductClassCapacity.PORId = " + id.ToString() + ")"; command.CommandText = sql; System.Data.SqlClient.SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { capacities.Add(reader.GetInt32(0)); } reader.Close(); foreach (int capacity in capacities) { sql = "insert into PORCapacity (PORId, CapacityID) values (" + id + ", " + capacity + "); SELECT IDENT_CURRENT('[PORCapacity]') AS ID"; command.CommandText = sql; int PORCapacityId = Convert.ToInt32(command.ExecuteScalar()); sql = "insert into durados_ChangeHistory (ViewName, PK, ActionId, UpdateUserId) values ('v_PORCapacity', '" + PORCapacityId + "', 1, " + userId + ")"; command.CommandText = sql; command.ExecuteNonQuery(); sql = "insert into PMM (PORCapacityId) values (" + PORCapacityId + "); SELECT IDENT_CURRENT('[PMM]') AS ID"; command.CommandText = sql; int PMMId = Convert.ToInt32(command.ExecuteScalar()); sql = "insert into durados_ChangeHistory (ViewName, PK, ActionId, UpdateUserId) values ('v_SamplesRequest', '" + PMMId + "', 1, " + userId + ")"; command.CommandText = sql; command.ExecuteNonQuery(); sql = "insert into PLM (PORCapacityId,PLMBEStatusId) values (" + PORCapacityId + ",5); SELECT IDENT_CURRENT('[PLM]') AS ID"; command.CommandText = sql; int PLMId = Convert.ToInt32(command.ExecuteScalar()); sql = "insert into durados_ChangeHistory (ViewName, PK, ActionId, UpdateUserId) values ('v_PLM', '" + PLMId + "', 1, " + userId + ")"; command.CommandText = sql; command.ExecuteNonQuery(); CreatePLMParameterModes(PLMId, userId, command); sql = "insert into Test (PORCapacity) values (" + PORCapacityId + "); SELECT IDENT_CURRENT('[Test]') AS ID"; command.CommandText = sql; int TestId = Convert.ToInt32(command.ExecuteScalar()); sql = "insert into durados_ChangeHistory (ViewName, PK, ActionId, UpdateUserId) values ('v_TEST', '" + TestId + "', 1, " + userId + ")"; command.CommandText = sql; command.ExecuteNonQuery(); } }
protected void BtnNext_Click(object sender, EventArgs e) { try { con = new SqlConnection("integrated security=sspi;Server=.;Database=Jobs"); con.Open(); if (txtUname.Text == "" || txtPwd.Text=="") { lblmsg.Text = "sorry pls enter the required fileds"; } else { cmd = new SqlCommand("select count(*) from users where uname = @uname", con); cmd.Parameters.Add("@uname", SqlDbType.VarChar, 10).Value = txtUname.Text; int cnt = (int)cmd.ExecuteScalar(); if (cnt == 1) { lblmsg.Text = "Sorry! Username is already present. Try another name!"; return; } cmd.CommandText = "select count(*) from users where email = @email"; cmd.Parameters.Add("@email", SqlDbType.VarChar, 50).Value = txtEmail.Text; cnt = (int)cmd.ExecuteScalar(); if (cnt == 1) { lblmsg.Text = "Sorry! Email address is already present."; return; } //cmd = new SqlCommand("insert into Users values(@p1,@p2,@p3,@p4,@p5,@p6,@p7)", con); //cmd.Parameters.Add(new SqlParameter("@p1", txtUname.Text)); //cmd.Parameters.Add(new SqlParameter("@p2", txtPwd.Text)); //cmd.Parameters.Add(new SqlParameter("@p4", dt)); //cmd.Parameters.Add(new SqlParameter("@p3", txtEmail.Text)); //cmd.Parameters.Add(new SqlParameter("@p6", txtAddress.Text)); //cmd.Parameters.Add(new SqlParameter("@p7", txtPhone.Text)); //cmd.Parameters.Add(new SqlParameter("@p5", ddlUtype.SelectedItem.Text)); //cmd.ExecuteNonQuery(); //con.Close(); //hPwd.Text = txtPwd.Text; hPwd.Text = txtPwd.Text; MultiView1.ActiveViewIndex = ddlUtype.SelectedIndex + 1; } } catch (Exception ex) { lblmsg.Text = "Error :" + ex.Message; } finally { con.Close(); } }
private void button1_Click(object sender, EventArgs e) { //必要信息填写验证 if (textBoxXINGZHI.Text == "" || textBoxBUMEN.Text == "" || textBoxWEITUOREN.Text == "" ) { MessageBox.Show("必要信息填写不完全"); } else { try { //申请估价编号算法,从新写,你妈的 SqlConnection conn = new SqlConnection(Jingtai.ConnectionString); conn.Open();//打开链接 //创建命令对象 SqlCommand cmd = null; //创建语句字符串 string sql = null; //到 table_ku 中获取指定年份的报告编号最大的数字和ID int baogaohao; sql = @"select MAX(报告编号) from Table_Ku where 报告年份='" + textBoxNIAN.Text + "'"; cmd = new SqlCommand(sql, conn); label3.Text = cmd.ExecuteScalar().ToString(); baogaohao = int.Parse(cmd.ExecuteScalar().ToString()); //得到最大的报告号 baogaohao++; sql = @"INSERT INTO Table_Ku (报告编号申请人,报告性质,相关部门,委托人,报告年份,报告编号,备注,编号申请时间) VALUES ('" + label2.Text + "','" + textBoxXINGZHI.Text + "','" + textBoxBUMEN.Text + "','" + textBoxWEITUOREN.Text + "','" + textBoxNIAN.Text + "','" + baogaohao + "','" + textBoxBEIZHU.Text + "','" + DateTime.Now + "')"; cmd = new SqlCommand(sql, conn); cmd.ExecuteNonQuery(); sql = @"select id from Table_Ku where 报告年份='" + textBoxNIAN.Text + "' and 报告编号='" + baogaohao.ToString() + "'"; cmd = new SqlCommand(sql, conn); int myid = int.Parse(cmd.ExecuteScalar().ToString()); Jingtai.myid = myid; conn.Close();//关闭连接 Close(); } catch(Exception ex) { MessageBox.Show(ex.Message); MessageBox.Show("fenpeiyichang"); } } }
static void Main(string[] args) { var time= new DateTime(); string connectionString = GetConnectionString(); // Open a connection to the AdventureWorks database. using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); // Perform an initial count on the destination table. SqlCommand commandRowCount = new SqlCommand( "SELECT COUNT(*) FROM " + "dbo.BulkCopyDemoMatchingColumns;", connection); long countStart = System.Convert.ToInt32( commandRowCount.ExecuteScalar()); Console.WriteLine("Starting row count = {0}", countStart); // Create a table with some rows. DataTable newProducts = MakeTable(); // Create the SqlBulkCopy object. // Note that the column positions in the source DataTable // match the column positions in the destination table so // there is no need to map columns. using (var bulkCopy = new System.Data.SqlClient.SqlBulkCopy(connection)) { bulkCopy.DestinationTableName = "dbo.BulkCopyDemoMatchingColumns"; try { // Write from the source to the destination. var b = DateTime.Now.Second; bulkCopy.WriteToServer(newProducts); var a = DateTime.Now.Second; Console.WriteLine("bat dau:"+b); Console.WriteLine("Tổng thời gian Lưu:"+a); } catch (Exception ex) { Console.WriteLine(ex.Message); } } // Perform a final count on the destination // table to see how many rows were added. long countEnd = System.Convert.ToInt32( commandRowCount.ExecuteScalar()); Console.WriteLine("Ending row count = {0}", countEnd); Console.WriteLine("{0} rows were added.", countEnd - countStart); Console.WriteLine("Press Enter to finish."); Console.ReadLine(); } }
public int ktra(string sql) { SqlConnection conn = new SqlConnection(kn); SqlCommand cmd = new SqlCommand(sql, conn); conn.Open(); cmd.ExecuteScalar(); int i =(int)cmd.ExecuteScalar(); conn.Close(); return i; }
protected void Page_Load(object sender, EventArgs e) { if ((Session["SCSLogin"] != null) & (Session["SCSDate"] != null)) { if ((DateTime)Session["SCSDate"] <= DateTime.Now) Response.Redirect("Default.aspx"); login = (string)Session["SCSLogin"]; expDate = ((DateTime)Session["SCSDate"]).ToString(); Response.Write("<div align=center> В системе как: " + login + "<BR> В системе до: " + expDate + "<BR></div>"); Session["SCSDate"] = DateTime.Now.AddMinutes(10); Title = Title + " : " + login; } else Response.Redirect("Default.aspx"); string constr = WebConfigurationManager.ConnectionStrings["SCSDataBase"].ConnectionString; SqlConnection con = new SqlConnection(constr); try { con.Open(); SqlCommand cmd = new SqlCommand("exec ShowNewMessages @lg", con); cmd.Parameters.Add("@lg", login); SqlDataReader dr = cmd.ExecuteReader(); gvMessageViewer.DataSource = dr; gvMessageViewer.DataBind(); dr.Close(); for (int i = 0; i < gvMessageViewer.Rows.Count; i++) { string mes = gvMessageViewer.Rows[i].Cells[2].Text; if (mes.StartsWith("[REQUEST MESSAGE]") == true) { gvMessageViewer.Rows[i].Cells[2].Text = "[запрос]"; mes = mes.Replace("[REQUEST MESSAGE]", ""); cmd.CommandText = "exec getUserAccess @lg"; int acc = (int)cmd.ExecuteScalar(); if (acc == 4) { Response.Write("<div align=center>"); Response.Write("<a href=" + mes + "> подтвердить " + gvMessageViewer.Rows[i].Cells[1].Text + "</a>"); Response.Write("<BR>"); Response.Write("</div>"); } } } cmd.CommandText = "exec NewMessagesReadedAlready @lg"; cmd.ExecuteScalar(); } catch (Exception err) { Response.Write(err.Message); } finally { con.Close(); } }
public static string fnGetValue(string sSql, string sConn) { string sValue = null; System.Data.SqlClient.SqlConnection sqlConn = new System.Data.SqlClient.SqlConnection(fnGetConStr(sConn)); sqlConn.Open(); System.Data.SqlClient.SqlCommand sqlComm = null; sqlComm = new System.Data.SqlClient.SqlCommand(sSql, sqlConn); sValue = (sqlComm.ExecuteScalar() == null) ? " " : sqlComm.ExecuteScalar().ToString(); sqlConn.Close(); return(sValue); }
public int Ejecutar_Sentencia(string strSQL) { _cmd.Connection = _cn; _cmd.CommandText = strSQL; _cmd.ExecuteNonQuery(); if (strSQL.ToLower().Contains("INSERT")) { _cmd.CommandText = "SELECT @@IDENTITY"; return(int.Parse(_cmd.ExecuteScalar().ToString())); } return(0); }
protected void appStatus() { System.Data.SqlClient.SqlCommand insert = new System.Data.SqlClient.SqlCommand(); insert.Connection = dbConnection; System.Data.SqlClient.SqlCommand getstatus = new System.Data.SqlClient.SqlCommand(); getstatus.Connection = dbConnection; System.Data.SqlClient.SqlCommand getaddress = new System.Data.SqlClient.SqlCommand(); getaddress.Connection = dbConnection; try { dbConnection.Open(); } catch { } try { foreach (RepeaterItem item in rptrTenant.Items) { var tenid = ((Label)item.FindControl("userid")).Text; int number = int.Parse(tenid); getaddress.CommandText = "select accomodationid from application where tenantid = " + number; int address = Int32.Parse(getaddress.ExecuteScalar().ToString()); getstatus.CommandText = "select status from application where tenantid = " + number + " and accomodationid = " + address; int id = Int32.Parse(getaddress.ExecuteScalar().ToString()); bool status = (bool)getstatus.ExecuteScalar(); if (status) { Label lblstat = (Label)item.FindControl("lblstatus"); lblstat.Text = "Accepted"; DropDownList ddl = (DropDownList)item.FindControl("ddlStatus"); ddl.SelectedValue = "1"; } else { Label lblstat = (Label)item.FindControl("lblstatus"); lblstat.Text = "Rejected"; DropDownList ddl = (DropDownList)item.FindControl("ddlStatus"); ddl.SelectedValue = "0"; } } } catch { } finally { dbConnection.Close(); } }
protected void Button1_Click(object sender, EventArgs e) { if (Page.IsValid) { sc.Close(); sc.Open(); System.Data.SqlClient.SqlCommand insert = new System.Data.SqlClient.SqlCommand(); insert.Connection = sc; email = txtEmail.Text; documentID = Convert.ToInt32(ddlRecommendation.SelectedItem.Value);// get document ID insert.CommandText = "SELECT accountID from LoginInfo where email ='" + email + "'"; int receiverAccountID = Convert.ToInt32(insert.ExecuteScalar());// get receiver ID insert.CommandText = "SELECT username,accountID from LoginInfo where email = '" + Session["email"] + "'"; insert.ExecuteNonQuery(); SqlDataReader reader = insert.ExecuteReader(); while (reader.Read()) { string user = reader["username"].ToString(); Session["user"] = user; string ID = reader["accountID"].ToString(); Session["accountID"] = ID; } reader.Close(); System.Data.SqlClient.SqlCommand test = new System.Data.SqlClient.SqlCommand(); test.Connection = sc; test.CommandText = "INSERT INTO [Recommendations] VALUES('" + documentID + "','" + receiverAccountID + "','" + Session["user"] + "','" + DateTime.Now.ToString() + "','" + DateTime.Now.ToString() + "','" + Convert.ToString(Session["user"]) + "')"; test.ExecuteNonQuery(); txtEmail.Text = " "; insert.CommandText = "SELECT accountID from [dbo].[LoginInfo] where accountID = '" + Session["accountID"] + "'"; int accountID = Convert.ToInt32(insert.ExecuteScalar());// get receiver ID TextBox1.Text = accountID.ToString(); //TextBox1.Text = Session["accountID"].ToString(); //int accountID = Convert.ToInt32(TextBox1.Text); } }
protected void Page_Load(object sender, EventArgs e) { if (localDB.State != ConnectionState.Open) { localDB.Open(); } System.Data.SqlClient.SqlCommand getApplicants = new System.Data.SqlClient.SqlCommand(); getApplicants.Connection = localDB; getApplicants.CommandText = "Select count(AppID) From App where(Opened like '*')"; string app = getApplicants.ExecuteScalar().ToString(); lblapps.Text = app; lblApplicants.Text = app; System.Data.SqlClient.SqlCommand getSch = new System.Data.SqlClient.SqlCommand(); getSch.Connection = localDB; getSch.CommandText = "Select count(PostID) From Scholarship where(Opened like '*')"; lblsch.Text = getSch.ExecuteScalar().ToString(); string sch = getSch.ExecuteScalar().ToString(); lblScholarship.Text = sch; System.Data.SqlClient.SqlCommand getEvents = new System.Data.SqlClient.SqlCommand(); getEvents.Connection = localDB; getEvents.CommandText = "Select count(PostID) From Event where(Opened like '*')"; string even = getEvents.ExecuteScalar().ToString(); lblEvents.Text = even; lblEv.Text = even; int not = Convert.ToInt32(app) + Convert.ToInt32(sch) + Convert.ToInt32(even); if (not == 0) { lblNotification.Text = ""; } else { lblNotification.Text = not.ToString(); } //System.Data.SqlClient.SqlCommand getMess = new System.Data.SqlClient.SqlCommand(); //getMess.Connection = localDB; //getMess.CommandText = "Select count(MessageID) From Messages1 where(HasSeen like '*')"; //string messages = getMess.ExecuteScalar().ToString(); //lblMessage.Text = messages; //localDB.Close(); }
protected void btnSelect3_Click(object sender, EventArgs e) { string subscription = "Monthly Subscription"; localDB.Open(); System.Data.SqlClient.SqlCommand getUserID = new System.Data.SqlClient.SqlCommand(); getUserID.Connection = localDB; //Find UserID for most recently entered User getUserID.CommandText = "select MAX(UserID) from USERS"; string returnID = getUserID.ExecuteScalar().ToString(); int idUser = Int32.Parse(returnID); getUserID.ExecuteNonQuery(); //Update user table SqlCommand update = new SqlCommand("Update Users SET Subscription=@Subscription WHERE UserId=@MaxID;", localDB); update.Parameters.Add(new SqlParameter("@Subscription", subscription)); update.Parameters.Add(new SqlParameter("@MaxID", idUser)); update.ExecuteNonQuery(); localDB.Close(); Response.Redirect("Registration4.aspx"); }
/// <summary> /// 执行查询,并将查询返回的结果集中第一行的第一列作为 .NET Framework 数据类型返回。忽略额外的列或行。 /// </summary> /// <param name="sql">SELECT 语句</param> /// <returns>.NET Framework 数据类型形式的结果集第一行的第一列;如果结果集为空或结果为 REF CURSOR,则为空引用</returns> public object ExecuteScalar(string sql) { using (sqlConnection = this.GetSqlConnection()) { if (sqlConnection == null) { return(null); } try { if (sqlConnection.State == System.Data.ConnectionState.Closed) { sqlConnection.Open(); } sqlCommand = new SqlCommand(sql, sqlConnection); return(sqlCommand.ExecuteScalar()); } catch (Exception ex) { #if DEBUG System.Diagnostics.Debug.WriteLine(ex.ToString()); #endif return(null); } } }
//Команда SELECT для суммирования ячеек public decimal SelectSum(String col, String tablename) { SqlConnection conn = DBUtils.GetDBConnection(); conn.Open(); decimal ret = 0; try { string sql = "Select sum(" + col + ") from " + tablename; System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(); cmd.Connection = conn; cmd.CommandText = sql; ret = ((decimal)cmd.ExecuteScalar()); } catch (Exception e) { MessageBox.Show("Error: " + e); MessageBox.Show(e.StackTrace); } finally { conn.Close(); conn.Dispose(); } Console.Read(); return(ret); }
//Loads the Employer's summary onto the page with the most recent description protected void ShowSummary(object sender, EventArgs e) { sc.Open(); System.Data.SqlClient.SqlCommand getdbPersonID = new System.Data.SqlClient.SqlCommand(); getdbPersonID.Connection = sc; //Gets the personid for the username getdbPersonID.CommandText = "SELECT PersonID from Account where Username = '******'"; int accountID = (int)getdbPersonID.ExecuteScalar(); ProfileSummary.Visible = true; subheader.Visible = true; BtnEdit.Visible = true; System.Data.SqlClient.SqlCommand getEmpSummary = new System.Data.SqlClient.SqlCommand(); getEmpSummary.Connection = sc; getEmpSummary.CommandText = "Select EmployerSummary from Employer where PersonID = @SummaryPersonID"; getEmpSummary.Parameters.Add(new SqlParameter("@SummaryPersonID", accountID)); String EmpSum = (String)getEmpSummary.ExecuteScalar(); ProfileSummary.InnerText = EmpSum; sc.Close(); }
private static MemoryStream GetStreamFromDB(string typeName) { //SqlConnection conn = new SqlConnection(MySpace.Configuration.ConnectionStringProvider.ConnectionStrings["TestAssistants"].ConnectionString); SqlConnection conn = new SqlConnection(@"server=devsrv\sql2005;database=TestAssistants;uid=ASPADONet;pwd=12345;Connect Timeout=20;Integrated Security=false;"); System.Data.SqlClient.SqlCommand sqlCommand = new System.Data.SqlClient.SqlCommand(); sqlCommand.Connection = conn; sqlCommand.CommandType = System.Data.CommandType.StoredProcedure; sqlCommand.CommandText = "getSerializedObject"; SqlParameter paramClassName = new SqlParameter("@className", SqlDbType.NVarChar, 100); paramClassName.Value = typeName; sqlCommand.Parameters.Add(paramClassName); object returnValue; try { conn.Open(); returnValue = sqlCommand.ExecuteScalar(); } finally { if (conn.State != ConnectionState.Closed) { conn.Close(); } } MemoryStream stream = new MemoryStream((byte[])returnValue); return(stream); }
/// <summary> /// Determines whether [is A member of group] [the specified group type]. /// </summary> /// <param name="groupType">if set to <c>true</c> [group type].</param> /// <param name="GroupSid">The group sid.</param> /// <param name="netSqlAzManMode">if set to <c>true</c> [net SQL az man mode].</param> /// <param name="rootDsePath">The root dse path.</param> /// <param name="token">The token.</param> /// <param name="userGroupsCount">The user groups count.</param> /// <returns> /// <c>true</c> if [is A member of group] [the specified group type]; otherwise, <c>false</c>. /// </returns> internal bool isAMemberOfGroup(bool groupType, byte[] GroupSid, bool netSqlAzManMode, string rootDsePath, byte[] token, int userGroupsCount) { SqlConnection conn = new SqlConnection(this.store.Storage.ConnectionString); conn.Open(); bool result = false; try { System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("dbo.netsqlazman_IsAMemberOfGroup", conn); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@GROUPTYPE", groupType); cmd.Parameters.AddWithValue("@GROUPOBJECTSID", GroupSid); cmd.Parameters.AddWithValue("@NETSQLAZMANMODE", netSqlAzManMode); cmd.Parameters.AddWithValue("@LDAPPATH", rootDsePath); cmd.Parameters.AddWithValue("@TOKEN", token); cmd.Parameters.AddWithValue("@USERGROUPSCOUNT", userGroupsCount); result = (bool)cmd.ExecuteScalar(); } finally { conn.Close(); } return(result); }
protected void ddlProgramType_SelectedIndexChanged(object sender, EventArgs e) { System.Data.SqlClient.SqlConnection sc = new System.Data.SqlClient.SqlConnection(); // sc.ConnectionString = @"Server=localhost;Database=WildTek;Trusted_Connection=Yes;"; String cs = ConfigurationManager.ConnectionStrings["WildTekConnectionString"].ConnectionString; sc.ConnectionString = cs; sc.Open(); System.Data.SqlClient.SqlCommand selectOrganization = new System.Data.SqlClient.SqlCommand(); selectOrganization.Connection = sc; selectOrganization.Parameters.Clear(); selectOrganization.CommandText = "SELECT OrgID From Program WHERE ProgramID = @progamID"; selectOrganization.Parameters.AddWithValue("@programID", ddlProgramType.SelectedItem.Value); String tempOrg = (String)selectOrganization.ExecuteScalar(); ddlOrganization.ClearSelection(); for (int i = 0; i < ddlOrganization.Items.Count; i++) { if (ddlOrganization.Items[i].Value == tempOrg) { ddlOrganization.Items[i].Selected = true; } } }
public static object RetrieveSingleValue(string Sql) { System.Data.SqlClient.SqlCommand objCom = new System.Data.SqlClient.SqlCommand(); object ld_Result; if (!OpenConnection()) { return(0); } try { objCom.Connection = lo_Connection; objCom.CommandTimeout = CommandTimeOutSeconds; objCom.CommandText = Sql; ld_Result = objCom.ExecuteScalar(); return(ld_Result); } catch { return(0); } finally { lo_Connection.Close(); } }
protected void Page_Load(object sender, EventArgs e) { if (Session["UserName"] == null || HttpContext.Current.Request.UrlReferrer == null) { Response.Redirect("Login.aspx"); } localDB.Open(); System.Data.SqlClient.SqlCommand getApplicants = new System.Data.SqlClient.SqlCommand(); getApplicants.Connection = localDB; getApplicants.CommandText = "Select count(AppID) From App"; lblApplicants.Text = getApplicants.ExecuteScalar().ToString(); System.Data.SqlClient.SqlCommand getjobs = new System.Data.SqlClient.SqlCommand(); getjobs.Connection = localDB; getjobs.CommandText = "Select count(PostID) From Job"; lblJobs.Text = getjobs.ExecuteScalar().ToString(); System.Data.SqlClient.SqlCommand getscholarships = new System.Data.SqlClient.SqlCommand(); getscholarships.Connection = localDB; getscholarships.CommandText = "Select count(PostID) From Scholarship"; lblScholarships.InnerText = getscholarships.ExecuteScalar().ToString(); System.Data.SqlClient.SqlCommand getEvent = new System.Data.SqlClient.SqlCommand(); getEvent.Connection = localDB; getEvent.CommandText = "Select count(PostID) From Event"; lblEvent.InnerText = getEvent.ExecuteScalar().ToString(); localDB.Close(); }
public bool UserExists(string slack_id) { QC.SqlCommand exists = new QC.SqlCommand(); exists.CommandType = System.Data.CommandType.Text; exists.Connection = this.conn; exists.CommandText = @"SELECT * FROM users WHERE slack_id = @User"; var param1 = new QC.SqlParameter("User", slack_id); exists.Parameters.Add(param1); try { int id = (int)exists.ExecuteScalar(); if (id > 0) { return(true); } } catch (Exception e) { return(false); } return(false); }
public bool seachingIfSameValueExists(string whichColumnToCheck, string givenValue) { bool doesExist = true; SqlConnection con = new SqlConnection(connectionString); try { con.Open(); System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(); cmd.CommandType = System.Data.CommandType.Text; cmd.Connection = con; cmd.Parameters.AddWithValue("@" + whichColumnToCheck, givenValue); cmd.CommandText = "SELECT isActive FROM [ManagementToolDatabase].[dbo].[UserRegistrationTable]" + " WHERE " + whichColumnToCheck + " = @" + whichColumnToCheck; try { string isActive = cmd.ExecuteScalar().ToString(); if (isActive.Equals(true) || isActive.Equals(false)) { doesExist = true; } } catch (Exception) { doesExist = false; } con.Close(); } catch (Exception) { MessageBox.Show("Unable to open database"); } return(doesExist); }
protected void Page_Load(object sender, EventArgs e) { if (Session["UserName"] == null || HttpContext.Current.Request.UrlReferrer == null) { Response.Redirect("Login.aspx"); } sendHide.Visible = false; responseHide.Visible = false; noMessagesAlert.Visible = false; localDB.Open(); //Select User's school name System.Data.SqlClient.SqlCommand selectSchoolName = new System.Data.SqlClient.SqlCommand(); selectSchoolName.Connection = localDB; selectSchoolName.CommandText = "select schoolName from school s inner join schoolemployee se on s.schoolID = se.schoolID"; string schoolName = selectSchoolName.ExecuteScalar().ToString(); mainViewUserName.InnerHtml = schoolName; sidebarContactName1.InnerText = schoolName; //Select User's First and Last Name System.Data.SqlClient.SqlCommand selectUserName = new System.Data.SqlClient.SqlCommand(); selectUserName.Connection = localDB; selectUserName.CommandText = "select concat(firstname, ' ', lastname) as userName from users inner join messages on userID = messagetoid AND messageid = (SELECT MAX(messageID) FROM messages)"; string userName = selectUserName.ExecuteScalar().ToString(); mainViewSpeakingWith.InnerHtml = "Speaking with: " + userName; //Display sidebar users }
public override object ConsultaDato(string SQL) { //Datos.connectionString. try { using (System.Data.SqlClient.SqlConnection Cnn = new System.Data.SqlClient.SqlConnection(connectionString)) { using (System.Data.SqlClient.SqlCommand Cmd = new System.Data.SqlClient.SqlCommand(SQL, Cnn)) { try { Cmd.CommandType = CommandType.Text; Cmd.CommandText = SQL; Cnn.Open(); return(Cmd.ExecuteScalar()); } catch (Exception ex) { throw new Exception("Error de acceso a datos, Error: " + ex.ToString()); } finally { if (Cnn.State == ConnectionState.Open) { Cnn.Close(); } } } } } catch (Exception ex) { throw new Exception("Error de acceso a datos, Error: " + ex.ToString()); } }
public int Insert(System.Data.SqlClient.SqlConnection sqlConnection, System.Data.SqlClient.SqlTransaction sqlTran) { System.Data.SqlClient.SqlCommand sqlcmd = new System.Data.SqlClient.SqlCommand("Insert into ASBLOB(FileContent,FileName,FileLength,FileType,FileExtName,AddUserNo,AddDate,IPAddress) Values(@FileContent,@FileName,@FileLength,@FileType,@FileExtName,@AddUserNo,@AddDate,@IPAddress);", sqlConnection); sqlcmd.Transaction = sqlTran; List <SqlParameter> _p = new List <SqlParameter>(); _p.Add(new SqlParameter("@FileContent", this.FileContent)); _p.Add(new SqlParameter("@FileName", this.FileName)); _p.Add(new SqlParameter("@FileLength", this.FileLength)); _p.Add(new SqlParameter("@FileType", this.FileType)); _p.Add(new SqlParameter("@FileExtName", this.FileExtension)); _p.Add(new SqlParameter("@AddUserNo", this.AddUser)); _p.Add(new SqlParameter("@AddDate", this.AddDate)); _p.Add(new SqlParameter("@IPAddress", this.IPAddress)); sqlcmd.Parameters.AddRange(_p.ToArray()); try { sqlcmd.ExecuteNonQuery(); sqlcmd.CommandText = "select @@IDENTITY"; int blobId = Convert.ToInt32(sqlcmd.ExecuteScalar()); return(blobId); } catch (Exception ex) { throw new Exception(ex.Message); } }
public int Add(Person person) { try { _connection.Open(); using (System.Data.SqlClient.SqlCommand sqlcommand = new System.Data.SqlClient.SqlCommand()) { sqlcommand.CommandText = @" INSERT INTO [dbo].[Person] ([FirstName] ,[LastName] ,[Phone] ,[Email] ,[CreateStamp] ,[ModifStamp]) OUTPUT INSERTED.ID VALUES ( '" + person.FirstName + @"', '" + person.LastName + @"', '" + person.PhoneNumber + @"', '" + person.Email + @"', '" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + @"', '" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + @"');"; sqlcommand.Connection = _connection; int count = (int)sqlcommand.ExecuteScalar(); return(count); } } catch (Exception ex) { Console.WriteLine(ex.Message); } finally { _connection.Close(); } return(-1); }
public bool CheckIsRecordExist(string a_strChkSqlCommand) { System.Data.SqlClient.SqlCommand objCommand; bool RecordExists = false; try { bool AttemptToConnect = OpenSqlConnection(); objCommand = new System.Data.SqlClient.SqlCommand(a_strChkSqlCommand); objCommand.Connection = myConn; //objCommand.CommandTimeout=ConnectionTimeOut; objCommand.CommandType = CommandType.Text; string result = Convert.ToString(objCommand.ExecuteScalar()); objCommand.Dispose(); if (result != "") { RecordExists = true; } return(RecordExists); } catch (SqlException ex) { ex.Data.Clear(); return(RecordExists); } catch (Exception ex) { ex.Data.Clear(); return(RecordExists); } finally { CloseConnection(); } }
//Use method in order to select the accommodationID from the Accommodation table. public int returnAccomIDFromDB(Accommodation newAccom) { int accomID; try { sc1.Open(); System.Data.SqlClient.SqlCommand getAccomID = new System.Data.SqlClient.SqlCommand(); getAccomID.Connection = sc1; getAccomID.CommandText = "SELECT AccommodationID FROM ACCOMMODATION WHERE HostID=@hostID AND Street=@address AND CityCo=@city AND AccomState=@state AND Zip=@zip AND Price=@price AND RoomType=@roomType AND Neighborhood=@neighborhood AND Description=@description"; getAccomID.Parameters.Add(new SqlParameter("@address", newAccom.getAddress())); getAccomID.Parameters.Add(new SqlParameter("@city", newAccom.getAccomCity())); getAccomID.Parameters.Add(new SqlParameter("@state", newAccom.getAccomState())); getAccomID.Parameters.Add(new SqlParameter("@zip", newAccom.getAccomZip())); getAccomID.Parameters.Add(new SqlParameter("@price", newAccom.getAccomPrice())); getAccomID.Parameters.Add(new SqlParameter("@roomType", newAccom.getAccomRoomType())); getAccomID.Parameters.Add(new SqlParameter("@neighborhood", newAccom.getAccomNeighborhood())); getAccomID.Parameters.Add(new SqlParameter("@description", newAccom.getAccomDescription())); getAccomID.Parameters.Add(new SqlParameter("@hostID", newAccom.getAccomHostID())); getAccomID.ExecuteNonQuery(); accomID = Convert.ToInt32(getAccomID.ExecuteScalar()); sc1.Close(); return(accomID); } catch { return(0); } }
protected object ScalarSQLCommand(System.Data.SqlClient.SqlCommand sqlcommand) { object ret = sqlcommand.ExecuteScalar(); DisposeCommand(sqlcommand); return(ret); }
/// <summary> /// 返回一行一列的数据 /// </summary> /// <param name="ConnStr">连接字符串</param> /// <param name="strSQL">SQL语句</param> /// <returns></returns> public virtual object GetScalarData(string ConnStr, string strSQL) { { try { using (sqlconn = new SDC.SqlConnection(ConnStr)) { sqlconn.Open(); using (sqlcmd = new SDC.SqlCommand(strSQL, sqlconn)) { return(sqlcmd.ExecuteScalar()); } } } catch (Exception ex) { throw ex; } finally { if (sqlconn != null) { if (sqlconn.State != SD.ConnectionState.Closed) { sqlconn.Close(); } sqlconn.Dispose(); } } } }
public override Object CreateSqlInsert(string _tableName, bool autoincr) { System.Text.StringBuilder _sb = new System.Text.StringBuilder(); _sb.Append("INSERT INTO "); _sb.Append("[" + _tableName + "]"); _sb.Append(" ("); _sb.Append(_sb_insertCol); _sb.Append(") VALUES("); _sb.Append(_sb_insertVal); _sb.Append(") "); if (autoincr) { _sb.AppendLine("SELECT SCOPE_IDENTITY()"); } this._command.CommandText = _sb.ToString(); Settings.Log.LogSQL(_command.CommandText, _command.Parameters); Connect(); var ret = _command.ExecuteScalar(); Disconnect(); return(ret); }
public void SqlConnection(String Statement) { try { System.Data.SqlClient.SqlConnection sc = new System.Data.SqlClient.SqlConnection(); System.Data.SqlClient.SqlDataReader DataReader = null; sc.ConnectionString = @"Server=SILAS-PC\LOCALHOST; Database=WLS;Trusted_Connection=Yes;"; sc.Open(); System.Diagnostics.Debug.WriteLine("databse connection opened"); System.Data.SqlClient.SqlCommand sendComm = new System.Data.SqlClient.SqlCommand(); sendComm.Connection = sc; sendComm.CommandText = Statement; if (Statement.Contains("Delete") || (Statement.Contains("Insert"))) { sendComm.ExecuteNonQuery(); } if ((Statement.Contains("Select"))) { String totSalary = sendComm.ExecuteScalar().ToString(); } if (Statement.Contains("Equip") && (Statement.Contains("Select"))) { } sc.Close(); } catch (System.Data.SqlClient.SqlException sqlException) { System.Diagnostics.Debug.WriteLine(sqlException.Message); } }
private void comboBox1_SelectedIndexChanged(object sender, EventArgs e) { SqlConnection sqlCon = new SqlConnection(str); System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand("select photo from test where name=@name", sqlCon); command.Parameters.AddWithValue("@name", comboBox1.SelectedValue.ToString()); sqlCon.Open(); byte[] image = (byte[])command.ExecuteScalar(); if (image != null) { sqlCon.Close(); //Save the picture from Db in the memory stream MemoryStream ms = new MemoryStream(image); //Load the picture from memory stream to picture box //pictureBox1.Image = Image.FromStream(ms); foreach (PictureBox pb in listPB) { //Find an empty picture box if (pb.Image == null) { pb.Image = Image.FromStream(ms); break; } } } }