Run a query (create procedure) in .net (C#)

Question

I have a query like below,how can i execute it from code (in C#) ? I can run this query in sql server.

create procedure addSystemError
    @date Datetime,
    @type nvarchar(50),
    @des nvarchar(max)
as
    insert into SystemError values(@date,@type,@des)
GO


create procedure loadSystemErrorCount
    @startDate datetime,
    @endDate datetime,
    @type nvarchar(50)
as
    if(@type='All')
        select COUNT(*) from SystemError where date>=@startDate and date<@endDate
    else
        select COUNT(*) from SystemError where date>=@startDate and date<@endDate and type=@type
Go

I want to run a query,maybe in this query exist more than a store procedure — user3780058, Jul 17 '14 at 06:07
You have to separate it into two SQL commands - one for each `create procedure` call - and then execute those one after the other. — marc_s, Jul 17 '14 at 06:07
If I want to create second procedure loadSystemErrorCount, how to do it? — user3780058, Jul 17 '14 at 06:14

score 0 · Answer 1 · answered Jul 17 '14 at 06:02

0

using (var conn = new SqlConnection(connectionString))
using (var command = new SqlCommand("addSystemError", conn) { 
                       CommandType = CommandType.StoredProcedure }) {
 conn.Open();

 /* Add the parameters in command
  .    
  . 
  .
 */

 command.ExecuteNonQuery();
 conn.Close();
}

answered Jul 17 '14 at 06:02

Jidheesh Rajan

4,744
4
23
29

run a query that contains store procedure,select ....,update , ... – user3780058 Jul 17 '14 at 06:09

user3840692 · Answer 2 · 2014-07-17T06:38:50.563

-1

this should work:

SqlConnection sqlConnection1 = new SqlConnection("Your Connection String");
SqlCommand cmd = new SqlCommand();

SqlDataReader reader;

cmd.CommandText = "SELECT * FROM Customers";
cmd.CommandType = CommandType.Text;
cmd.Connection = sqlConnection1;

sqlConnection1.Open();

reader = cmd.ExecuteReader();
// Data is accessible through the DataReader object here.

sqlConnection1.Close();

Microsoft how to

edited Jul 17 '14 at 06:38

answered Jul 17 '14 at 05:57

user3840692

273
2
13

2

comment would be sufficient if the answer is mostly a link.. – Yuliam Chandra Jul 17 '14 at 06:01
thanks i will remember this and change my answer – user3840692 Jul 17 '14 at 06:36
1

-1 for no `using` blocks. – John Saunders Jul 17 '14 at 06:40
this is only a simple sample, do i have to add using blocks also in this? it's a sample from microsoft ;) – user3840692 Jul 17 '14 at 06:59

Run a query (create procedure) in .net (C#)

2 Answers2