none
Write data to SQL database

    Question

  • I have a small program that I am writing and it scans through a .txt file to find "key" information for me.  The "key" information is stored in a 1-dimension arrary temporarily.  As I find the data I want to write it to a SQL database for storage until I call for the data to populate a calendar in CrystalReports.  Can someone please help me! I don't know how to do anything with SQL in VB.NET .
    Monday, February 12, 2007 6:49 PM

Answers

  • The best way to build a query from stored strings is to use parameters.  This comes out to something like:

    Dim oInsert As New SqlClient.SqlCommand("INSERT INTO Employees EmployeeID, First_Name, Last_Name VALUES (@ID, @First, @Last)", oConnection)

     

    oInsert.Parameters.AddWithValue("ID", StoredEmployeeID)

    oInsert.Parameters.AddWithValue("First", StoredEmployeeFirstName)

    oInsert.Parameters.AddWithValue("Last", StoredEmployeeLastName)

     

    LineCount = oInsert.ExecuteNonQuery() ' deceptively named, since you DO have a query...

     

    If LineCount > 0 Then

        ' insert was successful

    Else

        ' insert failed

    End If

     

    Thursday, February 15, 2007 5:55 PM

All replies

  • DMan1,

        Thanks for the links but I'm still as confused as before.  Can anyone else help me?

    QWERTYtech

    Monday, February 12, 2007 7:47 PM
  • You will recieve better help if you post the actual code you are having problems with and any error message that you are running into!
    Tuesday, February 13, 2007 5:33 AM
  • See thats the thing.  I'm pretty new to creating, opening/closing, and reading/writing db's.  I just need some code to get me started. 

     

    Tuesday, February 13, 2007 2:05 PM
  • Hi

    A good ADO.NET book is probably the best place to start.

    Check out the System.Data namespace.  Depending on the data source (eg oracle, sql server) there are various different base class implementations you would prefer to use, but as a starter, take a look at ...

    System.Data.SqlClient (SQL Server specific implementations)

    SqlConnection ... handles connection to a database

    SqlCommand ..... handles issuing commands against a connection

    SqlTransaction ... defines transaction boundaries

    SqlDateAdaptor / SqlDataReader etc ... for reading data into eg a dataset

    Personally, I rarely work with these classes direct and prefer to delegate to the enterprise services addins from Microsoft ... Microsoft.Practices.EnterpriseLibrary.Data   The Data factory abstracts most of the db interaction into a simple api .. well worth an inspection if you are working with SQL Server.

    Good luck

    Richard

    Tuesday, February 13, 2007 4:47 PM
  • Okay,

      So say that I have a db called Schedule with two tables inside of it: Employee and Patient.  The following are the entities of each tables.

    Employee
       
    Employee_ID  (pk)
        Employee_First_Name
        Employee_Last_Name

    Patient
       
    Patient_ID  (pk)
        Patient_First_Name
        Patient_Last_Name
        Patient_Date
        Patient_Notes
        Employee_ID
    (fk)

    As the program scans the a .txt file for information, how should I feed that info into the SQL db?  I want it to write the data to the db as soon as it finishes each record in the .txt file.   Im currnetly using Split to gather the info that I want off of each line. 

    Also, on a side note.  In my .txt file, I have a section callled Notes: and notes can sometimes continure onto another line underneath it and I was wondering how I would capture all that info with a slip or if I should even be using the Split function for this.  This is what my current Split function looks like.....

    Dim NotesSplitValues() As String = {"Notes:", ""}


    Thanks for any help,

        QWERTYtech

    Tuesday, February 13, 2007 8:02 PM
  • Hi

    There isn't enough information here for me to give you some robust code, so I've included below a simple flow structure which you may be able to adapt to your exact needs .. something along these lines .. (I've assumed you are using sql server)

    Using cn As New SqlClient.SqlConnection("connStr")

    Using sr As IO.StreamReader = My.Computer.FileSystem.OpenTextFileReader("filepath")

    cn.Open()

    Using trans As SqlClient.SqlTransaction = cn.BeginTransaction

    Do While sr.Peek >= 0

    Dim data As String = sr.ReadLine

    '' Split the data row up into the relevant bits per the layout of your text file

    '' Now build the command objects that will call the stored procedures used to insert the data

    Dim cmd As New SqlClient.SqlCommand("dbo.InsertEmployee", cn)

    cmd.CommandType = CommandType.StoredProcedure

    '' Add all the parameters you need

    Dim param As SqlClient.SqlParameter = cmd.CreateParameter

    param.DbType = DbType........

    param.Value = ParamValue

    cmd.Parameters.Add(param)

    '' Execute the stored procedure

    cmd.ExecuteNonQuery()

    Loop

    trans.Commit()

    End Using

    End Using

    End Using

    Apologies if there are errors therein as I haven't actually done this for quite some time, but it serves as an example of which types you might choose to use and how you might go about using them.

    I started by creating a connection object.  This requires a connection string to locate the relevant database to insert into (see www.connectionstrings.com if you need help here).

    Next we open the connection and create a transaction.  Transactions are used to package bulk operations into a logical atomic group.  The idea here is that if an exception is raised an some point within the group insert, the whole update process can be rolled back as if it never happened.  These may or may not be the semantics you are looking for and if you are unfamiliar with transaction processing I'd recommend you take some time out to read up on them before proceeding much further with your application.

    The next interesting part is the use of a command object.  There are many reasons why this is the preferred mechanism for adding data to your database, but suffice to say at this juncture, you simply create a stored procedure in your database that accepts the incoming data through parameters.  The stored proc will do the actual inserting/validation etc.  There are lots of online examples on how to create a stored procedure.

    The command object requires a parameter object for each parameter the stored procedure accepts.  I have shown an simple example of adding one, however you will need to look closely at this objects properties (eg size, precision) which are dependant on the underlying parameters data type.  Again, there are loads of articles on line that you should easily find.

    Once all the parameters are added, execute the command object and (assuming it succeeds) move onto the next row.

    Once all the data is added successfully, commit the transaction (rollback if there is an exception at some point).

    Now the above is a very simple example that you will want to expand upon.  There are many ways to configure connections etc to better suit your environment (isolation levels etc) so I would urge you to work on the enclosed and not actually use it as is.

    Hope this helps set you off along the "right" path and good luck.

    Richard

    PS - With regard to extract the data from your text file; this operation is as simple as the structure of the data therein lets it be.  If the parse is too complicated, maybe look to change the file layout to ease the operation.

    Wednesday, February 14, 2007 10:16 AM
  • I actually don't have the ability to change the way the info in the .txt file is layed out.  It is generated by another program we use.  That program doesn't do what we need it to do so we are writing a small app to take the information it gives us in the .txt file and are using it to make something more useful.  In my case I have to take the data from the .txt file and read it in and store it until I finish the entire .txt file, then I have to organize the data into a Calendar for each employee.
    Wednesday, February 14, 2007 2:39 PM
  • First, you need to create the Dataset and set it up with the tables and columns you want.  Add a new file to your project, and in the wizard that follows indicate that you are creating a Dataset (I'll call it the default value of Dataset1).  If you have an underlying database, create a TableAdapter for each of your tables (otherwise, create a DataTable for each table and add the columns yourself).  The wizard that follows will ask for two basic things:

    1.  A connection string leading to your database.

    2.  An SQL statement describing what data you want the table to represent.  In this case, you probably want to use "Select * FROM Table", replacing Table with the table name (Employee or Patient).

    You want to be sure the wizard generates Insert statements (at least), because that is the one you'll be using the most.  Once you have the two TableAdapters, you have the basic skeleton you need for database access.  Note the names of the TableAdapters, because you will be using them in code.

    Now, in your code, set up an instance of your Dataset and the two TableAdapters.  This should look something like:

    Dim oData As New DataSet1 ' or whatever you called the DataSet you created

    Dim oEmployeeAdapter As New Dataset1TableAdapters.EmployeeTableAdapter

    Dim oPatientAdapter As New Dataset1TableAdapters.PatientTableAdapter

    Now you start parsing your text file, inserting rows into the tables as needed:

    While Not EndOfFile ‘ or whatever you use to read the file

      ‘ read and line of text and parse it

      ‘ decide which table and which the text is supposed to be put in

      ‘ put the text in a row of the appropriate table

      ‘ for instance, say an array named Parsed() has data for the Employee table:

       

      Select Case RowType

        Case “Employee”

          Dim row As New oData.Employee.NewEmployeeRow

          row.EmployeeID = Parsed(0)

          row.First_Name = Parsed(1)

          row.Last_Name = Parsed(2)

          oData.Employee.Rows.Add(row)

        Case “Patient”

          ‘ code to add patient row

      End Select

    End While

    Your dataset will be ready to hook up to the report now, but you need to issue an update statement to permanently assign the data to the database:

    oEmployeeAdapter.Update(oData.Employee)

    oPatientAdapter.Update(oData.Patient)

    I've spelled it out as much as I am capable.  You should be able to tackle the rest on your own.

    Wednesday, February 14, 2007 4:21 PM
  • Okay so I have found a code snippit on the web for a basic Insertion of data into a db.  http://www.startvbdotnet.com/ado/sqlserver1.aspx

    I'm looking at the insert command and I was wondering how I could use it with data being gathered from my program.

    myCommand = New SqlCommand("Insert into Employee values .........")

    I not sure how to pass values to the insert command that are stored in a temporary String. 

    Can someone please help me?

     

    QWERTYtech

    Thursday, February 15, 2007 3:29 PM
  • The best way to build a query from stored strings is to use parameters.  This comes out to something like:

    Dim oInsert As New SqlClient.SqlCommand("INSERT INTO Employees EmployeeID, First_Name, Last_Name VALUES (@ID, @First, @Last)", oConnection)

     

    oInsert.Parameters.AddWithValue("ID", StoredEmployeeID)

    oInsert.Parameters.AddWithValue("First", StoredEmployeeFirstName)

    oInsert.Parameters.AddWithValue("Last", StoredEmployeeLastName)

     

    LineCount = oInsert.ExecuteNonQuery() ' deceptively named, since you DO have a query...

     

    If LineCount > 0 Then

        ' insert was successful

    Else

        ' insert failed

    End If

     

    Thursday, February 15, 2007 5:55 PM
  • Thanks alot that really helps me out.... One quick question though...... I have two tables Employee & Patient .  Employee has a PK of Employee_ID that is autogenerated.  I also have a Patient table that has Patient_ID as PK and Employee_ID as FK.  How would i link these together?
    Thursday, February 15, 2007 6:24 PM
  • I'm not sure what you mean by "link these together" so I will make some guesses.

    You don't need to include autogenerated fields in INSERT statements, so you can leave out the _ID fields and values (since you have no idea what they are before inserting them).  If you want to know the key value of what you just entered, use ExecuteScalar rather than ExecuteNonQuery(), like:

    key = theSqlCommand.ExecuteScalar()

    This will return the first column of the first row added (make sure the primary key is the first column).

    I don't know how your text file stores patient records.  It probably either has its own employee ID's, or it uses something like the employee's name.  If it's the name, then the employee ID can be determined by running a SELECT query on the database.  Something like:

    Dim oData as New DataSet

    oData.Tables.Add("Employee")

    Dim oSelect as New SqlCommand("SELECT Employee_ID FROM Employees WHERE First_Name=@First AND Last_Name=@Last ", oConnection)

    oSelect.AddWithValue("First", StoredFirstName)

    oSelect.AddWithValue("Last", StoredLastName)

    Dim oAdapter As New SqlDataAdapter(oSelect)

    oAdapter.Fill(oData.Tables("Employee")

    If oData.Tables("Employee").Rows.Count > 0 Then

        StoredEmployeeID = oData.Tables("Employee").Rows(0).Item("Employee_ID")

    Else

        ' No employees of this name... think about adding the employee here!

    End If

    Thursday, February 15, 2007 6:59 PM
  • Hi

    I'd recommend you try and stay away from issuing sql statements from within your application if you can.  It's a bad design and makes your application less secure, less performant, less scalable and generally less abstracted from the data tier.

    Try to use stored procedures to handle the inserts etc (assuming your db supports them) as shown in my previous example.

    If you are using sql server, in the stored procedure, you can use the SCOPE_IDENTITY function to return the identity id assigned to the insert of the Employee etc and then use that as the foreign key in your other table ...

    eg (exception handling etc omitted for clarity)

    create procedure addData(@Name varchar ....... ) as

    begin

    insert into dbo.employee(name, ....) values (@name ....);

    declare @employeeId integer;

    set @employeeId = scope_identity();

    insert into dbo.patient ( ... employeeid ....) values ( .... @employeeid ... );

    end

    Richard

    Friday, February 16, 2007 9:36 AM
  • Richard,

      What I'm trying to do is write a small app that will allow our employee's to run a report that is generated by our "Patient Care" software and use some of the info from it to build a weekly Calendar of what patients each employee is seeing.  What would u reconmend doing to store the information to then turn around and read it into a CrystalReport to populate my Calendars?

     

    Thanks,

      QWERTYtech

    Friday, February 16, 2007 5:38 PM
  • Hi,

     

    Got a very simple question, I'm trying to follow the instructions above to add some rows to a database and I'm getting a little confused.

     

    Dim row As New oData.Employee.NewEmployeeRow

     

    I can't repeat the above line, here's what I got

     

    Dim odata As New ServiceArchiveDBDataSet

    Dim service As New ServiceArchiveDBDataSetTableAdapters.ServicesTableAdapter

     

    and when I try to make

     

    dim row as new, odata is not one of the options to use. The only thing similar I found was

    Dim row As ServiceArchiveDBDataSet.ServicesRow

    but that don't work ofcourse.

     

    ServiceArchiveDBDataset was created with a wizard

    ServicesTableAdapter was created with a wizard as well.

     

    Thanks everyone.

     

    Viktor.

    Friday, March 28, 2008 7:39 PM
  • Thanks to the snippets I found the answer, I was supposed to do this:

    Dim newRow As ServiceArchiveDBDataSet.ServicesRow = Me.ServiceArchiveDBDataSet.Services.NewRow()

     

     

     

    Friday, March 28, 2008 9:59 PM