Try this:
1. Create a Type in SQL Server
as:
CREATE TYPE dbo.MyDataTable
AS TABLE
(
Category NVARCHAR(200)
);
GO
2. Create a Stored Proc(on SQL Server) consume the above TYPE created and insert into Categories(assuming your table name is "Categories"
CREATE PROCEDURE dbo.InsertCategories
@dt AS dbo.MyDataTable READONLY
AS
BEGIN
SET NOCOUNT ON;
INSERT dbo.Categories(Category)
SELECT Category
FROM @dt
WHERE Category NOT IN (SELECT Category FROM dbo.Categories);
END
Now the c# part:
string category = "Cat1, Cat2, Cat3, Cat4";
string[] categories = category.Split(',');
DataTable dt_Categories = new DataTable();
dt_Categories.Columns.Add("Category", typeof(String));
DataRow workRow;
foreach(string cat in categories)
{
workRow = dt_Categories.NewRow();
workRow["Category"] = cat.Trim();
dt_Categories.Rows.Add(workRow);
}
using (connectionObject)
{
SqlCommand cmd = new SqlCommand("dbo.InsertCategories", connectionObject);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter tvparam = cmd.Parameters.AddWithValue("@dt", dt_Categories);
tvparam.SqlDbType = SqlDbType.Structured;
cmd.ExecuteNonQuery();
}