Best Way to Continuously Write Many Values into SQL Table

[TexasAggie]

I write a program to run test beds that log anywhere between 1 and 400 physical values (temperature, pressure, etc.). Presently, I buffer 100 lines and then write to a synchronized text writer. The format is text tab delimited. I would like to write the data to SQL as well so that other engineers can plot data or run queries.

 

I have two options:

 

1. Buffer and write while the test sequence is running; or (preferred)

2. Programmatically convert the .txt file to a table or insert all rows into a SQL table. (not preferred)

 

Data is logged at 1Hz, but like I said, buffered into 100 lines to reduce HD access occurrences.

 

What would be the best way to carry out option 1? Is there a limit to the amount of data that can be inserted in one line? The insert string would be HUGE.

 

Thanks.

[lhite]

if you already have the data in a tab delimited text file, why not just use a BULK INSERT command:

 

BULK INSERT tablename FROM 'textfile.txt' WITH (FIELDTERMINATOR = '\t', ROWTERMINATOR = '\n')

 

Texas Longhorn <G>

[TexasAggie]

From the looks of the command, I guess it would work after the test is complete. If it is too difficult to write 70-400 parameters at run time then that is the option I'll have to take.

 

The ability to write to the database at run time enables me to run queries and plot data at run time rather than waiting until the test is complete. Tests are 30 minutes to 6 days in length.

 

For the run time method, if I enter values for A, B, D, and E, what value exists for that record for C? The default value given the data type (0 or "")?

 

I can build the string. The only issue is how long it is. I guess if SQL can handle it I shouldn't worry too much. Should I write every second or would it be much more efficient to write every 100s or so?

Edited April 25, 2012 by TexasAggie

[lhite]

i guess i'm not understanding the question. i thought you said you already were generating a text file as part of the buffering process -- and if that's the case, why not just do a SQL BULK INSERT using that text file each time the buffer is filled? seems like an easy one-line solution.

 

if for whatever reasons you don't want to do that, then yeah, just do plain old SQL inserts. but i thought that's what you were trying to avoid.

[TexasAggie]

Is it possible to use that command each time I add 100 lines to the file? I write to the text file in 100 line increments. Would the bulk insert not insert old lines every time?

[lhite]

ah, i see what you are getting at. no, you wouldn't want to import that file every time. i didn't realize you were appending directly to a file. what you would have to do is write the buffer to a temp file, then import that file to the SQL database, then append that temp file to your other file.

[TexasAggie]

Hah. Good point. I think I will try that.

 

How will it know the column headers? Will it look at the first line or does the data have to match the table architecture? What happens if I add or remove a channel?

[lhite]

BULK INSERT is not very flexible. if you have a header record, you'll have to remove that before doing the INSERT. also, it expects the fields to be in the correct order, matching the table structure. and if you add/remove fields, you will have to adjust the layout of the TXT import file accordingly.

[TexasAggie]

Thanks for all of your help.

[lhite]

good luck in the SEC. <G> hook 'em!

[TexasAggie]

I have tested this on my Development computer and the code is fairly simple.

 

        
       Columns = "[RowCount]"
       Data = RowCount 'RowCount will be the primary key and will always increment
       RowCount += 1

       For count = 1 To lstChannels.Items.Count ' I will substitute my data arrays for the items in this test list
           Columns &= ",[" & lstChannels.Items.Item(count - 1) & "]"
           Data &= "," & Rnd(10)
       Next

       Dim cmdString As String = "INSERT INTO Development (" & Columns & ") VALUES ( " & Data & ")"
       sqlCmd = New SqlCommand(cmdString, sqlConn)
       sqlCmd.ExecuteNonQuery()

 

Sound about right?

 

One more question: When I add another channel to the table, all values before that addition for that channel are NULL. Is it possible to make this default to zero since these are all numeric values?

Edited May 3, 2012 by TexasAggie

[TexasAggie]

I have this system set up and working, but it will only run for so long (less than a few hours) before it throws an error. The error is shown below. Any idea what would cause this? Why would I get an object reference error when it has already written many lines before? I need to know if there is a SQL problem or a problem with my software.

 

9:41:28 FlightRecorder

System.NullReferenceException: Object reference not set to an instance of an object.

at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)

at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)

at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)

at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)

at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

at DEER_DAQ.modFunctions.BuildSQLDataString()

[PlausiblyDamp]

Are you using the same connection for the inserts or opening and closing the connection on each insert?

 

Which line of code is throwing the error in your code?

[TexasAggie]

The connection remains open for the duration of the program execution.

 

I can only speculate that the line that calls for ExecuteNonQuery is the line that fails since its call shows up in the error stack trace.

[TexasAggie]

I just caught this error while testing it out (text message alert on error to allow me to check it quickly). Why would I lose connection? Because I'm developing this on Developer Edition?

 

ExecuteNonQuery requires an open and available Connection. The connection's current state: Broken.

[TexasAggie]

I added a check for connection state to work around that problem.

 

Now I was testing again on a computer we will call B, and I saved my Visual Studio project on a computer we will call A. A is also running the instance of SQL Server Developer Edition which B is connected to. Simultaneously, B threw an object reference error on the Executing Non Query statement.

 

Could this be because A (running Windows 7 pro) was busy and didn't manage SQL Server well enough? Can I expect this problem to go away when we get SQL Server 2012 running on a dedicated Windows Server machine?

 

Thanks.