请帮我在c#中编写storedprocedure调用函数 [英] please help me to write storedprocedure calling function in c#
问题描述
大家好,
i在sql server2008中创建了一个存储过程并成功执行...
现在我想通过c#windows应用程序执行该存储过程... 。
i在互联网上看了一些代码示例,他们为插入,更新和删除采用了三种不同的存储过程。但我在一个存储过程中进行了三次操作...
我在sql中为存储过程编写的代码是......
go
创建 procedure MasterInsertUpdateDelete
(
@ sno 整数,
@ name varchar ( 50 ),
@ phone bigint ,
@ city varchar ( 50 ),
@ statementType nvarchar ( 20 )= ' '
)
as
开始
如果 @ statementType = ' 插入'
开始
将插入 chaitu(sno,name,phone,city) values ( @ sno , @ name , @ phone , @ city )
end
if @ statementType = ' 选择'
开始
选择 * 来自 chaitu
end
if @ statementType = ' 更新'
开始
update chaitu set name = @ name,phone = @ phone,city = @ city
其中 sno = @ sno
end
else if @ statementType = ' 删除'
开始
delete 来自 chaitu 其中 sno = @ sno
end
end
我应该在c#windows中编写什么代码表单调用这个storedprocedure ...我有三个不同的按钮插入,更新,删除。
他们每个应该执行使用这个单独的存储过程来完成各自的任务....
请帮助我...
private void InsertDeletUpdate( string fName, Int64 phoneNumber, string city, string statementType)
{
使用(SqlConnection con = new SqlConnection( 此处的连接字符串)
{
使用(SqlCommand cmd = new SqlCommand( MasterInsertUpdateDelete,con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add( @ name,SqlDbType.VarChar ).Value = fName;
cmd.Parameters.Add( @ phone,SqlDbType.BigInt).Value =电话号码;
cmd.Parameters.Add( @ city,SqlDbType.VarChar).Value =市;
cmd.Parameters.Add( @ statementType,SqlDbType.NVarChar).Value = statementType;
con.Open();
cmd.ExecuteNonQuery();
}
}
}
像这样检查。我做了为您提供简单的锻炼。
创建常用功能并传递您的SP声明类型。
// 导入此
使用 System.Data;
使用 System.Data.SqlClient;
// Btn Insert调用函数并传递Insert作为参数
private void btnInsert_Click( object sender,EventArgs e)
{
Execute_SQLSP( Insert);
}
// Btn Update调用函数并将Update传递为参数
private void btnUpdate_Click( object sender,EventArgs e)
{
Execute_SQLSP( Update< /跨度>);
}
// Btn选择调用函数并将Select传递为参数
private void btnDelete_Click( object sender,EventArgs e)
{
Execute_SQLSP( 选择< /跨度>);
}
/执行Sp 的函数来自您的c#代码。
public void Execute_SQLSP( String SP_statementType)
{
try
{
SqlConnection myConnection = new SqlConnection(dbConnectionString);
SqlCommand( MasterInsertUpdateDelete,myConnection);
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.Parameters.Add( new SqlParameter( @sno,SqlDbType.Int))。Value = yourIntegersno;
myCommand.Parameters.Add( new SqlParameter( @name,SqlDbType.VarChar))。Value = YOUR_NAME;
myCommand.Parameters.Add( new SqlParameter( @phone,SqlDbType.BigInt))。Value =你的电话;
myCommand.Parameters.Add( new SqlParameter( @city,SqlDbType.VarChar))。Value = YOUR_city;
myCommand.Parameters.Add( new SqlParameter( @statementType,SqlDbType.NVarChar))。Value = SP_statementType;
myConnection.Open();
myCommand.ExecuteNonQuery();
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
mySqlDataAdapter.SelectCommand = myCommand;
DataSet yourDataSet = new DataSet();
mySqlDataAdapter.Fill(yourDataSet);
myConnection.Close();
}
catch (SqlException ex)
{
}
}
hi guys,
i created a stored procedure in sql server2008 and executed successfully...
now i want to execute that stored procedure through c# windows application....
i looked some code examples in internet there they took three different stored procedures for insert, update and delete. but i took three operations in a single stored procedure...
the code i written for stored procedure in sql is...
go
create procedure MasterInsertUpdateDelete
(
@sno integer,
@name varchar(50),
@phone bigint,
@city varchar(50),
@statementType nvarchar(20)=''
)
as
begin
if @statementType= 'Insert'
begin
insert into chaitu (sno, name, phone, city) values(@sno, @name, @phone, @city)
end
if @statementType='Select'
begin
select * from chaitu
end
if @statementType='Update'
begin
update chaitu set name=@name, phone=@phone, city=@city
where sno=@sno
end
else if @statementType='Delete'
begin
delete from chaitu where sno=@sno
end
end
what code should i have to write in c# windows form to call this storedprocedure... and i have three different buttons "Insert", "Update", "Delete".
each of them should perform their respective tasks, using this single stored procedure....
please help me...
private void InsertDeletUpdate(string fName,Int64 phoneNumber,string city,string statementType) { using (SqlConnection con = new SqlConnection("Your Connection string here") { using (SqlCommand cmd = new SqlCommand("MasterInsertUpdateDelete", con)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@name", SqlDbType.VarChar).Value = fName; cmd.Parameters.Add("@phone", SqlDbType.BigInt).Value = phoneNumber; cmd.Parameters.Add("@city", SqlDbType.VarChar).Value = city; cmd.Parameters.Add("@statementType", SqlDbType.NVarChar).Value = statementType; con.Open(); cmd.ExecuteNonQuery(); } } }
Hi,
Check like this .I have made a simple workout for you.
create common function and pass your SP Statement type .
// Import this using System.Data; using System.Data.SqlClient; // Btn Insert call the function and pass "Insert" as argument private void btnInsert_Click(object sender, EventArgs e) { Execute_SQLSP("Insert"); } // Btn Update call the function and pass "Update" as argument private void btnUpdate_Click(object sender, EventArgs e) { Execute_SQLSP("Update"); } // Btn Select call the function and pass "Select" as argument private void btnDelete_Click(object sender, EventArgs e) { Execute_SQLSP("Select"); } /Function to Execute Sp from your c# Code. public void Execute_SQLSP(String SP_statementType) { try { SqlConnection myConnection = new SqlConnection(dbConnectionString); SqlCommand("MasterInsertUpdateDelete", myConnection); myCommand.CommandType = CommandType.StoredProcedure; myCommand.Parameters.Add(new SqlParameter("@sno ", SqlDbType.Int)).Value = yourIntegersno; myCommand.Parameters.Add(new SqlParameter("@name ", SqlDbType.VarChar)).Value = "YOUR_NAME"; myCommand.Parameters.Add(new SqlParameter("@phone ", SqlDbType.BigInt)).Value = yourphone; myCommand.Parameters.Add(new SqlParameter("@city ", SqlDbType.VarChar)).Value = "YOUR_city "; myCommand.Parameters.Add(new SqlParameter("@statementType ", SqlDbType.NVarChar)).Value = SP_statementType; myConnection.Open(); myCommand.ExecuteNonQuery(); SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(); mySqlDataAdapter.SelectCommand = myCommand; DataSet yourDataSet = new DataSet(); mySqlDataAdapter.Fill(yourDataSet); myConnection.Close(); } catch (SqlException ex) { } }
这篇关于请帮我在c#中编写storedprocedure调用函数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!