连接到MySQL数据库用C# - 需要一些与数据集 [英] Connecting to a Mysql DB with C# - Need some with Datasets
问题描述
修改
我显然不知道如何做到这一点的权利。被提供的例子之后,我决定打的书籍多一点,并尝试给出的例子去解决它。
感谢您。
修改结束
我想连接到我的MySQL数据库,读取表/行,并把它们写入控制台。
是这个代码是否正确?
我得到Visual Studio 2005中的数据错误。
代码不是我的,从网上得到它。我只是修改了一点(变量名等等)。
如果你有一个很好的教程要做到这一点,请张贴的链接。 =)
/ *执行使用ADO.NET * /
#地区使用指令
SELECT语句
使用系统;
使用System.Data这;
使用System.Data.SqlClient的;
使用MySql.Data.MySqlClient;
#endregion
命名空间testConnect1
{
类SQLTEST1
{
静态无效的主要()
{
字符串的connectionString =服务器=本地主机的用户id = root密码=空白数据库=测试1; //连接字符串
的SqlConnection的MySqlConnection =新的SqlConnection(的connectionString); //创建连接
串selectString =选择field01,field02,field03+FROM myDataTable //选择要被访问的
的SqlCommand的MySqlCommand = mySqlConnection.CreateCommand字段();
mySqlCommand.CommandText = selectString;
SqlDataAdapter的MySqlDataAdapter的=新的SqlDataAdapter();
mySqlDataAdapter.SelectCommand =的MySqlCommand;
的DataSet test1DataSet =新的DataSet(); //创建一个数据集
mySqlConnection.Open(); //打开连接
Console.WriteLine(检索行从测试表);
串dataTableName =myDataTable;
mySqlDataAdapter.Fill(test1DataSet,dataTableName);
的DataTable myDataTable = test1DataSet.Tables [myDataTable] //我在这里得到一个错误
的foreach(DataRow的myDataRow在myDataTable.Rows)//迭代行表
{
//Console.WriteLine( field01)= + myDataRow [(field01)]; //我不得不注释掉这一地区也因为得到一个错误,但这不是我的疑问,现在
//Console.WriteLine(\"Field02)= + myDataRow [(field02)];
//Console.WriteLine(\"Field03)= + myDataRow [(field03)];
}
mySqlConnection.Close(); //关闭连接
}
}
}
下面是你应该遵循纠正你的方法的错误一个简单的例子:
SQL的东西
$如果不存在用户b $ b
删除表;
创建表的用户
(
的user_id INT无符号NOT NULL的auto_increment主键,
的用户名VARBINARY(32)唯一不为空
)
发动机= InnoDB的;
INSERT INTO的用户(用户名)VALUES('F00'),('巴');
C#DataAdapter的方法
请注意,我不明确地打开数据库连接 - 在DataAdpater确实对我来说。
使用系统;
使用System.Collections.Generic;
使用System.Linq的;
使用System.Text;
// addded使用MySql.Data这些
;
使用MySql.Data.MySqlClient;
使用System.Data这;
命名空间的mysql
{
类节目
{
静态无效的主要(字串[] args)
{
常量字符串DB_CONN_STR =服务器= 127.0.0.1; UID = foo_dbo; PWD =传递;数据库= foo_db;;
的MySqlConnection CN =新的MySqlConnection(DB_CONN_STR);
尝试{
串SQLCMD =由USER_ID SELECT * FROM用户命令;
MySqlDataAdapter的ADR =新MySqlDataAdapter的(SQLCMD,CN);
adr.SelectCommand.CommandType = CommandType.Text;
DataTable的DT =新的DataTable();
adr.Fill(DT); //打开并自动关闭数据库连接! (从池中获取)
的foreach(在dt.Rows的DataRow DR){
Console.WriteLine(的String.Format(USER_ID = {0},博士[user_ID的]。的ToString()));
}
}
赶上(异常前)
{
Console.WriteLine({哎呀 - {0},ex.Message);
}
终于
{
cn.Dispose(); //返回连接池
}
Console.WriteLine(按任意键...);
Console.ReadKey();
}
}
}
C#DataReader的例子
使用系统;
使用System.Collections.Generic;
使用System.Linq的;
使用System.Text;
// addded使用MySql.Data这些
;
使用MySql.Data.MySqlClient;
使用System.Data这;
命名空间的mysql
{
类节目
{
静态无效的主要(字串[] args)
{
常量字符串DB_CONN_STR =服务器= 127.0.0.1; UID = foo_dbo; PWD =传递;数据库= foo_db;;
的MySqlConnection CN =新的MySqlConnection(DB_CONN_STR);
尝试{
串SQLCMD =由USER_ID SELECT * FROM用户命令;
cn.Open(); //必须明确地打开连接(从池中获取)
的MySqlCommand CMD =新的MySqlCommand(SQLCMD,CN);
cmd.CommandType = CommandType.Text;
MySqlDataReader将RDR = cmd.ExecuteReader();
,而(rdr.Read()){
Console.WriteLine(的String.Format(USER_ID = {0},RDR [user_ID的]的ToString()));
}
}
赶上(异常前)
{
Console.WriteLine({哎呀 - {0},ex.Message);
}
终于
{
cn.Dispose(); //返回连接池
}
Console.WriteLine(按任意键...);
Console.ReadKey();
}
}
}
Edit
I clearly do not understand how to do this right. After the examples were provided, I've decided to hit the books a bit more, and try to work it out with the examples given.
Thank you.
End of Edit
I want to connect to my mySql DB, read the table/rows, and write them to the console. Is this code correct? I get the dataset error in Visual Studio 2005.
Code is not mine, got it from the web. I just modified it a little (variable names and such).
If you have a good tutorial to do this, please post the link. =)
/* Performing a SELECT statement using ADO.NET */
#region Using directives
using System;
using System.Data;
using System.Data.SqlClient;
using MySql.Data.MySqlClient;
#endregion
namespace testConnect1
{
class SqlTest1
{
static void Main()
{
string connectionString = "server = localhost user id = root Password = blank database = test1"; //connection string
SqlConnection mySqlConnection = new SqlConnection(connectionString); //creates connection
string selectString = "Select field01, field02, field03 " + "FROM myDataTable"; //selects fields to be accessed
SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
mySqlCommand.CommandText = selectString;
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
mySqlDataAdapter.SelectCommand = mySqlCommand;
DataSet test1DataSet = new DataSet(); //creates data set
mySqlConnection.Open(); // opens connection
Console.WriteLine("Retrieving rows from the test table");
string dataTableName = "myDataTable";
mySqlDataAdapter.Fill(test1DataSet, dataTableName);
DataTable myDataTable = test1DataSet.Tables[myDataTable]; //i get an error here
foreach (DataRow myDataRow in myDataTable.Rows) //iterates over rows in table
{
//Console.WriteLine("Field01") = + myDataRow[("field01")]; // i had to comment out this region because also get an error, but this is not my doubt right now
//Console.WriteLine("Field02") = + myDataRow[("field02")];
//Console.WriteLine("Field03") = + myDataRow[("field03")];
}
mySqlConnection.Close(); //close connection
}
}
}
Here's a simple example which you should follow to correct the mistakes in your approach:
SQL stuff
drop table if exists users;
create table users
(
user_id int unsigned not null auto_increment primary key,
username varbinary(32) unique not null
)
engine=innodb;
insert into users (username) values ('f00'),('bar');
C# DataAdapter method
Note I dont explicitly open the db connection - the DataAdpater does that for me.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
// addded these
using MySql.Data;
using MySql.Data.MySqlClient;
using System.Data;
namespace mysql
{
class Program
{
static void Main(string[] args)
{
const string DB_CONN_STR = "Server=127.0.0.1;Uid=foo_dbo;Pwd=pass;Database=foo_db;";
MySqlConnection cn = new MySqlConnection(DB_CONN_STR);
try {
string sqlCmd = "select * from users order by user_id";
MySqlDataAdapter adr = new MySqlDataAdapter(sqlCmd, cn);
adr.SelectCommand.CommandType = CommandType.Text;
DataTable dt = new DataTable();
adr.Fill(dt); //opens and closes the DB connection automatically !! (fetches from pool)
foreach (DataRow dr in dt.Rows){
Console.WriteLine(string.Format("user_id = {0}", dr["user_id"].ToString()));
}
}
catch (Exception ex)
{
Console.WriteLine("{oops - {0}", ex.Message);
}
finally
{
cn.Dispose(); // return connection to pool
}
Console.WriteLine("press any key...");
Console.ReadKey();
}
}
}
C# DataReader example
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
// addded these
using MySql.Data;
using MySql.Data.MySqlClient;
using System.Data;
namespace mysql
{
class Program
{
static void Main(string[] args)
{
const string DB_CONN_STR = "Server=127.0.0.1;Uid=foo_dbo;Pwd=pass;Database=foo_db;";
MySqlConnection cn = new MySqlConnection(DB_CONN_STR);
try {
string sqlCmd = "select * from users order by user_id";
cn.Open(); // have to explicitly open connection (fetches from pool)
MySqlCommand cmd = new MySqlCommand(sqlCmd, cn);
cmd.CommandType = CommandType.Text;
MySqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read()){
Console.WriteLine(string.Format("user_id = {0}", rdr["user_id"].ToString()));
}
}
catch (Exception ex)
{
Console.WriteLine("{oops - {0}", ex.Message);
}
finally
{
cn.Dispose(); // return connection to the pool
}
Console.WriteLine("press any key...");
Console.ReadKey();
}
}
}
这篇关于连接到MySQL数据库用C# - 需要一些与数据集的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!