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);
}
