Warning: "continue" targeting switch is equivalent to "break". Did you mean to use "continue 2"? in H:\root\home\sklibraries-001\www\rajmhr\wp-content\plugins\wp-markdown\markdownify\markdownify.php on line 299
SQL Bulk Entry using Type – RajTech

SQL Bulk Entry using Type

Inserting data into database is a easy task. We can use ADO.NET, and framework like Entity Framework and different ORM to make it work. Most of the time when we have to generate and insert huge amount of data into the Database, the performance issue might come up. ORM and Frameworks are not so good enough to implement such issues. Although we can implement them.

Today, I want to show ADO.NET and SQL server joint effort to make this thing to work. I am going to explain step by step how we can achieve this.

Let’s say, we want to generate vouchers/tickets and save it into database. We might have to generate multiple times depending upon the requirements. The number of tickets or vouchers that we have to generate at a time is more than10,000. So, let’s implement this.

Step 1. Make a table that is used for Bulk Insert.

Let’s create a table, “Tickets” that has columns

(TicketId, TicketPrice, CampaignId, UserId,CreatedDate).

TicketId : is a primary Key which is unique.

TicketPrice is the price that system generates for different Tickets.

CampaignId for different Campaign.

UserId for who generated the Ticket.

CreatedDate: Ticket Generated Date.

Step: 2. Create “Type” in SQL server Relating to that table.

In the SQL server, under the “programmability”  you can see “Types”. Right click and create a type or you can simply write in new query window as:

CREATE TYPE TicketType AS TABLE
(
       TicketId int,
       TicketPrice money,
       CampaignId int,
       UserId int,
       CreatedDate dateTime

Step 3. Create a store procedure to insert data.

We will create store procedure to insert data into database.

CREATE PROC saveTicket (@ticketType TicketType READONLY)
AS
       BEGIN
             INSERT INTO Ticket
             (
             TicketId,
             TicketPrice,
             CampaignId,
             UserId,
             CreatedDate
             )
             SELECT * FROM @ticketType

Step 4. Implement the ADO.NET code to make it work.

Now, this is a final section,  here we write some ADO.Net Code to persist data into database. This is only a sample code that demonstrate the working of bulk insert.

public class SqlBulk
    {
        private readonly SqlConnection _con;
        private DataTable _dataTable;
        private readonly List<TicketModel> _models;
        private readonly Random _price;
        public SqlBulk()
        {
            _con = new SqlConnection("server=x-pc;database=Tickets;uid=sa;password=sa01");
            _models = new List<TicketModel>();
            _price = new Random(100000);
            GenerateTickets();
            SaveData();
        }

        private void GenerateTickets()
        {
            for (int i = 0; i < 10000; i++)
            {
                var model = new TicketModel()
                {
                    CampaignId = 101,
                    CreatedDate = DateTime.Now,
                    TicketPrice = GetRandomePrice(),
                    TicketId = 1001 + i,
                    UserId = 1
                };
                _models.Add(model);
            }
        }

        private long GetRandomePrice()
        {
            return _price.Next();
        }

        public void SaveData()
        {
            _dataTable = new DataTable();
            _dataTable.Columns.Add("TicketId", typeof(int));
            _dataTable.Columns.Add("TicketPrice", typeof(long));
            _dataTable.Columns.Add("CampaignId", typeof(int));
            _dataTable.Columns.Add("UserId", typeof(int));
            _dataTable.Columns.Add("CreatedDate", typeof(DateTime));

            foreach (var item in _models)
            {
                _dataTable.Rows.Add(item.TicketId, item.TicketPrice, item.CampaignId, item.UserId, item.CreatedDate);
            }
            ExecuteSql();
        }

        private void ExecuteSql()
        {
            try
            {
                if (_con.State == ConnectionState.Closed)
                    _con.Open();
                SqlCommand cmd = new SqlCommand("saveTicket", _con)
                {
                    CommandType = CommandType.StoredProcedure
                };
                cmd.Parameters.AddWithValue("@ticketType", _dataTable);
                cmd.ExecuteNonQuery();
                _con.Close();
            }
            catch (Exception)
            {
                throw new MyException("Cannot write data.");
            }
            finally
            {
                _con.Dispose();
            }
        }
    }

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *