SQL
There are 9 entries for the tag
SQL
So I worked out how to, in a TSQL batch script, identity 1:? and M:N relationships.
If any SQL gurus out there can help streamline it or identify problems with my logic I would appreciate it.
DDL for test database
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
PRINT N'Creating [dbo].[DataTypes]'
GO
CREATE TABLE [dbo].[DataTypes]
(
[imageField] [image] NULL,
[binaryField] [binary] (50) NULL,
[varbinaryField] [varbinary] (50) NULL,
[timestampField] [timestamp] NULL,
[textField] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_DataTypes_textField] DEFAULT ('a'),
[ntextField] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_DataTypes_ntextField] DEFAULT ('a'),
[charField] [char] (10)...
I found this one somewhere on SqlTeam.com forums…
DECLARE @sep char(1), @s nvarchar(1024)
SET @sep = ','
SET @s = 'a,b,c';
WITH Pieces(pn, start, stop) AS (
SELECT 1, 1, CHARINDEX(@sep, @s)
UNION ALL
SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
FROM Pieces
WHERE stop > 0
)
SELECT pn,
SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 1024 END) AS s
FROM Pieces
Technorati tags: SQL
Recently I posted a question on stackoverflow asking how, in tsql, to differentiate a 1:? from an intermediate 1:n relationship.
Well, while all the inane bullshit polls and trivial questions were getting immediate response and comments and ups, a real technical question garnered no response. hmmmm...
Anyway, as usual, I had to answer my own question.
The solution was as I always thought it was but apparently I had been going about implementing it exactly the wrong way.
Keep in mind that this is in the context of generating source code for DTO objects.
Excluding edge cases like multiple 'inheritance' and abnormal schemas, the characteristics...
You can find the code @ DeadSimpleDTO.codeplex.com
New in 2.01
INotifyPropertyChanged option
One-to-many and many-to-one relationships optionally rendered
Examples:
Default options: basic property bag dto
/*
DeadSimpleDTO
default options
basic property bag dto
*/
using System;
using Northwind;
namespace Northwind
{
public partial class Orders
{
#region Properties
private Int32 _orderID;
public virtual Int32 OrderID
{
get { return _orderID; }
...
this_string IS all Over_the_MAP = ThisStringIsAllOverTheMap
/*
Pascal cases and concatenates a string. Underscores and spaces are
treated as boundaries and are removed after casing.
If a string is all upper or lower it will be proper cased.
Otherwise just capitalize first character.
*/
DECLARE @current_object_name NVARCHAR(128),
@generated_class_name NVARCHAR(128)
SET @current_object_name = 'this_string IS all Over_the_MAP'
-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
CREATE TABLE #fn_pascal_case_parts ([name] NVARCHAR(256))
DECLARE @fn_pascal_case_input NVARCHAR(256),
@fn_pascal_case_output NVARCHAR(256),
@fn_pascal_case_part NVARCHAR(128),
@fn_pascal_case_sql VARCHAR(MAX)
-- --> PascalCase Function - set input
SET @fn_pascal_case_input = @current_object_name
-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
BEGIN --> PascalCase Function common code
-- copy/paste this block as-is and just set/get input/output
-- if modifcations are made - other instances of this code block should be replaced.
-- is ugly but the closest...
PK Columns for [dbo].[Order Details] are "OrderID", "ProductID"
DECLARE @current_object_schema NVARCHAR(128),
@current_object_name NVARCHAR(128),
@generated_key_column_argument NVARCHAR(MAX)
USE Northwind
SET @current_object_schema = 'dbo'
SET @current_object_name = 'Order Details'
-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
DECLARE @fn_keylist_ordinal INT,
@fn_keylist_key_name NVARCHAR(128),
@fn_keylist_next_ordinal INT,
@fn_keylist_input_schema NVARCHAR(128),
@fn_keylist_input_name NVARCHAR(128),
@fn_keylist_output NVARCHAR(MAX),
@fn_keylist_pk_name NVARCHAR(128),
@fn_keylist_pk_schema NVARCHAR(128)
-- --> Function KeyList- set input
SET @fn_keylist_input_schema = @current_object_schema
SET @fn_keylist_input_name = @current_object_name
-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
BEGIN --> Function KeyList common code
SET @fn_keylist_output = ''
SET @fn_keylist_ordinal =0
SET @fn_keylist_next_ordinal =0
SELECT @fn_keylist_pk_schema = CONSTRAINT_SCHEMA,
@fn_keylist_pk_name = CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE ( TABLE_SCHEMA = @fn_keylist_input_schema )
AND ( TABLE_NAME = @fn_keylist_input_name )
AND ( CONSTRAINT_TYPE = 'Primary Key' )
IF ISNULL(@fn_keylist_pk_name, '') != ''
BEGIN
-- build an argument for the TableAttribute
SET @fn_keylist_output = ''
SELECT TOP 1
@fn_keylist_ordinal = [ORDINAL_POSITION]
FROM ...
UPDATE: After using this code a bit more it has become apparent that the Sql2008 types are all string convertable and that Byte[] is a better choice for a generic reader implementation.
You may also handle single field UDT by getting the name of the system_type_id when is_user_defined = 1 in sys.columns.
date NULL = Nullable<DateTime>
DECLARE @current_column_type VARCHAR(30),
@current_column_nullable BIT,
@generated_property_type NVARCHAR(128)
SET @current_column_type = 'date'
SET @current_column_nullable = 1
-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
DECLARE @fn_ctype_input_type NVARCHAR(128),
@fn_ctype_input_nullable BIT,
@fn_ctype_output_type NVARCHAR(128)
-- --> ConvertType Function - set input
SET @fn_ctype_input_type = @current_column_type
SET @fn_ctype_input_nullable = @current_column_nullable
-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
BEGIN
SET @fn_ctype_output_type = CASE @fn_ctype_input_type
...
I need to know if a string was all upper or all lower and was stumped for a case sensitive string comparison. Ultimately came up with this.
Better ideas???
-- case sensitive - not equal
IF (SELECT 'AbcdEfg' COLLATE Latin1_General_CS_AS) = 'abcdefg'
PRINT 'equal'
ELSE
PRINT 'not equal'
-- case insensitive - e
IF (SELECT 'AbcdEfg' COLLATE Latin1_General_CI_AS) = 'abcdefg'
PRINT 'equal'
ELSE
PRINT 'not equal'
Technorati tags: SQL
I am working on a spike that requires fetching metadata views from Sql Server's sys and INFORMATION_SCHEMA schemas. Initially, coding a few DTO by hand was no problem but as new views are required it obviously became tedious and typo prone.
I really did not want to break out ANY ORM, I just needed to read the data and a simple reflective filler would do just fine. In any case, I found myself in a query window writing a SQL based DTO generator. It ultimately ended up including a lot of clever functionality and I will present it here but first lets start with...