asp.net mysql helper_asp.net my sqlHelper

private static readonly stringconnString;staticSqlHelper()

{var conn = ConfigurationManager.ConnectionStrings["connStr"];if (conn != null)

connString=conn.ConnectionString;

}#region MyRegion 查询方法 + GetTable(string sql, CommandType type, params SqlParameter[] pars)

///

///查询方法///

/// sql语句

/// 类型：存储过程 OR 字符串?

/// 参数列表

///

public static DataTable GetTable(string sql, CommandType type, paramsSqlParameter[] pars)

{using (SqlConnection conn = newSqlConnection(connString))

{using (SqlDataAdapter apter = newSqlDataAdapter(sql, conn))

{

apter.SelectCommand.CommandType=type;if (pars != null)

{

apter.SelectCommand.Parameters.AddRange(pars);

}

DataTable da= newDataTable();

apter.Fill(da);returnda;

}

}

}#endregion

#region 查询方法，默认命令类型：字符串 +static DataTable GetTable(string sql, params SqlParameter[] pars)

///

///查询方法，默认类型：字符串///

/// sql语句

/// 参数列表

///

public static DataTable GetTable(string sql, paramsSqlParameter[] pars)

{returnGetTable(sql, CommandType.Text, pars);

}#endregion

#region 查询方法 + static SqlDataReader ExecuteReader(string sql, CommandType type, SqlParameter[] pars)

///

///执行一个查询的T-SQL语句, 返回一个SqlDataReader对象///

/// 要执行的T-SQL语句

/// 命令类型

/// 参数列表

/// SqlDataReader对象

public static SqlDataReader ExecuteReader(stringsql, CommandType type, SqlParameter[] pars)

{using (SqlConnection conn = newSqlConnection(connString))

{using (SqlCommand cmd = newSqlCommand(sql, conn))

{if (pars != null)

{

cmd.Parameters.AddRange(pars);

}

cmd.CommandType=type;if (conn.State !=ConnectionState.Open)

conn.Open();

SqlDataReader reader=cmd.ExecuteReader();returnreader;

}

}

}#endregion

#region 查询方法 默认命令类型：字符串 + static SqlDataReader ExecuteReader(string sql, params SqlParameter[] parameters)

///

///执行一个查询的T-SQL语句, 返回一个SqlDataReader对象///

/// 要执行的T-SQL语句

/// 参数列表

/// SqlDataReader对象

public static SqlDataReader ExecuteReader(string sql, paramsSqlParameter[] parameters)

{returnExecuteReader(sql, CommandType.Text, parameters);

}#endregion

#region 修改、添加、删除方法 +ExecuteNonquery(string sql, CommandType type, params SqlParameter[] pars)

///

///修改、添加、删除方法///

///

///

///

///

public static int ExecuteNonquery(string sql, CommandType type, paramsSqlParameter[] pars)

{using (SqlConnection conn = newSqlConnection(connString))

{using (SqlCommand cmd = newSqlCommand(sql, conn))

{

cmd.CommandType=type;if (pars != null)

cmd.Parameters.AddRange(pars);if (conn.State !=ConnectionState.Open)

conn.Open();returncmd.ExecuteNonQuery();

}

}

}#endregion

#region 修改、添加、删除方法，默认字符串 + static int ExecuteNonquery(string sql, params SqlParameter[] pars)

///

///修改、添加、删除方法，默认字符串///

///

///

///

public static int ExecuteNonquery(string sql, paramsSqlParameter[] pars)

{returnExecuteNonquery(sql, CommandType.Text, pars);

}#endregion

#region ExecuteScalar +static object ExecuteScalar(string cmdText, CommandType type, params SqlParameter[] parameters)

///

///执行一个查询的T-SQL语句，返回第一行第一列的结果///

/// 要执行的T-SQL语句

/// 命令类型

/// 参数列表

///

/// 返回第一行第一列的数据

public static object ExecuteScalar(string cmdText, CommandType type, paramsSqlParameter[] parameters)

{using (SqlConnection conn = newSqlConnection(connString))

{using (SqlCommand cmd = newSqlCommand(cmdText, conn))

{if (parameters != null)

{

cmd.Parameters.Clear();

cmd.Parameters.AddRange(parameters);

}

cmd.CommandType=type;try{

conn.Open();object res =cmd.ExecuteScalar();

cmd.Parameters.Clear();returnres;

}catch(System.Data.SqlClient.SqlException e)

{

conn.Close();throwe;

}

}

}

}#endregion

#region ExecuteScalar +static object ExecuteScalar(string cmdText, params SqlParameter[] parameters)

///

///执行一个查询的T-SQL语句，返回第一行第一列的结果///

/// 要执行的T-SQL语句

/// 参数列表

///

/// 返回第一行第一列的数据

public static object ExecuteScalar(string cmdText, paramsSqlParameter[] parameters)

{returnExecuteScalar(cmdText, CommandType.Text, parameters);

}#endregion

#region 公共方法

#region 将一个SqlDataReader转换为实体类对象 + static TEntity MapEntity(SqlDataReader reader) where TEntity : class, new()

///

///将一个SqlDataReader转换为实体类对象///

/// 实体类型

/// 当前指向的reader

/// 实体对象

public static TEntity MapEntity(SqlDataReader reader) where TEntity : class, new()

{try{var props = typeof(TEntity).GetProperties();var entity = newTEntity();foreach (var prop inprops)

{if(prop.CanWrite)

{try{var index =reader.GetOrdinal(prop.Name);var data =reader.GetValue(index);if (data !=DBNull.Value)

{

prop.SetValue(entity, Convert.ChangeType(data, prop.PropertyType),null);

}

}catch(IndexOutOfRangeException)

{continue;

}

}

}returnentity;

}catch{return null;

}

}#endregion

#region 将dataTable转换为实体 + static List MapEntity(DataTable table) where TEntity : class, new()

///

///将dataTable转换为实体///

///

///

///

public static List MapEntity(DataTable table) where TEntity : class, new()

{try{

List Entitys = new List();if (table.Rows.Count == 0)returnEntitys;var props = typeof(TEntity).GetProperties();foreach (DataRow dr intable.Rows)

{var entity = newTEntity();foreach (var prop inprops)

{if (prop.CanWrite &&table.Columns.Contains(prop.Name))

{var value =dr[prop.Name];if (value !=DBNull.Value)

{

prop.SetValue(entity, Convert.ChangeType(value, prop.PropertyType),null);

}

}

}

Entitys.Add(entity);

}returnEntitys;

}catch{return null;

}

}#endregion

#endregion

#region 判断某张表的某个字段是否存在某个值 + static bool ColumnExistsValue(string table, string column, string value)

///

///判断某张表的某个字段是否存在某个值///

/// 表名称

/// 列名称

/// 要判断的值

/// 是否存在

public static bool ColumnExistsValue(string table, string column, stringvalue)

{string sql = "SELECT count(1) FROM [" + table + "] WHERE [" + column + "] =@Value;";object res = ExecuteScalar(sql, new SqlParameter("@Value", value));if (res == null)

{return false;

}return Convert.ToInt32(res) > 0;

}#endregion

#region 构建分页查询

///

///构建分页的T-SQL语句///

///

///

///

///

public static string CreateQuerySql(int? page, int?rows, StringBuilder sbSql)

{int? iBeginIndex = 1;int? iEndIndex = 1;string strPageWhere = string.Empty;

StringBuilder sbSqlPager= null;try{

sbSqlPager= newStringBuilder();

page= page == null ? 1: page;

rows= rows == null ? 20: rows;

iBeginIndex= (page - 1) * rows + 1;

iEndIndex= (page) *rows;

strPageWhere= "where t.SN between" + iBeginIndex + "and" +iEndIndex;

sbSqlPager.AppendLine("select * from (");

sbSqlPager.AppendLine(sbSql.ToString());

sbSqlPager.AppendLine(")t" +strPageWhere);

}catch{

sbSqlPager=sbSql;

}returnsbSqlPager.ToString();

}#endregion