针对的MSSQL数据库,也不会用存储过程。。。
有没有些优秀的例子可以参考下哇
初学者请多多指教
23 个解决方案
#1
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.Odbc;
/// <summary>
/// DBtools 的摘要说明
/// </summary>
public class DBtools
{
#region 新方法
public DBtools()
{
}
/// <summary>
/// 实例化数据库连接
/// </summary>
/// <param name="type">1为SQT数据库连接(ConfigurationManager.AppSettings["ConnectionStringSQT"]),2为UC数据库连接(ConfigurationManager.AppSettings["ConnectionStringUC"])</param>
public DBtools(int type)
{
switch (type)
{
case 1:
connectionString = ConfigurationManager.AppSettings["ConnectionStringSQT"];
break;
case 2:
connectionString = ConfigurationManager.AppSettings["ConnectionStringUC"];
break;
default:
break;
}
}
/// <summary>
/// 实例化数据库连接
/// </summary>
/// <param name="constr">直接传入数据库连接字符串</param>
public DBtools(string constr)
{
connectionString = constr;
}
public void ConnConnect()
{
try
{
conn.ConnectionString = connectionString;
conn.Open();
}
catch
{
throw;
}
}
//数据库连接
OdbcConnection conn = new OdbcConnection();
//数据库连接字符串(web.config来配置),可以动态更改connectionString支持多数据库.
public string connectionString = null;
#region 常用方法
/// <summary>
/// 得到最大值
/// </summary>
/// <param name="FieldName"></param>
/// <param name="TableName"></param>
/// <returns></returns>
public int GetMaxID(string FieldName, string TableName)
{
string strsql = "select max(" + FieldName + ")+1 from " + TableName;
object obj = GetSingle(strsql);
if (obj == null)
{
return 1;
}
else
{
return int.Parse(obj.ToString());
}
}
/// <summary>
/// 是否存在
/// </summary>
/// <param name="strSql"></param>
/// <returns></returns>
public bool Exists(string strSql)
{
object obj = GetSingle(strSql);
int cmdresult;
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
cmdresult = 0;
}
else
{
cmdresult = int.Parse(obj.ToString());
}
if (cmdresult == 0)
{
return false;
}
else
{
return true;
}
}
/// <summary>
/// 执行一条计算查询结果语句,返回查询结果(object)。
/// </summary>
/// <param name="SQLString">计算查询结果语句</param>
/// <returns>查询结果(object)</returns>
public object GetSingle(string SQLString)
{
ConnConnect();
object obj = null;
OdbcCommand cmd = new OdbcCommand();
try
{
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = SQLString;
obj = cmd.ExecuteScalar();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
obj = null;
}
}
catch
{
throw;
}
finally
{
cmd.Dispose();
ConnClose();
}
return obj;
}
/// <summary>
/// 执行SQL语句,返回影响的记录数
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <returns>影响的记录数</returns>
public int ExecuteSql(string SQLString)
{
ConnConnect();
int num = 0;
OdbcCommand cmd = new OdbcCommand();
try
{
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = SQLString;
num = cmd.ExecuteNonQuery();
}
catch
{
throw;
}
finally
{
cmd.Dispose();
ConnClose();
}
return num;
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="procname"></param>
public DataSet ExecProc(string procname, OdbcParameter[] param)
{
ConnConnect();
OdbcCommand cmd = new OdbcCommand();
OdbcDataAdapter ss = new OdbcDataAdapter();
DataSet ds = new DataSet();
try
{
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = procname;
for (int i = 0; i < param.Length; i++)
{
cmd.Parameters.Add(param[i]);
}
ss.SelectCommand = cmd;
ss.Fill(ds);
}
catch
{
throw;
}
finally
{
cmd.Dispose();
ss.Dispose();
ConnClose();
}
return ds;
}
/// <summary>
/// 执行查询语句,返回DataSet
/// </summary>
/// <param name="SQLString">查询语句</param>
/// <returns>DataSet</returns>
public DataSet Query(string SQLString)
{
ConnConnect();
DataSet ds = new DataSet();
OdbcCommand cmd = new OdbcCommand();
OdbcDataAdapter ss = new OdbcDataAdapter();
try
{
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = SQLString;
ss.SelectCommand = cmd;
ss.Fill(ds);
}
catch
{
throw;
}
finally
{
cmd.Dispose();
ss.Dispose();
ConnClose();
}
return ds;
}
/// <summary>
/// 关闭数据库连接
/// </summary>
public void ConnClose()
{
try
{
conn.Close();
conn.Dispose();
}
catch
{ }
}
#endregion
#endregion
}
#2
请查看PETSHOP4里面的SQLHelper类,然后自己写一个自用的
#3
...
纠结,ADO操作MSSQL 你不会?
纠结,ADO操作MSSQL 你不会?
#4
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.Odbc;
/// <summary>
/// DBtools 的摘要说明
/// </summary>
public class DBtools
{
#region 新方法
public DBtools()
{
}
/// <summary>
/// 实例化数据库连接
/// </summary>
/// <param name="type">1为SQT数据库连接(ConfigurationManager.AppSettings["ConnectionStringSQT"]),2为UC数据库连接(ConfigurationManager.AppSettings["ConnectionStringUC"])</param>
public DBtools(int type)
{
switch (type)
{
case 1:
connectionString = ConfigurationManager.AppSettings["ConnectionStringSQT"];
break;
case 2:
connectionString = ConfigurationManager.AppSettings["ConnectionStringUC"];
break;
default:
break;
}
}
/// <summary>
/// 实例化数据库连接
/// </summary>
/// <param name="constr">直接传入数据库连接字符串</param>
public DBtools(string constr)
{
connectionString = constr;
}
public void ConnConnect()
{
try
{
conn.ConnectionString = connectionString;
conn.Open();
}
catch
{
throw;
}
}
//数据库连接
OdbcConnection conn = new OdbcConnection();
//数据库连接字符串(web.config来配置),可以动态更改connectionString支持多数据库.
public string connectionString = null;
#region 常用方法
/// <summary>
/// 得到最大值
/// </summary>
/// <param name="FieldName"></param>
/// <param name="TableName"></param>
/// <returns></returns>
public int GetMaxID(string FieldName, string TableName)
{
string strsql = "select max(" + FieldName + ")+1 from " + TableName;
object obj = GetSingle(strsql);
if (obj == null)
{
return 1;
}
else
{
return int.Parse(obj.ToString());
}
}
/// <summary>
/// 是否存在
/// </summary>
/// <param name="strSql"></param>
/// <returns></returns>
public bool Exists(string strSql)
{
object obj = GetSingle(strSql);
int cmdresult;
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
cmdresult = 0;
}
else
{
cmdresult = int.Parse(obj.ToString());
}
if (cmdresult == 0)
{
return false;
}
else
{
return true;
}
}
/// <summary>
/// 执行一条计算查询结果语句,返回查询结果(object)。
/// </summary>
/// <param name="SQLString">计算查询结果语句</param>
/// <returns>查询结果(object)</returns>
public object GetSingle(string SQLString)
{
ConnConnect();
object obj = null;
OdbcCommand cmd = new OdbcCommand();
try
{
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = SQLString;
obj = cmd.ExecuteScalar();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
obj = null;
}
}
catch
{
throw;
}
finally
{
cmd.Dispose();
ConnClose();
}
return obj;
}
/// <summary>
/// 执行SQL语句,返回影响的记录数
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <returns>影响的记录数</returns>
public int ExecuteSql(string SQLString)
{
ConnConnect();
int num = 0;
OdbcCommand cmd = new OdbcCommand();
try
{
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = SQLString;
num = cmd.ExecuteNonQuery();
}
catch
{
throw;
}
finally
{
cmd.Dispose();
ConnClose();
}
return num;
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="procname"></param>
public DataSet ExecProc(string procname, OdbcParameter[] param)
{
ConnConnect();
OdbcCommand cmd = new OdbcCommand();
OdbcDataAdapter ss = new OdbcDataAdapter();
DataSet ds = new DataSet();
try
{
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = procname;
for (int i = 0; i < param.Length; i++)
{
cmd.Parameters.Add(param[i]);
}
ss.SelectCommand = cmd;
ss.Fill(ds);
}
catch
{
throw;
}
finally
{
cmd.Dispose();
ss.Dispose();
ConnClose();
}
return ds;
}
/// <summary>
/// 执行查询语句,返回DataSet
/// </summary>
/// <param name="SQLString">查询语句</param>
/// <returns>DataSet</returns>
public DataSet Query(string SQLString)
{
ConnConnect();
DataSet ds = new DataSet();
OdbcCommand cmd = new OdbcCommand();
OdbcDataAdapter ss = new OdbcDataAdapter();
try
{
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = SQLString;
ss.SelectCommand = cmd;
ss.Fill(ds);
}
catch
{
throw;
}
finally
{
cmd.Dispose();
ss.Dispose();
ConnClose();
}
return ds;
}
/// <summary>
/// 关闭数据库连接
/// </summary>
public void ConnClose()
{
try
{
conn.Close();
conn.Dispose();
}
catch
{ }
}
#endregion
#endregion
}
#5
using System;
using System.Data;
using MySql.Data.MySqlClient;
namespace Niunan.BYLW.Utility
{
/// <summary>对MYSQL数据库的操作类
///
/// </summary>
public class MYSQLHelper
{
private MySqlConnection conn = null;
private MySqlCommand cmd = null;
private MySqlDataReader sdr = null;
public MYSQLHelper()
{
//string connStr = WebConfigurationManager.ConnectionStrings["connStr"].ToString();
string connStr = "server=localhost;database=test;uid=root;pwd=123456;charset=utf8";
conn = new MySqlConnection(connStr);
}
/// <summary>创建Command对象
///
/// </summary>
/// <param name="sql">SQL语句</param>
public void CreateCommand(string sql)
{
conn.Open();
cmd = new MySqlCommand(sql, conn);
}
/// <summary>添加参数
///
/// </summary>
/// <param name="paramName">参数名称</param>
/// <param name="value">值</param>
public void AddParameter(string paramName, object value)
{
cmd.Parameters.Add(new MySqlParameter(paramName, value));
}
/// <summary>执行不带参数的增删改SQL语句
///
/// </summary>
/// <param name="cmdText">增删改SQL语句</param>
/// <param name="ct">命令类型</param>
/// <returns></returns>
public bool ExecuteNonQuery()
{
int res;
try
{
res = cmd.ExecuteNonQuery();
if (res > 0)
{
return true;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
return false;
}
/// <summary>执行查询SQL语句
///
/// </summary>
/// <param name="cmdText">查询SQL语句</param>
/// <returns></returns>
public DataTable ExecuteQuery()
{
DataTable dt = new DataTable();
using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
dt.Load(sdr);
}
return dt;
}
/// <summary>返回查询SQL语句查询出的结果的第一行第一列的值
///
/// </summary>
/// <returns></returns>
public string ExecuteScalar()
{
string res = "";
try
{
object obj = cmd.ExecuteScalar();
if (obj != null)
{
res = obj.ToString();
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
return res;
}
}
}
/**
* 作者:牛腩
* 创建时间:2010年3月7日17时35分
* 类说明:对MYSQL数据库的操作类
*/
using System;
using System.Data;
using MySql.Data.MySqlClient;
namespace Niunan.BYLW.Utility
{
/// <summary>对MYSQL数据库的操作类
///
/// </summary>
public class MYSQLHelper
{
private MySqlConnection conn = null;
private MySqlCommand cmd = null;
private MySqlDataReader sdr = null;
public MYSQLHelper()
{
//string connStr = WebConfigurationManager.ConnectionStrings["connStr"].ToString();
string connStr = "server=localhost;database=test;uid=root;pwd=123456;charset=utf8";
conn = new MySqlConnection(connStr);
}
/// <summary>创建Command对象
///
/// </summary>
/// <param name="sql">SQL语句</param>
public void CreateCommand(string sql)
{
conn.Open();
cmd = new MySqlCommand(sql, conn);
}
/// <summary>添加参数
///
/// </summary>
/// <param name="paramName">参数名称</param>
/// <param name="value">值</param>
public void AddParameter(string paramName, object value)
{
cmd.Parameters.Add(new MySqlParameter(paramName, value));
}
/// <summary>执行不带参数的增删改SQL语句
///
/// </summary>
/// <param name="cmdText">增删改SQL语句</param>
/// <param name="ct">命令类型</param>
/// <returns></returns>
public bool ExecuteNonQuery()
{
int res;
try
{
res = cmd.ExecuteNonQuery();
if (res > 0)
{
return true;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
return false;
}
/// <summary>执行查询SQL语句
///
/// </summary>
/// <param name="cmdText">查询SQL语句</param>
/// <returns></returns>
public DataTable ExecuteQuery()
{
DataTable dt = new DataTable();
using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
dt.Load(sdr);
}
return dt;
}
/// <summary>返回查询SQL语句查询出的结果的第一行第一列的值
///
/// </summary>
/// <returns></returns>
public string ExecuteScalar()
{
string res = "";
try
{
object obj = cmd.ExecuteScalar();
if (obj != null)
{
res = obj.ToString();
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
return res;
}
}
}
using System.Data;
using MySql.Data.MySqlClient;
namespace Niunan.BYLW.Utility
{
/// <summary>对MYSQL数据库的操作类
///
/// </summary>
public class MYSQLHelper
{
private MySqlConnection conn = null;
private MySqlCommand cmd = null;
private MySqlDataReader sdr = null;
public MYSQLHelper()
{
//string connStr = WebConfigurationManager.ConnectionStrings["connStr"].ToString();
string connStr = "server=localhost;database=test;uid=root;pwd=123456;charset=utf8";
conn = new MySqlConnection(connStr);
}
/// <summary>创建Command对象
///
/// </summary>
/// <param name="sql">SQL语句</param>
public void CreateCommand(string sql)
{
conn.Open();
cmd = new MySqlCommand(sql, conn);
}
/// <summary>添加参数
///
/// </summary>
/// <param name="paramName">参数名称</param>
/// <param name="value">值</param>
public void AddParameter(string paramName, object value)
{
cmd.Parameters.Add(new MySqlParameter(paramName, value));
}
/// <summary>执行不带参数的增删改SQL语句
///
/// </summary>
/// <param name="cmdText">增删改SQL语句</param>
/// <param name="ct">命令类型</param>
/// <returns></returns>
public bool ExecuteNonQuery()
{
int res;
try
{
res = cmd.ExecuteNonQuery();
if (res > 0)
{
return true;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
return false;
}
/// <summary>执行查询SQL语句
///
/// </summary>
/// <param name="cmdText">查询SQL语句</param>
/// <returns></returns>
public DataTable ExecuteQuery()
{
DataTable dt = new DataTable();
using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
dt.Load(sdr);
}
return dt;
}
/// <summary>返回查询SQL语句查询出的结果的第一行第一列的值
///
/// </summary>
/// <returns></returns>
public string ExecuteScalar()
{
string res = "";
try
{
object obj = cmd.ExecuteScalar();
if (obj != null)
{
res = obj.ToString();
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
return res;
}
}
}
/**
* 作者:牛腩
* 创建时间:2010年3月7日17时35分
* 类说明:对MYSQL数据库的操作类
*/
using System;
using System.Data;
using MySql.Data.MySqlClient;
namespace Niunan.BYLW.Utility
{
/// <summary>对MYSQL数据库的操作类
///
/// </summary>
public class MYSQLHelper
{
private MySqlConnection conn = null;
private MySqlCommand cmd = null;
private MySqlDataReader sdr = null;
public MYSQLHelper()
{
//string connStr = WebConfigurationManager.ConnectionStrings["connStr"].ToString();
string connStr = "server=localhost;database=test;uid=root;pwd=123456;charset=utf8";
conn = new MySqlConnection(connStr);
}
/// <summary>创建Command对象
///
/// </summary>
/// <param name="sql">SQL语句</param>
public void CreateCommand(string sql)
{
conn.Open();
cmd = new MySqlCommand(sql, conn);
}
/// <summary>添加参数
///
/// </summary>
/// <param name="paramName">参数名称</param>
/// <param name="value">值</param>
public void AddParameter(string paramName, object value)
{
cmd.Parameters.Add(new MySqlParameter(paramName, value));
}
/// <summary>执行不带参数的增删改SQL语句
///
/// </summary>
/// <param name="cmdText">增删改SQL语句</param>
/// <param name="ct">命令类型</param>
/// <returns></returns>
public bool ExecuteNonQuery()
{
int res;
try
{
res = cmd.ExecuteNonQuery();
if (res > 0)
{
return true;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
return false;
}
/// <summary>执行查询SQL语句
///
/// </summary>
/// <param name="cmdText">查询SQL语句</param>
/// <returns></returns>
public DataTable ExecuteQuery()
{
DataTable dt = new DataTable();
using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
dt.Load(sdr);
}
return dt;
}
/// <summary>返回查询SQL语句查询出的结果的第一行第一列的值
///
/// </summary>
/// <returns></returns>
public string ExecuteScalar()
{
string res = "";
try
{
object obj = cmd.ExecuteScalar();
if (obj != null)
{
res = obj.ToString();
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
return res;
}
}
}
#6
using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace HelloWinForm.DBUtility
{
class SQLHelper
{
#region 通用方法
// 数据连接池
private SqlConnection con;
///
/// 返回数据库连接字符串
///
///
public static String GetSqlConnection()
{
String conn = ConfigurationManager.AppSettings["connectionString"].ToString();
return conn;
}
#endregion
#region 执行sql字符串
///
/// 执行不带参数的SQL语句
///
///
///
public static int ExecuteSql(String Sqlstr)
{
String ConnStr = GetSqlConnection();
using (SqlConnection conn = new SqlConnection(ConnStr))
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = Sqlstr;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
return 1;
}
}
///
/// 执行带参数的SQL语句
///
/// SQL语句
/// 参数对象数组
///
public static int ExecuteSql(String Sqlstr, SqlParameter[] param)
{
String ConnStr = GetSqlConnection();
using (SqlConnection conn = new SqlConnection(ConnStr))
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = Sqlstr;
cmd.Parameters.AddRange(param);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
return 1;
}
}
///
/// 返回DataReader
///
///
///
public static SqlDataReader ExecuteReader(String Sqlstr)
{
String ConnStr = GetSqlConnection();
SqlConnection conn = new SqlConnection(ConnStr);//返回DataReader时,是不可以用using()的
try
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = Sqlstr;
conn.Open();
return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);//关闭关联的Connection
}
catch //(Exception ex)
{
return null;
}
}
///
/// 执行SQL语句并返回数据表
///
/// SQL语句
///
public static DataTable ExecuteDt(String Sqlstr)
{
String ConnStr = GetSqlConnection();
using (SqlConnection conn = new SqlConnection(ConnStr))
{
SqlDataAdapter da = new SqlDataAdapter(Sqlstr, conn);
DataTable dt = new DataTable();
conn.Open();
da.Fill(dt);
conn.Close();
return dt;
}
}
///
/// 执行SQL语句并返回DataSet
///
/// SQL语句
///
public static DataSet ExecuteDs(String Sqlstr)
{
String ConnStr = GetSqlConnection();
using (SqlConnection conn = new SqlConnection(ConnStr))
{
SqlDataAdapter da = new SqlDataAdapter(Sqlstr, conn);
DataSet ds = new DataSet();
conn.Open();
da.Fill(ds);
conn.Close();
return ds;
}
}
#endregion
#region 操作存储过程
///
/// 运行存储过程(已重载)
///
/// 存储过程的名字
/// 存储过程的返回值
public int RunProc(string procName)
{
SqlCommand cmd = CreateCommand(procName, null);
cmd.ExecuteNonQuery();
this.Close();
return (int)cmd.Parameters["ReturnValue"].Value;
}
///
/// 运行存储过程(已重载)
///
/// 存储过程的名字
/// 存储过程的输入参数列表
/// 存储过程的返回值
public int RunProc(string procName, SqlParameter[] prams)
{
SqlCommand cmd = CreateCommand(procName, prams);
cmd.ExecuteNonQuery();
this.Close();
return (int)cmd.Parameters[0].Value;
}
///
/// 运行存储过程(已重载)
///
/// 存储过程的名字
/// 结果集
public void RunProc(string procName, out SqlDataReader dataReader)
{
SqlCommand cmd = CreateCommand(procName, null);
dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
}
///
/// 运行存储过程(已重载)
///
/// 存储过程的名字
/// 存储过程的输入参数列表
/// 结果集
public void RunProc(string procName, SqlParameter[] prams, out SqlDataReader dataReader)
{
SqlCommand cmd = CreateCommand(procName, prams);
dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
}
///
/// 创建Command对象用于访问存储过程
///
/// 存储过程的名字
/// 存储过程的输入参数列表
/// Command对象
private SqlCommand CreateCommand(string procName, SqlParameter[] prams)
{
// 确定连接是打开的
Open();
//command = new SqlCommand( sprocName, new SqlConnection( ConfigManager.DALConnectionString ) );
SqlCommand cmd = new SqlCommand(procName, con);
cmd.CommandType = CommandType.StoredProcedure;
// 添加存储过程的输入参数列表
if (prams != null)
{
foreach (SqlParameter parameter in prams)
cmd.Parameters.Add(parameter);
}
// 返回Command对象
return cmd;
}
///
/// 创建输入参数
///
/// 参数名
/// 参数类型
/// 参数大小
/// 参数值
/// 新参数对象
public SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value)
{
return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
}
///
/// 创建输出参数
///
/// 参数名
/// 参数类型
/// 参数大小
/// 新参数对象
public SqlParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size)
{
return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null);
}
///
/// 创建存储过程参数
///
/// 参数名
/// 参数类型
/// 参数大小
/// 参数的方向(输入/输出)
/// 参数值
/// 新参数对象
public SqlParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value)
{
SqlParameter param;
if (Size > 0)
{
param = new SqlParameter(ParamName, DbType, Size);
}
else
{
param = new SqlParameter(ParamName, DbType);
}
param.Direction = Direction;
if (!(Direction == ParameterDirection.Output && Value == null))
{
param.Value = Value;
}
return param;
}
#endregion
#region 数据库连接和关闭
///
/// 打开连接池
///
private void Open()
{
// 打开连接池
if (con == null)
{
//这里不仅需要using System.Configuration;还要在引用目录里添加
con = new SqlConnection(GetSqlConnection());
con.Open();
}
}
///
/// 关闭连接池
///
public void Close()
{
if (con != null)
con.Close();
}
///
/// 释放连接池
///
public void Dispose()
{
// 确定连接已关闭
if (con != null)
{
con.Dispose();
con = null;
}
}
#endregion
}
}
#7
将对学生信息的增删改查的方法封装到类中:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Entity;//
namespace DataAccess
{
/// <summary>
/// 学生表数据访问层
/// </summary>
public class StudentDA
{
/// <summary>
/// 执行查询学生信息的方法
/// </summary>
/// <param name="sql">传入sql语句</param>
/// <returns>返回学生集合</returns>
public static List<Student> QueryStudent(string sql)
{
List<Student> allStudents = new List<Student>();//集合存放所有学生
SqlDataReader reader = DBHelp.ExeQuerySql(sql);//调用执行查询的方法
while (reader.Read())
{
Student stu = new Student();//构建一个学生对象
stu.StudentNo = (int)reader["studentNo"];
stu.StudentName = reader["studentName"].ToString();
stu.Gender = reader["gender"].ToString();
stu.GradeId = new Grade();
stu.GradeId.GradeName = reader["gradeName"].ToString();
stu.Phone = reader["phone"].ToString();
stu.Address = reader["address"].ToString();
stu.Birthday = (DateTime)reader["birthday"];
stu.Email = reader["email"].ToString();
allStudents.Add(stu);//添加到集合
}
reader.Close();
DBHelp.CloseConn();//关闭连接
return allStudents;//返回集合
}
/// <summary>
/// 查询所有学生信息的方法
/// </summary>
/// <returns>返回集合</returns>
public static List<Student> QueryAllStu()
{
string sql = "select studentNo,studentName,gender=case gender when 1 then '男' else '女' end,gradeName=Grade.gradeName,phone,[address],birthday,email from Student,Grade where Student.gradeId = Grade.gradeId";
return QueryStudent(sql);//调用执行查询的方法
}
/// <summary>
/// 新增学生信息
/// </summary>
/// <param name="studentName">姓名</param>
/// <param name="gender">性别</param>
/// <param name="gradeId">年级</param>
/// <param name="phone">电话</param>
/// <param name="address">地址</param>
/// <param name="birthday">生日</param>
/// <param name="email">邮箱</param>
/// <returns>返回布尔值</returns>
public static bool AddStudent(string studentName,int gender,int gradeId,string phone,string address,string birthday,string email)
{
string sql = string.Format("insert Student(studentName,gender,gradeId,phone,[address],birthday,email) values('{0}',{1},{2},'{3}','{4}','{5}','{6}')", studentName, gender, gradeId, phone, address, birthday, email);
return DBHelp.ExeNonQuerySql(sql) > 0 ? true:false;
}
/// <summary>
/// 删除学生信息
/// </summary>
/// <param name="studentNo">编号</param>
/// <returns>返回布尔值</returns>
public static bool RemoveStudent(int studentNo)
{
string sql = string.Format("delete Student where studentNo={0}",studentNo);
return DBHelp.ExeNonQuerySql(sql) > 0 ? true : false;
}
/// <summary>
/// 修改学生信息
/// </summary>
/// <param name="studentName">姓名</param>
/// <param name="gender">性别</param>
/// <param name="gradeId">年级</param>
/// <param name="phone">电话</param>
/// <param name="address">地址</param>
/// <param name="birthday">生日</param>
/// <param name="email">邮箱</param>
/// <param name="studentNo">编号</param>
/// <returns></returns>
public static bool ModifyStudent(string studentName, int gender, int gradeId, string phone, string address, string birthday, string email, int studentNo)
{
string sql = string.Format("UPDATE Student set studentName='{0}',gender={1},gradeId={2},phone='{3}',[address]='{4}',birthday='{5}',email='{6}' where studentNo={7}", studentName, gender, gradeId, phone, address, birthday, email, studentNo);
return DBHelp.ExeNonQuerySql(sql) > 0 ? true : false;
}
}
}
#8
我来帮顶得!
#9
把类建好,最好做成dll,这样其他项目直接调用就可以了,只要在config里面修改连接字符串就可以了
#10
纠结 MySql数据库我用的不多耶
#11
+1
#12
mark
#13
你直接去下微软的
#14
学习····
#16
然后怎么调用它呀~~~谢谢
#17
using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;
namespace Niunan.Tg029.Hr.Utility
{
public class MSSQLHelper
{
private SqlConnection conn = null;
private SqlCommand cmd = null;
private SqlDataReader sdr = null;
public MSSQLHelper()
{
string connStr = WebConfigurationManager.ConnectionStrings["connStr"].ToString();
conn = new SqlConnection(connStr);
}
/// <summary>创建Command对象
/// 默认是SQL语句
/// </summary>
/// <param name="sql">SQL语句</param>
public void CreateCommand(string sql)
{
conn.Open();
cmd = new SqlCommand(sql, conn);
}
/// <summary>创建存储过程的Command对象
///
/// </summary>
/// <param name="procName">存储过程名称</param>
public void CreateStoredCommand(string procName)
{
conn.Open();
cmd = new SqlCommand(procName, conn);
cmd.CommandType = CommandType.StoredProcedure;
}
/// <summary>添加参数
/// 默认是输入参数
/// </summary>
/// <param name="paramName">参数名称</param>
/// <param name="value">值</param>
public void AddParameter(string paramName, object value)
{
SqlParameter p = new SqlParameter(paramName, value);
cmd.Parameters.Add(p);
}
/// <summary>添加输出参数
/// 用于存储过程
/// </summary>
/// <param name="paramName">参数名称</param>
/// <param name="value">值</param>
public void AddOutputParameter(string paramName) {
SqlParameter p = new SqlParameter();
p.ParameterName = paramName;
p.Direction = ParameterDirection.Output;
p.Size = 20;
cmd.Parameters.Add(p);
}
/// <summary>获取输出参数的值
///
/// </summary>
/// <param name="paramName">输出参数名称</param>
/// <returns></returns>
public string GetOutputParameter(string paramName) {
return cmd.Parameters[paramName].Value.ToString();
}
/// <summary>执行增删改SQL语句或存储过程
///
/// </summary>
/// <returns></returns>
public bool ExecuteNonQuery()
{
int res;
try
{
res = cmd.ExecuteNonQuery();
if (res > 0)
{
return true;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
return false;
}
/// <summary>执行查询SQL语句或存储过程
///
/// </summary>
/// <returns></returns>
public DataTable ExecuteQuery()
{
DataTable dt = new DataTable();
using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
dt.Load(sdr);
}
return dt;
}
/// <summary>返回查询SQL语句或存储过程查询出的结果的第一行第一列的值
///
/// </summary>
/// <returns></returns>
public string ExecuteScalar()
{
string res = "";
try
{
object obj = cmd.ExecuteScalar();
if (obj != null)
{
res = obj.ToString();
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
return res;
}
}
}
#18
各位大侠们。为什么我写了这个类以后,不能用呢??????
帮帮忙呀
帮帮忙呀
#19
学习学习
#20
学习学习。谢谢各位
#21
qq传你一个DB类。。。
#22
顺便也发我个 375199397@qq.com 谢谢
#23
支持自己去摸索,不支持用别人的类。关键在于你对代码复用的理解
#1
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.Odbc;
/// <summary>
/// DBtools 的摘要说明
/// </summary>
public class DBtools
{
#region 新方法
public DBtools()
{
}
/// <summary>
/// 实例化数据库连接
/// </summary>
/// <param name="type">1为SQT数据库连接(ConfigurationManager.AppSettings["ConnectionStringSQT"]),2为UC数据库连接(ConfigurationManager.AppSettings["ConnectionStringUC"])</param>
public DBtools(int type)
{
switch (type)
{
case 1:
connectionString = ConfigurationManager.AppSettings["ConnectionStringSQT"];
break;
case 2:
connectionString = ConfigurationManager.AppSettings["ConnectionStringUC"];
break;
default:
break;
}
}
/// <summary>
/// 实例化数据库连接
/// </summary>
/// <param name="constr">直接传入数据库连接字符串</param>
public DBtools(string constr)
{
connectionString = constr;
}
public void ConnConnect()
{
try
{
conn.ConnectionString = connectionString;
conn.Open();
}
catch
{
throw;
}
}
//数据库连接
OdbcConnection conn = new OdbcConnection();
//数据库连接字符串(web.config来配置),可以动态更改connectionString支持多数据库.
public string connectionString = null;
#region 常用方法
/// <summary>
/// 得到最大值
/// </summary>
/// <param name="FieldName"></param>
/// <param name="TableName"></param>
/// <returns></returns>
public int GetMaxID(string FieldName, string TableName)
{
string strsql = "select max(" + FieldName + ")+1 from " + TableName;
object obj = GetSingle(strsql);
if (obj == null)
{
return 1;
}
else
{
return int.Parse(obj.ToString());
}
}
/// <summary>
/// 是否存在
/// </summary>
/// <param name="strSql"></param>
/// <returns></returns>
public bool Exists(string strSql)
{
object obj = GetSingle(strSql);
int cmdresult;
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
cmdresult = 0;
}
else
{
cmdresult = int.Parse(obj.ToString());
}
if (cmdresult == 0)
{
return false;
}
else
{
return true;
}
}
/// <summary>
/// 执行一条计算查询结果语句,返回查询结果(object)。
/// </summary>
/// <param name="SQLString">计算查询结果语句</param>
/// <returns>查询结果(object)</returns>
public object GetSingle(string SQLString)
{
ConnConnect();
object obj = null;
OdbcCommand cmd = new OdbcCommand();
try
{
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = SQLString;
obj = cmd.ExecuteScalar();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
obj = null;
}
}
catch
{
throw;
}
finally
{
cmd.Dispose();
ConnClose();
}
return obj;
}
/// <summary>
/// 执行SQL语句,返回影响的记录数
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <returns>影响的记录数</returns>
public int ExecuteSql(string SQLString)
{
ConnConnect();
int num = 0;
OdbcCommand cmd = new OdbcCommand();
try
{
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = SQLString;
num = cmd.ExecuteNonQuery();
}
catch
{
throw;
}
finally
{
cmd.Dispose();
ConnClose();
}
return num;
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="procname"></param>
public DataSet ExecProc(string procname, OdbcParameter[] param)
{
ConnConnect();
OdbcCommand cmd = new OdbcCommand();
OdbcDataAdapter ss = new OdbcDataAdapter();
DataSet ds = new DataSet();
try
{
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = procname;
for (int i = 0; i < param.Length; i++)
{
cmd.Parameters.Add(param[i]);
}
ss.SelectCommand = cmd;
ss.Fill(ds);
}
catch
{
throw;
}
finally
{
cmd.Dispose();
ss.Dispose();
ConnClose();
}
return ds;
}
/// <summary>
/// 执行查询语句,返回DataSet
/// </summary>
/// <param name="SQLString">查询语句</param>
/// <returns>DataSet</returns>
public DataSet Query(string SQLString)
{
ConnConnect();
DataSet ds = new DataSet();
OdbcCommand cmd = new OdbcCommand();
OdbcDataAdapter ss = new OdbcDataAdapter();
try
{
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = SQLString;
ss.SelectCommand = cmd;
ss.Fill(ds);
}
catch
{
throw;
}
finally
{
cmd.Dispose();
ss.Dispose();
ConnClose();
}
return ds;
}
/// <summary>
/// 关闭数据库连接
/// </summary>
public void ConnClose()
{
try
{
conn.Close();
conn.Dispose();
}
catch
{ }
}
#endregion
#endregion
}
#2
请查看PETSHOP4里面的SQLHelper类,然后自己写一个自用的
#3
...
纠结,ADO操作MSSQL 你不会?
纠结,ADO操作MSSQL 你不会?
#4
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.Odbc;
/// <summary>
/// DBtools 的摘要说明
/// </summary>
public class DBtools
{
#region 新方法
public DBtools()
{
}
/// <summary>
/// 实例化数据库连接
/// </summary>
/// <param name="type">1为SQT数据库连接(ConfigurationManager.AppSettings["ConnectionStringSQT"]),2为UC数据库连接(ConfigurationManager.AppSettings["ConnectionStringUC"])</param>
public DBtools(int type)
{
switch (type)
{
case 1:
connectionString = ConfigurationManager.AppSettings["ConnectionStringSQT"];
break;
case 2:
connectionString = ConfigurationManager.AppSettings["ConnectionStringUC"];
break;
default:
break;
}
}
/// <summary>
/// 实例化数据库连接
/// </summary>
/// <param name="constr">直接传入数据库连接字符串</param>
public DBtools(string constr)
{
connectionString = constr;
}
public void ConnConnect()
{
try
{
conn.ConnectionString = connectionString;
conn.Open();
}
catch
{
throw;
}
}
//数据库连接
OdbcConnection conn = new OdbcConnection();
//数据库连接字符串(web.config来配置),可以动态更改connectionString支持多数据库.
public string connectionString = null;
#region 常用方法
/// <summary>
/// 得到最大值
/// </summary>
/// <param name="FieldName"></param>
/// <param name="TableName"></param>
/// <returns></returns>
public int GetMaxID(string FieldName, string TableName)
{
string strsql = "select max(" + FieldName + ")+1 from " + TableName;
object obj = GetSingle(strsql);
if (obj == null)
{
return 1;
}
else
{
return int.Parse(obj.ToString());
}
}
/// <summary>
/// 是否存在
/// </summary>
/// <param name="strSql"></param>
/// <returns></returns>
public bool Exists(string strSql)
{
object obj = GetSingle(strSql);
int cmdresult;
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
cmdresult = 0;
}
else
{
cmdresult = int.Parse(obj.ToString());
}
if (cmdresult == 0)
{
return false;
}
else
{
return true;
}
}
/// <summary>
/// 执行一条计算查询结果语句,返回查询结果(object)。
/// </summary>
/// <param name="SQLString">计算查询结果语句</param>
/// <returns>查询结果(object)</returns>
public object GetSingle(string SQLString)
{
ConnConnect();
object obj = null;
OdbcCommand cmd = new OdbcCommand();
try
{
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = SQLString;
obj = cmd.ExecuteScalar();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
obj = null;
}
}
catch
{
throw;
}
finally
{
cmd.Dispose();
ConnClose();
}
return obj;
}
/// <summary>
/// 执行SQL语句,返回影响的记录数
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <returns>影响的记录数</returns>
public int ExecuteSql(string SQLString)
{
ConnConnect();
int num = 0;
OdbcCommand cmd = new OdbcCommand();
try
{
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = SQLString;
num = cmd.ExecuteNonQuery();
}
catch
{
throw;
}
finally
{
cmd.Dispose();
ConnClose();
}
return num;
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="procname"></param>
public DataSet ExecProc(string procname, OdbcParameter[] param)
{
ConnConnect();
OdbcCommand cmd = new OdbcCommand();
OdbcDataAdapter ss = new OdbcDataAdapter();
DataSet ds = new DataSet();
try
{
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = procname;
for (int i = 0; i < param.Length; i++)
{
cmd.Parameters.Add(param[i]);
}
ss.SelectCommand = cmd;
ss.Fill(ds);
}
catch
{
throw;
}
finally
{
cmd.Dispose();
ss.Dispose();
ConnClose();
}
return ds;
}
/// <summary>
/// 执行查询语句,返回DataSet
/// </summary>
/// <param name="SQLString">查询语句</param>
/// <returns>DataSet</returns>
public DataSet Query(string SQLString)
{
ConnConnect();
DataSet ds = new DataSet();
OdbcCommand cmd = new OdbcCommand();
OdbcDataAdapter ss = new OdbcDataAdapter();
try
{
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = SQLString;
ss.SelectCommand = cmd;
ss.Fill(ds);
}
catch
{
throw;
}
finally
{
cmd.Dispose();
ss.Dispose();
ConnClose();
}
return ds;
}
/// <summary>
/// 关闭数据库连接
/// </summary>
public void ConnClose()
{
try
{
conn.Close();
conn.Dispose();
}
catch
{ }
}
#endregion
#endregion
}
#5
using System;
using System.Data;
using MySql.Data.MySqlClient;
namespace Niunan.BYLW.Utility
{
/// <summary>对MYSQL数据库的操作类
///
/// </summary>
public class MYSQLHelper
{
private MySqlConnection conn = null;
private MySqlCommand cmd = null;
private MySqlDataReader sdr = null;
public MYSQLHelper()
{
//string connStr = WebConfigurationManager.ConnectionStrings["connStr"].ToString();
string connStr = "server=localhost;database=test;uid=root;pwd=123456;charset=utf8";
conn = new MySqlConnection(connStr);
}
/// <summary>创建Command对象
///
/// </summary>
/// <param name="sql">SQL语句</param>
public void CreateCommand(string sql)
{
conn.Open();
cmd = new MySqlCommand(sql, conn);
}
/// <summary>添加参数
///
/// </summary>
/// <param name="paramName">参数名称</param>
/// <param name="value">值</param>
public void AddParameter(string paramName, object value)
{
cmd.Parameters.Add(new MySqlParameter(paramName, value));
}
/// <summary>执行不带参数的增删改SQL语句
///
/// </summary>
/// <param name="cmdText">增删改SQL语句</param>
/// <param name="ct">命令类型</param>
/// <returns></returns>
public bool ExecuteNonQuery()
{
int res;
try
{
res = cmd.ExecuteNonQuery();
if (res > 0)
{
return true;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
return false;
}
/// <summary>执行查询SQL语句
///
/// </summary>
/// <param name="cmdText">查询SQL语句</param>
/// <returns></returns>
public DataTable ExecuteQuery()
{
DataTable dt = new DataTable();
using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
dt.Load(sdr);
}
return dt;
}
/// <summary>返回查询SQL语句查询出的结果的第一行第一列的值
///
/// </summary>
/// <returns></returns>
public string ExecuteScalar()
{
string res = "";
try
{
object obj = cmd.ExecuteScalar();
if (obj != null)
{
res = obj.ToString();
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
return res;
}
}
}
/**
* 作者:牛腩
* 创建时间:2010年3月7日17时35分
* 类说明:对MYSQL数据库的操作类
*/
using System;
using System.Data;
using MySql.Data.MySqlClient;
namespace Niunan.BYLW.Utility
{
/// <summary>对MYSQL数据库的操作类
///
/// </summary>
public class MYSQLHelper
{
private MySqlConnection conn = null;
private MySqlCommand cmd = null;
private MySqlDataReader sdr = null;
public MYSQLHelper()
{
//string connStr = WebConfigurationManager.ConnectionStrings["connStr"].ToString();
string connStr = "server=localhost;database=test;uid=root;pwd=123456;charset=utf8";
conn = new MySqlConnection(connStr);
}
/// <summary>创建Command对象
///
/// </summary>
/// <param name="sql">SQL语句</param>
public void CreateCommand(string sql)
{
conn.Open();
cmd = new MySqlCommand(sql, conn);
}
/// <summary>添加参数
///
/// </summary>
/// <param name="paramName">参数名称</param>
/// <param name="value">值</param>
public void AddParameter(string paramName, object value)
{
cmd.Parameters.Add(new MySqlParameter(paramName, value));
}
/// <summary>执行不带参数的增删改SQL语句
///
/// </summary>
/// <param name="cmdText">增删改SQL语句</param>
/// <param name="ct">命令类型</param>
/// <returns></returns>
public bool ExecuteNonQuery()
{
int res;
try
{
res = cmd.ExecuteNonQuery();
if (res > 0)
{
return true;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
return false;
}
/// <summary>执行查询SQL语句
///
/// </summary>
/// <param name="cmdText">查询SQL语句</param>
/// <returns></returns>
public DataTable ExecuteQuery()
{
DataTable dt = new DataTable();
using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
dt.Load(sdr);
}
return dt;
}
/// <summary>返回查询SQL语句查询出的结果的第一行第一列的值
///
/// </summary>
/// <returns></returns>
public string ExecuteScalar()
{
string res = "";
try
{
object obj = cmd.ExecuteScalar();
if (obj != null)
{
res = obj.ToString();
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
return res;
}
}
}
using System.Data;
using MySql.Data.MySqlClient;
namespace Niunan.BYLW.Utility
{
/// <summary>对MYSQL数据库的操作类
///
/// </summary>
public class MYSQLHelper
{
private MySqlConnection conn = null;
private MySqlCommand cmd = null;
private MySqlDataReader sdr = null;
public MYSQLHelper()
{
//string connStr = WebConfigurationManager.ConnectionStrings["connStr"].ToString();
string connStr = "server=localhost;database=test;uid=root;pwd=123456;charset=utf8";
conn = new MySqlConnection(connStr);
}
/// <summary>创建Command对象
///
/// </summary>
/// <param name="sql">SQL语句</param>
public void CreateCommand(string sql)
{
conn.Open();
cmd = new MySqlCommand(sql, conn);
}
/// <summary>添加参数
///
/// </summary>
/// <param name="paramName">参数名称</param>
/// <param name="value">值</param>
public void AddParameter(string paramName, object value)
{
cmd.Parameters.Add(new MySqlParameter(paramName, value));
}
/// <summary>执行不带参数的增删改SQL语句
///
/// </summary>
/// <param name="cmdText">增删改SQL语句</param>
/// <param name="ct">命令类型</param>
/// <returns></returns>
public bool ExecuteNonQuery()
{
int res;
try
{
res = cmd.ExecuteNonQuery();
if (res > 0)
{
return true;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
return false;
}
/// <summary>执行查询SQL语句
///
/// </summary>
/// <param name="cmdText">查询SQL语句</param>
/// <returns></returns>
public DataTable ExecuteQuery()
{
DataTable dt = new DataTable();
using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
dt.Load(sdr);
}
return dt;
}
/// <summary>返回查询SQL语句查询出的结果的第一行第一列的值
///
/// </summary>
/// <returns></returns>
public string ExecuteScalar()
{
string res = "";
try
{
object obj = cmd.ExecuteScalar();
if (obj != null)
{
res = obj.ToString();
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
return res;
}
}
}
/**
* 作者:牛腩
* 创建时间:2010年3月7日17时35分
* 类说明:对MYSQL数据库的操作类
*/
using System;
using System.Data;
using MySql.Data.MySqlClient;
namespace Niunan.BYLW.Utility
{
/// <summary>对MYSQL数据库的操作类
///
/// </summary>
public class MYSQLHelper
{
private MySqlConnection conn = null;
private MySqlCommand cmd = null;
private MySqlDataReader sdr = null;
public MYSQLHelper()
{
//string connStr = WebConfigurationManager.ConnectionStrings["connStr"].ToString();
string connStr = "server=localhost;database=test;uid=root;pwd=123456;charset=utf8";
conn = new MySqlConnection(connStr);
}
/// <summary>创建Command对象
///
/// </summary>
/// <param name="sql">SQL语句</param>
public void CreateCommand(string sql)
{
conn.Open();
cmd = new MySqlCommand(sql, conn);
}
/// <summary>添加参数
///
/// </summary>
/// <param name="paramName">参数名称</param>
/// <param name="value">值</param>
public void AddParameter(string paramName, object value)
{
cmd.Parameters.Add(new MySqlParameter(paramName, value));
}
/// <summary>执行不带参数的增删改SQL语句
///
/// </summary>
/// <param name="cmdText">增删改SQL语句</param>
/// <param name="ct">命令类型</param>
/// <returns></returns>
public bool ExecuteNonQuery()
{
int res;
try
{
res = cmd.ExecuteNonQuery();
if (res > 0)
{
return true;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
return false;
}
/// <summary>执行查询SQL语句
///
/// </summary>
/// <param name="cmdText">查询SQL语句</param>
/// <returns></returns>
public DataTable ExecuteQuery()
{
DataTable dt = new DataTable();
using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
dt.Load(sdr);
}
return dt;
}
/// <summary>返回查询SQL语句查询出的结果的第一行第一列的值
///
/// </summary>
/// <returns></returns>
public string ExecuteScalar()
{
string res = "";
try
{
object obj = cmd.ExecuteScalar();
if (obj != null)
{
res = obj.ToString();
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
return res;
}
}
}
#6
using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace HelloWinForm.DBUtility
{
class SQLHelper
{
#region 通用方法
// 数据连接池
private SqlConnection con;
///
/// 返回数据库连接字符串
///
///
public static String GetSqlConnection()
{
String conn = ConfigurationManager.AppSettings["connectionString"].ToString();
return conn;
}
#endregion
#region 执行sql字符串
///
/// 执行不带参数的SQL语句
///
///
///
public static int ExecuteSql(String Sqlstr)
{
String ConnStr = GetSqlConnection();
using (SqlConnection conn = new SqlConnection(ConnStr))
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = Sqlstr;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
return 1;
}
}
///
/// 执行带参数的SQL语句
///
/// SQL语句
/// 参数对象数组
///
public static int ExecuteSql(String Sqlstr, SqlParameter[] param)
{
String ConnStr = GetSqlConnection();
using (SqlConnection conn = new SqlConnection(ConnStr))
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = Sqlstr;
cmd.Parameters.AddRange(param);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
return 1;
}
}
///
/// 返回DataReader
///
///
///
public static SqlDataReader ExecuteReader(String Sqlstr)
{
String ConnStr = GetSqlConnection();
SqlConnection conn = new SqlConnection(ConnStr);//返回DataReader时,是不可以用using()的
try
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = Sqlstr;
conn.Open();
return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);//关闭关联的Connection
}
catch //(Exception ex)
{
return null;
}
}
///
/// 执行SQL语句并返回数据表
///
/// SQL语句
///
public static DataTable ExecuteDt(String Sqlstr)
{
String ConnStr = GetSqlConnection();
using (SqlConnection conn = new SqlConnection(ConnStr))
{
SqlDataAdapter da = new SqlDataAdapter(Sqlstr, conn);
DataTable dt = new DataTable();
conn.Open();
da.Fill(dt);
conn.Close();
return dt;
}
}
///
/// 执行SQL语句并返回DataSet
///
/// SQL语句
///
public static DataSet ExecuteDs(String Sqlstr)
{
String ConnStr = GetSqlConnection();
using (SqlConnection conn = new SqlConnection(ConnStr))
{
SqlDataAdapter da = new SqlDataAdapter(Sqlstr, conn);
DataSet ds = new DataSet();
conn.Open();
da.Fill(ds);
conn.Close();
return ds;
}
}
#endregion
#region 操作存储过程
///
/// 运行存储过程(已重载)
///
/// 存储过程的名字
/// 存储过程的返回值
public int RunProc(string procName)
{
SqlCommand cmd = CreateCommand(procName, null);
cmd.ExecuteNonQuery();
this.Close();
return (int)cmd.Parameters["ReturnValue"].Value;
}
///
/// 运行存储过程(已重载)
///
/// 存储过程的名字
/// 存储过程的输入参数列表
/// 存储过程的返回值
public int RunProc(string procName, SqlParameter[] prams)
{
SqlCommand cmd = CreateCommand(procName, prams);
cmd.ExecuteNonQuery();
this.Close();
return (int)cmd.Parameters[0].Value;
}
///
/// 运行存储过程(已重载)
///
/// 存储过程的名字
/// 结果集
public void RunProc(string procName, out SqlDataReader dataReader)
{
SqlCommand cmd = CreateCommand(procName, null);
dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
}
///
/// 运行存储过程(已重载)
///
/// 存储过程的名字
/// 存储过程的输入参数列表
/// 结果集
public void RunProc(string procName, SqlParameter[] prams, out SqlDataReader dataReader)
{
SqlCommand cmd = CreateCommand(procName, prams);
dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
}
///
/// 创建Command对象用于访问存储过程
///
/// 存储过程的名字
/// 存储过程的输入参数列表
/// Command对象
private SqlCommand CreateCommand(string procName, SqlParameter[] prams)
{
// 确定连接是打开的
Open();
//command = new SqlCommand( sprocName, new SqlConnection( ConfigManager.DALConnectionString ) );
SqlCommand cmd = new SqlCommand(procName, con);
cmd.CommandType = CommandType.StoredProcedure;
// 添加存储过程的输入参数列表
if (prams != null)
{
foreach (SqlParameter parameter in prams)
cmd.Parameters.Add(parameter);
}
// 返回Command对象
return cmd;
}
///
/// 创建输入参数
///
/// 参数名
/// 参数类型
/// 参数大小
/// 参数值
/// 新参数对象
public SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value)
{
return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
}
///
/// 创建输出参数
///
/// 参数名
/// 参数类型
/// 参数大小
/// 新参数对象
public SqlParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size)
{
return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null);
}
///
/// 创建存储过程参数
///
/// 参数名
/// 参数类型
/// 参数大小
/// 参数的方向(输入/输出)
/// 参数值
/// 新参数对象
public SqlParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value)
{
SqlParameter param;
if (Size > 0)
{
param = new SqlParameter(ParamName, DbType, Size);
}
else
{
param = new SqlParameter(ParamName, DbType);
}
param.Direction = Direction;
if (!(Direction == ParameterDirection.Output && Value == null))
{
param.Value = Value;
}
return param;
}
#endregion
#region 数据库连接和关闭
///
/// 打开连接池
///
private void Open()
{
// 打开连接池
if (con == null)
{
//这里不仅需要using System.Configuration;还要在引用目录里添加
con = new SqlConnection(GetSqlConnection());
con.Open();
}
}
///
/// 关闭连接池
///
public void Close()
{
if (con != null)
con.Close();
}
///
/// 释放连接池
///
public void Dispose()
{
// 确定连接已关闭
if (con != null)
{
con.Dispose();
con = null;
}
}
#endregion
}
}
#7
将对学生信息的增删改查的方法封装到类中:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Entity;//
namespace DataAccess
{
/// <summary>
/// 学生表数据访问层
/// </summary>
public class StudentDA
{
/// <summary>
/// 执行查询学生信息的方法
/// </summary>
/// <param name="sql">传入sql语句</param>
/// <returns>返回学生集合</returns>
public static List<Student> QueryStudent(string sql)
{
List<Student> allStudents = new List<Student>();//集合存放所有学生
SqlDataReader reader = DBHelp.ExeQuerySql(sql);//调用执行查询的方法
while (reader.Read())
{
Student stu = new Student();//构建一个学生对象
stu.StudentNo = (int)reader["studentNo"];
stu.StudentName = reader["studentName"].ToString();
stu.Gender = reader["gender"].ToString();
stu.GradeId = new Grade();
stu.GradeId.GradeName = reader["gradeName"].ToString();
stu.Phone = reader["phone"].ToString();
stu.Address = reader["address"].ToString();
stu.Birthday = (DateTime)reader["birthday"];
stu.Email = reader["email"].ToString();
allStudents.Add(stu);//添加到集合
}
reader.Close();
DBHelp.CloseConn();//关闭连接
return allStudents;//返回集合
}
/// <summary>
/// 查询所有学生信息的方法
/// </summary>
/// <returns>返回集合</returns>
public static List<Student> QueryAllStu()
{
string sql = "select studentNo,studentName,gender=case gender when 1 then '男' else '女' end,gradeName=Grade.gradeName,phone,[address],birthday,email from Student,Grade where Student.gradeId = Grade.gradeId";
return QueryStudent(sql);//调用执行查询的方法
}
/// <summary>
/// 新增学生信息
/// </summary>
/// <param name="studentName">姓名</param>
/// <param name="gender">性别</param>
/// <param name="gradeId">年级</param>
/// <param name="phone">电话</param>
/// <param name="address">地址</param>
/// <param name="birthday">生日</param>
/// <param name="email">邮箱</param>
/// <returns>返回布尔值</returns>
public static bool AddStudent(string studentName,int gender,int gradeId,string phone,string address,string birthday,string email)
{
string sql = string.Format("insert Student(studentName,gender,gradeId,phone,[address],birthday,email) values('{0}',{1},{2},'{3}','{4}','{5}','{6}')", studentName, gender, gradeId, phone, address, birthday, email);
return DBHelp.ExeNonQuerySql(sql) > 0 ? true:false;
}
/// <summary>
/// 删除学生信息
/// </summary>
/// <param name="studentNo">编号</param>
/// <returns>返回布尔值</returns>
public static bool RemoveStudent(int studentNo)
{
string sql = string.Format("delete Student where studentNo={0}",studentNo);
return DBHelp.ExeNonQuerySql(sql) > 0 ? true : false;
}
/// <summary>
/// 修改学生信息
/// </summary>
/// <param name="studentName">姓名</param>
/// <param name="gender">性别</param>
/// <param name="gradeId">年级</param>
/// <param name="phone">电话</param>
/// <param name="address">地址</param>
/// <param name="birthday">生日</param>
/// <param name="email">邮箱</param>
/// <param name="studentNo">编号</param>
/// <returns></returns>
public static bool ModifyStudent(string studentName, int gender, int gradeId, string phone, string address, string birthday, string email, int studentNo)
{
string sql = string.Format("UPDATE Student set studentName='{0}',gender={1},gradeId={2},phone='{3}',[address]='{4}',birthday='{5}',email='{6}' where studentNo={7}", studentName, gender, gradeId, phone, address, birthday, email, studentNo);
return DBHelp.ExeNonQuerySql(sql) > 0 ? true : false;
}
}
}
#8
我来帮顶得!
#9
把类建好,最好做成dll,这样其他项目直接调用就可以了,只要在config里面修改连接字符串就可以了
#10
纠结 MySql数据库我用的不多耶
#11
+1
#12
mark
#13
你直接去下微软的
#14
学习····
#15
#16
然后怎么调用它呀~~~谢谢
#17
using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;
namespace Niunan.Tg029.Hr.Utility
{
public class MSSQLHelper
{
private SqlConnection conn = null;
private SqlCommand cmd = null;
private SqlDataReader sdr = null;
public MSSQLHelper()
{
string connStr = WebConfigurationManager.ConnectionStrings["connStr"].ToString();
conn = new SqlConnection(connStr);
}
/// <summary>创建Command对象
/// 默认是SQL语句
/// </summary>
/// <param name="sql">SQL语句</param>
public void CreateCommand(string sql)
{
conn.Open();
cmd = new SqlCommand(sql, conn);
}
/// <summary>创建存储过程的Command对象
///
/// </summary>
/// <param name="procName">存储过程名称</param>
public void CreateStoredCommand(string procName)
{
conn.Open();
cmd = new SqlCommand(procName, conn);
cmd.CommandType = CommandType.StoredProcedure;
}
/// <summary>添加参数
/// 默认是输入参数
/// </summary>
/// <param name="paramName">参数名称</param>
/// <param name="value">值</param>
public void AddParameter(string paramName, object value)
{
SqlParameter p = new SqlParameter(paramName, value);
cmd.Parameters.Add(p);
}
/// <summary>添加输出参数
/// 用于存储过程
/// </summary>
/// <param name="paramName">参数名称</param>
/// <param name="value">值</param>
public void AddOutputParameter(string paramName) {
SqlParameter p = new SqlParameter();
p.ParameterName = paramName;
p.Direction = ParameterDirection.Output;
p.Size = 20;
cmd.Parameters.Add(p);
}
/// <summary>获取输出参数的值
///
/// </summary>
/// <param name="paramName">输出参数名称</param>
/// <returns></returns>
public string GetOutputParameter(string paramName) {
return cmd.Parameters[paramName].Value.ToString();
}
/// <summary>执行增删改SQL语句或存储过程
///
/// </summary>
/// <returns></returns>
public bool ExecuteNonQuery()
{
int res;
try
{
res = cmd.ExecuteNonQuery();
if (res > 0)
{
return true;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
return false;
}
/// <summary>执行查询SQL语句或存储过程
///
/// </summary>
/// <returns></returns>
public DataTable ExecuteQuery()
{
DataTable dt = new DataTable();
using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
dt.Load(sdr);
}
return dt;
}
/// <summary>返回查询SQL语句或存储过程查询出的结果的第一行第一列的值
///
/// </summary>
/// <returns></returns>
public string ExecuteScalar()
{
string res = "";
try
{
object obj = cmd.ExecuteScalar();
if (obj != null)
{
res = obj.ToString();
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
return res;
}
}
}
#18
各位大侠们。为什么我写了这个类以后,不能用呢??????
帮帮忙呀
帮帮忙呀
#19
学习学习
#20
学习学习。谢谢各位
#21
qq传你一个DB类。。。
#22
顺便也发我个 375199397@qq.com 谢谢
#23
支持自己去摸索,不支持用别人的类。关键在于你对代码复用的理解