how to insert update and delete records in tables

Question

I have a large windows form with 50 different fields (text boxes,combo boxes,listview,check box) i have 10 tables in sql server database each table have different column I want to insert my windows forms 50 fields in these 10 tables. Here is my code to insert record in a table

  Dim cmd As New SqlCommand
  cmd.Connection = conn
  cmd.CommandText = "Insert Into ChartOfAccount (MainCode,MainDescription,AccountCode,AccountDescription,OpeningBalance) values ('" & MainCode & "','" & MainDescription & "','" & AccountCode & "','" & AccountDescription & "','" & OpeningBalance & "')"
  cmd.ExecuteNonQuery()

So question is if I insert data into 10 different tables then I need to write 10 insert statments.

If I update or delete record I need to write update and delete command 10 tables seprately
this will take long time please guide me if any short method exists.

You can use stored procedure.In store procedure,you can pass parameter and can write more than one sql statement. — sangram parmar, Jul 06 '13 at 04:50
http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx and http://www.codeproject.com/Articles/38682/Overview-of-SQL-Server-Stored-Procedure — sangram parmar, Jul 06 '13 at 04:58

score 1 · Answer 1 · edited Jul 08 '13 at 21:58

1

You can create a stored procedure for that. In order to create a stored procedure, you can use the following SQL query:

Create procedure [dbo].[NewUser] 
@Yourparameter1 int , 
@Yourparamtere2
As
Insert into dbo.Users 
( 
 // Db columns 
 Column1 , 
 Column2 
) 
values 
( 
@Yourparameter1
@Yourparameter2
)

And in your C# code, create a SQLParameter collection and pass it to the procedure. It will work fine and smoothly, and this way your code will be simpler.

edited Jul 08 '13 at 21:58

Andriy M

76,112
17
94
154

answered Jul 06 '13 at 04:57

Kas

3,747
5
29
56

this store procedure insert record into users table when i insert record in a department table then i need to write another store procedure.and when i insert record in employee table then i need another store procudre and so on........ 10 times – zahid mahmood Jul 06 '13 at 05:01
Yes, but IF you use stored procedures that will simplicity your work, because you don't have to use sql querry in C# , instead you will just pass the name of the procedure and add parameters , – Kas Jul 06 '13 at 05:03
so dooby i need to write 10 store procedure and then pass every table parameters seprately – zahid mahmood Jul 06 '13 at 05:41
No , You can add to as many as tables from one single procedure. – Kas Jul 06 '13 at 07:14

Edper · Accepted Answer · 2013-07-06T05:05:20.637

1

Your query is susceptible with SQL injection so better to use parameters:

        Dim connStr as String = "connection string values here";
        using con as new SqlConnection(connStr)

              Dim commandText as String = 
                 @"Insert Into ChartOfAccount (MainCode
                 ,MainDescription,AccountCode
                 ,AccountDescription,OpeningBalance) 
               VALUES 
               (@MainCode, @MainDesc,@AccountCode
               ,@AccountDesc,@OpeningBalance)"
              Dim cmd as New SqlCommand(commandText,con)
                cmd.Parameters.AddWithValue("@MainCode",MainCode)
                cmd.Parameters.AddWithValue("@MainDesc",MainDescription)
                cmd.Parameters.AddWithValue("@AccountCode",AccountCode)
                cmd.Parameters.AddWithValue("@AccountDesc",AccountDescription)
                cmd.Parameters.AddWithValue("@OpeningBalance",OpeningBalance)

            Try
                con.Open()
                cmd.ExecuteNonQuery()
            Catch ex as Exception
                MessageBox.Show(ex.Message) 
            End Try
        End Using

Now, this is only for Inserting records example.

edited Jul 06 '13 at 05:05

answered Jul 06 '13 at 04:57

Edper

9,144
1
27
46

edper question is if insert record to another table i need to write whole code again – zahid mahmood Jul 06 '13 at 05:43
First this is a self-contained code so it has the connection, command etc. However, if you are using a Class to do this specially in Layered enviroment like having a Data-Tier to do this - you may code every table in that Class/Layer but you could re-use it without having to code again. Say, DataTier.Insert("TableName",classForTable). – Edper Jul 06 '13 at 05:52
See a simple example on the link that follows http://www.codeproject.com/Articles/36847/Three-Layer-Architecture-in-C-NET. – Edper Jul 06 '13 at 05:54
Here's more http://stackoverflow.com/questions/13786549/how-to-implement-3-tiers-architecture-in-c-sharp and this link also http://msdn.microsoft.com/en-us/library/bb332381.aspx – Edper Jul 06 '13 at 05:55

how to insert update and delete records in tables

2 Answers2