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