private async Task <IEnumerable <Country> > GetCountries(string sql, object param = null, string whereExpression = null, string orderByExpression = null)
{
sql = SqlHelpers.SqlBuilder(sql, whereExpression, orderByExpression);
var countries = await _connection.QueryAsync <Country>(
sql,
param : param,
transaction : _transaction);
return(countries);
}
public IEnumerable <TEntityType> GetAll()
{
string classtype = typeof(TEntityType).Name;
string cmd = $"select * from {classtype}";
var dt = SqlHelpers.GetDataTable(_connection, cmd);
for (int i = 0; i < dt.Rows.Count; i++)
{
yield return(_mapper.ConvertToEntity(dt.Rows[i]));
}
}
public IEnumerable <TEntityType> GetByKeyValue(string condition, List <SqlParameter> parameters)
{
string classtype = typeof(TEntityType).Name;
string cmd = $"select * from {classtype} where {condition}";
var dt = SqlHelpers.GetDataTable(_connection, cmd, parameters);
for (int i = 0; i < dt.Rows.Count; i++)
{
yield return(_mapper.ConvertToEntity(dt.Rows[i]));
}
}
public int UpdatedArticleVisited(string id)
{
int result = 0;
using (IDbConnection conn = SqlHelpers.CreateDbConnection(_connection))
{
result = conn.Execute("update article set visited=visited+1 where id=@id", new { id });
}
return(result);
}
private static void SetupQpContext(string connectionStringOrCustomerCode)
{
if (SqlHelpers.TryParseConnectionString(connectionStringOrCustomerCode, out var cnsBuilder))
{
QPContext.CurrentDbConnectionString = QPConfiguration.TuneConnectionString(cnsBuilder.ConnectionString, out var _);
}
else
{
QPContext.CurrentCustomerCode = connectionStringOrCustomerCode;
}
}
public int DeleteLink(string id)
{
int result = 0;
using (IDbConnection conn = SqlHelpers.CreateDbConnection(_connection))
{
result = conn.Execute("delete from link where Id = @Id", new { Id = id });
}
return(result);
}
public int LinksVisitedCount()
{
int total = 0;
using (IDbConnection conn = SqlHelpers.CreateDbConnection(_connection))
{
total = conn.QuerySingle <int>("select sum(visited) from link");
}
return(total);
}
public int UpdatedLinkVisited(string id)
{
int result = 0;
using (IDbConnection conn = SqlHelpers.CreateDbConnection(_connection))
{
result = conn.Execute("update link set visited = visited + 1 where Id = @Id", new { Id = id });
}
return(result);
}
public void GenerateJsonData_WhenGivenEmptyDictionary_ReturnsParenthesis()
{
// 1. Arrange
var input = new Dictionary <string, string>();
var expected = "{}";
// 2. Act
var actual = SqlHelpers.GenerateJsonData(input);
// 3. Assert
Assert.AreEqual(expected, actual);
}
public Account GetLogin(string userid, string password, string fields = "*")
{
Account account;
using (IDbConnection conn = SqlHelpers.CreateDbConnection(_connection))
{
string sql = string.Format($"SELECT * FROM account where userid=@userid and password=@password");
account = conn.QueryFirstOrDefault <Account>(sql, new { userid, password });
}
return(account);
}
public Account GetAccount(string id, string fields = "*")
{
var account = new Account();
using (IDbConnection conn = SqlHelpers.CreateDbConnection(_connection))
{
string sql = string.Format("SELECT {0} FROM account WHERE Id = @Id", fields);
account = conn.QueryFirstOrDefault <Account>(sql, new { Id = id });
}
return(account);
}
public string Select(string sql)
{
string a = ";lt";
string b = ";gt";
sql = sql.Replace(a, "<").Replace(b, ">");
DataTable dt = SqlHelpers.ExecuteDataTable(SqlHelpers.connectionString, CommandType.Text, sql);
IsoDateTimeConverter timeFormat = new IsoDateTimeConverter();
timeFormat.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
return(JsonConvert.SerializeObject(dt, Newtonsoft.Json.Formatting.None, timeFormat).ToLower());
}
public Article GetArticle(string id, string fields = "*")
{
var article = new Article();
using (IDbConnection conn = SqlHelpers.CreateDbConnection(_connection))
{
string sql = string.Format("SELECT {0} FROM article WHERE Id = @Id", fields);
article = conn.QueryFirstOrDefault <Article>(sql, new { Id = id });
}
return(article);
}
public IEnumerable <Link> GetAllLink()
{
IEnumerable <Link> list;
using (IDbConnection conn = SqlHelpers.CreateDbConnection(_connection))
{
string sql = string.Format($"SELECT icon,title,id,linkType FROM link ORDER BY linkType asc,visited desc");
list = conn.Query <Link>(sql);
}
return(list);
}
public IEnumerable <Link> ListLinkByCat(string catid)
{
IEnumerable <Link> list;
using (IDbConnection conn = SqlHelpers.CreateDbConnection(_connection))
{
string sql = string.Format($"SELECT title,id,visited,brief FROM link where catid = @catid ORDER BY visited desc");
list = conn.Query <Link>(sql, new { catid });
}
return(list);
}
public IEnumerable <LinkCat> LinkCatList()
{
IEnumerable <LinkCat> list;
using (IDbConnection conn = SqlHelpers.CreateDbConnection(_connection))
{
string sql = string.Format($"SELECT id,catname FROM linkcat ORDER BY id desc");
list = conn.Query <LinkCat>(sql);
}
return(list);
}
public LinkCat GetLinkCat(string id)
{
var linkCat = new LinkCat();
using (IDbConnection conn = SqlHelpers.CreateDbConnection(_connection))
{
string sql = string.Format("SELECT * FROM linkcat WHERE Id = @Id");
linkCat = conn.QueryFirstOrDefault <LinkCat>(sql, new { Id = id });
}
return(linkCat);
}
public IEnumerable <CusLink> ListCusLinkByPaging(int size, int skip, string fields = "*")
{
IEnumerable <CusLink> list;
using (IDbConnection conn = SqlHelpers.CreateDbConnection(_connection))
{
string sql = string.Format($"SELECT {fields} FROM cuslink ORDER BY id desc LIMIT @PageSize OFFSET @Offset");
list = conn.Query <CusLink>(sql, new { PageSize = size, Offset = skip });
}
return(list);
}
public IEnumerable <CusLink> GetTopCusLink(int size, string fields = "*")
{
IEnumerable <CusLink> list;
using (IDbConnection conn = SqlHelpers.CreateDbConnection(_connection))
{
string sql = string.Format("SELECT {0} FROM cuslink ORDER BY id desc LIMIT @size", fields);
list = conn.Query <CusLink>(sql, new { size });
}
return(list);
}
public IEnumerable <Article> ListArticleByPaging(int size, int skip, string fields = "*")
{
IEnumerable <Article> list;
using (IDbConnection conn = SqlHelpers.CreateDbConnection(_connection))
{
string sql = string.Format($"SELECT {fields} FROM article ORDER BY catalog asc,createTime desc LIMIT @PageSize OFFSET @Offset");
list = conn.Query <Article>(sql, new { PageSize = size, Offset = skip });
}
return(list);
}
public Link GetLink(string id, string fields = "*")
{
var link = new Link();
using (IDbConnection conn = SqlHelpers.CreateDbConnection(_connection))
{
string sql = string.Format("SELECT {0} FROM link WHERE Id = @Id", fields);
link = conn.QueryFirstOrDefault <Link>(sql, new { Id = id });
}
return(link);
}
public IdleSessionKiller(string connectionString, TimeSpan idleTimeout)
{
this.cancellationTokenSource = new CancellationTokenSource();
var cancellationToken = this.cancellationTokenSource.Token;
this.task = Task.Run(async() =>
{
var applicationName = new SqlConnectionStringBuilder(connectionString).ApplicationName;
while (!cancellationToken.IsCancellationRequested)
{
using var connection = SqlHelpers.CreateConnection(connectionString);
await connection.OpenAsync(cancellationToken);
var spidsToKill = new List <short>();
using (var findIdleSessionsCommand = connection.CreateCommand())
{
var expirationDate = DateTime.Now - idleTimeout;
findIdleSessionsCommand.CommandText = @"
SELECT session_id FROM sys.dm_exec_sessions
WHERE session_id != @@SPID
AND login_name != 'sa'
AND (last_request_start_time IS NULL OR last_request_start_time <= @expirationDate)
AND (last_request_end_time IS NULL OR last_request_end_time <= @expirationDate)";
findIdleSessionsCommand.Parameters.Add(findIdleSessionsCommand.CreateParameter("expirationDate", expirationDate));
try
{
using var reader = await findIdleSessionsCommand.ExecuteReaderAsync(cancellationToken);
while (await reader.ReadAsync(cancellationToken))
{
spidsToKill.Add(reader.GetInt16(0));
}
}
catch (DbException) when(cancellationToken.IsCancellationRequested)
{
cancellationToken.ThrowIfCancellationRequested();
}
}
foreach (var spid in spidsToKill)
{
using var killCommand = connection.CreateCommand();
killCommand.CommandText = "KILL " + spid;
try { await killCommand.ExecuteNonQueryAsync(); }
catch (Exception ex) { Console.WriteLine($"Failed to kill {spid}: {ex}"); }
}
await Task.Delay(TimeSpan.FromTicks(idleTimeout.Ticks / 2), cancellationToken);
}
});
}
//我的方法
public void ValidThru()
{
DataTable dt = new DataTable();
try
{
string sql = "select LIST,MACADDRESS from DhcpFilterStatus where STATUS='validthru_1'";
dt = SqlHelpers.ExecuteDataTable(CommandType.Text, sql);
for (int i = 0; i < dt.Rows.Count; i++)
{
using (PowerShell PowerShellInstance = PowerShell.Create())
{
string Script = "Remove-DhcpServerv4Filter -MacAddress " + "\"" + dt.Rows[i][1].ToString() + "\"";
PowerShellInstance.AddScript(Script);
Collection <PSObject> PSOutput = PowerShellInstance.Invoke();
Collection <ErrorRecord> errors = PowerShellInstance.Streams.Error.ReadAll();
if (errors.Count > 0)
{
foreach (ErrorRecord er in errors)
{
string sql_log = $"insert into DhcpFilterLog(Status, Action, Object, ReasonOfFailure, Time) values('失败', '过期MAC删除_2', '{dt.Rows[i][1].ToString()}', '{er.Exception.Message}', getdate())";
SqlHelpers.ExecuteNonQuery(CommandType.Text, sql_log);
}
return;
}
else if (errors.Count == 0)
{
try
{
string Dsql = "delete from DhcpFilterStatus where MACADDRESS=" + "'" + dt.Rows[i][1].ToString() + "'";
SqlHelpers.ExecuteNonQuery(CommandType.Text, Dsql);
string sql_log = $"insert into DhcpFilterLog(Status, Action, Object, Time) values('成功', '过期MAC删除_2', '{dt.Rows[i][1].ToString()}', getdate())";
SqlHelpers.ExecuteNonQuery(CommandType.Text, sql_log);
//writestr($"过期MAC:{dt.Rows[i][1].ToString()}删除成功");
}
catch (Exception ex)
{
string sql_log = $"insert into DhcpFilterLog(Status, Action, Object, ReasonOfFailure, Time) values('失败', '过期MAC删除_2', '{dt.Rows[i][1].ToString()}', '{ex.Message}', getdate())";
SqlHelpers.ExecuteNonQuery(CommandType.Text, sql_log);
//writestr($"过期MAC:{dt.Rows[i][1].ToString()}从Dhcp服务器删除成功，但是从数据库删除失败,原因:" + ex.Message);
return;
}
}
}
}
}
catch (Exception err)
{
string sql_log = $"insert into DhcpFilterLog(Status, Action, Object, ReasonOfFailure, Time) values('失败', '服务出错_2', 'ValidThru', '{err.Message}', getdate())";
SqlHelpers.ExecuteNonQuery(CommandType.Text, sql_log);
}
}
public void TestConnectionDoesNotLeak()
{
var applicationName = nameof(TestConnectionDoesNotLeak) + Guid.NewGuid();
var connectionString = new SqlConnectionStringBuilder(ConnectionStringProvider.ConnectionString)
{
ApplicationName = applicationName,
}
.ConnectionString;
using (ConnectionStringProvider.UseConnectionString(connectionString))
using (var engine = this.CreateEngine())
{
var @lock = engine.CreateLock(nameof(TestConnectionDoesNotLeak));
for (var i = 0; i < 30; ++i)
{
using (@lock.Acquire())
{
CountActiveSessions().ShouldEqual(1, this.GetType().Name);
}
// still alive due to pooling
CountActiveSessions().ShouldEqual(1, this.GetType().Name);
}
}
using (var connection = SqlHelpers.CreateConnection(connectionString))
{
SqlTestHelper.ClearPool(connection);
// checking immediately seems flaky; likely clear pool finishing
// doesn't guarantee that SQL will immediately reflect the clear
var maxWaitForPoolsToClear = TimeSpan.FromSeconds(5);
var stopwatch = Stopwatch.StartNew();
do
{
var activeCount = CountActiveSessions();
if (activeCount == 0)
{
return;
}
Thread.Sleep(25);
}while (stopwatch.Elapsed < maxWaitForPoolsToClear);
}
int CountActiveSessions()
{
using var connection = SqlHelpers.CreateConnection(ConnectionStringProvider.ConnectionString);
connection.Open();
using var command = connection.CreateCommand();
command.CommandText = $@"SELECT COUNT(*) FROM sys.dm_exec_sessions WHERE program_name = '{applicationName}'";
return((int)command.ExecuteScalar());
}
}
private void CheckParameters()
{
if (SqlHelpers.CheckParameters(SqlContext.MetadataProvider, SqlContext.SyntaxProvider, SqlQuery.QueryParameters))
{
HideParametersErrorPanel();
}
else
{
var acceptableFormats =
SqlHelpers.GetAcceptableParametersFormats(SqlContext.MetadataProvider, SqlContext.SyntaxProvider);
ShowParametersErrorPanel(acceptableFormats);
}
}
/// <summary>
/// Clear the AuditLog table.
/// Implemented using a proc for raw speed.
/// </summary>
public static void ClearAuditLogTable()
{
string vendorImportProc = "pClearAuditLogTable";
using (SqlConnection sqlConnection = SqlHelpers.CreateAndOpenSqlConnection())
{
using (var cmd = new SqlCommand(vendorImportProc, sqlConnection))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.ExecuteNonQuery();
}
}
}
protected void PageLocations_SelectedIndexChanged(object sender, RadComboBoxSelectedIndexChangedEventArgs e)
{
var locationDropdown = (RadComboBox)sender;
if (locationDropdown.SelectedIndex > -1)
{
PageDescription.Content = SqlHelpers.SelectScalar(SqlStatements.SQL_GET_PAGE_CONTENTS.FormatWith(locationDropdown.SelectedValue)).ToString();
}
else
{
PageDescription.Content = string.Empty;
}
}
public void GenerateJsonData_WhenGivenDictionaryWithSingleEntry_ReturnsCorrectResult()
{
// 1. Arrange
var input = new Dictionary <string, string>();
input.Add("name", "wekoslav");
var expected = "{\"name\": \"wekoslav\"}";
// 2. Act
var actual = SqlHelpers.GenerateJsonData(input);
// 3. Assert
Assert.AreEqual(expected, actual);
}
private void CheckParameters()
{
if (SqlHelpers.CheckParameters(queryBuilder.MetadataProvider, queryBuilder.SyntaxProvider, queryBuilder.Parameters))
{
HideParametersErrorPanel();
}
else
{
var acceptableFormats =
SqlHelpers.GetAcceptableParametersFormats(queryBuilder.MetadataProvider, queryBuilder.SyntaxProvider);
ShowParametersErrorPanel(acceptableFormats);
}
}
public frmMain()
{
InitializeComponent();
frmimportFile.Owner = this;
frmadd.Owner = this;
frmalter.Owner = this;
dtpQueryFrom.Value = DateTime.Today;
dtpQueryThru.Value = DateTime.Today.AddYears(10);
string sql = "select LIST,MACADDRESS,DESCRIPTION,VALIDFROM,VALIDTHRU from DhcpFilterStatus where STATUS !='deleting'";
dt = SqlHelpers.ExecuteDataTable(CommandType.Text, sql);
dgvData.DataSource = dt;
}
