Solved

inserting data from a class object into a database

Posted on 2006-07-10
6
404 Views
Last Modified: 2010-04-16
Can some1 tell me how data stored in a object be inserted into a MS Access Database using  C# . I have written a program in which data is stored in different variables of a class object and i need to insert all of it into a MS Access Database.

thanks  
0
Comment
Question by:jhav1594
6 Comments
 
LVL 2

Expert Comment

by:shy_talk
ID: 17073757
As I recall, you need to make your class Serializable, by implementing the ISerializable interface within the class. That way you can save your object as an XML schema. From there the schema can either be imported directly into the database, or a DataSet via the ReadXMLSchema or Infer XMLSchema.


Help file extract:

Serialization can be defined as the process of storing the state of an object to a storage medium. During this process, the public and private fields of the object and the name of the class, including the assembly containing the class, are converted to a stream of bytes, which is then written to a data stream. When the object is subsequently deserialized, an exact clone of the original object is created.

When implementing a serialization mechanism in an object-oriented environment, you have to make a number of tradeoffs between ease of use and flexibility. The process can be automated to a large extent, provided you are given sufficient control over the process. For example, situations may arise where simple binary serialization is not sufficient, or there might be a specific reason to decide which fields in a class need to be serialized. The following sections examine the robust serialization mechanism provided with the .NET Framework and highlight a number of important features that allow you to customize the process to meet your needs.

In This Section
Serialization Concepts
Discusses two scenarios where serialization is useful: when persisting data to storage and when passing objects across application domains.

Basic Serialization
Describes how to use the binary and SOAP formatters to serialize objects.

Selective Serialization
Describes how to prevent some members of a class from being serialized.

Custom Serialization
Describes how to customize serialization for a class by using the ISerializable interface.

Steps in the Serialization Process
Describes the course of action serialization takes when the Serialize method is called on a formatter.

Version Tolerant Serialization
Explains how to create serializable types that can be modified over time without causing applications to throw exceptions.

Serialization Guidelines
Provides some general guidelines for deciding when to serialize an object.

Reference
System.Runtime.Serialization
Contains classes that can be used for serializing and deserializing objects.

Related Sections
XML and SOAP Serialization
Describes the XML serialization mechanism that is included with the common language runtime.

Security and Serialization
Describes the secure coding guidelines to



The easiest way to make a class serializable is to mark it with the Serializable attribute as follows.

C#  Copy Code
[Serializable]
public class MyObject {
  public int n1 = 0;
  public int n2 = 0;
  public String str = null;
}
 

The code example below shows how an instance of this class can be serialized to a file.

C#  Copy Code
MyObject obj = new MyObject();
obj.n1 = 1;
obj.n2 = 24;
obj.str = "Some String";
IFormatter formatter = new BinaryFormatter();
Stream stream = new FileStream("MyFile.bin", FileMode.Create, FileAccess.Write, FileShare.None);
formatter.Serialize(stream, obj);
stream.Close();
 

This example uses a binary formatter to do the serialization. All you need to do is create an instance of the stream and the formatter you intend to use, and then call the Serialize method on the formatter. The stream and the object to serialize are provided as parameters to this call. Although it is not explicitly demonstrated in this example, all member variables of a class will be serialized—even variables marked as private. In this aspect, binary serialization differs from the XMLSerializer Class, which only serializes public fields. For information on excluding member variables from binary serialization, see Selective Serialization.

Restoring the object back to its former state is just as easy. First, create a stream for reading and a formatter, and then instruct the formatter to deserialize the object. The code example below shows how this is done.

C#  Copy Code
IFormatter formatter = new BinaryFormatter();
Stream stream = new FileStream("MyFile.bin", FileMode.Open, FileAccess.Read, FileShare.Read);
MyObject obj = (MyObject) formatter.Deserialize(stream);
stream.Close();

// Here's the proof.
Console.WriteLine("n1: {0}", obj.n1);
Console.WriteLine("n2: {0}", obj.n2);
Console.WriteLine("str: {0}", obj.str);
 

The BinaryFormatter used above is very efficient and produces a compact byte stream. All objects serialized with this formatter can also be deserialized with it, which makes it an ideal tool for serializing objects that will be deserialized on the .NET Framework. It is important to note that constructors are not called when an object is deserialized. This constraint is placed on deserialization for performance reasons. However, this violates some of the usual contracts the runtime makes with the object writer, and developers should ensure that they understand the ramifications when marking an object as serializable.

If portability is a requirement, use the SoapFormatter instead. Simply replace the BinaryFormatter in the code above with SoapFormatter, and call Serialize and Deserialize as before. This formatter produces the following output for the example used above.

  Copy Code
<SOAP-ENV:Envelope
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xmlns:xsd="http://www.w3.org/2001/XMLSchema"
  xmlns:SOAP- ENC="http://schemas.xmlsoap.org/soap/encoding/"
  xmlns:SOAP- ENV="http://schemas.xmlsoap.org/soap/envelope/"
  SOAP-ENV:encodingStyle=
  "http://schemas.microsoft.com/soap/encoding/clr/1.0"
  "http://schemas.xmlsoap.org/soap/encoding/"
  xmlns:a1="http://schemas.microsoft.com/clr/assem/ToFile">

  <SOAP-ENV:Body>
    <a1:MyObject id="ref-1">
      <n1>1</n1>
      <n2>24</n2>
      <str id="ref-3">Some String</str>
    </a1:MyObject>
  </SOAP-ENV:Body>
</SOAP-ENV:Envelope>
 

It is important to note that the Serializable attribute cannot be inherited. If you derive a new class from MyObject, the new class must be marked with the attribute as well, or it cannot be serialized. For example, when you attempt to serialize an instance of the class below, you will get a SerializationException informing you that the MyStuff type is not marked as serializable.

C#  Copy Code
public class MyStuff : MyObject
{
  public int n3;
}
 

Using the Serializable attribute is convenient, but it has limitations as demonstrated above. Refer to the Serialization Guidelines for information about when you should mark a class for serialization; serialization cannot be added to a class after it has been compiled.

See Also
0
 
LVL 35

Accepted Solution

by:
mrichmon earned 250 total points
ID: 17073769
There are a few ways, the most straightforward being manually.

Doing so manually you can use a SQL statement or a stored procedure.

Here is .NET 1.1 code since you did not say which version.

SqlConnection objConn = new SqlConnection(ConfigurationSettings.AppSettings["yourconnection"]);
string strSQL = "INSERT INTO yourtable (field1, field2, etc) VALUES (@Field1, @Field2, etc)";
SqlCommand objCmd = new SqlCommand(strSQL, objConn);

objCmd.Parameters.Add(new SqlParameter("@Field1", SqlDbType.Int)).Value = classobject.var1;
objCmd.Parameters.Add(new SqlParameter("@Field2", SqlDbType.VarChar)).Value = classobject.var2;
etc.

objConn.Open();
objCmd.ExecuteNonQuery();
objConn.Close();
0
 
LVL 18

Assisted Solution

by:Ravi Singh
Ravi Singh earned 250 total points
ID: 17073832
Hi... I recommend you read around the subject of C# and databases, heres some sample code you can play with... if you had a class Person defined as:

      public class Person
      {
            public string name;
            public int age;

            public Person(string name, int age)
            {
                  this.name = name;
                  this.age = age;
            }
      }

and you had an MS Access DB with a table named "People" and two attributes "Name" and "Age", then you could define a method such as the following (note you'll have to change the connectionString variable to point to a database on your file system):

public void InsertPersonInDb(Person person)
{
      const string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp\PeopleDB.mdb;User Id=admin;Password=;";

      try
      {
            OleDbConnection conn = new OleDbConnection(connectionString);

            OleDbCommand cmdInsert = new OleDbCommand();
            cmdInsert.CommandText = "INSERT INTO People (Name, Age) VALUES (@Name, @Age)";
            cmdInsert.CommandType = CommandType.Text;
                  
            cmdInsert.Parameters.Add("@Name", person.name);
            cmdInsert.Parameters.Add("@Age", person.age);

            cmdInsert.Connection = conn;

            conn.Open();
            cmdInsert.ExecuteNonQuery();
            conn.Close();
      }
      catch (Exception ex)
      {
            //...Handle exception
      }
}

Finally, you can use the above method like:

//Objects of type Person
Person p1 = new Person("John", 25);
Person p2 = new Person("Harry", 30);

//Store in array
Person[] people = {p1, p2};

//Database insertion
foreach (Person p in people)
{
      InsertPersonInDb(p);
}
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Bit flags and bit flag manipulation is perhaps one of the most underrated strategies in programming, likely because most programmers developing in high-level languages rely too much on the high-level features, and forget about the low-level ones. Th…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

743 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now