111,098
社区成员
发帖
与我相关
我的任务
分享请发表友善的回复…
发表回复
laserterry 2017-05-02
- 打赏
- 举报
/// <summary>
/// 返回数据库连接SqlConnection
/// </summary>
/// <returns>SqlConnection</returns>
public static SqlConnection ReturnSqlConnet()
{
SqlConnection con = null;
XmlElement xmlElement = LoadXmlRoot("\\config.xml");
con = new SqlConnection("Data Source=" + "localhost"
+ ";Password=" + xmlElement.SelectSingleNode("db").SelectSingleNode("pwd").InnerText
+ ";User ID=" + xmlElement.SelectSingleNode("db").SelectSingleNode("uid").InnerText
+ ";Initial Catalog=" + xmlElement.SelectSingleNode("db").SelectSingleNode("database").InnerText
+ ";Connection TimeOut=30");
try
{
con.Open();
con.Close();
return con;
}
catch (SqlException err)
{
MessageBox.Show(err.Message);
return null;
}
}
正怒月神 2017-05-02
- 打赏
- 举报
dbhelper？ 网上很多啊。strConn改成读取config就可以了
public static class DBHelper
{
//连接字符串
static string strConn = "server=.;Database=TestData;uid=sa;pwd=sasa;";
#region 执行查询，返回DataTable对象-----------------------
public static DataTable GetTable(string strSQL)
{
return GetTable(strSQL, null);
}
public static DataTable GetTable(string strSQL, SqlParameter[] pas)
{
return GetTable(strSQL, pas, CommandType.Text);
}
public static DataTable GetTable(string strSQL, SqlParameter[] pas, CommandType cmdtype)
{
DataTable dt = new DataTable(); ;
using (SqlConnection conn = new SqlConnection(strConn))
{
SqlDataAdapter da = new SqlDataAdapter(strSQL, conn);
da.SelectCommand.CommandType = cmdtype;
if (pas != null)
{
da.SelectCommand.Parameters.AddRange(pas);
}
da.Fill(dt);
}
return dt;
}
#endregion
#region 执行查询，返回DataSet对象-------------------------
public static DataSet GetDataSet(string strSQL)
{
return GetDataSet(strSQL, null);
}
public static DataSet GetDataSet(string strSQL, SqlParameter[] pas)
{
return GetDataSet(strSQL, pas, CommandType.Text);
}
public static DataSet GetDataSet(string strSQL, SqlParameter[] pas, CommandType cmdtype)
{
DataSet dt = new DataSet(); ;
using (SqlConnection conn = new SqlConnection(strConn))
{
SqlDataAdapter da = new SqlDataAdapter(strSQL, conn);
da.SelectCommand.CommandType = cmdtype;
if (pas != null)
{
da.SelectCommand.Parameters.AddRange(pas);
}
da.Fill(dt);
}
return dt;
}
#endregion
#region 执行非查询存储过程和SQL语句-----------------------------
public static int ExcuteProc(string ProcName)
{
return ExcuteSQL(ProcName, null, CommandType.StoredProcedure);
}
public static int ExcuteProc(string ProcName, SqlParameter[] pars)
{
return ExcuteSQL(ProcName, pars, CommandType.StoredProcedure);
}
public static int ExcuteSQL(string strSQL)
{
return ExcuteSQL(strSQL, null);
}
public static int ExcuteSQL(string strSQL, SqlParameter[] paras)
{
return ExcuteSQL(strSQL, paras, CommandType.Text);
}
public static int ExcuteSQL(string strSQL, SqlParameter[] paras, CommandType cmdType)
{
int i = 0;
using (SqlConnection conn = new SqlConnection(strConn))
{
SqlCommand cmd = new SqlCommand(strSQL, conn);
cmd.CommandType = cmdType;
if (paras != null)
{
cmd.Parameters.AddRange(paras);
}
conn.Open();
i = cmd.ExecuteNonQuery();
conn.Close();
}
return i;
}
#endregion
#region 执行查询返回第一行，第一列---------------------------------
public static int ExcuteScalarSQL(string strSQL)
{
return ExcuteScalarSQL(strSQL, null);
}
public static int ExcuteScalarSQL(string strSQL, SqlParameter[] paras)
{
return ExcuteScalarSQL(strSQL, paras, CommandType.Text);
}
public static int ExcuteScalarProc(string strSQL, SqlParameter[] paras)
{
return ExcuteScalarSQL(strSQL, paras, CommandType.StoredProcedure);
}
public static int ExcuteScalarSQL(string strSQL, SqlParameter[] paras, CommandType cmdType)
{
int i = 0;
using (SqlConnection conn = new SqlConnection(strConn))
{
SqlCommand cmd = new SqlCommand(strSQL, conn);
cmd.CommandType = cmdType;
if (paras != null)
{
cmd.Parameters.AddRange(paras);
}
conn.Open();
i = Convert.ToInt32(cmd.ExecuteScalar());
conn.Close();
}
return i;
}
#endregion
#region 查询获取单个值------------------------------------
public static object GetObjectByProc(string ProcName)
{
return GetObjectByProc(ProcName, null);
}
public static object GetObjectByProc(string ProcName, SqlParameter[] paras)
{
return GetObject(ProcName, paras, CommandType.StoredProcedure);
}
public static object GetObject(string strSQL)
{
return GetObject(strSQL, null);
}
public static object GetObject(string strSQL, SqlParameter[] paras)
{
return GetObject(strSQL, paras, CommandType.Text);
}
public static object GetObject(string strSQL, SqlParameter[] paras, CommandType cmdtype)
{
object o = null;
using (SqlConnection conn = new SqlConnection(strConn))
{
SqlCommand cmd = new SqlCommand(strSQL, conn);
cmd.CommandType = cmdtype;
if (paras != null)
{
cmd.Parameters.AddRange(paras);
}
conn.Open();
o = cmd.ExecuteScalar();
conn.Close();
}
return o;
}
#endregion
#region 查询获取DataReader------------------------------------
public static SqlDataReader GetReaderByProc(string procName)
{
return GetReaderByProc(procName, null);
}
public static SqlDataReader GetReaderByProc(string procName, SqlParameter[] paras)
{
return GetReader(procName, paras, CommandType.StoredProcedure);
}
public static SqlDataReader GetReader(string strSQL)
{
return GetReader(strSQL, null);
}
public static SqlDataReader GetReader(string strSQL, SqlParameter[] paras)
{
return GetReader(strSQL, paras, CommandType.Text);
}
public static SqlDataReader GetReader(string strSQL, SqlParameter[] paras, CommandType cmdtype)
{
SqlDataReader sqldr = null;
SqlConnection conn = new SqlConnection(strConn);
SqlCommand cmd = new SqlCommand(strSQL, conn);
cmd.CommandType = cmdtype;
if (paras != null)
{
cmd.Parameters.AddRange(paras);
}
conn.Open();
//CommandBehavior.CloseConnection的作用是如果关联的DataReader对象关闭，则连接自动关闭
sqldr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return sqldr;
}
#endregion
#region 批量插入数据---------------------------------------------
public static void BulkToDB(DataTable sourceDt, string targetTable)
{
SqlConnection conn = new SqlConnection(strConn);
SqlBulkCopy bulkCopy = new SqlBulkCopy(conn); //用其它源的数据有效批量加载sql server表中
bulkCopy.DestinationTableName = targetTable; //服务器上目标表的名称
bulkCopy.BatchSize = sourceDt.Rows.Count; //每一批次中的行数
try
{
conn.Open();
if (sourceDt != null && sourceDt.Rows.Count != 0)
bulkCopy.WriteToServer(sourceDt); //将提供的数据源中的所有行复制到目标表中
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
if (bulkCopy != null)
bulkCopy.Close();
}
}
#endregion
}
娃都会打酱油了 2017-05-02
- 打赏
- 举报
什么叫公共类？
难道还能自动组织出链接字符串？
以专业开发人员为伍 2017-05-02
- 打赏
- 举报
直接手写成一个字符串常量，或者直接作为配置中的一行，不就行了吗？你难道要写1千个不同的“连接字符串”？
写一大堆“通用”代码反而是繁琐的。
吉普赛的歌 2017-05-02
- 打赏
- 举报
橙色阳光 2017-05-02
- 打赏
- 举报
代码之城 2017-05-02
- 打赏
- 举报
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient; //这一行是我们手动添加上去的
/// <summary>
/// DBHelper 的摘要说明
/// </summary>
public class DBHelper
{
public DBHelper()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
/// <summary>
/// 连接数据库
/// </summary>
/// <returns>返回SqlConnection对象</returns>
///
public SqlConnection GetConnection()
{
string myStr = System.Configuration.ConfigurationManager.AppSettings["ConnectionString"].ToString();
SqlConnection myConn = new SqlConnection(myStr);
return myConn;
}
/// <summary>
/// 执行SQL语句，并返回受影响的行数
/// </summary>
/// <param name="myCmd">执行SQL语句命令的SqlCommand对象</param>
public void ExecNonQuery(SqlCommand myCmd)
{
try
{
if (myCmd.Connection.State != ConnectionState.Open)
{
myCmd.Connection.Open();
}
//使用SqlCommand对象的ExecuteNonQuery方法执行SQL语句，并返回受影响的行数
myCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
if (myCmd.Connection.State == ConnectionState.Open)
{
myCmd.Connection.Close(); //关闭与数据库的连接
}
}
}
/// <summary>
/// 说 明： 返回数据集的表的集合
/// 返回值： 数据源的数据表
/// 参 数： myCmd 执行SQL语句命令的SqlCommand对象，TableName 数据表名称
/// </summary>
public DataTable GetDataSet(SqlCommand MyCmd, string TableName)
{
SqlDataAdapter adapt;
DataSet ds = new DataSet();
try
{
if (MyCmd.Connection.State != ConnectionState.Open)
{
MyCmd.Connection.Open();
}
adapt = new SqlDataAdapter(MyCmd);
adapt.Fill(ds, TableName);
return ds.Tables[TableName];
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
if (MyCmd.Connection.State == ConnectionState.Open)
{
MyCmd.Connection.Close(); //关闭与数据库的连接
}
}
}
/// <summary>
/// 执行查询语句，返回sqlCommand类对象
/// </summary>
/// <param name="strSql">查询语句</param>
/// <returns>返回sqlCommand类对象</returns>
public SqlCommand GetCommandStr(string strSql)
{
SqlConnection myConn = GetConnection();
SqlCommand myCmd = new SqlCommand();
myCmd.Connection = myConn;
myCmd.CommandText = strSql;
myCmd.CommandType = CommandType.Text;
return myCmd;
}
}
