C#使用ODBC链接MySQL数据库
C#使用ODBC链接MySQL数据库
1、ODBC.NET(全称ODBC .NET Data Provider)是一个免费的.NET Framework附加组件,需要到微软公司的网站上去下载,下载地址为:http://download.microsoft.com/download/dasdk/Install/1.0.4030.0/W98NT42KMeXP/EN-US/odbc_net.msi,它需要系统已经安装MDAC 2.7或者更高版本。
2、还需要安装MySQL的ODBC驱动程序,下载地址为:http://www.mysql.com/downloads/connector/odbc/
3、还需要在"ODBC数据源管理器"中配置一下DSN,步骤如下图所示:
(1)打开ODBC配置界面
(2)点击“添加”按钮,然后选择MySQL驱动
(3)打击完成后,要求配置数据库连接属性
Data Source Name:为该数据源取个名字(程序中要用)
Description:描述,非必填项
TCP/IP Server:数据库的IP地址,Port:端口
User:登录数据库的用户名
Password:密码
Database:需要操作的数据库名称
---------------------------------------------------------------------------------
填写完后,点击右下角的“test”按钮,测试是否可以链接上数据库,如下图:
表示链接成功。
(4)配置完成后,可以在ODBC数据源中看到刚才配置的MySQL数据源
(5)到此,ODBC数据源配置完成。
操作C#数据库代码:
Program.cs
- using System;
- using System.Collections.Generic;
- using System.Linq;
- using System.Text;
- using System.Data.Odbc;
- namespace AccessDB
- {
- class Program
- {
- static void Main(string[] args)
- {
- string constr = "DSN=MySQL;" + "UID=root;" + "PWD=671354;";
- OdbcConnection conn = new OdbcConnection(constr);
- conn.Open();
- //string insert = "insert into test.test values(null, 'wwh', '123')";
- //string select = "select * from test.test";
- //string update = "update test.test set name='whwang' where id = 11";
- string delete = "delete from test.test where id = 12";
- DB db = new DB();
- //db.Insert(conn, insert);
- //db.Select(conn, select);
- //db.Update(conn, update);
- db.Delete(conn, delete);
- conn.Close();
- Console.Read();
- }
- }
- }
- using System;
- using System.Collections.Generic;
- using System.ComponentModel;
- using System.Text;
- using System.Data.Odbc;
- namespace AccessDB
- {
- class DB
- {
- public void Select(OdbcConnection conn, string selectSql)
- {
- OdbcCommand cmd = new OdbcCommand(selectSql, conn);
- OdbcDataReader reader = cmd.ExecuteReader();
- while (reader.Read())
- {
- Console.Write(reader.GetString(0) + ", ");
- Console.Write(reader.GetString(1) + ", ");
- Console.WriteLine(reader.GetString(2));
- }
- }
- public void Insert(OdbcConnection conn, string insertSql)
- {
- OdbcCommand cmd = new OdbcCommand(insertSql, conn);
- cmd.ExecuteNonQuery();
- cmd.Dispose();
- }
- public void Update(OdbcConnection conn, string updateSql)
- {
- OdbcCommand cmd = new OdbcCommand(updateSql, conn);
- cmd.ExecuteNonQuery();
- cmd.Dispose();
- }
- public void Delete(OdbcConnection conn, string deleteSql)
- {
- OdbcCommand cmd = new OdbcCommand(deleteSql, conn);
- cmd.ExecuteNonQuery();
- cmd.Dispose();
- }
- }
I'm using the Impala ODBC driver provided by Cloudera. I can't seem to use query parameters correctly. For instance:
OdbcCommand command = DbConnection.CreateCommand();
command.CommandText = "INSERT INTO TABLE test VALUES(?, ?)";
command.Parameters.Add("key", OdbcType.VarChar).Value = "csharp";
command.Parameters.Add("val", OdbcType.VarChar).Value = "test";
command.ExecuteNonQuery();
throws the following exception.
{"ERROR [HY000] [Cloudera][ImpalaODBC] (110) Error while executing a query in Impala: [HY000] : AnalysisException: Syntax error in line 1:\nINSERT INTO TABLE test VALUES(?, ?)\n
^\nEncountered: Unexpected character\nExpected: CASE, CAST, EXISTS, FALSE, IF, INTERVAL, NOT, NULL, TRUNCATE, TRUE, IDENTIFIER\n\nCAUSED BY: Exception: Syntax error\n"}
Which should manage query parameters (and replacement of ? by values), ODBC library or the driver ? It seems to be the driver and in this case, it's not implemented... And it's a shame because I cannot use prepared statement.
Does somebody know a way to use query parameters with the Impala ODBC driver?