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