CUBRID學習筆記 32 對net的datatable的支持 cubrid教程

• 在net的驅動中實現理一下的支持
• DataTable data populate
• Built-in commands construct: INSERT , UPDATE, DELETE
• Columns metadata/properties
• DataSet, DataView inter-connection

還是看例子  會net都會看懂 不解釋

//獲取列屬性:
String sql = "select * from nation";
CUBRIDDataAdapter da = new CUBRIDDataAdapter();
da.SelectCommand = new CUBRIDCommand(sql, conn);
DataTable dt = new DataTable("nation");
da.FillSchema(dt, SchemaType.Source); //To retrieve all the column properties you have to use the FillSchema() method

Debug.Assert(dt.Columns[0].ColumnName == "code");
Debug.Assert(dt.Columns[0].AllowDBNull == false);
Debug.Assert(dt.Columns[0].DefaultValue.ToString() == "");
Debug.Assert(dt.Columns[0].Unique == true);
Debug.Assert(dt.Columns[0].DataType == typeof(System.String));
Debug.Assert(dt.Columns[0].Ordinal == 0);
Debug.Assert(dt.Columns[0].Table == dt);
//插入記錄
String sql = "select * from nation order by `code` asc";
using (CUBRIDDataAdapter da = new CUBRIDDataAdapter(sql, conn))
{
    using (CUBRIDDataAdapter daCmd = new CUBRIDDataAdapter(sql, conn))
    {
        CUBRIDCommandBuilder cmdBuilder = new CUBRIDCommandBuilder(daCmd);
        da.InsertCommand = cmdBuilder.GetInsertCommand();
    }

    DataTable dt = newDataTable("nation");
    da.Fill(dt);

    DataRow newRow = dt.NewRow();
    newRow["code"] = "ZZZ";
    newRow["name"] = "ABCDEF";
    newRow["capital"] = "MyXYZ";
    newRow["continent"] = "QWERTY";
    dt.Rows.Add(newRow);
    da.Update(dt);
}
//完整的代碼:
using CUBRID.Data.CUBRIDClient;
using System.Diagnostics;
using System.Data;
using System;

namespace DataTableExample
{
    class Program
    {
        private static void ExecuteSQL(string sql, CUBRIDConnection conn)
        {
            using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
            {
                cmd.ExecuteNonQuery();
            }
        }

        private static int GetTableRowsCount(string tableName, CUBRIDConnection conn)
        {
            int count = -1;
            string sql = "select count(*) from `" + tableName + "`";

            using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
            {
                count = (int)cmd.ExecuteScalar();
            }

            return count;
        }

        static void Main(string[] args)
        {
            CUBRIDConnectionStringBuilder sb = new CUBRIDConnectionStringBuilder("localhost", "demodb", "public", "", "33000");
            using (CUBRIDConnection conn = new CUBRIDConnection(sb.GetConnectionString()))
            {
                conn.Open();

                String sql = "select * from nation order by `code` DESC LIMIT 10";
                using (CUBRIDDataAdapter da = new CUBRIDDataAdapter(sql, conn))
                {

                    //Initialize the command object that will be used as the UpdateCommand for the DataAdapter.
                    CUBRIDCommand daInsert = new CUBRIDCommand("insert into nation values(?,?,?,?)", conn);
                    daInsert.CommandType = CommandType.Text;

                    //Parameter: code
                    daInsert.Parameters.Add(new CUBRIDParameter("?p1", DbType.String));
                    daInsert.Parameters["?p1"].SourceVersion = DataRowVersion.Current;
                    daInsert.Parameters["?p1"].SourceColumn = "code";
                    daInsert.Parameters["?p1"].SourceColumnNullMapping = false;

                    //Parameter: name
                    daInsert.Parameters.Add(new CUBRIDParameter("?p2", DbType.String));
                    daInsert.Parameters["?p2"].SourceVersion = DataRowVersion.Original;
                    daInsert.Parameters["?p2"].SourceColumn = "name";
                    daInsert.Parameters["?p2"].SourceColumnNullMapping = false;

                    //Parameter: continent
                    daInsert.Parameters.Add(new CUBRIDParameter("?p3", DbType.String));
                    daInsert.Parameters["?p3"].SourceVersion = DataRowVersion.Current;
                    daInsert.Parameters["?p3"].SourceColumn = "continent";
                    daInsert.Parameters["?p3"].SourceColumnNullMapping = false;

                    //Parameter: capital
                    daInsert.Parameters.Add(new CUBRIDParameter("?p4", DbType.String));
                    daInsert.Parameters["?p4"].SourceVersion = DataRowVersion.Original;
                    daInsert.Parameters["?p4"].SourceColumn = "capital";
                    daInsert.Parameters["?p4"].SourceColumnNullMapping = false;

                    daInsert.UpdatedRowSource = UpdateRowSource.None;

                    //Assign the command to the InsertCommand property of the DataAdapter.
                    da.InsertCommand = daInsert;

                    DataTable dt = new DataTable("nation");
                    da.Fill(dt);
                    DataRow newRow = dt.NewRow();
                    newRow["code"] = "ZZZ";
                    newRow["name"] = "ABCDEF";
                    newRow["capital"] = "MyXYZ";
                    newRow["continent"] = "QWERTY";
                    dt.Rows.InsertAt(newRow, 0);
                    da.Update(dt);
                    dt.AcceptChanges();

                    Debug.Assert(dt.Rows[0]["capital"].ToString() == "MyXYZ");
                    Debug.Assert(newRow.RowState.ToString() != "New");
                }

                Debug.Assert(GetTableRowsCount("nation", conn) == 216);
                //Revert changes
                ExecuteSQL("delete from nation where `code` = 'ZZZ'", conn);
                Debug.Assert(GetTableRowsCount("nation", conn) == 215);

                conn.Close();
            }
        }
    }
}