Q96BAp69

1Connecting to a Mysql DB with C# - Need some with Datasets
2/* Performing a SELECT statement using ADO.NET */
3#region Using directives
4
5using System;
6using System.Data;
7using System.Data.SqlClient;
8using MySql.Data.MySqlClient;
9
10
11
12
13namespace testConnect1
14{
15    class SqlTest1
16    {
17    static void Main()
18
19    {
20        string connectionString = "server = localhost user id = root  Password = blank  database = test1"; //connection string
21
22
23        SqlConnection mySqlConnection = new SqlConnection(connectionString);  //creates connection
24
25        string selectString = "Select field01, field02, field03 " + "FROM myDataTable";  //selects fields to be accessed
26
27        SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
28
29        mySqlCommand.CommandText = selectString;
30
31        SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
32
33        mySqlDataAdapter.SelectCommand = mySqlCommand;
34
35        DataSet test1DataSet = new DataSet();  //creates data set
36
37        mySqlConnection.Open();   // opens connection
38
39        Console.WriteLine("Retrieving rows from the test table");
40
41        string dataTableName = "myDataTable";
42        mySqlDataAdapter.Fill(test1DataSet, dataTableName); 
43
44        DataTable myDataTable = test1DataSet.Tables[myDataTable];  //i get an error here
45
46        foreach (DataRow myDataRow in myDataTable.Rows)  //iterates over rows in table
47
48    {
49
50        //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
51        //Console.WriteLine("Field02") = + myDataRow[("field02")];
52        //Console.WriteLine("Field03") = + myDataRow[("field03")];
53
54    }
55
56
57        mySqlConnection.Close();  //close connection
58
59    }
60}
61	
62drop table if exists users;
63create table users
64(
65user_id int unsigned not null auto_increment primary key,
66username varbinary(32) unique not null
67)
68engine=innodb;
69
70insert into users (username) values ('f00'),('bar');
71	
72using System;
73using System.Collections.Generic;
74using System.Linq;
75using System.Text;
76
77// addded these
78using MySql.Data;
79using MySql.Data.MySqlClient;
80using System.Data;
81
82namespace mysql
83{
84    class Program
85    {
86        static void Main(string[] args)
87        {
88            const string DB_CONN_STR = "Server=127.0.0.1;Uid=foo_dbo;Pwd=pass;Database=foo_db;";
89
90            MySqlConnection cn = new MySqlConnection(DB_CONN_STR);
91
92            try {
93
94                string sqlCmd = "select * from users order by user_id";
95
96                MySqlDataAdapter adr = new MySqlDataAdapter(sqlCmd, cn);
97                adr.SelectCommand.CommandType = CommandType.Text;
98                DataTable dt = new DataTable();
99                adr.Fill(dt); //opens and closes the DB connection automatically !! (fetches from pool)
100
101                foreach (DataRow dr in dt.Rows){
102                    Console.WriteLine(string.Format("user_id = {0}", dr["user_id"].ToString()));
103                }
104            }
105            catch (Exception ex)
106            {
107                Console.WriteLine("{oops - {0}", ex.Message);
108            }
109            finally
110            {
111                cn.Dispose(); // return connection to pool
112            }
113            Console.WriteLine("press any key...");
114            Console.ReadKey();
115        }
116    }
117}
118	
119using System;
120using System.Collections.Generic;
121using System.Linq;
122using System.Text;
123
124// addded these
125using MySql.Data;
126using MySql.Data.MySqlClient;
127using System.Data;
128
129namespace mysql
130{
131    class Program
132    {
133        static void Main(string[] args)
134        {
135            const string DB_CONN_STR = "Server=127.0.0.1;Uid=foo_dbo;Pwd=pass;Database=foo_db;";
136
137            MySqlConnection cn = new MySqlConnection(DB_CONN_STR);
138
139            try {
140
141                string sqlCmd = "select * from users order by user_id";
142
143                cn.Open(); // have to explicitly open connection (fetches from pool)
144
145                MySqlCommand cmd = new MySqlCommand(sqlCmd, cn);
146                cmd.CommandType = CommandType.Text;
147                MySqlDataReader rdr = cmd.ExecuteReader();
148
149                while (rdr.Read()){
150                    Console.WriteLine(string.Format("user_id = {0}", rdr["user_id"].ToString()));   
151                }
152            }
153            catch (Exception ex)
154            {
155                Console.WriteLine("{oops - {0}", ex.Message);
156            }
157            finally
158            {
159                cn.Dispose(); // return connection to the pool
160            }
161            Console.WriteLine("press any key...");
162            Console.ReadKey();
163        }
164    }
165}