嵌入式firebird+VS2015实例十三

示例FbCommandTest

FbCommand对象

FbCommand对象的作用

Command 对象定义了将对数据源执行的指定命令。

l  ADO Command 对象用于执行面向数据库的一次简单查询。此查询可执行诸如创建、添加、取回、删除或更新记录等动作。

l  如果该查询用于取回数据，此数据将以一个 RecordSet 对象返回。这意味着被取回的数据能够被 RecordSet 对象的属性、集合、方法或事件进行操作。

l  Command 对象的主要特性是有能力使用存储查询和带有参数的存储过程

FbCommand对象的主要属性和方法

代码示例 

        privatevoidbtnUpdateTBL_Click(object sender, EventArgs e)

        {//更新数据

            myConnection.Open();//打开连接

            string updateQuery = "Update TestTBL set \"姓名\"='小李'" + "Where ID='1'";

            FbCommand UpdateCMD = newFbCommand(updateQuery,myConnection);

            try

            {

                int RecordsAffected = UpdateCMD.ExecuteNonQuery();

//返回值为该命令所影响的行数

                MessageBox.Show("影响行数：" + RecordsAffected);

 

                BinddgView(); //显示数据

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message);

            }

            UpdateCMD.Dispose();

            myConnection.Close();//关闭连接

        }

实例

创建项目：FbCommandTest

完整代码

using System;

using System.Data;

using System.Windows.Forms;

using System.IO;

using FirebirdSql.Data.FirebirdClient;

 

namespace FbCommandTestTest

{

    publicpartialclassFrmMain : Form

    {

        publicFbConnection myConnection;

        public FrmMain()

        {

            InitializeComponent();

            myConnection = newFbConnection(GetConnectionString());

        }

        staticstringGetConnectionString()

        {//构建连接字符串

            FbConnectionStringBuilder cs = newFbConnectionStringBuilder();

            cs.UserID = "SYSDBA";

            cs.Password = "masterkey";

            cs.Database = Application.StartupPath + "\\data\\测试.fdb";

            cs.DataSource = "localhost";

            cs.Charset = "UTF8";

            cs.Port = 3050;

            cs.Dialect = 3;

            cs.Role = "";

            cs.ConnectionLifeTime = 15;

            /*返回一个连接池时,它的创建时间与当前时间相比,

             * 和连接破坏如果时间(以秒计)超过指定的值连接。*/

            cs.Pooling = true;//当真正从池中连接了,或者如果有必要,创建和添加到适当的池。

            cs.MinPoolSize = 0;//池中所允许的最小连接数。

            cs.MaxPoolSize = 50;//池中允许的最大连接数。

            cs.PacketSize = 8192;

            cs.ServerType = FbServerType.Embedded;

            return cs.ToString();

        }

        privatevoid btnCreateDB_Click(object sender, EventArgs e)

        {//创建数据库

            string path = Application.StartupPath + "\\data\\测试.fdb";

            try

            {

                if (File.Exists(path))

                {

                    File.Delete(path);

                    FbConnection.CreateDatabase(GetConnectionString());

                    MessageBox.Show("创建数据库成功！");

                }

                else

                {

                    FbConnection.CreateDatabase(GetConnectionString());

                    MessageBox.Show("创建数据库成功！");

                }

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message);

            }

        }

        privatevoidbtnTestConn_Click(object sender, EventArgs e)

        {//测试连接

            try

            {

                myConnection.Open();

                MessageBox.Show("测试连接成功！");

                myConnection.Close();//关闭连接

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message);

            }

        }

        privatevoidbtnCreateTable_Click(object sender, EventArgs e)

        {//创建表

 

            //创建空表SQL

            myConnection.Open();//打开连接

            FbCommand createTable = myConnection.CreateCommand();

            //CreateCommand,创建并返回 DbCommand 与当前连接关联的对象。

            createTable.CommandText = "create table TestTBL (id int,\"姓名\" varchar(20))";

 

            //插入数据

            FbCommand insertData = myConnection.CreateCommand();

            try

            {

                createTable.ExecuteNonQuery();//创建空表执行SQL

 

                //插入数据一

                insertData.CommandText = "insert into TestTBL values(@id, @name)";

                insertData.Parameters.Clear();

                insertData.Parameters.Add("@id", FbDbType.Integer).Value = 1;

                insertData.Parameters.Add("@name", FbDbType.VarChar, 20).Value = "张三";

                insertData.ExecuteNonQuery();

                //插入数据二

                insertData.CommandText = "insert into TestTBL values(@id, @胡作非为)";

                insertData.Parameters.Clear();

                insertData.Parameters.Add("@id", FbDbType.Integer).Value = 2;

                insertData.Parameters.Add("@胡作非为", FbDbType.VarChar, 20).Value = "李四";

                insertData.ExecuteNonQuery();

 

                BinddgView(); //显示数据

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message);

            }

            createTable.Dispose();

            insertData.Dispose();

 

            myConnection.Close();//关闭连接

        }

        privatevoid BinddgView()

        {//DataGridView显示数据

            FbDataAdapter dt = newFbDataAdapter("select * from TestTBL", myConnection);

            DataSet ds = newDataSet();

            try

            {

                dt.Fill(ds, "TestTBL");

                this.dgViewDB.DataSource = ds;

                this.dgViewDB.DataMember = "TestTBL";

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message);

            }

            dt.Dispose();

            ds.Dispose();

        }

        privatevoidbtnUpdateTBL_Click(object sender, EventArgs e)

        {//更新数据

            myConnection.Open();//打开连接

            string updateQuery = "Update TestTBL set \"姓名\"='小李'" + "Where ID='1'";

            FbCommand UpdateCMD = newFbCommand(updateQuery, myConnection);

            try

            {

                int RecordsAffected = UpdateCMD.ExecuteNonQuery();//返回值为该命令所影响的行数

                MessageBox.Show("影响行数：" + RecordsAffected);

 

                BinddgView(); //显示数据

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message);

            }

            UpdateCMD.Dispose();

            myConnection.Close();//关闭连接

        }

    }

}

执行