I'm assigned to migrate a database to a mid-class ERP. The new system uses composite primary keys here and there, and from a pragmatic point of view, why?

Compared to autogenerated IDs, I can only see negative aspects;

• Foreign keys becomes blurry
• Harder migration or db-redesigns
• Inflexible as business change. (My car has no reg.plate..)
• Same integrity better achieved with constraints.

It's falling back to the design concept of candiate keys, which I neither see the point of.

Is it a habit/artifact from the floppy-days (minimizing space/indexes), or am I missing something?

//edit// Just found good SO-post: Composite primary keys versus unique object ID field //

Personally I prefer the use of surrogate keys. However, in joining tables that consist only of the ids from two other tables (to create a many-to-many relationships) composite keys are the way to go and thus taking them out would make things more difficult.

There is a school of thought that surrogate keys are always bad and that if you don't have uniqueness to record through the use of natural keys you have a bad design. I strongly disagree with this (if you aren't storing SSN or some other unique value I defy you to come up with a natural key for a person table for instance.) But many people feel that it is necessary for proper normalization.

Sometimes having a composite key reduces the need to join to another table. Sometimes it doesn't. So there are times when a composite key can boost performance as well as times when it can harm performance. If the key is relatively stable, you may be fine with faster performance on select queries. However, if it is something that is subject to change like a company name, you could be in a world of hurt when company A changes it's name and you have to update a million associated records.

There is no one size fits all in database design. There are time when composite keys are helpful and times when they are horrible. There are times when surrogate keys are helpful and times when they are not.

What the difference between

INSERT INTO table VALUES (values)

and

INSERT OVER table VALUES (values)

?

People at Microsoft say:

Thanks for reporting this issue. We keep recognizing the OVER keyword along with the INTO keyword (with the same meaning) in INSERT statements to provide backward compatibility with the previous versions of SQL Server. This should not present any problem for application development.

Eugene Zabokritski, SQL Engine

Hi Everybody,

As i couldnt get any satisfying answer to my Question it seems we have to write our own program for that, we are in the design phase and we are thinking which format shall we use to backup the data.

The program will be written in Delphi.

Needed is Exporting/Importing data between Oracle/Informix/Msserver, very important here is the Performance issue, as this program will run on a 1-2 GB Databases. Beside the normal data there are Blobs in the Database which have to be backuped.

We thought of Xml-Data or comma-separated data as both are transparent (which is nice to have), but Blobs must be considered here. Paradox format is not optinal in this case.

Can anybody recommend some performant formats?

Any other Ideas to achieve the same Goal are welcome.

Thanx in Advance.

I use an excellent program called OmegaSync for my backups, but it will only handle Informix via ODBC and not directly. If you find you can use OmegaSync, you'll find its performance to be excellent, because it compares the databases first, and then syncs only the differences. You might want to use this idea if you decide to do the programming yourself if efficiency is your number one goal.

But programming database conversion is very complex as others answers to your question have said. So why not just develop the SQL you need, and do the conversion that way. For example see: Convert Informix Schema to Oracle Schema Or Any Other RDBMS For moving the data, check out sources like: Moving non-informaix data between computers and dbspaces

You can optimize the SQL to what I'm sure will be an adequate speed if you dump and load your data smartly.

I have to store a small number of database fields (~3) in encrypted form in database tables.

In general, is it better to encrypt/decrypt data within an application using .net cryptography or encrypt/decrypt data in a database using symmetric key encryption? What are the pros/cons of the two methods?

Here is my Environment:
Application - intranet web application
Development Platform - Visual studio 2010, ASP.Net, .Net Framework 3.5
Server Operating System - Windows Server 2008
Database - SQL Server 2008

This is a great question.

My take on it is to let the part responsible for storage of the data to also be responsible for any encryption of it. In this case, let SQL server manage the encryption.

The primary reason is that we rarely build stand alone applications anymore. It's more common to have multiple apps utilize the same database backend. Further, it's more common to replace the tech used to build an app than it is to replace the database engine underneath it.

If you do it in code, then every app that uses the data store would have to use it's own encryption/decryption library to get access to the data. If done in sql server, then the apps can be pretty ignorant of the process while you maintain the same level of protection.

Along with this, you should leverage the ability to encrypt connections to the SQL server, which would handle keeping the data encrypted while it's in motion between the web/app server and the database. Also you should have SSL enabled between the browser and your web site to ensure the data is never decrypted between the browser -> web server -> database server.

Hope that helps.

I need to run .Net code in Sql and I'm trying to decide between F# and C#. I'm doing more and more code in F# nowadays so if it's not too impractical, I'd like it to be F#.

Is it possible to coerce VS2010 to deploy my F# assemblies (and their references) to Sql Server, in the same nice way a C# project does?

Would you recommend/not recommend running F# in Sql? Why?

EDIT: I agree that the language is better, that is not the question. I was mainly wondering if anyone had experience with using F# in SqlClr and specifically if the tools can offer a simple workflow for development, i.e. Deploy in VS2010.

EDIT 2: I'm experimenting with this, and registering manually is downright painful. Besides CREATE ASSEMBLY you have to register each function, sp, aggregate etc. You also have to drop them in the right order first, if they exist, lest you get a DROP ASSEMBLY failed because 'Nibbler' is referenced by object 'Hello'.

I then had the idea to use a C# project as a front end and have this project reference an F# project, just to have all this deploying taken care of automatically. Turns out you can only reference other C#/VB Sql Clr projects, or assemblies that are already referenced in Sql. This could still simplify deployment though, as all creation/deletion of functions etc would be handled automatically. Then, for deploying from test to production, I would just generate scripts from all the stuff that is registered in my test environment.

PS. I also tried fiddling with the .fsproj file, diffing with the .csproj of a C# Clr project, to enable deployment to no avail.

If you have trouble with FSharp.Core and other references in the SQL CLR context, you could try the --standalone compiler flag to cause external references to be embedded in the assembly that you deploy to SQL Server.

Currently I'm working on a RFID project where each tag is attached to an object. An object could be a person, a computer, a pencil, a box or whatever it comes to the mind of my boss. And of course each object have different attributes.

So I'm trying to have a table tags where I can keep a register of each tag in the system (registration of the tag). And another tables where I can relate a tag with and object and describe some other attributes, this is what a have done. (No real schema just a simplified version)

Suddenly, I realize that this schema could have the same tag in severals tables. For example, the tag 123 could be in C and B at the same time. Which is impossible because each tag just could be attached to just a single object.

To put it simple I want that each tag could not appear more than once in the database.

My current approach

What I really want

Update: Yeah, the TagID is chosen by the end user. Moreover the TagID is given by a Tag Reader and the TagID is a 128-bit number.

New Update: The objects until now are:

-- Medicament(TagID, comercial_name, generic_name, amount, ...)

-- Machine(TagID, name, description, model, manufacturer, ...)

-- Patient(TagID, firstName, lastName, birthday, ...)

All the attributes (columns or whatever you name it) are very different.

Update after update

I'm working on a system, with RFID tags for a hospital. Each RFID tag is attached to an object in order keep watch them and unfortunately each object have a lot of different attributes.

An object could be a person, a machine or a medicine, or maybe a new object with other attributes.

So, I just want a flexible and cleaver schema. That allow me to introduce new object's types and also let me easily add new attributes to one object. Keeping in mind that this system could be very large.

Examples:

Tag(TagID)
Medicine(generic_name, comercial_name, expiration_date, dose, price, laboratory, ...)
Machine(model, name, description, price, buy_date, ...)
Patient(PatientID, first_name, last_name, birthday, ...)

We must relate just one tag for just one object.

Note: I don't really speak (or also write) really :P sorry for that. Not native speaker here.

You can enforce these rules using relational constraints. Check out the use of a persisted column to enforce the constraint Tag:{Pencil or Computer}. This model gives you great flexibility to model each child table (Person, Machine, Pencil, etc.) and at same time prevent any conflicts between tag. Also good that we dont have to resort to triggers or udfs via check constraints to enforce the relation. The relation is built into the model.

create table dbo.TagType (TagTypeID int primary key, TagTypeName varchar(10));
insert into dbo.TagType
    values(1, 'Computer'), (2, 'Pencil');

create table dbo.Tag
(   TagId       int primary key, 
    TagTypeId   int references TagType(TagTypeId), 
    TagName     varchar(10),
    TagDate     datetime,
    constraint UX_Tag unique (TagId, TagTypeId)
)
go
create table dbo.Computer 
(   TagId       int primary key, 
    TagTypeID   as 1 persisted,
    CPUType     varchar(25),
    CPUSpeed    varchar(25), 
    foreign key (TagId, TagTypeID) references Tag(TagId, TagTypeID)
)
go
create table dbo.Pencil 
(   TagId       int primary key, 
    TagTypeId   as 2 persisted,
    isSharp     bit,
    Color       varchar(25),
    foreign key (TagId, TagTypeID) references Tag(TagId, TagTypeId)
)
go



-----------------------------------------------------------
-- create a new tag of type Pencil:
-----------------------------------------------------------
insert into dbo.Tag(TagId, TagTypeId, TagName, TagDate)
    values(1, 2, 'Tag1', getdate());

insert into dbo.Pencil(TagId, isSharp, Color)
    values(1, 1, 'Yellow');

-----------------------------------------------------------
-- try to make it a Computer too (fails FK)
-----------------------------------------------------------
insert into dbo.Computer(TagId, CPUType, CPUSpeed)
    values(1, 'Intel', '2.66ghz')

Hello. I've got an application witch uses NHibernate as an ORM. I have one persistent class:

public class Match : IEntity
{
    public virtual int ID { get; set; }
    public virtual string Word { get; set; }
    public virtual int WordIntervalBeginning { get; set; }
    public virtual int WordIntervalEnding { get; set; }
}

and I have an SQL function on the server side:

CREATE FUNCTION ftMatchTest
( )
RETURNS TABLE 
AS
RETURN 
(
    SELECT mt1.*, mt2.*,
    CASE WHEN mt1.Word = mt2.Word THEN 1 ELSE 0 END AS sc
    FROM
        dbo.tMatchesTest mt1, dbo.tMatchesTest mt2
)

I want to be able to call this function and map the result from it into the following class

public class FResult
{
    public Match Match1 { get; set; }
    public Match Match2 { get; set; }
    public int sc { get; set; }
}

Is it possible to do it with NHibernate 3.0? Is it possible to do it with FluentNHibernate?
Thanks in advance!

UPDATED
I map Match class into tMatchesTest table.
Structure of tMatchesTest table is:

CREATE TABLE [dbo].[tMatchesTest](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Word] [varchar](50) NOT NULL,
    [WordIntervalBeginning] [int] NOT NULL,
    [WordIntervalEnding] [int] NOT NULL,
 CONSTRAINT [PK_tMatchesTest] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

UPDATED2
The solution I found on my own:
1. Create named query like this

<?xml version="1.0" encoding="utf-8" ?>

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" 
                   namespace=" ConsoleApplication8.Domain.Entities"
                   assembly="ConsoleApplication8">

  <resultset name="fresult-resset">
    <return alias="Match1" class="Match"/>
    <return alias="Match2" class="Match"/>
    <return-scalar column="sc" type="int"/>
  </resultset>


  <sql-query name="getfresult" resultset-ref="fresult-resset">
    SELECT {Match1.*}, {Match2.*},
    CASE WHEN Match1.Word = Match2.Word THEN 1 ELSE 0 END sc
    FROM dbo.tMatchesTest Match1, dbo.tMatchesTest Match2
  </sql-query>

</hibernate-mapping>

and execute the query like this:

Session.GetNamedQuery("getfresult")
                .SetResultTransformer(new AliasToBeanResultTransformer(typeof(FResult)))
                .List<FResult>();

This is the shortest and simples way I found so far to perform the task.

IResultTransformer is used to transform query results into a application-visible types.

Also, mapping the SQL function call as a named SQL query will give cleaner code.

var list = Session.GetNamedQuery("ftMatchTest")
    .SetResultTransformer(new AliasToFResultTransformer())
    .List<FResult>();

Since we have a multi-table result, AliasToBeanResultTransformer is not directly usable. Instead we will subclass it and convert the result to the desired type.

public class AliasToFResultTransformer : AliasToBeanResultTransformer
{
    public AliasToFResultTransformer() : base(typeof(FMatches)) {}

    object IResultTransformer.TransformTuple(object[] tuple, string[] aliases)
    {
        FMatches fm = base.TransformTuple( tuple, aliases ) as FMatches;

        return fm.ToFResult();
    }

    public class FMatches
    {
        public int sc { get; set; }
        public virtual int Mt1ID { get; set; }
        public virtual string Mt1Word { get; set; }
        public virtual int Mt1WordIntervalBeginning { get; set; }
        public virtual int Mt1WordIntervalEnding { get; set; }
        public virtual int Mt2ID { get; set; }
        public virtual string Mt2Word { get; set; }
        public virtual int Mt2WordIntervalBeginning { get; set; }
        public virtual int Mt2WordIntervalEnding { get; set; }

        public FResult ToFResult()
        {
            return new FResult {
                sc = this.sc,
                Match1 = new Match {
                    Id = this.Mt1Id,
                    Word = this.Mt1Word,
                    WordIntervalBeginning = this.Mt1WordIntervalBeginning,
                    WordIntervalEnding = this.Mt1WordIntervalEnding
                },
                Match2 = new Match {
                    Id = this.Mt2Id,
                    Word = this.Mt2Word,
                    WordIntervalBeginning = this.Mt2WordIntervalBeginning,
                    WordIntervalEnding = this.Mt2WordIntervalEnding
                }
            }
        }
    }
}

I got this hint from mssqlcity.com. However, I cannot understand its explanation.

Avoid creating a clustered index based on an incrementing key

For example, if a table has surrogate integer primary key declared as IDENTITY and the clustered index was created on this column, then every time data is inserted into this table, the rows will be added to the end of the table. When many rows will be added a "hot spot" can occur. A "hot spot" occurs when many queries try to read or write data in the same area at the same time. A "hot spot" results in I/O bottleneck. Note. By default, SQL Server creates clustered index for the primary key constraint. So, in this case, you should explicitly specify NONCLUSTERED keyword to indicate that a nonclustered index is created for the primary key constraint.

Before I read that, I thought if I pick a column that is random in nature, it's not correct because this will cause unnecessary page relocation when adding a new row. So, I think using a sorted column is preferrable.

After reading this hint, I think it's trying to say we don't really want to use a straightly sorted column to be our clustered index either because there is going to be an I/O bottleneck for those write-intensive application.

I don't really understand the cause of the I/O bottleneck that they are talking about. Are they saying too many operations sharing the same page is going to slow down the disk operations? How does this happen? Can somebody explain to me?

The Hot Spot they are referring to is not an issue in SQL Server 2005 and newer.

What USED to happen is that all your data was being written to the same area of the clustered index and the same sector(s) on the disk which caused a lot of dirty pages to be created at once (dirty pages being data pages that have been altered but not committed to disk), and when a flush or checkpoint ran this could cause issues.

Newer versions do not experience this behavior due to changes in the IO architecture (from what I understand).

Very simplified, I have two tables Source and Target.

declare @Source table (SourceID int identity(1,2), SourceName varchar(50))
declare @Target table (TargetID int identity(2,2), TargetName varchar(50))

insert into @Source values ('Row 1'), ('Row 2')

I would like to move all rows from @Source to @Target and know the TargetID for each SourceID because there are also the tables SourceChild and TargetChild that needs to be copied as well and I need to add the new TargetID into TargetChild.TargetID FK column.

There are a couple of solutions to this.

1. Use a while loop or cursors to insert one row (RBAR) to Target at a time and use scope_identity() to fill the FK of TargetChild.
2. Add a temp column to @Target and insert SourceID. You can then join that column to fetch the TargetID for the FK in TargetChild.
3. SET IDENTITY_INSERT OFF for @Target and handle assigning new values yourself. You get a range that you then use in TargetChild.TargetID.

I'm not all that fond of any of them. The one I used so far is cursors.

What I would really like to do is to use the output clause of the insert statement.

insert into @Target(TargetName)
output inserted.TargetID, S.SourceID
select SourceName
from @Source as S

But it is not possible

The multi-part identifier "S.SourceID" could not be bound.

But it is possible with a merge.

merge @Target as T
using @Source as S
on 0=1
when not matched then
  insert (TargetName) values (SourceName)
output inserted.TargetID, S.SourceID;

Result

TargetID    SourceID
----------- -----------
2           1
4           3

I want to know if you have used this? If you have any thoughts about the solution or see any problems with it? It works fine in simple scenarios but perhaps something ugly could happen when the query plan get really complicated due to a complicated source query. Worst scenario would be that the TargetID/SourceID pairs actually isn't a match.

MSDN has this to say about the from_table_name of the output clause.

Is a column prefix that specifies a table included in the FROM clause of a DELETE, UPDATE, or MERGE statement that is used to specify the rows to update or delete.

For some reason they don't say "rows to insert, update or delete" only "rows to update or delete".

Any thoughts are welcome and totally different solutions to the original problem is much appreciated.

In my opinion this is a great use of MERGE and output. I've used in several scenarios and haven't experienced any oddities to date. For example, here is test setup that clones a Folder and all Files (identity) within it into a newly created Folder (guid).

    declare @FolderIndex table (FolderId uniqueidentifier primary key, FolderName varchar(25));
    insert into @FolderIndex (FolderId, FolderName)
        values(newid(), 'OriginalFolder');

declare @FileIndex table (FileId int identity(1,1) primary key, FileName varchar(10));
    insert into @FileIndex (FileName)
        values('test.txt');

declare @FileFolder table (FolderId uniqueidentifier, FileId int, primary key(FolderId, FileId));
insert into @FileFolder (FolderId, FileId)
    select  FolderId, 
            FileId
    from    @FolderIndex
    cross
    join    @FileIndex;  -- just to illustrate

declare @sFolder table (FromFolderId uniqueidentifier, ToFolderId uniqueidentifier);
declare @sFile table (FromFileId int, ToFileId int);

-- copy Folder Structure
merge @FolderIndex fi
using   (   select  1 [Dummy],
                    FolderId, 
                    FolderName
            from    @FolderIndex [fi]
            where   FolderName = 'OriginalFolder'
        ) d on  d.Dummy = 0
when not matched 
then insert (FolderId, FolderName)
    values (newid(), 'copy_'+FolderName)
output  d.FolderId,
        inserted.FolderId
into    @sFolder (FromFolderId, toFolderId);

-- copy File structure
merge   @FileIndex fi
using   (   select  1 [Dummy],
                    fi.FileId, 
                    fi.[FileName]
            from    @FileIndex fi
            inner
            join    @FileFolder fm on
                    fi.FileId = fm.FileId
            inner
            join    @FolderIndex fo on
                    fm.FolderId = fo.FolderId
            where   fo.FolderName = 'OriginalFolder'
        ) d on d.Dummy = 0
when not matched 
then insert ([FileName])
    values ([FileName])
output  d.FileId,
        inserted.FileId
into    @sFile (FromFileId, toFileId);

-- link new files to Folders
insert into @FileFolder (FileId, FolderId)
    select  sfi.toFileId, sfo.toFolderId
    from    @FileFolder fm
    inner
    join    @sFile sfi on 
            fm.FileId = sfi.FromFileId
    inner
    join    @sFolder sfo on
            fm.FolderId = sfo.FromFolderId
-- return    
select  * 
from    @FileIndex fi 
join    @FileFolder ff on 
        fi.FileId = ff.FileId 
join    @FolderIndex fo on 
        ff.FolderId = fo.FolderId

When I create a temp table using a select into in SQL Server, is there a way to specify that a column should be nullable? I have a multi-step process where I'm making a temp table by selecting a lot of columns (which is why I'm not doing a create table #tmp (...)). After I make that temp table, I'm updating some columns and some of those updates might null out a field.

I know I could do an alter table alter column statement to achieve what I want, but I'm curious about whether there's a way to specify this in the select itself. I know you can inline cast your columns to get the desired datatype, but I can't see how you specify nullability.

Nullability is inherited from the source column.

You can lose or gain nullability with an expression:

Example (constant literals appear to be problematic - need a good NOOP function which can return NULL):

CREATE TABLE SO5465245_IN
    (
     a INT NOT NULL
    ,b INT NULL
    ) ;
GO

SELECT  COALESCE(a, NULL) AS a
       ,ISNULL(b, 0) AS b
       ,COALESCE(10, NULL) AS c1
       ,COALESCE(ABS(10), NULL) AS c2
       ,CASE WHEN COALESCE(10, NULL) IS NOT NULL THEN COALESCE(10, NULL) ELSE NULL END AS c3
INTO    SO5465245_OUT
FROM    SO5465245_IN ;
GO

SELECT  TABLE_NAME
       ,COLUMN_NAME
       ,IS_NULLABLE
FROM    INFORMATION_SCHEMA.COLUMNS
WHERE   TABLE_NAME LIKE 'SO5465245%'
ORDER BY TABLE_NAME
       ,ORDINAL_POSITION ;
GO

DROP TABLE SO5465245_IN ;
GO

DROP TABLE SO5465245_OUT ;
GO

Rather than poll against some tables, I'd like to signal a waiting c# app that there are new rows to be processed in a table, maybe via a trigger. Is there some way for the database to signal to a console app, or am I stuck polling the table looking for new rows?

Take a look at Query Notifications (SQL Server 2005+).

Microsoft SQL Server 2005 introduces query notifications, new functionality that allows an application to request a notification from SQL Server when the results of a query change. Query notifications allow programmers to design applications that query the database only when there is a change to information that the application has previously retrieved.

There is an example here of how to write a simple form app to register a query for notification: http://msdn.microsoft.com/en-us/library/a52dhwx7(VS.80).aspx.

This does require the Service Broker to be enabled on the database.

You should take a look at the notes in the Remarks section of the MSDN SqlDependency documentation to make sure it is the right choice for you scenario

Is there a way (similar to the below code) to delete all the rows in a specified table using c#?

SqlConnection con = new SqlConnection(conString);
con.Open();

string sql = @"DELETE*FROM compsTickers;";
SqlCommand cmd = new SqlCommand(sql, con);
cmd.ExecuteNonQuery();
con.Close();

Right now i'm getting an error: Incorrect syntax near '*'.

No need for the *. You delete rows, not columns, from a table, so you don't specify columns to delete.

DELETE FROM compsTickers

when getting return value from stored procedure, return value only first character,

Exec sp_Auto_Gen_TTBDBatNo 'TT', '' in sql server get whole string but in asp.net get first character

how to get the whole string value?

CREATE PROC sp_Auto_Gen_TTBDBatNo
      @Prefix nvarchar(2),
      @Result nvarchar(8) output
AS
BEGIN
    DECLARE @LastValue int


    -- CompanyCode = @CompanyCode AND BankCode = @BankCode AND AccountCode = @AccountCode

    SET NOCOUNT ON
    If @Prefix = 'BD' 
        SELECT @LastValue = MAX(RIGHT(RTRIM(ISNULL(BatchNo, '')),2)) from dbo.Cheque_IssueRecord_Secretary_Review_BD WHERE ISNUMERIC(RIGHT(RTRIM(BatchNo),2))= 1 AND LEN(RIGHT(RTRIM(BatchNo),2)) = 2
    ELSE
        SELECT @LastValue = MAX(RIGHT(RTRIM(ISNULL(BatchNo, '')),2)) from dbo.Cheque_IssueRecord_Secretary_Review_TT WHERE ISNUMERIC(RIGHT(RTRIM(BatchNo),2))= 1 AND LEN(RIGHT(RTRIM(BatchNo),2)) = 2

    SET NOCOUNT OFF
    set @Result = @Prefix + RIGHT(RTRIM(STR(year(getdate()))),2)+RIGHT('0'+LTRIM(RTRIM(STR(month(getdate())))),2) + RIGHT('0'+LTRIM(RTRIM(STR(ISNULL(@LastValue,0)+1))),2)

    print @Result

END

C# code:

string tAuto_Batch = "";

SqlTransaction trans = null;
using (SqlConnection connection = new SqlConnection(_connectionString))
{
    try
    {
        SqlCommand command = new SqlCommand("sp_Auto_Gen_TTBDBatNo", connection);
        command.CommandType = CommandType.StoredProcedure;

        command.Parameters.Add(new SqlParameter("@Prefix", "TT"));
        //command.Parameters.Add(new SqlParameter("@CompanyCode", cheque.Voucherbatchno));
        //command.Parameters.Add(new SqlParameter("@BankCode", cheque.Voucherno));
        //command.Parameters.Add(new SqlParameter("@AccountCode", cheque.Voucherno)); 

        SqlParameter ResultValue = new SqlParameter("@Result", tAuto_Batch);
        ResultValue.Direction = ParameterDirection.Output;

        command.Parameters.Add(ResultValue);

        connection.Open();
        trans = connection.BeginTransaction();
        command.Transaction = trans;
        command.Connection = connection;

        command.ExecuteNonQuery();
        trans.Commit();

        tAuto_Batch = command.Parameters["@Result"].Value.ToString();

        command.Dispose();
        trans.Dispose();
        connection.Close();

    }
    catch (Exception ex)
    {
        connection.Close();
        Error_Label.Text = Error_Label.Text + "sp_Auto_Gen_TTBDBatNo error " + ex.Message;
    }
}

Make sure you really use it like this:

@Result NVARCHAR(8) OUTPUT
SqlParameter resultValue = new SqlParameter("@Result", SqlDbType.NVarChar, 8);

The default length for (N)VARCHAR columns is 1.

I have this code:

string insertSql = 
    "INSERT INTO aspnet_GameProfiles(UserId,GameId) VALUES(@UserId, @GameId)";

using (SqlConnection myConnection = new SqlConnection(myConnectionString))
{
   myConnection.Open();

   SqlCommand myCommand = new SqlCommand(insertSql, myConnection);

   myCommand.Parameters.AddWithValue("@UserId", newUserId);
   myCommand.Parameters.AddWithValue("@GameId", newGameId);

   myCommand.ExecuteNonQuery();

   myConnection.Close();
}

When I insert into this table, I have an auto_increment int primary key column called GamesProfileId, how can i get the last inserted one after this so I can use that id to insert into another table?

Change the insert statement to this for SQL Server 2005+

INSERT INTO aspnet_GameProfiles(UserId,GameId) OUTPUT INSERTED.ID VALUES(@UserId, @GameId)

...or SQL Server 2000

INSERT INTO aspnet_GameProfiles(UserId,GameId) VALUES(@UserId, @GameId);SELECT SCOPE_IDENTITY()

And then

 Int32 newId = (Int32) myCommand.ExecuteScalar();

I'm a Java retread pretty new to C#. I'm hoping to stay out of trouble when I crank out a bunch of DML code in the next few weeks.

I'm used to the idea of using JDBC's abstract classes like Connection, Statement, and the like. C# offers similar abstract classes like DbConnection, DbCommand, and so forth, in the System.Data.Common namespace.

But, most of the examples I've seen -- both in MS documentation and other books -- use the concrete classes: SqlConnection, OracleCommand, etc. This kind of concreteness even shows up in the mySQL documentation.

What is the best practice in this area? Is there some strong reason to choose concrete table-server-specific rather than abstract classes for this purpose? (I'm aware of the hazards of downcasting abstract to concrete, of course).

The abstract classes were not part of the first versions of the framework, they were introduced in version 2.0. A lot of examples were written before that, or are based on examples that were written before that.

Using concrete or abstract classes is mostly a matter of taste. It's a nice idea to write code that could work with any database, but my experience is that you don't switch database systems very often, and if you do there are so many changes that you need to do that it doesn't matter much if you used abstract classes or not.