示例ProcedureTest
存储过程
什么是存储过程
存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能的SQL 语句集,存储在数据库中,经过第一次编译后再次调用不需要再次编译,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程是数据库中的一个重要对象。
简单的说:存储过程就是固化在SQL数据库系统内部的SQL语句,这样做的好处是可以提高执行效率、提高数据库的安全性、减少网络流量。
存储过程的结构
CREATE PROCEDUREProcedureName <parameter list>
RETURNS<return parameter list>
AS <localvariable declarations>
BEGIN
SQL创建存储过程
创建存储过程可以用SQL命令在程序中进行,在存储过程中,除了Create Procedure,As,Begin…End语句之外,任何其他语句末尾都要添加分号结束;不论是输入参数、输出参数还是局部变量,如果在SQL语句中使用,前面必须加上冒号。
执行存储过程
为了ADO.NET应用程序中执行存储过程,需要把存储过程的名称赋给命令文本;同时将命令的CommandType属性设置为存储过程;Parameters参数与存储过程中定义的名称相同,不可以带冒号,而“@”,可有可无;Parameters参数顺序比较奇怪,第一次调用必须与存储过程中的参数列表顺序、数量相同,第二次即便顺序不同也能通过。
代码示例
privatevoid btnCreateProc_Click(object sender, EventArgs e)
{//创建存储过程
myConnection.Open();//打开连接
FbCommand UpdateCMD = myConnection.CreateCommand();
UpdateCMD.CommandText = "CREATE PROCEDURE ProcName"+
"(myId int, myName varchar(20)) " +
"as "+
"begin "+
"update TestTBL set \"姓名\"=:myName whereid=:myId;" +
"end";
try
{
UpdateCMD.ExecuteNonQuery();
MessageBox.Show("加入存储过程ProcName");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
UpdateCMD.Dispose();
myConnection.Close();//关闭连接
}
privatevoidbtnOpenProc_Click(object sender, EventArgs e)
{//调用存储过程
myConnection.Open();//打开连接
FbCommand UpdateCMD;
UpdateCMD = newFbCommand("ProcName", myConnection);
UpdateCMD.CommandType = CommandType.StoredProcedure;
try
{
int myid = 1;
string myname = "小张";
UpdateCMD.Parameters.Clear();
UpdateCMD.Parameters.Add("myId", myid);
UpdateCMD.Parameters.Add("@myName", myname);
UpdateCMD.ExecuteNonQuery();
myid = 2;
myname = "小李";
UpdateCMD.Parameters.Clear();
UpdateCMD.Parameters.Add("myName", myname);
UpdateCMD.Parameters.Add("@myId", myid);
UpdateCMD.ExecuteNonQuery();
BinddgView(); //显示数据
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
UpdateCMD.Dispose();
myConnection.Close();//关闭连接
实例
创建项目:ProcedureTest
ParametersTest模板
完整代码
uusingSystem;
usingSystem.Data;
usingSystem.Windows.Forms;
usingSystem.IO;
usingFirebirdSql.Data.FirebirdClient;
namespaceProcedureTest
{
public partial class FrmMain : Form
{
public FbConnection myConnection;
public FrmMain()
{
InitializeComponent();
myConnection = newFbConnection(GetConnectionString());
}
static string GetConnectionString()
{//构建连接字符串
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();
}
private void btnCreateDB_Click(objectsender, EventArgs e)
{//创建数据库
string path =Application.StartupPath + "\\data\\测试.fdb";
try
{
if (File.Exists(path))
{
File.Delete(path);
FbConnection.CreateDatabase(GetConnectionString());
//MessageBox.Show("创建数据库成功!");
btnTestConn.PerformClick();//调用测试连接
}
else
{
FbConnection.CreateDatabase(GetConnectionString());
//MessageBox.Show("创建数据库成功!");
btnTestConn.PerformClick();//调用测试连接
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void btnTestConn_Click(objectsender, EventArgs e)
{//测试连接
try
{
myConnection.Open();
myConnection.Close();//关闭连接
//MessageBox.Show("测试连接成功!");
btnCreateTable.PerformClick();//调用创建表
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private voidbtnCreateTable_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(stringparameterName, object value)
//在SQL语句中,字段名(如:姓名)与形参名(如:name)可以不同,但占位要相同.
//使用前Parameters集合,首先要清空它
//注入Parameters集合的参数的顺序可以不同,比如可以先加入@age,也可以先加入@name
int id = 1;
string name = "赵一";
int age = 12;
insertData.CommandText ="insert into TestTBL(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(stringparameterName, 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 into TestTBL(Id,\"姓名\",age) values(@胡作, @与,@非为)";
insertData.Parameters.Clear();
insertData.Parameters.Add("@胡作", id);
insertData.Parameters.Add("@与", name);
insertData.Parameters.Add("@非为", age);
insertData.ExecuteNonQuery();
//插入数据五
//原型:public FbParameter Add(FbParametervalue)
//注:这是新建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 =
{
new FbParameter("@胡作", FbDbType.Integer),
new FbParameter("@与", FbDbType.VarChar, 20),
new FbParameter("@非为", FbDbType.Integer, 20)
};
Params[0].Value = id;
Params[1].Value = name;
Params[2].Value = age;
insertData.Parameters.Clear();
foreach (FbParameter Parameterin Params)
{
insertData.Parameters.Add(Parameter);
}
insertData.ExecuteNonQuery();
//插入数据七
//原型:public FbParameterAddWithValue(string parameterName, 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();//关闭连接
}
private void BinddgView()
{//DataGridView显示数据
FbDataAdapter dt = newFbDataAdapter("select * from TestTBL", myConnection);
DataSet ds = new DataSet();
try
{
dt.Fill(ds,"TestTBL");
this.dgViewDB.DataSource = ds;
this.dgViewDB.DataMember ="TestTBL";
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
dt.Dispose();
ds.Dispose();
}
private void btnCreateProc_Click(objectsender, EventArgs e)
{//创建存储过程
myConnection.Open();//打开连接
FbCommand UpdateCMD =myConnection.CreateCommand();
UpdateCMD.CommandText ="CREATE PROCEDURE ProcName " +
"(myId int, myNamevarchar(20)) " +
"as " +
"begin " +
"update TestTBL set\"姓名\"=:myName where id=:myId;" +
"end";
try
{
UpdateCMD.ExecuteNonQuery();
MessageBox.Show("加入存储过程ProcName");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
UpdateCMD.Dispose();
myConnection.Close();//关闭连接
}
private void btnOpenProc_Click(objectsender, EventArgs e)
{//调用存储过程
myConnection.Open();//打开连接
FbCommand UpdateCMD;
UpdateCMD = newFbCommand("ProcName", myConnection);
UpdateCMD.CommandType =CommandType.StoredProcedure;
try
{
int myid = 1;
string myname = "小张";
UpdateCMD.Parameters.Clear();
UpdateCMD.Parameters.Add("myId", myid);
UpdateCMD.Parameters.Add("@myName", myname);
UpdateCMD.ExecuteNonQuery();
myid = 2;
myname = "小李";
UpdateCMD.Parameters.Clear();
UpdateCMD.Parameters.Add("myName", myname);
UpdateCMD.Parameters.Add("@myId", myid);
UpdateCMD.ExecuteNonQuery();
BinddgView(); //显示数据
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
UpdateCMD.Dispose();
myConnection.Close();//关闭连接
}
}
}
执行