Executing SQl Queries in C# using CLR

Question

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?

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;

dizzy128 · Accepted Answer · 2013-02-28 15:52:31Z

12

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

Simple, returning data to client:

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

Returning data via 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();

edited Feb 28, 2013 at 15:52

answered Feb 28, 2013 at 15:46

dizzy128

2893 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Fedor Hajdu · Accepted Answer · 2012-10-18 09:45:03Z

0

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).

answered Oct 18, 2012 at 9:45

Fedor Hajdu

4,7053 gold badges35 silver badges50 bronze badges

1 Comment

liang Over a year ago

Are you sure you could use Linq to Sql in SQLCLR? Does the database project even accept dbml file?

Saasu Ganesan · Accepted Answer · 2012-10-18 10:16:19Z

-2

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

answered Oct 18, 2012 at 10:16

Saasu Ganesan

1808 bronze badges

2 Comments

Saasu Ganesan Over a year ago

@sharmi Do you want an example to use select and insert query???Actually what are you expecting?

Jason Kelley Over a year ago

no, you want to use the existing connection as dizzy128 explained.

Collectives™ on Stack Overflow

Executing SQl Queries in C# using CLR

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related