DBHelper林新
using System;
using System.Collections.Generic;
//using System.Linq;
using System.Text;
//using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
namespace DAL
{
public class DBHelper
{
#region 连接字符串
private string s = System.Configuration.ConfigurationSettings.AppSettings["connectstring"];
private SqlDataAdapter dap;
#endregion
#region 创建连接对象
public SqlConnection con = null;
#endregion
#region 测试链接
public void Init()
{
if (con == null)
{
con = new SqlConnection(s);
}
if (con.State == ConnectionState.Broken)
{
con.Close();
con.Open();
}
if (con.State == ConnectionState.Closed)
{
con.Open();
}
}
#endregion
#region 查询
/// <summary>
/// 查询
/// </summary>
/// <param name="sql">sql语句</param>
/// <returns>数据表</returns>
public DataTable Select(string sql)
{
try
{
Init();
SqlDataAdapter da = new SqlDataAdapter(sql, con);
DataTable table = new DataTable();
da.Fill(table);
con.Close();
return table;
}
catch (Exception ex)
{
return null;
}
}
#endregion
#region 查询返回字符串
/// <summary>
/// 查询
/// </summary>
/// <param name="sql">sql语句</param>
/// <returns>数据表</returns>
public string SelectSHSL(string sql)
{
Init();
SqlDataAdapter da = new SqlDataAdapter(sql, con);
DataTable table = new DataTable();
da.Fill(table);
con.Close();
if (table.Rows.Count > 0)
{
return table.Rows[0][0].ToString();
}
else
{
return "";
}
}
#endregion
#region 返回DataRow
public DataRow GetDataRow(string sql)
{
Init();
SqlDataAdapter da = new SqlDataAdapter(sql, con);
DataTable table = new DataTable();
da.Fill(table);
con.Close();
if (table.Rows.Count > 0)
{
return table.Rows[0];
}
else
{
return null;
}
}
#endregion
/// <summary>
/// 更新DataTable与数据库交互
/// </summary>
public void UpdateDataTable(DataTable table) {
try
{
Init();
SqlDataAdapter sda = new SqlDataAdapter("select * from CommodityConservation", con);
sda.Update(table);
table.AcceptChanges();
}
catch (Exception)
{
}
finally {
con.Close();
}
}
#region 增删改
public bool Exect(string sql)
{
Init();
SqlCommand com = new SqlCommand(sql, con);
int i = com.ExecuteNonQuery();
con.Close();
return i > 0;
}
#endregion
#region result
public object ExectResult(string sql)
{
Init();
SqlCommand com = new SqlCommand(sql, con);
object i = com.ExecuteScalar();
con.Close();
return i ;
}
#endregion
#region 逐行查询
public SqlDataReader du(string sql)
{
Init();
SqlCommand com = new SqlCommand(sql, con);
return com.ExecuteReader(CommandBehavior.CloseConnection);
}
#endregion
#region 断开式连接（增，删，改）
public bool Update(DataTable table)
{
if (dap == null)
{
return false;
}
else
{
try
{
dap.Update(table);
return true;
}
catch (Exception)
{
return false;
}
}
}
#endregion
#region 绑定数据到ComboBox
public void BindDataToComboBox(string sql, ComboBox cbb, string ID, string Name)
{
DataTable dt = Select(sql);
foreach (DataRow dr in dt.Rows)
{
cbb.Items.Add(dr[ID] + "|" + dr[Name]);
}
}
public void BindDataToToolStripComboBox(string sql, ToolStripComboBox cbb, string ID, string Name)
{
DataTable dt = Select(sql);
foreach (DataRow dr in dt.Rows)
{
cbb.Items.Add(dr[ID] + "|" + dr[Name]);
}
}
public void BindEmptyDataToComboBox(string sql, ComboBox cbb, string ID, string Name)
{
DataTable dt = Select(sql);
DataTable s = new DataTable();
s.Columns.Add(new DataColumn("ID", typeof(int)));
s.Columns.Add(new DataColumn("Name", typeof(string)));
DataRow row = s.NewRow();
s.Rows.Add(row);
foreach (DataRow dr in dt.Rows)
{
DataRow rows = s.NewRow();
rows["Name"]=dr[ID] + "|" + dr[Name];
rows["ID"] = dr[ID];
s.Rows.Add(rows);
}
cbb.DataSource = s;
cbb.ValueMember = "ID";
cbb.DisplayMember = "Name";
}
#endregion
public SqlDataReader GetReader(string sqltxt)
{
Init();
SqlCommand Scom = new SqlCommand(sqltxt,con); //声明SqlCommand对象，getcon数据是连接语句
SqlDataReader P_Dr;
P_Dr = Scom.ExecuteReader();
return P_Dr;
}
}
}
<?xml version="1.0"?>
<configuration>
<startup>
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/></startup>
<appSettings>
<!--add key="connectstring" value="Data Source=SQL5006.webweb.com ;Initial Catalog=DB_9A461C_xiaohongdian;User Id=DB_9A461C_xiaohongdian_admin;Password=tttdd.cn"/-->
<add key="connectstring" value="server=192.168.0.106;database=EMS;uid=sa;pwd=asDF1234"/>
<!---->
<!--<add key="connectstring" value="server=.;database=EMS; Trusted_connection=true"/>-->
</appSettings>
</configuration>
