public bool addAlbum(string name, int pubYear) { SqlConnection con = getConnection(); string result = "OK"; try { con.Open(); string query = "INSERT INTO Album " + "(name, year_published)" + " VALUES (@name, @pub_year)"; SqlCommand command = new SqlCommand(query, con); command.Prepare(); command.Parameters.AddWithValue("@name", name); command.Parameters.AddWithValue("@pub_year", pubYear); command.ExecuteNonQuery(); } catch (Exception e) { result = e.Message; return false; } finally { con.Close(); // Log the result Console.WriteLine(result); } return true; }
public bool anbieterAktualisieren(Anbieter benutzer) { string query = "UPDATE Benutzer SET " + "vorname=@vorname, " + "nachname=@nachname, " + "institut=@institut " + "WHERE email=@email"; try { SqlCommand cmd = new SqlCommand(query, con); cmd.Prepare(); cmd.Parameters.AddWithValue("@vorname", benutzer.vorname); cmd.Parameters.AddWithValue("@nachname", benutzer.nachname); cmd.Parameters.AddWithValue("@institut", benutzer.institut); cmd.Parameters.AddWithValue("@email", benutzer.email); connect(); cmd.ExecuteNonQuery(); disconnect(); return true; } catch (SqlException e) { lastError = e.StackTrace; Console.WriteLine(e.StackTrace); return false; } }
public void addCVs(SqlConnection sql, int id, string path) { sql.Open(); using (SqlCommand addCVs = new SqlCommand("INSERT INTO dbo.ExternFile VALUES (1, @idEmployee, @path)", sql)) { addCVs.Parameters.Clear(); // Parameters SqlParameter idEmployeeParam = new SqlParameter("@ID", SqlDbType.Int); SqlParameter pathParam = new SqlParameter("@path", SqlDbType.VarChar, 255); // Definitions for parameters idEmployeeParam.Value = id; pathParam.Value = path; // Add parameters addCVs.Parameters.Add(idEmployeeParam); addCVs.Parameters.Add(pathParam); // Execute command addCVs.Prepare(); addCVs.ExecuteNonQuery(); MessageBox.Show("Životopis úspěšně přidán do DB"); } sql.Close(); }
public string AddFeatureToRole(SysFeature feature) { if (!HasFeature(feature)) { string connection = ConfigurationManager.ConnectionStrings["EVENTSConnectionString"].ToString(); SqlConnection dbConnection = new SqlConnection(connection); try { dbConnection.Open(); SqlParameter FeatureParam = new SqlParameter("@FEATURE_NAME", System.Data.SqlDbType.NVarChar, 70); FeatureParam.Value = feature.FeatureID; SqlParameter RoleParam = new SqlParameter("@ROLE_NAME", System.Data.SqlDbType.NVarChar, 70); RoleParam.Value = this.roleID; SqlParameter TimeStampParam = new SqlParameter("@SYS_TIMESTAMP", System.Data.SqlDbType.NVarChar, 70); TimeStampParam.Value = System.DateTime.Now; SqlParameter[] AddFeatureParam = new SqlParameter[] { FeatureParam, RoleParam, TimeStampParam }; string addFeatureCommand = "INSERT INTO ROLE_SET (ROLE_NAME,FEATURE_NAME,SYS_TIMESTAMP) VALUES (@ROLE_NAME, @FEATURE_NAME, @SYS_TIMESTAMP);"; SqlCommand addFeature = new SqlCommand(null, dbConnection); addFeature.Parameters.AddRange(AddFeatureParam); addFeature.CommandText = addFeatureCommand; addFeature.Prepare(); addFeature.ExecuteNonQuery(); dbConnection.Close(); return feature.FeatureID + " has been added to " + this.roleID; } catch (SqlException exception) { return "<p> Error Code " + exception.Number + ": " + exception.Message + "</p>"; } } { return "Role already has feature"; } }
protected void CountBikesButton_Click(object sender, EventArgs e) { StringBuilder count = new StringBuilder(); string fDateTime = FromDate.Text + " " + FromDateTime.Text; string tDateTime = ToDate.Text + " " + ToDateTime.Text; SqlConnection advbikecountconn = null; SqlDataReader advbikecountrdr = null; advbikecountconn = new SqlConnection(conStr); advbikecountconn.Open(); // cmd2 RETRIEVE DATA SqlCommand advbikecountcmd = new SqlCommand(); advbikecountcmd.Connection = advbikecountconn; advbikecountcmd.CommandText = @"SELECT COUNT(*) FROM rightalertmaster WHERE DateTime >= '" + fDateTime + "' AND DateTime <= '" + tDateTime + "';"; advbikecountcmd.Prepare(); advbikecountrdr = advbikecountcmd.ExecuteReader(); if (advbikecountrdr.HasRows) { while (advbikecountrdr.Read()) { string num = advbikecountrdr[0].ToString(); //count.Append("<div class=\"ov - widget__value\"><label>"); count.Append("Bike Count: " + num.ToString()); //count.Append("</label></div><div class=\"ov-widget__info\"><div class=\"ov-widget__title\">Bike Count</div></div>"); } } advbikecountrdr.Close(); advbikecountconn.Close(); CountBikesPlaceHolder.Controls.Add(new Literal { Text = count.ToString() }); }
public void AddUserRole(string roleID = "REGISTERER") { string connection = ConfigurationManager.ConnectionStrings["EVENTSConnectionString"].ToString(); SqlConnection dbConnection = new SqlConnection(connection); try { dbConnection.Open(); SqlParameter EmailParam = new SqlParameter("@EMAIL", System.Data.SqlDbType.NVarChar, 50); EmailParam.Value = this.applicationUser.Email; SqlParameter RoleParam = new SqlParameter("@ROLE_NAME", System.Data.SqlDbType.NVarChar, 70); RoleParam.Value = roleID; SqlParameter[] AddRoleParameters = new SqlParameter[] { EmailParam, RoleParam }; string addRoleCommand = "INSERT INTO USER_ROLE (EMAIL, ROLE_NAME) VALUES (@EMAIL, @ROLE_NAME);"; SqlCommand addRole = new SqlCommand(null, dbConnection); addRole.Parameters.AddRange(AddRoleParameters); addRole.CommandText = addRoleCommand; addRole.Prepare(); addRole.ExecuteNonQuery(); dbConnection.Close(); } catch (SqlException exception) { // "<p> Error Code " + exception.Number + ": " + exception.Message + "</p>"; } }
public ActionResult Comments() { List<string> comments = new List<string>(); //Get current user from default membership provider MembershipUser user = Membership.Provider.GetUser(HttpContext.User.Identity.Name, true); if (user != null) { //Add request param : https://msdn.microsoft.com/fr-fr/library/system.data.sqlclient.sqlcommand.prepare(v=vs.110).aspx SqlCommand cmd = new SqlCommand(null, _dbConnection); // Create and prepare an SQL statement. cmd.CommandText ="Select Comment from Comments where UserId = @user_id"; SqlParameter idParam = new SqlParameter("@user_id", SqlDbType.UniqueIdentifier); idParam.Value = user.ProviderUserKey; cmd.Parameters.Add(idParam); _dbConnection.Open(); // Call Prepare after setting the Commandtext and Parameters. cmd.Prepare(); SqlDataReader rd = cmd.ExecuteReader(); while (rd.Read()) { comments.Add(rd.GetString(0)); } rd.Close(); _dbConnection.Close(); } return View(comments); }
public static void DeleteOpenMessage(Message message, string email) { string connection = ConfigurationManager.ConnectionStrings["EVENTSConnectionString"].ToString(); SqlConnection dbConnection = new SqlConnection(connection); try { dbConnection.Open(); SqlParameter SelectedMessageParam = new SqlParameter("@MESSAGE_ID", System.Data.SqlDbType.Int); SelectedMessageParam.Value = Int32.Parse(message.MessageID); SqlParameter EmailParam = new SqlParameter("@EMAIL", System.Data.SqlDbType.NVarChar, 50); EmailParam.Value = email; string deleteMessageCommand = "UPDATE USER_MESSAGE_RECIPIENTS " + "SET IS_DELETED = 'T' " + "WHERE MESSAGE_ID = @MESSAGE_ID " + "AND MESSAGE_RECIPIENT = @EMAIL;"; SqlCommand deleteMessage = new SqlCommand(null, dbConnection); deleteMessage.Parameters.Add(SelectedMessageParam); deleteMessage.Parameters.Add(EmailParam); deleteMessage.CommandText = deleteMessageCommand; deleteMessage.Prepare(); deleteMessage.ExecuteNonQuery(); dbConnection.Close(); } catch (SqlException exception) { //Response.Write("<p> Error Code " + exception.Number + ": " + exception.Message + "</p>"); } }
static int Main () { if (Environment.GetEnvironmentVariable ("MONO_TESTS_SQL") == null) return 0; using (SqlConnection myConnection = new SqlConnection (CreateConnectionString ())) { SqlCommand command = new SqlCommand (drop_stored_procedure, myConnection); myConnection.Open (); command.ExecuteNonQuery (); command = new SqlCommand (create_stored_procedure, myConnection); command.ExecuteNonQuery (); command = new SqlCommand ("bug316091", myConnection); command.CommandType = CommandType.StoredProcedure; command.Parameters.Add ("@UserAccountStatus", SqlDbType.SmallInt).Value = UserAccountStatus.ApprovalPending; command.Prepare (); SqlDataReader dr = command.ExecuteReader (); if (!dr.Read ()) return 1; if (dr.GetInt32 (0) != 1) return 2; if (dr.GetInt32 (1) != 0) return 3; dr.Close (); // clean-up command = new SqlCommand (drop_stored_procedure, myConnection); command.ExecuteNonQuery (); return 0; } }
public SqlDataReader GetDataReader(string sql) { SqlConnection conn = new SqlConnection(ConnectionString); conn.Open(); using (SqlCommand cmd = new SqlCommand(sql, conn)) { cmd.Prepare(); return cmd.ExecuteReader(CommandBehavior.CloseConnection); } }
protected void LoginButton_Click(object sender, EventArgs e) { if (!Page.IsValid) return; try { var connString = ConfigurationManager.ConnectionStrings["CineQuilla"].ConnectionString; using (SqlConnection connection = new SqlConnection(connString)) { connection.Open(); using (SqlCommand command = new SqlCommand(null, connection)) { command.CommandText = "SELECT TOP 1 id, username, first_name, last_name, permission_level FROM users WHERE username = @user AND password_hash = HASHBYTES('SHA2_256', @pass)"; SqlParameter userParam = new SqlParameter("@user", SqlDbType.VarChar, 255); SqlParameter passParam = new SqlParameter("@pass", SqlDbType.VarChar, 255); userParam.Value = UsernameBox.Text; passParam.Value = PasswordBox.Text; command.Parameters.Add(userParam); command.Parameters.Add(passParam); command.Prepare(); var reader = command.ExecuteReader(); if (!reader.HasRows) { // There's no matching user LoginError.Visible = true; return; } else { reader.Read(); Session["UserInfo"] = new UserInfo { Id = reader.GetInt32(0), Username = reader.GetString(1), FirstName = reader.GetString(2), LastName = reader.GetString(3), PermissionLevel = reader.GetInt32(4) }; // Login success! FormsAuthentication.RedirectFromLoginPage(reader.GetInt32(0).ToString(), true); } } } } catch (SqlException ex) { throw; } }
public static SqlCommand CommandStored(string query) { var sqlCo = new SqlCommand { CommandType = CommandType.StoredProcedure, Connection = GetConnection(), CommandText = query }; sqlCo.Prepare(); return sqlCo; }
void update1() { SqlCommand comm = new SqlCommand("insert into tblEmployee(EmployeeName,Sex,Age,DepartmentId) value(@a,'@b',@c,@d)",new SqlConnection(ConfigurationSettings.AppSettings["connectionString"])); comm.Connection.Open(); comm.Prepare(); comm.Parameters.Add(new SqlParameter("@a","'Ragz'")); comm.Parameters.Add(new SqlParameter("@b","M")); comm.Parameters.Add(new SqlParameter("@c",23)); comm.Parameters.Add(new SqlParameter("@d",1)); comm.ExecuteNonQuery(); comm.Connection.Close(); }
protected void Button2_Click(object sender, EventArgs e) { if (!Page.IsValid) return; try { string imgPath = "Uploads" + "/" + ImageUpload.FileName; ImageUpload.SaveAs(Server.MapPath("Uploads") + "/" + ImageUpload.FileName); var connString = ConfigurationManager.ConnectionStrings["CineQuilla"].ConnectionString; using (SqlConnection connection = new SqlConnection(connString)) { connection.Open(); using (SqlCommand command = new SqlCommand(null, connection)) { command.CommandText = "INSERT INTO movies (name, description, year, director, length, image) VALUES (@name, @descr, @year, @direc, @len, @img)"; SqlParameter nameParam = new SqlParameter("@name", SqlDbType.VarChar, 255); SqlParameter descrParam = new SqlParameter("@descr", SqlDbType.Text, DescriptionBox.Text.Length); SqlParameter yearParam = new SqlParameter("@year", SqlDbType.Int); SqlParameter direcParam = new SqlParameter("@direc", SqlDbType.VarChar, 255); SqlParameter lenParam = new SqlParameter("@len", SqlDbType.Int); SqlParameter imgParam = new SqlParameter("@img", SqlDbType.Text, imgPath.Length); nameParam.Value = NameBox.Text; descrParam.Value = DescriptionBox.Text; yearParam.Value = YearBox.Text; direcParam.Value = DirectorBox.Text; lenParam.Value = LengthBox.Text; imgParam.Value = imgPath; command.Parameters.Add(nameParam); command.Parameters.Add(descrParam); command.Parameters.Add(yearParam); command.Parameters.Add(direcParam); command.Parameters.Add(lenParam); command.Parameters.Add(imgParam); command.Prepare(); command.ExecuteNonQuery(); } } } catch (SqlException ex) { ErrorLabel.Visible = true; return; } Session["ShowMovieAddedMessage"] = true; Response.Redirect(Request.RawUrl); }
/// <summary> /// Constructor. The constructor will call the Prepare method on the SqlCommand /// object, so the caller does not need to call this method. /// </summary> public TSSqlCommandContainer(int connectionId, SqlConnection sqlConnection, String tableName, String keyString, SqlCommand sqlCommand) { ConnectionId = connectionId; SqlConnection = sqlConnection; TableName = tableName; KeyString = keyString; SqlCommand = sqlCommand; // Prepare the command to ensure that it is cached by the database system. // http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/a702d6eb-54bd-492b-9715-59bac182263d/ SqlCommand.Prepare(); }
public System.Xml.XmlDocument CreateDeepZoomForSite(long siteID) { using (SqlConnection conn = new SqlConnection(_connectionString)) { conn.Open(); using (SqlCommand command = new SqlCommand("select Photos.ID,BlobID,Width,Height,ContainerID from Photos inner join CameraSites on Photos.Site_ID = CameraSites.ID where CameraSites.ID = @id", conn)) { command.Prepare(); command.Parameters.AddWithValue("@id", siteID); return CreateDeepZoomDocument(command, null); } } }
public Form1() { InitializeComponent(); connection = new System.Data.SqlClient.SqlConnection(connectionString); connection.Open(); command = new System.Data.SqlClient.SqlCommand(null, connection); command.CommandText = "insert into employee(ename, city) values(@name, @addr);select SCOPE_IDENTITY();"; command.Parameters.Add(nameparam); command.Parameters.Add(addrparam); command.Prepare(); }
/// <summary> /// 得到一个SqlDataReader /// </summary> /// <param name="sql">sql语句</param> /// <returns></returns> public SqlDataReader GetDataReader(string sql, SqlParameter[] parameter) { SqlConnection conn = new SqlConnection(ConnectionString); conn.Open(); using (SqlCommand cmd = new SqlCommand(sql, conn)) { if (parameter != null && parameter.Length > 0) cmd.Parameters.AddRange(parameter); SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); cmd.Parameters.Clear(); cmd.Prepare(); return dr; } }
protected void Session_Start(object sender, EventArgs e) { if (!Request.IsAuthenticated) return; // Fill the needed session information try { var connString = ConfigurationManager.ConnectionStrings["CineQuilla"].ConnectionString; using (SqlConnection connection = new SqlConnection(connString)) { connection.Open(); using (SqlCommand command = new SqlCommand(null, connection)) { command.CommandText = "SELECT TOP 1 id, username, first_name, last_name, permission_level FROM users WHERE id = @id"; SqlParameter idParam = new SqlParameter("@id", SqlDbType.Int); idParam.Value = HttpContext.Current.User.Identity.Name; command.Parameters.Add(idParam); command.Prepare(); var reader = command.ExecuteReader(); if (!reader.HasRows) { // There's no matching user Auth.Helpers.Logout(null); return; } else { reader.Read(); Session["UserInfo"] = new UserInfo { Id = reader.GetInt32(0), Username = reader.GetString(1), FirstName = reader.GetString(2), LastName = reader.GetString(3), PermissionLevel = reader.GetInt32(4) }; } } } } catch (SqlException ex) { throw; } }
public SqlDataReader buscarContrato(long contratoId) { SqlConnection conn = conectarBD(); conn.Open(); string transaccion = "SELECT * " + "FROM Contrato C, Producto P " + "WHERE ContratoId = @contrato AND C.ProductoId = P.ProductoId"; SqlCommand busqueda = new SqlCommand(transaccion, conn); SqlParameter parametroContrato = new SqlParameter("@contrato", SqlDbType.Int, 200); parametroContrato.Value = (int)contratoId; busqueda.Parameters.Add(parametroContrato); busqueda.Prepare(); SqlDataReader resultado = busqueda.ExecuteReader(); return resultado; }
protected void Button2_Click(object sender, EventArgs e) { if (!Page.IsValid) return; try { var connString = ConfigurationManager.ConnectionStrings["CineQuilla"].ConnectionString; using (SqlConnection connection = new SqlConnection(connString)) { connection.Open(); using (SqlCommand command = new SqlCommand(null, connection)) { command.CommandText = "INSERT INTO users (username, password_hash, first_name, last_name, permission_level) VALUES (@user, HASHBYTES('SHA2_256', @pass), @fname, @lname, @plevel)"; SqlParameter userParam = new SqlParameter("@user", SqlDbType.VarChar, 255); SqlParameter passParam = new SqlParameter("@pass", SqlDbType.VarChar, 255); SqlParameter fnameParam = new SqlParameter("@fname", SqlDbType.VarChar, 255); SqlParameter lnameParam = new SqlParameter("@lname", SqlDbType.VarChar, 255); SqlParameter plevelParam = new SqlParameter("@plevel", SqlDbType.Int); userParam.Value = UsernameBox.Text; passParam.Value = PasswordBox.Text; fnameParam.Value = FNameBox.Text; lnameParam.Value = LNameBox.Text; plevelParam.Value = PermissionLevelSelect.Value; command.Parameters.Add(userParam); command.Parameters.Add(passParam); command.Parameters.Add(fnameParam); command.Parameters.Add(lnameParam); command.Parameters.Add(plevelParam); command.Prepare(); command.ExecuteNonQuery(); } } } catch (SqlException ex) { ErrorLabel.Visible = true; return; } Session["ShowUserAddedMessage"] = true; Response.Redirect(Request.RawUrl); }
public static void DeleteAllRows(string tableName) { if (tableName == null) throw new ArgumentNullException("tableName"); using (var connection = new SqlConnection(Database.GetConnection(false, "Natekdb").ConnectionString)) { connection.Open(); var command = new SqlCommand(null, connection) { CommandText = "DELETE FROM " + tableName }; // Call Prepare after setting the Commandtext and Parameters. command.Prepare(); command.ExecuteNonQuery(); connection.Close(); } }
static void Main(string[] args) { Console.WriteLine("Fun with Data Readers\n"); // 特定于SQLserver数据库的提供程序的对象 // create and open a connection SqlConnectionStringBuilder cnStrBuilder = new SqlConnectionStringBuilder(); cnStrBuilder.InitialCatalog = "AutoLot"; cnStrBuilder.DataSource = @"(local)\SQLEXPRESS"; cnStrBuilder.ConnectTimeout = 30; cnStrBuilder.IntegratedSecurity = true; //string cnStrTest = @"Data Source=(local)\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=AutoLot"; //SqlConnectionStringBuilder cnStrBuilderTest = new SqlConnectionStringBuilder(cnStrTest); //cnStrBuilderTest.ConnectTimeout = 10; using (SqlConnection cn = new SqlConnection()) { //cn.ConnectionString = cnStrBuilderTest.ConnectionString; cn.ConnectionString = cnStrBuilder.ConnectionString; cn.Open(); // create an sql command object. ShowConnectionStatus(cn); string strSql = "Select * From Inventory"; SqlCommand myCommand = new SqlCommand(strSql, cn); myCommand.Prepare(); //SqlCommand myCommand = new SqlCommand(); //myCommand.Connection = cn; //myCommand.CommandText = strSql; // obtain a data reader ExecuteReader(); using (SqlDataReader myDataReader = myCommand.ExecuteReader()) { // 数据读取器通过Read()方法返回一个布尔值来表示是否读完数据 while (myDataReader.Read()) { Console.WriteLine("-> Make: {0}, PetName: {1}, Color: {2}, Type: {3}", myDataReader["Make"].ToString(), myDataReader[1].ToString(), myDataReader["Color"].ToString(), myDataReader["PetName"].GetType().Name); } } } }
static int Main () { if (Environment.GetEnvironmentVariable ("MONO_TESTS_SQL") == null) return 0; using (SqlConnection myConnection = new SqlConnection (CreateConnectionString ())) { SqlDataReader dr; SqlCommand command; command = new SqlCommand (drop_stored_procedure, myConnection); myConnection.Open (); command.ExecuteNonQuery (); command = new SqlCommand (create_stored_procedure, myConnection); command.ExecuteNonQuery (); command = new SqlCommand ("bug324590", myConnection); command.CommandType = CommandType.StoredProcedure; SqlParameter param1 = command.Parameters.Add ("@Param1", SqlDbType.NVarChar); param1.Value = "data"; SqlParameter param2 = command.Parameters.Add ("@Param2", SqlDbType.NVarChar); param2.Value = "mo, no"; command.Prepare (); dr = command.ExecuteReader (); Assert.IsTrue (dr.Read (), "#A1"); Assert.AreEqual ("datamo, no", dr.GetString (0), "#A2"); Assert.IsFalse (dr.Read (), "#A3"); dr.Close (); param2.Value = "mo,no"; dr = command.ExecuteReader (); Assert.IsTrue (dr.Read (), "#B1"); Assert.AreEqual ("datamo,no", dr.GetString (0), "#B2"); Assert.IsFalse (dr.Read (), "#B3"); dr.Close (); // clean-up command = new SqlCommand (drop_stored_procedure, myConnection); command.ExecuteNonQuery (); return 0; } }
/// <summary> /// ExecuteNonQuery /// </summary> /// <param name="command">Sql command</param> /// <returns>count of affected rows</returns> public int ExecuteNonQuery(SqlCommand command) { int rowNumber = 0; try { command.Prepare(); rowNumber = command.ExecuteNonQuery(); } catch (Exception e) { throw e; } finally { Close(); } return rowNumber; }
public SqlDataReader buscarReconocimientoPor(long contratoID, DateTime fecha) { SqlConnection conn = conectarBD(); conn.Open(); string transaccion = "SELECT Cantidad " + "FROM dbo.ReconocimientoIngreso "+ "WHERE contratoId = @contrato AND FechaReconocimiento <= @fecha"; SqlCommand busqueda = new SqlCommand(transaccion, conn); SqlParameter paramContrato = new SqlParameter("@contrato", SqlDbType.Int, 200); SqlParameter paramFecha = new SqlParameter("@fecha", SqlDbType.Date); paramContrato.Value = contratoID; paramFecha.Value = fecha.ToShortDateString(); busqueda.Parameters.Add(paramContrato); busqueda.Parameters.Add(paramFecha); busqueda.Prepare(); SqlDataReader resultado = busqueda.ExecuteReader(); return resultado; }
protected void btnSchedule_Click(object sender, EventArgs e) { SqlConnection beansdbconnection; beansdbconnection = new SqlConnection(ConfigurationManager.ConnectionStrings["Beans_DB_v5ConnectionString"].ToString()); int EntID = int.Parse(grdContactInfo.SelectedValue.ToString()); int ShiftID = int.Parse(grdShift.SelectedValue.ToString()); int numWork = int.Parse(txtNumberofWorkers.Text); SqlCommand cmdSchedule = new SqlCommand("INSERT INTO Volunteer(Entity_ID," + "Shift_ID, Volunteers_Scheduled, Volunteer_Comments) VALUES(@EntID, @ShiftID," + "@VolScheduled, @VolComments)", beansdbconnection); cmdSchedule.Prepare(); cmdSchedule.Parameters.Add("@EntID", SqlDbType.Int, 4); cmdSchedule.Parameters.Add("@ShiftID", SqlDbType.Int, 4); cmdSchedule.Parameters.Add("@VolScheduled", SqlDbType.Int, 4); cmdSchedule.Parameters.Add("@VolComments", SqlDbType.VarChar, 50); try { beansdbconnection.Open(); Response.Write("Connection is open!!!"); cmdSchedule.Parameters["@EntID"].Value = EntID; cmdSchedule.Parameters["@ShiftID"].Value = ShiftID; cmdSchedule.Parameters["@VolScheduled"].Value = numWork; cmdSchedule.Parameters["@VolComments"].Value = txtComments.Text; cmdSchedule.ExecuteNonQuery(); } catch { Response.Write("Could Not Open Database"); } finally { beansdbconnection.Close(); Response.Write("Connection is now closed"); } Response.Redirect("ScheduleResults.aspx"); }
protected void Button2_Click(object sender, EventArgs e) { if (!Page.IsValid) return; try { var connString = ConfigurationManager.ConnectionStrings["CineQuilla"].ConnectionString; using (SqlConnection connection = new SqlConnection(connString)) { connection.Open(); using (SqlCommand command = new SqlCommand(null, connection)) { command.CommandText = "INSERT INTO showroom (rows, columns, capable_3d, active) VALUES (@rows, @columns, @ac_3d, @active)"; SqlParameter rowsParam = new SqlParameter("@rows", SqlDbType.Int); SqlParameter colsParam = new SqlParameter("@columns", SqlDbType.Int); SqlParameter cap3dParam = new SqlParameter("@ac_3d", SqlDbType.Int); SqlParameter activeParam = new SqlParameter("@active", SqlDbType.Int); rowsParam.Value = ChairRowsBox.Text; colsParam.Value = ChairColumnsBox.Text; cap3dParam.Value = Capable3DBox.Checked ? 1 : 0; activeParam.Value = ActiveBox.Checked ? 1 : 0; command.Parameters.Add(rowsParam); command.Parameters.Add(colsParam); command.Parameters.Add(cap3dParam); command.Parameters.Add(activeParam); command.Prepare(); command.ExecuteNonQuery(); } } } catch (SqlException ex) { ErrorLabel.Visible = true; return; } Session["ShowShowroomAddedMessage"] = true; Response.Redirect(Request.RawUrl); }
protected void Page_Load(object sender, EventArgs e) { SqlConnection syslogconn = null; SqlDataReader syslogrdr = null; syslogconn = new SqlConnection(conStr); syslogconn.Open(); // cmd2 RETRIEVE DATA SqlCommand syslogcmd = new SqlCommand(); syslogcmd.Connection = syslogconn; syslogcmd.CommandText = @"SELECT ID, Time, Date, Sender, Status FROM dbo.activity ORDER BY ID DESC;"; syslogcmd.Prepare(); syslogrdr = syslogcmd.ExecuteReader(); if (syslogrdr.HasRows) { while (syslogrdr.Read()) { string id = syslogrdr[0].ToString(); string time; if (syslogrdr[1].ToString().Contains('.')) { time = syslogrdr[1].ToString().Substring(0, syslogrdr[1].ToString().IndexOf(".")); } else { time = syslogrdr[1].ToString(); } string date = Regex.Match(syslogrdr[2].ToString(), "^[^ ]+").Value; string _sender = syslogrdr[3].ToString(); string status = syslogrdr[4].ToString(); table.Append("<tr>"); table.Append("<td>" + id + "</td>"); table.Append("<td>" + time + "</td>"); table.Append("<td>" + date + "</td>"); table.Append("<td>" + _sender + "</td>"); table.Append("<td>" + status + "</td>"); table.Append("</tr>"); } } syslogrdr.Close(); syslogconn.Close(); ActivityLogTableDataPlaceHolder.Controls.Add(new Literal { Text = table.ToString() }); }
public bool addKorisnik(string username, string password, string name, string email, int type, DateTime birthDay, int sex) { SqlConnection con = getConnection(); string result = "OK"; try { con.Open(); // TODO: Ovde mozi treba trustlevel? ILi default se vnesuva ako nema niso? //TODO: ADD email???? string query = "INSERT INTO Korisnik (type, name, username, passwd, birthday, sex, datum_na_reg)" + " VALUES (@type, @name, @username, @email, AES_ENCRYPT(@passwd, SHA1(@username)), @birthday, @sex, @datum_na_reg)"; SqlCommand command = new SqlCommand(query, con); command.Prepare(); command.Parameters.AddWithValue("@type", type); command.Parameters.AddWithValue("@name", name); command.Parameters.AddWithValue("@username", username); command.Parameters.AddWithValue("@email", email); command.Parameters.AddWithValue("@passwd", password); command.Parameters.AddWithValue("@birthday", birthDay); command.Parameters.AddWithValue("@sex", sex); command.Parameters.AddWithValue("@datum_na_reg", DateTime.Now); command.ExecuteNonQuery(); } catch (Exception e) { result = e.Message; return false; } finally { con.Close(); // Log the result Console.WriteLine(result); } return true; //return new Korisnik(name, username, new DateTime(), 0, type, 0); }
/// <summary> /// Returns the supported countries. /// </summary> /// <param name="language">Code of the language that should be used.</param> /// <remarks>Shows how to call a stored procedure.</remarks> public Countries GetCountries(string language) { var result = new Countries(); //First: A connection is needed using (var connection = new System.Data.SqlClient.SqlConnection("Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=C:\\MyProjects\\Wifi-Kurs\\WIFI-Sisharp-Training\\wifi.sisharp.training.web\\App_Data\\AndritzHydro2019.mdf")) { //Second: A command is needed using (var command = new System.Data.SqlClient.SqlCommand("GetCountries", connection)) { //Configure the command command.CommandType = System.Data.CommandType.StoredProcedure; command.Parameters.AddWithValue("@language", language); //The Sql Server should cache the procedure... command.Prepare(); //Don't forget: connection.Open(); //Third: (Not needed with INSERT, UPDATE or DELETE: command.ExecuteNonQuery() is used) // - only with SELECT using (var reader = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection)) { //Map the data to the data transfer objects while (reader.Read()) { result.Add(new Country { Code = reader["ISO"].ToString(), Name = reader["Name"].ToString(), MaxNumber = (int)reader["MaxNumber"], NumberCount = (int)reader["NumberCount"] }); } } } } return(result); }
public void Add(WaterDataValue waterDataValue) { using (SqlConnection conn = new SqlConnection(_connectionString)) { conn.Open(); SqlCommand command = new SqlCommand(null, conn); // Create and prepare an SQL statement. command.CommandText = "insert into WaterValues (Station_ID, DataType_ID, DateTime, Value) values (@station, @type, @date, @value)"; command.Parameters.Add("@station", SqlDbType.BigInt); command.Parameters["@station"].Value = waterDataValue.StationID; command.Parameters.Add("@type", SqlDbType.BigInt); command.Parameters["@type"].Value = waterDataValue.DataTypeID; command.Parameters.Add("@date", SqlDbType.DateTime); command.Parameters.Add("@value", SqlDbType.Float); command.Parameters["@date"].Value = waterDataValue.DateOf; command.Parameters["@value"].Value = waterDataValue.Value; command.Prepare(); // Calling Prepare after having set the Commandtext and parameters. command.ExecuteNonQuery(); } }