从网上找到一个数据库连接类,请问怎样使用呢?比如在一个界面中的combobox中添加Person表的PersonName字段;using System;
using System.Data;
using System.Data.SqlClient;
namespace abc
{
public class DataBase
{
// 连接数据源
private SqlConnection conn = null;
/// <summary>
/// 数据源
/// </summary>
/// <param name="conStr">数据源连接字符串</param>
public DataBase()
{
conn = new SqlConnection("server=.;database=test;uid=sa;pwd=");
}
/// <summary>
/// 根据SQL查询返回DataSet对象,如果没有查询到则返回NULL
/// </summary>
/// <param name="sql">查询语句</param>
/// <returns>DataSet</returns>
public DataSet GetDataSet(string sql)
{
DataSet ds = new DataSet();
try
{
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.CommandTimeout = 20;
System.Data.SqlClient.SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(ds, "tempTable");
}
catch (Exception e)
{
ds = null;
}
finally
{
this.Close();
}
return ds;
}
/// <summary>
/// 根据SQL查询返回DataSet对象,如果没有查询到则返回NULL
/// </summary>
/// <param name="sql">查询语句</param>
/// <param name="sRecord">开始记录数</param>
/// <param name="mRecord">最大记录数</param>
/// <returns>DataSet</returns>
public DataSet GetDataSet(string sql, int sRecord, int mRecord)
{
DataSet ds = new DataSet();
try
{
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.CommandTimeout = 20;
System.Data.SqlClient.SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(ds, sRecord, mRecord, "tempTable");
}
catch (Exception e)
{
ds = null;
}
finally
{
this.Close();
}
return ds;
}
/// <summary>
/// 对数据库的增,删,改的操作
/// </summary>
/// <param name="sql">SQL语句</param>
/// <returns>是否成功</returns>
public bool ExecuteDataBase(string sql)
{
bool succeed = false;
int cnt = 0;
try
{
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.CommandTimeout = 20;
if (this.Open())
cnt = cmd.ExecuteNonQuery();
}
catch (Exception e)
{
e.ToString();
}
finally
{
if (cnt > 0)
{
succeed = true;
}
this.Close();
}
return succeed;
}
/// <summary>
/// 获得该SQL查询返回的第一行第一列的值,如果没有查询到则返回NULL
/// </summary>
/// <param name="sql">查询语句</param>
/// <returns>返回的第一行第一列的值</returns>
public string GetScalar(string sql)
{
string str = null;
try
{
SqlCommand cmd = new SqlCommand(sql, conn);
if (this.Open())
str = cmd.ExecuteScalar().ToString();
}
catch (Exception e)
{
e.ToString();
}
finally
{
this.Close();
}
return str;
} /// <summary>
/// 获得该SQL查询返回DataTable,如果没有查询到则返回NULL
/// </summary>
/// <param name="sql">查询语句</param>
/// <returns></returns>
public DataTable GetDataTable(string sql)
{
DataTable tb = null;
DataSet ds = this.GetDataSet(sql);
if (ds != null)
{
tb = ds.Tables["tempTable"];
}
return tb;
}
/// <summary>
/// 打开数据库连接.
/// </summary>
private bool Open()
{
bool succeed = false;
try
{
if (conn.State == System.Data.ConnectionState.Closed)
{
conn.Open();
succeed = true;
}
else if (conn.State == System.Data.ConnectionState.Broken)
{
conn.Close();
conn.Open();
succeed = true;
}
else if (conn.State == System.Data.ConnectionState.Open)
{
succeed = true;
}
}
catch (Exception e)
{
e.ToString();
} return succeed;
}
/// <summary>
/// 关闭数据库连接
/// </summary>
public void Close()
{
if (conn != null)
{
conn.Close();
}
}
/// <summary>
/// 释放数据库连接资源
/// </summary>
public void Dispose()
{
if (conn != null)
{
conn.Dispose();
conn = null;
}
}
}
}
using System.Data;
using System.Data.SqlClient;
namespace abc
{
public class DataBase
{
// 连接数据源
private SqlConnection conn = null;
/// <summary>
/// 数据源
/// </summary>
/// <param name="conStr">数据源连接字符串</param>
public DataBase()
{
conn = new SqlConnection("server=.;database=test;uid=sa;pwd=");
}
/// <summary>
/// 根据SQL查询返回DataSet对象,如果没有查询到则返回NULL
/// </summary>
/// <param name="sql">查询语句</param>
/// <returns>DataSet</returns>
public DataSet GetDataSet(string sql)
{
DataSet ds = new DataSet();
try
{
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.CommandTimeout = 20;
System.Data.SqlClient.SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(ds, "tempTable");
}
catch (Exception e)
{
ds = null;
}
finally
{
this.Close();
}
return ds;
}
/// <summary>
/// 根据SQL查询返回DataSet对象,如果没有查询到则返回NULL
/// </summary>
/// <param name="sql">查询语句</param>
/// <param name="sRecord">开始记录数</param>
/// <param name="mRecord">最大记录数</param>
/// <returns>DataSet</returns>
public DataSet GetDataSet(string sql, int sRecord, int mRecord)
{
DataSet ds = new DataSet();
try
{
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.CommandTimeout = 20;
System.Data.SqlClient.SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(ds, sRecord, mRecord, "tempTable");
}
catch (Exception e)
{
ds = null;
}
finally
{
this.Close();
}
return ds;
}
/// <summary>
/// 对数据库的增,删,改的操作
/// </summary>
/// <param name="sql">SQL语句</param>
/// <returns>是否成功</returns>
public bool ExecuteDataBase(string sql)
{
bool succeed = false;
int cnt = 0;
try
{
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.CommandTimeout = 20;
if (this.Open())
cnt = cmd.ExecuteNonQuery();
}
catch (Exception e)
{
e.ToString();
}
finally
{
if (cnt > 0)
{
succeed = true;
}
this.Close();
}
return succeed;
}
/// <summary>
/// 获得该SQL查询返回的第一行第一列的值,如果没有查询到则返回NULL
/// </summary>
/// <param name="sql">查询语句</param>
/// <returns>返回的第一行第一列的值</returns>
public string GetScalar(string sql)
{
string str = null;
try
{
SqlCommand cmd = new SqlCommand(sql, conn);
if (this.Open())
str = cmd.ExecuteScalar().ToString();
}
catch (Exception e)
{
e.ToString();
}
finally
{
this.Close();
}
return str;
} /// <summary>
/// 获得该SQL查询返回DataTable,如果没有查询到则返回NULL
/// </summary>
/// <param name="sql">查询语句</param>
/// <returns></returns>
public DataTable GetDataTable(string sql)
{
DataTable tb = null;
DataSet ds = this.GetDataSet(sql);
if (ds != null)
{
tb = ds.Tables["tempTable"];
}
return tb;
}
/// <summary>
/// 打开数据库连接.
/// </summary>
private bool Open()
{
bool succeed = false;
try
{
if (conn.State == System.Data.ConnectionState.Closed)
{
conn.Open();
succeed = true;
}
else if (conn.State == System.Data.ConnectionState.Broken)
{
conn.Close();
conn.Open();
succeed = true;
}
else if (conn.State == System.Data.ConnectionState.Open)
{
succeed = true;
}
}
catch (Exception e)
{
e.ToString();
} return succeed;
}
/// <summary>
/// 关闭数据库连接
/// </summary>
public void Close()
{
if (conn != null)
{
conn.Close();
}
}
/// <summary>
/// 释放数据库连接资源
/// </summary>
public void Dispose()
{
if (conn != null)
{
conn.Dispose();
conn = null;
}
}
}
}
解决方案 »
- VSTO中给Excel 的Cell赋值出错..
- 大家觉得wrox的红皮书和o‘reilly的动物书哪个好一些? 我觉得wrox的书普遍都...
- 求SIP 客户端资料
- 反序列化报错
- 解决立刻加分啊!!
- 想问一下关于字符串的“\”问题,请帮帮我!
- 关于DataView and DataGrid的两个问题。
- 关闭窗体时报错:未处理AccessViolationException--尝试读取或写入受保护的内存。这通常指示其他内存已损坏。
- c#程序返回错误 本机提示404 谁帮帮忙啊 非常感谢
- 如何隐藏winform中DataGrid的列
- 用SQLHelper.cs做了一个站点,发现问题多多,求好类库!
- 怎样用c#编写数学模型和算法?
DataBase db=new DataBase();
bool t=db.ExecuteDataBase(sql);
if(t){/*OK!*/}
string a=combobox.Text;
bll.addname(a);bll层:
public bool addname(string personname)
{
DAL.PersonDAL dal = new DAL.PersonDAL();
SqlCommand cmd = dal.addname(personname);
return dal.SafeExcuteResult(cmd);
}DAL层
public class DocDAL:DataBase
{
public SqlCommand addname(string personname)
{
string strSQL = "insert into person (personname) values(@personname)";
SqlCommand cmd = new SqlCommand(strSQL);
return cmd;
}
}
DataSet ds = db.GetDataSet("SELECT [PersonName] FROM [Person]");
combobox.DataSource = ds;
combobox.DataBind();
大致这样,没有IDE不知道对不对。这样写只是实现功能,但最好遵循三层架构。希望楼主能多看书,这种问题又不难
{
string sconn = "server=.;database=库名;uid=sa";
SqlConnection conn = new SqlConnection(sconn);//连接数据库 string sql = "select PersonName from Person";
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
da.Fill(ds);//DataSet查询数据库并返回结果集合 DataTable dt = ds.Tables[0];//dt接收返回结果中的第一张表
if (dt.Rows.Count > 0)//判断有无结果
{
for (int i = 0; i < dt.Rows.Count; i++)//for循环输出每一行的第一个结果
{
comboBox1.Text = dt.Rows[0][0].ToString();//comboBox1默认显示第一个数据
comboBox1.Items.Add(dt.Rows[i][0].ToString());//将每个结果加入
}
}
}DataSet 和 SqlDataAdapter 一起用
SqlDataReader 和 SqlCommand 一起用