.SQL files in visual studio 2012

Question

I have an question about executin of sql commands from visual studio, I have a few procedures which are not so short so it would be a drag if i execute them with comandtype text and write it with stringbuilder. I decided to write them in .sql file that is saved in my project but i dont know much about it. Like if there is created procedure, I assume i need to make it once but how to call it then later after for something else etc. when user clicks i need to call it with various parameters. Any tutorials or examples about it?
Something like

FileInfo file = new FileInfo(@"\\ares\c$\Inetpub\wwwroot\TestArea\SQL\testsql.sql");


        string script = file.OpenText().ReadToEnd();


        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["WhatIfDatabaseConnectionString"].ConnectionString))
        {
              SqlCommand sqlCmd = new SqlCommand(script, conn);
              sqlCmd.ExecuteNonQuery(script);

But with parameters.

About 766,000 of them: http://www.google.com.au/search?q=ado+.net+calling+stored+procedure&rlz=1C1CHLW_enAU510AU510&oq=ado+.net+calling+stored+procedure&sourceid=chrome&ie=UTF-8 — ta.speot.is, Jan 19 '13 at 10:26
Maybe i wasn't clear enough. I need to write all and use it from visual studio, so my procedures are in visual studio project in folder i created in .sql files. so im reading content with fileinfo and than execute commands. But i don't know if I can make something like procedure like that, and how to call it than if i have parameters or it goes the same like if procedure is in sql written — djedjica, Jan 19 '13 at 10:36

score 0 · Answer 1 · answered Jan 19 '13 at 10:36

0

SqlCommand cmd = new SqlCommand();

cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "CRUD_myproc";

cmd.Parameters.Add("@flag", SqlDbType.Char).Value = 'a';
cmd.Parameters.Add("@type", SqlDbType.Int).Value = cmbBuildingType.SelectedIndex;

cmd.ExecuteNonQuery();

So you should create proc named 'CRUD_myproc' in sql. Hope this will help you little

answered Jan 19 '13 at 10:36

Sandip Bantawa

2,822
4
31
47

I want to make procedure in txt file and save it like .sql file in project, and than read it and execute it. As far as database itself, I have only connection string. nothing in managment studio. – djedjica Jan 19 '13 at 10:38

score 0 · Accepted Answer · answered Apr 19 '16 at 08:49

You are going down the right lines, personally I have a factory that caches the SQL files and checks the last modified date, but it's doing what you are doing:

string script = File.ReadAllText(_serverPath + sqlName + ".sql");
using (SqlConnection conn = new SqlConnection(ConnString))
{
    conn.Open();
    using (SqlCommand sqlCmd = new SqlCommand(script, conn))
    {
        sqlCmd.CommandType = CommandType.Text;
        sqlCmd.Parameters.AddWithValue("@CompanyGuid", CompanyGuid);
        sqlCmd.ExecuteNonQuery();
    }
}

You then have a SQL text file with the parameter prefixed with the @ symbol:

SELECT * FROM tblCompany WHERE CompanyGuid = @CompanyGuid

.SQL files in visual studio 2012

2 Answers2