Question

C# 怎么利用dataset输出sql 2005数据库数据

Answer 1

下面这段写的还行 你可以看看

1 连接SQL Server数据库示例
// 连接字符串
string ConnectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionSqlServer"];
// 创建SqlConnection对象
SqlConnection connection = new SqlConnection(ConnectionString);

try
{
// 打开数据库连接
connection.Open();
myLabel.Text = "连接数据库成功";
}
catch
{
myLabel.Text = "连接数据库失败";
}
finally
{
// 关闭数据库连接
connection.Close();
}
<appSettings>
<add key="ConnectionSqlServer" value="Server=(local);User id=sa;Pwd=sa;Database=Northwind"></add>
<add key="ConnectionSqlServer1" value="Server=(local);User id=sa;Pwd=sa;"></add>
<add key="ConnectionSqlServer_tempdb" value="Server=(local);User id=sa;Pwd=sa;Database=tempdb"></add>
<add key="ConnectionDB2" value="DATABASE=SAMPLE;UID=username;PWD=password"></add>
<add key="ConnectionOracle" value="Data Source=Oracle8i;Integrated Security=yes"></add>
</appSettings>
<system.web>

2 // 连接到 ACCESS 的连接字符串
string ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0; Data source=" + Server.MapPath("grocertogo.mdb");
// 使用OleDb .NET数据提供程序创建连接
OleDbConnection oleConnection = new OleDbConnection(ConnStr);
try
{
// 打开数据库连接
oleConnection.Open();
// 显示连接成功信息
myLabel.Text = "Access数据库连接状态：" + oleConnection.State;
}
catch(Exception ex)
{
// 如果出现异常，显示异常信息
myLabel.Text = "Access数据库连接状态：" + ex.ToString();
}
finally
{
// 关闭数据库连接
oleConnection.Close();
}

3 // 连接到 Oracle 数据库示例

string ORACLE_ConnStr = "Data Source=Oracle8i;Integrated Security=yes";
// 创建 OracleConnection 对象
OracleConnection oConnection = new OracleConnection(ORACLE_ConnStr);
try
{
oConnection.Open();
myLabel.Text = "连接到 Oracle 数据库";
}
catch(Exception ex)
{
myLabel.Text = ex.ToString();
}
finally
{
oConnection.Close();
}

4 SqlCommand 执行SQL命令示例
string ConnStr = System.Configuration.ConfigurationSettings.AppSettings["ConnectionSqlServer"];
// 创建SqlConnection对象
// 创建Command对象
SqlConnection thisConnection = new SqlConnection(ConnStr);
SqlCommand thisCommand = new SqlCommand();
// 关联Connection对象
// 赋值SQL语句到CommandText属性
// 指定命令类型是Sql语句
thisCommand.Connection = thisConnection;
thisCommand.CommandText = "SELECT COUNT(*) FROM Employees";
thisCommand.CommandType = CommandType.Text;
try
{
// 打开数据库连接
thisCommand.Connection.Open();
// 获取查询结果
myLabel.Text = thisCommand.ExecuteScalar().ToString();
}
catch(SqlException ex)
{
// 如果出现异常，在Label标签中显示异常信息
myLabel.Text = ex.ToString();
}
finally
{
// 关闭数据库连接
thisCommand.Connection.Close();
}

5 SqlDataReader 读取数据示例
string ConnectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionSqlServer"];
string Sql = "SELECT LastName, FirstName FROM Employees";
SqlConnection thisConnection = new SqlConnection(ConnectionString);
SqlCommand thisCommand = new SqlCommand(Sql, thisConnection);
thisCommand.CommandType = CommandType.Text;
try
{
// 打开数据库连接
thisCommand.Connection.Open();
// 执行SQL语句，并返回DataReader对象
SqlDataReader dr = thisCommand.ExecuteReader();
// 以粗体显示标题
myLabel.Text = "<b>LastName FirstName</b><br>";
// 循环读取结果集
while(dr.Read())
{
// 读取两个列值并输出到Label中
myLabel.Text += dr["LastName"] + " " + dr["FirstName"] + "<br>";
}
// 关闭DataReader
dr.Close();
}
catch(SqlException ex)
{
// 异常处理
Response.Write(ex.ToString());
}
finally
{
// 关闭数据库连接
thisCommand.Connection.Close();
}

6 使用DataAdapter填充数据到DataSet
string ConnectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionSqlServer"];
string Sql = "SELECT EmployeeID, LastName, FirstName,Title, TitleOfCourtesy, BirthDate FROM Employees";
// 创建SqlConnection对象
// 创建DataAdapter对象并初始化
SqlConnection thisConnection = new SqlConnection(ConnectionString);
SqlDataAdapter adapter = new SqlDataAdapter(Sql, thisConnection);
// 创建DataSet对象
DataSet data = new DataSet();
// 填充数据到DataSet
adapter.Fill(data);
// 数据绑定
myDataGrid.DataSource = data;
myDataGrid.DataBind();

7 使用DataTable存储数据库表内容
string ConnectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionSqlServer"];
string Sql = "SELECT EmployeeID, LastName, FirstName, BirthDate FROM Employees";
// 创建SqlConnection、SqlDataAdapter对象
SqlConnection thisConnection = new SqlConnection(ConnectionString);
SqlDataAdapter adapter = new SqlDataAdapter(Sql, thisConnection);
// 创建DataTable对象
DataTable table = new DataTable();
// 填充数据到DataTable
adapter.Fill(table);
// 将DataTable绑定到DataGrid控件
myDataGrid.DataSource = table;
myDataGrid.DataBind();

8 将数据库数据填充到 XML 文件
// 连接字符串及 SQL 语句
string ConnString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionSqlServer"];
string Sql = "SELECT CustomerID,CompanyName,Country FROM Customers";
// 连接 SqlConnection 及 SqlDataAdapter 对象
SqlConnection thisConnection = new SqlConnection(ConnString);
SqlDataAdapter adapter = new SqlDataAdapter(Sql, thisConnection);
// 创建 DataSet 对象
DataSet data = new DataSet();
// 填充 DataSet
adapter.Fill(data, "Customers");
// 将 DataSet 数据其及架构填充到 Xml 文件
data.WriteXml(Server.MapPath(".") + "\\myXml.xml", XmlWriteMode.WriteSchema);

// 提示填充是否成功
Label1.Text = "填充到XML文件成功";

9 ASP.NET 使用存储过程
// 连接字符串
string ConnStr = System.Configuration.ConfigurationSettings.AppSettings["ConnectionSqlServer"];
// 创建Connection对象
SqlConnection myConn = new SqlConnection(ConnStr);
// 创建Command对象并和Connection对象关联
SqlCommand myCommand = new SqlCommand();
myCommand.Connection = myConn;

// 指定要执行的存储过程名称
myCommand.CommandText = "CustomersProc";
// 使用要执行的是存储过程
myCommand.CommandType = CommandType.StoredProcedure;
// 创建DataAdapter对象填充数据
DataSet myDS = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(myCommand);
adapter.Fill(myDS, "Customers");
// 将返回的数据和DataGrid绑定显示
myDataGrid.DataSource = myDS.Tables["Customers"];
myDataGrid.DataBind();

10 使用带输入参数的存储过程
string ConnStr = System.Configuration.ConfigurationSettings.AppSettings["ConnectionSqlServer"];
// 创建数据库操作对象
SqlDataAdapter myAdapter = new SqlDataAdapter();
SqlCommand myCommand = new SqlCommand();
myCommand.Connection = new SqlConnection(ConnStr);
DataTable dt = new DataTable();
// 指定要调用的存储过程名称 "Customer_Select"
// 指定SqlCommand对象的命令类型为 "StoredProcedure"枚举值
myCommand.CommandText = "Customer_Select";
myCommand.CommandType = CommandType.StoredProcedure;
// 创建SqlParameter对象，指定参数名称、数据类型、长度及参数值
SqlParameter para = new SqlParameter("@country", SqlDbType.NVarChar, 15);
para.Value = DropDownList1.SelectedValue;

myCommand.Parameters.Add(para);
// 关联SqlDataAdapter与SqlCommand对象
myAdapter.SelectCommand = myCommand;
myAdapter.Fill(dt);
// 绑定DataGrid
DataGrid1.DataSource = dt;
DataGrid1.DataBind();

11 使用带输入、输出参数的存储过程示
string ConnStr = System.Configuration.ConfigurationSettings.AppSettings["ConnectionSqlServer"];
// 创建 Connection 和 Command 对象
SqlConnection myConn = new SqlConnection(ConnStr);
SqlCommand myCommand = new SqlCommand("EmployeesProc", myConn);
// 指定要执行的命令为存储过程
myCommand.CommandType = CommandType.StoredProcedure;
// 增加输入参数并赋值
myCommand.Parameters.Add("@TitleOfCourtesy", SqlDbType.NVarChar, 20);
myCommand.Parameters["@TitleOfCourtesy"].Value = myDropDownList.SelectedItem.Text;
myCommand.Parameters["@TitleOfCourtesy"].Direction = ParameterDirection.Input;
// 增加输出参数
myCommand.Parameters.Add("@empCount", SqlDbType.Int);
myCommand.Parameters["@empCount"].Direction = ParameterDirection.Output;
// 创建 DataAdapter 对象填充数据
DataSet myDS = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(myCommand);
adapter.Fill(myDS, "Customers");
// 使用 Label 控件显示输出参数的输出值
rtnLabel.Text = myCommand.Parameters["@empCount"].Value.ToString();
// 将返回的数据和 DataGrid 绑定显示
myDataGrid.DataSource = myDS.Tables["Customers"];
myDataGrid.DataBind();

12 获得数据库中表的数目和名称 获取服务器端数据库列表
string listQuery = "SELECT name FROM sysobjects WHERE xtype = 'U'";
string sumQuery = "SELECT COUNT(*) FROM sysobjects WHERE xtype = 'U'

string db_query = "sp_helpdb";

13 保存图片到SQL Server数据库示例
// HttpPostedFile对象，用于读取图象文件属性
HttpPostedFile UpFile = UP_FILE.PostedFile;
// FileLength 变量存储图片的字节大小
int FileLength = UpFile.ContentLength;
try
{
if (FileLength == 0)
{
txtMessage.Text = "<b>您未选择上传的文件</b>";
}
else
{
// 创建存储图片文件的临时 Byte 数组
Byte[] FileByteArray = new Byte[FileLength];
// 建立数据流对象
Stream StreamObject = UpFile.InputStream;

// 读取图象文件数据，FileByteArray为数据储存体，0为数据指针位置、FileLnegth为数据长度
StreamObject.Read(FileByteArray,0,FileLength);
// 数据库操作
string ConnStr = System.Configuration.ConfigurationSettings.AppSettings["ConnectionSqlServer"];
string query = "INSERT INTO ImageTable (ImageData, ImageContentType, ImageDescription, ImageSize) VALUES (@ImageData, @ImageContentType, @ImageDescription, @ImageSize)";
SqlCommand myCommand = new SqlCommand(query, new SqlConnection(ConnStr));
// 添加各项参数并赋值
myCommand.Parameters.Add("@ImageData", SqlDbType.Image);
myCommand.Parameters.Add("@ImageContentType", SqlDbType.VarChar, 50);
myCommand.Parameters.Add("@ImageDescription", SqlDbType.VarChar, 200);
myCommand.Parameters.Add("@ImageSize", SqlDbType.BigInt);
myCommand.Parameters["@ImageData"].Value = FileByteArray;
myCommand.Parameters["@ImageContentType"].Value = UpFile.ContentType;
myCommand.Parameters["@ImageDescription"].Value = txtDescription.Text;
myCommand.Parameters["@ImageSize"].Value = FileLength;

// 执行数据库操作
myCommand.Connection.Open();
myCommand.ExecuteNonQuery();
myCommand.Connection.Close();
// 提示上传成功
txtMessage.Text = "<b>上传文件成功</b>";
}
}
catch (Exception ex)
{
// 使用 Label 标签显示异常
txtMessage.Text = ex.Message.ToString();
}

14 获得插入记录标识号的示例
// 数据库连接字符串
string ConnStr = System.Configuration.ConfigurationSettings.AppSettings["ConnectionSqlServer"];
// 创建插入SQL语句及调用@@identity函数返回标识值
string insert_query = "insert into Categories (CategoryName,Description) values ('IT', 'Internet');"
+ "SELECT @@identity AS 'identity';";
// 执行数据库操作
SqlCommand myCommand = new SqlCommand(insert_query, new SqlConnection(ConnStr));
myCommand.Connection.Open();
myLabel.Text = myCommand.ExecuteScalar().ToString();
myCommand.Connection.Close();
15 如何读取Excel表格中的数据
// 获取Excep文件的完整路径
string source = File1.Value;
string ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + source + ";Extended Properties=Excel 8.0";
string query = "SELECT * FROM [Sheet1$]";
OleDbCommand oleCommand = new OleDbCommand(query, new OleDbConnection(ConnStr));
OleDbDataAdapter oleAdapter = new OleDbDataAdapter(oleCommand);
DataSet myDataSet = new DataSet();
// 将 Excel 的[Sheet1]表内容填充到 DataSet 对象
oleAdapter.Fill(myDataSet, "[Sheet1$]");
// 数据绑定
DataGrid1.DataSource = myDataSet;
DataGrid1.DataMember = "[Sheet1$]";
DataGrid1.DataBind();

Answer 2

额。。。如果你用的是sqlserver 那么直接引用命名空间：using System.Data.SqlClient;
如果你是oracle 先添加引用--》using System.data.OracleClient；然后在代码添上using System.data.OracleClient；
然后呢就简单sql、语句问题了。你可以找着方面的，如果你要最简单的可以参考我的代码：
//增删查改
//string sql = "";
//SqlConnection cn=new SqlConnection("连接字符串");
//sql = "insert into table (字段1，字段2) values ('xx','aa')";//增
//sql = "delete from table where table_id ='" + ID + "'";//删
//sql = "select 字段1,字段2 from table where 条件";//查
//sql = "update table set 字段1='',字段2='' where table_id='" + ID + "' ";//改
//SqlDataAdapter da = new SqlDataAdapter(sql, cn);
//DataSet ds = new DataSet();
//da.Fill(ds);// 这样就执行了
最简单的例子了,适合菜鸟使用，。。纯手打~！ 当然你是用datagridview之类的绑定select检索出来的数据，你就在上面的代码后面datagridview.datasource=ds.table[0].DefaultView;
如果是winfrom就直接ds.table["你自己定义的表名，如果没有就写0"].rows[行][列].tostring();
webfrom就直接写个属性在页面，然后直接在gridview那里定义keyname 然后就写控件，getkeyXX...这样写效率高

Answer 3

比如
界面有一个textbox控件

你获得了 dataset
dataset ds=getds();
给前台控件赋值
this.TextBox1.text=ds.Tables[0].Rows[0][0].toString()
Rows[0] //ds中的第一行
Rows[0][0] //ds中的第一行第一列
也可以这样
Rows[1]["name"] //ds中第二行 列名为name的值