基础 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 = new MySqlCommand();
    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 = new MySqlDataAdapter(command);
    DataTable table = new DataTable();
    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 reader 
    reader = 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" <- MySql 用的是问号 "?"

　"select * from task_record where id=@para0" <- SQL SERVER 用的是 "@"

4. command.Transaction = transaction <-- MSSQL 必须加多一句，每个command 必须要填写 transaction , 如果你有开的话

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

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