/// <summary>
/// 配置文件的读取
/// </summary>
public class INIClass
{
public string inipath;
[DllImport("kernel32")]
private static extern long WritePrivateProfileString(string section, string key, string val, string filePath);
[DllImport("kernel32")]
private static extern int GetPrivateProfileString(string section, string key, string def, StringBuilder retVal, int size, string filePath);
/// <summary>
/// 构造方法
/// </summary>
/// <param name="INIPath">文件路径</param>
public INIClass(string INIPath)
{
inipath = INIPath;
}
/// <summary>
/// 写入INI文件
/// </summary>
/// <param name="Section">项目名称(如 [TypeName] )</param>
/// <param name="Key">键</param>
/// <param name="Value">值</param>
public void IniWriteValue(string Section, string Key, string Value)
{
WritePrivateProfileString(Section, Key, Value, this.inipath);
}
/// <summary>
/// 读出INI文件
/// </summary>
/// <param name="Section">项目名称(如 [TypeName] )</param>
/// <param name="Key">键</param>
public string IniReadValue(string Section, string Key)
{
StringBuilder temp = new StringBuilder(500);
int i = GetPrivateProfileString(Section, Key, "", temp, 500, this.inipath);
return temp.ToString();
}
/// <summary>
/// 验证文件是否存在
/// </summary>
/// <returns>布尔值</returns>
public bool ExistINIFile()
{
return File.Exists(inipath);
}
}
/// <summary>
/// 通用数据访问类
/// </summary>
public class SQLHelper
{
public static string connString = null;
/// <summary>
/// 执行增删改操作
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static int OperateInfoOfSQLServer(string sql)
{
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
int result = cmd.ExecuteNonQuery();
conn.Close();
return result;
}
/// <summary>
/// 添加一条数据并返回ID
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static int GetSingleResultID(string sql)
{
int id = 0;
using (SqlConnection conn = new SqlConnection(connString))
{
SqlCommand cmd = new SqlCommand();
conn.Open();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
try
{
cmd.ExecuteNonQuery(); //注意,执行这句之后先不要关闭连接
cmd.CommandText = "select @@identity as id"; //用ExecuteScalar()方法执行这句就能得到ID了
id = Convert.ToInt32(cmd.ExecuteScalar()); //这里得到新产品ID
//清除内存参数
cmd.Parameters.Clear();
}
catch (Exception ex)
{
throw new ApplicationException("插入的数据有失败" + ex.Message);
}
finally
{
conn.Close();
}
}
return id;
}
/// <summary>
/// 获取一张数据表
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static DataTable GetTable(string sql)
{
using (SqlConnection conn = new SqlConnection(connString))
{
try
{
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
return ds.Tables[0];
}
catch (Exception ex)
{
throw ex;
return null;
}
}
}
/// <summary>
/// 返回单一结果的查询
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static object GetSingleResult(string sql)
{
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sql, conn);
try
{
conn.Open();
MessageBox.Show("成功连接数据库");
object result = cmd.ExecuteScalar();
return result;
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
}
}
/// <summary>
/// 返回一个结果集的查询
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static SqlDataReader GetReader(string sql)
{
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
#region 执行带参数的SQL语句
public static int Update(string sql, SqlParameter[] parameters)
{
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sql, conn);
try
{
conn.Open();
cmd.Parameters.AddRange(parameters);//添加参数
int result = cmd.ExecuteNonQuery();
return result;
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
public static object GetSingleResult(string sql, SqlParameter[] parameters)
{
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sql, conn);
try
{
conn.Open();
cmd.Parameters.AddRange(parameters);//添加参数
object result = cmd.ExecuteScalar();
return result;
}
catch (Exception ex)
{
//写入日志...
throw ex;
}
finally
{
conn.Close();
}
}
public static SqlDataReader GetReader(string sql, SqlParameter[] parameters)
{
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sql, conn);
try
{
conn.Open();
cmd.Parameters.AddRange(parameters);//添加参数
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
//写入日志...
conn.Close();
throw ex;
}
}
#endregion
/// <summary>
/// 预处理
/// </summary>
/// <param name="conn"></param>
/// <param name="cmd"></param>
/// <param name="cmdType"></param>
/// <param name="sqlParams"></param>
private static void PreparedCommand(SqlConnection conn, SqlCommand cmd, CommandType cmdType, SqlParameter[] sqlParams)
{
cmd.CommandType = cmdType;
if (sqlParams != null)
cmd.Parameters.AddRange(sqlParams);
conn.Open();
}
/// <summary>
/// 获取一张数据表
/// </summary>
/// <param name="sql">SQL语句或存储过程名称</param>
/// <param name="cmdType"></param>
/// <param name="Params">对应的参数</param>
/// <returns></returns>
public static DataTable GetTable(string sql, CommandType cmdType, SqlParameter[] sqlParams)
{
using (SqlConnection conn = new SqlConnection(connString))
{
try
{
SqlCommand cmd = new SqlCommand(sql, conn);
PreparedCommand(conn, cmd, cmdType, sqlParams);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
return ds.Tables[0];
}
catch (Exception ex)
{
return null;
}
}
}
#region 调用存储过程
public static int UpdateByProcedure(string procedureName, SqlParameter[] param)
{
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
try
{
conn.Open();
cmd.CommandType = CommandType.StoredProcedure;//声明当前要执行的是存储过程
cmd.CommandText = procedureName;//commandText只需要赋值存储过程名称即可
cmd.Parameters.AddRange(param);//添加存储过程的参数
int result = cmd.ExecuteNonQuery();
return result;
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
/// <summary>
/// 执行多结果查询(select)
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static SqlDataReader GetReaderByProcedure(string procedureName, SqlParameter[] param)
{
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
try
{
conn.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = procedureName;
cmd.Parameters.AddRange(param);
SqlDataReader objReader =
cmd.ExecuteReader(CommandBehavior.CloseConnection);
return objReader;
}
catch (Exception ex)
{
conn.Close();
throw ex;
}
}
#endregion
#region 启用事务
/// <summary>
/// 启用事务执行多条SQL语句
/// </summary>
/// <param name="sqlList">SQL语句列表</param>
/// <returns></returns>
public static bool ExecSQLByTran(List<string> sqlList)
{
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
conn.Open();
cmd.Transaction = conn.BeginTransaction(); //开启事务
try
{
foreach (string itemSql in sqlList)//循环提交SQL语句
{
cmd.CommandText = itemSql;
cmd.ExecuteNonQuery();
}
cmd.Transaction.Commit(); //提交事务
return true;
}
catch (Exception e)
{
cmd.Transaction.Rollback();//回滚事务
throw e;
}
finally
{
cmd.Transaction = null;
conn.Close();
}
}
#endregion
}
/// <summary>
/// 访问Access数据库通用类
/// </summary>
public class OleDbHelper
{
//适合Excel2003
//private static string connString = "Provider=Microsoft.ACE.OLEDB.4.0;Data Source={0};Extended Properties=Excel8.0";
//创建连接字符串(适合与Excel2007以后的版本)
public static string connString = null;
#region 执行格式化SQL语句的查询
/// <summary>
/// 执行增、删、改方法
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static int OperateInfoOfAccess(string sql)
{
OleDbConnection conn = new OleDbConnection(connString);
OleDbCommand cmd = new OleDbCommand(sql, conn);
try
{
conn.Open();
return cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
/// <summary>
/// 添加一条数据并返回ID
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static int GetSingleResultID(string sql)
{
int id = 0;
using (OleDbConnection conn = new OleDbConnection(connString))
{
OleDbCommand cmd = new OleDbCommand();
conn.Open();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
try
{
cmd.ExecuteNonQuery(); //注意,执行这句之后先不要关闭连接
cmd.CommandText = "select @@identity as id"; //用ExecuteScalar()方法执行这句就能得到ID了
id = Convert.ToInt32(cmd.ExecuteScalar()); //这里得到新产品ID
//清除内存参数
cmd.Parameters.Clear();
}
catch (Exception ex)
{
throw new ApplicationException("插入的数据有失败" + ex.Message);
}
finally
{
conn.Close();
}
}
return id;
}
/// <summary>
/// 获取一张数据表
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static DataTable GetDataTable(string sql)
{
OleDbConnection conn = new OleDbConnection(connString);
OleDbCommand cmd = new OleDbCommand(sql, conn);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
try
{
conn.Open();
da.Fill(ds);
return ds.Tables[0];
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
/// <summary>
/// 执行单一结果
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static object GetSingleResult(string sql)
{
OleDbConnection conn = new OleDbConnection(connString);
OleDbCommand cmd = new OleDbCommand(sql, conn);
try
{
conn.Open();
//return cmd.ExecuteScalar();
object result = cmd.ExecuteScalar();
return result;
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
/// <summary>
/// 执行结果集查询
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static OleDbDataReader GetReader(string sql)
{
OleDbConnection conn = new OleDbConnection(connString);
OleDbCommand cmd = new OleDbCommand(sql, conn);
try
{
conn.Open();
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
throw ex;
}
}
#endregion
/// <summary>
/// 将指定路径的Excel导入到数据集中
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static DataSet GetDataSet(string sql, string path)
{
OleDbConnection conn = new OleDbConnection(string.Format(connString, path));
OleDbCommand cmd = new OleDbCommand(sql, conn);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
try
{
conn.Open();
da.Fill(ds);
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
}