sqlhelper for mysql_c# SQLHelper(for winForm)实现代码

SQLHelper.cs

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

}

}

简单用一下:

using System;

using System.Collections.Generic;

using System.Text;

using System.Data;

using System.Data.SqlClient;

using System.Collections;

using HelloWinForm.DBUtility;

namespace HelloWinForm.DAL

{

class Student

{

public string test()

{

string str = "";

SqlDataReader dr = SQLHelper.ExecuteReader("select * from Student");

while (dr.Read())

{

str += dr["StudentNO"].ToString();

}

dr.Close();

return str;

}

}

}