/// <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);
}
/// <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>
/// 执行SQL语句，返回影响的记录数
/// Insert插入,Delete删除,Update更新
/// </summary>
/// <param name="cmdText">sql语句或存储过程</param>
/// <param name="sqlParams">[可选]sql参数化</param>
/// <returns>int：受影响的行数</returns>
public static int ExecNonQuery(string cmdText, params SqlParameter[] sqlParams)
{
int rowsCount = 0;
try
{
using (SqlConnection conn = new SqlConnection(_ConnString))
{
OpenConnection(conn);
using (SqlCommand cmd = conn.CreateCommand())
{
//cmd.CommandType = CommandType.Text; //指定cmd命令类型为文本类型(默认，可不写);
cmd.CommandText = cmdText; //sql语句或存储过程
//检查参数组是否有数据
if (sqlParams != null && sqlParams.Length > 0)
{
cmd.Parameters.AddRange(sqlParams);
}
rowsCount = cmd.ExecuteNonQuery(); //执行非查询命令，接收受影响行数，大于0的话表示添加成功
cmd.Parameters.Clear();
}
CloseConnection(conn);
}
}
catch (Exception e)
{
Log2Txt.logError(Log2Txt.GetMethodInfo() + e.Message);
throw e;
}
return(rowsCount);
}
/// <summary>
/// 批量查询返回数据集DataSet
/// </summary>
/// <param name="sqlParamsDic">Dictionary类型的【sql语句】和【sql参数化】</param>
/// <param name="tabNames">[可选]DataSet中对应的DataTable名称</param>
/// <returns>DataSet</returns>
public static DataSet GetDataSet(Dictionary <string, SqlParameter[]> sqlParamsDic, params string[] tabNames)
{
DataSet ds = null;
try
{
using (ds = new DataSet())
{
string cmdText = string.Empty; //sql语句
SqlParameter[] sqlParams = null; //sql参数化
if (tabNames != null && tabNames.Length > 0 && sqlParamsDic.Count == tabNames.Length)
{
int i = 0; //表名称数组
foreach (var cmdTexts in sqlParamsDic.CheckNull())
{
string tabName = tabNames[i] ?? i.ToString();
cmdText = cmdTexts.Key; //sql语句
sqlParams = cmdTexts.Value;
using (SqlConnection conn = new SqlConnection(_ConnString))
{
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = cmdText;
//检查参数组是否有数据
if (sqlParams != null && sqlParams.Length > 0)
{
cmd.Parameters.AddRange(sqlParams);
}
//适配器自动打开数据库连接
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
da.Fill(ds, tabName); // 将tabName查询结果集合填入DataSet中，并且将DataTable命名为tabName
}
cmd.Parameters.Clear();
}
}
if (i < tabNames.Length)
{
i++;
}
}
}
else
{
foreach (var cmdTexts in sqlParamsDic.CheckNull())
{
cmdText = cmdTexts.Key; //sql语句
sqlParams = cmdTexts.Value;
var dt = GetDataTable(cmdText, sqlParams);
ds.Tables.Add(dt.Copy());
}
}
}
}
catch (Exception e)
{
Log2Txt.logError(Log2Txt.GetMethodInfo() + e.Message);
}
return(ds);
}
/// <summary>
/// 获取数据表DataTable中的值，处理转换为ArrayList
/// </summary>
/// <param name="ds">DataSet</param>
/// <param name="isCount">[可选]是否统计数据行</param>
/// <returns>ArrayList</returns>
public static ArrayList ConvertDataTableToArrayList(DataTable dt, bool isCount = false)
{
ArrayList list = new ArrayList();
try
{
for (int i = 0; i < dt.Rows.Count; i++)
{
Dictionary <string, string> dic = new Dictionary <string, string>();
for (int j = 0; j < dt.Columns.Count; j++)
{
string key = GetStringFirstUpper(dt.Columns[j].ColumnName.ToString());
string val = dt.Rows[i][j].ToString().Trim();
dic[key] = val;
}
list.Add(dic);
}
if (isCount)
{
//统计查询数据行数
Dictionary <string, int> dicCount = new Dictionary <string, int> {
{ "RowsCount", dt.Rows.Count }
};
list.Add(dicCount);
}
}
catch (Exception e)
{
Log2Txt.logError(Log2Txt.GetMethodInfo() + e.Message);
}
return(list);
}
/// <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>
/// 存储过程(增、删、改)
/// </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>
/// 获取数据集DataSet中的值，处理转换为List<ArrayList>
/// </summary>
/// <param name="ds">DataSet</param>
/// <param name="isCount">[可选]是否统计数据行</param>
/// <returns>List<ArrayList></returns>
public static List <ArrayList> ConvertDataSetToList(DataSet ds, bool isCount = false)
{
List <ArrayList> list = new List <ArrayList>();
try
{
//获取ds中表的数量
for (int i = 0; i < ds.Tables.Count; i++)
{
ArrayList al = new ArrayList {
ConvertDataTableToArrayList(ds.Tables[i], isCount)
};
if (isCount)
{
Dictionary <string, int> dicCount = new Dictionary <string, int>();
dicCount.Add("TablesCount", ds.Tables.Count); //统计查询数据表行数
al.Add(dicCount);
}
list.Add(al);
}
}
catch (Exception e)
{
Log2Txt.logError(Log2Txt.GetMethodInfo() + e.Message);
}
return(list);
}
public void setProxyToFile(string proxy)
{
try
{
File.AppendAllText(proxyFilePath, proxy + "\r\n");
}
catch (IOException e)
{
Log2Txt.logError(Log2Txt.GetMethodInfo() + e.Message);
}
}
public void setProxysToFile()
{
try
{
File.WriteAllLines(proxyFilePath, Proxy);
}
catch (IOException e)
{
Log2Txt.logError(Log2Txt.GetMethodInfo() + e.Message);
}
}
public void removeProxy(string proxy)
{
if (proxy == null)
{
return;
}
if (!isVaildProxy(proxy))
{
Proxy.Remove(proxy);
Log2Txt.logInfo("移除代理：" + proxy);
}
}
/// <summary>
/// 静态构造函数设置SQLServer连接字符串
/// </summary>
static MSSQLDBHelper()
{
try
{
if (string.IsNullOrEmpty(_ConnString))
{
_ConnString = ConfigurationManager.ConnectionStrings["Conn_Engine"].ToString();
}
}
catch (Exception e)
{
Log2Txt.logError(Log2Txt.GetMethodInfo() + e.Message);
}
}
/// <summary>
/// 打开OracleConnection
/// </summary>
/// <param name="conn">数据库连接对象</param>
private static void OpenConnection(SqlConnection conn)
{
try
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
}
catch (Exception e)
{
Log2Txt.logError(Log2Txt.GetMethodInfo() + e.Message);
}
}
/// <summary>
/// 关闭Connection
/// </summary>
/// <param name="conn">数据库(Oracle)连接对象</param>
private static void CloseConnection(SqlConnection conn)
{
try
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
conn.Dispose();//释放资源
}
}
catch (Exception e)
{
Log2Txt.logError(Log2Txt.GetMethodInfo() + e.Message);
}
}
public void getProxyFormFile()
{
try
{
if (!File.Exists(proxyFilePath))
{
File.Create(proxyFilePath).Close();
}
Proxy.AddRange(File.ReadAllLines(proxyFilePath).ToList());
}
catch (IOException e)
{
Log2Txt.logError(Log2Txt.GetMethodInfo() + e.Message);
}
}
public void start()
{
while (true)
{
try
{
if (File.Exists(proxyFilePath))
{
getProxyFormFile();
}
crawlProxy();
}
catch (Exception e)
{
Log2Txt.logError(Log2Txt.GetMethodInfo() + e.Message);
}
}
}
/// <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>
/// 多语句（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);
}
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>
/// 返回字典型表
/// </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);
}
/// <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>
/// 存储过程(增、删、改)
/// </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>
/// 多语句（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="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);
}
}
