SQLite Users Table Creation and C# Connection Example

Create a 'Users' Table in SQLite Database

The following SQL code creates a 'Users' table with various fields and indexes:

create table Users (
	PID INTEGER not null
		primary key autoincrement,
	UserName varchar(50) not null,
	Password varchar(50) not null,
	RealName varchar(50) default '' not null,
	Telephone varchar(50) default '' not null,
	Phone varchar(50) default '' not null,
	GroupID INT default 2 not null,
	RegisterDate datetime not null,
	LastLoginDate datetime not null
);

create index Users_PID_index
	on Users (PID);

create index Users_UserName_index
	on Users (UserName);

This code defines the 'Users' table with the following columns:

• PID: Integer primary key, auto-incrementing for unique user identification.
• UserName: VARCHAR(50), not null, for the user's username.
• Password: VARCHAR(50), not null, for the user's password.
• RealName: VARCHAR(50), default '', not null, for the user's real name.
• Telephone: VARCHAR(50), default '', not null, for the user's telephone number.
• Phone: VARCHAR(50), default '', not null, for the user's phone number.
• GroupID: INT, default 2, not null, for the user's group ID.
• RegisterDate: DATETIME, not null, for the user's registration date.
• LastLoginDate: DATETIME, not null, for the user's last login date.

Additionally, two indexes are created to optimize searches:

• Users_PID_index: Indexed on 'PID' column for fast retrieval based on user ID.
• Users_UserName_index: Indexed on 'UserName' column for quick searches by username.

C# SQLite Database Connection Function

The following C# function demonstrates connecting to the SQLite database:

public static SQLiteConnection ConnectToDatabase(string databasePath)
{
    SQLiteConnection connection = new SQLiteConnection("Data Source='" + databasePath + "';Version=3;");
    connection.Open();
    return connection;
}

This function takes the path to the SQLite database file as input. It creates a new SQLiteConnection object with the provided path and version information. The connection is then opened, and the open SQLiteConnection object is returned. This object can be used to execute queries and update the database.

Important: If the database file does not exist, this function will create a new empty database file at the specified path.