嵌入式firebird+VS2015实例十六

示例ParametersTest

Parameters集合

FBCommand类提供了一个Parameters集合属性，用以为命令保存所有的参数。其定义如下:

public FbParameterAdd(string parameterName, object value)

public FbParameterAdd(string parameterName, FbDbType type)

public FbParameterAdd(FbParameter value)

public FbParameterAddWithValue(string parameterName, object value)

代码示例

//创建空表SQL

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

            FbCommand createTable = myConnection.CreateCommand();

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

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

 

            //插入数据

            FbCommand insertData = myConnection.CreateCommand();

            try

            {

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

 

                //插入数据一

                //原型:public FbParameter Add(string parameterName,object value)

                //在SQL语句中,字段名(如:姓名)与形参名(如:name)可以不同,但占位要相同.

                //使用前Parameters集合,首先要清空它

                //注入Parameters集合的参数的顺序可以不同,比如可以先加入@age,也可以先加入@name

                int id = 1;

                string name = "赵一";

                int age = 12;

 

                insertData.CommandText = "insert intoTestTBL(Id,\"姓名\",age) values(@id, @name,@age)";

                insertData.Parameters.Clear();

                insertData.Parameters.Add("@id", id);

                insertData.Parameters.Add("@age", age);

                insertData.Parameters.Add("@name", name);

                insertData.ExecuteNonQuery();

 

                //插入数据二

                //

                //这种形式的SQL不如上面的好,它没有指定字段名,容易造成混乱.

                //

                //

                id = 2;

                name = "孙二";

                age = 13;

 

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

                insertData.Parameters.Clear();

                insertData.Parameters.Add("@id", id);

                insertData.Parameters.Add("@age", age);

                insertData.Parameters.Add("@name", name);

                insertData.ExecuteNonQuery();

 

                //插入数据三

                //原型:public FbParameter Add(string parameterName,FbDbType type)

                //注:参数的FbDbType可以指定,也可以不指定.

                //上面有CommandText,这里不要也行.

                id = 3;

                name = "张三";

                age = 14;

 

                insertData.Parameters.Clear();

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

                insertData.Parameters.Add("@name", FbDbType.VarChar, 100).Value = name;

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

                insertData.ExecuteNonQuery();

 

 

                //插入数据四

                //

                //参数名称可以随便起.

                id = 4;

                name = "李四";

                age = 15;

 

                insertData.CommandText = "insert intoTestTBL(Id,\"姓名\",age) values(@胡作, @与,@非为)";

                insertData.Parameters.Clear();

                insertData.Parameters.Add("@胡作", id);

                insertData.Parameters.Add("@与", name);

                insertData.Parameters.Add("@非为", age);

                insertData.ExecuteNonQuery();

 

 

                //插入数据五

                //原型:public FbParameter Add(FbParameter value)

                //注:这是新建FbParameter对象的方法.

                id = 5;

                name = "王五";

                age = 15;

                FbParameter ParamID = newFbParameter("@胡作", FbDbType.Integer);

                FbParameter ParamName = newFbParameter("@与", FbDbType.VarChar, 20);

                FbParameter ParamAge = newFbParameter("@非为", FbDbType.Integer, 20);

                ParamID.Value = id;

                ParamName.Value = name;

                ParamAge.Value = age;

 

                insertData.Parameters.Clear();

               insertData.Parameters.Add(ParamID);

               insertData.Parameters.Add(ParamName);

               insertData.Parameters.Add(ParamAge);

                insertData.ExecuteNonQuery();

               

 

                //插入数据六

                //数组方法,注意参数和值要对应好.

                id = 6;

                name = "刘六";

                age = 17;

                FbParameter[] Params =

                    {

                    newFbParameter("@胡作", FbDbType.Integer),

                    newFbParameter("@与", FbDbType.VarChar, 20),

                    newFbParameter("@非为", FbDbType.Integer, 20)

                    };

                Params[0].Value = id;

                Params[1].Value = name;

                Params[2].Value = age;

 

                insertData.Parameters.Clear();

                foreach(FbParameter Parameter in Params)

                {

                   insertData.Parameters.Add(Parameter);

                }

                insertData.ExecuteNonQuery();

 

                //插入数据七

                //原型:public FbParameter AddWithValue(stringparameterName, object value)

                //注:AddWithValue方法,只有这一种,没多的.

                id = 7;

                name = "牛七";

                age = 18;

 

                insertData.Parameters.Clear();

               insertData.Parameters.AddWithValue("@胡作", id);

               insertData.Parameters.AddWithValue("@与", name);

                insertData.Parameters.AddWithValue("@非为", age);

                insertData.ExecuteNonQuery();

 

                BinddgView(); //显示数据

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message);

            }

            createTable.Dispose();

            insertData.Dispose();

 

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

实例

创建项目：ParametersTest

StringConcatenationTest模板

完整代码

using System;

using System.Data;

using System.Windows.Forms;

using System.IO;

using FirebirdSql.Data.FirebirdClient;

 

namespace ParametersTest

{

    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();

        }

        privatevoidbtnCreateDB_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),age int)";

 

            //插入数据

            FbCommand insertData = myConnection.CreateCommand();

            try

            {

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

 

                //插入数据一

                //原型:public FbParameter Add(string parameterName,object value)

                //在SQL语句中,字段名(如:姓名)与形参名(如:name)可以不同,但占位要相同.

                //使用前Parameters集合,首先要清空它

                //注入Parameters集合的参数的顺序可以不同,比如可以先加入@age,也可以先加入@name

                int id = 1;

                string name = "赵一";

                int age = 12;

 

                insertData.CommandText = "insert intoTestTBL(Id,\"姓名\",age) values(@id, @name,@age)";

                insertData.Parameters.Clear();

                insertData.Parameters.Add("@id", id);

                insertData.Parameters.Add("@age", age);

                insertData.Parameters.Add("@name", name);

                insertData.ExecuteNonQuery();

 

                //插入数据二

                //

                //这种形式的SQL不如上面的好,它没有指定字段名,容易造成混乱.

                //

                //

                id = 2;

                name = "孙二";

                age = 13;

 

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

                insertData.Parameters.Clear();

                insertData.Parameters.Add("@id", id);

                insertData.Parameters.Add("@age", age);

                insertData.Parameters.Add("@name", name);

                insertData.ExecuteNonQuery();

 

                //插入数据三

                //原型:public FbParameter Add(string parameterName,FbDbType type)

                //注:参数的FbDbType可以指定,也可以不指定.

                //上面有CommandText,这里不要也行.

                id = 3;

                name = "张三";

                age = 14;

 

                insertData.Parameters.Clear();

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

                insertData.Parameters.Add("@name", FbDbType.VarChar, 100).Value = name;

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

                insertData.ExecuteNonQuery();

 

 

                //插入数据四

                //

                //参数名称可以随便起.

                id = 4;

                name = "李四";

                age = 15;

 

                insertData.CommandText = "insert intoTestTBL(Id,\"姓名\",age) values(@胡作, @与,@非为)";

                insertData.Parameters.Clear();

                insertData.Parameters.Add("@胡作", id);

                insertData.Parameters.Add("@与", name);

                insertData.Parameters.Add("@非为", age);

                insertData.ExecuteNonQuery();

 

 

                //插入数据五

                //原型:public FbParameter Add(FbParameter value)

                //注:这是新建FbParameter对象的方法.

                id = 5;

                name = "王五";

                age = 15;

                FbParameter ParamID = newFbParameter("@胡作", FbDbType.Integer);

                FbParameter ParamName = newFbParameter("@与", FbDbType.VarChar, 20);

                FbParameter ParamAge = newFbParameter("@非为", FbDbType.Integer, 20);

                ParamID.Value = id;

                ParamName.Value = name;

                ParamAge.Value = age;

 

                insertData.Parameters.Clear();

                insertData.Parameters.Add(ParamID);

               insertData.Parameters.Add(ParamName);

               insertData.Parameters.Add(ParamAge);

                insertData.ExecuteNonQuery();

               

 

                //插入数据六

                //数组方法,注意参数和值要对应好.

                id = 6;

                name = "刘六";

                age = 17;

                FbParameter[] Params =

                    {

                    newFbParameter("@胡作", FbDbType.Integer),

                    newFbParameter("@与", FbDbType.VarChar, 20),

                    newFbParameter("@非为", FbDbType.Integer, 20)

                    };

                Params[0].Value = id;

                Params[1].Value = name;

                Params[2].Value = age;

 

                insertData.Parameters.Clear();

                foreach(FbParameter Parameter in Params)

                {

                   insertData.Parameters.Add(Parameter);

                }

                insertData.ExecuteNonQuery();

 

                //插入数据七

                //原型:public FbParameter AddWithValue(stringparameterName, object value)

                //注:AddWithValue方法,只有这一种,没多的.

                id = 7;

                name = "牛七";

                age = 18;

 

                insertData.Parameters.Clear();

               insertData.Parameters.AddWithValue("@胡作", id);

               insertData.Parameters.AddWithValue("@与", name);

               insertData.Parameters.AddWithValue("@非为", age);

                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 id = "1";

            string name = "小李";

 

            FbCommand UpdateCMD = myConnection.CreateCommand();//CreateCommand方法

            UpdateCMD.CommandText = "Update TestTBL set \"姓名\"=@name Where ID=@id";

            UpdateCMD.Parameters.Clear();

            UpdateCMD.Parameters.Add("@name", name);

            UpdateCMD.Parameters.Add("@id", id);

            try

            {

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

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

 

                BinddgView(); //显示数据

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message);

            }

            UpdateCMD.Dispose();

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

        }

    }

}

 

 

执行