public static DataTable GetHandoverLogByHandoverLogId(int handoverLogId)
        {
            string connectionString = ConnectionStringFactory.GetNXJCConnectionString();
            DataSet ds = new DataSet();

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = @"SELECT  WorkingTeamShiftLog.*,
                                                StaffInfo.Name AS ChargeManName, ProductLine.Name AS ProductLineName,
                                                WorkingTeam.Name AS WorkingTeamName
                                        FROM    WorkingTeam INNER JOIN
                                                StaffInfo INNER JOIN
                                                WorkingTeamShiftLog ON StaffInfo.ID = WorkingTeamShiftLog.ChargeManID INNER JOIN
                                                ProductLine ON WorkingTeamShiftLog.ProductLineID = ProductLine.ID ON
                                                WorkingTeam.ID = WorkingTeamShiftLog.WorkingTeamID
                                        WHERE   (WorkingTeamShiftLog.ID = @workingTeamShiftLogID)";

                command.Parameters.Add(new SqlParameter("workingTeamShiftLogID", handoverLogId));
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                adapter.Fill(ds);
            }

            return ds.Tables[0];
        }
Example #2
1
        public void PersistUpdateOf(IAggregateRoot entity)
        {
            Account account = entity as Account;

            string sql = "UPDATE Account SET [email protected] WHERE Id[email protected]";
            SqlParameter parameter1 = new SqlParameter("@Id", account.Id);
            SqlParameter parameter2= new SqlParameter("@Balance", account.Balance);

            using (SqlConnection conn = new SqlConnection(_connStr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    cmd.Parameters.Add(parameter1);
                    cmd.Parameters.Add(parameter2);

                    int affectedCount = cmd.ExecuteNonQuery();

                    if (affectedCount <= 0) 
                    {
                        throw new Exception("update Account failed");
                    }
                }
            }
        }
Example #3
1
        // This function will be used to execute CUD(CRUD) operation of parameterized commands
        internal static bool ExecuteNonQuery(string CommandName, CommandType cmdType, SqlParameter[] pars)
        {
            int result = 0;

            using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
            {
                using (SqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandType = cmdType;
                    cmd.CommandText = CommandName;
                    cmd.Parameters.AddRange(pars);

                    try
                    {
                        if (con.State != ConnectionState.Open)
                        {
                            con.Open();
                        }

                        result = cmd.ExecuteNonQuery();
                    }
                    catch
                    {
                        throw;
                    }
                }
            }
            return (result > 0);
        }
Example #4
1
        // This function will be used to execute R(CRUD) operation of parameterized commands
        internal static DataTable ExecuteParamerizedSelectCommand(string CommandName, CommandType cmdType, SqlParameter[] param)
        {
            DataTable table = new DataTable();

            using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
            {
                using (SqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandType = cmdType;
                    cmd.CommandText = CommandName;
                    cmd.Parameters.AddRange(param);

                    try
                    {
                        if (con.State != ConnectionState.Open)
                        {
                            con.Open();
                        }

                        using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                        {
                            da.Fill(table);
                        }
                    }
                    catch
                    {
                        throw;
                    }
                }
            }

            return table;
        }
Example #5
1
 public bool CreateUser(UserInfo userInfo)
 {
     var salt = _helper.GenerateSalt();
     var pas = _helper.EncodePassword(userInfo.Password, salt);
     using (var connection = new SqlConnection(_configurationService.DatabaseConnectionString))
     {
         using (var command = connection.CreateCommand())
         {
             command.CommandType = CommandType.StoredProcedure;
             command.CommandText = "sp_CreateUser";
             command.Parameters.AddWithValue("@Login", userInfo.Login).SqlDbType = SqlDbType.NVarChar;
             command.Parameters.AddWithValue("@Password", pas).SqlDbType = SqlDbType.NVarChar;
             command.Parameters.AddWithValue("@PasswordSalt", salt).SqlDbType = SqlDbType.NVarChar;
             command.Parameters.AddWithValue("@Email", userInfo.Email).SqlDbType = SqlDbType.NVarChar;
             command.Parameters.AddWithValue("@Fio", userInfo.Fio).SqlDbType = SqlDbType.NVarChar;
             command.Parameters.AddWithValue("@Address", userInfo.Address).SqlDbType = SqlDbType.NVarChar;
             command.Parameters.AddWithValue("@Phone", userInfo.Phone).SqlDbType = SqlDbType.NVarChar;
             command.Parameters.AddWithValue("@Mobile", userInfo.Mobile).SqlDbType = SqlDbType.NVarChar;
             command.Parameters.AddWithValue("@Country", userInfo.Country).SqlDbType = SqlDbType.Char;
             command.Parameters.AddWithValue("@Zip", userInfo.Zip).SqlDbType = SqlDbType.NVarChar;
             var retParam = command.Parameters.AddWithValue("@Return", SqlDbType.Int);
             retParam.Direction = ParameterDirection.ReturnValue;
             connection.Open();
             command.ExecuteNonQuery();
             return (int)retParam.Value == 1;
         }
     }
 }
Example #6
1
        public IEnumerable<UserInfoShort> EnumerateClients(string fio, string login, int pageNumber, int countPerPage)
        {
            using (var connection = new SqlConnection(_configurationService.DatabaseConnectionString))
            {
                using (var command = connection.CreateCommand())
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.CommandText = "sp_EnumerateClients";
                    command.Parameters.AddWithValue("@FIO", fio ?? (object)DBNull.Value).SqlDbType = SqlDbType.NVarChar;
                    command.Parameters.AddWithValue("@Login",
                        login != null ? login.ToLower() : (object)DBNull.Value).SqlDbType = SqlDbType.NVarChar;
                    command.Parameters.AddWithValue("@PageNumber", pageNumber).SqlDbType = SqlDbType.Int;
                    command.Parameters.AddWithValue("@CountPerPage", countPerPage).SqlDbType = SqlDbType.Int;
                    connection.Open();
                    using (var reader = command.ExecuteReader())
                    {
                        var lst = new List<UserInfoShort>();
                        while (reader.Read())
                        {
                            lst.Add(new UserInfoShort
                                {
                                    UserId = (Guid)reader["UserId"],
                                    CounOfCardAccounts = (int)reader["CounOfCardAccounts"],
                                    FIO = (string)reader["FIO"],
                                    Login = (string)reader["Login"],
                                    RegistrationDate = (DateTime)reader["RegistrationDate"]
                                });
                        }

                        return lst;
                    }
                }
            }
        }
        public void FixtureTearDown()
        {
            // Delete database
            SqlConnection cn = new SqlConnection(ConnString);
            cn.Open();

            SqlCommand cmd = cn.CreateCommand();
            cmd.CommandText = "alter database [ScrewTurnWikiTest] set single_user with rollback immediate";
            try {
                cmd.ExecuteNonQuery();
            }
            catch(SqlException sqlex) {
                Console.WriteLine(sqlex.ToString());
            }

            cmd = cn.CreateCommand();
            cmd.CommandText = "drop database [ScrewTurnWikiTest]";
            try {
                cmd.ExecuteNonQuery();
            }
            catch(SqlException sqlex) {
                Console.WriteLine(sqlex.ToString());
            }

            cn.Close();
        }
        public void FixtureTearDown()
        {
            // Delete database
            SqlConnection cn = new SqlConnection(ConnString);
            cn.Open();

            SqlCommand cmd = cn.CreateCommand();
            cmd.CommandText = "alter database [ScrewTurnWikiTest] set single_user with rollback immediate";
            try {
                cmd.ExecuteNonQuery();
            }
            catch(SqlException sqlex) {
                Console.WriteLine(sqlex.ToString());
            }

            cmd = cn.CreateCommand();
            cmd.CommandText = "drop database [ScrewTurnWikiTest]";
            try {
                cmd.ExecuteNonQuery();
            }
            catch(SqlException sqlex) {
                Console.WriteLine(sqlex.ToString());
            }

            cn.Close();

            // This is neede because the pooled connection are using a session
            // that is now invalid due to the commands executed above
            SqlConnection.ClearAllPools();
        }
Example #9
0
        public Response DB(dynamic parameters)
        {
            ConnectionStringSettings css = ConfigurationManager.ConnectionStrings["Default"];

            var sb = new StringBuilder("<html><body><pre>");
            try
            {
                if (null != css && false == String.IsNullOrWhiteSpace(css.ConnectionString))
                {
                    using (var conn = new SqlConnection(css.ConnectionString))
                    {
                        conn.Open();
                        using (var cmd = conn.CreateCommand())
                        {
                            cmd.CommandText = "SELECT GETDATE()";
                            sb.AppendLine(cmd.ExecuteScalar().ToString());
                        }
                        using (var cmd = conn.CreateCommand())
                        {
                            cmd.CommandText = "SELECT @@VERSION";
                            sb.AppendLine(cmd.ExecuteScalar().ToString());
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                sb.Append(ex.Message);
            }
            sb.Append("</pre></body></html>");
            return sb.ToString();
        }
Example #10
0
 protected void Associate_Authenticate(object sender, AuthenticateEventArgs e)
 {
     SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["AssociateConn"].ConnectionString);
     SqlCommand cmd = sqlConn.CreateCommand();
     cmd.CommandType = System.Data.CommandType.StoredProcedure;
     cmd.CommandText = "dbo.Get_Login";
     cmd.Parameters.AddWithValue("@id", AssociateLogin.UserName);
     cmd.Parameters.AddWithValue("@password", AssociateLogin.Password);
     if (sqlConn.State != ConnectionState.Open)
         sqlConn.Open();
     object o = cmd.ExecuteScalar();
     int Authenticated = Convert.ToInt32(cmd.ExecuteScalar());
     if (Authenticated == 1)
     {
         HttpContext.Current.Session["UserName"] = AssociateLogin.UserName.ToString();
         SqlCommand cmdRole = sqlConn.CreateCommand();
         cmdRole.CommandType = System.Data.CommandType.StoredProcedure;
         cmdRole.CommandText = "dbo.Get_RoleByUserID";
         cmdRole.Parameters.AddWithValue("@id", AssociateLogin.UserName);
         SqlDataReader rdr = cmdRole.ExecuteReader();
         while (rdr.Read())
         {
             HttpContext.Current.Session["Role"] = rdr["RoleDesc"].ToString();
             HttpContext.Current.Session["IsAdmin"] = rdr["IsAdmin"].ToString();
             HttpContext.Current.Session.Timeout = 15;
         }
         rdr.Close();
         Response.Redirect("~/default.aspx");
     }
     sqlConn.Close();
 }
Example #11
0
        public bool altaRol(Rol rol)
        {
            using(TransactionScope ts = new TransactionScope())
            using(SqlConnection conn = new SqlConnection(Settings.Default.ConnectionString))
            using (SqlCommand cmd = conn.CreateCommand())
                try
                {
                    cmd.CommandText = "INSERT INTO BONDIOLA.Rol (nombre) VALUES (@nombreRol)";
                    cmd.Parameters.Add(new SqlParameter("@nombreRol", rol.nombre));
                    conn.Open();
                    cmd.ExecuteNonQuery();

                    foreach (string funcionalidad in rol.funcionalidades)
                    {
                        SqlCommand cmdFuncionalidad = conn.CreateCommand();
                        cmdFuncionalidad.CommandType = CommandType.StoredProcedure;
                        cmdFuncionalidad.CommandText = "BONDIOLA.altaFuncionalidadRol";
                        cmdFuncionalidad.Parameters.AddWithValue("@funcionalidad", funcionalidad);
                        cmdFuncionalidad.Parameters.AddWithValue("@rol", rol.nombre);
                        cmdFuncionalidad.ExecuteNonQuery();
                    }
                    ts.Complete();
                    MessageBox.Show("Rol guardado con éxito.");
                    return true;
                }
                catch (SqlException ex)
                {
                    if (ex.Message.Contains("IX_Rol"))
                        MessageBox.Show("Ya existe un rol en el sistema con el nombre ingresado");
                    else MessageBox.Show(ex.Message);
                    return false;
                }
        }
Example #12
0
        public void SetupFixture()
        {
            SqlConnectionStringBuilder connStrBuilder = new SqlConnectionStringBuilder(UnitTests.Properties.Settings.Default.SqlServer2008);
            if (string.IsNullOrEmpty(connStrBuilder.DataSource) || string.IsNullOrEmpty(connStrBuilder.InitialCatalog))
            {
                Assert.Ignore("Requires SQL Server connectionstring");
            }

            GeoAPI.GeometryServiceProvider.Instance = new NetTopologySuite.NtsGeometryServices();

            // Set up sample table
            using (SqlConnection conn = new SqlConnection(UnitTests.Properties.Settings.Default.SqlServer2008))
            {
                conn.Open();
                using(SqlCommand cmd = conn.CreateCommand())
                {
                    // The ID column cannot simply be int, because that would cause GetOidsInView to fail. The provider internally works with uint
                    cmd.CommandText = "CREATE TABLE roads_ugl(ID decimal(10,0) identity(1,1) PRIMARY KEY, NAME nvarchar(100), GEOM geometry)";
                    cmd.ExecuteNonQuery();
                }

                // Load data
                using (SharpMap.Data.Providers.ShapeFile shapeFile = new SharpMap.Data.Providers.ShapeFile(GetTestFile()))
                {
                    shapeFile.Open();

                    IEnumerable<uint> indexes = shapeFile.GetOidsInView(shapeFile.GetExtents());

                    indexes = indexes.Take(100);

                    foreach (uint idx in indexes)
                    {
                        var feature = shapeFile.GetFeatureByOid(idx);

                        using (SqlCommand cmd = conn.CreateCommand())
                        {
                            cmd.CommandText = "INSERT INTO roads_ugl(NAME, GEOM) VALUES (@Name, geometry::STGeomFromText(@Geom, @Srid))";

                            cmd.Parameters.AddWithValue("@Geom", feature.Geometry.AsText());
                            cmd.Parameters.AddWithValue("@Name", feature.Attributes["NAME"]);
                            cmd.Parameters.AddWithValue("@Srid", shapeFile.SRID);
                            cmd.ExecuteNonQuery();
                        }
                    }
                }

                // Create spatial index
                using(SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "CREATE SPATIAL INDEX [IX_roads_ugl_GEOM] ON [dbo].[roads_ugl](GEOM)USING  GEOMETRY_GRID WITH (BOUNDING_BOX =(-98, 40, -82, 50), GRIDS =(LEVEL_1 = MEDIUM,LEVEL_2 = MEDIUM,LEVEL_3 = MEDIUM,LEVEL_4 = MEDIUM))";
                    cmd.ExecuteNonQuery();
                }
            }
        }
Example #13
0
 public void SetupClass()
 {
     SqlConnection = new SqlConnection(@"Data Source=(localdb)\v11.0;Integrated Security=True");
     SqlConnection.Open();
     string createDatabase =
         string.Format("if not exists(select * from sys.databases where name = '{0}') CREATE DATABASE {0};", DataBaseName);
     var cmd = SqlConnection.CreateCommand();
     cmd.CommandText = createDatabase;
     cmd.ExecuteNonQuery();
     cmd = SqlConnection.CreateCommand();
     cmd.CommandText = "USE " + DataBaseName;
     cmd.ExecuteNonQuery();
 }
        public static XElement ModifyTelephoneIdentificationCode(Session session, Guid accountId, string oldCode, string newCode)
        {
            bool lastResult = false;
            try
            {
                Token token = SessionManager.Default.GetToken(session);
                string connectionString = SettingManager.Default.ConnectionString;
                using (SqlConnection sqlconnection = new SqlConnection(connectionString))
                {
                    SqlCommand sqlCommand = sqlconnection.CreateCommand();
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.CommandText = "Account_UpdateDescription";
                    SqlCommandBuilder.DeriveParameters(sqlCommand);
                    sqlCommand.Parameters["@id"].Value = accountId;
                    sqlCommand.Parameters["@oldDescription"].Value = oldCode;
                    sqlCommand.Parameters["@newDescription"].Value = newCode;

                    sqlCommand.ExecuteNonQuery();
                    int result = (int)sqlCommand.Parameters["@RETURN_VALUE"].Value;
                    if (result == 0)
                    {
                        sqlCommand = sqlconnection.CreateCommand();
                        sqlCommand.CommandType = CommandType.Text;
                        sqlCommand.CommandText = string.Format("UPDATE AccountHistory SET UpdatePersonID = '{0}' WHERE ID = '{1}' AND [Description] = '{2}' AND UpdateTime = (SELECT MAX(UpdateTime) FROM AccountHistory WHERE ID='{1}' AND [Description] = '{2}')", token.UserID, accountId, newCode);
                        sqlCommand.ExecuteNonQuery();
                        lastResult = true;
                    }
                    else
                    {
                        //maybe the accountId is an employee id
                        sqlCommand = sqlconnection.CreateCommand();
                        sqlCommand.CommandType = CommandType.StoredProcedure;
                        sqlCommand.CommandText = "Employee_UpdateTelephonePin";
                        sqlconnection.Open();
                        SqlCommandBuilder.DeriveParameters(sqlCommand);
                        sqlCommand.Parameters["@id"].Value = accountId;
                        sqlCommand.Parameters["@oldPin"].Value = oldCode;
                        sqlCommand.Parameters["@newPin"].Value = newCode;

                        sqlCommand.ExecuteNonQuery();
                        result = (int)sqlCommand.Parameters["@RETURN_VALUE"].Value;
                        lastResult = (result == 0);
                    }
                }
            }
            catch (System.Exception ex)
            {
                _Logger.Error(ex);
            }
            return XmlResultHelper.NewResult(lastResult.ToPlainBitString());
        }
Example #15
0
        private void ConfigureDataAdapter()
        {
            con = new SqlConnection(cs);

            adapter.SelectCommand = con.CreateCommand();
            adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
            adapter.SelectCommand.CommandText = "DohvatiSveTablice";

            adapter.InsertCommand = con.CreateCommand();
            adapter.InsertCommand.CommandType = CommandType.StoredProcedure;
            adapter.InsertCommand.CommandText = "DodajBolid";

            SqlParameter paramIDVozac = new SqlParameter();
            paramIDVozac.ParameterName = "@IDVozac";
            paramIDVozac.SqlDbType = SqlDbType.Int;
            paramIDVozac.SourceColumn = "IDVozac";
            SqlParameter paramNaziv = new SqlParameter();
            paramNaziv.ParameterName = "@Naziv";
            paramNaziv.SqlDbType = SqlDbType.NVarChar;
            paramNaziv.SourceColumn = "Naziv";

            adapter.InsertCommand.Parameters.Add(paramIDVozac);
            adapter.InsertCommand.Parameters.Add(paramNaziv);

            adapter.UpdateCommand = con.CreateCommand();
            adapter.UpdateCommand.CommandType = CommandType.StoredProcedure;
            adapter.UpdateCommand.CommandText = "UpdateBolid";

            SqlParameter paramUpdateID = new SqlParameter();
            paramUpdateID.ParameterName = "@ID";
            paramUpdateID.SqlDbType = SqlDbType.Int;
            paramUpdateID.SourceColumn = "ID";
            SqlParameter paramUpdateNaziv = new SqlParameter();
            paramUpdateNaziv.ParameterName = "@Naziv";
            paramUpdateNaziv.SqlDbType = SqlDbType.NVarChar;
            paramUpdateNaziv.SourceColumn = "Naziv";

            adapter.UpdateCommand.Parameters.Add(paramUpdateNaziv);
            adapter.UpdateCommand.Parameters.Add(paramUpdateID);

            adapter.DeleteCommand = con.CreateCommand();
            adapter.DeleteCommand.CommandType = CommandType.StoredProcedure;
            adapter.DeleteCommand.CommandText = "BrisiBolid";

            SqlParameter paramDeleteID = new SqlParameter();
            paramDeleteID.ParameterName = "@ID";
            paramDeleteID.SqlDbType = SqlDbType.Int;
            paramDeleteID.SourceColumn = "ID";

            adapter.DeleteCommand.Parameters.Add(paramDeleteID);
        }
Example #16
0
        public void CreateCommandTests()
        {
            var connection = new SqlConnection();
            var command = connection.CreateCommand("ABCDEFG");
            Assert.AreEqual("ABCDEFG", command.CommandText);
            Assert.AreSame(connection, command.Connection);

            command = connection.CreateCommand("123", new { IntParam = 57, DateParam = DateTime.MaxValue });
            Assert.AreEqual("123", command.CommandText);
            Assert.AreSame(connection, command.Connection);
            Assert.AreEqual(2, command.Parameters.Count);
            Assert.AreEqual(57, command.Parameters["IntParam"].Value);
            Assert.AreEqual(DateTime.MaxValue, command.Parameters["DateParam"].Value);
        }
Example #17
0
        /// <summary>
        /// Restores a SQL database from a backup file.
        /// Warning: closes all existing connections
        /// </summary>
        /// <param name="databaseName"></param>
        /// <param name="backUpFile">Backup File accesible from the SQL Server</param>
        /// <param name="serverName">SQL server instance</param>
        /// <param name="userName"></param>
        /// <param name="password"></param>
        public static void Restore(string databaseName, string backUpFile, string serverName, string userName = null, string password = null)
        {
            try
            {
                Console.WriteLine("Restoring database " + databaseName + " at " + serverName + " ...");

                string connString = "Data Source=" + serverName + ";Initial Catalog=master;";

                if (string.IsNullOrEmpty(userName))
                {
                    connString += "Integrated Security=True;";
                }
                else
                {
                    connString += string.Format("User ID={0};Password={1};", userName, password);
                }

                using (SqlConnection conn = new SqlConnection(connString))
                {
                    SqlCommand command = conn.CreateCommand();
                    command = conn.CreateCommand();
                    command.CommandTimeout = 3 * 60;
                    command.CommandType = System.Data.CommandType.Text;

                    // check if db exists
                    command.CommandText = "SELECT COUNT(*) FROM sys.databases WHERE name = '" + databaseName + "'";

                    conn.Open();

                    if (command.ExecuteScalar() as int? == 1)
                    {
                        // db exists, set single user
                        command.CommandText = "ALTER DATABASE [" + databaseName + "] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE";
                        command.ExecuteNonQuery();
                    }

                    command.CommandText = "RESTORE DATABASE [" + databaseName + "] FROM  DISK = N'" +
                        backUpFile + "' WITH FILE = 1,  NOUNLOAD, REPLACE, STATS = 10";
                    command.ExecuteNonQuery();

                    conn.Close();
                }
                Console.WriteLine("Database restored successfully");
            }
            catch (Exception ex)
            {
                Console.WriteLine("Database restore failed: " + ex.Message);
                throw;
            }
        }
 //Se realiza el alta de un profesional dado
 public bool altaProfesional(Profesional profesional)
 {
     using (SqlConnection conn = new SqlConnection(Settings.Default.ConnectionString))
     using (TransactionScope ts = new TransactionScope())
     {
         SqlCommand cmd = conn.CreateCommand();
         cmd.CommandText = "BONDIOLA.altaProfesional";
         cmd.CommandType = CommandType.StoredProcedure;
         cmd.Parameters.AddWithValue("@nombre", profesional.nombre);
         cmd.Parameters.AddWithValue("@apellido", profesional.apellido);
         cmd.Parameters.AddWithValue("@tipoDocumento", profesional.tipoDeDocumento);
         cmd.Parameters.AddWithValue("@numeroDocumento", profesional.numeroDeDocumento);
         cmd.Parameters.AddWithValue("@fechaNacimiento", profesional.fechaDeNacimiento);
         cmd.Parameters.AddWithValue("@direccion", profesional.direccion);
         cmd.Parameters.AddWithValue("@mail", profesional.mail);
         cmd.Parameters.AddWithValue("@telefono", profesional.telefono);
         cmd.Parameters.AddWithValue("@sexo", profesional.sexo);
         cmd.Parameters.AddWithValue("@matricula", profesional.matricula);
         cmd.Parameters.AddWithValue("@nombreUsuario", profesional.usuario);
         cmd.Parameters.AddWithValue("@hashContrasenia", Utils.hashearSHA256(profesional.contraseña));
         try
         {
             conn.Open();
             int idGenerado = Convert.ToInt32(cmd.ExecuteScalar());
             foreach (var especialidad in profesional.especialidades)
             {
                 SqlCommand cmdEsp = conn.CreateCommand();
                 cmdEsp.CommandType = CommandType.StoredProcedure;
                 cmdEsp.CommandText = "BONDIOLA.altaProfesionalEspecialidad";
                 cmdEsp.Parameters.AddWithValue("@idProfesional", idGenerado);
                 cmdEsp.Parameters.AddWithValue("@especialidad", especialidad);
                 cmdEsp.ExecuteNonQuery();
             }
             MessageBox.Show("Operación realizada con éxito.");
             ts.Complete();
         }
         catch (SqlException ex)
         {
             string error = "";
             if (ex.Message.Contains("Constraint_Documento"))
                 error = "Ya existe un profesional con el número de documento " + profesional.numeroDeDocumento;
             else if (ex.Message.Contains("Constraint_Matricula"))
                 error = "Ya existe un profesional con la matrícula " + profesional.matricula;
             MessageBox.Show(error+Environment.NewLine+ex.Message);
             return false;
         }
     }
     return true;
 }
        public void ProcessRequest(HttpContext context)
        {
            string idStr = context.Request.Params["id"];
            string name = context.Request.Params["name"];
            int id = -1;
            string xml = null;

            using (var conn = new SqlConnection(ConfigurationManager.AppSettings["SdeConnectionString"]))
            {
                if (!string.IsNullOrWhiteSpace(idStr) && int.TryParse(idStr, out id))
                {
                    conn.Open();
                    var cmd = conn.CreateCommand();
                    cmd.CommandText = Resources.GetMetadataById;
                    cmd.Parameters.AddWithValue("@id", id);
                    xml = cmd.ExecuteScalar() as string;
                }
                else if (!string.IsNullOrWhiteSpace(name))
                {
                    Match match = _nameRe.Match(name);
                    if (match.Success)
                    {
                        conn.Open();
                        var cmd = conn.CreateCommand();
                        cmd.CommandText = Resources.GetMetadataByName;
                        cmd.Parameters.AddWithValue("@databaseName", match.Groups["databaseName"].Value);
                        cmd.Parameters.AddWithValue("@owner", match.Groups["owner"].Value);
                        cmd.Parameters.AddWithValue("@name", match.Groups["name"].Value);
                        xml = cmd.ExecuteScalar() as string;
                    }
                }
            }

            var xsl = new XslCompiledTransform();
            xsl.Load(HttpContext.Current.Server.MapPath("~/style/FGDC Plus HTML5.xsl"));

            var doc = new XmlDocument();
            doc.LoadXml(xml);

            context.Response.ContentType = "text/html";
            XsltArgumentList args = new XsltArgumentList();
            args.AddParam("includeDublinCore", string.Empty, false);
            args.AddParam("externalJS", string.Empty, "scripts/fgdcPlus.js");
            args.AddParam("externalCss", string.Empty, "style/fgdcPlus.css");
            xsl.Transform(doc, args, context.Response.OutputStream);

            ////context.Response.ContentType = "text/xml";
            ////context.Response.Write(xml);
        }
Example #20
0
        static void Main(string[] args)
        {
            string connectString = @"Data Source = ASPIRE-5560G\ZLSQL; Initial Catalog = DBSlides; Integrated Security = True";

            try
            {
                SqlConnection db = new SqlConnection();
                db.ConnectionString = connectString;

                string dmlQueryDel = "DELETE FROM student WHERE student_id > 25";
                string dmlQueryIns = "INSERT INTO student (student_id, first_name, last_name, birth_date, login, section_id, year_result, course_id) VALUES (26, 'Darren', 'Aronofsky', '19650513', 'darono', 1020, 18, 'EG2110')";

                SqlCommand delCmd = db.CreateCommand();
                delCmd.CommandText = dmlQueryDel;

                db.Open();
                try
                {
                    int delRows = delCmd.ExecuteNonQuery();
                    Console.WriteLine(delRows + " lignes affectées par le DELETE");
                }
                catch (Exception ex)
                {
                    Console.WriteLine(string.Format("DELETE échoué\n{0}", ex.Message));
                }
                db.Close();

                SqlCommand insCmd = db.CreateCommand();
                insCmd.CommandText = dmlQueryIns;

                db.Open();
                try
                {
                    int insRows = insCmd.ExecuteNonQuery();
                    Console.WriteLine(insRows + " lignes affectées par le INSERT");
                }
                catch (Exception ex)
                {
                    Console.WriteLine(string.Format("INSERT échoué\n{0}", ex.Message));
                }
                db.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }

            Console.ReadKey();
        }
Example #21
0
        internal static SqlDataAdapter CreateAndConfigDataAdapter(DataSet dataSet, SqlConnection conn, string tableName, string selCommand, KeyValuePair<string, SqlDbType> keyCol)
        {
            var adapter = new SqlDataAdapter { SelectCommand = conn.CreateCommand() };

            adapter.SelectCommand.CommandText = selCommand;
            adapter.TableMappings.Add("Table", tableName);
            adapter.Fill(dataSet);

            Dictionary<string, SqlDbType> cols = GetColsDictionary(dataSet, tableName);

            var sb = new StringBuilder();
            var sbPar = new StringBuilder();
            sb.AppendFormat("INSERT INTO {0} (", tableName);
            foreach (var col in cols)
            {
                sb.AppendFormat("{0},", col.Key);
                sbPar.AppendFormat("@{0},", col.Key);
            }
            sb.Remove(sb.Length - 1, 1);
            sbPar.Remove(sbPar.Length - 1, 1);
            sb.AppendFormat(") VALUES({0})", sbPar);

            adapter.InsertCommand = conn.CreateCommand();
            adapter.InsertCommand.CommandText = sb.ToString();
            AddParams(adapter.InsertCommand, cols);

            sb.Remove(0, sb.Length);
            sb.AppendFormat("UPDATE {0} SET ", tableName);
            if (cols.ContainsKey(keyCol.Key)) cols.Remove(keyCol.Key);
            foreach (KeyValuePair<string, SqlDbType> col in cols)
            {
                sb.AppendFormat("{0}[email protected]{1},", col.Key, col.Key);
            }
            sb.Remove(sb.Length - 1, 1);
            cols.Add(keyCol.Key, keyCol.Value);
            sb.AppendFormat(" WHERE {0}[email protected]{1}", keyCol.Key, keyCol.Key);
            adapter.UpdateCommand = conn.CreateCommand();
            adapter.UpdateCommand.CommandText = sb.ToString();
            AddParams(adapter.UpdateCommand, cols);

            sb.Remove(0, sb.Length);
            sb.AppendFormat("DELETE FROM {0} WHERE {1}[email protected]{2}", tableName, keyCol.Key, keyCol.Key);
            adapter.DeleteCommand = conn.CreateCommand();
            adapter.DeleteCommand.CommandText = sb.ToString();
            var keyPar = new Dictionary<string, SqlDbType> { { keyCol.Key, keyCol.Value } };
            AddParams(adapter.DeleteCommand, keyPar);
            return adapter;
        }
        public static void AddShortageDetail(ShortageDetails sd)
        {
            SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString);
            SqlCommand command;
            connection.Open();
            try
            {
                command = connection.CreateCommand();

                command.CommandText = "INSERT INTO UnileverInvoiceTrackingSystem.dbo.shortagedetails (invoiceno, shortagedetails) VALUES (@IN,@SD);";

                command.Parameters.AddWithValue("@IN", Convert.ToInt64(sd.InvoiceNo));

                command.Parameters.AddWithValue("@SD", sd.SD);

                command.ExecuteNonQuery();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
            }
        }
        public string[] GetSubscribers(Type messageType)
        {
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();

                using (var command = connection.CreateCommand())
                {
                    command.CommandText = string.Format(@"select endpoint from [{0}]
                                                where message_type = @message_type", subscriptionsTableName);

                    command.Parameters.AddWithValue("message_type", messageType.FullName);

                    var endpoints = new List<string>();
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            endpoints.Add((string)reader["endpoint"]);
                        }
                    }
                    return endpoints.ToArray();
                }
            }
        }
        public static string GetCompanyData()
        {
            string connString = ConnectionStringFactory.NXJCConnectionString;   //填写链接字符串
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(connString))
            {
                //conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT KeyID,CementTypes,vDate,OutputFirstTeam,OutputSecondTeam,OutputThirdTeam,OutputAmountto FROM table_CementMillProductionLineMonthlyOutput";                          //填写查询语句
                SqlDataAdapter da = new SqlDataAdapter(cmd);

                da.Fill(dt);
            }

            DataGridColumnType columnType = new DataGridColumnType
            {
                ColumnText = new string[] { "ID", "日期", "水泥品种", "甲班产量", "乙班产量", "丙班产量", "合计产量" },                                 //填写表头及宽度
                ColumnWidth = new int[] { 10, 100, 100, 100,100, 100, 100 },
                ColumnType = new string[] { "", "", "", "", "", "","" }
            };

            string result = ReportTemplateHelper.GetDataGridTemplate(dt, columnType);

            return result;
        }
		private static void SetupSqlServer(Cfg.Configuration cfg)
		{
			var connStr = cfg.Properties[Cfg.Environment.ConnectionString];

			using (var conn = new SqlConnection(connStr.Replace("initial catalog=nhibernate", "initial catalog=master")))
			{
				conn.Open();

				using (var cmd = conn.CreateCommand())
				{
					cmd.CommandText = "drop database nhibernate";

					try
					{
						cmd.ExecuteNonQuery();
					}
					catch(Exception e)
					{
						Console.WriteLine(e);
					}

					cmd.CommandText = "create database nhibernate";
					cmd.ExecuteNonQuery();
				}
			}
		}
Example #26
0
        public List<User> FindAll()
        {
            List<User> Users = new List<User>();
            string query = "SELECT * FROM [User]";

            using (SqlConnection db = new SqlConnection(CONN_STR))
            {
                if (db.State != System.Data.ConnectionState.Open)
                {
                    db.Open();
                }

                using (SqlCommand cmd = db.CreateCommand())
                {
                    cmd.CommandText = query;

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            User u = new User();
                            u.UserID = reader.GetInt32(reader.GetOrdinal("UserID"));
                            u.Username = reader.GetString(reader.GetOrdinal("Username"));
                            u.Password = reader.GetString(reader.GetOrdinal("Password"));
                            u.IsAdmin = reader.GetBoolean(reader.GetOrdinal("IsAdmin"));
                            Users.Add(u);
                        }
                    }
                }
            }
            return Users;
        }
Example #27
0
 private void button1_Click(object sender, EventArgs e)
 {
     string connStr = "Data Source=(local);Initial Catalog=MaoMaoFriends;Persist Security Info=True;User ID=RAY\Administrator;Password=''";
     SqlConnection myconn = new SqlConnection(connStr);
     SqlCommand mycomm = myconn.CreateCommand();
     myconn.Open();
 }
Example #28
0
        public Account GetById(int id)
        {
            string sql = "SELECT Id, Balance FROM Account WHERE [email protected]";
            SqlParameter parameter = new SqlParameter("@Id", id);
            Account account = new Account();

            using (SqlConnection conn = new SqlConnection(_connStr))
            {
                conn.Open();
                using(SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    cmd.Parameters.Add(parameter);

                    SqlDataReader reader = cmd.ExecuteReader();
                    if (reader.Read()) 
                    {
                        account.Id = id;
                        account.Balance = Convert.ToDecimal(reader["Balance"]);
                    }
                }
            }

            return account;
        }
    protected void Button1_Click(object sender, EventArgs e)
    {
        SqlTransaction myTransaction = null;
        {
            try
            {
                SqlConnection conn = new SqlConnection(@"Data Source=ajjpsqlserverdb.db.4338448.hostedresource.com; database=ajjpsqlserverdb;
                                                            User ID=ajjpsqlserverdb; Password= Devry2010;");

                conn.Open();
                SqlCommand command = conn.CreateCommand();
                string strSQL;
                string txtBoxText = TextBox1.Text;
                txtBoxText = txtBoxText.Replace("'", "''");

                myTransaction = conn.BeginTransaction();
                command.Transaction = myTransaction;

                strSQL = "UPDATE aspnet_Membership SET Password = '" + txtBoxText + "' WHERE UserID = '" + DropDownList1.SelectedValue + "'";

                command.CommandType = System.Data.CommandType.Text;
                command.CommandText = strSQL;
                command.ExecuteNonQuery();

                myTransaction.Commit();

                command.Connection.Close();
                Response.Redirect("~/User/Main.aspx");
            }
            catch (Exception ex)
            {
                lblErr.Text = ex.Message;
            }
        }
    }
Example #30
-1
        private void boutonSave_Click(object sender, EventArgs e)
        {
            try
            {
                DateTime dateCommentaire = Convert.ToDateTime(inputDate.Text);

                SqlConnection db = new SqlConnection();
                db.ConnectionString = connectionString;

                SqlCommand sql = db.CreateCommand();
                sql.CommandText = "SELECT MAX(Clé)+1 FROM Inscrits";
                db.Open();
                object id = sql.ExecuteScalar();
                db.Close();

                string query = "set dateformat mdy;" +
                               "INSERT INTO CoachingComments VALUES ('" + _cleSession + "',null,'" + inputCommentaire.Text + "','" + dateCommentaire.ToString(System.Globalization.CultureInfo.GetCultureInfo("en-US")) + "')";
                SqlCommand cmd = db.CreateCommand();
                cmd.CommandText = query;

                db.Open();
                cmd.ExecuteNonQuery();
                db.Close();
            }
            catch (SqlException sqlex)
            {
                MessageBox.Show("Une erreur SQL est survenue: " + sqlex.Message, "Erreur 7", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            catch (Exception ex)
            {
                MessageBox.Show("Une erreur est survenue: " + ex.Message, "Erreur 8", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            this.Close();
        }