SQL Server Management Studio - Create queries, Export, Import

Question

Hello,
What is the best way to create a SQL query with the studio? Is there a wizard similar to Office Access?

I have a data model with primary key, foreign key. Can I have the complete database model displayed visually? If yes how?

I have to fill the database from C#. How do I get the primary key so that I can put it in the subtable as a foreign key?

How do I best add this under C#.
Do I read a text file for the INSERT and replace the variables or do I create the complete INSERT string under C#. Which variant is better, what do the experts recommend?

I add something and later notice that another function caused an error. Is there a rollback and how do I access it correctly?

Thanks for the answers and sample code.

---

How can I export a database of data? How can I import this with data to enable testing? How does it work under the studio?

Which menu items do I have to click?

Regards Markus

Answer 1

Attached is some test code that I had around and which demonstrates using output parameters. I have already pointed you to an article where I show how to use table-valued parameters.

I am very much deliberately given you incoherent pieces of examples. There are two reasons for this: 1) There is a limited amount to time I am willing to give free training to an individual. 2) I think you learn better by getting examples that you can explore on your own than if you are spoonfed code.

You will need to rename the attached file to have a .cs extension; the forum only accepts a limited set of file types.

160482-transactionscope.txt

Answer 2

I have to put the primary key of the master table in the detail table as a foreign key

This is best done in SSMS, create a Database Diagram then add tables followed by drag-n-drop columns from one table to the other to create a foreign key.

Answer 3

So one thing we don't know is whether you are working with a real business case, or you are just playing around trying to learn.

I and Karen may have pretended that it is the first, because this is a lot easier to help with. To wit, in this case there is - hopefully! - decently well-defined constraints in terms of business requirements. In the second case, it gets more difficult, because there are too many degrees of freedom. You make things up, and then you change them the next day.

Also, it's sort of difficult to help from a forum with a true beginner that asks wide open-ended questions. Forum questions work better for questions about concrete problems. But forums are not very good replacements for training classes.

I've mentioned table-valued parameters, and I do have an article on this topic on my web site: https://www.sommarskog.se/arrays-in-sql-2008.html. The examples I present are not really in match with what you are asking for, but you may still have use for them. A second article is this article by my MVP colleage Lenni Lobel: http://lennilobel.wordpress.com/2009/07/29/sql-server-2008-table-valued-parameters-and-c-custom-iterators-a-match-made-in-heaven/

As for the data model that Karen posted, this is basically the old demo database Northwind, which you can get here: https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/linq/downloading-sample-databases. (Karen has made a few alterations and additions to the original.)

Answer 4

I think your basic question is how to handle inserting data when there is a one to many relationship that uses identity columns as the primary key. The first step is inserting a record in the one side of the relationship and returning the primary key. Then use the primary key as a parameter to insert records on the many side of the relationship. That's pretty much it...

The actual logical design depends on the use case which in not clear from your posts. Are you loading data from a flat file? Is the data coming from user input?

Can you share the code you've tried?

Answer 5

With the Google search one is overloaded and I do not know at the end properly what is good. That's why I ask the experts here.

As you may have noticed, you get a bit of overload here too, not the least when you add several tags. Myself I come from the SQL Server side, while Joe and Karen are .NET experts.

Here is a stored procedure that you can work from:

CREATE TYPE dbo.ProductsEntry_type AS TABLE 
   (ProductID  int NOT NULL PRIMARY KEY,
    Quantity   int NOT NULL,
    Price      decimal(10,2) NOT NULL
)
go
CREATE PROCEDURE dbo.AddOrder @CustomerID  int,
                              @EmployeeID  int,
                              @Freight     decimal(10,2),
                              @Products    dbo.ProductsEntry_type READONLY,
                              @OrderID     int OUTPUT AS
   BEGIN TRANSACTION

   SELECT @OrderID = isnull(MAX(OrderID), 0) 
   FROM   dbo.Orders WITH (UPDLOCK)

   INSERT dbo.Orders (OrderID, OrderDate, CustomerID, 
                      EmployeeID, Freight)
      VALUES(@OrderID, convert(date, sysdatetime()), @CustomerID, 
             @EmployeeID, @Freight)

   INSERT dbo.OrderDetails(OrderID, ProductID, Quantity, Price)
      SELECT @OrderID, ProductID, Quantity, Price
      FROM   @Products
go

Very important: here I'm assuming that the Orders table does not have the IDENTITY property. IDENTITY is a highly abused feature. It has its uses in systems with high-concurrency inserts, and of course you can argue that a true order system qualifies. However, I want you to first learn the basics and to roll your own ID, which I'm doing here. IDENTITY has some problems that can be a nuisance when you run into them, so why use it when you don't need to?

Note here that the data I send into the SP does not include an order number. As I said previously, the order ID is typically created in the database when the order is created. But you can add your own attributes to the pattern above.

Can you show me the best way to do this in C#?

As I said, I have different expertise from Joe and Karen. I can write simple console-mode .NET programs, and I even have an article where I show how to use table-valued parameters in .NET, and I dropped the link to it above. But beyond direct data-access code I am inexperienced in .NET myself. Maybe Karen and Joe can fill in here. Then again, they prefer to do things their way. After all, they know SQL a lot better than I know .NET.