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