public VpnModel GetEffctiveVPN()
{
DataTable dt = dbHelper.ExecuteDataTable(
"SELECT Id, Address, Username, Password, Country,Name, VpnType, L2tpSec,updateTime, ConnQty, Status "
+"FROM vpns where status = 1 order by ConnQty asc, updateTime asc limit 0, 1", null);
if (dt.Rows.Count > 0)
{
DataRow row = dt.Rows[0];
VpnModel model = new VpnModel();
model.Id = Convert.ToInt32(row["id"]);
model.Address = (string)row["Address"];
model.Username = (string)row["Username"];
model.Password = (string)row["Password"];
model.Country = (string)row["Country"];
model.Name = (string)row["Name"];
model.VpnType = (string)row["VpnType"];
model.L2tpSec = (string)row["L2tpSec"];
model.ConnQty = Convert.ToInt32(row["ConnQty"]);
model.Status = Convert.ToInt32(row["Status"]);
model.UpdateTime = Convert.ToDateTime(row["updateTime"]);
return model;
}
return null;
}
void worker_DoWork(object sender, DoWorkEventArgs e)
{
Workbook workBook = new Workbook();
try
{
workBook.Open(SelectExcelFile);
}
catch (Exception ex)
{
this.ErrorMsg.Text = "打开选定的Excel文件出错: " + ex.Message;
return;
}
List<VpnModel> vpnList = new List<VpnModel>();
foreach (Worksheet sheet in workBook.Worksheets)
{
int AddressCol = -1;
int UsernameCol = -1;
int PasswordCol = -1;
int CountryCol = -1;
int AgentNameCol = -1;
int VpnTypeCol = -1;
int L2tpSecCol = -1;
string sheetName = sheet.Name;
Cells cells = sheet.Cells;
for (int j = 0; j < cells.MaxDataColumn + 1; j++)
{
string value = sheet.Cells[0, j].StringValue.Trim();
if ("address".Equals(value.ToLower()))
{
AddressCol = j;
}
if ("username".Equals(value.ToLower()))
{
UsernameCol = j;
}
if ("password".Equals(value.ToLower()))
{
PasswordCol = j;
}
if ("country".Equals(value.ToLower()))
{
CountryCol = j;
}
if ("agentname".Equals(value.ToLower()))
{
AgentNameCol = j;
}
if ("vpntype".Equals(value.ToLower()))
{
VpnTypeCol = j;
}
if ("l2tpsec".Equals(value.ToLower()))
{
L2tpSecCol = j;
}
//System.Diagnostics.Trace.WriteLine(value);
}
for (int i = 1; i < cells.MaxDataRow + 1; i++)
{
if (AddressCol == -1)
{
continue;
}
VpnModel model = new VpnModel();
model.Address = sheet.Cells[i, AddressCol].StringValue.Trim();
if (UsernameCol != -1)
{
model.Username = sheet.Cells[i, UsernameCol].StringValue.Trim();
}
if (PasswordCol != -1)
{
model.Password = sheet.Cells[i, PasswordCol].StringValue.Trim();
}
if (CountryCol != -1)
{
model.Country = sheet.Cells[i, CountryCol].StringValue.Trim();
}
if (AgentNameCol != -1)
{
model.Name = sheet.Cells[i, AgentNameCol].StringValue.Trim();
}
if (VpnTypeCol != -1)
{
model.VpnType = sheet.Cells[i, VpnTypeCol].StringValue.Trim();
}
if (L2tpSecCol != -1)
{
model.L2tpSec = sheet.Cells[i, L2tpSecCol].StringValue.Trim();
}
vpnList.Add(model);
}
}
if (vpnList.Count == 0)
{
this.ErrorMsg.Text = "此Excel中未包含任何邮件数据。请重新选择。";
return;
}
vpnDao.ImportVpns(vpnList);
if (dataGridView.InvokeRequired)
{
UpdateDataGridView uActive = LoadDataview;
this.BeginInvoke(uActive, null);
}
else
{
LoadDataview();
}
}
private void InsertBtn_Click(object sender, EventArgs e)
{
VpnModel model = new VpnModel();
model.Address = AddressBox.Text.Trim().Replace(" ", "");
try
{
IPAddress.Parse(model.Address);
}catch{
ErrorMsg.Text = "输入的VPN IP地址不合法.";
return;
}
model.Username = UsernameBox.Text.Trim();
if (string.IsNullOrEmpty(model.Username))
{
ErrorMsg.Text = "VPN 用户名不能为空.";
return;
}
model.Password = PasswordBox.Text.Trim();
if (string.IsNullOrEmpty(model.Password))
{
ErrorMsg.Text = "VPN 用户名密码不能为空.";
return;
}
model.Country = countryTxt.Text.Trim();
if (string.IsNullOrEmpty(model.Country))
{
ErrorMsg.Text = "国家不能为空.";
return;
}
if (PptpBtn.Checked)
{
model.VpnType = Constants.PPTP;
model.L2tpSec = string.Empty;
}
else
{
model.VpnType = Constants.L2TP;
model.L2tpSec = L2tpKeyTxtBox.Text.Trim();
}
model.Name = AgentTxt.Text.Trim();
bool existAddress = vpnDao.ExistAddress(model.Address, model.VpnType);
if (existAddress)
{
ErrorMsg.Text = "VPN 地址已经存在列表中.";
return;
}
vpnDao.Insert(model);
AddressBox.Text = "";
LoadDataview();
}
public VpnModel GetVpnModelByIpAddress(string ipAddress)
{
string sql = "SELECT Id, Address, Username, Password, Country,Name, VpnType, L2tpSec,updateTime, ConnQty, Status "
+ "FROM vpns where status = 1 Address = @Address";
DataTable dt = dbHelper.ExecuteDataTable(sql,
new SQLiteParameter[]{
new SQLiteParameter("@Address",ipAddress)
}
);
if (dt.Rows.Count > 0)
{
DataRow row = dt.Rows[0];
VpnModel model = new VpnModel();
model.Id = Convert.ToInt32(row["id"]);
model.Address = (string)row["Address"];
model.Username = (string)row["Username"];
model.Password = (string)row["Password"];
model.Country = (string)row["Country"];
model.Name = (string)row["Name"];
model.VpnType = (string)row["VpnType"];
model.L2tpSec = (string)row["L2tpSec"];
model.ConnQty = Convert.ToInt32(row["ConnQty"]);
model.Status = Convert.ToInt32(row["Status"]);
model.UpdateTime = Convert.ToDateTime(row["updateTime"]);
return model;
}
return GetEffctiveVPN();
}
public void UpdateUserPassword(VpnModel model)
{
string sql = @"UPDATE vpns SET Username =@Username, Password =@Password, updateTime = @updateTime where id = @id";
SQLiteParameter[] parameter = new SQLiteParameter[]
{
new SQLiteParameter("@Username", model.Username),
new SQLiteParameter("@Password",model.Password),
new SQLiteParameter("@updateTime",DateTime.Now),
new SQLiteParameter("@id",model.Id)
};
dbHelper.ExecuteNonQuery(sql, parameter);
}
public void Insert(VpnModel model)
{
string sql = @"INSERT INTO vpns(Address, Username, Password, Country, Name, VpnType, L2tpSec, createTime, updateTime)"
+ "values(@Address,@Username,@Password, @Country, @Name, @VpnType,@L2tpSec, @createTime, @updateTime)";
SQLiteParameter[] parameter = new SQLiteParameter[]
{
new SQLiteParameter("@Address",model.Address),
new SQLiteParameter("@Username", model.Username),
new SQLiteParameter("@Password",model.Password),
new SQLiteParameter("@Country",model.Country),
new SQLiteParameter("@Name",model.Name),
new SQLiteParameter("@VpnType",model.VpnType),
new SQLiteParameter("@L2tpSec",model.L2tpSec),
new SQLiteParameter("@createTime",DateTime.Now),
new SQLiteParameter("@updateTime",DateTime.Now)
};
dbHelper.ExecuteNonQuery(sql, parameter);
}
public List<VpnModel> GetVpnModelList()
{
DataTable dt = dbHelper.ExecuteDataTable(
"SELECT Id, Address, Username, Password, Country,Name, VpnType, L2tpSec,updateTime,Status FROM vpns order by updateTime desc", null);
List<VpnModel> list = new List<VpnModel>();
foreach (DataRow row in dt.Rows)
{
VpnModel model = new VpnModel();
model.Id = Convert.ToInt32(row["id"]);
model.Address = (string)row["Address"];
model.Username = (string)row["Username"];
model.Password = (string)row["Password"];
model.Country = (string)row["Country"];
model.Name = (string)row["Name"];
model.VpnType = (string)row["VpnType"];
model.L2tpSec = (string)row["L2tpSec"];
model.Status = Convert.ToInt32(row["Status"]);
model.UpdateTime = Convert.ToDateTime(row["updateTime"]);
list.Add(model);
}
return list;
}
public VPN(string connName, VpnModel model)
{
this.connName = connName;
this.model = model;
}
public void Dispose()
{
connName = null;
model = null;
dialer = null;
handle = null;
}
