CODEWITHSUNDEEP
c#sqldatabaseado.net
Eliazar
Eliazar

Reputation: 91

SQL Query in C# application

I need to create the following query

CREATE TABLE [dbo].[soft] (
[img_pk] [int] IDENTITY (1, 1) NOT NULL ,
[name] [varchar] (50) NULL ,
[img_data] [image] NULL ,
[img_contenttype] [varchar] (50) NULL, 
[file_data] [image] NULL ,
[file_contenttype] [varchar] (50) NULL 
) 

ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[soft] WITH NOCHECK ADD 
CONSTRAINT [PK_image] PRIMARY KEY NONCLUSTERED 
(
[img_pk]
) ON [PRIMARY]
GO

No problem with the first part, it's solved like 

string query1 = "CREATE TABLE soft" +
   "(pk int IDENTITY (1, 1) NOT NULL," +
  "name VARCHAR(50) NULL, img_data IMAGE NULL, img_contenttype VARCHAR(50) NULL, file_data IMAGE NULL, file_contenttype VARCHAR(50) NULL)"

But no ideas how to work with the next part. Can anyone help me?

Upvotes: 1

Views: 546

Answers (3)

Mitch Wheat
Mitch Wheat

Reputation: 300489

You can combine the table and primary key definitions:

CREATE TABLE [dbo].[soft] 
( 
   [img_pk] [int] IDENTITY (1, 1) NOT NULL 
       CONSTRAINT PK_Image PRIMARY KEY CLUSTERED, 
   [name] [varchar] (50) NULL , 
   [img_data] [image] NULL , 
   [img_contenttype] [varchar] (50) NULL,  
   [file_data] [image] NULL , 
   [file_contenttype] [varchar] (50) NULL  
)  
GO

You can dispense with the extra 'ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] ' if that's your default FileGroup, which it is in many (most?) cases

Also, I have created the PK as Clustered, which is more usual for an auto-inc column.

Also:

ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

Upvotes: 4

this-Me
this-Me

Reputation: 2145

Any problems you face when you use single line queries ??

string query = "ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO ALTER TABLE [dbo].[soft] WITH NOCHECK ADD CONSTRAINT [PK_image] PRIMARY KEY NONCLUSTERED ( [img_pk] ) ON [PRIMARY] GO";

Upvotes: 0

Rup
Rup

Reputation: 34408

You mean just load the query into a string? You can use the @"" multi-line string syntax:

string query2 = @"
ALTER TABLE [dbo].[soft] WITH NOCHECK ADD 
CONSTRAINT [PK_image] PRIMARY KEY NONCLUSTERED 
(
[img_pk]
) ON [PRIMARY]
";

(The 'ON PRIMARY .. GO' is part of the first query, by the way.)

To execute this you'd use something like

var command2 = dbConnection.CreateCommand();
command2.CommandText = query2;
command2.ExecuteNonQuery();

as usual.

Upvotes: 0

Related Questions