How to get total records in stored procedure and C# code?

Question

I am using a table valued parameter with a user defined table type here is my code below. I am trying to populate my stored procedure from my data table.

ALTER PROCEDURE [dbo].[TableName] @dt AS dbo.DataTableAsType READONLY

AS
BEGIN
INSERT INTO dbo.[DataTableAsType]
    ([Column names]) --There are 89 column names
SELECT
([ColumnNames])
FROM @dt
END

Second Stored procedure
@totalRecords int OUTPUT
INSERT INTO dbo.tablename1 FROM dbo.tablename2
SELECT @totalRecords = COUNT(*) FROM dbo.[tableName2]







public void InsertDataTableAF2CSV(string ext)
    {

        DataTable tvp = new DataTable();
        string[] arr = new string[89] {"names go here"};

//add 89 column names 1 by 1 tvp.Columns.Add(new DataColumn("column name", typeof(string)));

       //populate datarows I currently have over 1,000 rows. 
        DataRow row;
        for (int i = 0; i <= arr.Length; i++)
        {
            row = tvp.NewRow();
            row["Column name"] = i;

            //I add all 89 column names = i then I add rows.
            tvp.Rows.Add(row);
        }
        //read the file name I entered              
        tvp= ReadFile(filename, " ", null);

        //Passing a Table-Valued Parameter to a Stored Procedure
        using (SqlConnection con = new SqlConnection(connection name))
        {
            connection.Open();
            //Execute the cmd
            // Configure the command and parameter. 
            SqlCommand cmd = new SqlCommand("dbo.storedprocedure", connection);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandTimeout = 5000;
            ///SqlParameter tvparam = cmd.Parameters.AddWithValue("@dt", tvp);
            // Create a DataTable with the modified rows.  
            DataTable addedCategories = tvp.GetChanges(DataRowState.Added);
            // these next lines are important to map the C# DataTable object to the correct SQL User Defined Type
            SqlParameter parameter = new SqlParameter("@dt", SqlDbType.Structured)
            {
                //TypeName = "dbo.DataTableAsType",
                TypeName = "dbo.importDataTable",
                Value = tvp
            };                                   
            cmd.ExecuteNonQuery();
            con.Close();
        }
    }

Raphael M. · Answer 1 · 2018-05-04T14:26:51.957

Probably you are trying to add a foreign key before creating the database. Try to create them and after you can update them adding the foreign keys.

[Edit]

Here:

CREATE TABLE ACCOUNT
(ACCT_ID CHAR(10),
BRANCH_ID CHAR(20),
CUST_NUM CHAR(15),
ACCT_TYPE CHAR(20),
FOREIGN KEY (BRANCH_ID) REFERENCES BRANCH_1 (BRANCH_ID),
FOREIGN KEY (CUST_NUM) REFERENCES CUSTOMER_1 (CUST_NUM));

You are trying to reference CUSTOMER_1 (CUST_NUM) which does not exist yet.

[Edit 2]

Seems that you are missing some indexes. Try this:

Probably you are trying to add a foreign key before creating the database. Try to create them and after you can update them adding the foreign keys.

[Edit]

Here:

CREATE TABLE ACCOUNT
(ACCT_ID CHAR(10),
BRANCH_ID CHAR(20),
CUST_NUM CHAR(15),
ACCT_TYPE CHAR(20),
FOREIGN KEY (BRANCH_ID) REFERENCES BRANCH_1 (BRANCH_ID),
FOREIGN KEY (CUST_NUM) REFERENCES CUSTOMER_1 (CUST_NUM));

You are trying to reference CUSTOMER_1 (CUST_NUM) which does not exist yet.

[Edit 2]

Try this:

CREATE TABLE BRANCH_1
    (BRANCH_ID CHAR(20) PRIMARY KEY,
    BRANCH_NAME CHAR(20),
    BRANCH_ADDRESS CHAR(30));

CREATE TABLE EMPLOYEE
    (EMP_ID CHAR(4) PRIMARY KEY,
    EMP_FNAME CHAR(50),
    EMP_LNAME CHAR(50),
    BRANCH_ID CHAR(20));

    CREATE TABLE ACCOUNT
    (ACCT_ID CHAR(10),
    BRANCH_ID CHAR(20),
    CUST_NUM CHAR(15),
    ACCT_TYPE CHAR(20));

CREATE TABLE CUSTOMER_1
    (CUST_NUM CHAR(15),
    CUS_FNAME CHAR(50),
    CUS_LNAME CHAR(50),
    CUS_ADDRESS CHAR(15),
    BRANCH_ID CHAR(20),
    ACCT_ID CHAR(10),
    LOAN_ID CHAR(10));

CREATE TABLE LOAN
    (LOAN_ID CHAR(10),
    LOAN_NAME CHAR(50),
    LOAN_TYPE CHAR(20),
    CUST_NUM CHAR(15));


ALTER TABLE CUSTOMER_1 ADD INDEX(CUST_NUM);
ALTER TABLE ACCOUNT ADD INDEX(ACCT_ID);
ALTER TABLE EMPLOYEE ADD FOREIGN KEY (BRANCH_ID) REFERENCES BRANCH_1 (BRANCH_ID);
ALTER TABLE ACCOUNT ADD FOREIGN KEY (BRANCH_ID) REFERENCES BRANCH_1 (BRANCH_ID);
ALTER TABLE ACCOUNT ADD FOREIGN KEY (CUST_NUM) REFERENCES CUSTOMER_1 (CUST_NUM);
ALTER TABLE CUSTOMER_1 ADD FOREIGN KEY (BRANCH_ID) REFERENCES BRANCH_1 (BRANCH_ID);
ALTER TABLE CUSTOMER_1 ADD FOREIGN KEY (ACCT_ID) REFERENCES ACCOUNT (ACCT_ID);
ALTER TABLE LOAN ADD FOREIGN KEY (CUST_NUM) REFERENCES CUSTOMER_1 (CUST_NUM);

I created without the constraints then tried to go back and update with the foreign keys and get the same error — 212Coder, May 04 '18 at 13:57
ALTER TABLE Account ADD FOREIGN KEY (CUST_NUM) REFERENCES CUSTOMER_1(CUST_NUM); — 212Coder, May 04 '18 at 13:58
CREATE TABLE ACCOUNT (ACCT_ID CHAR(10), BRANCH_ID CHAR(20), CUST_NUM CHAR(15), ACCT_TYPE CHAR(20), FOREIGN KEY (BRANCH_ID) REFERENCES BRANCH_1 (BRANCH_ID)); — 212Coder, May 04 '18 at 14:04
THEN I add Customer_1 then I go back and try to alter the table. — 212Coder, May 04 '18 at 14:04

score 0 · Answer 2 · answered May 04 '18 at 14:04

0

Your ACCOUNT table and the CUSTOMER_1 has a circular foreign key reference which raised the problem. Similar problems are already described here and here.

I would like to suggest you to make a simplified database structure. As far as I could understand you do not need the CUST_NUM column in your ACCOUNT table which you are trying to use a a foreign key from CUSTOMER_1 table. Try to keep your database table structure as simple as you can as suggested in the second link that I have provided above.

answered May 04 '18 at 14:04

Reaz Murshed

23,691
13
78
98

Yea i was adding a customer num column so I can say its linked to an account. – 212Coder May 04 '18 at 14:09
I think having the `ACCT_ID` in the `CUSTOMER_1` table suffices your requirement. – Reaz Murshed May 04 '18 at 14:11
ALTER TABLE CUSTOMER_1 -> ADD FOREIGN KEY(ACCT_ID) REFERENCES ACCOUNT(ACCT_ID); ERROR 1215 (HY000): Cannot add foreign key constraint – 212Coder May 04 '18 at 15:03
Did you remove the foreign key from the account table? – Reaz Murshed May 04 '18 at 15:04
Yes it is removed – 212Coder May 04 '18 at 15:40
Ok. Did you drop your old account table and created a new one? – Reaz Murshed May 04 '18 at 15:41
Yes. I just started completely over. – 212Coder May 04 '18 at 16:59

How to get total records in stored procedure and C# code?

2 Answers2