Question

急 .net 读取SQL SERVER中数据并显示在页面上

用ADO连接的 如何读取指定表内的数据并显示在页面上 ？

Answer 1

恩 ，用 ADO.NET 技术来把数据库里面的数据显示到控件上，具体如下：

如果楼主擅长DataGridView和ListView控件中，写了以下几小段代码：

DataGridView 控件：

{
string sql="select * from Uses"; // 定义的Sql 语句 查询数据库中 User表的信息
SqlConnection conn=new SqlConnection("server=.;database=My;integrated security=sspi"); //定义数据连接，、
//server=. 的意思 连接到自己的数据库
//database=My 的意思 连接自己的数据库 数据库的名字是My
//integrated security=sspi 的意思 是用WIndows 身份登陆数据库

SqlAdapter sda=new SqlAdatper (sql,conn); //定义适配器
DataSet ds=new DataSet (); //定义数据集
sda.Fill(ds,"User"); //填充数据集 把读取到的数据库里面的信息 填充到DateSet 数据集的User表中
dataGridView1.Datasource=ds.Table["User"]; //指定控件的数据源
// dataGridView1.Datasource 是控件的Datasource 数据源的意思
//ds.Table["User"] 的意思是 表User表中已经存在的数据库里面的信息

}

下面的是 ListView控件的 一小段代码：

{
string sql="select * from User"; //还拿上面的User表来读取
SqlConnection conn=new SqlConnection("server=.;database=My;integrated security=sspi"); //还是定义数据库连接
SqlCommand cmd=new SqlCommand(sql,conn); //定义Command 命令 这个Command 命令是 ADO.NET技术中重要的一部分 希望楼主会注意看下哈！
SqlDataReader sdr=cmd.ExecuteReader(); //这个SqlDataReader也是ADO.Net技术中的一部分 cmd.ExecuteReder(); 这句是Command命令的执行命令的一部分也是要背记下来的
while(sdr.Read())
{
ListViewItem lvi=new ListViewItem(sdr["Name"].ToString()); //新建一个ListViewItem 项 sdr["Name"].ToString() 是读取数据库中的一条信息 名为Name的列的信息
lvi.SubItems.Add(sdr["age"].ToString()); //给LIstView控件再添加子项（即 第二个项 ） sdr["age"].ToString() 读取数据库中的一条信息 为AGe的列的信息

listView1.Items.Add(lvi); // 把添加完成的信息填充到ListView控件中去
listView1.Items.Add是要记住的
}
sdr.Close(); //这步要记住 需要关闭SqlDataReader的连接
conn.Close(); //关闭数据库连接 也非常重要的 ！
}

以上是应用比较多的控件 DataGridView和ListView控件 的实现过程

希望对楼主有所帮助！

希望楼主仔细看下哈 有不明白的地方可以给我留言哦！

祝楼主编程之路顺利！

Answer 2

用datagrid和datalist都行。。

SqlConnection con=new SqlConnection("server=.;database=数据库名称;uid=数据库用户名;pwd=数据库密码");
string sql="select * from 指定表";
SqlDataAdapter ada=new SqlDataAdapter(sql,con);
DataSet ds=new DataSet();
ada.Fill(ds);
DataGrid1.DataSource=ds;
DataGrid1.DataBind();..
回答者： sjyh5201 - 魔法师 五级 11-24 11:19
public class DBHelper
{
SqlConnection conn = null;
string connStr = ConfigurationManager.ConnectionStrings["News"].ConnectionString;

public DBHelper()//构造方法中定义连接
{
conn = new SqlConnection(connStr);
}

public int ExecutBySql(string sql) //执行增删改操作都可以
{
SqlCommand com = new SqlCommand(sql, conn);
try
{
conn.Open();
return com.ExecuteNonQuery();
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
throw ex;
}
finally
{
conn.Close();
}
}

public DataTable SelectBySql(string sql)//执行查找操作
{
SqlDataAdapter adp = new SqlDataAdapter(sql, conn);
DataSet ds = new DataSet();
try
{
adp.Fill(ds);
return ds.Tables[0];
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
throw ex;
}
}
}

public class ArticleService
{
DBHelper help = new DBHelper();
public int AddArticle(Article a)
{
string sql = "insert into articles values('" + a.Title + "','" + a.Contents + "',getdate(),0,0,getdate(),1,"+a.PId+")";
return help.ExecutBySql(sql);
}
public int UpdateArticleById(Article a)
{
string sql = "update articles set title = '"+a.Title+"',contents = '"+a.Contents+"' where articleId = "+a.ArticleId;
return help.ExecutBySql(sql);
}
public List<Article> SelectArticleBySql(string sql)
{
DataTable dt = help.SelectBySql(sql);
List<Article> list = new List<Article>();
foreach (DataRow dr in dt.Rows)
{
Article a = new Article();
a.ArticleId = (int)dr["ArticleId"];
a.Title = (string) dr["Title"];
a.Contents = (string) dr["contents"];
a.Click = (int)dr["click"];
a.ArticleDate = (DateTime)dr["articleDate"];
a.IsTop = (int)dr["isTop"];
a.TopDate = (DateTime) dr["topdate"];
a.Enable = (int)dr["enable"];
a.PId = (int)dr["pid"];
list.Add(a);
}
return list;
}
public List<Article> SelectAllArticle(string pid)
{
string sqlIsTop = "select * from articles where enable = 1 and istop = 1 and pid = " + pid + " order by topDate desc";
List<Article> listAll = SelectArticleBySql(sqlIsTop);
string sql = "select * from articles where enable = 1 and istop = 0 and pid = "+pid+" order by articleDate desc";
List<Article> list = SelectArticleBySql(sql);
foreach(Article a in list)
{
listAll.Add(a);
}
return listAll;
}
public Article SelectArticleById(int id)
{
string sql = "select * from Articles where ArticleId = "+id;
DataTable dt = help.SelectBySql(sql);
Article a = new Article();
foreach (DataRow dr in dt.Rows)
{
a.ArticleId = (int)dr["ArticleId"];
a.Title = (string)dr["Title"];
a.Contents = (string)dr["contents"];
a.ArticleDate = DateTime.Parse(dr["articleDate"].ToString());
a.Click = (int)dr["click"];
a.IsTop = (int)dr["isTop"];
a.TopDate = (DateTime)dr["topdate"];
a.Enable = (int)dr["enable"];
a.PId = (int)dr["pid"];
}
return a;
}
public int DelArticleById(string id)
{
string sql = "update articles set enable = 0 where articleId = "+id;
return help.ExecutBySql(sql);
}
public int SetTop(string id, string istop)
{
string sql = "update articles set istop = "+istop+",topDate = getdate() where articleId = " + id;
return help.ExecutBySql(sql);

加油

Answer 3

public class DBHelper
{
SqlConnection conn = null;
string connStr = ConfigurationManager.ConnectionStrings["News"].ConnectionString;

public DBHelper()//构造方法中定义连接
{
conn = new SqlConnection(connStr);
}

public int ExecutBySql(string sql) //执行增删改操作都可以
{
SqlCommand com = new SqlCommand(sql, conn);
try
{
conn.Open();
return com.ExecuteNonQuery();
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
throw ex;
}
finally
{
conn.Close();
}
}

public DataTable SelectBySql(string sql)//执行查找操作
{
SqlDataAdapter adp = new SqlDataAdapter(sql, conn);
DataSet ds = new DataSet();
try
{
adp.Fill(ds);
return ds.Tables[0];
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
throw ex;
}
}
}

public class ArticleService
{
DBHelper help = new DBHelper();
public int AddArticle(Article a)
{
string sql = "insert into articles values('" + a.Title + "','" + a.Contents + "',getdate(),0,0,getdate(),1,"+a.PId+")";
return help.ExecutBySql(sql);
}
public int UpdateArticleById(Article a)
{
string sql = "update articles set title = '"+a.Title+"',contents = '"+a.Contents+"' where articleId = "+a.ArticleId;
return help.ExecutBySql(sql);
}
public List<Article> SelectArticleBySql(string sql)
{
DataTable dt = help.SelectBySql(sql);
List<Article> list = new List<Article>();
foreach (DataRow dr in dt.Rows)
{
Article a = new Article();
a.ArticleId = (int)dr["ArticleId"];
a.Title = (string) dr["Title"];
a.Contents = (string) dr["contents"];
a.Click = (int)dr["click"];
a.ArticleDate = (DateTime)dr["articleDate"];
a.IsTop = (int)dr["isTop"];
a.TopDate = (DateTime) dr["topdate"];
a.Enable = (int)dr["enable"];
a.PId = (int)dr["pid"];
list.Add(a);
}
return list;
}
public List<Article> SelectAllArticle(string pid)
{
string sqlIsTop = "select * from articles where enable = 1 and istop = 1 and pid = " + pid + " order by topDate desc";
List<Article> listAll = SelectArticleBySql(sqlIsTop);
string sql = "select * from articles where enable = 1 and istop = 0 and pid = "+pid+" order by articleDate desc";
List<Article> list = SelectArticleBySql(sql);
foreach(Article a in list)
{
listAll.Add(a);
}
return listAll;
}
public Article SelectArticleById(int id)
{
string sql = "select * from Articles where ArticleId = "+id;
DataTable dt = help.SelectBySql(sql);
Article a = new Article();
foreach (DataRow dr in dt.Rows)
{
a.ArticleId = (int)dr["ArticleId"];
a.Title = (string)dr["Title"];
a.Contents = (string)dr["contents"];
a.ArticleDate = DateTime.Parse(dr["articleDate"].ToString());
a.Click = (int)dr["click"];
a.IsTop = (int)dr["isTop"];
a.TopDate = (DateTime)dr["topdate"];
a.Enable = (int)dr["enable"];
a.PId = (int)dr["pid"];
}
return a;
}
public int DelArticleById(string id)
{
string sql = "update articles set enable = 0 where articleId = "+id;
return help.ExecutBySql(sql);
}
public int SetTop(string id, string istop)
{
string sql = "update articles set istop = "+istop+",topDate = getdate() where articleId = " + id;
return help.ExecutBySql(sql);
}
}

参照这2个类

Answer 4

用datagrid和datalist都行。。

SqlConnection con=new SqlConnection("server=.;database=数据库名称;uid=数据库用户名;pwd=数据库密码");
string sql="select * from 指定表";
SqlDataAdapter ada=new SqlDataAdapter(sql,con);
DataSet ds=new DataSet();
ada.Fill(ds);
DataGrid1.DataSource=ds;
DataGrid1.DataBind();..

Answer 5

用GridView
SqlConnection con = new SqlConnection();
SqlCommand cmd = new SqlCommand();
try
{
con.ConnectionString = "Server=(local);Database=数据库名;Integrated Security=SSPI";
con.Open();
cmd.CommandText = "select * from 表名";
cmd.Connection = con;

SqlDataReader reader = cmd.ExecuteReader();
this.GridView1.DataSource = reader;
this.GridView1.DataBind();

}
catch (Exception ex)
{
Result.Text = ex.Message;
}
finally
{
con.Close();
}
这是.NET中初学者最常用的套用式代码