C#擷取MySql 資料常用的代碼

標籤：

1.讀取

public DataTable ExecuteDataTable(string SQLString){using (MySqlConnection connection = new MySqlConnection(connectionString)){DataSet ds = new DataSet();try{connection.Open();MySqlCommand cmd = new MySqlCommand(SQLString, connection);MySqlDataAdapter command = new MySqlDataAdapter(cmd);command.Fill(ds, "ds");}catch (MySql.Data.MySqlClient.MySqlException ex){throw new Exception(ex.Message);}return ds.Tables[0];}}

2.使用SET IDENTITY_INSERT手動為資料庫自增欄位插入編號

    string sqlstr = "SET IDENTITY_INSERT t1 ON;";                lists.Add(sqlstr);                sqlstr = "INSERT INTO t1       (id, name) VALUES ("+i.ToString()+", ‘" + i.ToString()+"‘);";                lists.Add(sqlstr);                sqlstr = "set identity_insert t1 off;";                lists.Add(sqlstr);                da.SqlCeExecuteNonQuery(lists);

 public void SqlCeExecuteNonQuery(List<string> lists)        {            SqlCeConnection conn = new SqlCeConnection();            SqlCeCommand comm = null;            try            {                conn.ConnectionString = PDAConnectionLocStr;                conn.Open();                comm = new SqlCeCommand("", conn);                for (int i = 0; i < lists.Count; i++)                {                    comm.CommandText = lists[i];                    comm.ExecuteNonQuery();                }            }            catch (SqlCeException ex)            {            }            finally            {                conn.Close();                conn.Dispose();                comm.Dispose();            }        }

3.mysql資料Unix時間戳記與C# DateTime時間類型互換

dangranusing System;using System.Collections.Generic;using System.Text;namespace WWFramework.DateTimes{    /// <summary>    /// 時間相關函數    /// </summary>    public static class Function    {        /// <summary>        /// 將Unix時間戳記轉換為DateTime類型時間        /// </summary>        /// <param name="d">double 型數字</param>        /// <returns>DateTime</returns>        public static System.DateTime ConvertIntDateTime(double d)        {             System.DateTime time = System.DateTime.MinValue;             System.DateTime startTime = TimeZone.CurrentTimeZone.ToLocalTime(new System.DateTime(1970, 1, 1));             time = startTime.AddSeconds(d);            return time;         }        /// <summary>        /// 將c# DateTime時間格式轉換為Unix時間戳記格式        /// </summary>        /// <param name="time">時間</param>        /// <returns>double</returns>        public static double ConvertDateTimeInt(System.DateTime time)        {            double intResult = 0;             System.DateTime startTime = TimeZone.CurrentTimeZone.ToLocalTime(new System.DateTime(1970, 1, 1));             intResult = (time - startTime).TotalSeconds;            return intResult;         }     }}

 

C#擷取MySql 資料常用的代碼