Will the usage of stored procedures make my work more complex, or will it be too time or performance consuming to use?

I am currently in a project for college to write a program to store information about other companies they interact with (like contact information, delivery information etc.) and what is in their general stock at any point of time (Such as the Brand, Model, Amount of the item in stock, Manufacturer/Supplier information etc.) and customer information.

I am wondering if I must use a huge amount of stored procedures to take advantage of error handling and transactions using T-SQL, or would it be faster to just use the System.Data.SqlClient.SqlCommand Class to run plain queries? I have some sample code that I have worked out to store information on a customer in a database using T-SQL and C#, which wasn't a problem. Our task states that we will be judged by complexity and performance of our program.

This is the stored procedure I used:

CREATE PROCEDURE uspAddCustomers @Name varchar(30), @Surname varchar(30), @Addressline1 varchar(300),@Addressline2 varchar(300),@Telephone varchar(12),@CellPhone varchar(12),@Email Varchar(100)
AS
BEGIN TRY
    BEGIN TRANSACTION TrnAddCustomer;
        INSERT INTO [TCTdb].[dbo].[Customers]
                   ([Name]
                   ,[Surname]
                   ,[Addressline1]
                   ,[Addressline2]
                   ,[Telephone]
                   ,[Cellphone]
                   ,[Email])
             VALUES
                   (@Name
                   ,@Surname
                   ,@Addressline1
                   ,@Addressline2
                   ,@Telephone
                   ,@CellPhone
                   ,@Email)
    COMMIT TRANSACTION TrnAddCustomer;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION TrnAddCustomer;

    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT 
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

    RAISERROR (@ErrorMessage,
               @ErrorSeverity,
               @ErrorState
               );
END CATCH
GO

These are the Code Snippets I used to call the stored procedure:

private void btnAdd_Click(object sender, EventArgs e)
{
    try
    {
        SQLCommands comm = new SQLCommands(); //Custom Class containing method below.
        comm.AddCustomer(tbName.Text, tbSurname.Text, tbAddressline1.Text, tbAddressline2.Text, tbPhoneNumber.Text, tbCellphoneNumber.Text, tbEmail.Text);
        MessageBox.Show("Success! Note, if the data you inserted was too long, it may have been subjected to truncation to fit in the Database.", "Success!", MessageBoxButtons.OK, MessageBoxIcon.Information);
        tbName.Text = tbSurname.Text = tbPhoneNumber.Text = tbCellphoneNumber.Text = tbAddressline2.Text = tbAddressline1.Text = tbEmail.Text = string.Empty;
    }
    catch
    {
        MessageBox.Show("It appears something has gone wrong. Please contact the Supervisor if this problem keeps reoccurring", "Unknown Error", MessageBoxButtons.OK, MessageBoxIcon.Hand);
    }
}

AND

 public void AddCustomer(params string[] parame开发者_如何学Pythonters)
    {
        try
        {
            comm = new SqlCommand("UspAddCustomers", conn); // Stored Procedure
            comm.Parameters.AddWithValue("@Name", parameters[0]);
            comm.Parameters.AddWithValue("@Surname", parameters[1]);
            comm.Parameters.AddWithValue("@Addressline1", parameters[2]);
            comm.Parameters.AddWithValue("@Addressline2", parameters[3]);
            comm.Parameters.AddWithValue("@Telephone", parameters[4]);
            comm.Parameters.AddWithValue("@CellPhone", parameters[5]);
            comm.Parameters.AddWithValue("@Email", parameters[6]);
            comm.CommandType = CommandType.StoredProcedure;
            comm.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            System.Windows.Forms.MessageBox.Show(ex.Message, "Error", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
        }
    }

The Question: So to revise my question, I was about to use this type of code to handle all CRUD for my tables in my database. I am worried though that this may chew the performance of my program, because there is a lot of tables involved. Is it advisable to carry on like this, or should I consider using simpler methods for manipulating data in my database?

The reason why I thought of using this method was to enhance complexity

Since you are using C#, have you considered using LINQ-to-SQL? It's a pretty sophisticated framework built into .NET, but very easy to use and eliminates the need for a good deal of framework code in the lower levels of your code. It could save you a good deal of time, but maybe only if you have time to familiarize yourself with LINQ-to-SQL. I have used it in an environment where we don't use stored procedures (the queries and updates are effectively generated by LINQ-to-SQL based on LINQ queries and methods written in the code), but I believe you can also tell it to use stored procedures.

I'm not sure how they're scoring complexity. If it's based on how much code you write, then this might be a step in the wrong direction, since I think you would write much less code. But overall, I think more complexity/sophistication would be involved in a LINQ-to-SQL solution because it translates LINQ queries into SQL queries internally, and can be ported to other database platforms as a result.

whilst not really an answer to the question, one thing is for certain, your method:

 public void AddCustomer(params string[] parameters)

is a terrible idea. How will you know which parameter to pass where? you should declare this method with the details it needs to populate the table:

 public void AddCustomer(string fisrtName, string lastName, string addressLine1, string addressLine2, string telephone, string cellphone, string emailAddress)

or even better encapsulate the address in an Address class, the phone details in a PhoneDetails class and haver a Customer class which has an Address and a PhoneDetails and the first and last name and then pass this to your method.

Have you thought about using an object relational mapper such as NHibernate (it might get you extra brownie points)

I would always prefer plain queries over stored procs. Stored procs are hard to debug considering we dont see the actual query behind it from the IDE. Since you would want to finish it off quickly, to simply make debugging easier, you should be going for plain queries. If the application would be given maintenance and support later on in large scale, stored proc must be better. But in your case I feel standard application querying would be better.