SQL Stored Procedures within a .NET build 1

[bbartlin]

I have a C# program that uses the following code to compile stored procedures for later use.

private void createproc(string spName, System.Data.SqlClient.SqlConnection dataConnection)
{
SqlCommand dataCommand = new SqlCommand();
dataCommand.Connection = dataConnection;
string FILE_NAME = @"c:\Program Files\path\" + spName + ".sql";
StreamReader sr = File.OpenText(FILE_NAME);
String input;
String vinput;
vinput = " ";
while ((input=sr.ReadLine())!=null)
{
vinput += ('\n' + input);
}
sr.Close();

//compile procedure
dataCommand.CommandText = vinput;
SqlDataReader dataReader0 = dataCommand.ExecuteReader();
dataReader0.Close();
}

Right now I have the .sql files for each stored proc attached to the project as Project Output. So when I build, then run the msi file it puts the .sql files that I need on my C drive to be grabbed later to compile.

The question. Is there any way to embed these files. I don't really like it that a user will be able to see the .sql files after install on their C drive. But I need the files to be visible to the C# code to grab and compile.

I hope this makes some sense. Thank you!

 

[SHelton]

Add the .sql file to your project and change the Build Action for the file to Embedded Resource. You can then retrieve the contents of the file using:

private void button1_Click(object sender, System.EventArgs e)
{
	string filename = "test.sql";

	try
	{

		System.IO.StreamReader sr = new System.IO.StreamReader(System.Reflection.Assembly.GetExecutingAssembly().GetManifestResourceStream(Application.ProductName + "." + filename));

		if (sr != null)
		{
			string s = sr.ReadToEnd();

			sr.Close();

			MessageBox.Show(s);
		}
	}

	catch
	{
		MessageBox.Show("Invalid resource.");
	}
}

 

[bbartlin]

Thanks SHelton...

I think its much closer, but I am now getting the error "Value cannot be null. Parameter name: stream."
I've changed my code to the following...

private void createproc(string spName, System.Data.SqlClient.SqlConnection dataConnection)
{
SqlCommand dataCommand = new SqlCommand();
dataCommand.Connection = dataConnection;

string FILE_NAME = spName + ".sql";
try
{
System.IO.StreamReader sr = new System.IO.StreamReader(System.Reflection.Assembly.GetExecutingAssembly().GetManifestResourceStream(Application.ProductName + "." + FILE_NAME));

if (sr != null)
{
string s = sr.ReadToEnd();
sr.Close();
MessageBox.Show(s);
}
}
catch (Exception exc2)
{
MessageBox.Show(exc2.Message);
}
}

I have also added the files as embedded resourses under the project. Any other ideas?

 

[chiph]

What line of code is raising the error?

Chip H.


If you want to get the best response to a question, please check out FAQ222-2244 first

 

[bbartlin]

I got it to work by using the following...thank you all for your help.

private void createproc(string spName, System.Data.SqlClient.SqlConnection dataConnection)
{
SqlCommand dataCommand = new SqlCommand();
dataCommand.Connection = dataConnection;

string FILE_NAME = spName + ".sql";
try
{
string assembly = System.Reflection.Assembly.GetExecutingAssembly().GetName().Name.ToString();
// get the resource into a stream
System.IO.StreamReader sr = new System.IO.StreamReader(System.Reflection.Assembly.GetExecutingAssembly().GetManifestResourceStream(assembly + "." + FILE_NAME));

if (sr != null)
{
string s = sr.ReadToEnd();
sr.Close();

//drop the procedure if exists
dataCommand.CommandText = "IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[" + spName + "]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)" +
"DROP PROCEDURE [dbo].[" + spName + "]";
SqlDataReader dataReader = dataCommand.ExecuteReader();
dataReader.Close();
//compile procedure
dataCommand.CommandText = s;
SqlDataReader dataReader0 = dataCommand.ExecuteReader();
dataReader0.Close();
}
}
catch
{
MessageBox.Show("Invalid resource."
}
}