Tuesday, 8 January 2013

create database using dataset in C#


Step 1)  Import XML file into DataSet

              ds.ReadXml("c:\data\books.xml")

Once you import books.xml into DataSet. It will create multiple tables based on xml data.
In our case, Book has many authors,  so 1-many relationship will be created between book(one)-authors(many).
 The above dataset has 3 tables.
               1.book table
               2.author table
               3.authors table(relationship between table 1 & 2).


Step 2)  Create Database using DataSet(i.e SqlServer Management Objects SMO)


Creating  Database using   SQLServer Management Objects(SMO).  
I am naming database as "BooksList", Creating 3 tables that exists in above dataset.

Using SQLServer Management Objects in C#/ASP.NET/WPF

Add following references located in C:\Program Files\Microsoft SQL Server\110\SDK\Assemblies

  •  Microsoft.SqlServer.ConnectionInfo.dll
  • Microsoft.SqlServer.Smo.dll
  • Microsoft.SqlServer.SqlEnum.dll
  • Microsoft.SqlServer.Management.Sdk.Sfc.dll

        void CreateDataBase()
        {
            if (ds.Tables.Count == 0) LoadXmlIntoDataSet();
            try
            {
            Microsoft.SqlServer.Management.Smo.Server server =
                new Microsoft.SqlServer.Management.Smo.Server(new ServerConnection()
                {
                ConnectAsUser = false,
                ServerInstance = @"machinename\sqlexpress2012",
                DatabaseName = "master",
                ConnectAsUserName = "sa",
                ConnectAsUserPassword = "password"

                });
            Database db = new Database(server, "master");
            db.Name = "BooksList";
            //db.Drop();

            AddDBTables(db);
            CreateDatabse_and_tables(db);
            CreateForeignKeysForTables(db);

            db.AutoClose = true;
            }
            catch (Exception ex)
            {
                 Response.Write(ex.Message);
          
            }
           
        }
        void CreateForeignKeysForTables(Database db)
        {
            ForeignKey fKeybook = new ForeignKey(db.Tables["authors"], "book_table_book_id");
            fKeybook.IsEnabled = true;
            ForeignKeyColumn fkbookidcol = new ForeignKeyColumn(fKeybook, "book_id", "book_id");
            fKeybook.Columns.Add(fkbookidcol);
            fKeybook.ReferencedTable = "book";

            fKeybook.Create();

        }
        void CreateDatabse_and_tables(Database db)
        {
        db.Create();
        foreach (Microsoft.SqlServer.Management.Smo.Table table in db.Tables)
        {
        table.Create();
        }

        }
        void AddDBTables(Database db)
        {
            foreach (DataTable tbl in ds.Tables)
            {
             Microsoft.SqlServer.Management.Smo.Table table =
                 new Microsoft.SqlServer.Management.Smo.Table(db, tbl.TableName);
//Adding DB columns to database table
//using DataSet table columns
             AddDBColumns(table, tbl);
             db.Tables.Add(table);
            }
         }
        void AddDBColumns(Microsoft.SqlServer.Management.Smo.Table table,
DataTable datatable)
        {
            foreach (DataColumn col in datatable.Columns)
            {
               
                Column tblcol =  new Column();
                tblcol.Name = col.ColumnName;
                //tblcol.Create();
                table.Columns.Add(tblcol);
                if(col.DataType.Name=="String"){
                    tblcol.DataType = DataType.NVarChar(col.MaxLength);
                   
                }
                if (col.DataType.Name == "Int32" || col.DataType.Name == "Int64")
                {
                    tblcol.DataType = DataType.Int;
                }
                tblcol.Default = col.DefaultValue.ToString();
               
                tblcol.Nullable = col.AllowDBNull;
                if (col.AutoIncrement)
                {
                    tblcol.Nullable = false;
                    Index idx = new Index(table, "pk_" + col.ColumnName);
                    idx.IndexedColumns.Add(new IndexedColumn(idx,tblcol.Name));
                    idx.IsClustered = true;
                    idx.IsUnique = true;
                    idx.IndexKeyType = IndexKeyType.DriPrimaryKey;
                    table.Indexes.Add(idx);

                    tblcol.Identity = col.AutoIncrement;
                    tblcol.IdentityIncrement = col.AutoIncrementStep;
                    tblcol.IdentitySeed = col.AutoIncrementSeed;
                }
              
            }

           
        }

//Once Database is Created with table objects.
//we can populate Database  tables (just now created step2) using ADO.NET Bulk Insert

Step 3) Bulk Insert Datatable into SqlServer

      
 void BulkInsertData()
        {
          SqlBulkCopy blkCopy = new SqlBulkCopy(@"server=(local)\SqlExpress2012;
uid=sa;pwd=password;database=BooksList",SqlBulkCopyOptions.KeepIdentity);
          foreach (DataTable table in ds.Tables)
          {
              blkCopy.DestinationTableName = table.TableName;
              blkCopy.WriteToServer(table);
          }
            blkCopy.Close();
        }
Bulk Insert into Sql Server using C# ADO.NET


















books.xml


<?xml version="1.0" encoding="utf-8" ?>

<bookslist>
  <book id="ISBN-220-244">
    <title>LINQ Unleased</title>
    <price>$40.99</price>
    <authors>
      <author>
        <firstname>Kimmel </firstname>
        <lastname>Paul</lastname>
      </author>
    </authors>
    <publisher>Sams</publisher>
  </book>
  <book id="ISBN-456-344">
    <title>C# Design Patterns</title>
    <price>$30.99</price>
    <authors>
      <author>
        <firstname>john</firstname>
        <lastname>peter</lastname>
      </author>
      <author>
        <firstname>mary</firstname>
        <lastname>kurl</lastname>
      </author>
    </authors>
    <publisher>Tata Mcgraw Hill</publisher>
  </book>
  <book id="ISBN-434-233">
    <title>Pro Entity Framework 4.0</title>
    <price>29.99</price>
    <authors>
      <author>
        <firstname>Scott</firstname>
        <lastname>Klein</lastname>
      </author>
    </authors>
    <publisher>APress</publisher>
  </book>
  <book id="ISBN-433-234">
    <title>Beginning PHP and MySQL E-Commerce</title>
    <price>$35.99</price>
    <authors>
      <author>
        <firstname>Cristian</firstname>
        <lastname>Darie</lastname>
      </author>
      <author>
        <firstname>Emilian</firstname>
        <lastname>Balanescu</lastname>
      </author>
    </authors>
    <publisher>APress</publisher>
  </book>
</bookslist>