使用CLR在c#中执行SQl查询

I am using SQl CLR for parsing some table column. I want to execute the queries also in C# user defined function. Can somebody give an example to execute select and insert queries in the function?

我使用SQl CLR解析某些表列。我还想在c#用户定义函数中执行查询。有人能给出一个在函数中执行选择和插入查询的例子吗?

Thank you in advance.

提前谢谢你。

 SqlConnection objSqlConn;
    string connString = string.Empty;
    connString = "Data Source=(local);Initial Catalog=DB;User ID=uname;pwd=pass;Password=pass";
    objSqlConn = new SqlConnection(connString);
    objSqlConn.Open();

    string query = "Select count(*) FROM [DB].[dbo].[TableName]";
    SqlCommand cmdTotalCount = new SqlCommand(query, objSqlConn);
    cmdTotalCount.CommandTimeout = 0;
    string TotalCountValue = cmdTotalCount.ExecuteScalar().ToString();
    return TotalCountValue;

3 个解决方案

#1

Once you switch to C# you execute queries like you'd normally do from your application (using ADO.NET's SqlConnection and SqlDataReader, using LINQ to SQL or using your custom build data layer).

一旦切换到c#，就可以像通常在应用程序中执行查询一样执行查询(使用ADO)。NET的SqlConnection和SqlDataReader，使用LINQ到SQL或使用自定义构建数据层)。

#2

In CLR, you can use existing connection to run queries.

在CLR中，您可以使用现有的连接来运行查询。

Simple, returning data to client:

简单，返回数据给客户:

var cmd = new SqlCommand("select * from [table]");
SqlContext.Pipe.ExecuteAndSend(cmd);

Returning data via SqlDataReader:

通过SqlDataReader返回数据:

var con = new SqlConnection("context connection=true"); // using existing CLR context connection
var cmd = new SqlCommand("select * from table", con);
con.Open();
var rdr = cmd.ExecuteReader();
SqlContext.Pipe.Send(rdr);
rdr.Close();
con.Close();

Running other commands:

运行其他命令:

var con = new SqlConnection("context connection=true"); // using existing CLR context connection
var cmd = new SqlCommand("insert into [table] values ('ahoj')", con);
con.Open();
var rsa = cmd.ExecuteNonQuery();
con.Close();

#3

-2

To connect with the database you have to mention the database username and password in the connection string of your web.config file.

要连接数据库，必须在web的连接字符串中提到数据库用户名和密码。配置文件。

#1