ado 操作mysql例子_基础 ADO.NET 访问MYSQL 与 MSSQL 数据库例子

虽然实际开发时都是用 Entity 了，但是基础还是要掌握和复习的 ^^

//set connection string, server,database,username,password

MySqlConnection con = new MySqlConnection("SERVER=localhost;DATABASE=Gridview;UID=keatkeat;PASSWORD=001001");

MySqlTransaction transaction= null;

MySqlDataReader reader = null;try{

MySqlCommand command= newMySqlCommand();

command.Connection=con;

command.CommandType=CommandType.Text;

con.Open();

transaction=con.BeginTransaction();

command.CommandText= "select * from task_record where id=?para0";//add and remove parameters

command.Parameters.Clear();

command.Parameters.AddWithValue("?para0", 1); //for protect sql inject attack//for insert update//int result = command.ExecuteNonQuery();//long lastInsertId = command.LastInsertedId;

transaction.Commit();//for select then use Adapter

MySqlDataAdapter adapter = newMySqlDataAdapter(command);

DataTable table= newDataTable();

adapter.Fill(table);//装入table之后就可以调用了

for (int i = 0, l = table.Rows.Count; i < l; i++)

{for (int a = 0, b = table.Columns.Count; a < b; a++)

{string columnName =table.Columns[a].ColumnName;

}string someData = table.Rows[i]["columnName"].ToString();

}//select then use readerreader=command.ExecuteReader();int colummCount =reader.FieldCount;int rowIndex = 0;while(reader.Read()) //reader 内的数据只能被read 一次哦

{for (int i = 0; i < colummCount; i++)

{string columnName =reader.GetName(i);object value =reader[i];

}

rowIndex++;

}

reader.Close();

}catch(Exception ex)

{if (transaction != null) transaction.Rollback();string x =ex.Message;

}finally{if (con.State ==System.Data.ConnectionState.Open)

{

con.Close();

}

}

上面给的是一个 MYSQL 的例子。

这里我要说说 SQL SERVER (MSSQL) 的区别。

1. Class不同, 上面的全部 "MySql" 改成 Sql 就可以了

2. CommandType 的namespace 不同， MSSQL 是在 System.Data

3. cosmmand 的 para 符号不同

"select * from task_record where id=?para0"

"select * from task_record where id=@para0" 

4. command.Transaction = transaction

5.

command.CommandText = "insert into product (code,name) output inserted.id values ('mk400','puma')";int result = (int)command.ExecuteScalar();

如果要获得last inserted id , 是通过 command output. (我不清楚这算不算SQL规范语句)