Page 1 of 1

Using SQL stored procedures and Integration Services Packages to expor An all-in-one example for how to easily implement importing an excel d

#1 hoinarut  Icon User is offline

  • New D.I.C Head
  • member icon

Reputation: 5
  • View blog
  • Posts: 5
  • Joined: 25-October 07

Post icon  Posted 25 October 2007 - 11:00 PM

REQUIREMENTS: Visual Studio 2005 / C# Express Edition , SQL Server 2005 (starting from Eval Edition) and of course .NET 2.0
I cannot guarantee that it works with previous versions of VS / SQL
Time of completion: less than one hour


Please keep in mind that this example is just for giving an idea about how you can do complex stuff with sql commands and stored procedures, using them in you C# application, and exporting the data you process. Why use SQL stored procedures and export data in this way? I will give you three reasons:
1)It is very easy to implement, not very much code, and easy to modify (most part pf it outside your application code
2)The total time for retrieving/inserting/updating data via a stored procedure is by far much smaller than using a CommandType.Text query from your code.
3)You can very easy costumize the format of your data export, by naming, selecting whatever you need, and, do not forget the many export formats (see export_format.jpg).
This is only a brief presentation about what you can do.
So, too much talking is not good ... let's get to the real stuff :)

Part 1 - Application description

The example i am providing is a simple application, wich imports a sheet from an excel file into a specific table in our SQL database. After that, we will execute some procedures that make some calculation upon our data, puts it in anothert temporary table for processing, and in the end, exports it. I could have make this simpler, but i really wanted to show how easy you can do stuff like passing some data into SQL via input parameters, and customize you export file and stuff.

Part 2 - The Excel import file

After we design our user interface (i used a MenuStrip for this) we can think about the first step, that is importing our data. Assuming you have already a database created with a table tblMain defined, we want to make a File Selection function, wich adds a specific sheet from our xls in the database (you could also select whatever you need from that specific sheet, you only need to make sure that the table inside the database contains only the columns you need to import, or, configure the rest of columns to accept NULL values).
So, basically the following code is attached to my "MenuStrip item".onclick event:
public void whateverToolStripMenuItem_Click(object sender, EventArgs e)
		{
		   //A previously declared and instantiated OpenFileDialog, i put it from Design Mode, but you can just
		   //declare it as OpenFileDialog dlgImport = new OpenFileDialog();
		   //We show the dialog:
			dlgImport.ShowDialog();
		   //We declare a variable to store the file path and name:
			string fileName = dlgImport.FileName;
			try
			{
				//We invoke our method, wich is created in the following section, and pass it two parameters
				//The file name and .... a DataGridView name that we put is the Form, so we can also see what
				//We imported. Cool, isn't it?
				importExcel(fileName, gridMain);
			}
			//It is best to always try to handle errors, you will se later why it is OleDbException and not 
			 catch (OleDbException ex)
			{
				MessageBox.Show("Error ocurred: " + ex.Message);
			}
		}



Ok, so far we've made only the code for opening our file, and passing it's path to our method. Here it is the method!

//Assuming you have a string declared as sqlConnection, wich has the ConnectionString for your database
//We declare our method as void (it doesn't return anything) with two parameters, a string for our excel file path
//and a datagrid
public void importExcel(string dbFileName, DataGridView dtGrid)
		{
		   //Here is an SQL command that takes us out of much code, for doing this importing task
		   //It inserts in our tblMain all data from sheet MAIN in my case, and specifies that first row is HeaderRow 
		   //by HDR=yes
			string insertSql = "INSERT INTO dbo.tblMain SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;HDR=yes;Database=" + dbFileName + "',[MAIN$])";
			SqlConnection conSql = new SqlConnection(sqlConnection);
			SqlCommand commandInsExcSql = new SqlCommand(insertSql, conSql);
			try
			{
				commandInsExcSql.Connection.Open();
				commandInsExcSql.ExecuteNonQuery();
				commandInsExcSql.Connection.Close();
			}
			catch (Exception ex)
			{
				MessageBox.Show(ex.Message);
			}
			//The data is imported! Next we create a DataAdapter to fill a DataTable, wich will be source of our	  
			//DataGrid; it is actually the data we have just imported
			string selectCmd = "select * from tblMain";
			//We use the same connection we declared upper, only the SelectCommand differs
			SqlDataAdapter dtaDataAdapter = new SqlDataAdapter(selectCmd, conSql);
			DataTable tblGridMain = new DataTable();
			dtaDataAdapter.Fill(tblGridMain);
			dtGrid.DataSource = tblGridMain;
		}			 



Ok. Now we have our data imported and set it as source for a DataGridView. All done in few rows (most of them are my comments :) )
Next, we create the stored procedures in our database!
I made for this application 4 stored procedures. First one is selecting the desired data from the main table, for processing, second one is processing our data, the third one is used to delete the data from the temporary tables, and the fourth one is used to set our export path, you will se why!
I will show you the code to put in a "New Query..." in SQL Management Studio (or whatever you use, T-SQL is the same) for creating those procedures, but please keep in mind you must take it as example, not the actual names :)
So, first procedure:
//main sintax, and declaration of one input parameter (all parameters are declared with @ in front) of type of 
//VarChar and size of 20; this is for filtering if needed our data!
CREATE PROCEDURE [dbo].[selectFromTbl] (@origin VarChar(20))
AS
//Here we declare our "inside" parameters
DECLARE @price float
DECLARE @artID varchar(100)
DECLARE @quantity int
//We put condition so if our needed data has in, Origin column  in this example, to filter for the columns value and
//execute different query for different value
IF @origin = 'Brazil'
	DECLARE test1 CURSOR FOR select ID, Quantity, [Unit Price] from tblMain where Origin = @origin
ELSE
	DECLARE test1 CURSOR FOR select ID, Quantity, [Unit Price] from tblMain where Origin != 'Brazil' AND [Name on English] != 'parts of shelfs'
//The CURSOR is like a DataReader, and a DataRowView, in wich you iterate in every row resulted from query
//so "WHILE" there are rows, we "fetch" them inside our "internal" parameters, as values!
OPEN test1
 fetch next from test1 INTO @artID, @quantity, @price WHILE @@FETCH_STATUS = 0
//In this part we insert in a temporary table the data retrieved in our parameters
 BEGIN
		insert into tblTest (artId,unitprice,quantity) values (@artID, @price, @quantity)
//We move to next row in the results
fetch next from test1 INTO @artId, @quantity, @price
 END
CLOSE test1 


This is not as bad as it looks :) First time is hard....
Next it stored procedure to make the actuall processing of the data we copy from the initial table ....
//This one expects 3 parameters. Please note that if you try to execute a stored procedure without any parameters, 
//or not all that are declared, you will have error that the procedure expects fpr them; so if you think there will be situations when no parameters cand be provided, pass them with NULL value, don't try not to pass them at all
CREATE PROCEDURE [dbo].[genereazaDoc] (@tipDoc VarChar(20), @nrFact VarChar(20), @euro Float)
AS
DECLARE @price Float
DECLARE @artID varchar(100)
DECLARE @quantity int
DECLARE @price_ron_wo_vat Float
DECLARE @price_ron_w_vat Float
DECLARE calculatePrice CURSOR FOR select artid, Quantity, UnitPrice from tblTest
OPEN calculatePrice
fetch next from calculatePrice INTO @artID, @quantity, @price WHILE @@FETCH_STATUS = 0
 BEGIN 
//Here is a simple function. The parameters @price_ron .... will make a calculation with a formula; they use an 
//internal parameter (a select result) and one external (input) parameter; lower you can see a basic sum and round 
//function.
	SET @price_ron_wo_vat = round(sum(@price*@euro),2)
	SET @price_ron_w_vat = round(sum(@price_ron_wo_vat*1.19),4)
//Again a condition, if you look in the query you will see that in one query the quantity is passed as negative (-)
//instead of positive (again, this was my case, you can make anything, use your imagination :)
IF @tipDoc='Invoice'
	insert into tblInvoice (NDoc,NSklad,Vid01,Sklad,DokIme,DokNomer,DokData,Kontragent,TotalTO,Stoka,Kol,Miarka,EdCena,ProdCena,Valuta,KursLV,VklDDS,Systavil,Komentar,StokovaLine,Kol01,Kol02,IzhodNomer,Nasreshten,SkladCena,StokaGrupa,StkImportMode,RealTime,Kind,Department,PayType,PayDate,POSNo,Signed,DealKind,VATRate)
		values (1,2,0,'Depozit',@tipDoc, @nrFact, '23.10.2007', 'Euro 07 SA', 0, @artID,-@quantity,'buc.', @price_ron_w_vat, @price_ron_w_vat,'RON', 1, 0, 'admin',null, 0, 0, 0, 0, 0, @price_ron_wo_vat, null, 1, '10:10',21,null,4,null,0,0,null,'-0.19');
ELSE
	insert into tblInvoice (NDoc,NSklad,Vid01,Sklad,DokIme,DokNomer,DokData,Kontragent,TotalTO,Stoka,Kol,Miarka,EdCena,ProdCena,Valuta,KursLV,VklDDS,Systavil,Komentar,StokovaLine,Kol01,Kol02,IzhodNomer,Nasreshten,SkladCena,StokaGrupa,StkImportMode,RealTime,Kind,Department,PayType,PayDate,POSNo,Signed,DealKind,VATRate)
		values (1,2,0,'Depozit',@tipDoc, @nrFact, '23.10.2007', 'Euro 07 SA', 0, @artID, @quantity,'buc.', @price_ron_w_vat, @price_ron_w_vat,'RON', 1, 0, 'admin',null, 0, 0, 0, 0, 0, @price_ron_wo_vat, null, 1, '10:10',21,null,4,null,0,0,null,'-0.19');
fetch next from calculatePrice INTO @artId, @quantity, @price
 END
CLOSE calculatePrice


So this function actually makes some calculation upon one value we retrieved earlier in the first procedure, and inserts some "default" values, besides the ones we already selected, in the final table, wich is the one we will use as export source
Next, we move to the third procedure, for deleting data from tables. This is a very simple procedure, wich empties the two temporary tables, because in my case the data wont be required again after it is exported.
So ....
CREATE PROCEDURE [dbo].[deleteData]
AS
DELETE FROM tblInvoice
DELETE FROM tblTest


I quite don’t have anything to explain  It just deletes data from those two tables. You can modify it and for example, first move all the data in separate tables, in the case when you want to store the data …

The next and final procedure in this tutorial, is for setting the path of the exported file. I consider that we should first see how to create the package and use it in our application.
Part 3 – Creating the package for exporting data.

Please note that this is possible to execute only if you have SQL server installed with Integration Services (if you execute it as a separate file). If you save the package inside the database, you can execute it like a procedure, from a client station wich has only SQL Native Client.
So, in picture “create_package.jpg” you will see the steps to create a package for exporting data to a flat file (basically text file, tab delimited).
So, you first select your database as data source (as you can see, you can also select other data sources, but let’s keep it in the line of this tutorial). After that, you must specify a file name to save to. Put anything you want, for testing purposes, we will dynamically change that path later. Also, note that I checked the “Column names in first row of data”, and that is as it sounds, we specify that we must export the table column names, as our first row of data in the export file. For the next step, leave it as default, copy data from table.
In the next step (see picture export_package_step3.jpg), you select the table from wich you need data, specify the delimiter. Next is the most important step in my opinion: you have the oportunity to save the export task (didn’t I mention until now? On your sql server management studio, you right click on your database, go to “tasks”, and select “Export data……..”). You have the option to save it as external file or in you server. For this tutorial, save it as external file. Also, if you want you can execute it after the wizard (see the checkbox) and also you have security options. Leave it default for now, “play” with the options after you finish this tutorial. See all of this in the picture “export_package_step4.jpg”. In this final stept, , put a name for it and if you want, a description and select location of the package file you will save. Hit next when you are ready, and finish. Before you get to finish, you will se a summary of the export task, after you finish, it will be executed if you selected that, or just be saved.

Part 4 – Executing the procedures and the package from our C# application

In this moment you have the procedures for creating the data you need, and the export package, but have no clue about how you will use them in the application. The stored procedures are executed like the text command types, the only difference is that in your SqlCommand object, you will set the CommandType to StoredProcedure and the CommandText to “databasename.procedurename”. After this, you just open the connection (by invoking the public method SqlCommand.Connection.Open(), after that to actually execute it, you use SqlCommand.ExecuteNonQuery(), and in the end, close the connection with SqlCommand.Connection.Close().
See bellow in the code what I was talking about here.
//We declare the method, again void
private void proceduriStocate()
		{
		//Declare a new SqlConnection object, with the //connectionstring sqlConnection wich Is assumed you declared somewhere //in the form; next declare the SqlCommand object, create a “try”, in //wich you execute your command object. You can open the connection and //then add it as the SqlCommand.Connection property, or like I said //upper.
			SqlConnection conexiune = new SqlConnection(sqlConnection);
			SqlCommand cmdProceduri = new SqlCommand();
			try
			{
				conexiune.Open();
				cmdProceduri.Connection = conexiune;
				cmdProceduri.CommandText = "nomenklaturaSQL.dbo.selecteazaTbl";
				cmdProceduri.CommandType = CommandType.StoredProcedure;
//From here, it is a bit complicated, but I’m sure you’ll get it in no-//time. If you remember, we created our procedures to expect some input //parameters, therefor we must declare them and assign a value; you can //see the basic syntax bellow; the value of my origin parameter is the //combobox selecteditem value, converted to string. This was my need, //you can do anything.Also make sure that you declare the same datatype
//you declared inside the procedure, for this input data type
				SqlParameter origin = new SqlParameter("@origin", SqlDbType.VarChar, 20, "input");
				origin.Value = cmbOrigine.SelectedItem.ToString();
				cmdProceduri.Parameters.Add(origin);
				cmdProceduri.ExecuteNonQuery();
				cmdProceduri.Connection.Close();
			}
			catch (SqlException ex)
			{
				MessageBox.Show("A fost intampinata o eroare in timp ce se executa " + cmdProceduri.CommandText.ToString() + ";" + ex.Message);
			}
		}


Ok, we now have our method for executing the procedure. But, we have also other methods to execute, with other parameters. So, think practically: you can create an overloaded method from the first one, so just copy the code from first method, and make modifications in the parameters section, rename “origin” with the appropriate name, type and value, and add two more, so we can execute our next stored procedure. This cand be further simplified within the encapsulation model and concepts, but, again, is beyond the scope of my tutorial.
Here you have the code for the overloaded method:
private void proceduriStocate(string document, string nrfactura)
		{
			SqlConnection conexiune = new SqlConnection(sqlConnection);
			SqlCommand cmdProceduri = new SqlCommand();
			try
			{
				conexiune.Open();
				cmdProceduri.Connection = conexiune;
				cmdProceduri.CommandText = "nomenklaturaSQL.dbo.genereazaDoc";
				cmdProceduri.CommandType = CommandType.StoredProcedure;
				SqlParameter tipdoc = new SqlParameter("@tipDoc", SqlDbType.VarChar, 20, "input");
				SqlParameter nrfact = new SqlParameter("@nrFact", SqlDbType.VarChar, 20, "input");
				SqlParameter cursEuro = new SqlParameter("@euro", SqlDbType.Decimal, 4, "input");
				tipdoc.Value = document;
				nrfact.Value = nrfactura;
				cursEuro.Value = float.Parse(txtEur.Text);
				cmdProceduri.Parameters.Add(tipdoc);
				cmdProceduri.Parameters.Add(nrfact);
				cmdProceduri.Parameters.Add(cursEuro);
				cmdProceduri.ExecuteNonQuery();
				cmdProceduri.Connection.Close();
				}
			catch (SqlException ex)
			{
				MessageBox.Show("A fost intampinata o eroare in timp ce se executa " + cmdProceduri.CommandText.ToString() + ";" + ex.Message);
			}
		}


I believe there is no other explanation needed. Please mind also that I didn’t quite optimise my coding in very many parts, yet. Not all content should be in the try { } section, only the Connection.Open, ExecuteNonQuery and Connection.Close methods, and, of course, all these methods we create, could stand very elegant in a separate class file, wich contains only methods, eventually compile it as a sepparate dll file. The optimization can continue further more.

I won’t put the code for the deleteData stored procedure, it is far more simple than these two methods, you only modify the command text and remove all parameters. You could also put in the try { } section a message box to have a message if all is ok, besides the one that (hopefully wont appear  ) in the catch { } section.
We move forward to the package execution. When i first thought of this, i had no idea what are Integration Services, and how to interact with them from my application’s code. But Microsoft’s MSDN is indeed very helpful! It is far more simple to execute a DTS package than to create a method to execute a stored procedure. You don’t believe me?
Let’s continue then 

You must first add reference in visual studio to a sql server library file, wich is Microsoft.SqlServer.ManagedDts. Please see picture „add_references.jpg”. You go into your solution explorer, right click on References, Add Refference, and from the .NET section, scroll down to the library mentioned upper. This will give you access to the Microsoft.SqlServer.Dts.Runtime namespace.
You will put in the beggining of your cs file:
using Microsoft.SqlServer.Dts.Runtime


The following code is very small and simple:
private void exportDocument()
{
	//You declare a new DTS application object
	Microsoft.SqlServer.Dts.Runtime.Application aplicatie = new Microsoft.SqlServer.Dts.Runtime.Application();
	//You declare your package and load it from the path on disk
			Package export = aplicatie.LoadPackage("D:\\exportdocument.dtsx", null);			
			try
			{
			//You finally execute the package
				DTSExecResult resultat = export.Execute();
			}
			catch (DtsException ex)
			{
				MessageBox.Show("A fost intampinata urmatoarea eroare in timp ce se exporta fisierul: " + ex.Message);
			}

		}


And that’s it! The package will execute the export task, and store the file with the path and name you specified when you created the package!

Now all you need to do, is add in your menu items, buttons or whatever you will use, the execution of the created methods.
I will give you just a self explanatory example:
private void genereazaFacturaToolStripMenuItem_Click(object sender, EventArgs e)
		{
		//We create variables to store values for the input parameters
			string doc = "Fac. Fisc.";
			string factura = "0" + txtNrDoc.Text;
		//We execute first stored procedure by calling the method //with no parameters
			proceduriStocate();
		//We execute the second stored procedure, this time passing //two parameters; c# knows 
								//that this is overloaded, therefor it goes to //the owner method of the signature (wich is the 
								//required parameters)
			 proceduriStocate(doc, factura);
		//Export the package’s result
			exportDocument();
		//Clear the data from tables
			deleteData();
		}



This maybe looks pretty complicated for the first time of usage, and you might think “why not use classic text commands, stream writers, datatables, etc…”. Think about the flexibility of using these methods of handling the task, the power of configuration and modification.
I hope you will find this very usefull and maybe change the thinking for your next applications.
I will give you only one more example of using through C# code very usefull SQL Integration Services properties.
I will keep my promise and show you how two modify the code so you can select the path and filename of the package’s result.

Part 5 – Using Package Configuration Files

For this, you must make two steps. First, is to create a configuration file for your package. This you will make from Business Inteligence Development Studio, wich is a part of Visual Studio after you install SQL Server. You will just open the package file, by File, Open File ...
You will get to the screen in the picture “create_configuration_package.jpg”. I will first explain what I did, and then show the code for c#.
You can create configuration files for packages in many ways, like a SQL table, XML file, system environment path or registry keys. I’ve chosen the SQL table option for now. Basically it creates a table in SQL in wich you may export what properties you want from the package properties list, and with them also their values. So, we will do that, then make a stored procedure to modify one of the values: name
Wich is actually the path and filename of the export result file.
Here are the steps:

We open our file in Visual Studio. After that, we go to the SSIS menu, Package Configuration Organizer (as in the picture mentioned above).
Here, you can already see my configuration, but you will click add because you do not have it. You can see in the next picture that I’ve chosen SQL Server, you will select a connection , and a table in wich to store the settings (just click new and then ok). Then, in the filter name, put whatever you want, you will see in the table what it is. It is a column wich assigns that filter name to all the properties you include in the package. Suppose you have 5 configurations, all stored in the same table, you can distinguish them by the filter value.
In the picture for step three, you can see what properties I exported:
All the properties off the Connection Manager for our destination file (we actually need only “name”, so you can export only that one” ). Now, just hit next and let the wizard finish the tasks.
It wasn’t so hard, was it?  If you go now in your databse, you will see a new table, SSIS Configurations (picture for step four).
So, now all we have to do is to somehow modify that “name” value at runtime. The best solution in my view is to create a stored procedure to do that, wich will have a parameter to pass the value of the path.
Code for new procedure:
CREATE PROCEDURE [dbo].[exportFle] (@location VarChar(100))
AS
 BEGIN 
	update [SSIS Configurations] set ConfiguredValue = @location where PackagePath = '\Package.Connections[DestinationConnectionFlatFile].Properties[ConnectionString]'	
 END


So we have an input parameter to pass the new path, wich we use to update the value of the column ConfiguredValue. I put a ‘where’ clause because I have many properties in my table.

And now, we just need to modify our exportDocument method with this code:
	SaveFileDialog dlgExport = new SaveFileDialog();
			dlgExport.Filter ="Fisiere REX | *.REX";
			dlgExport.ShowDialog();
			string locatie = dlgExport.FileName;
			SqlConnection conexiune = new SqlConnection(sqlConnection);
			SqlCommand cmdExport = new SqlCommand();
			cmdExport.CommandText = "nomenklaturaSQL.dbo.exportaFisier";
			cmdExport.CommandType = CommandType.StoredProcedure;
			SqlParameter fileLocation = new SqlParameter("@location", SqlDbType.VarChar, 100, "input");
			locatieFisier.Value = locatie;
			cmdExport.Parameters.Add(fileLocation);
			try
			{
				cmdExport.Connection = conexiune;
				cmdExport.Connection.Open();
				cmdExport.ExecuteNonQuery();
				cmdExport.Connection.Close();
			}
			catch (SqlException ex)
			{
				MessageBox.Show("A fost intampinata eroarea: " + ex.Message);
			}


Just add the code right in the beginning of the method, before the package execution.
The code is self explanatory: we instantiate a new SaveFileDialog wich will show when you will hit your menu/button, and after you select the location and name where you need it, the application sends the FileName property wich is full path of file to the stored procedure, wich further modifies the value of the package’s path, and then you execute the package, wich will save your file to the desired location.
For now, it is the only way I thought of, regarding DTS packages.

Well folks, this is all!
I think it will get you more to read than to really make this small application. I will soon put the full code of the application, after I optimise the code and put the appropriate comments. I just hope you found this tutorial usefull, and, do not hesitate to let me know of that 

Attached image(s)

  • Attached Image
  • Attached Image
  • Attached Image
  • Attached Image
  • Attached Image
  • Attached Image
  • Attached Image
  • Attached Image
  • Attached Image


Is This A Good Question/Topic? 0
  • +

Replies To: Using SQL stored procedures and Integration Services Packages to expor

#2 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1633
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Posted 03 November 2007 - 01:55 AM

Nice tutorial hoinarut, but I feel the need to point out something that you may or may not realize. The following line in your code

string insertSql = "INSERT INTO dbo.tblMain SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;HDR=yes;Database=" + dbFileName + "',[MAIN$])";


Opens your application/website up for a SQL Injection Attack. If you want your database code to be secure you have 2 options:
  • Parameterized inline SQL
  • Stored procedure

Since you've already got it in inline, lets parameterize this insert statement like so:

SqlConnection conSql = new SqlConnection(sqlConnection);
SqlCommand commandInsExcSql = new SqlCommand();
//Here is an SQL command that takes us out of much code, for doing this importing task
//It inserts in our tblMain all data from sheet MAIN in my case, and specifies that first row is HeaderRow
//by HDR=yes

'Change our inline SQL to use a parameter instead 
of the dbFileName concatenated to the string, 
which helps to prevent SQL Injection attacks
string insertSql = "INSERT INTO dbo.tblMain SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;HDR=yes;Database=@FileName,[MAIN$])";
'We then use the Parameters.AddWithValue to give the value of dbFileName to @FileName
commandInsExcSql.Parameters.AddWithValue("@FileName",dbFileName);
'Then we set various properties of our SqlCommand object
commandInsExcSql.Connection = conSql;
commandInsExcSql .CommandType = CommandType.Text;
commandInsExcSql.CommandText - insertSql;
try
	 {
		   commandInsExcSql.Connection.Open();
		   commandInsExcSql.ExecuteNonQuery();
		   commandInsExcSql.Connection.Close();
	   }
		catch (Exception ex)
		{
				MessageBox.Show(ex.Message);
		  }



You will also notice I made a few minor changes to how the SqlCommand object was declared, I removed the connection object and the SQL string from it in the declaration, and set those items when I add the property to the inline SQL string.

Other than that one change, on first glimpse thats all I see, nice job :)
Was This Post Helpful? 0
  • +
  • -

#3 hoinarut  Icon User is offline

  • New D.I.C Head
  • member icon

Reputation: 5
  • View blog
  • Posts: 5
  • Joined: 25-October 07

Posted 05 November 2007 - 01:39 AM

View PostPsychoCoder, on 3 Nov, 2007 - 10:55 AM, said:

You will also notice I made a few minor changes to how the SqlCommand object was declared, I removed the connection object and the SQL string from it in the declaration, and set those items when I add the property to the inline SQL string.

Other than that one change, on first glimpse thats all I see, nice job :)

Thank you very much for the correction. I've seen in SQL doc, and also in Surface Area Config that ad-hoc queries is disabled by default, for the reason that it might get "violated". But, on the other hand, this was an ex for an app i use on my own laptop, and it wasn't in discussion the security problem. But, maybe someone adapts it for his / hers needs, and it is a vital information and correction the one you made. Thank you again ... sharing information is the greatest thing possible! :)
Was This Post Helpful? 0
  • +
  • -

#4 Guest_Ravinder Sirohi*


Reputation:

Posted 17 March 2010 - 02:50 PM

I want to know to develop complete SSIS package from C#.net and then able to open that package in BI IDE environment, just a kind of template of SSIS package having 2 connnection, 1 dataflow task.
Was This Post Helpful? 0

Page 1 of 1