Example #1
0
        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;
        }
Example #2
0
        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;
            }
        }
Example #3
0
    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() });
    }
Example #4
0
        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 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>";
        }
    }
Example #6
0
        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>");
        }
    }
Example #8
0
    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";
        }
    }
Example #9
0
File: test.cs Project: mono/gert
	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;
		}
	}
Example #10
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);
     }
 }
 public static SqlCommand CommandStored(string query)
 {
     var sqlCo = new SqlCommand
     {
         CommandType = CommandType.StoredProcedure,
         Connection = GetConnection(),
         CommandText = query
     };
     sqlCo.Prepare();
     return sqlCo;
 }
Example #12
0
        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;
            }
        }
Example #13
0
 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();
 }
Example #14
0
        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();
        }
Example #16
0
 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);
         }
     }
 }
Example #17
0
 /// <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;
     }
 }
Example #18
0
        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;
            }
        }
Example #19
0
 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;
 }
Example #20
0
        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);
        }
Example #21
0
        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);
                    }
                }
            }
        }
        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();
            }
        }
Example #23
0
File: test.cs Project: mono/gert
	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;
		}
	}
Example #24
0
 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;
 }
Example #25
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;
 }
Example #26
0
    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");
    }
Example #27
0
    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() });
    }
Example #28
0
        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);
        }
Example #29
0
        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);
        }
 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();
     }
 }