基礎 ADO.NET 訪問MYSQL資料庫例子

標籤：style   blog   io   ar   color   os   sp   for   資料   

雖然實際開發時都是用 Entity 了，但是基礎還是要掌握和複習的 ^^ 

 

//set connection string, server,database,username,passwordMySqlConnection 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();    }}

 

基礎 ADO.NET 訪問MYSQL資料庫例子