Create A Fixed Text File From SQL DB Table
Question
Answers
-
User-952121411 posted
Hello and welcome to .NET! I have a few suggestions on how to go about solving your need in general, but I must start by stating that you did not give any specefics on what the exact issue you were having with your code. Therefore, I can give some general reccomendations on how to solve your problem.
1st off, I was once where you are; going from a VB6 to a .NET developer and I can tell by the way you wrote your .NET code that you are trying to do things in .NET the VB6 way (#Print, calling variables 'objrec' for Recordsets, etc). My suggestion is as you develop to try and find the .NET equivilent of how to do the same end result that you did back in VB6. For example classic ADO with recordsets was replaced with ADO.NET and DataSets, DataTables, DataReaders, etc. They have some of the same principals as being disconnected data objects (except the DataReader which is connected), but are much more powerful. Learn them well as they can and will be the backbone of your data access methods going forward (until Microsoft comes out with a new data access method that replaces ADO.NET
; they have never done that before, right? DAO,RDO,ADO,ADO.NET,EF,
etc...).I reccomend looking at some of the links within the link below to continue familiarizing yourself with ADO.NET:
ADO.NET:
http://msdn.microsoft.com/en-us/library/e80y5yhx(VS.80).aspx
As far as writiing to or manipulating files, your new fried will be the System.IO or System.Text namespaces in .NET. Those framework libraries offer a vast amount of wrapped up functionality to write/read/ and do all sorts of manipulation that you need.
To get you started, here is a terrific example from the MSDN on how to use a 'StreamWriter' object (from the System.IO namespace) to write to a text file:
How to: Write Text to a File:
http://msdn.microsoft.com/en-us/library/6ka1wd3w.aspx
Here is another good link-
Basic File I/O:
http://msdn.microsoft.com/en-us/library/336wast5.aspx
If I was doing what you are trying to do by reading all records returned from a database into a formatted text file, I would modify the code to look more like the following:
'Import at the top of the class Imports System.IO Imports System.Data.SqlClient
The code that goes within the class:Dim ds As New DataSet Dim da As New SqlDataAdapter Dim conn As New SqlConnection Dim cmd As New SqlCommand Try 'Open a connection to the database conn.ConnectionString = "Data Source=CODA2K;Persist Security Info=True;password=codaprod; USER;Initial Catalog=CODAPROD;" conn.Open() 'Build command object using string SQL and DB cmd.CommandText = "select * from tbl2txt" cmd.Connection = conn da.SelectCommand = cmd da.Fill(ds) 'Close the db connection conn.Close() 'Create a StreamWriter to write to the text file Dim sw As New System.IO.StreamWriter("C:\MyLog.txt", True) 'Loop through each DataRow in the DataTable within the DataSet's 1st table (index 0) For Each dr As DataRow In ds.Tables(0).Rows 'Writing to file (add TimeStamp to beginning) sw.WriteLine(DateTime.Now.ToShortDateString() & ": This is line 1!") sw.WriteLine("This is line 2!") sw.WriteLine("This is line 3!") Next 'Clean up sw.Flush() sw.Close() Catch ex As SqlException 'Do any SQL exception handling here if needed/wanted Catch ex As Exception 'Do any exception handling here if needed/wanted Finally 'Clean Up conn.Dispose() ds.Dispose() da.Dispose() End Try
Now the exception handling, clean up, etc are very basic here but I am just trying to provide a good example of how to use ADO.NET and a StreamWriter to accomplish what you need to do. I know the advice and content may have been more than what you needed, but I remember some of the road bumps moving to .NET as well, so I know what it is like.If you still have issues or want to post back with another question, please do so.
Hope this helps!
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
All replies
-
User-952121411 posted
Hello and welcome to .NET! I have a few suggestions on how to go about solving your need in general, but I must start by stating that you did not give any specefics on what the exact issue you were having with your code. Therefore, I can give some general reccomendations on how to solve your problem.
1st off, I was once where you are; going from a VB6 to a .NET developer and I can tell by the way you wrote your .NET code that you are trying to do things in .NET the VB6 way (#Print, calling variables 'objrec' for Recordsets, etc). My suggestion is as you develop to try and find the .NET equivilent of how to do the same end result that you did back in VB6. For example classic ADO with recordsets was replaced with ADO.NET and DataSets, DataTables, DataReaders, etc. They have some of the same principals as being disconnected data objects (except the DataReader which is connected), but are much more powerful. Learn them well as they can and will be the backbone of your data access methods going forward (until Microsoft comes out with a new data access method that replaces ADO.NET
; they have never done that before, right? DAO,RDO,ADO,ADO.NET,EF,
etc...).I reccomend looking at some of the links within the link below to continue familiarizing yourself with ADO.NET:
ADO.NET:
http://msdn.microsoft.com/en-us/library/e80y5yhx(VS.80).aspx
As far as writiing to or manipulating files, your new fried will be the System.IO or System.Text namespaces in .NET. Those framework libraries offer a vast amount of wrapped up functionality to write/read/ and do all sorts of manipulation that you need.
To get you started, here is a terrific example from the MSDN on how to use a 'StreamWriter' object (from the System.IO namespace) to write to a text file:
How to: Write Text to a File:
http://msdn.microsoft.com/en-us/library/6ka1wd3w.aspx
Here is another good link-
Basic File I/O:
http://msdn.microsoft.com/en-us/library/336wast5.aspx
If I was doing what you are trying to do by reading all records returned from a database into a formatted text file, I would modify the code to look more like the following:
'Import at the top of the class Imports System.IO Imports System.Data.SqlClient
The code that goes within the class:Dim ds As New DataSet Dim da As New SqlDataAdapter Dim conn As New SqlConnection Dim cmd As New SqlCommand Try 'Open a connection to the database conn.ConnectionString = "Data Source=CODA2K;Persist Security Info=True;password=codaprod; USER;Initial Catalog=CODAPROD;" conn.Open() 'Build command object using string SQL and DB cmd.CommandText = "select * from tbl2txt" cmd.Connection = conn da.SelectCommand = cmd da.Fill(ds) 'Close the db connection conn.Close() 'Create a StreamWriter to write to the text file Dim sw As New System.IO.StreamWriter("C:\MyLog.txt", True) 'Loop through each DataRow in the DataTable within the DataSet's 1st table (index 0) For Each dr As DataRow In ds.Tables(0).Rows 'Writing to file (add TimeStamp to beginning) sw.WriteLine(DateTime.Now.ToShortDateString() & ": This is line 1!") sw.WriteLine("This is line 2!") sw.WriteLine("This is line 3!") Next 'Clean up sw.Flush() sw.Close() Catch ex As SqlException 'Do any SQL exception handling here if needed/wanted Catch ex As Exception 'Do any exception handling here if needed/wanted Finally 'Clean Up conn.Dispose() ds.Dispose() da.Dispose() End Try
Now the exception handling, clean up, etc are very basic here but I am just trying to provide a good example of how to use ADO.NET and a StreamWriter to accomplish what you need to do. I know the advice and content may have been more than what you needed, but I remember some of the road bumps moving to .NET as well, so I know what it is like.If you still have issues or want to post back with another question, please do so.
Hope this helps!
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
