我以前用的是另外一个DBHelper类,前天自己写了一个类,但是为什么就一直读不出数据。。我觉得是我的方法有错,大家帮我写一下 查询数据的方法,我来看看我错在哪里。比如:string sql="."+"Northwind"+"sa"+"123456"; 写一个查询任意一张表数据的方法吧,多谢!
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;namespace ExeciseProject.DAL
{
public class DBHelper
{
private SqlConnection conn;
private SqlCommand cmd;
private SqlDataAdapter da;
private DataSet ds; private string myServer;
private string myDatabase;
private string myUid;
private string myPwd;
private string myStrConn;
public DBHelper(string server, string database, string uid, string pwd)
{
//获取连接数据库语句的各个属性的值
this.myServer = server;
this.myDatabase = database;
this.myUid = uid;
this.myPwd = pwd;
try
{
conn = new SqlConnection(getStrConn());
}
catch
{
return null;
} } #region 封装连接数据库语句的属性 public string MyServer
{
//服务器地址
get { return myServer; }
set { myServer = value; }
} public string MyDatabase
{
//数据库
get { return myDatabase; }
set { myDatabase = value; }
} public string MyUid
{
//用户名
get { return myUid; }
set { myUid = value; }
} public string MyPwd
{
// 密码
get { return myPwd; }
set { myPwd = value; }
}
#endregion #region 组合数据库连接语句
private string getStrConn()
{
//组合连接数据库语句
try
{
connOpen();
myStrConn = "server=" + MyServer + ";database=" + MyDatabase + ";uid=" + MyUid + ";pwd=" + MyPwd;
connClose();
}
catch
{
return null;
} return myStrConn;
}
#endregion #region 打开数据库 private bool connOpen()
{
try
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
}
catch
{
return false;
} return true; }
#endregion #region 关闭数据库 private bool connClose()
{
try
{
if(conn.State == ConnectionState.Open)
{
conn.Close();
}
}
catch
{
return false;
} return true;
}
#endregion #region 读取数据 public DataSet getDs(string strSelect,string tempTable)
{
// 读取数据库数据
try
{
da=new SqlDataAdapter(strSelect,conn);
ds = new DataSet();
da.Fill(ds,tempTable);
conn.Close;
}
catch
{
return null;
} return ds.Tables[0];
}
#endregion #region 写入数据 public bool setDb(string sql )
{
// 向数据库写入数据
try
{
if (conn.Open())
{
cmd = new SqlCommand(sql,conn);
da=cmd.ExecuteNonQuery();
connClose();
}
}
catch
{
return false;
} return true; }
#endregion
}}
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;namespace ExeciseProject.DAL
{
public class DBHelper
{
private SqlConnection conn;
private SqlCommand cmd;
private SqlDataAdapter da;
private DataSet ds; private string myServer;
private string myDatabase;
private string myUid;
private string myPwd;
private string myStrConn;
public DBHelper(string server, string database, string uid, string pwd)
{
//获取连接数据库语句的各个属性的值
this.myServer = server;
this.myDatabase = database;
this.myUid = uid;
this.myPwd = pwd;
try
{
conn = new SqlConnection(getStrConn());
}
catch
{
return null;
} } #region 封装连接数据库语句的属性 public string MyServer
{
//服务器地址
get { return myServer; }
set { myServer = value; }
} public string MyDatabase
{
//数据库
get { return myDatabase; }
set { myDatabase = value; }
} public string MyUid
{
//用户名
get { return myUid; }
set { myUid = value; }
} public string MyPwd
{
// 密码
get { return myPwd; }
set { myPwd = value; }
}
#endregion #region 组合数据库连接语句
private string getStrConn()
{
//组合连接数据库语句
try
{
connOpen();
myStrConn = "server=" + MyServer + ";database=" + MyDatabase + ";uid=" + MyUid + ";pwd=" + MyPwd;
connClose();
}
catch
{
return null;
} return myStrConn;
}
#endregion #region 打开数据库 private bool connOpen()
{
try
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
}
catch
{
return false;
} return true; }
#endregion #region 关闭数据库 private bool connClose()
{
try
{
if(conn.State == ConnectionState.Open)
{
conn.Close();
}
}
catch
{
return false;
} return true;
}
#endregion #region 读取数据 public DataSet getDs(string strSelect,string tempTable)
{
// 读取数据库数据
try
{
da=new SqlDataAdapter(strSelect,conn);
ds = new DataSet();
da.Fill(ds,tempTable);
conn.Close;
}
catch
{
return null;
} return ds.Tables[0];
}
#endregion #region 写入数据 public bool setDb(string sql )
{
// 向数据库写入数据
try
{
if (conn.Open())
{
cmd = new SqlCommand(sql,conn);
da=cmd.ExecuteNonQuery();
connClose();
}
}
catch
{
return false;
} return true; }
#endregion
}}
getStrConn()这个方法是获取链接字符串的吧,不明白你先打开一次链接,又把它关闭是干什么用的
大家看看这个方法读取数据有没问题using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;namespace ExeciseProject.DAL
{
public class TestService
{
/// <summary>
/// 查询数据库表的数据
/// </summary>
public DataTable getTable(int num)
{
//查询数据库表的数据
int i=10; // 控制每页显示数据的行数
DataTable dt;
try
{
DBHelper db = new DBHelper(".", "Northeind", "sa", "123456");
string sql = "select top " + i + " * from Products where ProductID not in (select top " + ((num - 1) * i) + " ProductID from Products)";
if (i <= 0)
{
i = 1;
}
dt = db.getDs(sql,"table1");
}
catch
{
return null;
} return dt;
}
}
}
还有你的构造函数中能用return 的吗?
setDb方法中if(conn.Open())和da=cmd.ExecuteNonQuery();
能编译通过??
我组合字符串那里是为了用来打开需要用的数据库的 那里没问题 只是把后面的关闭去了就可以了
你们看看其他地方有神门问题
1 构造函数不能返回值
改为
public DBHelper(string server, string database, string uid, string pwd)
{
//获取连接数据库语句的各个属性的值
this.myServer = server;
this.myDatabase = database;
this.myUid = uid;
this.myPwd = pwd;
}2 连接字符串没有准备好就进行数据库连接,一定失败
另外,getStrConn函数就做返回连接字符串,不要做其它
private string getStrConn()
{
//组合连接数据库语句
try
{
connOpen();
myStrConn = "server=" + MyServer + ";database=" + MyDatabase + ";uid=" + MyUid + ";pwd=" + MyPwd;
connClose();
}
catch
{
return null;
} return myStrConn;
}
改为 private string getStrConn()
{
//组合连接数据库语句
myStrConn = "server=" + MyServer + ";database=" + MyDatabase + ";uid=" + MyUid + ";pwd=" + MyPwd; return myStrConn;
}3 打开连接时进行初始化
private bool connOpen()
{
try
{
if(null== conn)
conn=new SqlConnection(getStrConn());
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
}
catch
{
return false;
} return true; }4 用完数据库连接后,要尽快关闭。
myStrConn = "server=" + MyServer + ";database=" + MyDatabase + ";uid=" + MyUid + ";pwd=" + MyPwd;
也可查询web.config,配置config文件,查询连接字符串。可参考petshop,dot bbs