Bulk Insert into SQL Server using C#

I was recently tasked with a project at my work to update SQL Server database with large amounts of data each hour. Every hour, I’ve got an text file contains more than 500,000 records and there were several that needed processing daily. 

 

DEFAULT Constraint (default values)

Sometimes you want to insert a specific value to this field when the user leaves this field without a value. In other words, the default value will be added to all new records, if no other value is specified.

You can see this by executing the following T-SQL command:-

CREATE TABLE [dbo].[MyTable](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NULL,
[PhoneNumber] [varchar](50) NULL,
[Address] [varchar](500) NULL,
[MobileNumber] [varchar](50) NULL,
[City] [varchar](50) NULL,
[Country] [varchar](50) NULL,
[Notes] [varchar](5000) NULL,
CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED
(
[ID] ASC
)
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [DF_MyTable_Name]  DEFAULT (‘Normal Customer‘) FOR [Name]
GO

ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [DF_MyTable_PhoneNumber]  DEFAULT (‘No Number‘) FOR [PhoneNumber]
GO

ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [DF_MyTable_MobileNumber]  DEFAULT (‘No Number‘) FOR [MobileNumber]
GO

ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [DF_MyTable_Notes]  DEFAULT (‘No notes available‘) FOR [Notes]
GO

In the previous T-SQL command, we create a table with some default values of some columns. To test and see the result of this code run the following code:-

INSERT dbo.MyTable DEFAULT VALUES

You will see the next result as a new row added by the previous command:-

 

Save vs. Retrieve images in SQL Server db using C# .NET.

In this post we will learn how can I save pictures and retrieve them from Microsoft SQL Server database using MS C# .NET.

Technically … saving and retrieving pictures depended on converting the real (binary) picture to bytes or array of bytes, then save these bytes in (image) data type field in SQL Server database.

Follow this code …:-

• Create your table at first withe this T-SQL script:

CREATE TABLE [dbo].[tblPhotos]
( [picID] [int] IDENTITY(1,1) NOT NULL,
[Photo] [image] NULL,
CONSTRAINT [PK_tblPhotos]
PRIMARY KEY CLUSTERED ( [picID] ASC ))

• You have to import or use this namespaces:

using System.Data.SqlClient;
using System.IO;