using System;
using System.Collections.Generic;
using System.Web;
using System.Data;
using System.Data.OleDb;
using System.Configuration;
using System.Net;
/// <summary>
///sqlHelper 的摘要说明
/// </summary>
public class sqlHelper
{
//获取连接字符串
public static readonly string conn = ConfigurationManager.ConnectionStrings["constr"].ToString();
public static int ExecuteNonQuery(string connectionStrings, CommandType cmdType, string cmdText, params OleDbParameter[] commandParameters)
{
OleDbCommand cmd = new OleDbCommand();
using (OleDbConnection conn = new OleDbConnection(connectionStrings))
{
//通过preparecommand方法将参数逐个加入到OleDbCommand的参数集合中
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
//清空OleDbCommand的值
cmd.Parameters.Clear();
return val;
}
}
public static OleDbDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params OleDbParameter[] commandparamters)
{
OleDbCommand cmd = new OleDbCommand();
OleDbConnection conn = new OleDbConnection(connectionString);
//在这里使用try/catch处理是因为如果方法出现异常，则sqlDataReader就不存在
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandparamters);
OleDbDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return rdr;
}
catch
{
conn.Close();
throw;
}
}
public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params OleDbParameter[] commandParameters)
{
OleDbCommand cmd = new OleDbCommand();
using (OleDbConnection conn = new OleDbConnection(connectionString))
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
}
public static void PrepareCommand(OleDbCommand cmd, OleDbConnection conn, OleDbTransaction trans, CommandType cmdType, string cmdText, OleDbParameter[] cmdParam)
{
//判断数据库连接状态
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
//判断是否需要事务处理
if (trans != null)
cmd.Transaction = trans;
cmd.CommandType = cmdType;
if (cmdParam != null)
{
foreach (OleDbParameter parm in cmdParam)
cmd.Parameters.Add(parm);
}
}
public static DataTable ExectueTable(string strSQL)
{
//strSQL = "select zj_Company.Company_Name,zj_VIP.ID,zj_VIP.Vip_Name,zj_VIP.Vip_Type from zj_VIP,zj_Company where zj_VIP.CompanyID=zj_Company.ID and (zj_VIP.Vip_Type='' or zj_Company.Company_Name='' or zj_VIP.Vip_Name='')";
using (OleDbConnection constr = new OleDbConnection(conn))
{
//实例DataTalbe
DataTable dt = new DataTable();
//添充数据集
OleDbDataAdapter da = new OleDbDataAdapter(strSQL, constr);
da.Fill(dt);
constr.Close();
constr.Dispose();
return dt;
}
}
/// <summary>
/// 执行SQL语句，返回影响的记录数
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSqlCount(string strSQL)
{
int count = 0;
using (OleDbConnection conn = new OleDbConnection(sqlHelper.conn))
{
using (OleDbCommand cmd = new OleDbCommand(strSQL, conn))
{
try
{
conn.Open();
OleDbDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
count += 1;
}
dr.Dispose();
conn.Close();
return count;
}
catch (System.Data.SqlClient.SqlException E)
{
conn.Close();
throw new Exception(E.Message);
}
}
}
}
/// <summary>
/// 分页读取数据
/// </summary>
/// <param name="query">select查询语句</param>
/// <param name="pageSize">每页显示数据数</param>
/// <param name="currentPageIndex">当前页码</param>
/// <param name="dt">存储数据的DataTable实例</param>
public static void FillDataTable(string query, int pageSize, int CurrentPageIndex, DataTable dt)
{
//读取数据的开始索引
long startIndex = (CurrentPageIndex - 1) * pageSize;
//读取数据的结束索引
long endIndex = CurrentPageIndex * pageSize - 1;
//DataReader读取当前索引行
long readToIndex = -1;
using (OleDbConnection conn = new OleDbConnection(sqlHelper.conn))
{
OleDbCommand cmd = new OleDbCommand(query, conn);
conn.Open();
OleDbDataReader dr = cmd.ExecuteReader();
//数据源中的列数
int cols = dr.VisibleFieldCount;
//构造DataTable结构
for (int i = 0; i < cols; i++)
{
dt.Columns.Add(new DataColumn(dr.GetName(i), dr.GetFieldType(i)));
}
//读取数据将数据一行一行的添加到DataTable
while (dr.Read())
{
readToIndex++;
//当DataReader指针在开始索引和结束索引闭区间时才读数据构造DataRow,并添加到DataTable
if (readToIndex >= startIndex && readToIndex <= endIndex)
{
DataRow row = dt.NewRow();
for (int i = 0; i < cols; i++)
{
row[i] = dr[i];
}
dt.Rows.Add(row);
}
}
dr.Close();
conn.Close();
}
}
//取IP地址
public static string getIPAddress()
{
//System.Net.IPAddress addr;
// 获得本机局域网IP地址
//addr = new System.Net.IPAddress(Dns.GetHostByName(Dns.GetHostName()).AddressList[0].Address);
string addr = HttpContext.Current.Request.UserHostAddress;
return addr.ToString();
}
//MD5
public static string md5(string str)
{
str=System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(str,"MD5");
return str;
}
}