/// <summary>
/// 批量插入数据，并返回受影响的行数
/// </summary>
/// <param name="dt">DataTable</param>
/// <param name="dtName">表名称</param>
/// <returns>int：受影响的行数</returns>
public static int InsertByBulk(DataTable dt, string dtName)
{
int rowsCount = 0;
try
{
Log2Txt.logInfo(Log2Txt.GetMethodInfo() + "dtName: " + dtName);
using (SqlConnection conn = new SqlConnection(_ConnString))
{
OpenConnection(conn);
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn))
{
if (dt != null && dt.Rows.Count != 0)
{
bulkCopy.DestinationTableName = dtName; //数据表名称
bulkCopy.BatchSize = dt.Rows.Count;
bulkCopy.WriteToServer(dt);
rowsCount = bulkCopy.BatchSize;
}
}
CloseConnection(conn);
}
}
catch (Exception e)
{
Log2Txt.logError(Log2Txt.GetMethodInfo() + e.Message);
}
return(rowsCount);
}
/// <summary>
/// 存储过程(增、删、改)
/// </summary>
/// <param name="cmdText">sql语句或存储过程</param>
/// <param name="sqlParams">[可选]sql参数化</param>
/// <returns>int</returns>
public static int GetCountByProcedure(string cmdText, params SqlParameter[] sqlParams)
{
int count = -1;
try
{
Log2Txt.logInfo(Log2Txt.GetMethodInfo() + "sql: " + cmdText);
using (SqlConnection conn = new SqlConnection(_ConnString))
{
OpenConnection(conn);
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = cmdText; //sql语句或存储过程
cmd.CommandType = CommandType.StoredProcedure; //指定cmd命令类型为存储过程
//检查参数组是否有数据
if (sqlParams != null && sqlParams.Length > 0)
{
cmd.Parameters.AddRange(sqlParams);
}
count = cmd.ExecuteNonQuery(); //执行非查询命令,并返回受影响的行数
cmd.Parameters.Clear();
cmd.Dispose();
}
}
}
catch (Exception e)
{
Log2Txt.logError(Log2Txt.GetMethodInfo() + e.Message);
}
return(count);
}
/// <summary>
/// 执行sql语句，返回第一行第一列的值
/// </summary>
/// <param name="cmdText">sql语句</param>
/// <param name="sqlParams">sql格式化参数</param>
/// <returns>object</returns>
public static object ExecuteScalar(string cmdText, CommandType commandType, params SqlParameter[] sqlParams)
{
object id = null; //接收数据表自增长Id
try
{
Log2Txt.logInfo(Log2Txt.GetMethodInfo() + "sql: " + cmdText);
using (SqlConnection conn = new SqlConnection(_ConnString)) // 建立数据库连接对象
{
OpenConnection(conn);
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = cmdText; //sql语句或存储过程
cmd.CommandType = commandType;
if (sqlParams != null && sqlParams.Length > 0) //检查参数组是否有数据
{
foreach (SqlParameter sqlParam in sqlParams)
{
//参数格式化，防止sql注入
cmd.Parameters.Add(sqlParam);
}
}
id = cmd.ExecuteScalar(); //执行命令，返回第一行第一列的值 obj
cmd.Parameters.Clear();
}
CloseConnection(conn);
}
}
catch (Exception e)
{
Log2Txt.logError(Log2Txt.GetMethodInfo() + e.Message);
}
return(id);
}
/// <summary>
/// 数据读取器，逐行读取数据库表的所有字段的值
/// 取值reader["字段名"]
/// </summary>
/// <param name="cmdText">sql语句</param>
/// <param name="sqlParams">sql参数化</param>
/// <returns>SqlDataReader</returns>
public static DataTable GetDataReader(string cmdText, params SqlParameter[] sqlParams)
{
DataTable dt = null;
try
{
Log2Txt.logInfo(Log2Txt.GetMethodInfo() + "sql: " + cmdText);
using (SqlConnection conn = new SqlConnection(_ConnString))
{
OpenConnection(conn);
using (SqlCommand cmd = new SqlCommand(cmdText, conn))
{
if (sqlParams != null && sqlParams.Length > 0)
{
cmd.Parameters.AddRange(sqlParams);
}
//保证当SqlDataReader对象被关闭时，其依赖的连接也会被自动关闭CommandBehavior.
using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
cmd.Parameters.Clear();
dt = ConvertDataReaderToDataTable(reader);
CloseDataReader(reader);
cmd.Dispose();
}
}
}
}
catch (Exception e)
{
Log2Txt.logError(Log2Txt.GetMethodInfo() + e.Message);
}
return(dt);
}
public void removeProxy(string proxy)
{
if (proxy == null)
{
return;
}
if (!isVaildProxy(proxy))
{
Proxy.Remove(proxy);
Log2Txt.logInfo("移除代理：" + proxy);
}
}
/// <summary>
/// 多语句（sql）事务操作
/// 无参执行
/// </summary>
/// <param name="sqlStringList">多条SQL语句</param>
/// <returns>bool</returns>
public static bool ExecSqlTran(ArrayList sqlStringList)
{
SqlTransaction transaction = null;
try
{
Log2Txt.logInfo(Log2Txt.GetMethodInfo() + "sql: " + sqlStringList.ToString());
using (SqlConnection conn = new SqlConnection(_ConnString))
{
OpenConnection(conn);
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = conn;
using (transaction = conn.BeginTransaction()) //开始事务操作
{
cmd.Transaction = transaction;
for (int n = 0; n < sqlStringList.Count; n++)
{
string strsql = sqlStringList[n].ToString().Trim();
if (strsql.Length > 1)
{
cmd.CommandText = strsql;
cmd.ExecuteNonQuery();
}
}
transaction.Commit(); //事务提交
}
cmd.Parameters.Clear();
return(true); //事务执行成功返回true
}
}
}
catch (Exception e)
{
transaction.Rollback(); //事务回滚
Log2Txt.logError(Log2Txt.GetMethodInfo() + e.Message);
}
return(false);
}
/// <summary>
/// 根据数据源（数据库表）动态构建DataTable类型对象，并填充slq参数化数据
/// </summary>
/// <param name="tabName">表名称</param>
/// <param name="listParameters">sql参数化</param>
/// <returns>DataTable</returns>
public static DataTable CreateDataTable(string tabName, List <SqlParameter[]> listParameters)
{
string sqlQuery = $"select * from {tabName} where 1!=1";
DataTable myDt = new DataTable(tabName.Trim());
try
{
Log2Txt.logInfo(Log2Txt.GetMethodInfo() + "sql:" + sqlQuery);
DataTable dt = GetDataTable(sqlQuery);
//完成数据库表动态创建DataTable的构架，但是里面是没有任何数据的
foreach (DataColumn dc in dt.Columns)
{
string columnName = dc.ColumnName.ToString().Trim(); //获取表列名称
string columnType = dc.DataType.ToString().Trim(); //获取表类型名称
myDt.Columns.Add(new DataColumn(columnName, Type.GetType(columnType)));
}
//在构建好的myDt(DataTable类型)中填充数据（批量数据）
foreach (SqlParameter[] parameters in listParameters.CheckNull())
{
foreach (SqlParameter parameter in parameters.CheckNull())
{
DataRow dr = myDt.NewRow(); //在构建好的Datatable中动态添加行
//参数格式化，防止sql注入
var name = parameter.ParameterName.Trim();
var val = parameter.Value;
foreach (DataColumn dc in myDt.Columns)
{
string columnName = dc.ColumnName.ToString().Trim(); //获取表列名称
dr[columnName] = val;
}
myDt.Rows.Add(dr);
}
}
}
catch (Exception e)
{
Log2Txt.logError(Log2Txt.GetMethodInfo() + e.Message);
}
return(myDt);
}
/// <summary>
/// 存储过程(增、删、改)
/// </summary>
/// <param name="cmdText">sql语句或存储过程</param>
/// <param name="sqlParams">[可选]sql参数化</param>
/// <returns>DataTable</returns>
public static DataTable GetDataTableByProcedure(string cmdText, params SqlParameter[] sqlParams)
{
DataTable dt = null;
try
{
Log2Txt.logInfo(Log2Txt.GetMethodInfo() + "sql: " + cmdText);
using (SqlConnection conn = new SqlConnection(_ConnString))
{
OpenConnection(conn);
using (SqlCommand cmd = new SqlCommand(cmdText, conn))
{
cmd.CommandTimeout = 0;
cmd.CommandType = CommandType.StoredProcedure; //指定cmd类型为存储过程
//检查可选参数组是否有数据
if (sqlParams != null && sqlParams.Length > 0)
{
cmd.Parameters.AddRange(sqlParams);
}
//数据读取器SqlDataReader
//保证当SqlDataReader对象被关闭时，其依赖的连接也会被自动关闭CommandBehavior.
using (SqlDataReader dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
cmd.Parameters.Clear();
dt = ConvertDataReaderToDataTable(dataReader);
CloseDataReader(dataReader);
cmd.Dispose();
}
}
}
}
catch (Exception e)
{
Log2Txt.logError(Log2Txt.GetMethodInfo() + e.Message);
}
return(dt);
}
/// <summary>
/// 返回数据库表DataTable
/// </summary>
/// <param name="cmdText">sql语句</param>
/// <param name="sqlParams">sql参数化</param>
/// <returns>DataTable</returns>
public static DataTable GetDataTable(string cmdText, params SqlParameter[] sqlParams)
{
DataTable dt = null;
try
{
Log2Txt.logInfo(Log2Txt.GetMethodInfo() + "sql: " + cmdText);
using (dt = new DataTable())
{
using (SqlConnection conn = new SqlConnection(_ConnString))
{
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = cmdText;
cmd.CommandTimeout = 0;
//检查参数组是否有数据
if (sqlParams != null && sqlParams.Length > 0)
{
cmd.Parameters.AddRange(sqlParams);
}
//适配器自动打开数据库连接
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
da.Fill(dt);
}
cmd.Parameters.Clear();
}
}
}
}
catch (Exception e)
{
Log2Txt.logError(Log2Txt.GetMethodInfo() + e.Message);
}
return(dt);
}
private void crawlProxy()
{
HtmlDocument html = new HtmlDocument();
List <string> ipList = new List <string>();
List <string> portList = new List <string>();
int maxPageNum = 0;
string page = string.Empty;
int pageIndex = 1;
string itemUrl = string.Empty;
string itemProxy = string.Empty;
try
{
for (int i = 0; i < proxySites.Length; i++)
{
pageIndex = 1;
itemUrl = proxySites[i];
itemProxy = getProxy();
page = getPage(itemUrl);
if (string.IsNullOrEmpty(page))
{
continue;
}
html.LoadHtml(page);
string item = getValueByXPathAndAttribute(html, maxPagNumXPath);
maxPageNum = Convert.ToInt32(item);
while (maxPageNum >= pageIndex)
{
do
{
itemProxy = getProxy();
Log2Txt.logInfo(Log2Txt.GetMethodInfo() + "page(" + pageIndex + "/" + maxPageNum + "):" + "currentUrl:" + itemUrl + " currentProxy:" + itemProxy + "\r\n");
page = getPage(itemUrl, itemProxy);
html.LoadHtml(page);
ipList = getValuesByXPathAndAttribute(html, ipXPath);
portList = getValuesByXPathAndAttribute(html, portXPath);
for (int j = 0; j < ipList.Count; j++)
{
string str = ipList[j].ToString() + ":" + portList[j].ToString();
if (!isVaildProxy(str))
{
continue;
}
if (Proxy.Contains(str))
{
continue;
}
Proxy.Add(str);
Log2Txt.logInfo("Find a proxy:" + str);
}
if (ipList.Count > 0)
{
pageIndex++;
itemUrl = proxySites[i].Replace("1", pageIndex.ToString());
}
else
{
removeProxy(itemProxy);
}
Thread.Sleep(1000);
} while (ipList.Count == 0);
}
if (Proxy.Count > 1)
{
setProxysToFile();
}
}
}
catch (Exception e)
{
Log2Txt.logError(Log2Txt.GetMethodInfo() + e.Message);
}
}