資料庫連線應用

2018-08-08

4510
0
C#.Practices.DB
2018-09-04

一些簡單的資料庫應用..有想到在慢慢新增吧 !!

基本的連線字串

string MsSqlConn = @"Server=;Database=;User Id=;Password=;";
string MySqlConn = @"server=;uid=;pwd=;database=;";

MySQL 需要額外下載 MySQL.Data.dll，然後把相關 Class 改成 MySql 用的就好了，內容基本上不用再改。

SqlConnection → MySqlConnection
SqlTransaction → MySqlTransaction
SqlCommand → MySqlCommand
SqlDataAdapter → MySqlDataAdapter

基本的一些應用操作

因為工作需要有完整交易，一定要整套流程毫無問題才把資料異動一口氣執行完成，所以有用到 Transaction，會額外下 Commit。

// Oracle, MSSQL
using System.Data.SqlClient;
// MySQL
using MySql.Data.MySqlClient;

public class DataBaseConnector
{
    // 資料庫相關常數
    private SqlConnection MsSqlConn = null;
    private SqlTransaction MsSqlTran = null;
    public string ConnectString {  get; private set; }


    public DataBaseConnector(string ConnectString)
    {
        this.ConnectString = Encoding.UTF8.GetString(Convert.FromBase64String(ConnectString));
        MsSqlConn = new SqlConnection(this.ConnectString);
    }

    /// <summary>
    /// 建立連線
    /// </summary>
    /// <returns></returns>
    public void OpenConn()
    {
        try
        {
            if (MsSqlConn != null)
            {
                MsSqlConn.Open();
                MsSqlTran = MsSqlConn.BeginTransaction();
            }
        }
        catch
        {
            throw;
        }
    }
    /// <summary>
    /// 關閉連線
    /// </summary>
    public void CloseConn()
    {
        try
        {
            if (MsSqlConn != null)
            {
                MsSqlConn.Close();
                MsSqlConn.Dispose();
                MsSqlConn = null;
            }

            if (MsSqlTran != null)
            {
                MsSqlTran.Dispose();
                MsSqlTran = null;
            }
        }
        catch
        {
            throw;
        }
    }
    /// <summary>
    /// 確認執行語法
    /// </summary>
    /// <returns></returns>
    public void Commit()
    {
        try
        {
            if (MsSqlTran.Connection != null)
                MsSqlTran.Commit();
        }
        catch
        {
            throw;
        }
    }

    /// <summary>
    /// 取得查詢結果
    /// </summary>
    /// <param name="Query">查詢語法</param>
    /// <returns>查詢結果</returns>
    public DataTable GetResult(string Query, out string ErrMsg)
    {
        ErrMsg = string.Empty;
        DataTable ld_Result = new DataTable("Result");

        try
        {
            using (SqlCommand cmd = new SqlCommand(Query, MsSqlConn, MsSqlTran))
            {
                using (SqlDataAdapter aptr = new SqlDataAdapter(cmd))
                {
                    aptr.Fill(ld_Result);
                    aptr.Dispose();
                }
                cmd.Dispose();
            }
        }
        catch (Exception ex)
        {
            ErrMsg = ex.Message;
        }

        return ld_Result;
    }

    /// <summary>
    /// 取得查詢結果(只有第一行第一列)
    /// </summary>
    /// <param name="Query">查詢語法</param>
    /// <returns>查詢結果</returns>
    public string GetScalar(string Query, out string ErrMsg)
    {
        ErrMsg = string.Empty;
        string ld_Result = string.Empty;

        if (Query.Trim().Substring(0, 6).ToUpper() != "SELECT")
        {
            ErrMsg = "此函式用來取得數據，不能執行非SELECT的語法。";
            return ld_Result;
        }
        
        try
        {
            using (SqlCommand cmd = new SqlCommand(Query, MsSqlConn, MsSqlTran))
            {
                ld_Result = cmd.ExecuteScalar().ToString();
                cmd.Dispose();
            }
        }
        catch (Exception e)
        {
            try
            {
                if (MsSqlTran != null) MsSqlTran.Rollback();
            }
            catch (Exception e2)
            {
                ErrMsg = string.Format("SQL_ERR={0};ROLLBACK_FAIL={1}", e.Message, e2.Message);
            }

            ErrMsg = string.Format("SQL_ERR={0}", e.Message);
        }

        return ld_Result;
    }

    /// <summary>
    /// 執行SQL語法
    /// </summary>
    /// <param name="Query">SQL語法</param>
    /// <returns>受影響的資料筆數</returns>
    public string ExecuteCmd(string Query, out string ErrMsg)
    {
        ErrMsg = string.Empty;
        int li_EffectCount = 0;
        System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
        sw.Reset();
        sw.Start();

        try
        {
            using (SqlCommand cmd = new SqlCommand(Query, MsSqlConn, MsSqlTran))
            {
                li_EffectCount = cmd.ExecuteNonQuery();
                cmd.Dispose();
            }
        }
        catch (Exception e)
        {
            try
            {
                if (MsSqlTran != null) MsSqlTran.Rollback();
            }
            catch (Exception e2)
            {
                ErrMsg = string.Format("SQL_ERR={0};ROLLBACK_FAIL={1}", e.Message, e2.Message);
            }

            ErrMsg = string.Format("SQL_ERR={0}", e.Message);
        }
        finally
        {
            sw.Stop();
        }

        return string.Format("EFFECT_DATA={0};EXE_TIME={1}s", li_EffectCount, sw.Elapsed.TotalSeconds.ToString("#0.0#"));
    }
}

2018 / 09 / 04 新增 DataCopy Function

如果需要利用C#定時將A DB的TABLE內部份資料複製到B DB，可以先在B DB建立跟A一樣的空資料表，然後就能引用這個Function，再搭配設定排程，定時做資料轉移~

/// <summary>
/// 資料移轉
/// </summary>
/// <param name="TableName"></param>
/// <param name="WhereSql"></param>
public static void DataCopy(string TableName, string WhereSql)
{
    DataTable dataTable = new DataTable();

    using (SqlConnection conn = new SqlConnection(sourConnStr))
    {
        conn.Open();
        using (SqlDataAdapter aptr = new SqlDataAdapter(string.Format("select * from {0}{1}", TableName, WhereSql), conn))
        {
            aptr.Fill(dataTable);
        }
    }

    if (dataTable.Rows.Count > 0)
    {

        string ls_insert = string.Format("insert into {0} ", TableName);
        string ls_cols = string.Empty;
        string ls_row = string.Empty;
        string ls_vals = string.Empty;

        foreach (DataColumn dc in dataTable.Columns)
        {
            ls_cols += dc.ColumnName + ",";
        }
        ls_cols = ls_cols.Substring(0, ls_cols.Length - 1);
        ls_insert = ls_insert + "(" + ls_cols + ") values ";



        using (SqlConnection conn = new SqlConnection(descConnStr))
        {
            conn.Open();

            // 先清空表單資料
            using (SqlCommand cmd = new SqlCommand(string.Format("delete from {0}", TableName), conn))
            {
                cmd.ExecuteNonQuery();
            }

            try
            {
                // 組字串
                int li_row = 0;
                foreach (DataRow dr in dataTable.Rows)
                {
                    if (li_row % 100 == 0 && li_row > 0)
                    {
                        ls_vals = ls_vals.Substring(0, ls_vals.Length - 1);
                        ls_vals = ls_insert + ls_vals;

                        // 重塞
                        using (SqlCommand cmd = new SqlCommand(ls_vals, conn))
                        {
                            cmd.ExecuteNonQuery();
                        }

                        ls_vals = string.Empty;
                    }

                    ls_row = string.Empty;
                    foreach (DataColumn dc in dataTable.Columns)
                    {
                        ls_row += (dr[dc.ColumnName] == null || dr[dc.ColumnName].ToString().Length == 0) ? "NULL," :
                                  (dr[dc.ColumnName].GetType() == typeof(DateTime)) ? "'" + DateTime.Parse(dr[dc.ColumnName].ToString()).ToString("yyyy-MM-dd HH:mm:ss.fff") + "'," :
                                                                                       "'" + dr[dc.ColumnName].ToString().Trim().Replace("'", "''") + "',";
                    }
                    ls_vals += "(" + ls_row.Substring(0, ls_row.Length - 1) + "),";

                    li_row++;
                }

                ls_vals = ls_vals.Substring(0, ls_vals.Length - 1);
                ls_vals = ls_insert + ls_vals;


                // 重塞
                using (SqlCommand cmd = new SqlCommand(ls_vals, conn))
                {
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception e)
            {
                //sw.WriteLine(DateTime.Today.ToString("yyyy-MM-dd") + " [" + TableName + "] → " + ls_vals);
            }
        }
    }
}

用法範例

DataCopy("table_name", "");
DataCopy("table_name", " where sn = '1'");

回首頁