MySqlDBHelper数据库连接

这里是本人在工作中用到，希望给大家帮助

public class MySqlDBHelper
{
//获取一个记录器
private static readonly log4net.ILog log = log4net.LogManager.GetLogger(typeof(MySqlDBHelper));

//数据库连接字符串（注意：这里的“DBConnectionString”一定要与web.config文件中connectionStrings节点值一致）
// public static readonly string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["DBConnectionString"].ToString();
public static string connectionString = ConfigurationManager.AppSettings["MySql_difserver"];

/// <summary>
/// 给定连接的数据库用假设参数执行一个sql命令（不返回数据集）
/// </summary>
/// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param>
/// <param name="cmdText">存储过程名称或者sql命令语句</param>
/// <param name="commandParameters">执行命令所用参数的集合</param>
/// <returns>执行命令所影响的行数</returns>
public static int UpdateMySql(string sqlText)
{
//写入一条新log ,这样就将信息同时输出到控制台和写入到数据库
log.Info(DateTime.Now.ToString() + "{" + sqlText + "}" + Environment.NewLine);
return ExecuteNonQuery(sqlText, CommandType.Text, null);
}
public static DataTable QueryDataTable(string sqlText)
{
//创建一个空表
DataTable dtresult = null;
//创建DataSet，并将数据传给DataSet
DataSet ds = GetDataSet(sqlText, CommandType.Text, null);
//条件判断
if (ds != null && ds.Tables.Count > 0)
{
dtresult = ds.Tables[0];
}
//添加一条新LOG
log.Info(DateTime.Now.ToString() + "{" + sqlText + "}" + Environment.NewLine);
return dtresult;

}

public static int CheckExist(string sqlText)
{
log.Info(DateTime.Now.ToString() + "{" + sqlText + "}" + Environment.NewLine);
//初始值为0
int result = 0;
// ExecuteReader。此方法用于返回 SqlDataReader 对象，该对象包含由某一命令返回的结果集。
MySqlDataReader mysqlDr = ExecuteReader(sqlText, CommandType.Text, null);
// HasRows表示的是从数据库读出来的数据集dataReader中是否存在数据，它的作用是用来判断数据集是否为空，执行的结果为 true / false
if (mysqlDr.HasRows)
{
result = 1;
}
else
{
result = 0;
}
return result;
}

// 用于缓存参数的HASH表
private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
/// <summary>
/// 给定连接的数据库用假设参数执行一个sql命令（不返回数据集）
/// </summary>
/// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param>
/// <param name="cmdText">存储过程名称或者sql命令语句</param>
/// <param name="commandParameters">执行命令所用参数的集合</param>
/// <returns>执行命令所影响的行数</returns>
public static int ExecuteNonQuery(string cmdText, CommandType cmdType = CommandType.Text, params MySqlParameter[] commandParameters)
{
MySqlCommand cmd = new MySqlCommand();
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
}

/// <summary>
/// 用执行的数据库连接执行一个返回数据集的sql命令
/// </summary>
/// <remarks>
/// 举例:
/// MySqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24));
/// </remarks>
/// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param>
/// <param name="cmdText">存储过程名称或者sql命令语句</param>
/// <param name="commandParameters">执行命令所用参数的集合</param>
/// <returns>包含结果的读取器</returns>
public static MySqlDataReader ExecuteReader(string cmdText, CommandType cmdType = CommandType.Text, params MySqlParameter[] commandParameters)
{
//创建一个MySqlCommand对象，数据库命令执行时使用Command对象
MySqlCommand cmd = new MySqlCommand();
//创建一个MySqlConnection对象，Connection对象连接数据库
MySqlConnection conn = new MySqlConnection(connectionString);

//在这里我们用一个try/catch结构执行sql文本命令/存储过程，因为如果这个方法产生一个异常我们要关闭连接，因为没有读取器存在，
//因此commandBehaviour.CloseConnection 就不会执行
try
{
//调用 PrepareCommand 方法，对 MySqlCommand 对象设置参数
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
//调用 MySqlCommand 的 ExecuteReader 方法
MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
//清除参数
cmd.Parameters.Clear();
return reader;
}
catch
{
//关闭连接，抛出异常
conn.Close();
throw;
}
}
/// <summary>
/// 返回DataSet
/// </summary>
/// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param>
/// <param name="cmdText">存储过程名称或者sql命令语句</param>
/// <param name="commandParameters">执行命令所用参数的集合</param>
/// <returns></returns>
public static DataSet GetDataSet(string cmdText, CommandType cmdType = CommandType.Text, params MySqlParameter[] commandParameters)
{
//创建一个MySqlCommand对象
MySqlCommand cmd = new MySqlCommand();
//创建一个MySqlConnection对象
MySqlConnection conn = new MySqlConnection(connectionString);
//在这里我们用一个try/catch结构执行sql文本命令/存储过程，因为如果这个方法产生一个异常我们要关闭连接，因为没有读取器存在，
try
{
//调用 PrepareCommand 方法，对 MySqlCommand 对象设置参数
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
//调用 MySqlCommand 的 ExecuteReader 方法
MySqlDataAdapter adapter = new MySqlDataAdapter();
adapter.SelectCommand = cmd;
DataSet ds = new DataSet();

adapter.Fill(ds);
//清除参数
cmd.Parameters.Clear();
conn.Close();
return ds;
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// 用指定的数据库连接字符串执行一个命令并返回一个数据集的第一列
/// </summary>
/// <remarks>
///例如:
/// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24));
/// </remarks>
/// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param>
/// <param name="cmdText">存储过程名称或者sql命令语句</param>
/// <param name="commandParameters">执行命令所用参数的集合</param>
/// <returns>用 Convert.To{Type}把类型转换为想要的 </returns>
public static object ExecuteScalar(string cmdText, CommandType cmdType = CommandType.Text, params MySqlParameter[] commandParameters)
{
MySqlCommand cmd = new MySqlCommand();

using (MySqlConnection connection = new MySqlConnection(connectionString))
{
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
}
/// <summary>
/// 将参数集合添加到缓存
/// </summary>
/// <param name="cacheKey">添加到缓存的变量</param>
/// <param name="commandParameters">一个将要添加到缓存的sql参数集合</param>
public static void CacheParameters(string cacheKey, params MySqlParameter[] commandParameters)
{
parmCache[cacheKey] = commandParameters;
}

/// <summary>
/// 找回缓存参数集合
/// </summary>
/// <param name="cacheKey">用于找回参数的关键字</param>
/// <returns>缓存的参数集合</returns>
public static MySqlParameter[] GetCachedParameters(string cacheKey)
{
MySqlParameter[] cachedParms = (MySqlParameter[])parmCache[cacheKey];
if (cachedParms == null)
return null;
MySqlParameter[] clonedParms = new MySqlParameter[cachedParms.Length];
for (int i = 0, j = cachedParms.Length; i < j; i++)
clonedParms[i] = (MySqlParameter)((ICloneable)cachedParms[i]).Clone();
return clonedParms;
}
/// <summary>
/// 准备执行一个命令
/// </summary>
/// <param name="cmd">sql命令</param>
/// <param name="conn">OleDb连接</param>
/// <param name="trans">OleDb事务</param>
/// <param name="cmdType">命令类型例如存储过程或者文本</param>
/// <param name="cmdText">命令文本,例如:Select * from Products</param>
/// <param name="cmdParms">执行命令的参数</param>
private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, CommandType cmdType, string cmdText, MySqlParameter[] cmdParms)
{
if (conn.State != ConnectionState.Open)
conn.Open();

cmd.Connection = conn;
cmd.CommandText = cmdText;

if (trans != null)
cmd.Transaction = trans;

cmd.CommandType = cmdType;

if (cmdParms != null)
{
foreach (MySqlParameter parameter in cmdParms)
{
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
(parameter.Value == null))
{
parameter.Value = DBNull.Value;
}
cmd.Parameters.Add(parameter);
}
}
}
/// <summary>
/// DataTable批量加入MYSQL数据库
/// </summary>
/// <param name="dataTable"></param>
/// <returns></returns>
public static string InsertByDataTable(DataTable dataTable)
{
string result = string.Empty;
if (null == dataTable || dataTable.Rows.Count <= 0)
{
return "添加失败！DataTable暂无数据！";
}
if (string.IsNullOrEmpty(dataTable.TableName))
{
return "添加失败！请先设置DataTable的名称！";
}
// 构建INSERT语句
StringBuilder sb = new StringBuilder();
sb.Append("INSERT INTO " + dataTable.TableName + "(");
for (int i = 0; i < dataTable.Columns.Count; i++)
{
sb.Append(dataTable.Columns[i].ColumnName + ",");
}
sb.Remove(sb.ToString().LastIndexOf(','), 1);
sb.Append(") VALUES ");
for (int i = 0; i < dataTable.Rows.Count; i++)
{
sb.Append("(");
for (int j = 0; j < dataTable.Columns.Count; j++)
{
sb.Append("'" + dataTable.Rows[i][j] + "',");
}
sb.Remove(sb.ToString().LastIndexOf(','), 1);
sb.Append("),");
}
sb.Remove(sb.ToString().LastIndexOf(','), 1);
sb.Append(";");
int res = -1;
using (MySqlConnection con = new MySqlConnection(connectionString))
{
con.Open();
using (MySqlCommand cmd = new MySqlCommand(sb.ToString(), con))
{
try
{
res = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
res = -1;
// Unknown column 'names' in 'field list'
result = "操作失败！" + ex.Message.Replace("Unknown column", "未知列").Replace("in 'field list'", "存在字段集合中！");
}
}
}
if (res > 0)
{
result = "success";
}
return result;
}
}
}

---------------------------------------------------------------------------------

namespace MySqlController
{
public class MySql_Comm
{
public static string DaySignQuery(string acc)
{
string sql = string.Format(@" SELECT *
FROM SIGNINFO
WHERE NTID = '{0}'
AND TO_DAYS(NOW()) = TO_DAYS(SIGNTIME)", acc);
return sql;
}
}
}

-----------------------------------------------------------------------------------------

<?xml version="1.0" encoding="utf-8"?>
<configuration>
<configSections>
<section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler, log4net"/>
</configSections>
<log4net>
<appender name="LogFileAppender" type="log4net.Appender.RollingFileAppender">
<file value="C:\\TSMC_ETE\\SysLog\\"/>
<appendToFile value="true"/>
<datePattern value="MMdd\\Clien\t_yyyy-MM-dd_HH.lo\g"/>
<rollingStyle value="Date"/>
<param name="StaticLogFileName" value="false"/>
<layout type="log4net.Layout.PatternLayout">
<param name="ConversionPattern" value="%d [%-5p] %-45c - %m%n"/>
</layout>
</appender>
<appender name="ErrLogFileAppender" type="log4net.Appender.RollingFileAppender">
<file value="C:\\TSMC_ETE\\SysLog\\Err_Client_"/>
<appendToFile value="true"/>
<datePattern value="yyyy-MM-dd.lo\g"/>
<rollingStyle value="Date"/>
<param name="StaticLogFileName" value="false"/>
<layout type="log4net.Layout.PatternLayout">
<param name="ConversionPattern" value="%d [%-5p] %-45c - %m%n"/>
</layout>
<threshold value="WARN"/>
</appender>
<root>
<level value="ALL"/>

<appender-ref ref="LogFileAppender"/>
<appender-ref ref="ErrLogFileAppender"/>
</root>
</log4net>
<appSettings>

<add key="KeyWord" value=""/>
<add key="Username" value="litmfdm10"/>
<add key="Password" value="f10litmfdm10"/>
<add key="JsonDataChannel" value="https://f10litjboss:8443/si_mfdm10/listener?"/>
<add key="JsonDataChannel2" value="https://f10litjboss:8443/si_odsf10/listener?"/>
<add key="ClientSettingsProvider.ServiceUri" value=""/>
<add key="MySql_difserver" value="server=difserver;user id=root;password=root;database=tsmc_ete;persist security info=true;charset=utf8"/>

<add key="DIF_FTPUserID" value="DIFFTP" />
<add key="DIF_FTPUserPWD" value="difTSMCSH2017" />
<add key="DIF_FTPIP" value="mydif" />
<add key="DIF_FTPSERVER" value="ftp://mydif/"/>
<add key="FtpDownLoadPath" value="C:\\TSMC_ETE\\REPORT\\"/>
</appSettings>
<startup><supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/></startup><system.serviceModel>
<bindings>
<basicHttpBinding>
<binding name="BasicHttpBinding_IMail" />
</basicHttpBinding>
</bindings>
<client>
<endpoint address="http://mydif:88/MailService.svc" binding="basicHttpBinding"
bindingConfiguration="BasicHttpBinding_IMail" contract="DIFMailWCF.IMail"
name="BasicHttpBinding_IMail" />
</client>
</system.serviceModel>
</configuration>

-----------------------------------------------------------------------------------------------------------

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;

namespace CommonHelper
{
/// <summary>
/// 标题：Excel文件助手类
/// 描述：1.读取指定条件的Excel信息到内存中
/// 2.将内存中的信息导出到Excel文件中
/// 3.消除Excel文件中的空白记录
/// </summary>
public static class ExcelHelper
{
#region Field

/// <summary>
/// 读取Excel的连接字符串
/// </summary>
/// <example>
/// Provider代表连接驱动4.0版本
/// Data Source代表Excel的路径
/// Extended Properties代表连接Excel的版本，对于Excel 97以上版本都用Excel 8.0
/// HDR代表默认Excel第一行是否列名，Yse代表是可以直接读取，No反之
/// IMEX代表
/// </example>
private const string strFormat = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = {0};Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";

#endregion

#region Method
/// <summary>
/// 把指定的DataTable里的空白行去除，返回一个没有空白行的DataTable
/// </summary>
/// <param name="dataTable">原始的DataTable</param>
/// <returns>没有空白行的DataTable</returns>
public static System.Data.DataTable DataTableEmptyRowsFilter(System.Data.DataTable dataTable)
{
System.Data.DataTable newDataTable = null;
if (dataTable.Rows.Count != 0)
{
newDataTable = dataTable.Clone();
foreach (DataRow dr in dataTable.Rows)
{
if (dr[0].ToString() != string.Empty)
{
newDataTable.ImportRow(dr);
}
}
}
return newDataTable;
}

/// <summary>
/// 读取Excel文件内指定sheet页的数据到DataSet
/// </summary>
/// <param name="strFilePath">Excel文件完整路径</param>
/// <param name="dataTable">读取的表格数据</param>
/// <param name="strSheetName">sheet名称</param>
/// <param name="strNewSheetName">dataTable名称</param>
/// <returns>操作错误信息，若为空则执行成功</returns>
public static string TryRead(string strFilePath, out System.Data.DataTable dataTable, string strSheetName = "Sheet", string strNewSheetName = "ExcelInfo")
{
string errormsg = string.Empty;

if (!string.IsNullOrEmpty(strFilePath) && File.Exists(strFilePath))
{
string strConn = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = " + strFilePath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
//string strConn = string.Format(strFormat, strFilePath);
string strSQL = "SELECT * FROM [" + strSheetName + "$]";
//string strSQL = string.Format("SELECT * FROM [{0}$]", strSheetName);
try
{
using (OleDbDataAdapter ExcelDA = new OleDbDataAdapter(strSQL, strConn))
{
DataSet ExcelDS = new DataSet();
ExcelDA.Fill(ExcelDS, strNewSheetName);
dataTable = DataTableEmptyRowsFilter(ExcelDS.Tables[0]);
}
}
catch (Exception err)
{
errormsg = err.Message;
dataTable = null;
}
}
else
{
errormsg = "The file path of the file does not exist";
dataTable = null;
}

return errormsg;
}

/// <summary>
/// 将DataTable写入指定路径Excel文件
/// </summary>
/// <remarks>
/// 采用将导出的数据保存到数组，然后一次过导出，以提高速度
/// excelSheet.Merge(Missing.Value); 合并
/// excelSheet.Font.Bold=true; 设置粗体
/// excelSheet.Font.Size=12;设置字体大小
/// excelSheet.HorizontalAlignment=Excel.XlHAlign.xlHAlignCenter;水平对齐设置
/// excelSheet.VerticalAlignment=Excel.XlVAlign.xlVAlignCenter;垂直对齐设置
/// excelSheet.FormulaR1C1=公式; 公式设置
/// excelSheet.ColumnWidth=20; 设置列宽
/// excelSheet.RowHeight=20; 设置行高
/// </remarks>
/// <param name="dTable">数据表</param>
/// <param name="strFilePath">导出路径文件夹路径</param>
/// <param name="SaveMode">false新建Excel保存，true向已存在的Excel中添加Sheet保存</param>
/// <param name="strSheetName">导出Excel的Sheet名</param>
/// <param name="startCol">Excel内容开始列，默认为1，第一列为标题</param>
/// <param name="startRow">Excel内容开始行，默认为1，第一行为标题</param>
public static string DataTableToExcel(System.Data.DataTable dTable, string strFiledirectPath, bool SaveMode, string strSheetName = "", int startRow = 1, int startCol = 1)
{
string errorMsg = string.Empty;

if (dTable == null || dTable.Rows.Count <= 0 || startRow <= 0 || startCol <= 0)
{
return errorMsg = "DataTable is null";
}

var excel = new Application();
Workbook excelBook;
if (SaveMode)
excelBook = excel.Workbooks.Open(strFiledirectPath);
else
excelBook = excel.Workbooks.Add(Type.Missing);
var excelSheet = (Worksheet)excelBook.Sheets.Add();
var rowCount = dTable.Rows.Count;
var colCount = dTable.Columns.Count;

//设置新建Sheet的名字
if (string.IsNullOrWhiteSpace(strSheetName))
excelSheet.Name = "Sheet" + excelBook.Sheets.Count;
else
excelSheet.Name = strSheetName;

//二维数组定义是多一个标题行
var dataArray = new object[rowCount + 1, colCount];

for (var j = 0; j < colCount; j++)
{
//导出字段标题
dataArray[0, j] = dTable.Columns[j].Caption;

//根据各列的数据类型设置Excel的格式。
switch (dTable.Columns[j].DataType.ToString())
{
case "System.String":
excelSheet.get_Range(excelSheet.Cells[startRow, startCol + j] as Range, excelSheet.Cells[rowCount + startRow, startCol + j] as Range).
NumberFormatLocal = "@";
break;
case "System.DateTime":
excelSheet.get_Range(excelSheet.Cells[startRow, startCol + j] as Range, excelSheet.Cells[rowCount + startRow, startCol + j] as Range).
NumberFormatLocal = "yyyy-MM-dd HH:mm:ss";
break;
//可以根据自己的需要扩展。
default:
excelSheet.get_Range(excelSheet.Cells[startRow, startCol + j] as Range, excelSheet.Cells[rowCount + startRow, startCol + j] as Range).
NumberFormatLocal = "G/通用格式";
break;
}
for (int i = 0; i < rowCount; i++)
{
dataArray[i + 1, j] = dTable.Rows[i][j];
}
}

//写入Excel Sheet
excelSheet.get_Range(excel.Cells[startRow, startCol] as Range, excel.Cells[rowCount + startRow, colCount + startCol - 1] as Range).Value2 = dataArray;
//设置列头为粗体字
excelSheet.get_Range(excel.Cells[1, 1] as Range, excel.Cells[1, colCount] as Range).Font.Bold = true;
//设置列头底色为灰色
excelSheet.get_Range(excel.Cells[1, 1] as Range, excel.Cells[1, colCount] as Range).Interior.ColorIndex = 15;
//设置内容的字体大小为9
excelSheet.get_Range(excel.Cells[2, 1] as Range, excel.Cells[rowCount + startRow, colCount + startCol - 1] as Range).Font.Size = 9;
//设置内容的字体微软雅黑
excelSheet.get_Range(excel.Cells[2, 1] as Range, excel.Cells[rowCount + startRow, colCount + startCol - 1] as Range).Font.Name = "微软雅黑";
//设置Sheet的名称
//if (!String.IsNullOrWhiteSpace(dTable.TableName))
// excelSheet.Name = dTable.TableName;

//保存文档
try
{
//strFiledirectPath = Path.Combine(strFiledirectPath, dTable.TableName + ".xls");
excelBook.Saved = true;
if (SaveMode)
excelBook.Save();
else
excelBook.SaveCopyAs(strFiledirectPath);
}
catch (Exception ex)
{
errorMsg = ex.Message;
}
finally
{
excel.Quit();
GC.Collect();
}

return errorMsg;
}
#endregion
}
}

------------------------------------------------------------------------------------------------------------------

using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;

namespace CommonHelper
{
public static class INIHelper
{

#region 读写INI文件相关
[DllImport("kernel32.dll", EntryPoint = "WritePrivateProfileString", CharSet = CharSet.Ansi)]
private static extern long WritePrivateProfileString(string section, string key, string val, string filePath);

[DllImport("kernel32.dll", EntryPoint = "GetPrivateProfileString", CharSet = CharSet.Ansi)]
private static extern int GetPrivateProfileString(string section, string key, string def, StringBuilder retVal, int size, string filePath);

[DllImport("kernel32")]
private static extern int GetPrivateProfileInt(string lpApplicationName, string lpKeyName, int nDefault, string lpFileName);

[DllImport("kernel32.dll", EntryPoint = "GetPrivateProfileSectionNames", CharSet = CharSet.Ansi)]
private static extern int GetPrivateProfileSectionNames(IntPtr lpszReturnBuffer, int nSize, string filePath);

[DllImport("KERNEL32.DLL ", EntryPoint = "GetPrivateProfileSection", CharSet = CharSet.Ansi)]
private static extern int GetPrivateProfileSection(string lpAppName, byte[] lpReturnedString, int nSize, string filePath);
#endregion

#region 读写操作（字符串）
/// <summary>
/// 向INI写入数据
/// </summary>
/// <PARAM name="Section">节点名</PARAM>
/// <PARAM name="Key">键名</PARAM>
/// <PARAM name="Value">值（字符串）</PARAM>
public static void Write(string Section, string Key, string Value, string path)
{
WritePrivateProfileString(Section, Key, Value, path);
}
/// <summary>
/// 读取INI数据
/// </summary>
/// <PARAM name="Section">节点名</PARAM>
/// <PARAM name="Key">键名</PARAM>
/// <PARAM name="Path">值名</PARAM>
/// <returns>值（字符串）</returns>
public static string Read(string Section, string Key, string path)
{
StringBuilder temp = new StringBuilder(255);
int i = GetPrivateProfileString(Section, Key, "", temp, 255, path);
return temp.ToString();
}
#endregion

#region 配置节信息
/// <summary>
/// 读取一个ini里面所有的节
/// </summary>
/// <param name="sections"></param>
/// <param name="path"></param>
/// <returns>-1:没有节信息，0:正常</returns>
public static int GetAllSectionNames(out string[] sections, string path)
{
int MAX_BUFFER = 32767;
IntPtr pReturnedString = Marshal.AllocCoTaskMem(MAX_BUFFER);
int bytesReturned = GetPrivateProfileSectionNames(pReturnedString, MAX_BUFFER, path);
if (bytesReturned == 0)
{
sections = null;
return -1;
}
string local = Marshal.PtrToStringAnsi(pReturnedString, (int)bytesReturned).ToString();
Marshal.FreeCoTaskMem(pReturnedString);
//use of Substring below removes terminating null for split
sections = local.Substring(0, local.Length - 1).Split('\0');
return 0;
}
/// <summary>
/// 返回指定配置文件下的节名称列表
/// </summary>
/// <param name="path"></param>
/// <returns></returns>
public static List<string> GetAllSectionNames(string path)
{
List<string> sectionList = new List<string>();
int MAX_BUFFER = 32767;
IntPtr pReturnedString = Marshal.AllocCoTaskMem(MAX_BUFFER);
int bytesReturned = GetPrivateProfileSectionNames(pReturnedString, MAX_BUFFER, path);
if (bytesReturned != 0)
{
string local = Marshal.PtrToStringAnsi(pReturnedString, (int)bytesReturned).ToString();
Marshal.FreeCoTaskMem(pReturnedString);
sectionList.AddRange(local.Substring(0, local.Length - 1).Split('\0'));
}
return sectionList;
}

/// <summary>
/// 得到某个节点下面所有的key和value组合
/// </summary>
/// <param name="section">指定的节名称</param>
/// <param name="keys">Key数组</param>
/// <param name="values">Value数组</param>
/// <param name="path">INI文件路径</param>
/// <returns></returns>
public static int GetAllKeyValues(string section, out string[] keys, out string[] values, string path)
{
byte[] b = new byte[65535];//配置节下的所有信息
GetPrivateProfileSection(section, b, b.Length, path);
string s = System.Text.Encoding.Default.GetString(b);//配置信息
string[] tmp = s.Split((char)0);//Key\Value信息
List<string> result = new List<string>();
foreach (string r in tmp)
{
if (r != string.Empty)
result.Add(r);
}
keys = new string[result.Count];
values = new string[result.Count];
for (int i = 0; i < result.Count; i++)
{
string[] item = result[i].Split(new char[] { '=' });//Key=Value格式的配置信息
//Value字符串中含有=的处理，
//一、Value加""，先对""处理
//二、Key后续的都为Value
if (item.Length > 2)
{
keys[i] = item[0].Trim();
values[i] = result[i].Substring(keys[i].Length + 1);
}
if (item.Length == 2)//Key=Value
{
keys[i] = item[0].Trim();
values[i] = item[1].Trim();
}
else if (item.Length == 1)//Key=
{
keys[i] = item[0].Trim();
values[i] = "";
}
else if (item.Length == 0)
{
keys[i] = "";
values[i] = "";
}
}
return 0;
}
/// <summary>
/// 得到某个节点下面所有的key
/// </summary>
/// <param name="section">指定的节名称</param>
/// <param name="keys">Key数组</param>
/// <param name="path">INI文件路径</param>
/// <returns></returns>
public static int GetAllKeys(string section, out string[] keys, string path)
{
byte[] b = new byte[65535];

GetPrivateProfileSection(section, b, b.Length, path);
string s = System.Text.Encoding.Default.GetString(b);
string[] tmp = s.Split((char)0);
ArrayList result = new ArrayList();
foreach (string r in tmp)
{
if (r != string.Empty)
result.Add(r);
}
keys = new string[result.Count];
for (int i = 0; i < result.Count; i++)
{
string[] item = result[i].ToString().Split(new char[] { '=' });
if (item.Length == 2)
{
keys[i] = item[0].Trim();
}
else if (item.Length == 1)
{
keys[i] = item[0].Trim();
}
else if (item.Length == 0)
{
keys[i] = "";
}
}
return 0;
}
/// <summary>
/// 获取指定节下的Key列表
/// </summary>
/// <param name="section">指定的节名称</param>
/// <param name="path">配置文件名称</param>
/// <returns>Key列表</returns>
public static List<string> GetAllKeys(string section, string path)
{
List<string> keyList = new List<string>();
byte[] b = new byte[65535];
GetPrivateProfileSection(section, b, b.Length, path);
string s = System.Text.Encoding.Default.GetString(b);
string[] tmp = s.Split((char)0);
List<string> result = new List<string>();
foreach (string r in tmp)
{
if (r != string.Empty)
result.Add(r);
}
for (int i = 0; i < result.Count; i++)
{
string[] item = result[i].Split(new char[] { '=' });
if (item.Length == 2 || item.Length == 1)
{
keyList.Add(item[0].Trim());
}
else if (item.Length == 0)
{
keyList.Add(string.Empty);
}
}
return keyList;
}
/// <summary>
/// 获取值
/// </summary>
/// <param name="section"></param>
/// <param name="path"></param>
/// <returns></returns>
public static List<string> GetAllValues(string section, string path)
{
List<string> keyList = new List<string>();
byte[] b = new byte[65535];
GetPrivateProfileSection(section, b, b.Length, path);
string s = System.Text.Encoding.Default.GetString(b);
string[] tmp = s.Split((char)0);
List<string> result = new List<string>();
foreach (string r in tmp)
{
if (r != string.Empty)
result.Add(r);
}
for (int i = 0; i < result.Count; i++)
{
string[] item = result[i].Split(new char[] { '=' });
if (item.Length == 2 || item.Length == 1)
{
keyList.Add(item[1].Trim());
}
else if (item.Length == 0)
{
keyList.Add(string.Empty);
}
}
return keyList;
}

#endregion

#region 通过值查找键（一个节中的键唯一，可能存在多个键值相同，因此反查的结果可能为多个）

/// <summary>
/// 第一个键
/// </summary>
/// <param name="section"></param>
/// <param name="path"></param>
/// <param name="value"></param>
/// <returns></returns>
public static string GetFirstKeyByValue(string section, string path, string value)
{
foreach (string key in GetAllKeys(section, path))
{
if (ReadString(section, key, "", path) == value)
{
return key;
}
}
return string.Empty;
}
/// <summary>
/// 所有键
/// </summary>
/// <param name="section"></param>
/// <param name="path"></param>
/// <param name="value"></param>
/// <returns></returns>
public static List<string> GetKeysByValue(string section, string path, string value)
{
List<string> keys = new List<string>();
foreach (string key in GetAllKeys(section, path))
{
if (ReadString(section, key, "", path) == value)
{
keys.Add(key);
}
}
return keys;
}
#endregion

#region 具体类型的读写

#region string
/// <summary>
///
/// </summary>
/// <param name="sectionName"></param>
/// <param name="keyName"></param>
/// <param name="defaultValue" />
/// <param name="path"></param>
/// <returns></returns>
public static string ReadString(string sectionName, string keyName, string defaultValue, string path)
{
const int MAXSIZE = 255;
StringBuilder temp = new StringBuilder(MAXSIZE);
GetPrivateProfileString(sectionName, keyName, defaultValue, temp, 255, path);
return temp.ToString();
}

/// <summary>
///
/// </summary>
/// <param name="sectionName"></param>
/// <param name="keyName"></param>
/// <param name="value"></param>
/// <param name="path"></param>
public static void WriteString(string sectionName, string keyName, string value, string path)
{
WritePrivateProfileString(sectionName, keyName, value, path);
}
#endregion

#region Int
/// <summary>
///
/// </summary>
/// <param name="sectionName"></param>
/// <param name="keyName"></param>
/// <param name="defaultValue"></param>
/// <param name="path"></param>
/// <returns></returns>
public static int ReadInteger(string sectionName, string keyName, int defaultValue, string path)
{

return GetPrivateProfileInt(sectionName, keyName, defaultValue, path);

}
/// <summary>
///
/// </summary>
/// <param name="sectionName"></param>
/// <param name="keyName"></param>
/// <param name="value"></param>
/// <param name="path"></param>
public static void WriteInteger(string sectionName, string keyName, int value, string path)
{

WritePrivateProfileString(sectionName, keyName, value.ToString(), path);

}
#endregion

#region bool
/// <summary>
/// 读取布尔值
/// </summary>
/// <param name="sectionName"></param>
/// <param name="keyName"></param>
/// <param name="defaultValue"></param>
/// <param name="path"></param>
/// <returns></returns>
public static bool ReadBoolean(string sectionName, string keyName, bool defaultValue, string path)
{

int temp = defaultValue ? 1 : 0;

int result = GetPrivateProfileInt(sectionName, keyName, temp, path);

return (result == 0 ? false : true);

}
/// <summary>
/// 写入布尔值
/// </summary>
/// <param name="sectionName"></param>
/// <param name="keyName"></param>
/// <param name="value"></param>
/// <param name="path"></param>
public static void WriteBoolean(string sectionName, string keyName, bool value, string path)
{
string temp = value ? "1 " : "0 ";
WritePrivateProfileString(sectionName, keyName, temp, path);
}
#endregion

#endregion

#region 删除操作
/// <summary>
/// 删除指定项
/// </summary>
/// <param name="sectionName"></param>
/// <param name="keyName"></param>
/// <param name="path"></param>
public static void DeleteKey(string sectionName, string keyName, string path)
{
WritePrivateProfileString(sectionName, keyName, null, path);
}

/// <summary>
/// 删除指定节下的所有项
/// </summary>
/// <param name="sectionName"></param>
/// <param name="path"></param>
public static void EraseSection(string sectionName, string path)
{
WritePrivateProfileString(sectionName, null, null, path);
}
#endregion

#region 判断节、键是否存在
/// <summary>
/// 指定节知否存在
/// </summary>
/// <param name="section"></param>
/// <param name="fileName"></param>
/// <returns></returns>
public static bool ExistSection(string section, string fileName)
{
string[] sections = null;
GetAllSectionNames(out sections, fileName);
if (sections != null)
{
foreach (var s in sections)
{
if (s == section)
{
return true;
}
}
}
return false;
}
/// <summary>
/// 指定节下的键是否存在
/// </summary>
/// <param name="section"></param>
/// <param name="key"></param>
/// <param name="fileName"></param>
/// <returns></returns>
public static bool ExistKey(string section, string key, string fileName)
{
string[] keys = null;
GetAllKeys(section, out keys, fileName);
if (keys != null)
{
foreach (var s in keys)
{
if (s == key)
{
return true;
}
}
}
return false;
}
#endregion

#region 同一Section下添加多个Key\Value
/// <summary>
///
/// </summary>
/// <param name="section"></param>
/// <param name="keyList"></param>
/// <param name="valueList"></param>
/// <param name="path"></param>
/// <returns></returns>
public static bool AddSectionWithKeyValues(string section, List<string> keyList, List<string> valueList, string path)
{
bool bRst = true;
//判断Section是否已经存在，如果存在，返回false
//已经存在，则更新
//if (GetAllSectionNames(path).Contains(section))
//{
// return false;
//}
//判断keyList中是否有相同的Key，如果有，返回false

//添加配置信息
for (int i = 0; i < keyList.Count; i++)
{
WriteString(section, keyList[i], valueList[i], path);
}
return bRst;
}
#endregion
}
}

MySqlDBHelper数据库连接

推荐阅读