Ejemplo n.º 1
3
        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];
        }
Ejemplo n.º 2
2
        public void PersistUpdateOf(IAggregateRoot entity)
        {
            Account account = entity as Account;

            string sql = "UPDATE Account SET Balance=@Balance WHERE Id=@Id";
            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");
                    }
                }
            }
        }
Ejemplo n.º 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);
        }
Ejemplo n.º 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;
        }
Ejemplo n.º 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;
         }
     }
 }
Ejemplo n.º 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;
                    }
                }
            }
        }
Ejemplo n.º 7
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();
        }
Ejemplo n.º 8
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;
                }
        }
        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();
        }
Ejemplo n.º 11
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();
 }
Ejemplo n.º 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();
                }
            }
        }
Ejemplo n.º 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();
 }
Ejemplo n.º 14
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);
        }
Ejemplo n.º 15
0
        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());
        }
Ejemplo n.º 16
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;
            }
        }
Ejemplo n.º 17
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);
        }
        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);
        }
Ejemplo n.º 19
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();
        }
Ejemplo n.º 20
0
 //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;
 }
Ejemplo n.º 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}=@{1},", col.Key, col.Key);
            }
            sb.Remove(sb.Length - 1, 1);
            cols.Add(keyCol.Key, keyCol.Value);
            sb.AppendFormat(" WHERE {0}=@{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}=@{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 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;
        }
Ejemplo n.º 23
0
		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();
				}
			}
		}
Ejemplo n.º 24
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();
 }
    protected void Button1_Click(object sender, EventArgs e)
    {
        SqlTransaction myTransaction = null;
        {
            try
            {
                SqlConnection conn = new SqlConnection(@"Data Source=ajjpsqlserverdb.db.4338448.hostedresource.com; database=ajjpsqlserverdb;
                                                            User ID=ajjpsqlserverdb; Password= Devry2010;");

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

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

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

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

                myTransaction.Commit();

                command.Connection.Close();
                Response.Redirect("~/User/Main.aspx");
            }
            catch (Exception ex)
            {
                lblErr.Text = ex.Message;
            }
        }
    }
        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();
                }
            }
        }
Ejemplo n.º 27
0
        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();
                }
            }
        }
Ejemplo n.º 28
0
        public Account GetById(int id)
        {
            string sql = "SELECT Id, Balance FROM Account WHERE Id=@Id";
            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;
        }
Ejemplo n.º 29
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;
        }
        /// <summary>
        /// Créer l'enregistrement en base en récupérant la valeur de la clé primaire créée automatiquement.
        /// </summary>
        /// <param string="sRequest">Chemin du dossier à ziper.</param>
        /// <param PNPU_PROCESS="input">Paramétres récupérés depuis le Web Service</param>
        /// <param string="sTable">Nom de la table impacté par l'insertion</param>
        /// <returns>Retourne 0 si ok, -1 en cas de problème.</returns>
        public static string SendTransactionWithGetLastPKid(string sRequest, PNPU_PROCESS input, string sTable)
        {
            using (var conn = new System.Data.SqlClient.SqlConnection(ParamAppli.ConnectionStringBaseAppli))
            {
                string LastInsertedPK = "";
                conn.Open();

                SqlCommand     command = conn.CreateCommand();
                SqlTransaction transaction;

                // Start a local transaction.
                transaction = conn.BeginTransaction("CreateProcess");

                // Must assign both transaction object and connection
                // to Command object for a pending local transaction
                command.Connection  = conn;
                command.Transaction = transaction;
                try
                {
                    command.CommandText = sRequest;

                    /*for (int i = 0; i < sParameters.Length; i++)
                     * {
                     *  sParameters[i].ToString();
                     *  command.Parameters.Add("@" + sParameters[i].ToString(), SqlDbType.VarChar, 254).Value = sParameters[i].ToString();
                     * }*/
                    command.Parameters.Add("@PROCESS_LABEL", SqlDbType.VarChar, 254).Value = input.PROCESS_LABEL;
                    command.Parameters.Add("@IS_LOOPABLE", SqlDbType.VarChar, 254).Value   = input.IS_LOOPABLE;
                    command.ExecuteNonQuery();
                    command.CommandText = "SELECT IDENT_CURRENT('" + sTable + "') AS [IDENT_CURRENT]";
                    LastInsertedPK      = command.ExecuteScalar().ToString();
                    transaction.Commit();
                    Console.WriteLine(LastInsertedPK);
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Commit Exception Type: {0}", ex.GetType());
                    Console.WriteLine("  Message: {0}", ex.Message);
                    try
                    {
                        transaction.Rollback();
                        return("Commit Exception Type: {0}" + ex.GetType());
                    }
                    catch (Exception ex2)
                    {
                        Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
                        Console.WriteLine("  Message: {0}", ex2.Message);
                        return("Rollback Exception Type: {0}" + ex2.GetType());
                    }
                }
                return(LastInsertedPK);
            }
        }
Ejemplo n.º 31
0
        public Usuario update(Usuario usuario)
        {
            Usuario      u   = null;
            const string SQL = "";

            using (SqlConnection conexion = new System.Data.SqlClient.SqlConnection())
            {
                SqlCommand command = conexion.CreateCommand();
            }

            return(u);
        }
Ejemplo n.º 32
0
        private void buscarRfid(string rfid)
        {
            try
            {
                con.Open();
                SqlCommand query = con.CreateCommand();
                query.CommandType = CommandType.Text;
                //Consulta sobre tarjetas de personas
                if (step == 1)
                {
                    query.CommandText = string.Format("EXEC BUSCAR_PERSONA_PRESTAMO '" + rfid + "'");
                    SqlDataReader busqueda;
                    busqueda = query.ExecuteReader();
                    while (busqueda.Read() == true)
                    {
                        lbNombre.Text = busqueda["Nombre"].ToString();
                        valido        = 1;
                    }
                }
                //Consulta sobre tarjetas de articulos
                else if (step == 2)
                {
                    query.CommandText = string.Format("EXEC BUSCAR_ARTICULO_PRESTAMO '" + rfid + "'");
                    SqlDataReader busqueda;
                    busqueda = query.ExecuteReader();
                    while (busqueda.Read() == true)
                    {
                        lbNombre.Text = busqueda["Nombre"].ToString();
                        valido        = 1;

                        infoArt[0] = busqueda["Nombre"].ToString();
                        infoArt[1] = busqueda["Descripcion"].ToString();
                        infoArt[2] = busqueda["Categoria"].ToString();
                        infoArt[3] = busqueda["Precio_renta"].ToString();
                        infoArt[4] = busqueda["Stock"].ToString();
                        infoArt[5] = busqueda["Disponible"].ToString();
                    }
                }
                else if (step == 3)
                {
                }
            }
            catch
            {
            }
            finally
            {
                if (con.State != ConnectionState.Closed)
                {
                    con.Close();
                }
            }
        }
Ejemplo n.º 33
0
        private void OpenWithCheck(string checkQueryString)
        {
            var connectionChecked = false;
            var restoreTriggered  = false;

            while (!connectionChecked)
            {
                base.Open();
                try {
                    using (var command = underlyingConnection.CreateCommand()) {
                        command.CommandText = checkQueryString;
                        command.ExecuteNonQuery();
                    }
                    connectionChecked = true;
                }
                catch (Exception exception) {
                    if (InternalHelpers.ShouldRetryOn(exception))
                    {
                        if (restoreTriggered)
                        {
                            throw;
                        }

                        var newConnection = new SqlServerConnection(underlyingConnection.ConnectionString);
                        try {
                            underlyingConnection.Close();
                            underlyingConnection.Dispose();
                        }
                        catch { }

                        underlyingConnection = newConnection;
                        restoreTriggered     = true;
                        continue;
                    }

                    throw;
                }
            }
        }
Ejemplo n.º 34
0
    /// <summary>
    /// 包含事务对象执行Sql语句
    /// </summary>
    /// <param name="con"></param>
    /// <param name="sql"></param>
    /// <param name="trans"></param>
    /// <returns></returns>
    public static int ExecuteSql(this System.Data.SqlClient.SqlConnection con, string sql, System.Data.SqlClient.SqlTransaction trans)
    {
        //创建SqlCommand对象
        var cmd = con.CreateCommand();

        //设置执行的Sql语句(语句限定insert update delete)
        cmd.CommandText = sql;
        cmd.CommandType = System.Data.CommandType.Text;
        //设置事务
        cmd.Transaction = trans;
        //执行Sql语句
        return(cmd.ExecuteNonQuery());
    }
Ejemplo n.º 35
0
        private static ErrorMessageParser CreateMessageParser(SqlServerConnection connection)
        {
            bool isEnglish;

            using (var command = connection.CreateCommand()) {
                command.CommandText = LangIdQuery;
                isEnglish           = command.ExecuteScalar().ToString() == "0";
            }

            var templates = new Dictionary <int, string>();

            using (var command = connection.CreateCommand()) {
                command.CommandText = MessagesQuery;
                using (var reader = command.ExecuteReader()) {
                    while (reader.Read())
                    {
                        ReadMessageTemplate(reader, templates);
                    }
                }
            }
            return(new ErrorMessageParser(templates, isEnglish));
        }
    public void deleteEquipment(Equipment e)
    {
        sc.ConnectionString = ConfigurationManager.ConnectionStrings["Lab2ConnectionString"].ConnectionString;
        myCommand           = sc.CreateCommand();
        SqlParameter equipmentIDPara = new SqlParameter("@eqipmentID", SqlDbType.Int);

        myCommand.Parameters.Add(equipmentIDPara);
        equipmentIDPara.Value = e.getID();
        myCommand.CommandText = @"DELETE FROM Equipment WHERE ID = @equipmentID";
        sc.Open();
        myCommand.ExecuteNonQuery();
        sc.Close();
    }
        //CreateDatabase TO BE IMPLEMENTED
        public void CreateSqlDatabase()
        {
            string path      = System.AppDomain.CurrentDomain.BaseDirectory;
            string jclue     = path + "JClues.mdf";
            string jclue_log = path + "JClues_log.ldf";


            using (var connection = new System.Data.SqlClient.SqlConnection(
                       "Data Source=(LocalDB)\\MSSQLLocalDB;Integrated Security=True;"))
            {
                connection.Open();
                using (var command = connection.CreateCommand())
                {
                    string str = String.Format("CREATE DATABASE JClues ON PRIMARY " +
                                               "(NAME = JClues, " +
                                               "FILENAME = '{0}', " +
                                               "SIZE = 2MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10%) " +
                                               "LOG ON (NAME = JClues_Log, " +
                                               "FILENAME = '{1}', " +
                                               "SIZE = 1MB, " +
                                               "MAXSIZE =  2048MB, " +
                                               "FILEGROWTH = 10%)", jclue, jclue_log);
                    command.CommandText = str;
                    try
                    {
                        command.ExecuteNonQuery();
                    }
                    catch (SqlException e)
                    {
                        command.CommandText = "Drop Database JClues";
                        command.ExecuteNonQuery();
                        command.CommandText = str;
                        command.ExecuteNonQuery();
                    }
                    command.CommandText = "USE JClues";

                    command.ExecuteNonQuery();
                    str = String.Format("CREATE TABLE [dbo].[Clues] (" +
                                        "[Id]          INT            IDENTITY (1, 1) NOT NULL," +
                                        "[Text]        NVARCHAR (MAX) NOT NULL," +
                                        "[Answer]      NVARCHAR (MAX) NOT NULL," +
                                        "[Value]       INT            NOT NULL," +
                                        "[Category]    NVARCHAR (MAX) NOT NULL," +
                                        "[DailyDouble] BIT            NOT NULL," +
                                        "[Round]       INT            NOT NULL," +
                                        "[Airdate]     SMALLDATETIME  NOT NULL);");
                    command.CommandText = str;
                    command.ExecuteNonQuery();
                }
            }
        }
Ejemplo n.º 38
0
        public DataTable PackingGetDatabyBOX(string BoxNumber)
        {
            using (sqlConnection4 = new SqlConnection(strSqlConnection4_608FFCPACKING))
            {
                sqlConnection4.Open();

                #region Kiem tra Prod Order trong T_information
                string result = "Kiem tra ProdOrder trong T_information";

                SqlCommand cmdGetProdOrderSerials = sqlConnection4.CreateCommand();
                cmdGetProdOrderSerials.CommandType = CommandType.StoredProcedure;
                cmdGetProdOrderSerials.CommandText = "PackingRecord_GetByBox";

                cmdGetProdOrderSerials.Parameters.Add("@BoxNumber", SqlDbType.Char, 20);
                cmdGetProdOrderSerials.Parameters["@BoxNumber"].Value     = BoxNumber;
                cmdGetProdOrderSerials.Parameters["@BoxNumber"].Direction = ParameterDirection.Input;

                SqlDataReader rec = cmdGetProdOrderSerials.ExecuteReader();
                rec.Read();

                result = rec[0].ToString();
                #endregion


                if (result.Equals("OK"))
                {
                    //TraceStepDoing("Doc thong Tinformation ");


                    //rec.NextResult();
                    //rec.Read();
                    //strPN = rec["T_ProdOrder"].ToString().Trim();
                    //strProductmap = rec["T_ProductMap"].ToString().Trim();
                    //strPN = rec["T_Material"].ToString().Trim();
                    //strRev = rec["T_Revision"].ToString().Trim();
                    //StrDes = "";
                    //string tmpProductMap = getProductMapDetail(strPN, ref StrDes);
                    //intPOQuantity = Int32.Parse(rec["T_Quantity"].ToString().Trim());
                    //intPOPacked = Int32.Parse(rec["T_Packed"].ToString().Trim());
                    sqlConnection4.Close();
                }

                DataTable d = new DataTable();
                d.Load(cmdGetProdOrderSerials.ExecuteReader());
                cmdGetProdOrderSerials.Dispose();

                sqlConnection4.Close();
                return(d);
            }
        }
Ejemplo n.º 39
0
 //增、更新
 public static void Excsql(string ConnString, string sql)
 {
     using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection())
     {
         conn.ConnectionString = ConnString;
         conn.Open();
         using (System.Data.SqlClient.SqlCommand command = conn.CreateCommand())
         {
             command.CommandText = sql;
             command.ExecuteNonQuery();
         }
         conn.Close();
     }
 }
Ejemplo n.º 40
0
        /// <summary>
        /// [To be supplied.]
        /// </summary>
        /// <param name="connectionString">Connection string to be used when accessing the database.</param>
        /// <param name="col_CustomerID">[To be supplied.]</param>
        public Customer(string connectionString, System.Data.SqlTypes.SqlInt32 col_CustomerID)
        {
#if OLYMARS_DEBUG
            object olymarsDebugCheck = System.Configuration.ConfigurationSettings.AppSettings["OlymarsDebugCheck"];
            if (olymarsDebugCheck == null || (string)olymarsDebugCheck == "True")
            {
                string DebugConnectionString = connectionString;

                if (DebugConnectionString == System.String.Empty)
                {
                    DebugConnectionString = Bob.DataClasses.Information.GetConnectionStringFromConfigurationFile;
                }

                if (DebugConnectionString == System.String.Empty)
                {
                    DebugConnectionString = Bob.DataClasses.Information.GetConnectionStringFromRegistry;
                }

                if (DebugConnectionString != String.Empty)
                {
                    System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(DebugConnectionString);

                    sqlConnection.Open();

                    System.Data.SqlClient.SqlCommand sqlCommand = sqlConnection.CreateCommand();

                    sqlCommand.CommandType = System.Data.CommandType.Text;
                    sqlCommand.CommandText = "Select sysobjects.schema_ver from sysobjects where sysobjects.name = 'Customers'";

                    int CurrentRevision = (int)sqlCommand.ExecuteScalar();

                    sqlConnection.Close();

                    int OriginalRevision = ((Bob.DataClasses.OlymarsInformationAttribute)System.Attribute.GetCustomAttribute(this.GetType(), typeof(Bob.DataClasses.OlymarsInformationAttribute), false)).SqlObjectDependancyRevision;
                    if (CurrentRevision != OriginalRevision)
                    {
                        throw new System.InvalidOperationException(System.String.Format("OLYMARS: This code is not in sync anymore with [{0}]. It was generated when [{0}] version was: {2}. Current [{0}] version is: {1}", "Customers", CurrentRevision, OriginalRevision));
                    }
                }
            }
#endif

            this.recordWasLoadedFromDB = true;
            this.recordIsLoaded        = false;

            this.connectionString        = connectionString;
            this.lastKnownConnectionType = Bob.DataClasses.ConnectionType.ConnectionString;

            this.col_CustomerID = col_CustomerID;
        }
Ejemplo n.º 41
0
        public static string apply_contents(System.Data.SqlClient.SqlConnection conn, System.Web.HttpRequest req, AccessControl.AccessManager access_manager, System.Data.SqlClient.SqlTransaction tr)
        {
            System.Guid guididx = new System.Guid(req["guididx"]);
            SqlCommand  cmd     = conn.CreateCommand();

            cmd.Transaction = tr;

            cmd.CommandText = String.Format(
                @"
            delete from shop_item_tests where idx=(select idx from shop_item where guididx='{0}');
            delete from shop_item_pdfs where idx=(select idx from shop_item where guididx='{0}');
            update shop_item set parent_idx=null where parent_idx=(select idx from shop_item where guididx='{0}');
            
         "
                , guididx);
            System.Collections.Generic.List <int> tests = new System.Collections.Generic.List <int> ();
            for (int i = 0; i < req.Params.Keys.Count; ++i)
            {
                string key = req.Params.Keys[i];
                if (key.IndexOf("idx_", 0) == 0)
                {
                    string[] a = key.Substring(4).Split('_');
                    if (a[0] == "1")
                    {
                        cmd.CommandText += String.Format("update shop_item set parent_idx=(select idx from shop_item where guididx='{0}') where idx={1};\n", guididx, a[1]);
                    }
                    if (a[0] == "2")
                    {
                        cmd.CommandText += String.Format("insert into shop_item_tests (test_idx,idx) select {1},idx from shop_item where guididx='{0}';\n", guididx, a[1]);
                        tests.Add(Int32.Parse(a[1]));
                    }
                    if (a[0] == "3")
                    {
                        cmd.CommandText += String.Format("insert into shop_item_pdfs (pdf_idx,idx) select {1},idx from shop_item where guididx='{0}';\n", guididx, a[1]);
                        tests.Add(Int32.Parse(a[1]));
                    }
                }
            }

            cmd.ExecuteNonQuery();


            /*foreach(int testid in tests)
             * {
             * access_manager.check_function_and_group(String.Format("test_{0}",testid), get_test_name_by_id_(conn,tr,testid));
             * } */

            return(show_item_cont(conn, req, tr));
        }
Ejemplo n.º 42
0
        /// <summary>
        /// 将Excel文件导入数据库中,先删除库中表再导入
        /// </summary>
        /// <param name="excelFile"></param>
        /// <param name="sheetName"></param>
        /// <param name="connectionString"></param>
        public void TransferData(string excelFile, string sheetName, string connectionString)
        {
            DataSet ds = new DataSet();

            try
            {
                //获取全部数据
                string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFile + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1;\";";
                // string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFile + ";" + "Extended Properties=Excel 8.0;";
                OleDbConnection conn = new OleDbConnection(strConn);
                conn.Open();
                string           strExcel  = "";
                OleDbDataAdapter myCommand = null;
                strExcel  = string.Format("select * from [{0}$]", sheetName);
                myCommand = new OleDbDataAdapter(strExcel, strConn);
                myCommand.Fill(ds, sheetName);

                //如果目标表不存在则创建
                string strSql = string.Format("if object_id('{0}') is not null drop table {0} if object_id('{0}') is null create table {0}(", sheetName);  //以sheetName为表名
                foreach (System.Data.DataColumn c in ds.Tables[0].Columns)
                {
                    strSql += string.Format("[{0}] varchar(255),", c.ColumnName);
                }
                strSql = strSql.Trim(',') + ")";

                using (System.Data.SqlClient.SqlConnection sqlconn = new System.Data.SqlClient.SqlConnection(connectionString))
                {
                    sqlconn.Open();
                    System.Data.SqlClient.SqlCommand command = sqlconn.CreateCommand();
                    command.CommandText = strSql;
                    command.ExecuteNonQuery();
                    sqlconn.Close();
                }
                //用bcp导入数据
                using (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy(connectionString))
                {
                    bcp.SqlRowsCopied       += new System.Data.SqlClient.SqlRowsCopiedEventHandler(bcp_SqlRowsCopied);
                    count                    = ds.Tables[0].Rows.Count;
                    bcp.BatchSize            = count > 1000 ? ds.Tables[0].Rows.Count / 10 : 100;; //每次传输的行数
                    bcp.NotifyAfter          = count > 100 ? ds.Tables[0].Rows.Count / 10 : 1;     // 1000;//进度提示的行数
                    bcp.DestinationTableName = sheetName;                                          //目标表
                    bcp.WriteToServer(ds.Tables[0]);
                }
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
            }
        }
Ejemplo n.º 43
0
        private void upData()
        {
            using (System.Data.SqlClient.SqlConnection sqlconn = new System.Data.SqlClient.SqlConnection(connString))
            {
                sqlconn.Open();
                System.Data.SqlClient.SqlCommand command = sqlconn.CreateCommand();
                foreach (var strSql in sqlList)
                {
                    command.CommandText = strSql;
                    command.ExecuteNonQuery();
                }

                sqlconn.Close();
            }
        }
Ejemplo n.º 44
0
    // creates and prepares a new DbCommand object on a new connection
    public static DbCommand CreateCommand()
    {
        //  string connectionString = SiteConfiguration.DbConnectionString;
        // Set the connection string
        //  System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connectionString);
        dy();
        System.Data.SqlClient.SqlConnection conn = epconn;
        // Create a database specific command object
        DbCommand comm = conn.CreateCommand();

        // Set the command type to stored procedure
        comm.CommandType = CommandType.StoredProcedure;
        // Return the initialized command object
        return(comm);
    }
Ejemplo n.º 45
0
        public static string list_q_in_test(System.Data.SqlClient.SqlConnection conn, System.Web.HttpRequest req)
        {
            int        test_id = Int32.Parse(req["test_id"]);
            SqlCommand cmd     = conn.CreateCommand();

            cmd.CommandText = String.Format(
                @"SELECT     SetsToQuestions.QuestionId, Questions.text
FROM         Tests INNER JOIN
                      TestContents ON Tests.Id = TestContents.TestId INNER JOIN
                      QuestionSets ON TestContents.QuestionSetId = QuestionSets.Id INNER JOIN
                      SetsToQuestions ON QuestionSets.Id = SetsToQuestions.SetId INNER JOIN
                      Questions ON SetsToQuestions.QuestionId = Questions.Id
                      
WHERE     (Tests.Id = {0})
ORDER BY TestContents.QuestionSetOrder, SetsToQuestions.QuestionOrder"
                ,
                test_id);

            string ret = "";
            bool   f   = true;

            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    if (!f)
                    {
                        ret += ";";
                    }
                    if (f)
                    {
                        f = false;
                    }
                    ret += reader[0].ToString();
                    ret += "||";
                    string s = reader[1].ToString();
                    if (s.Length != 0)
                    {
                        ret += s.Substring(0, s.Length >= 32?32:s.Length) + "...";
                    }
                    else
                    {
                        ret += s + "...";
                    }
                }
            }
            return(ret);
        }
Ejemplo n.º 46
0
        public static string save_item_descr(System.Data.SqlClient.SqlConnection conn, System.Web.HttpRequest req, System.Data.SqlClient.SqlTransaction tr)
        {
            string descr = req["descr"];

            System.Guid guididx = new System.Guid(req["guididx"]);


            SqlCommand cmd = conn.CreateCommand();

            cmd.Transaction = tr;

            cmd.CommandText = String.Format("update shop_item set descr='{1}' where guididx='{0}';", req["guididx"], req["descr"]);
            cmd.ExecuteNonQuery();

            return("ok");
        }
Ejemplo n.º 47
0
        //get all items in the Numbers table
        #region access functions for the numbers table
        public List <NumberDAL> GetAllNumbers()
        {
            //Ensure connected opens the connection to the database if it is not already opended
            EnsureConnected();
            //create an empty list to return
            List <NumberDAL> rv = new List <NumberDAL>();
            //create a sql command object using the existing and open connection. we know it is open
            //because of ensureconnection();
            SqlCommand localcommand = MyConnection.CreateCommand();

            //configure the command object. we are sending literal sql to execute, so use text as the type
            // and select * from numbers since we want all numbers
            localcommand.CommandType = System.Data.CommandType.Text;
            localcommand.CommandText = "Select * from Numbers";
            //start the query
            SqlDataReader reader = localcommand.ExecuteReader();
            //reader.read positions us to the first record returned by the sql query
            //and it returns false when there are no more records, and true when the record is postioned
            int  IDPosition   = 0;
            int  NamePosition = 0;
            int  DSPosition   = 0;
            int  FSPosition   = 0;
            bool first        = true;

            while (reader.Read())
            {
                if (first)
                {
                    first        = false;
                    IDPosition   = reader.GetOrdinal("ID");
                    NamePosition = reader.GetOrdinal("Name");
                    DSPosition   = reader.GetOrdinal("Doublestuff");
                    FSPosition   = reader.GetOrdinal("Floatstuff");
                }
                NumberDAL r = new NumberDAL();
                r.ID   = reader.GetInt32(IDPosition);// this involves boxing, unboxing and garbage generation
                r.Name = (string)reader[NamePosition];
                //r.Doublstuff = (double)reader[DSPosition];
                r.Doublstuff = reader.GetDouble(DSPosition);
                r.Floatstuff = reader.GetFloat(FSPosition);
                //r.Floatstuff = (float)reader[FSPosition];

                rv.Add(r);
            }
            reader.Close();
            return(rv);
        }
Ejemplo n.º 48
0
        /// <summary>
        /// creates an empty database with the name specified in the connection string
        /// </summary>
        public static void CreateDatabase(string connectionString)
        {
            SqlConnectionStringBuilder builder = new System.Data.SqlClient.SqlConnectionStringBuilder(connectionString);
            string dbName = builder.InitialCatalog;

            builder.InitialCatalog = string.Empty;
            using (SqlConnection conn = new System.Data.SqlClient.SqlConnection(builder.ConnectionString))
            {
                conn.Open();
                var command = conn.CreateCommand();
                command.CommandType = System.Data.CommandType.Text;
                command.CommandText = "use [master]";
                command.ExecuteNonQuery();
                command.CommandText = string.Format("create database {0} collate SQL_Latin1_General_CP1_CS_AS", dbName);
                command.ExecuteNonQuery();
            }
        }
Ejemplo n.º 49
0
        public static string show_item_descr(System.Data.SqlClient.SqlConnection conn, System.Web.HttpRequest req, System.Data.SqlClient.SqlTransaction tr)
        {
            SqlCommand cmd = conn.CreateCommand();

            cmd.Transaction = tr;
            cmd.CommandText = String.Format("select descr from shop_item where guididx='{0}';", req["guididx"]);
            System.Object obj   = cmd.ExecuteScalar();
            String        value = obj.GetType() == typeof(System.DBNull)?"":obj.ToString();

            string ret = String.Format(@"<div>
<form name='item_descr' action='1.axp' method='get' onsubmit='javascript:save_text({0}); return false;'>
<textarea id='HereIsTheText'> {1}</textarea></form>
</div>",
                                       "\"" + req["guididx"] + "\"", value);

            return(ret);
        }
Ejemplo n.º 50
0
        static public bool SetModalityPerformedProcedureStep(int procedureStepId, XmlElement sequenceXmlDoc)
        {
            SqlConnection conn = new System.Data.SqlClient.SqlConnection();

            conn.ConnectionString = MwlMppsDBBroker.Properties.Settings.Default.ConnectionString;
            SqlTransaction transaction = null;

            try
            {
                conn.Open();
                transaction = conn.BeginTransaction();
                string     commandText = string.Format("EXEC dbo.sp_SetModalityPerformedProcedureStep @psId = {0} ", procedureStepId);
                SqlCommand cmd         = conn.CreateCommand();
                cmd.CommandText = commandText;
                int recordAffected = cmd.ExecuteNonQuery();

                bool success = CreatePerformedSeriesSequence(sequenceXmlDoc, transaction);

                if (success)
                {
                    transaction.Commit();
                }
                else
                {
                    transaction.Rollback();
                }
                return(true);
            }
            catch (Exception ex)
            {
                Platform.Log(LogLevel.Error, ex);
                if (transaction != null)
                {
                    transaction.Rollback();
                }
                return(false);
            }
            finally
            {
                if (conn.State != System.Data.ConnectionState.Closed)
                {
                    conn.Close();
                }
            }
        }
Ejemplo n.º 51
0
        public static void RunSP(string spName, List <SqlDbParameter> inPars, ref DataTable destTable)
        {
            SqlDataAdapter DBAdapter = null;
            SqlConnection  DBConn    = new System.Data.SqlClient.SqlConnection(ConnectionString);

            DBConn.Open();

            try
            {
                var DBComm = DBConn.CreateCommand();
                DBComm.CommandType = CommandType.StoredProcedure;
                DBComm.CommandText = spName;
                DBAdapter          = new SqlDataAdapter(DBComm);

                if (inPars.Count > 0)
                {
                    foreach (var sqlDbParameter in inPars)
                    {
                        DBAdapter.SelectCommand.Parameters.AddWithValue("@" + sqlDbParameter.ParName, sqlDbParameter.ParValue);
                    }
                }

                var ds = new DataSet();
                DBAdapter.Fill(ds);
                DBAdapter.Dispose();
                DBComm.Dispose();

                destTable = ds.Tables[0];
            }
            catch (SqlException ex)
            {
            }
            catch (Exception ex)
            {
            }
            finally
            {
                if (DBAdapter != null)
                {
                    DBAdapter.Dispose();
                }

                DBConn.Close();
            }
        }
Ejemplo n.º 52
0
 private void button8_Click(object sender, EventArgs e)
 {
     if (MessageBox.Show("ZEKER WETEN>>>!!!!!!!", "MegaTabelMaak", MessageBoxButtons.OKCancel) == System.Windows.Forms.DialogResult.OK)
     {
         var conn = new System.Data.SqlClient.SqlConnection();
         conn.ConnectionString = "Data Source=SQL-SERVER;Initial Catalog=zeebregtsdb;User ID=daan;Password=Bl22sk22k!";
         conn.Open();
         for (int i = 0; i < 100000; i++)
         {
             var        r       = new Random(DateTime.Now.Second);
             string     sql     = "INSERT INTO StresTestTable ([Spoef]) VALUES (" + r.GetHashCode().ToString() + ")";
             SqlCommand command = conn.CreateCommand();
             command.CommandText = sql;
             command.ExecuteNonQuery();
         }
         conn.Close();
     }
 }
Ejemplo n.º 53
0
 //查询
 public static System.Data.DataSet Query(string ConnString, string sql)
 {
     System.Data.DataSet result = new System.Data.DataSet();
     using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection())
     {
         conn.ConnectionString = ConnString;
         conn.Open();
         using (System.Data.SqlClient.SqlCommand command = conn.CreateCommand())
         {
             command.CommandText = sql;
             using (System.Data.SqlClient.SqlDataAdapter adp = new System.Data.SqlClient.SqlDataAdapter(command))
             {
                 adp.Fill(result);
             }
         }
         conn.Close();
     }
     return(result);
 }
Ejemplo n.º 54
0
Archivo: Isv.cs Proyecto: zwkjgs/XKD
        private string CreateAdministrator(string connectionstr)
        {
            System.Data.Common.DbConnection dbConnection = new System.Data.SqlClient.SqlConnection(connectionstr);
            dbConnection.Open();
            System.Data.Common.DbCommand dbCommand = dbConnection.CreateCommand();
            dbCommand.Connection  = dbConnection;
            dbCommand.CommandType = System.Data.CommandType.Text;
            dbCommand.CommandText = "INSERT INTO aspnet_Roles(RoleName,IsDefault) VALUES('超级管理员',1); SELECT @@IDENTITY";
            int num = System.Convert.ToInt32(dbCommand.ExecuteScalar());

            dbCommand.CommandText = "INSERT INTO aspnet_Managers(RoleId, UserName, Password, Email, CreateDate) VALUES (@RoleId, @UserName, @Password, @Email, getdate())";
            dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@RoleId", num));
            dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Username", "admin"));
            dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Password", HiCryptographer.Md5Encrypt("123456")));
            dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Email", "*****@*****.**"));
            dbCommand.ExecuteNonQuery();
            dbConnection.Close();
            return("");
        }
Ejemplo n.º 55
0
        public static void ClassInitialize(TestContext testContext)
        {
            path = Path.Combine(new FileInfo(typeof(DBTests).Assembly.Location).Directory.FullName, "UnitTestData.mdf");
            if (File.Exists(path))
            {
                File.Delete(path);
            }
            DatabaseUtil.CreateSqlDatabase(path);
            conn = new System.Data.SqlClient.SqlConnection(connstr + path);
            conn.Open();
            var cmd = conn.CreateCommand();

            cmd.CommandText = OgcConformanceMap.DropTables;
            cmd.ExecuteNonQuery();
            cmd.CommandText = OgcConformanceMap.CreateTables;
            cmd.ExecuteNonQuery();
            cmd.CommandText = OgcConformanceMap.CreateRows;
            cmd.ExecuteNonQuery();
        }
Ejemplo n.º 56
0
        public void Commit()
        {
            using (var connection = new System.Data.SqlClient.SqlConnection(_connectionString.FormattedConnectionString)) {
                connection.Open();

                var entitySets = ConvertToBatches(_entities, 200);                      // limitation of num rows allowed to insert in one call

                foreach (var entitySet in entitySets)
                {
                    string sql = "INSERT TestEntities(TestDate , TestInt, TestString) VALUES"
                                 + String.Join(",", entitySet.Select(e => String.Format("('{0}',{1},'{2}')", e.TestDate.ToString("yyyy-MM-ddTHH:mm:ss.fff"), e.TestInt, e.TestString)));

                    var cmd = connection.CreateCommand();
                    cmd.CommandText = sql;
                    cmd.CommandType = System.Data.CommandType.Text;
                    cmd.ExecuteNonQuery();
                }
            }
        }
Ejemplo n.º 57
0
        private String getDBVersion()
        {
            String version = "Access error";


            try
            {
                SqlConnection conn = new System.Data.SqlClient.SqlConnection(BaseConnectionStringBuilder.ConnectionString);
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT count(*) From  " + TestTable;
                version         = cmd.ExecuteScalar().ToString();
                conn.Close();
            }
            catch (Exception ex)
            {
            }
            return(version);
        }
Ejemplo n.º 58
0
        private void ReadInforFromT_Information(string PO, ref string strPN, ref string strRev, ref string StrDes, ref string strProductmap)
        {
            using (sqlConnection4 = new SqlConnection(strSqlConnection4_608FFCPACKING))
            {
                sqlConnection4.Open();

                #region Kiem tra Prod Order trong T_information
                string result = "Kiem tra ProdOrder trong T_information";
                PO = (PO).PadLeft(12, '0');

                SqlCommand cmdGetProdOrderSerials = sqlConnection4.CreateCommand();
                cmdGetProdOrderSerials.CommandType = CommandType.StoredProcedure;
                cmdGetProdOrderSerials.CommandText = "ame_T_getProdOrderInfo";;
                cmdGetProdOrderSerials.Parameters.Add("@ProdOrder", SqlDbType.Char, 20);
                cmdGetProdOrderSerials.Parameters["@ProdOrder"].Value     = PO;
                cmdGetProdOrderSerials.Parameters["@ProdOrder"].Direction = ParameterDirection.Input;
                SqlDataReader rec = cmdGetProdOrderSerials.ExecuteReader();
                rec.Read();

                result = rec[0].ToString();
                #endregion


                if (result.Equals("OK"))
                {
                    //TraceStepDoing("Doc thong Tinformation ");


                    rec.NextResult();
                    rec.Read();
                    strPN         = rec["T_ProdOrder"].ToString().Trim();
                    strProductmap = rec["T_ProductMap"].ToString().Trim();
                    strPN         = rec["T_Material"].ToString().Trim();
                    strRev        = rec["T_Revision"].ToString().Trim();
                    StrDes        = "";
                    //string tmpProductMap = getProductMapDetail(strPN,ref StrDes);
                    //intPOQuantity = Int32.Parse(rec["T_Quantity"].ToString().Trim());
                    //intPOPacked = Int32.Parse(rec["T_Packed"].ToString().Trim());
                    sqlConnection4.Close();
                }
            }
        }
Ejemplo n.º 59
0
        private bool CreateAdministrator(out string errorMsg)
        {
            System.Data.Common.DbConnection dbConnection = new System.Data.SqlClient.SqlConnection(this.GetConnectionString());
            dbConnection.Open();
            System.Data.Common.DbCommand dbCommand = dbConnection.CreateCommand();
            dbCommand.Connection  = dbConnection;
            dbCommand.CommandType = System.Data.CommandType.Text;
            dbCommand.CommandText = "INSERT INTO aspnet_Roles(RoleName,IsDefault) VALUES('超级管理员',1); SELECT @@IDENTITY";
            int num = System.Convert.ToInt32(dbCommand.ExecuteScalar());

            dbCommand.CommandText = "INSERT INTO aspnet_Managers(RoleId, UserName, Password, Email, CreateDate) VALUES (@RoleId, @UserName, @Password, @Email, getdate())";
            dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@RoleId", num));
            dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Username", this.username));
            dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Password", HiCryptographer.Md5Encrypt(this.password)));
            dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Email", this.email));
            dbCommand.ExecuteNonQuery();
            dbConnection.Close();
            errorMsg = null;
            return(true);
        }
Ejemplo n.º 60
-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();
        }