c# 连接mysql 增删改查操作
[c#]代碼庫
| usingSystem; |
| usingSystem.Configuration; |
| usingMySql.Data.MySqlClient; |
| /// <summary> |
| /// TestDatebase 的摘要說明 |
| /// </summary> |
| publicclassTestDatebase |
| { |
| publicTestDatebase() |
| { |
| // |
| // TODO: 在此處添加構造函數邏輯 |
| // |
| } |
| publicstaticvoidMain ( String[] args ) |
| { |
| MySqlConnection mysql = getMySqlCon(); |
| //查詢sql |
| String sqlSearch ="select * from student"; |
| //插入sql |
| String sqlInsert ="insert into student values (12,'張三',25,'大專')"; |
| //修改sql |
| String sqlUpdate ="update student set name='李四' where id= 3"; |
| //刪除sql |
| String sqlDel ="delete from student where id = 12"; |
| //打印SQL語句 |
| Console.WriteLine ( sqlDel ); |
| //四種語句對象 |
| //MySqlCommand mySqlCommand = getSqlCommand(sqlSearch, mysql); |
| //MySqlCommand mySqlCommand = getSqlCommand(sqlInsert, mysql); |
| //MySqlCommand mySqlCommand = getSqlCommand(sqlUpdate, mysql); |
| MySqlCommand mySqlCommand = getSqlCommand ( sqlDel, mysql ); |
| mysql.Open(); |
| //getResultset(mySqlCommand); |
| //getInsert(mySqlCommand); |
| //getUpdate(mySqlCommand); |
| getDel ( mySqlCommand ); |
| //記得關閉 |
| mysql.Close(); |
| String readLine = Console.ReadLine(); |
| } |
| /// <summary> |
| /// 建立mysql數據庫鏈接 |
| /// </summary> |
| /// <returns></returns> |
| publicstaticMySqlConnection getMySqlCon() |
| { |
| String mysqlStr ="Database=test;Data Source=127.0.0.1;User Id=root;Password=root;pooling=false;CharSet=utf8;port=3306"; |
| // String mySqlCon = ConfigurationManager.ConnectionStrings["MySqlCon"].ConnectionString; |
| MySqlConnection mysql =newMySqlConnection ( mysqlStr ); |
| returnmysql; |
| } |
| /// <summary> |
| /// 建立執行命令語句對象 |
| /// </summary> |
| /// <param name="sql"></param> |
| /// <param name="mysql"></param> |
| /// <returns></returns> |
| publicstaticMySqlCommand getSqlCommand ( String sql,MySqlConnection mysql ) |
| { |
| MySqlCommand mySqlCommand =newMySqlCommand ( sql, mysql ); |
| // MySqlCommand mySqlCommand = new MySqlCommand(sql); |
| // mySqlCommand.Connection = mysql; |
| returnmySqlCommand; |
| } |
| /// <summary> |
| /// 查詢并獲得結果集并遍歷 |
| /// </summary> |
| /// <param name="mySqlCommand"></param> |
| publicstaticvoidgetResultset ( MySqlCommand mySqlCommand ) |
| { |
| MySqlDataReader reader = mySqlCommand.ExecuteReader(); |
| try |
| { |
| while( reader.Read() ) |
| { |
| if( reader.HasRows ) |
| { |
| Console.WriteLine ("編號:"+ reader.GetInt32 ( 0 ) +"|姓名:"+ reader.GetString ( 1 ) +"|年齡:"+ reader.GetInt32 ( 2 ) +"|學歷:"+ reader.GetString ( 3 ) ); |
| } |
| } |
| } |
| catch( Exception ) |
| { |
| Console.WriteLine ("查詢失敗了！"); |
| } |
| finally |
| { |
| reader.Close(); |
| } |
| } |
| /// <summary> |
| /// 添加數據 |
| /// </summary> |
| /// <param name="mySqlCommand"></param> |
| publicstaticvoidgetInsert ( MySqlCommand mySqlCommand ) |
| { |
| try |
| { |
| mySqlCommand.ExecuteNonQuery(); |
| } |
| catch( Exception ex ) |
| { |
| String message = ex.Message; |
| Console.WriteLine ("插入數據失敗了！"+ message ); |
| } |
| } |
| /// <summary> |
| /// 修改數據 |
| /// </summary> |
| /// <param name="mySqlCommand"></param> |
| publicstaticvoidgetUpdate ( MySqlCommand mySqlCommand ) |
| { |
| try |
| { |
| mySqlCommand.ExecuteNonQuery(); |
| } |
| catch( Exception ex ) |
| { |
| String message = ex.Message; |
| Console.WriteLine ("修改數據失敗了！"+ message ); |
| } |
| } |
| /// <summary> |
| /// 刪除數據 |
| /// </summary> |
| /// <param name="mySqlCommand"></param> |
| publicstaticvoidgetDel ( MySqlCommand mySqlCommand ) |
| { |
| try |
| { |
| mySqlCommand.ExecuteNonQuery(); |
| } |
| catch( Exception ex ) |
| { |
| String message = ex.Message; |
| Console.WriteLine ("刪除數據失敗了！"+ message ); |
| } |
| } |