Solved

How to get one variable into SQL Express

Posted on 2009-05-12
8
264 Views
Last Modified: 2013-11-27
Hi all, Using vb.net/2005/.net 2  - I have a simple program that produces large numbers of one variable of 110 characters.  There is no user involvement.  I need to get the variable into a field in SQL Express 2005 where it is then broken up into its constituent parts.  The variables are produced quite rapidly so the solution needs to be fast and stable. Would appreciate some instruction and code samples of the best method.  Thanks in advance.          
0
Comment
Question by:PNRT
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 24372481
I am unable to understand your description so would appreciate if you could provide sonme more information.
0
 
LVL 2

Author Comment

by:PNRT
ID: 24374722
Thanks for the reply.
I have a VB.Net program that produces a string that is then output as a simple line of text 110 characters long.  Currently this line of text is sent to a text file at a rate of about 2000 lines per minute. I am running SQL Express on the same server.  Instead of outputing this data to a text file, I would like to send it to a database in SQL Express. There is no user input in the program, the data is produced automatically.  I am just looking for the quickest and most reliable way of connecting to the database and getting the line of text into the required column, but at the speed and volumes I have mentioned.   Thanks.
0
 
LVL 2

Author Comment

by:PNRT
ID: 24375243
I thought I would also include the code that sends to the text file.  Very simple as you can see.  I would like to find the best way of getting  Str2Write  into SQL  - Thanks

Private Sub Write_To_File(ByVal Str2Write As String)
        If File.Exists("C:\DATA\Data.txt") = False Then
            fs1 = File.CreateText("c:\DATA\Data.txt")
            fs1.Close()
            fs1 = Nothing
        End If
        fs1 = File.AppendText("C:\DATA\Data.txt")
        fs1.Write(Str2Write)
        fs1.Close()
        fs1 = Nothing
    End Sub
0
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 
LVL 83

Expert Comment

by:CodeCruiser
ID: 24375363
Try with this code. Note that if this code snippet is to be called 2000 times a minute, it would be worth it to have the connection created and opened outside of this function and just executing the command within this function because creating a connection could be a overhead.
Dim dbcon as new SqlConnection(connectionstring)
dbcon.open
dim dbcmd as new SqlCommand
dbcmd.connection = dbcon
dbcmd.commandtext = "Insert Into TableName(ColumnName) Values('" & str2write & "')"
dbcmd.executenonquery
 
 
'If you want to write this line multiple times then
for i as integer = 0 to 2000
dbcmd.commandtext = "Insert Into TableName(ColumnName) Values('" & str2write & "')"
dbcmd.executenonquery
next

Open in new window

0
 
LVL 2

Author Comment

by:PNRT
ID: 24380390
It worked first time CC - Many Thanks. As I mentioned, there is a lot of data and  I really like the advice about reducing the overhead by creating and opening the connection seperately.  Could you please show a way this could be done?  Would it be a seperate function or something?
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 24382282
It depends on how and where your data is being generated. Can you show me some code?
0
 
LVL 2

Author Comment

by:PNRT
ID: 24383062
Hi
This is a legacy system that I'm trying to upgrade piece by piece as the system is still being used and
I cant get into the code of the original program.  Three old programs produce string data into text files with sequential filenames.  My program combines these  into a single text file, with other data in a particular format that is used locally. As you can imagine there is a nightly backlog as the system is very slow, but as I replace the original programs getting away from text files, the process will hopefully be much quicker.   I have tested your example into SQL and it works fine, its just to follow your suggestion of creating and opening the connection seperately.

 Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick

Dim account_File As String = ""
Dim Finance_File As String = ""
Dim MKT_File As String = ""
Dim Str2Write As String = ""
Dim fs1 As StreamWriter

'SEQU is obtained and saved when program opens and closes

account_File = "C:\DATA\Accounts" & SEQU & ".txt"
Finance_File = "C:\DATA\Finance" & SEQU & ".txt"
MKT_File = "C:\DATA\MKT" & SEQU & ".txt"

Dim Accounts As String = ""
Dim Finance As String = ""
Dim MKT As String = ""

If File.Exists(Finance_File) = False Or File.Exists(account_File) = False Or File.Exists(MKT_File) = False Then
Exit Sub
End If

Accounts = My.Computer.FileSystem.ReadAllText(account_File)
Finance = My.Computer.FileSystem.ReadAllText(Finance_File)
MKT = My.Computer.FileSystem.ReadAllText(MKT_File)

File.Delete(account_File)
File.Delete(Finance_File)
File.Delete(MKT_File)

Str2Write = Now & Accounts & Finance & MKT & Lead_Form  'Lead_Form from formula elsewhere in prgram

If File.Exists("C:\DATA\Data.txt") = False Then
fs1 = File.CreateText("c:\DATA\Data.txt")
fs1.Close()
fs1 = Nothing
End If
fs1 = File.AppendText("C:\DATA\Data.txt")
fs1.Write(Str2Write)
fs1.Close()
fs1 = Nothing

SEQU = SEQU + 1

End Sub
0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 500 total points
ID: 24383086
Ok i can see the bigger picture now. You would create and open the connection and the command objects in the start of the application. i.e, move these lines to the form load event

Dim dbcon as new SqlConnection(connectionstring)
dbcon.open
dim dbcmd as new SqlCommand
dbcmd.connection = dbcon

and place these lines into the Timer_Tick event above
dbcmd.commandtext = "Insert Into TableName(ColumnName) Values('" & str2write & "')"
dbcmd.executenonquery
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question