c# SQLHelper(for winForm)实现代码
c# SQLHelper(for winForm)实现代码
发布时间:2016-12-28 来源:查字典编辑
摘要:SQLHelper.cs复制代码代码如下:usingSystem;usingSystem.Collections.Generic;using...

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;

/// <summary>

/// 返回数据库连接字符串

/// </summary>

/// <returns></returns>

public static String GetSqlConnection()

{

String conn = ConfigurationManager.AppSettings["connectionString"].ToString();

return conn;

}

#endregion

#region 执行sql字符串

/// <summary>

/// 执行不带参数的SQL语句

/// </summary>

/// <param name="Sqlstr"></param>

/// <returns></returns>

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;

}

}

/// <summary>

/// 执行带参数的SQL语句

/// </summary>

/// <param name="Sqlstr">SQL语句</param>

/// <param name="param">参数对象数组</param>

/// <returns></returns>

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;

}

}

/// <summary>

/// 返回DataReader

/// </summary>

/// <param name="Sqlstr"></param>

/// <returns></returns>

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;

}

}

/// <summary>

/// 执行SQL语句并返回数据表

/// </summary>

/// <param name="Sqlstr">SQL语句</param>

/// <returns></returns>

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;

}

}

/// <summary>

/// 执行SQL语句并返回DataSet

/// </summary>

/// <param name="Sqlstr">SQL语句</param>

/// <returns></returns>

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 操作存储过程

/// <summary>

/// 运行存储过程(已重载)

/// </summary>

/// <param name="procName">存储过程的名字</param>

/// <returns>存储过程的返回值</returns>

public int RunProc(string procName)

{

SqlCommand cmd = CreateCommand(procName, null);

cmd.ExecuteNonQuery();

this.Close();

return (int)cmd.Parameters["ReturnValue"].Value;

}

/// <summary>

/// 运行存储过程(已重载)

/// </summary>

/// <param name="procName">存储过程的名字</param>

/// <param name="prams">存储过程的输入参数列表</param>

/// <returns>存储过程的返回值</returns>

public int RunProc(string procName, SqlParameter[] prams)

{

SqlCommand cmd = CreateCommand(procName, prams);

cmd.ExecuteNonQuery();

this.Close();

return (int)cmd.Parameters[0].Value;

}

/// <summary>

/// 运行存储过程(已重载)

/// </summary>

/// <param name="procName">存储过程的名字</param>

/// <param name="dataReader">结果集</param>

public void RunProc(string procName, out SqlDataReader dataReader)

{

SqlCommand cmd = CreateCommand(procName, null);

dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);

}

/// <summary>

/// 运行存储过程(已重载)

/// </summary>

/// <param name="procName">存储过程的名字</param>

/// <param name="prams">存储过程的输入参数列表</param>

/// <param name="dataReader">结果集</param>

public void RunProc(string procName, SqlParameter[] prams, out SqlDataReader dataReader)

{

SqlCommand cmd = CreateCommand(procName, prams);

dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);

}

/// <summary>

/// 创建Command对象用于访问存储过程

/// </summary>

/// <param name="procName">存储过程的名字</param>

/// <param name="prams">存储过程的输入参数列表</param>

/// <returns>Command对象</returns>

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;

}

/// <summary>

/// 创建输入参数

/// </summary>

/// <param name="ParamName">参数名</param>

/// <param name="DbType">参数类型</param>

/// <param name="Size">参数大小</param>

/// <param name="Value">参数值</param>

/// <returns>新参数对象</returns>

public SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value)

{

return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);

}

/// <summary>

/// 创建输出参数

/// </summary>

/// <param name="ParamName">参数名</param>

/// <param name="DbType">参数类型</param>

/// <param name="Size">参数大小</param>

/// <returns>新参数对象</returns>

public SqlParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size)

{

return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null);

}

/// <summary>

/// 创建存储过程参数

/// </summary>

/// <param name="ParamName">参数名</param>

/// <param name="DbType">参数类型</param>

/// <param name="Size">参数大小</param>

/// <param name="Direction">参数的方向(输入/输出)</param>

/// <param name="Value">参数值</param>

/// <returns>新参数对象</returns>

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 数据库连接和关闭

/// <summary>

/// 打开连接池

/// </summary>

private void Open()

{

// 打开连接池

if (con == null)

{

//这里不仅需要using System.Configuration;还要在引用目录里添加

con = new SqlConnection(GetSqlConnection());

con.Open();

}

}

/// <summary>

/// 关闭连接池

/// </summary>

public void Close()

{

if (con != null)

con.Close();

}

/// <summary>

/// 释放连接池

/// </summary>

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;

}

}

}

推荐文章
猜你喜欢
附近的人在看
推荐阅读
拓展阅读
相关阅读
网友关注
最新C#教程学习
热门C#教程学习
编程开发子分类