Programming from MS Sql server to C#

[ehx6]

hi,
I am interested to convert my code from MS sql store procedures to C# and have the same resutls. All my code are inside store procedure which contains the following
1. Cursor to iterate through rows

2. Executing DTS to Export Data to Excel code :
declare @cmd varchar(200),@ZipFile
select @cmd ='dtsrun /S EH /N DtsName /UBob /Pmypassword'
exec @cmd = master..xp_cmdshell @cmd
-- Calling to execute DTS package inside store procedure

3. Executing a batch file to zip folder content:
select @cmd =null
select @cmd = 'd:\Batch_code\zip -j d:\OUT\'+@ZipFile+' d:\OUT\*.*'
exec master..xp_cmdshell @cmd

4.Move the zipped file from the production server to another server to keep my data else ware in case I lost my production server, so I have a backup copy of my data else ware:
select @cmd='move D:\OUT\'+@ZipFile+'\\PcName\BackupFolder '
exec master..xp_cmdshell @cmd

The above steps basically does the following:
1 + 2 Export the database tables to both excel file and to text file to a folder in the production server
3 will zip the files
4 will move the zip file which contain production data to another PC in the Network in case the production server crashes , so I have a backup copy of my data.

My question is how I can write the same functions in C#, any advice
thanks
Ehx

 

[Korach]

1. Fetch data from SQL server:

SqlConnection myConnection = new SqlConnection(myConnectionString);
   SqlCommand myCommand = new SqlCommand(mySelectQuery, myConnection);
   myConnection.Open();
   SqlDataReader myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
   myReader.Close();
   myConnection.Close();

2. Write data to Excel file:
There are some differences between excel versions. This should work with excel 2000. There are some ways to do it.

Excel.Application ExcelApplication = New Excel.Application;
Excel.Workbook ExcelWorkbook = ExcelApplication.Workbooks.Add();
While (myReader.Read())
{
   ExcelWorkbook.Worksheets.Application.Cells(i,j) = myReader["FieldName"];
}

3. Write to a text file:

using (StreamWriter sw = new StreamWriter("TestFile.txt")) 
        {
            While (myReader.Read())
            {
                sw.Write(myReader["FieldName1"].ToString() + ", ");
                sw.WriteLine(myReader["FieldName2"].ToString());
            }
         }

4. Make a zip file:
I don't know if there is a zip class in .NET, but you can find a command line zip program and call it:

Process.Start("C:\\Program Files\\Zip\\Zip.exe", "C:\\Text1.txt C:\\Excel.xls");

5. Copy a file:

IO.File.Copy("C:\\MyZip.Zip", "D:\\Backup\\MyZipBackup.zip");

 

[ehx6]

Thanks Korach for your input. It seems that there are already code created for me in classes in .net . So I guess what I need to do is to know how to find those classess and know their cababilities. Is there any programming knowledge that I need to know in order to utilze those classes.

Also, where I can have a list of all the .net Classes. I guess the important is how to know what class I need.

Correct me if I am wrong, if .Net has provided the necessary classess for developers; what are the common , popular classess in your opinion.

thanks agin
Ehx

 

[Korach]

There is a lot of programming knowledge you need to know. One of the great things in .NET is the speed of the knowledge earning, Thanks to the object oriented structure of the .NET classes, the MSDN help, and many examples in the internet.
.NET classes are arranged in namespaces. "System" is the root namespace. It contains many classes and other namespaces. Don't waste time on reading the help like it was a novell... Think what is the functionality you need, search examples in google, like: "sql excel c#".
I recommend you read a book. If you know programming, you can read it in a few days.
Common, popular classes: (I no anyone will argue with me about that)
File
Directory
Stream
StreamReader
Math
Control (Form, Button, TextBox etc...)
UserControl
string
DateTime
Convert
Object
DataSet
DataTable
DataView
DataReader
DataRelation
SqlCommand
SqlDataAdapter
SqlParameter
Process
Environment
Exception
Array
ArrayList
CollectionBase
ListDictionary
Type
EventHandler
Graphics
Color
Brush
Pen
Font
CultureInfo
Thread
Timer
XmlSerializer

I almost forgot the MessageBox class!!! How will you make your users angry without this class?

 

[ehx6]

Hi Korach,

The most populare classes you mentioned about are a good jump start. Thanks for your input

Ehx

 

[chiph]

Most people use SharpZipLib for their compression needs:

http://www.icsharpcode.net/OpenSource/SharpZipLib/Default.aspx

Chip H.


____________________________________________________________________
Click here to learn Ways to help with Tsunami Relief
If you want to get the best response to a question, please read FAQ222-2244 first