Database

<br />
SqlConnection connection =  new SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename="D:\Working Directory\Win Application\Nakoda\Development\Bin\Database\cERP.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True");<br />
<br />
 ServerConnection srvConn = new ServerConnection(connection);<br />
                // Create a new SQL Server object using the connection we created<br />
                srvSql = new Server(srvConn);<br />
<br />
                // If the user has chosen the file from which he wants the database to be restored<br />
                if (openBackupDialog.ShowDialog() == DialogResult.OK)<br />
                {<br />
                    //Declare a BackupDeviceItem by supplying the backup device file name in the constructor, and the type of device is a file.<br />
                    BackupDeviceItem bdi = default(BackupDeviceItem);<br />
                    bdi = new BackupDeviceItem(openBackupDialog.FileName, DeviceType.File);<br />
<br />
                    //Create another file device for the differential backup and add the Backup object.<br />
                    BackupDeviceItem bdid = default(BackupDeviceItem);<br />
                    bdid = new BackupDeviceItem(openBackupDialog.FileName, DeviceType.File);<br />
<br />
                    // Set the database that we want to perform the restore on<br />
                    string db = cmbDatabase.SelectedItem.ToString();<br />
                    //rstDatabase.Database = db;<br />
<br />
                    //Define a Restore object variable.<br />
                    Restore rs = default(Restore);<br />
                    rs = new Restore();<br />
                    //Set the NoRecovery property to true, so the transactions are not recovered.<br />
                    rs.NoRecovery = true;<br />
                    //Add the device that contains the full database backup to the Restore object.<br />
                    rs.Devices.Add(bdi);<br />
                    //Specify the database name.<br />
                    rs.Database = db;<br />
                    //Restore the full database backup with no recovery.<br />
                    rs.SqlRestore(srvSql);<br />
<br />
<br />

ALTER PROCEDURE [dbo].[get_next_id](@Term int,@ID INT OUTPUT) AS
SET NOCOUNT ON
DECLARE @Current int;
SELECT @Current=[ID] FROM [IdGenerator] where Term = @Term;
IF @Current<1000
BEGIN
	update IdGenerator SET @ID = ID, ID = ID + 1 where Term=@Term
END
ELSE
BEGIN
	update IdGenerator SET @ID =1,ID = 2 where Term=@Term 
END

OrderId
ProductCode
Quantity

1000, ProdA, 100
1000, ProdB, 200
1001, Prodc, 50
1002, ProdB, 200

OrderId, ProdA, ProdB, ProdC
----------------------------
1000,      100,   200,     0
1001,        0,     0,    50
1002,        0,   200,     0