protected override void Up(MigrationBuilder migrationBuilder) { var tables = new string[] { "Users", "Dungeons", "DungeonOptions", "Options" }; foreach (var table in tables) { migrationBuilder.Sql( @$ " CREATE TRIGGER Set{table}TimestampOnUpdate AFTER UPDATE ON {table} BEGIN UPDATE {table} SET Timestamp = randomblob(8) WHERE rowid = NEW.rowid; END "); migrationBuilder.Sql( @$ " CREATE TRIGGER Set{table}TimestampOnInsert AFTER INSERT ON {table} BEGIN UPDATE {table} SET Timestamp = randomblob(8) WHERE rowid = NEW.rowid; END "); } }
public async Task <CustomResponse> UpdateComponent(Component component) { CustomResponse response = await CheckExistingComponentValid(component); if (response.Value == 0) { //component is invalid -> return response message return(response); } if (component.Id == 0) { return(new CustomResponse(0, "Diese Zutat kann nicht verändert werden")); } DbConnection db = new DbConnection(); try { var query = @$ "UPDATE component SET name = '{component.Name.Trim()}' WHERE id = {component.Id};"; await db.ExecuteQuery(query); return(new CustomResponse((int)component.Id, $"Zutat {component.Name} erfolgreich bearbeitet")); }
public async Task <Commentary> GetCommentaryIdAsync(int id) { using (var con = new SqlConnection(_configuration["ConnectionString"])) { var sqlCmd = @$ "SELECT * FROM Commentary WHERE PostageId= '{id}'" ; using (var cmd = new SqlCommand(sqlCmd, con)) { cmd.CommandType = CommandType.Text; con.Open(); var reader = await cmd .ExecuteReaderAsync() .ConfigureAwait(false); while (reader.Read()) { var commentary = new Commentary(int.Parse(reader["Id"].ToString()), int.Parse(reader["PostageId"].ToString()), int.Parse(reader["ClientId"].ToString()), reader["Text"].ToString(), DateTime.Parse(reader["Creation"].ToString())); return(commentary); } return(default);
public Task <Game> SaveGame(GameCreationRequest game) { return(gamestakDb.Use(async conn => { var query = @$ " IF NOT EXISTS (SELECT GameID FROM {DbTables.Games} WHERE Title = @Title) BEGIN INSERT INTO {DbTables.Games} (Title, Publisher, [Description], Price, DiscountRate, ReleaseDate, ThumbnailUrl) VALUES (@Title, @Publisher, @Description, @Price, @DiscountRate, @ReleaseDate, @ThumbnailUrl) END "; var selectQuery = $@"SELECT * FROM {DbTables.Games} WHERE Title = @Title"; var rowsAffected = await conn.ExecuteAsync(query, game); if (rowsAffected < 0) { throw new ArgumentException("Game object already exists or is invalid"); } var result = (await conn.QueryAsync <Game>(selectQuery, game)).FirstOrDefault(); return result; })); }
public WHERE Where(string Value) { WHERE whr = new WHERE(Value, this); WhereFilter.Add(whr); return(whr); }
public Task <List <Feature> > AssignGameFeatures(int gameId, List <int> featureIds) { return(gamestakDb.Use(async conn => { var query = @$ " IF NOT EXISTS (SELECT * FROM {DbTables.FeatureAssignments} WHERE GameID = @GameID AND FeatureID = @FeatureID) BEGIN INSERT INTO {DbTables.FeatureAssignments} (GameID, FeatureID) VALUES (@GameID, @FeatureID) END "; var parameters = featureIds.Select(featureID => new { GameId = gameId, FeatureID = featureID }).ToArray(); await conn.ExecuteAsync(query, parameters); var result = await GetFeaturesByGameID(gameId); return result; })); }
public Task <int> SaveGameKeys(int userId, List <int> gameIds) { return(gamestakDb.Use(async conn => { var query = @$ " IF NOT EXISTS (SELECT GameID FROM {DbTables.GameKeys} WHERE UserID = @UserId and GameID = @GameId) BEGIN INSERT INTO {DbTables.GameKeys} (GameID, UserID) VALUES (@GameId, @UserId) END "; var parameters = gameIds.Select(gameId => new { GameId = gameId, UserId = userId, }).ToArray(); var rowsAffected = await conn.ExecuteAsync(query, parameters); if (rowsAffected < 0) { throw new ArgumentException("GameKey already exists or ids are invalid"); } return rowsAffected; })); }
public async Task <List <Team> > GetAllTeamsBelowManager(int managerId) { // This may look like string interporlation, but // The interpolated value is converted to a DbParameter and isn't vulnerable to SQL injection // @see: https://docs.microsoft.com/en-us/ef/core/querying/raw-sql#code-try-2 return(await _schedulearnContext.Teams.FromSqlInterpolated(@$ "WITH org AS ( SELECT team.*, usr.Id as UserId FROM dbo.Teams team INNER JOIN dbo.Users usr ON usr.TeamId = team.Id WHERE ManagerId = {managerId} UNION ALL SELECT e.*, eUser.Id as UserId FROM dbo.Teams e INNER JOIN org o ON o.UserId = e.ManagerId INNER JOIN dbo.Users eUser ON eUser.TeamId = e.Id ) SELECT DISTINCT org.Id, org.LimitId, org.ManagerId FROM org;").ToListAsync()); }
public async Task <int> GetUsuarioIdByPostagemId(int postagemId) { using (var con = new SqlConnection(_configuration["ConnectionString"])) { var sqlCmd = @$ "SELECT UsuarioId FROM Postagem WHERE Id= '{postagemId}'" ; using (var cmd = new SqlCommand(sqlCmd, con)) { cmd.CommandType = CommandType.Text; con.Open(); var reader = await cmd .ExecuteReaderAsync() .ConfigureAwait(false); var postagensUsuario = new List <Postagem>(); int id = -300; while (reader.Read()) { id = int.Parse(reader["UsuarioId"].ToString()); } return(id); } } }
public LoginRoles(DBConnectionControl4.DBConnection xcon) { select = new selection(this); where = new WHERE(this); m_con = xcon; tablename = LoginRoles.tablename_const; myUpdateObjects = UpdateObjects; o_id.col_name = LoginRoles.id.name; o_id.col_type.m_Type = "int"; o_id.col_type.bPRIMARY_KEY = true; o_id.col_type.bFOREIGN_KEY = false; o_id.col_type.bUNIQUE = false; o_id.col_type.bNULL = false; Add(o_id); o_Name.col_name = LoginRoles.Name.name; o_Name.col_type.m_Type = "nvarchar"; o_Name.col_type.bPRIMARY_KEY = false; o_Name.col_type.bFOREIGN_KEY = false; o_Name.col_type.bUNIQUE = false; o_Name.col_type.bNULL = true; Add(o_Name); o_description.col_name = LoginRoles.description.name; o_description.col_type.m_Type = "nvarchar"; o_description.col_type.bPRIMARY_KEY = false; o_description.col_type.bFOREIGN_KEY = false; o_description.col_type.bUNIQUE = false; o_description.col_type.bNULL = true; Add(o_description); }
static void Main(string[] args) { const string connectionString = "Data Source=.;database=MinionsDB;Integrated Security=True;"; int[] idInput = Console.ReadLine().Split().Select(int.Parse).ToArray(); using (var connection = new SqlConnection(connectionString)) { connection.Open(); foreach (var id in idInput) { string query = @$ "UPDATE Minions SET Age += 1, Name = UPPER(LEFT(Name, 1)) + LOWER(RIGHT(Name, LEN(Name) - 1)) WHERE Id = {id}"; using (var command = new SqlCommand(query, connection)) { command.ExecuteNonQuery(); } using (var command = new SqlCommand($"SELECT Name, Age FROM Minions WHERE Id = {id}", connection)) { using (var reader = command.ExecuteReader()) { reader.Read(); Console.WriteLine($"{reader[0]} {reader[1]}"); } } } } }
public async Task <int> ObterCurtidas(int postagemId) { using (var con = new SqlConnection(_configuration["ConnectionString"])) { var sqlCmd = @$ "SELECT COUNT(*) AS Quantidade FROM Curtidas WHERE PostagemId={postagemId}" ; using (var cmd = new SqlCommand(sqlCmd, con)) { cmd.CommandType = CommandType.Text; con.Open(); var reader = await cmd .ExecuteReaderAsync() .ConfigureAwait(false); while (reader.Read()) { return(int.Parse(reader["Quantidade"].ToString())); } return(default);
public SqlSELECT(object select, string from, object where = null) : this() { if (select != null) { if (select.GetType().IsArray) { SELECT.AddRange((object[])select); } else { SELECT.Add(select); } } FROM = from; if (where != null) { var whereArr = where as IEnumerable <object>; if (whereArr == null) { WHERE.Add(where); } else { WHERE.AddRange(whereArr); } } }
public async Task <Gender> GetByIdAsync(int id) { using (var con = new SqlConnection(_configuration["ConnectionString"])) { var sqlCmd = @$ "SELECT Id, Descricao FROM Genero WHERE Id={id}" ; using (var cmd = new SqlCommand(sqlCmd, con)) { cmd.CommandType = CommandType.Text; con.Open(); var reader = await cmd .ExecuteReaderAsync() .ConfigureAwait(false); while (reader.Read()) { var gender = new Gender(reader["Descricao"].ToString()); gender.SetId(int.Parse(reader["Id"].ToString())); return(gender); } return(default);
internal STARTSWITH(string value, WHERE parent) { this.parent = parent; this.value = value; parent.StartWithValues.Add(this); }
internal ENDSWITH(string value, WHERE parent) { this.parent = parent; this.value = value; parent.EndsWithValues.Add(this); }
public WHERE OrWhere(string value) { WHERE whr = new WHERE(value, parent.parent, "or"); this.parent.parent.WhereFilter.Add(whr); return(whr); }
public async Task <List <int> > ObterListaDeAmigos(int idUsuarioSeguidor) { using (var con = new SqlConnection(_configuration["ConnectionString"])) { var SqlCmd = @$ "SELECT IdSeguido FROM Amigos WHERE IdSeguidor= '{idUsuarioSeguidor}'"; using (var cmd = new SqlCommand(SqlCmd, con)) { cmd.CommandType = CommandType.Text; con.Open(); var reader = await cmd.ExecuteReaderAsync().ConfigureAwait(false); List <int> listaIdAmigos = new List <int>(); while (reader.Read()) { listaIdAmigos.Add(int.Parse(reader["IdSeguido"].ToString())); } return(listaIdAmigos); } }
internal ISLIKE(string value, WHERE parent, bool val) { this.parent = parent; this.value = value; this.equals = val; parent.LikeValues.Add(this); }
protected override async Task InitializeAsync() { await using var conn = new NpgsqlConnection(TranslationOptions.ConnectionString); await conn.OpenAsync(); await using var cmd = new NpgsqlCommand(@$ "SELECT EXISTS ( SELECT FROM information_schema.tables WHERE table_schema = '{SchemeName}'
public ISBETWEEN(string start, string end, WHERE parent) { this.parent = parent; this.start = start; this.end = end; this.parent.IsBetweenValues.Add(this); }
internal List <CondicaoPagamentoParcela> GetByCondicaoPagamentoId(int id) { var sql = @$ "SELECT CondicaoPagamentoParcelas.*, formapagamentos.nome as " "formapagamentos.nome" " FROM CondicaoPagamentoParcelas INNER JOIN formapagamentos ON formapagamentos.id = CondicaoPagamentoParcelas.formapagamentoid WHERE condicaopagamentosid = @id"; return(base.ExecuteGetAll(sql, new { id })); }
public List <Roommate> GetAllWithRoom(int roomId) { using (SqlConnection conn = Connection) { // Note, we must Open() the connection, the "using" block doesn't do that for us. conn.Open(); // We must "use" commands too. using (SqlCommand cmd = conn.CreateCommand()) { // Here we setup the command with the SQL we want to execute before we execute it. cmd.CommandText = @$ "SELECT roommate.Id, roommate.Firstname, roommate.Lastname, roommate.RentPortion, roommate.MoveInDate, room.Name FROM Roommate roommate JOIN Room room ON roommate.RoomId = room.Id WHERE roomId = @roomId"; cmd.Parameters.AddWithValue("@roomId", roomId); // Execute the SQL in the database and get a "reader" that will give us access to the data. SqlDataReader reader = cmd.ExecuteReader(); // A list to hold the rooms we retrieve from the database. List <Roommate> someRoommates = new List <Roommate>(); // Read() will return true if there's more data to read while (reader.Read()) { Roommate roommate = new Roommate { Firstname = reader.GetString(reader.GetOrdinal("Firstname")), Lastname = reader.GetString(reader.GetOrdinal("Lastname")), RentPortion = reader.GetInt32(reader.GetOrdinal("RentPortion")), MovedInDate = reader.GetDateTime(reader.GetOrdinal("MoveInDate")), Room = new Room() { Id = roomId, Name = reader.GetString(reader.GetOrdinal("Name")) } }; // ...and add that room object to our list. someRoommates.Add(roommate); } // We should Close() the reader. Unfortunately, a "using" block won't work here. reader.Close(); // Return the list of rooms who whomever called this method. return(someRoommates); } } }
public override async Task UpdateUserTaskAsync(UserTask userTask) { using (IDbConnection cnn = new SQLiteConnection(LoadConnectionString())) { if (userTask is BoolTypeUserTask boolUserTask) { await cnn.ExecuteAsync(@$ "UPDATE {boolUserTask.ParentTaskHolder.Title} SET IsTaskDone = @IsTaskDone, TimeOfCompletionUTC = @TimeOfCompletionUTC, TimeOfCompletionLocal = @TimeOfCompletionLocal WHERE Date = @Date;"
public static bool ExistsInDatabaseByChatId(this ChatIdToUsernamePair pair, SQLiteConnection connection) { string sql = @$ " SELECT COUNT(1) AS 'Count' FROM ChatIdUsernamePairs WHERE ChatId = {pair.ChatId}"; var rows = connection.Query(sql); return((int)(rows.First().Count) > 0); }
private void fillDGV() { // ORDER BY ID jer kolona `date` ne hvata sate pri sortiranju string _fillDGVquery = @$ "SELECT `prices`.`id_price`, `value`, DATE_FORMAT(`date`, '%d.%m.%Y') as `date`, `status` FROM `prices` JOIN `articles` ON `prices`.`fk_article_id` = `articles`.`id_article` WHERE `fk_article_id` = {_articleId} ORDER BY `prices`.`id_price` DESC"; DbConnection.fillDGV(articlePricesDataGridView, _fillDGVquery); }
public List <VendaProduto> GetInVenda(VendaId vendaId) { var sql = @$ "SELECT {PropertiesIds.FormatProperty(e => $" VendaProdutos.{ e } ")}, {Property.FormatProperty(e => $" VendaProdutos.{ e } ")}, produtos.id AS " "produto.id" ", produtos.nome AS " "produto.nome" " FROM VendaProdutos INNER JOIN produtos ON produtos.id = vendaprodutos.produtoid WHERE VendaNumero= @Numero AND VendaSerie= @Serie AND VendaModelo= @Modelo "; return(base.ExecuteGetAll(sql, vendaId)); }
public override async Task CopySandboxAsync(string originalDatabaseName, string newDatabaseName) { using (var conn = CreateConnection()) { string sql = @$ " SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname='{originalDatabaseName}'; CREATE DATABASE " "{newDatabaseName}" " TEMPLATE " "{originalDatabaseName}" " "; await conn.ExecuteAsync(sql, commandTimeout : CommandTimeout).ConfigureAwait(false); }
public IReadOnlyList <TestImage> GetAll(User user) { var sql = @$ "SELECT {ColumnNames} FROM {TestImageTable} WHERE UserId = @UserId"; using var connection = CreateConnection(); var imgs = connection.Query <DbTestImage>(sql, new { user.UserId }).Select(i => _mapper.Map <TestImage>(i)); return(imgs.ToList()); }
public async Task <PaginatedResponse <IAsset> > GetByPagesAsync(int?skip = null, int?take = null) { //Used raw SQL cause EF does not support Union after select from different tables yet skip ??= 0; take = PaginationHelper.GetTake(take); using (var context = _contextFactory.CreateDataContext()) using (var command = context.Database.GetDbConnection().CreateCommand()) { var query = @$ "SELECT ProductId as Id, Name as Name FROM [Products] WHERE IsStarted = '1' Union SELECT Id as Id, Id as Name FROM [Currencies] ORDER BY Name OFFSET @skip ROWS FETCH NEXT @take ROWS ONLY"; command.CommandText = query; command.Parameters.Add(new SqlParameter("@skip", SqlDbType.Int) { Value = skip.Value }); command.Parameters.Add(new SqlParameter("@take", SqlDbType.Int) { Value = take.Value }); await context.Database.OpenConnectionAsync(); var reader = await command.ExecuteReaderAsync(); var assets = new List <IAsset>(); while (await reader.ReadAsync()) { assets.Add(new Asset(reader["Id"].ToString(), reader["Name"].ToString(), AssetConstants.Accuracy)); } await reader.CloseAsync(); var totalCountQuery = "SELECT (SELECT COUNT(*) FROM [Products]) + (SELECT COUNT(*) FROM [Currencies])"; command.CommandText = totalCountQuery; command.Parameters.Clear(); var totalCount = (int)await command.ExecuteScalarAsync(); return(new PaginatedResponse <IAsset>( contents: assets, start: skip.Value, size: assets.Count, totalSize: totalCount )); } }
public void UpdateCustomPromptForCentre(int centreId, int promptNumber, bool mandatory, string?options) { connection.Execute( @$ "UPDATE Centres SET F{promptNumber}Mandatory = @mandatory, F{promptNumber}Options = @options WHERE CentreID = @centreId", new { mandatory, options, centreId } ); }
public Log(Log_DBConnection xcon) { select = new selection(this); where = new WHERE(this); m_con = xcon; tablename = Log.tablename_const; myUpdateObjects = UpdateObjects; o_id.col_name = Log.id.name; o_id.col_type.m_Type = "int"; o_id.col_type.bPRIMARY_KEY = true; o_id.col_type.bFOREIGN_KEY = false; o_id.col_type.bUNIQUE = false; o_id.col_type.bNULL = false; Add(o_id); o_LogFile_id.col_name = Log.LogFile_id.name; o_LogFile_id.col_type.m_Type = "int"; o_LogFile_id.col_type.bPRIMARY_KEY = false; o_LogFile_id.col_type.bFOREIGN_KEY = true; o_LogFile_id.col_type.bUNIQUE = false; o_LogFile_id.col_type.bNULL = false; Add(o_LogFile_id); o_LogTime.col_name = Log.LogTime.name; o_LogTime.col_type.m_Type = "datetime"; o_LogTime.col_type.bPRIMARY_KEY = false; o_LogTime.col_type.bFOREIGN_KEY = false; o_LogTime.col_type.bUNIQUE = false; o_LogTime.col_type.bNULL = false; Add(o_LogTime); o_Log_Type_id.col_name = Log.Log_Type_id.name; o_Log_Type_id.col_type.m_Type = "int"; o_Log_Type_id.col_type.bPRIMARY_KEY = false; o_Log_Type_id.col_type.bFOREIGN_KEY = true; o_Log_Type_id.col_type.bUNIQUE = false; o_Log_Type_id.col_type.bNULL = false; Add(o_Log_Type_id); o_Log_Description_id.col_name = Log.Log_Description_id.name; o_Log_Description_id.col_type.m_Type = "int"; o_Log_Description_id.col_type.bPRIMARY_KEY = false; o_Log_Description_id.col_type.bFOREIGN_KEY = true; o_Log_Description_id.col_type.bUNIQUE = false; o_Log_Description_id.col_type.bNULL = false; Add(o_Log_Description_id); }
public LogFile_Attachment_VIEW(Log_DBConnection xcon) { select = new selection(this); where = new WHERE(this); m_con = xcon; tablename = LogFile_Attachment_VIEW.tablename_const; myUpdateObjects = UpdateObjects; o_id.col_name = LogFile_Attachment_VIEW.id.name; o_id.col_type.m_Type = "int"; Add(o_id); o_LogFile_id.col_name = LogFile_Attachment_VIEW.LogFile_id.name; o_LogFile_id.col_type.m_Type = "int"; Add(o_LogFile_id); o_Attachment.col_name = LogFile_Attachment_VIEW.Attachment.name; o_Attachment.col_type.m_Type = "varbinary"; Add(o_Attachment); o_Attachment_type.col_name = LogFile_Attachment_VIEW.Attachment_type.name; o_Attachment_type.col_type.m_Type = "nvarchar"; Add(o_Attachment_type); o_LogFile_Attachment_Type_id.col_name = LogFile_Attachment_VIEW.LogFile_Attachment_Type_id.name; o_LogFile_Attachment_Type_id.col_type.m_Type = "int"; Add(o_LogFile_Attachment_Type_id); }
public LogFile_Attachment(Log_DBConnection xcon) { select = new selection(this); where = new WHERE(this); m_con = xcon; tablename = LogFile_Attachment.tablename_const; myUpdateObjects = UpdateObjects; o_id.col_name = LogFile_Attachment.id.name; o_id.col_type.m_Type = "int"; o_id.col_type.bPRIMARY_KEY = true; o_id.col_type.bFOREIGN_KEY = false; o_id.col_type.bUNIQUE = false; o_id.col_type.bNULL = false; Add(o_id); o_LogFile_id.col_name = LogFile_Attachment.LogFile_id.name; o_LogFile_id.col_type.m_Type = "int"; o_LogFile_id.col_type.bPRIMARY_KEY = false; o_LogFile_id.col_type.bFOREIGN_KEY = true; o_LogFile_id.col_type.bUNIQUE = false; o_LogFile_id.col_type.bNULL = false; Add(o_LogFile_id); o_LogFile_Attachment_Type_id.col_name = LogFile_Attachment.LogFile_Attachment_Type_id.name; o_LogFile_Attachment_Type_id.col_type.m_Type = "int"; o_LogFile_Attachment_Type_id.col_type.bPRIMARY_KEY = false; o_LogFile_Attachment_Type_id.col_type.bFOREIGN_KEY = true; o_LogFile_Attachment_Type_id.col_type.bUNIQUE = false; o_LogFile_Attachment_Type_id.col_type.bNULL = false; Add(o_LogFile_Attachment_Type_id); o_Attachment.col_name = LogFile_Attachment.Attachment.name; o_Attachment.col_type.m_Type = "varbinary"; o_Attachment.col_type.bPRIMARY_KEY = false; o_Attachment.col_type.bFOREIGN_KEY = false; o_Attachment.col_type.bUNIQUE = false; o_Attachment.col_type.bNULL = true; Add(o_Attachment); o_Description.col_name = LogFile_Attachment.Description.name; o_Description.col_type.m_Type = "nvarchar"; o_Description.col_type.bPRIMARY_KEY = false; o_Description.col_type.bFOREIGN_KEY = false; o_Description.col_type.bUNIQUE = false; o_Description.col_type.bNULL = true; Add(o_Description); }
public Login_VIEW(DBConnectionControl40.DBConnection xcon) { select = new selection(this); where = new WHERE(this); m_con = xcon; tablename = Login_VIEW.tablename_const; myUpdateObjects = UpdateObjects; o_LoginUsers_id.col_name = Login_VIEW.Users_id.name; o_LoginUsers_id.col_type.m_Type = "int"; Add(o_LoginUsers_id); o_first_name.col_name = Login_VIEW.first_name.name; o_first_name.col_type.m_Type = "nvarchar"; Add(o_first_name); o_last_name.col_name = Login_VIEW.last_name.name; o_last_name.col_type.m_Type = "nvarchar"; Add(o_last_name); o_Identity.col_name = Login_VIEW.Identity.name; o_Identity.col_type.m_Type = "nvarchar"; Add(o_Identity); o_Contact.col_name = Login_VIEW.Contact.name; o_Contact.col_type.m_Type = "nvarchar"; Add(o_Contact); o_username.col_name = Login_VIEW.username.name; o_username.col_type.m_Type = "nvarchar"; Add(o_username); o_password.col_name = Login_VIEW.password.name; o_password.col_type.m_Type = "varbinary"; Add(o_password); o_PasswordNeverExpires.col_name = Login_VIEW.PasswordNeverExpires.name; o_PasswordNeverExpires.col_type.m_Type = "bit"; Add(o_PasswordNeverExpires); o_enabled.col_name = Login_VIEW.enabled.name; o_enabled.col_type.m_Type = "bit"; Add(o_enabled); o_Maximum_password_age_in_days.col_name = Login_VIEW.Maximum_password_age_in_days.name; o_Maximum_password_age_in_days.col_type.m_Type = "int"; Add(o_Maximum_password_age_in_days); o_NotActiveAfterPasswordExpires.col_name = Login_VIEW.NotActiveAfterPasswordExpires.name; o_NotActiveAfterPasswordExpires.col_type.m_Type = "bit"; Add(o_NotActiveAfterPasswordExpires); o_Time_When_UserSetsItsOwnPassword_FirstTime.col_name = Login_VIEW.Time_When_UserSetsItsOwnPassword_FirstTime.name; o_Time_When_UserSetsItsOwnPassword_FirstTime.col_type.m_Type = "datetime"; Add(o_Time_When_UserSetsItsOwnPassword_FirstTime); o_Time_When_UserSetsItsOwnPassword_LastTime.col_name = Login_VIEW.Time_When_UserSetsItsOwnPassword_LastTime.name; o_Time_When_UserSetsItsOwnPassword_LastTime.col_type.m_Type = "datetime"; Add(o_Time_When_UserSetsItsOwnPassword_LastTime); o_ChangePasswordOnFirstLogin.col_name = Login_VIEW.ChangePasswordOnFirstLogin.name; o_ChangePasswordOnFirstLogin.col_type.m_Type = "bit"; Add(o_ChangePasswordOnFirstLogin); o_Role_id.col_name = Login_VIEW.Role_id.name; o_Role_id.col_type.m_Type = "int"; Add(o_Role_id); o_Role_Name.col_name = Login_VIEW.Role_Name.name; o_Role_Name.col_type.m_Type = "nvarchar"; Add(o_Role_Name); o_Role_PrivilegesLevel.col_name = Login_VIEW.Role_PrivilegesLevel.name; o_Role_PrivilegesLevel.col_type.m_Type = "int"; Add(o_Role_PrivilegesLevel); o_Role_description.col_name = Login_VIEW.Role_description.name; o_Role_description.col_type.m_Type = "nvarchar"; Add(o_Role_description); }
public LoginUsersAndLoginRoles(DBConnectionControl40.DBConnection xcon) { select = new selection(this); where = new WHERE(this); m_con = xcon; tablename = LoginUsersAndLoginRoles.tablename_const; myUpdateObjects = UpdateObjects; o_id.col_name = LoginUsersAndLoginRoles.id.name; o_id.col_type.m_Type = "int"; o_id.col_type.bPRIMARY_KEY = true; o_id.col_type.bFOREIGN_KEY = false; o_id.col_type.bUNIQUE = false; o_id.col_type.bNULL = false; Add(o_id); o_LoginUsers_id.col_name = LoginUsersAndLoginRoles.LoginUsers_id.name; o_LoginUsers_id.col_type.m_Type = "int"; o_LoginUsers_id.col_type.bPRIMARY_KEY = false; o_LoginUsers_id.col_type.bFOREIGN_KEY = false; o_LoginUsers_id.col_type.bUNIQUE = false; o_LoginUsers_id.col_type.bNULL = false; Add(o_LoginUsers_id); o_LoginRoles_id.col_name = LoginUsersAndLoginRoles.LoginRoles_id.name; o_LoginRoles_id.col_type.m_Type = "int"; o_LoginRoles_id.col_type.bPRIMARY_KEY = false; o_LoginRoles_id.col_type.bFOREIGN_KEY = false; o_LoginRoles_id.col_type.bUNIQUE = false; o_LoginRoles_id.col_type.bNULL = false; Add(o_LoginRoles_id); }
public LoginUsers(DBConnectionControl40.DBConnection xcon) { select = new selection(this); where = new WHERE(this); m_con = xcon; tablename = LoginUsers.tablename_const; myUpdateObjects = UpdateObjects; o_id.col_name = LoginUsers.id.name; o_id.col_type.m_Type = "int"; o_id.col_type.bPRIMARY_KEY = true; o_id.col_type.bFOREIGN_KEY = false; o_id.col_type.bUNIQUE = false; o_id.col_type.bNULL = false; Add(o_id); o_first_name.col_name = LoginUsers.first_name.name; o_first_name.col_type.m_Type = "nvarchar"; o_first_name.col_type.bPRIMARY_KEY = false; o_first_name.col_type.bFOREIGN_KEY = false; o_first_name.col_type.bUNIQUE = false; o_first_name.col_type.bNULL = true; Add(o_first_name); o_last_name.col_name = LoginUsers.last_name.name; o_last_name.col_type.m_Type = "nvarchar"; o_last_name.col_type.bPRIMARY_KEY = false; o_last_name.col_type.bFOREIGN_KEY = false; o_last_name.col_type.bUNIQUE = false; o_last_name.col_type.bNULL = true; Add(o_last_name); o_Identity.col_name = LoginUsers.Identity.name; o_Identity.col_type.m_Type = "nvarchar"; o_Identity.col_type.bPRIMARY_KEY = false; o_Identity.col_type.bFOREIGN_KEY = false; o_Identity.col_type.bUNIQUE = false; o_Identity.col_type.bNULL = true; Add(o_Identity); o_Contact.col_name = LoginUsers.Contact.name; o_Contact.col_type.m_Type = "nvarchar"; o_Contact.col_type.bPRIMARY_KEY = false; o_Contact.col_type.bFOREIGN_KEY = false; o_Contact.col_type.bUNIQUE = false; o_Contact.col_type.bNULL = true; Add(o_Contact); o_username.col_name = LoginUsers.username.name; o_username.col_type.m_Type = "nvarchar"; o_username.col_type.bPRIMARY_KEY = false; o_username.col_type.bFOREIGN_KEY = false; o_username.col_type.bUNIQUE = true; o_username.col_type.bNULL = false; Add(o_username); o_password.col_name = LoginUsers.password.name; o_password.col_type.m_Type = "varbinary"; o_password.col_type.bPRIMARY_KEY = false; o_password.col_type.bFOREIGN_KEY = false; o_password.col_type.bUNIQUE = false; o_password.col_type.bNULL = true; Add(o_password); o_enabled.col_name = LoginUsers.enabled.name; o_enabled.col_type.m_Type = "bit"; o_enabled.col_type.bPRIMARY_KEY = false; o_enabled.col_type.bFOREIGN_KEY = false; o_enabled.col_type.bUNIQUE = false; o_enabled.col_type.bNULL = false; Add(o_enabled); o_ChangePasswordOnFirstLogin.col_name = LoginUsers.ChangePasswordOnFirstLogin.name; o_ChangePasswordOnFirstLogin.col_type.m_Type = "bit"; o_ChangePasswordOnFirstLogin.col_type.bPRIMARY_KEY = false; o_ChangePasswordOnFirstLogin.col_type.bFOREIGN_KEY = false; o_ChangePasswordOnFirstLogin.col_type.bUNIQUE = false; o_ChangePasswordOnFirstLogin.col_type.bNULL = false; Add(o_ChangePasswordOnFirstLogin); o_Time_When_AdministratorSetsPassword.col_name = LoginUsers.Time_When_AdministratorSetsPassword.name; o_Time_When_AdministratorSetsPassword.col_type.m_Type = "datetime"; o_Time_When_AdministratorSetsPassword.col_type.bPRIMARY_KEY = false; o_Time_When_AdministratorSetsPassword.col_type.bFOREIGN_KEY = false; o_Time_When_AdministratorSetsPassword.col_type.bUNIQUE = false; o_Time_When_AdministratorSetsPassword.col_type.bNULL = true; Add(o_Time_When_AdministratorSetsPassword); o_Administrator_LoginUsers_id.col_name = LoginUsers.Administrator_LoginUsers_id.name; o_Administrator_LoginUsers_id.col_type.m_Type = "int"; o_Administrator_LoginUsers_id.col_type.bPRIMARY_KEY = false; o_Administrator_LoginUsers_id.col_type.bFOREIGN_KEY = false; o_Administrator_LoginUsers_id.col_type.bUNIQUE = false; o_Administrator_LoginUsers_id.col_type.bNULL = true; Add(o_Administrator_LoginUsers_id); o_Time_When_UserSetsItsOwnPassword_FirstTime.col_name = LoginUsers.Time_When_UserSetsItsOwnPassword_FirstTime.name; o_Time_When_UserSetsItsOwnPassword_FirstTime.col_type.m_Type = "datetime"; o_Time_When_UserSetsItsOwnPassword_FirstTime.col_type.bPRIMARY_KEY = false; o_Time_When_UserSetsItsOwnPassword_FirstTime.col_type.bFOREIGN_KEY = false; o_Time_When_UserSetsItsOwnPassword_FirstTime.col_type.bUNIQUE = false; o_Time_When_UserSetsItsOwnPassword_FirstTime.col_type.bNULL = true; Add(o_Time_When_UserSetsItsOwnPassword_FirstTime); o_Time_When_UserSetsItsOwnPassword_LastTime.col_name = LoginUsers.Time_When_UserSetsItsOwnPassword_LastTime.name; o_Time_When_UserSetsItsOwnPassword_LastTime.col_type.m_Type = "datetime"; o_Time_When_UserSetsItsOwnPassword_LastTime.col_type.bPRIMARY_KEY = false; o_Time_When_UserSetsItsOwnPassword_LastTime.col_type.bFOREIGN_KEY = false; o_Time_When_UserSetsItsOwnPassword_LastTime.col_type.bUNIQUE = false; o_Time_When_UserSetsItsOwnPassword_LastTime.col_type.bNULL = true; Add(o_Time_When_UserSetsItsOwnPassword_LastTime); o_PasswordNeverExpires.col_name = LoginUsers.PasswordNeverExpires.name; o_PasswordNeverExpires.col_type.m_Type = "bit"; o_PasswordNeverExpires.col_type.bPRIMARY_KEY = false; o_PasswordNeverExpires.col_type.bFOREIGN_KEY = false; o_PasswordNeverExpires.col_type.bUNIQUE = false; o_PasswordNeverExpires.col_type.bNULL = false; Add(o_PasswordNeverExpires); o_Maximum_password_age_in_days.col_name = LoginUsers.Maximum_password_age_in_days.name; o_Maximum_password_age_in_days.col_type.m_Type = "int"; o_Maximum_password_age_in_days.col_type.bPRIMARY_KEY = false; o_Maximum_password_age_in_days.col_type.bFOREIGN_KEY = false; o_Maximum_password_age_in_days.col_type.bUNIQUE = false; o_Maximum_password_age_in_days.col_type.bNULL = true; Add(o_Maximum_password_age_in_days); o_NotActiveAfterPasswordExpires.col_name = LoginUsers.NotActiveAfterPasswordExpires.name; o_NotActiveAfterPasswordExpires.col_type.m_Type = "bit"; o_NotActiveAfterPasswordExpires.col_type.bPRIMARY_KEY = false; o_NotActiveAfterPasswordExpires.col_type.bFOREIGN_KEY = false; o_NotActiveAfterPasswordExpires.col_type.bUNIQUE = false; o_NotActiveAfterPasswordExpires.col_type.bNULL = false; Add(o_NotActiveAfterPasswordExpires); }
/// <summary> /// 条件语句where /// </summary> /// <param name="conditionColumnNames">条件列名数组</param> /// <param name="conditionValues">条件值数组</param> /// <param name="where">多条件之间并列关系,AND,OR</param> /// <param name="relation">列名和值的关系,相等,大于,小于,不等于,范围</param> /// <returns>完整版字符串</returns> public static string FormatWHERE(string[] conditionColumnNames, string[] conditionValues, WHERE[] where, Relation[] relation) { StringBuilder sqlWHERE = new StringBuilder("WHERE "); for (int i = 0; i < conditionColumnNames.Length; i++) { sqlWHERE.AppendFormat("([{0}] {1} N'{2}')", conditionColumnNames[i], GetRelation(relation[i]), conditionValues[i]); if (i < conditionColumnNames.Length - 1) { sqlWHERE.AppendFormat(" {0} ", GetWhere(where[i])); } } return sqlWHERE.ToString(); }
public Log_VIEW(Log_DBConnection xcon) { select = new selection(this); where = new WHERE(this); m_con = xcon; tablename = Log_VIEW.tablename_const; myUpdateObjects = UpdateObjects; o_LogFile_id.col_name = Log_VIEW.LogFile_id.name; o_LogFile_id.col_type.m_Type = "int"; Add(o_LogFile_id); o_Log_Time.col_name = Log_VIEW.Log_Time.name; o_Log_Time.col_type.m_Type = "datetime"; Add(o_Log_Time); o_Log_TypeName.col_name = Log_VIEW.Log_TypeName.name; o_Log_TypeName.col_type.m_Type = "nvarchar"; Add(o_Log_TypeName); o_Log_Type_id.col_name = Log_VIEW.Log_Type_id.name; o_Log_Type_id.col_type.m_Type = "int"; Add(o_Log_Type_id); o_Log_Description.col_name = Log_VIEW.Log_Description.name; o_Log_Description.col_type.m_Type = "nvarchar"; Add(o_Log_Description); o_Log_Description_id.col_name = Log_VIEW.Log_Description_id.name; o_Log_Description_id.col_type.m_Type = "int"; Add(o_Log_Description_id); o_ComputerName.col_name = Log_VIEW.ComputerName.name; o_ComputerName.col_type.m_Type = "nvarchar"; Add(o_ComputerName); o_Log_Computer_id.col_name = Log_VIEW.Log_Computer_id.name; o_Log_Computer_id.col_type.m_Type = "int"; Add(o_Log_Computer_id); o_UserName.col_name = Log_VIEW.UserName.name; o_UserName.col_type.m_Type = "nvarchar"; Add(o_UserName); o_Log_UserName_id.col_name = Log_VIEW.Log_UserName_id.name; o_Log_UserName_id.col_type.m_Type = "int"; Add(o_Log_UserName_id); o_Program.col_name = Log_VIEW.Program.name; o_Program.col_type.m_Type = "nvarchar"; Add(o_Program); o_Log_Program_id.col_name = Log_VIEW.Log_Program_id.name; o_Log_Program_id.col_type.m_Type = "int"; Add(o_Log_Program_id); o_LogFile_Description.col_name = Log_VIEW.LogFile_Description.name; o_LogFile_Description.col_type.m_Type = "nvarchar"; Add(o_LogFile_Description); o_LogFile_Description_id.col_name = Log_VIEW.LogFile_Description_id.name; o_LogFile_Description_id.col_type.m_Type = "int"; Add(o_LogFile_Description_id); }
public LoginManagerJournal_VIEW(DBConnectionControl40.DBConnection xcon) { select = new selection(this); where = new WHERE(this); m_con = xcon; tablename = LoginManagerJournal_VIEW.tablename_const; myUpdateObjects = UpdateObjects; o_Time.col_name = LoginManagerJournal_VIEW.Time.name; o_Time.col_type.m_Type = "datetime"; Add(o_Time); o_LoginManagerEvent_id.col_name = LoginManagerJournal_VIEW.LoginManagerEvent_id.name; o_LoginManagerEvent_id.col_type.m_Type = "int"; Add(o_LoginManagerEvent_id); o_LoginManagerEvent_Message.col_name = LoginManagerJournal_VIEW.LoginManagerEvent_Message.name; o_LoginManagerEvent_Message.col_type.m_Type = "nvarchar"; Add(o_LoginManagerEvent_Message); o_LoginUsers_id.col_name = LoginManagerJournal_VIEW.LoginUsers_id.name; o_LoginUsers_id.col_type.m_Type = "int"; Add(o_LoginUsers_id); o_username.col_name = LoginManagerJournal_VIEW.username.name; o_username.col_type.m_Type = "nvarchar"; Add(o_username); }
public LoginManagerJournal(DBConnectionControl40.DBConnection xcon) { select = new selection(this); where = new WHERE(this); m_con = xcon; tablename = LoginManagerJournal.tablename_const; myUpdateObjects = UpdateObjects; o_id.col_name = LoginManagerJournal.id.name; o_id.col_type.m_Type = "int"; o_id.col_type.bPRIMARY_KEY = true; o_id.col_type.bFOREIGN_KEY = false; o_id.col_type.bUNIQUE = false; o_id.col_type.bNULL = false; Add(o_id); o_LoginUsers_id.col_name = LoginManagerJournal.LoginUsers_id.name; o_LoginUsers_id.col_type.m_Type = "int"; o_LoginUsers_id.col_type.bPRIMARY_KEY = false; o_LoginUsers_id.col_type.bFOREIGN_KEY = false; o_LoginUsers_id.col_type.bUNIQUE = false; o_LoginUsers_id.col_type.bNULL = false; Add(o_LoginUsers_id); o_LoginManagerEvent_id.col_name = LoginManagerJournal.LoginManagerEvent_id.name; o_LoginManagerEvent_id.col_type.m_Type = "int"; o_LoginManagerEvent_id.col_type.bPRIMARY_KEY = false; o_LoginManagerEvent_id.col_type.bFOREIGN_KEY = false; o_LoginManagerEvent_id.col_type.bUNIQUE = false; o_LoginManagerEvent_id.col_type.bNULL = false; Add(o_LoginManagerEvent_id); o_Time.col_name = LoginManagerJournal.Time.name; o_Time.col_type.m_Type = "datetime"; o_Time.col_type.bPRIMARY_KEY = false; o_Time.col_type.bFOREIGN_KEY = false; o_Time.col_type.bUNIQUE = false; o_Time.col_type.bNULL = false; Add(o_Time); }
public sysdiagrams(Log_DBConnection xcon) { select = new selection(this); where = new WHERE(this); m_con = xcon; tablename = sysdiagrams.tablename_const; myUpdateObjects = UpdateObjects; o_name.col_name = sysdiagrams.name__.name; o_name.col_type.m_Type = "nvarchar"; o_name.col_type.bPRIMARY_KEY = false; o_name.col_type.bFOREIGN_KEY = false; o_name.col_type.bUNIQUE = true; o_name.col_type.bNULL = false; Add(o_name); o_principal_id.col_name = sysdiagrams.principal_id.name; o_principal_id.col_type.m_Type = "int"; o_principal_id.col_type.bPRIMARY_KEY = false; o_principal_id.col_type.bFOREIGN_KEY = false; o_principal_id.col_type.bUNIQUE = true; o_principal_id.col_type.bNULL = false; Add(o_principal_id); o_diagram_id.col_name = sysdiagrams.diagram_id.name; o_diagram_id.col_type.m_Type = "int"; o_diagram_id.col_type.bPRIMARY_KEY = true; o_diagram_id.col_type.bFOREIGN_KEY = false; o_diagram_id.col_type.bUNIQUE = false; o_diagram_id.col_type.bNULL = false; Add(o_diagram_id); o_version.col_name = sysdiagrams.version.name; o_version.col_type.m_Type = "int"; o_version.col_type.bPRIMARY_KEY = false; o_version.col_type.bFOREIGN_KEY = false; o_version.col_type.bUNIQUE = false; o_version.col_type.bNULL = true; Add(o_version); o_definition.col_name = sysdiagrams.definition.name; o_definition.col_type.m_Type = "varbinary"; o_definition.col_type.bPRIMARY_KEY = false; o_definition.col_type.bFOREIGN_KEY = false; o_definition.col_type.bUNIQUE = false; o_definition.col_type.bNULL = true; Add(o_definition); }
/// <summary> /// 将where枚举返回sql语句 /// </summary> /// <param name="where">where枚举</param> /// <returns>返回sql语句,错误返回空字符串</returns> private static string GetWhere(WHERE where) { switch (where) { case WHERE.AND: return "AND"; case WHERE.OR: return "OR"; case WHERE.NOT: return "NOT"; default: return string.Empty; } }
/// <summary> /// 删除方法 /// </summary> /// <param name="tableName">表名</param> /// <param name="columnValues">条件列名数组</param> /// <param name="conditionColumnNames"></param> /// <param name="conditionValues">条件列值</param> /// <param name="where">where条件并列条件</param> /// <param name="relation">列和值的关系</param> /// <returns>完整版连接字符串</returns> internal int DeleteRows(string tableName, string[] columnNames, string[] columnValues, WHERE[] where, Relation[] relation) { return UpdateDB(FormatDELETE(tableName, columnNames, columnValues, where, relation)); }
public LoginSession(DBConnectionControl40.DBConnection xcon) { select = new selection(this); where = new WHERE(this); m_con = xcon; tablename = LoginSession.tablename_const; myUpdateObjects = UpdateObjects; o_id.col_name = LoginSession.id.name; o_id.col_type.m_Type = "int"; o_id.col_type.bPRIMARY_KEY = true; o_id.col_type.bFOREIGN_KEY = false; o_id.col_type.bUNIQUE = false; o_id.col_type.bNULL = false; Add(o_id); o_LoginUsers_id.col_name = LoginSession.LoginUsers_id.name; o_LoginUsers_id.col_type.m_Type = "int"; o_LoginUsers_id.col_type.bPRIMARY_KEY = false; o_LoginUsers_id.col_type.bFOREIGN_KEY = false; o_LoginUsers_id.col_type.bUNIQUE = false; o_LoginUsers_id.col_type.bNULL = false; Add(o_LoginUsers_id); o_Login_time.col_name = LoginSession.Login_time.name; o_Login_time.col_type.m_Type = "datetime"; o_Login_time.col_type.bPRIMARY_KEY = false; o_Login_time.col_type.bFOREIGN_KEY = false; o_Login_time.col_type.bUNIQUE = false; o_Login_time.col_type.bNULL = false; Add(o_Login_time); o_Logout_time.col_name = LoginSession.Logout_time.name; o_Logout_time.col_type.m_Type = "datetime"; o_Logout_time.col_type.bPRIMARY_KEY = false; o_Logout_time.col_type.bFOREIGN_KEY = false; o_Logout_time.col_type.bUNIQUE = false; o_Logout_time.col_type.bNULL = true; Add(o_Logout_time); o_LoginComputer_id.col_name = LoginSession.LoginComputer_id.name; o_LoginComputer_id.col_type.m_Type = "int"; o_LoginComputer_id.col_type.bPRIMARY_KEY = false; o_LoginComputer_id.col_type.bFOREIGN_KEY = false; o_LoginComputer_id.col_type.bUNIQUE = false; o_LoginComputer_id.col_type.bNULL = true; Add(o_LoginComputer_id); o_LoginComputerUser_id.col_name = LoginSession.LoginComputerUser_id.name; o_LoginComputerUser_id.col_type.m_Type = "int"; o_LoginComputerUser_id.col_type.bPRIMARY_KEY = false; o_LoginComputerUser_id.col_type.bFOREIGN_KEY = false; o_LoginComputerUser_id.col_type.bUNIQUE = false; o_LoginComputerUser_id.col_type.bNULL = true; Add(o_LoginComputerUser_id); }
public Log_UserName(Log_DBConnection xcon) { select = new selection(this); where = new WHERE(this); m_con = xcon; tablename = Log_UserName.tablename_const; myUpdateObjects = UpdateObjects; o_id.col_name = Log_UserName.id.name; o_id.col_type.m_Type = "int"; o_id.col_type.bPRIMARY_KEY = true; o_id.col_type.bFOREIGN_KEY = false; o_id.col_type.bUNIQUE = false; o_id.col_type.bNULL = false; Add(o_id); o_UserName.col_name = Log_UserName.UserName.name; o_UserName.col_type.m_Type = "nvarchar"; o_UserName.col_type.bPRIMARY_KEY = false; o_UserName.col_type.bFOREIGN_KEY = false; o_UserName.col_type.bUNIQUE = false; o_UserName.col_type.bNULL = false; Add(o_UserName); }
public LoginSession_VIEW(DBConnectionControl40.DBConnection xcon) { select = new selection(this); where = new WHERE(this); m_con = xcon; tablename = LoginSession_VIEW.tablename_const; myUpdateObjects = UpdateObjects; o_LoginSession_id.col_name = LoginSession_VIEW.LoginSession_id.name; o_LoginSession_id.col_type.m_Type = "int"; Add(o_LoginSession_id); o_LoginUsers_id.col_name = LoginSession_VIEW.LoginUsers_id.name; o_LoginUsers_id.col_type.m_Type = "int"; Add(o_LoginUsers_id); o_username.col_name = LoginSession_VIEW.username.name; o_username.col_type.m_Type = "nvarchar"; Add(o_username); o_first_name.col_name = LoginSession_VIEW.first_name.name; o_first_name.col_type.m_Type = "nvarchar"; Add(o_first_name); o_last_name.col_name = LoginSession_VIEW.last_name.name; o_last_name.col_type.m_Type = "nvarchar"; Add(o_last_name); o_Identity.col_name = LoginSession_VIEW.Identity.name; o_Identity.col_type.m_Type = "nvarchar"; Add(o_Identity); o_Contact.col_name = LoginSession_VIEW.Contact.name; o_Contact.col_type.m_Type = "nvarchar"; Add(o_Contact); o_Login_time.col_name = LoginSession_VIEW.Login_time.name; o_Login_time.col_type.m_Type = "datetime"; Add(o_Login_time); o_Logout_time.col_name = LoginSession_VIEW.Logout_time.name; o_Logout_time.col_type.m_Type = "datetime"; Add(o_Logout_time); o_LoginComputer_id.col_name = LoginSession_VIEW.LoginComputer_id.name; o_LoginComputer_id.col_type.m_Type = "int"; Add(o_LoginComputer_id); o_ComputerName.col_name = LoginSession_VIEW.ComputerName.name; o_ComputerName.col_type.m_Type = "nvarchar"; Add(o_ComputerName); o_LoginComputerUser_id.col_name = LoginSession_VIEW.LoginComputerUser_id.name; o_LoginComputerUser_id.col_type.m_Type = "int"; Add(o_LoginComputerUser_id); o_ComputerUserName.col_name = LoginSession_VIEW.ComputerUserName.name; o_ComputerUserName.col_type.m_Type = "nvarchar"; Add(o_ComputerUserName); }
public LogFile(Log_DBConnection xcon) { select = new selection(this); where = new WHERE(this); m_con = xcon; tablename = LogFile.tablename_const; myUpdateObjects = UpdateObjects; o_id.col_name = LogFile.id.name; o_id.col_type.m_Type = "int"; o_id.col_type.bPRIMARY_KEY = true; o_id.col_type.bFOREIGN_KEY = false; o_id.col_type.bUNIQUE = false; o_id.col_type.bNULL = false; Add(o_id); o_LogFileImportTime.col_name = LogFile.LogFileImportTime.name; o_LogFileImportTime.col_type.m_Type = "datetime"; o_LogFileImportTime.col_type.bPRIMARY_KEY = false; o_LogFileImportTime.col_type.bFOREIGN_KEY = false; o_LogFileImportTime.col_type.bUNIQUE = false; o_LogFileImportTime.col_type.bNULL = false; Add(o_LogFileImportTime); o_Log_Computer_id.col_name = LogFile.Log_Computer_id.name; o_Log_Computer_id.col_type.m_Type = "int"; o_Log_Computer_id.col_type.bPRIMARY_KEY = false; o_Log_Computer_id.col_type.bFOREIGN_KEY = true; o_Log_Computer_id.col_type.bUNIQUE = false; o_Log_Computer_id.col_type.bNULL = false; Add(o_Log_Computer_id); o_Log_UserName_id.col_name = LogFile.Log_UserName_id.name; o_Log_UserName_id.col_type.m_Type = "int"; o_Log_UserName_id.col_type.bPRIMARY_KEY = false; o_Log_UserName_id.col_type.bFOREIGN_KEY = true; o_Log_UserName_id.col_type.bUNIQUE = false; o_Log_UserName_id.col_type.bNULL = true; Add(o_Log_UserName_id); o_Log_Program_id.col_name = LogFile.Log_Program_id.name; o_Log_Program_id.col_type.m_Type = "int"; o_Log_Program_id.col_type.bPRIMARY_KEY = false; o_Log_Program_id.col_type.bFOREIGN_KEY = true; o_Log_Program_id.col_type.bUNIQUE = false; o_Log_Program_id.col_type.bNULL = false; Add(o_Log_Program_id); o_Log_PathFile_id.col_name = LogFile.Log_PathFile_id.name; o_Log_PathFile_id.col_type.m_Type = "int"; o_Log_PathFile_id.col_type.bPRIMARY_KEY = false; o_Log_PathFile_id.col_type.bFOREIGN_KEY = true; o_Log_PathFile_id.col_type.bUNIQUE = false; o_Log_PathFile_id.col_type.bNULL = false; Add(o_Log_PathFile_id); o_LogFile_Description_id.col_name = LogFile.LogFile_Description_id.name; o_LogFile_Description_id.col_type.m_Type = "int"; o_LogFile_Description_id.col_type.bPRIMARY_KEY = false; o_LogFile_Description_id.col_type.bFOREIGN_KEY = true; o_LogFile_Description_id.col_type.bUNIQUE = false; o_LogFile_Description_id.col_type.bNULL = true; Add(o_LogFile_Description_id); }
/// <summary> /// 单表查询字符串 /// </summary> /// <param name="tableName">表名</param> /// <param name="columnNames">想要的列名数组</param> /// <param name="conditionColumnNames">条件列名数组</param> /// <param name="conditionValues">条件列值</param> /// <param name="where">where条件并列条件</param> /// <param name="relation">列和值的关系</param> /// <returns>完整版连接字符串</returns> public static string FormatSelectWhere(string tableName, string[] columnNames, string[] conditionColumnNames, string[] conditionValues, WHERE[] where, Relation[] relation) { StringBuilder sqlSearch = new StringBuilder("SELECT "); foreach (string columnName in columnNames) { sqlSearch.AppendFormat("[{0}],", columnName); } sqlSearch.Append(","); sqlSearch.Replace(",,", ""); sqlSearch.AppendFormat(" FROM [{0}] ", tableName); sqlSearch.Append(" WHERE "); for (int i = 0; i < conditionColumnNames.Length; i++) { sqlSearch.AppendFormat("([{0}] {1} N'{2}')", conditionColumnNames[i], GetRelation(relation[i]), conditionValues[i]); if (i < conditionColumnNames.Length - 1) { sqlSearch.AppendFormat(" {0} ", GetWhere(where[i])); } } return sqlSearch.ToString(); }
/// <summary> /// 删除字符串 /// </summary> /// <param name="tableName">表名</param> /// <param name="columnNames">where条件列名</param> /// <param name="columnValues">条件值数组</param> /// <param name="where">多条件之间并列关系,AND,OR</param> /// <param name="relation">列名和值的关系,相等,大于,小于,不等于,范围</param> /// <returns>完整版字符串</returns> public static string FormatDELETE(string tableName, string[] columnNames, string[] columnValues, WHERE[] where, Relation[] relation) { StringBuilder sqlDELETE = new StringBuilder("DELETE FROM "); sqlDELETE.AppendFormat("[{0}] ", tableName); sqlDELETE.Append(FormatWHERE(columnNames, columnValues, where, relation)); return sqlDELETE.ToString(); }
public LoginFailed(DBConnectionControl40.DBConnection xcon) { select = new selection(this); where = new WHERE(this); m_con = xcon; tablename = LoginFailed.tablename_const; myUpdateObjects = UpdateObjects; o_id.col_name = LoginFailed.id.name; o_id.col_type.m_Type = "int"; o_id.col_type.bPRIMARY_KEY = true; o_id.col_type.bFOREIGN_KEY = false; o_id.col_type.bUNIQUE = false; o_id.col_type.bNULL = false; Add(o_id); o_username.col_name = LoginFailed.username.name; o_username.col_type.m_Type = "nvarchar"; o_username.col_type.bPRIMARY_KEY = false; o_username.col_type.bFOREIGN_KEY = false; o_username.col_type.bUNIQUE = false; o_username.col_type.bNULL = false; Add(o_username); o_AttemptTime.col_name = LoginFailed.AttemptTime.name; o_AttemptTime.col_type.m_Type = "datetime"; o_AttemptTime.col_type.bPRIMARY_KEY = false; o_AttemptTime.col_type.bFOREIGN_KEY = false; o_AttemptTime.col_type.bUNIQUE = false; o_AttemptTime.col_type.bNULL = false; Add(o_AttemptTime); o_username_does_not_exist.col_name = LoginFailed.username_does_not_exist.name; o_username_does_not_exist.col_type.m_Type = "bit"; o_username_does_not_exist.col_type.bPRIMARY_KEY = false; o_username_does_not_exist.col_type.bFOREIGN_KEY = false; o_username_does_not_exist.col_type.bUNIQUE = false; o_username_does_not_exist.col_type.bNULL = false; Add(o_username_does_not_exist); o_password_wrong.col_name = LoginFailed.password_wrong.name; o_password_wrong.col_type.m_Type = "bit"; o_password_wrong.col_type.bPRIMARY_KEY = false; o_password_wrong.col_type.bFOREIGN_KEY = false; o_password_wrong.col_type.bUNIQUE = false; o_password_wrong.col_type.bNULL = true; Add(o_password_wrong); o_LoginComputer_id.col_name = LoginFailed.LoginComputer_id.name; o_LoginComputer_id.col_type.m_Type = "int"; o_LoginComputer_id.col_type.bPRIMARY_KEY = false; o_LoginComputer_id.col_type.bFOREIGN_KEY = false; o_LoginComputer_id.col_type.bUNIQUE = false; o_LoginComputer_id.col_type.bNULL = true; Add(o_LoginComputer_id); o_LoginComputerUser_id.col_name = LoginFailed.LoginComputerUser_id.name; o_LoginComputerUser_id.col_type.m_Type = "int"; o_LoginComputerUser_id.col_type.bPRIMARY_KEY = false; o_LoginComputerUser_id.col_type.bFOREIGN_KEY = false; o_LoginComputerUser_id.col_type.bUNIQUE = false; o_LoginComputerUser_id.col_type.bNULL = true; Add(o_LoginComputerUser_id); }