文档章节

使用C#创建SQL Server的存储过程

不折腾难受斯基
 不折腾难受斯基
发布于 2015/05/12 23:02
字数 3063
阅读 49
收藏 0
点赞 0
评论 0
使用C#创建SQL Server的存储过程
-
介绍
通常,开发人员使用的是T-SQL来创建SQL Server的存储过程函数和触发器 而现在的SQL Server 2005已经完全支持.NET通用语言运行时(CLR)了 这就意味着,你可以使用.NET的语言,如C#VB.NET之类的来开发SQL Server的存储过程函数和触发器 SQL Server 和 CLR 的集成给我们带来了n多好处,如实时编译类型安全增强的安全性以及增强的编程模型等 本文中,我将向大家演示如何使用C#创建SQL Server的存储过程
背景
我们在使用SQL Server存储过程时,最常做的工作就是从数据库中读取或保存数据 其常用应用如下:
        ·执行一些简单的逻辑,没有任何返回值 也没有输出参数
        ·执行一些逻辑,并通过一个或更多的输出参数返回结果
        ·执行一些逻辑,并返回从表中读取的一条或多条记录
        ·执行一些逻辑,并返回一行或多行记录 这些记录不是从表中读取的,而是你自定义的一些数据行
为了演示如何用C#开发出这几种应用的SQL Server存储过程,我将一个一个地举出示例
启用CLR集成
在你开始用C#写存储过程之前,必须要启用你的SQL Server的CLR集成特性 默认情况它是不启用的 打开你的SQL Server Management Studio并执行如下脚本
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
这里,我们执行了系统存储过程sp_configure,为其提供的两个参数分别为:clr enabled和1如果要停用CLR集成的话也是执行这个存储过程,只不过第二个参数要变为0而已另外,为了使新的设置产生效果,不要忘记调用RECONFIGURE
SQL Server项目
现在打开Visual Studio,并从文件菜单中选择新建项目 在新建项目对话框中选择Visual C#下的Database 然后选择SQL Server项目模板
起好项目名称后就单击确定按钮
很快,你所创建的项目就要求你选择一个SQL Server数据库
按照提示一步一步地做就好了,就算你选择了取消,也可以在项目属性对话框中再一次选择数据库 举个例子,假如你的电脑上有一个Northwind数据库,那么就在新建数据库引用对话框中选中它,然后单击确定按钮 之后,SQL Server项目在部署的时候就会将我们开发的存储过程写入这个数据库(继续往后看你就清楚是怎么回事了)
接下来,右键单击你新建的这个项目,选择添加-存储过程 然后将会出现如下图所示的对话框:
选择存储过程模板,并起一个合适的名字,然后单击添加按钮
添加完后你就会发现,实际上这是创建了一个已经导入了需要用到的命名空间的类
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
注意一下加粗显示的命名空间(译者注:后两个using) System.Data.SqlTypes命名空间包含了很多不同的类型,它们可以用来代替SQL Server的数据类型 Microsoft.SqlServer.Server命名空间下的类负责SQL Server的CLR集成
没有返回值的存储过程
在这一节中,我们将会看到如何写一个执行了一些逻辑,但是却没有任何返回值和输出参数的存储过程 在这个例子里,我们将创建一个名为ChangeCompanyName的存储过程,它用来修改Customers表中CompanyName字段的值 这个存储过程需要两个参数    CustomerID(需要更改公司名称的客户的ID)和CompanyName(新的公司名称) ChangeCompanyName存储过程完成后的代码如下:
[SqlProcedure]
public static void ChangeCompanyName
(SqlString CustomerID, SqlString CompanyName)
{
SqlConnection cnn = new SqlConnection
("context c");
cnn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = cnn;
cmd.CommandText = "update customers set
[email=companyname=@p1]companyname=@p1[/email] where [email=customerid=@p2]customerid=@p2[/email]";
SqlParameter p1 = new SqlParameter("@p1", CompanyName);
SqlParameter p2 = new SqlParameter("@p2", CustomerID);
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
int i=cmd.ExecuteNonQuery();
cnn.Close();
SqlContext.Pipe.Send(i.ToString());
}
仔细看一下这个ChangeCompanyName()方法 它是一个静态方法并且没有返回值(void) 它需要两个名为CustomerID和CompanyName的参数 请注意这两个参数的数据类型都是SqlString SqlString可以用来代替SQL Server中的nvarchar数据类型 这个方法用了一个[SqlProcedure]属性来修饰 该属性用于标记ChangeCompanyName()方法是一个SQL Server存储过程
在方法内我们创建了一个SqlConnection对象,并设置其连接字符串为context connection = true 上下文连接可以让你使用当前登录到数据库的用户作为你的登录数据库的验证信息 本例中,ChangeCompanyName()方法将会转换为存储过程,然后保存到Northwind数据库里 所以在这里的上下文连接指的就是Northwind数据库 这样你就不需要再写任何关于登录数据库的验证信息了
接下来是打开数据库连接 然后通过设置SqlCommand对象的Connection和CommandText属性,让其执行更新操作 同时,我们还需要设置两个参数 这样通过调用ExecuteNonQuery()方法就可以执行更新操作了 再接下来就是关闭连接
最后,将ExecuteNonQuery()方法的返回值发送到客户端 当然你也可以不做这一步 现在我们来了解一下SqlContext类的使用 SqlContext类用于在服务端和客户端之间传递处理结果 本例使用了Send()方法发送一个字符串返回给调用者
返回从表中读取的一条或多条记录的存储过程
我们在使用存储过程时,经常会SELECT一条或多条记录 你可以采用两种方法来创建这样的存储过程
首先我们创建一个名为GetAllCustomers()的方法,代码如下:
[SqlProcedure]
public static void GetAllCustomers()
{
SqlConnection cnn = new SqlConnection
("context c");
cnn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = cnn;
cmd.CommandText = "select * from customers";
SqlDataReader reader = cmd.ExecuteReader();
SqlContext.Pipe.Send(reader);
reader.Close();
cnn.Close();
}
这个GetAllCustomers()方法用了一个[SqlProcedure]属性来修饰 在方法内创建一个SqlConnection和一个SqlCommand对象 然后使用ExecuteReader()方法来执行SELECT语句 接下来用Send()方法将取得的SqlDataReader数据发送到客户端 最后就是关闭SqlDataReader和SqlConnection 在这种方法中,是我们自己创建的SqlDataReader 其实,我们也可以把这个任务交给SqlContext类去完成,代码如下:
[SqlProcedure]
public static void GetCustomerByID
(SqlString CustomerID)
{
SqlConnection cnn = new SqlConnection
("context c");
cnn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = cnn;
cmd.CommandText = "select * from customers
where [email=customerid=@p1]customerid=@p1[/email]";
SqlParameter p1 = new SqlParameter("@p1", CustomerID);
cmd.Parameters.Add(p1);
SqlContext.Pipe.ExecuteAndSend(cmd);
cnn.Close();
}
GetCustomerByID()方法需要一个参数    CustomerID,它将从Customers表中返回某个customer的记录 这个方法内的代码,除了ExecuteAndSend()方法外,你应该都已经比较熟悉了 ExecuteAndSend()方法接收一个SqlCommand对象作为参数,执行它就会返回数据集给客户端
有输出参数的存储过程
我们在使用存储过程时,经常会通过输出参数返回一个经过计算的值 所以,现在让我们来看一看如何创建具有一个或多个输出参数的存储过程
[SqlProcedure]
public static void GetCompanyName
(SqlString CustomerID,out SqlString CompanyName)
{
SqlConnection cnn = new SqlConnection
("context c");
cnn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = cnn;
cmd.CommandText = "select companyname from
customers where [email=customerid=@p1]customerid=@p1[/email]";
SqlParameter p1 = new SqlParameter
("@p1", CustomerID);
cmd.Parameters.Add(p1);
object obj = cmd.ExecuteScalar();
cnn.Close();
CompanyName = obj.ToString();
}
这是一个名为GetCompanyName()的方法,它需要两个参数 第一个参数是CustomerID,它是一个输入参数;第二个参数是CompanyName,它是一个输出参数(用关键字out来指明) 这两个参数都是SqlString类型的 GetCompanyName()方法会接收一个CustomerID参数,然后返回CompanyName(作为输出参数)
该方法内的代码首先设置了SqlConnection和SqlCommand对象 然后,使用ExecuteScalar()方法来执行SELECT语句 ExecuteScalar()方法返回的值是一个object类型,它其实就是公司名称 最后将输出参数CompanyName设置为这个值
返回一行或多行自定义数据的存储过程
我们在使用存储过程时,更多的还是从某些表中读取数据 但是,某些情况下我们需要的数据可能不在任何表里 例如,你可能会基于某些计算来生成一个数据表格 因为它的数据不是从表中获得的,所以上面的方法就不在适用了 幸运的是,SQL Server的CLR集成特性给我们提供了一个解决这个问题的方法 请看如下代码:
[SqlProcedure]
public static void GetCustomRow()
{
SqlMetaData[] metadata = new SqlMetaData[2];
metadata[0] = new SqlMetaData
("CustomerID", SqlDbType.NVarChar,50);
metadata[1] = new SqlMetaData
("CompanyName", SqlDbType.NVarChar,50);
SqlDataRecord record = new SqlDataRecord(metadata);
record.SetString(0, "ALFKI");
record.SetString(1, "Alfreds Futterkiste");
SqlContext.Pipe.Send(record);
}
GetCustomRow()方法会返回一条记录并发送给客户端 这个方法首先声明了一个SqlMetaData对象 当你要用到自定义列的时候,就可以使用这个SqlMetaData类 在我们的示例中,创建了两个类型为NVarChar,长度为50的列然后创建了一个SqlDataRecord对象 SqlDataRecord类可以用来表示一个自定义行 它的构造函数需要一个SqlMetaData数组作为参数 SqlDataRecord对象的SetString()方法用来设置列的值 另外,还有许多不同的类似SetString()这样的方法,可以用来处理不同的数据类型 最后,调用Send()方法将SqlDataRecord对象发送到客户端
在上面的示例中,我们只返回了一行数据给调用者 那么,如果要返回多行呢? 请看下面的代码:
[SqlProcedure]
public static void GetMultipleCustomRows()
{
SqlMetaData[] metadata = new SqlMetaData[2];
metadata[0] = new SqlMetaData
("CustomerID", SqlDbType.NVarChar, 50);
metadata[1] = new SqlMetaData
("CompanyName", SqlDbType.NVarChar, 50);
SqlDataRecord record = new SqlDataRecord(metadata);
SqlContext.Pipe.SendResultsStart(record);
record.SetString(0, "ALFKI");
record.SetString(1, "Alfreds Futterkiste");
SqlContext.Pipe.SendResultsRow(record);
record.SetString(0, "ANATR");
record.SetString(1, "Ana Trujillo Emparedados y helados");
SqlContext.Pipe.SendResultsRow(record);
SqlContext.Pipe.SendResultsEnd();
}
GetMultipleCustomRows()方法将会返回多个SqlDataRecord对象到客户端 接下来创建自定义列和设置列的值都和之前的例子一样 但是,我们使用的是SendResutlsStart()方法来传输数据 SendResultsRow()方法也是发送一个SqlDataRecord对象到客户端,但是我们可以多次调用它,从而做到发送多条记录 最后,调用SendResultsEnd()方法用来标记已经完成数据传输操作
我们已经开发完了存储过程 现在就可以将这个项目编译为一个程序集(.DLL) 但是我们的工作并没有到此结束 我们还需要部署这个程序集和存储过程到SQL Server数据库 有两种方法可以完成这个工作    手动和自动 手动方法是使用T-SQL语句注册你的程序集,并将存储过程部署到SQL Server数据库中 在本例中,我将使用自动的方法来部署存储过程到SQL Server数据库
右键单击你的项目,然后在菜单中选择部署选项
如此就会自动地完成注册程序集和部署存储过程的工作 注意,只有在你创建项目时添加了数据库引用的时候,才会出现部署选项 如果因为某些原因你没能添加数据库引用,那么你可以通过项目属性对话框来设置它
如果你在SQL Server Management Studio查看Northwind数据库的话,那么就应该可以看到和下图相似的结果
注意,在存储过程节点下出现了我们创建的所有方法(有锁图标的),并且在程序集节点下出现了我们的程序集
就是这些东西,很简单吧 现在你就可以在你的程序中调用这些存储过程了 你也可以在SQL Server Management Studio中来测试它们
----------------------------
一键转贴,快速捕捉生活精彩,赢每周好礼!查看活动首页>>

© 著作权归作者所有

共有 人打赏支持
不折腾难受斯基
粉丝 12
博文 128
码字总数 9664
作品 0
海淀
QA/测试工程师
使用 PowerShell 加密 SQL Server 的存储过程

下面代码演示如何使用 PowerShell 脚本将 SQL Server 中的所有存储过程加密。 出于安全的考虑,我们使用 WITH ENCRYPTION 在创建存储过程的时候进行加密,但如果我们想要更新已加密的存储过程...

红薯 ⋅ 2012/08/04 ⋅ 5

【SQL Server】SQL Server占用CPU使用率100%的解决方法

原文:【SQL Server】SQL Server占用CPU使用率100%的解决方法 近日,帮一个客户解决了服务器CPU占用率高达100%的问题。 以前做的一个某污水处理厂自控系统项目,客户反映其自控服务器有故障,...

杰克.陈 ⋅ 2017/12/15 ⋅ 0

储存过程简介

存储过程简介 什么是存储过程:存储过程可以说是一个记录集吧,它是由一些T-SQL语句组成的代码块,这些T-SQL语句代码像一个方法一样实现一些功能(对单表或多表的增删改查),然后再给这个代...

zh151832 ⋅ 2016/02/02 ⋅ 0

数据库优化之创建存储过程、触发器

存储过程可加快查询的执行速度,提高访问数据的速度,帮助实现模块化编程,保存一致性,提高安全性。触发器是在对表进行插入、更新、删除操作时自动执行的存储过程,通常用于强制业务规则。 ...

杨书凡 ⋅ 2017/12/02 ⋅ 0

如何创建MySQL存储过程可以返回一个表类型的数据?在C#代码中如何调用这样的存储过程?

@飞晏 你好,想跟你请教个问题: 我最近在做实验室的项目,需要考虑些存储过程,想请教您一些MySQL存储过程的问题,麻烦您帮我看一下,万分感谢。 MySQL如何创建可以返回DataTable类型的存储...

hushaod ⋅ 2013/12/04 ⋅ 2

周公51CTO博客有价值文章整理

说明:为方便朋友们查看本博客的文章,特地整理了本博客中周公认为值得一看的文章,不过有些文章因为年代久远且技术更新快的原因,里面所讲述的方法或者理论不再适合,请大家斟酌。 asp.net ...

周金桥 ⋅ 2011/06/23 ⋅ 0

用C#写存储过程(VS.NET 2005)

SQL2005中内置CLR, 因此可以用更高级的托管语言编写SP, Trigger,Function....等 下面是我用C#编写SP的过程: 环境: Win 2003 + SQL Server 2005 CTP(今年2月版本) +VS.NET 2005 CTP(今年2...

postdep ⋅ 2013/07/08 ⋅ 0

linux环境php调用sqlServer2000存储过程遇到的问题

  在linux环境中用php查询sql server并不是很常见的需求.一般来说如果一定要用sql server,编程语言我会先c#;如果一定要用php,数据库我就选mysql.   这几天的开发中,需要从一个非开源的e...

吕明明 ⋅ 2013/03/14 ⋅ 0

MySQL如何创建可以返回DataTable类型的存储过程?以及在C#中如何调用该存储过程来获得查询数据?

1、MySQL如何创建可以返回DataTable类型的存储过程? 具体:比如MySQL数据库中有mydbtest数据库,里面有一张表mytable,含有字段myID,myName,myNumber等字段,现在项目当中需要查询这个表的...

hushaod ⋅ 2013/12/04 ⋅ 1

创建代码生成器可以很简单:如何通过T4模板生成代码?[上篇]

在《基于T4的代码生成方式》中,我对T4模板的组成结构、语法,以及T4引擎的工作原理进行了大体的介绍,并且编写了一个T4模板实现了如何将一个XML转变成C#代码。为了让由此需求的读者对T4有更...

长平狐 ⋅ 2012/09/04 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

Centos7重置Mysql 8.0.1 root 密码

问题产生背景: 安装完 最新版的 mysql8.0.1后忘记了密码,向重置root密码;找了网上好多资料都不尽相同,根据自己的问题总结如下: 第一步:修改配置文件免密码登录mysql vim /etc/my.cnf 1...

豆花饭烧土豆 ⋅ 今天 ⋅ 0

熊掌号收录比例对于网站原创数据排名的影响[图]

从去年下半年开始,我在写博客了,因为我觉得业余写写博客也还是很不错的,但是从2017年下半年开始,百度已经推出了原创保护功能和熊掌号平台,为此,我也提交了不少以前的老数据,而这些历史...

原创小博客 ⋅ 今天 ⋅ 0

LVM讲解、磁盘故障小案例

LVM LVM就是动态卷管理,可以将多个硬盘和硬盘分区做成一个逻辑卷,并把这个逻辑卷作为一个整体来统一管理,动态对分区进行扩缩空间大小,安全快捷方便管理。 1.新建分区,更改类型为8e 即L...

蛋黄Yolks ⋅ 今天 ⋅ 0

Hadoop Yarn调度器的选择和使用

一、引言 Yarn在Hadoop的生态系统中担任了资源管理和任务调度的角色。在讨论其构造器之前先简单了解一下Yarn的架构。 上图是Yarn的基本架构,其中ResourceManager是整个架构的核心组件,它负...

p柯西 ⋅ 今天 ⋅ 0

uWSGI + Django @ Ubuntu

创建 Django App Project 创建后, 可以看到路径下有一个wsgi.py的问题 uWSGI运行 直接命令行运行 利用如下命令, 可直接访问 uwsgi --http :8080 --wsgi-file dj/wsgi.py 配置文件 & 运行 [u...

袁祾 ⋅ 今天 ⋅ 0

JVM堆的理解

在JVM中,我们经常提到的就是堆了,堆确实很重要,其实,除了堆之外,还有几个重要的模块,看下图: 大 多数情况下,我们并不需要关心JVM的底层,但是如果了解它的话,对于我们系统调优是非常...

不羁之后 ⋅ 昨天 ⋅ 0

推荐:并发情况下:Java HashMap 形成死循环的原因

在淘宝内网里看到同事发了贴说了一个CPU被100%的线上故障,并且这个事发生了很多次,原因是在Java语言在并发情况下使用HashMap造成Race Condition,从而导致死循环。这个事情我4、5年前也经历...

码代码的小司机 ⋅ 昨天 ⋅ 2

聊聊spring cloud gateway的RetryGatewayFilter

序 本文主要研究一下spring cloud gateway的RetryGatewayFilter GatewayAutoConfiguration spring-cloud-gateway-core-2.0.0.RC2-sources.jar!/org/springframework/cloud/gateway/config/G......

go4it ⋅ 昨天 ⋅ 0

创建新用户和授予MySQL中的权限教程

导读 MySQL是一个开源数据库管理软件,可帮助用户存储,组织和以后检索数据。 它有多种选项来授予特定用户在表和数据库中的细微的权限 - 本教程将简要介绍一些选项。 如何创建新用户 在MySQL...

问题终结者 ⋅ 昨天 ⋅ 0

android -------- 颜色的半透明效果配置

最近有朋友问我 Android 背景颜色的半透明效果配置,我网上看资料,总结了一下, 开发中也是常常遇到的,所以来写篇博客 常用的颜色值格式有: RGB ARGB RRGGBB AARRGGBB 这4种 透明度 透明度...

切切歆语 ⋅ 昨天 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部