实用类：UtilityClass 包含如下方法
判断对象是否为空或NULL,如果是空或NULL返回true，否则返回false
验证手机号是否正确 13,15,18
验证邮箱
验证网址
MD5加密,返回32位的字符串
把字符串的第一个字符变为大写
判断一个字符串是否是时间
生成随机数方法 小于9位
检查某个文件是否存在于磁盘上，存在--true，不存在--false
转换相对路径为物理路径
截取字符枚举值
地址栏传值加密/解密
格式化要显示的内容，主要用于在网页上显示由textarea产生的内容
判断当前访问是否来自非IE浏览器软件
SQL操作类：DBAccess 包含如下方法
实用类：UtilityClass 包含如下方法
判断对象是否为空或NULL,如果是空或NULL返回true，否则返回false
验证手机号是否正确 13,15,18
验证邮箱
验证网址
MD5加密,返回32位的字符串
把字符串的第一个字符变为大写
判断一个字符串是否是时间
生成随机数方法 小于9位
检查某个文件是否存在于磁盘上，存在--true，不存在--false
转换相对路径为物理路径
截取字符枚举值
地址栏传值加密/解密
格式化要显示的内容，主要用于在网页上显示由textarea产生的内容
判断当前访问是否来自非IE浏览器软件
SQL操作类：DBAccess 包含如下方法
代码
Code highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/--> 1 public class DBAccess
{
private static readonly string _connectionString = ConfigurationManager.ConnectionStrings["connectionString"].ToString();
// ExecuteNonQuery
public static int ExecuteNonQuery(string commandText)
{
return Sqlhelper.ExecuteNonQuery(_connectionString, CommandType.Text, commandText);
}
public static int ExecuteNonQuery(string commandText, params SqlParameter[] cmdParameters)
{
return Sqlhelper.ExecuteNonQuery(_connectionString, CommandType.Text, commandText, cmdParameters);
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="procName"></param>
/// <param name="cmdParameters"></param>
/// <returns></returns>
public static int ExecuteNonQueryProc(string procName,params SqlParameter[] cmdParameters)
{
return Sqlhelper.ExecuteNonQuery(_connectionString, CommandType.StoredProcedure, procName, cmdParameters);
}
// ExecuteDataset
public static DataSet ExecuteDataset(string commandText)
{
return Sqlhelper.ExecuteDataset(_connectionString, CommandType.Text, commandText);
}
public static DataSet ExecuteDataset(string commandText, params SqlParameter[] cmdParameters)
{
return Sqlhelper.ExecuteDataset(_connectionString, CommandType.Text, commandText, cmdParameters);
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="procName"></param>
/// <param name="cmdParameters"></param>
/// <returns></returns>
public static DataSet ExecuteDatasetProc(string procName, params SqlParameter[] cmdParameters)
{
return Sqlhelper.ExecuteDataset(_connectionString, CommandType.StoredProcedure, procName, cmdParameters);
}
// ExecuteReader
public static SqlDataReader ExecuteReader(string commandText)
{
return Sqlhelper.ExecuteReader(_connectionString, CommandType.Text, commandText);
}
public static SqlDataReader ExecuteReader(string commandText, params SqlParameter[] cmdParameters)
{
return Sqlhelper.ExecuteReader(_connectionString, CommandType.Text, commandText, cmdParameters);
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="procName"></param>
/// <param name="cmdParameters"></param>
/// <returns></returns>
public static SqlDataReader ExecuteReaderProc(string procName, params SqlParameter[] cmdParameters)
{
return Sqlhelper.ExecuteReader(_connectionString, CommandType.StoredProcedure, procName, cmdParameters);
}
// ExecuteScalar
public static object ExecuteScalar(string commandText)
{
return Sqlhelper.ExecuteScalar(_connectionString, CommandType.Text, commandText);
}
public static object ExecuteScalar(string commandText, params SqlParameter[] cmdParameters)
{
return Sqlhelper.ExecuteScalar(_connectionString, CommandType.Text, commandText, cmdParameters);
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="procName"></param>
/// <param name="cmdParameters"></param>
/// <returns></returns>
public static object ExecuteScalarProc(string procName, params SqlParameter[] cmdParameters)
{
return Sqlhelper.ExecuteScalar(_connectionString, CommandType.StoredProcedure, procName, cmdParameters);
}
}
Json操作类：JsonHelper包含如下方法
代码
Code highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/--> 1 /*
添加引用 System.Runtime.Serialization
添加引用 System.ServiceModel.Web
*/
public static class JsonHelper
{
/// <summary>
/// 格式化成Json字符串
/// </summary>
/// <param name="obj">需要格式化的对象</param>
/// <returns>Json字符串</returns>
public static string ToJson(this object obj)
{
// 首先，当然是JSON序列化
DataContractJsonSerializer serializer = new DataContractJsonSerializer(obj.GetType());
// 定义一个stream用来存发序列化之后的内容
Stream stream = new MemoryStream();
serializer.WriteObject(stream, obj);
// 从头到尾将stream读取成一个字符串形式的数据，并且返回
stream.Position = 0;
StreamReader streamReader = new StreamReader(stream);
return streamReader.ReadToEnd();
}
//DataSetToJson
public static string ToJSON(DataSet dataSet, IDictionary<string, IDictionary<string, string>> details)
{
string json = string.Empty;
if (dataSet != null && dataSet.Tables.Count > 0 && dataSet.Tables[0].Rows.Count > 0)
{
int i = 0, j = 0;
json += "[";
foreach (DataRow row in dataSet.Tables[0].Rows)
{
if (i == 0) { } else { json += ","; }
j = 0;
json += "{";
foreach (DataColumn column in dataSet.Tables[0].Columns)
{
if (j == 0) { } else { json += ","; }
if (details != null && details.ContainsKey(column.ColumnName))
{
IDictionary<string, string> dict = details[column.ColumnName] as IDictionary<string, string>;
if (dict != null && dict.ContainsKey(row[column].ToString()))
json += string.Format("'{0}':'{1}'", column.ColumnName.ToLower(), dict[row[column].ToString()]);
else
json += string.Format("'{0}':'{1}'", column.ColumnName.ToLower(), row[column].ToString());
}
else
json += string.Format("'{0}':'{1}'", column.ColumnName.ToLower(), row[column].ToString());
j++;
}
json += "}";
i++;
}
json += "]";
}
//json = "{"result":"" + json + ""}";
return json;
}
}
网页Messagebox：
代码
Code highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/--> 1 public class MessageBox : System.Web.UI.Page
{
public MessageBox()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
public static void Show(System.Web.UI.Page page, string msg)
{
page.ClientScript.RegisterStartupScript(page.GetType(), "message", "<script language='javascript' defer>alert('" + msg.ToString() + "');</script>");
}
public static void ShowAndRedirect(System.Web.UI.Page page, string msg, string url)
{
StringBuilder Builder = new StringBuilder();
Builder.Append("<script language='javascript' defer>");
Builder.AppendFormat("alert('{0}');", msg);
Builder.AppendFormat("self.location.href='{0}'", url);
Builder.Append("</script>");
page.ClientScript.RegisterStartupScript(page.GetType(), "message", Builder.ToString());
}
/// <summary>
/// 控件点击 消息确认提示框
/// </summary>
/// <param name="page">当前页面指针，一般为this</param>
/// <param name="msg">提示信息</param>
public static void ShowConfirm(System.Web.UI.WebControls.WebControl Control, string msg)
{
//Control.Attributes.Add("onClick","if (!window.confirm('"+msg+"')){return false;}");
Control.Attributes.Add("onclick", "return confirm('" + msg + "');");
}
/// <summary>
/// 信息提示
/// </summary>
/// <param name="mess"></param>
//public virtual void Alert(string mess)
//{
// ClientScript.RegisterStartupScript(this.GetType(), "Alert", "<script language = javascript>alert("提示:" + mess.Replace("
", "") + "")</script>");
//}
}
EXCEL操作类，包含动态EXCEL导入导出方法：
代码
Code highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/--> 1 public class ExcelM:Page
{
/// <summary>
/// 导出Excel Datatable版本
/// </summary>
/// <param name="dt">导出的Datatable</param>
/// <param name="ExcelName">导出EXCEL的名称 不需要要带有扩展名_xls</param>
public static void ExportExcelDT(DataTable dt, string Title)
{
HttpResponse resp = System.Web.HttpContext.Current.Response;
string ExcelName = Title + DateTime.Now.ToString("yyyyMMddHHmmss");
resp.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
resp.AppendHeader("Content-Disposition", "attachment;filename=" + ExcelName + ".xls");
string colHeaders = "", ls_item = "";
DataRow[] myRow = dt.Select();//可以类似dt.Select("id>10")之形式达到数据筛选目的
int i = 0;
int cl = dt.Columns.Count;
//取得数据表各列标题，各标题之间以t分割，最后一个列标题后加回车符
resp.Write("<html><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8" /></head><body><table border=1><tr style="background-color:#000088; color:White;border: Gray 1px solid;text-align:center">");
for (i = 0; i < cl; i++)
{
colHeaders += "<th>" + dt.Columns[i].Caption.ToString() + "</th>";
}
resp.Write(colHeaders + "</tr>");
//向HTTP输出流中写入取得的数据信息
//逐行处理数据
foreach (DataRow row in myRow)
{
//当前行数据写入HTTP输出流，并且置空ls_item以便下行数据
ls_item = "<tr bgcolor=#ABCDC1>";
for (i = 0; i < cl; i++)
{
if (i == (cl - 1))//最后一列，加n
{
ls_item += "<td>" + row[i].ToString() + "</td></tr>";
}
else
{
ls_item += "<td>" + row[i].ToString() + "</td>";
}
}
resp.Write(ls_item);
}
resp.Write("</table></body></html>");
resp.End();
}
public enum eControl { GridView,Repeater}
/// <summary>
/// 控件导出EXCEL
/// </summary>
/// <param name="dataControl">控件名称</param>
/// <param name="dt">要导出的Datatable数据</param>
/// <param name="title">名称</param>
/// <param name="Control">控件类型 GridView or Repeater</param>
public static void ExportExcelDataControl(object dataControl, ref DataTable dt, string title, eControl Control)
{
HttpResponse Response = System.Web.HttpContext.Current.Response;
StringWriter objStringWriter = new StringWriter();
HtmlTextWriter objHtmlTextWriter = new HtmlTextWriter(objStringWriter);
if (Control == eControl.GridView)
{
GridView gvList = (GridView)dataControl;
gvList.DataSource = dt;
gvList.DataBind();
gvList.RenderControl(objHtmlTextWriter);
}
if (Control == eControl.Repeater)
{
Repeater rpList = (Repeater)dataControl;
rpList.DataSource = dt;
rpList.DataBind();
rpList.RenderControl(objHtmlTextWriter);
}
string style = @"<html><head><meta http-equiv=""Content-Type"" content=""text/html; charset=utf-8"" /><style> .text { mso-number-format:@; } </style></head><body>";
string filename = title + DateTime.Now.ToString("yyyyMMddHHmmss");
Response.Clear();
Response.Buffer = true;
Response.AppendHeader("Content-Disposition", "attachment;filename=" + filename + ".xls");
Response.ContentType = "application/ms-excel";
Response.Write(style);
Response.Write(objStringWriter.ToString());
Response.Write("</body></html>");
Response.End();
}
/// <summary>
/// Gridview重载函数
/// </summary>
/// <param name="control"></param>
public override void VerifyRenderingInServerForm(System.Web.UI.Control control) { }
private static readonly string _connectionString = ConfigurationManager.ConnectionStrings["connectionString"].ToString();
/// <summary>
/// EXCEL导入到数据库指定表 需配置XML文件
/// tableName 即将导入的表名
/// OutColumn EXCEL中对应的列名 默认第一行为列名
/// TableColumn 数据库表中对应的列名
/// CType 导入列的数据类型 以数据库中为准
/// Clong 导入列的长度
/// </summary>
/// <param name="filePath">上传EXCEL的路径</param>
/// <param name="erroMsg">错误信息</param>
public static void ExcelToTable(string filePath,out string erroMsg)
{
try
{
erroMsg = "";
DataTable dtExcel = GetExcelFileData(filePath);
//过滤dtExcel 中的空行
for (int i = 0; i < dtExcel.Rows.Count; i++)
{
DataRow dr=dtExcel.Rows[i];
if (dr.IsNull(0) && dr.IsNull(dtExcel.Columns.Count-1))
{
bool isd = true;
for (int j = 1; j < dtExcel.Columns.Count - 1; j++)
{
if (dr.IsNull(j))
continue;
else
{
isd = false;
break;
}
}
if (isd)
dtExcel.Rows[i].Delete();
}
}
List<string> listC = new List<string>();
List<string> tableC = new List<string>();
Dictionary<string,string> Det=new Dictionary<string,string>();
HttpServerUtility server = System.Web.HttpContext.Current.Server;
//此处XML 为网站根目录下的XML
string path = server.MapPath("ImportExcel.xml");
XElement xmldoc = XElement.Load(path);
string tableName = xmldoc.FirstAttribute.Value;
if (UtilityClass.IsNullOrEmpty(tableName))
{
erroMsg = "tableName不能为空！";
return;
}
var qOutColumn = from q in xmldoc.Descendants("OutColumn") select q;
foreach (var q in qOutColumn)
{
listC.Add(q.Value.Trim());
}
var qTableColumn = from q in xmldoc.Descendants("TableColumn") select q;
foreach (var q in qTableColumn)
{
tableC.Add(q.Value.Trim());
}
if (listC.Count != tableC.Count)
{
erroMsg = "OutColumn同TableColumn不是一一对应！";
return;
}
for(int i = 0; i < listC.Count; i++)
{
if (listC[i] != dtExcel.Columns[i].ColumnName.Trim())
{
erroMsg = "OutColumn[" + listC[i] + "]与实际导入列名[" + dtExcel.Columns[i].ColumnName.Trim() + "]不一致";
return;
}
}
for (int i = 0; i < listC.Count; i++)
{
Det.Add(listC[i],tableC[i]);
}
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(_connectionString))
{
for (int i = 0; i < listC.Count; i++)
{
bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(listC[i], Det[listC[i]]));
}
bulkCopy.DestinationTableName = tableName;
bulkCopy.WriteToServer(dtExcel);
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 导入检测EXCEL之后的Datatable
/// EXCEL导入到数据库指定表 需配置XML文件
/// tableName 即将导入的表名
/// OutColumn EXCEL中对应的列名 默认第一行为列名
/// TableColumn 数据库表中对应的列名
/// CType 导入列的数据类型 以数据库中为准
/// Clong 导入列的长度
/// </summary>
/// <param name="dtExcel">传入Datatable</param>
/// <param name="erroMsg">错误信息</param>
/// <param name="isGLNullColumn">是否需要过滤空行</param>
public static void ExcelToTable(DataTable dtExcel, out string erroMsg,bool isGLNullColumn)
{
try
{
erroMsg = "";
//过滤dtExcel 中的空行
if (isGLNullColumn)
{
for (int i = 0; i < dtExcel.Rows.Count; i++)
{
DataRow dr = dtExcel.Rows[i];
if (dr.IsNull(0) && dr.IsNull(dtExcel.Columns.Count - 1))
{
bool isd = true;
for (int j = 1; j < dtExcel.Columns.Count - 1; j++)
{
if (dr.IsNull(j))
continue;
else
{
isd = false;
break;
}
}
if (isd)
dtExcel.Rows[i].Delete();
}
}
}
List<string> listC = new List<string>();
List<string> tableC = new List<string>();
Dictionary<string, string> Det = new Dictionary<string, string>();
HttpServerUtility server = System.Web.HttpContext.Current.Server;
//此处XML 为网站根目录下的XML
string path = server.MapPath("ImportExcel.xml");
XElement xmldoc = XElement.Load(path);
string tableName = xmldoc.FirstAttribute.Value;
if (UtilityClass.IsNullOrEmpty(tableName))
{
erroMsg = "tableName不能为空！";
return;
}
var qOutColumn = from q in xmldoc.Descendants("OutColumn") select q;
foreach (var q in qOutColumn)
{
listC.Add(q.Value.Trim());
}
var qTableColumn = from q in xmldoc.Descendants("TableColumn") select q;
foreach (var q in qTableColumn)
{
tableC.Add(q.Value.Trim());
}
if (listC.Count != tableC.Count)
{
erroMsg = "OutColumn同TableColumn不是一一对应！";
return;
}
for (int i = 0; i < listC.Count; i++)
{
if (listC[i] != dtExcel.Columns[i].ColumnName.Trim())
{
erroMsg = "OutColumn与实际导入列名不一致";
return;
}
}
for (int i = 0; i < listC.Count; i++)
{
Det.Add(listC[i], tableC[i]);
}
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(_connectionString))
{
for (int i = 0; i < listC.Count; i++)
{
bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(listC[i], Det[listC[i]]));
}
bulkCopy.DestinationTableName = tableName;
bulkCopy.WriteToServer(dtExcel);
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 读取Excel
/// </summary>
/// <param name="filePath">EXCEL 路径</param>
/// <returns></returns>
public static DataTable GetExcelFileData(string filePath)
{
OleDbDataAdapter oleAdp = new OleDbDataAdapter();
OleDbConnection oleCon = new OleDbConnection();
string strCon = "Provider=Microsoft.Jet.oleDb.4.0;data source=" + filePath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
try
{
DataTable dt = new DataTable();
oleCon.ConnectionString = strCon;
oleCon.Open();
DataTable table = oleCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string sheetName = table.Rows[0][2].ToString();
string sqlStr = "Select * From [" + sheetName + "]";
oleAdp = new OleDbDataAdapter(sqlStr, oleCon);
oleAdp.Fill(dt);
oleCon.Close();
return dt;
}
catch (Exception ex)
{
throw ex;
}
finally
{
oleAdp = null;
oleCon = null;
}
}
}
类库下载请点这里