- 浏览: 272102 次
文章分类
最新评论
-
panxiaochao:
很有帮助啊!
[030] 微信公众帐号开发教程第6篇-文本消息的内容长度限制揭秘 -
78339810:
我们这些新手向你学习了!
[028] 微信公众帐号开发教程第4篇-消息及消息处理工具的封装 -
78339810:
楼主给力,继续更新!
[028] 微信公众帐号开发教程第4篇-消息及消息处理工具的封装 -
跨省少年:
已关注,学习了
[024] 欢迎大家关注我的微信公众帐号小q机器人(xiaoqrobot) -
10209019:
楼主,能发个源码来学习下吗?如果可以的话发这个邮箱, 7759 ...
[028] 微信公众帐号开发教程第4篇-消息及消息处理工具的封装
ASP.NET好用的MySql操作类
这个操作类是在网上淘的,找了好多份,个人觉得这个是最好的,拿出来跟大家分享
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Text; using System.Data; using System.Collections; using MySql.Data.Common; using MySql.Data.MySqlClient; using MySql.Data.Types; using System.Configuration; using System.IO; namespace Tools.Models { /// <summary> /// when use mysql database application this class /// Generic database access code /// this class that is a abstract,which does not allow instantiation, the application can directly call it /// </summary> public abstract class MYSQLHelper { //Get the database connectionstring, which are static variables and readonly, all project documents can be used directly, but can not modify it //the database connectionString //public static readonly string connectionStringManager = ConfigurationManager.ConnectionStrings["MYSQLConnectionString"].ConnectionString; public static string ConnectionStringManager { get { return connectionStringManager; } } //This connectionString for the local test public static readonly string connectionStringManager = ConfigurationManager.ConnectionStrings["MySQLConnString"].ConnectionString; //hashtable to store the parameter information, the hash table can store any type of argument //Here the hashtable is static types of static variables, since it is static, that is a definition of global use. //All parameters are using this hash table, how to ensure that others in the change does not affect their time to read it //Before ,the method can use the lock method to lock the table, does not allow others to modify.when it has readed then unlocked table. //Now .NET provides a HashTable's Synchronized methods to achieve the same function, no need to manually lock, completed directly by the system framework private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable()); /// <summary> /// Execute a SqlCommand command that does not return value, by appointed and specified connectionstring /// The parameter list using parameters that in array forms /// </summary> /// <remarks> /// Usage example: /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, /// "PublishOrders", new MySqlParameter("@prodid", 24)); /// </remarks> /// <param name="connectionString">a valid database connectionstring</param> /// <param name="cmdType">MySqlCommand command type (stored procedures, T-SQL statement, and so on.) </param> /// <param name="cmdText">stored procedure name or T-SQL statement</param> /// <param name="commandParameters">MySqlCommand to provide an array of parameters used in the list</param> /// <returns>Returns a value that means number of rows affected/returns> public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, 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> /// Execute a SqlCommand command that does not return value, by appointed and specified connectionstring /// The parameter list using parameters that in array forms /// </summary> /// <remarks> /// Usage example: /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, /// "PublishOrders", new MySqlParameter("@prodid", 24)); /// </remarks> /// <param name="cmdType">MySqlCommand command type (stored procedures, T-SQL statement, and so on.) </param> /// <param name="connectionString">a valid database connectionstring</param> /// <param name="cmdText">stored procedure name or T-SQL statement</param> /// <param name="commandParameters">MySqlCommand to provide an array of parameters used in the list</param> /// <returns>Returns true or false </returns> public static bool ExecuteNonQuery(CommandType cmdType, string connectionString, string cmdText, params MySqlParameter[] commandParameters) { MySqlCommand cmd = new MySqlCommand(); using (MySqlConnection conn = new MySqlConnection(connectionString)) { PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); try { int val = cmd.ExecuteNonQuery(); return true; } catch { return false; } finally { cmd.Parameters.Clear(); } } } /// <summary> /// Execute a SqlCommand command that does not return value, by appointed and specified connectionstring /// Array of form parameters using the parameter list /// </summary> /// <param name="conn">connection</param> /// <param name="cmdType">MySqlCommand command type (stored procedures, T-SQL statement, and so on.)</param> /// <param name="cmdText">stored procedure name or T-SQL statement</param> /// <param name="commandParameters">MySqlCommand to provide an array of parameters used in the list</param> /// <returns>Returns a value that means number of rows affected</returns> public static int ExecuteNonQuery(MySqlConnection conn, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters) { MySqlCommand cmd = new MySqlCommand(); PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; } /// <summary> /// Execute a SqlCommand command that does not return value, by appointed and specified connectionstring /// Array of form parameters using the parameter list /// </summary> /// <param name="conn">sql Connection that has transaction</param> /// <param name="cmdType">SqlCommand command type (stored procedures, T-SQL statement, and so on.)</param> /// <param name="cmdText">stored procedure name or T-SQL statement</param> /// <param name="commandParameters">MySqlCommand to provide an array of parameters used in the list</param> /// <returns>Returns a value that means number of rows affected </returns> public static int ExecuteNonQuery(MySqlTransaction trans, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters) { MySqlCommand cmd = new MySqlCommand(); PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; } /// <summary> /// Call method of sqldatareader to read data /// </summary> /// <param name="connectionString">connectionstring</param> /// <param name="cmdType">command type, such as using stored procedures: CommandType.StoredProcedure</param> /// <param name="cmdText">stored procedure name or T-SQL statement</param> /// <param name="commandParameters">parameters</param> /// <returns>SqlDataReader type of data collection</returns> public static MySqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters) { MySqlCommand cmd = new MySqlCommand(); MySqlConnection conn = new MySqlConnection(connectionString); // we use a try/catch here because if the method throws an exception we want to // close the connection throw code, because no datareader will exist, hence the // commandBehaviour.CloseConnection will not work try { PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); MySqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); cmd.Parameters.Clear(); return rdr; } catch { conn.Close(); throw; } } /// <summary> /// use the ExectueScalar to read a single result /// </summary> /// <param name="connectionString">connectionstring</param> /// <param name="cmdType">command type, such as using stored procedures: CommandType.StoredProcedure</param> /// <param name="cmdText">stored procedure name or T-SQL statement</param> /// <param name="commandParameters">parameters</param> /// <returns>a value in object type</returns> public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, 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; } } public static DataSet GetDataSet(string connectionString, string cmdText, params MySqlParameter[] commandParameters) { DataSet retSet = new DataSet(); using (MySqlDataAdapter msda = new MySqlDataAdapter(cmdText, connectionString)) { msda.Fill(retSet); } return retSet; } /// <summary> /// cache the parameters in the HashTable /// </summary> /// <param name="cacheKey">hashtable key name</param> /// <param name="commandParameters">the parameters that need to cached</param> public static void CacheParameters(string cacheKey, params MySqlParameter[] commandParameters) { parmCache[cacheKey] = commandParameters; } /// <summary> /// get parameters in hashtable by cacheKey /// </summary> /// <param name="cacheKey">hashtable key name</param> /// <returns>the parameters</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> ///Prepare parameters for the implementation of the command /// </summary> /// <param name="cmd">mySqlCommand command</param> /// <param name="conn">database connection that is existing</param> /// <param name="trans">database transaction processing </param> /// <param name="cmdType">SqlCommand command type (stored procedures, T-SQL statement, and so on.) </param> /// <param name="cmdText">Command text, T-SQL statements such as Select * from Products</param> /// <param name="cmdParms">return the command that has parameters</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 parm in cmdParms) cmd.Parameters.Add(parm); } #region parameters /// <summary> /// Set parameters /// </summary> /// <param name="ParamName">parameter name</param> /// <param name="DbType">data type</param> /// <param name="Size">type size</param> /// <param name="Direction">input or output</param> /// <param name="Value">set the value</param> /// <returns>Return parameters that has been assigned</returns> public static MySqlParameter CreateParam(string ParamName, MySqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value) { MySqlParameter param; if (Size > 0) { param = new MySqlParameter(ParamName, DbType, Size); } else { param = new MySqlParameter(ParamName, DbType); } param.Direction = Direction; if (!(Direction == ParameterDirection.Output && Value == null)) { param.Value = Value; } return param; } /// <summary> /// set Input parameters /// </summary> /// <param name="ParamName">parameter names, such as:@ id </param> /// <param name="DbType">parameter types, such as: MySqlDbType.Int</param> /// <param name="Size">size parameters, such as: the length of character type for the 100</param> /// <param name="Value">parameter value to be assigned</param> /// <returns>Parameters</returns> public static MySqlParameter CreateInParam(string ParamName, MySqlDbType DbType, int Size, object Value) { return CreateParam(ParamName, DbType, Size, ParameterDirection.Input, Value); } /// <summary> /// Output parameters /// </summary> /// <param name="ParamName">parameter names, such as:@ id</param> /// <param name="DbType">parameter types, such as: MySqlDbType.Int</param> /// <param name="Size">size parameters, such as: the length of character type for the 100</param> /// <param name="Value">parameter value to be assigned</param> /// <returns>Parameters</returns> public static MySqlParameter CreateOutParam(string ParamName, MySqlDbType DbType, int Size) { return CreateParam(ParamName, DbType, Size, ParameterDirection.Output, null); } /// <summary> /// Set return parameter value /// </summary> /// <param name="ParamName">parameter names, such as:@ id</param> /// <param name="DbType">parameter types, such as: MySqlDbType.Int</param> /// <param name="Size">size parameters, such as: the length of character type for the 100</param> /// <param name="Value">parameter value to be assigned<</param> /// <returns>Parameters</returns> public static MySqlParameter CreateReturnParam(string ParamName, MySqlDbType DbType, int Size) { return CreateParam(ParamName, DbType, Size, ParameterDirection.ReturnValue, null); } /// <summary> /// Generate paging storedProcedure parameters /// </summary> /// <param name="CurrentIndex">CurrentPageIndex</param> /// <param name="PageSize">pageSize</param> /// <param name="WhereSql">query Condition</param> /// <param name="TableName">tableName</param> /// <param name="Columns">columns to query</param> /// <param name="Sort">sort</param> /// <returns>MySqlParameter collection</returns> public static MySqlParameter[] GetPageParm(int CurrentIndex, int PageSize, string WhereSql, string TableName, string Columns, Hashtable Sort) { MySqlParameter[] parm = { MYSQLHelper.CreateInParam("@CurrentIndex", MySqlDbType.Int32, 4, CurrentIndex ), MYSQLHelper.CreateInParam("@PageSize", MySqlDbType.Int32, 4, PageSize ), MYSQLHelper.CreateInParam("@WhereSql", MySqlDbType.VarChar, 2500, WhereSql ), MYSQLHelper.CreateInParam("@TableName", MySqlDbType.VarChar, 20, TableName ), MYSQLHelper.CreateInParam("@Column", MySqlDbType.VarChar, 2500, Columns ), MYSQLHelper.CreateInParam("@Sort", MySqlDbType.VarChar, 50, GetSort(Sort) ), MYSQLHelper.CreateOutParam("@RecordCount", MySqlDbType.Int32, 4 ) }; return parm; } /// <summary> /// Statistics data that in table /// </summary> /// <param name="TableName">table name</param> /// <param name="Columns">Statistics column</param> /// <param name="WhereSql">conditions</param> /// <returns>Set of parameters</returns> public static MySqlParameter[] GetCountParm(string TableName, string Columns, string WhereSql) { MySqlParameter[] parm = { MYSQLHelper.CreateInParam("@TableName", MySqlDbType.VarChar, 20, TableName ), MYSQLHelper.CreateInParam("@CountColumn", MySqlDbType.VarChar, 20, Columns ), MYSQLHelper.CreateInParam("@WhereSql", MySqlDbType.VarChar, 250, WhereSql ), MYSQLHelper.CreateOutParam("@RecordCount", MySqlDbType.Int32, 4 ) }; return parm; } /// <summary> /// Get the sql that is Sorted /// </summary> /// <param name="sort"> sort column and values</param> /// <returns>SQL sort string</returns> private static string GetSort(Hashtable sort) { string str = ""; int i = 0; if (sort != null && sort.Count > 0) { foreach (DictionaryEntry de in sort) { i++; str += de.Key + " " + de.Value; if (i != sort.Count) { str += ","; } } } return str; } /// <summary> /// execute a trascation include one or more sql sentence(author:donne yin) /// </summary> /// <param name="connectionString"></param> /// <param name="cmdType"></param> /// <param name="cmdTexts"></param> /// <param name="commandParameters"></param> /// <returns>execute trascation result(success: true | fail: false)</returns> public static bool ExecuteTransaction(string connectionString, CommandType cmdType, string[] cmdTexts, params MySqlParameter[][] commandParameters) { MySqlConnection myConnection = new MySqlConnection(connectionString); //get the connection object myConnection.Open(); //open the connection MySqlTransaction myTrans = myConnection.BeginTransaction(); //begin a trascation MySqlCommand cmd = new MySqlCommand(); cmd.Connection = myConnection; cmd.Transaction = myTrans; try { for (int i = 0; i < cmdTexts.Length; i++) { PrepareCommand(cmd, myConnection, null, cmdType, cmdTexts[i], commandParameters[i]); cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); } myTrans.Commit(); } catch { myTrans.Rollback(); return false; } finally { myConnection.Close(); } return true; } #endregion } }
简单使用方法例子:
插入或者更新
public String Reg(string email, string password, string nickname) { String ip = CommonFunction.getIP(); MySqlCommand sqlcom = new MySqlCommand(); sqlcom.CommandText = "INSERT INTO `common_members` (`uid`, `email`, `password`, `privilege`, `emailstatus`, `regtime`, `regip`, `lastloginip`, `lastlogintime`, `salt`, `nickname`, `avater`, `token`)" + " VALUES(NULL, @email, @password, 1, 0,NOW(), @regip, @loginip, NOW(), 'asda22', @nickname, NULL, '');"; MySqlParameter[] commandParameters = new MySqlParameter[]{ new MySqlParameter("@email",email), new MySqlParameter("@password",password), new MySqlParameter("@nickname",nickname), new MySqlParameter("@regip",ip), new MySqlParameter("@loginip",ip) }; bool res = MYSQLHelper.ExecuteNonQuery(CommandType.Text, MYSQLHelper.ConnectionStringManager, sqlcom.CommandText, commandParameters); if (res == true) { return "1"; } else { return "0"; } }
查询:
public string Login(string email,string password){ MySqlCommand sqlcom = new MySqlCommand(); sqlcom.CommandText = "select * from common_members where email =@email"; MySqlParameter commandParameters = new MySqlParameter("@email", email); MySqlDataReader reader = MYSQLHelper.ExecuteReader(MYSQLHelper.ConnectionStringManager, CommandType.Text, sqlcom.CommandText, commandParameters); if (reader.Read() == true) { string pwd = reader["password"].ToString(); if (pwd.Equals(password)) { return "1"; }else { return "0"; } } else { return "-1"; } }
相关推荐
和sqlhelper一样使用的mysql数据库操作类
ASP.NET操作MySQL数据库的类文件和DLL文件。
主要介绍了一个ASP.NET的MYSQL的数据库操作类自己封装的,在数据库操作类中的连接字符串中记得加上charset=utf8需要的朋友可以参考下
本文件纯属个人学习参考自编,如有雷同纯属巧合! 转载请注明原编者。
Asp.net手动绑定数据(分页、编辑、删除、加控件等操作) Asp.net中My97DatePicker4.2日期的使用 Asp.net中DataList控件添加删除 Asp.net获取请求的用户信息IP地址 获取世界IP地址库显所在城市信息 ...
Asp.net手动绑定数据(分页、编辑、删除、加控件等操作) Asp.net中My97DatePicker4.2日期的使用 Asp.net中DataList控件添加删除 Asp.net获取请求的用户信息IP地址 获取世界IP地址库显所在城市信息 MySQL...
MySql.Data.dll操作文件,可以是asp.net更方便的连接mysql数据库
为ASP.NET封装的SQL数据库访问类 DataTable.Select方法的性能问题 .NET 2.0里使用强类型数据创建多层应用 ADO.NET实用经验无保留曝光 有了System.Data.IDataReader,一切皆成数据 理解DataSet的数据缓存机制 存储过程...
数据库操作类: 支持 SQLServer mysql sqlite Sybase Oracle等DB 数据库操作类 包括执行SQL或者存储过程,返回DataSet、DataTable等功能 完全支持存储过程和参数调用 javascriptClassLibrary 包括一些JavaScript 类...
第5章:详细的介绍了ASP.NET应用程序中提供的控件,控件为开发人员提供了高效的应用程序开发方法,开发人员无需专业的知识就能够实现复杂的应用操作。 第6章:着重讲解了ASP.NET高级控件中的登陆控件的使用,并讲解...
代码如下:using System;using System.Collections;using System.Collections.Specialized;using System.Data;using System.Data.Oracle... /// /// 数据访问抽象基础类 /// /// </summary>public class DBBase{
9.6 ASP.NET数据库操作实例 9.6.1 制作用户界面(UI) 9.6.2 使用GridView显示、删除、修改数据 9.6.3 使用DataList显示数据 9.6.4 DataList分页实现 9.6.5 使用SQLHelper操作数据库 9.7 小结 第10章 访问其他数据源...
一套基于ASP.NET MVC+EF6+Bootstrap开发出来的框架源代码! 采用主流框架,容易上手,简单易学,学习成本低。可完全实现二次开发、基本满足80%项目需求。 可以帮助解决.NET项目70%的重复工作,让开发更多关注业务...
9.6 ASP.NET 数据库操作实例 9.6.1 制作用户界面(UI) 9.6.2 使用GridView 显示、删除、修改数据 9.6.3 使用DataList 显示数据 9.6.4 DataList 分页实现 9.6.5 使用SQLHelper 操作数据库 9.7 小结 第 10 章访问其他...
9.6 ASP.NET数据库操作实例 9.6.1 制作用户界面(UI) 9.6.2 使用GridView显示、删除、修改数据 9.6.3 使用DataList显示数据 9.6.4 DataList分页实现 9.6.5 使用SQLHelper操作数据库 9.7 小结 第10章 访问其他数据源...
第5章:详细的介绍了ASP.NET应用程序中提供的控件,控件为开发人员提供了高效的应用程序开发方法,开发人员无需专业的知识就能够实现复杂的应用操作。 第6章:着重讲解了ASP.NET高级控件中的登陆控件的使用,并讲解...
9.6 ASP.NET数据库操作实例 9.6.1 制作用户界面(UI) 9.6.2 使用GridView显示、删除、修改数据 9.6.3 使用DataList显示数据 9.6.4 DataList分页实现 9.6.5 使用SQLHelper操作数据库 9.7 小结 第10章 访问其他数据源...
9.6 ASP.NET数据库操作实例 9.6.1 制作用户界面(UI) 9.6.2 使用GridView显示、删除、修改数据 9.6.3 使用DataList显示数据 9.6.4 DataList分页实现 9.6.5 使用SQLHelper操作数据库 9.7 小结 第10章 访问其他数据源...