/// <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="dic">key：表名，val：参数化sql语句</param>
/// <param name="parameters">key：表名，val：该表对应的sql（格式化）参数</param>
/// <param name="isCount">[可选]是否统计数据行</param>
/// <returns>Dictionary<string, DataTable> key：表名，val：表（数据）</returns>
public static Dictionary <string, ArrayList> GetDictionaryTable(Dictionary <string, string> dic, Dictionary <string, SqlParameter[]> parameters, bool isCount = false)
{
Dictionary <string, ArrayList> dicTab = new Dictionary <string, ArrayList>();
try
{
foreach (var item in dic)
{
string tbName = item.Key; //表名
string sql = item.Value; //sql语句
var dt = GetDataTable(sql, parameters[tbName]);
dicTab.Add(tbName, ConvertDataTableToArrayList(dt, isCount));
}
}
catch (Exception e)
{
Log2Txt.logError(Log2Txt.GetMethodInfo() + e.Message);
}
return(dicTab);
}
public string getProxy()
{
string itemProxy = null;
try
{
if (!Proxy.Contains(null))
{
Proxy.Add(null);
new Thread(new ThreadStart(start)).Start();
}
Random random = new Random();
int index = random.Next(0, Proxy.Count);
itemProxy = Proxy[index];
}
catch (Exception e)
{
Log2Txt.logError(Log2Txt.GetMethodInfo() + e.Message);
}
return(itemProxy);
}
/// <summary>
/// 数据读取器【DataReader】转换DataTable
/// </summary>
/// <param name="dataReader">数据读取器</param>
/// <returns>DataTable</returns>
public static DataTable ConvertDataReaderToDataTable(SqlDataReader dataReader)
{
DataTable dt = null;
try
{
using (dt = new DataTable())
{
//动态添加表的数据列
for (int i = 0; i < dataReader.FieldCount; i++)
{
using (DataColumn myDataColumn = new DataColumn())
{
myDataColumn.DataType = dataReader.GetFieldType(i);
myDataColumn.ColumnName = dataReader.GetName(i);
dt.Columns.Add(myDataColumn);
}
}
//添加表的数据
while (dataReader.Read())
{
DataRow myDataRow = dt.NewRow();
for (int i = 0; i < dataReader.FieldCount; i++)
{
myDataRow[i] = dataReader[i].ToString();
}
dt.Rows.Add(myDataRow);
}
//关闭数据读取器DataReader
CloseDataReader(dataReader);
}
}
catch (Exception e)
{
Log2Txt.logError(Log2Txt.GetMethodInfo() + e.Message);
}
return(dt);
}
/// <summary>
/// 多语句（sql）事务操作
/// 带参执行（sql参数化）
/// </summary>
/// <param name="dic">Dictionary<string,SqlParameter[]></param>
/// <returns>int</returns>
public static int ExecSqlTranByParams(Dictionary <string, SqlParameter[]> dic)
{
int count = 0;
SqlTransaction transaction = null; // 创建事务对象
try
{
using (SqlConnection conn = new SqlConnection(_ConnString))
{
OpenConnection(conn);
using (transaction = conn.BeginTransaction()) //开始事务操作
{
using (SqlCommand cmd = new SqlCommand())
{
foreach (var item in dic)
{
cmd.CommandText = item.Key.Trim(); //sql语句
SqlParameter[] sqlParams = item.Value; //sql参数化
//检查可选参数组是否有数据
if (sqlParams != null && sqlParams.Length > 0)
{
cmd.Parameters.AddRange(sqlParams);
}
count += cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
transaction.Commit(); //事务提交
}
}
}
}
catch (Exception e)
{
transaction.Rollback(); //事务回滚
Log2Txt.logError(Log2Txt.GetMethodInfo() + e.Message);
}
return(count);
}
/// <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>
/// 获取字符串首字母大写
/// </summary>
/// <param name="s">待处理字符串</param>
/// <returns>string</returns>
public static string GetStringFirstUpper(string s)
{
string firstUpperStr = string.Empty;
try
{
if (!string.IsNullOrEmpty(s))
{
if (s.Length > 1)
{
firstUpperStr = char.ToUpper(s[0]) + s.Substring(1).ToLower().Trim();
}
else
{
firstUpperStr = char.ToUpper(s[0]).ToString().Trim();
}
}
}
catch (Exception e)
{
Log2Txt.logError(Log2Txt.GetMethodInfo() + e.Message);
}
return(firstUpperStr);
}
/// <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);
}
}