【C#】 CRUD Records Using SqlCommand

C# - CRUD Records Using SqlCommand

In this post, we will explore how to perform CRUD (Create, Read, Update, Delete) operations on records in a SQL Server database using the SqlCommand class in C#. We will discuss the basic concepts, provide a detailed explanation of each operation, and provide coding examples to help you understand and implement the functionality in your own projects.

Table of - contents

No.	Title
1	Explanation
2	Coding Example CRUD
3	Conclusion

1 - Explanation.

The SqlCommand class in C# is a powerful tool for executing SQL statements and stored procedures against a SQL Server database. It allows you to perform various database operations such as creating new records, reading existing records, updating records, and deleting records.

a) Create Operation: To create a new record, you need to construct an INSERT statement and execute it using the SqlCommand class. The INSERT statement specifies the table name and column values for the new record. You can use parameterized queries to prevent SQL injection attacks and improve performance.

b) Read Operation: For reading records, you can use a SELECT statement. You can specify the columns you want to retrieve or use the ‘*’ wildcard to fetch all columns. The SqlCommand class allows you to execute the SELECT statement and retrieve the results using SqlDataReader. You can iterate over the result set to access each record’s data.

c) Update Operation: Updating records involves constructing an UPDATE statement with the desired column values and a condition to identify the record to update. Similar to the create operation, you can use parameterized queries to ensure the safety and efficiency of your code.

d) Delete Operation: To delete records, you need to construct a DELETE statement with a condition to identify the record(s) to remove. Executing this statement using the SqlCommand class will delete the specified records from the database.

2 - Coding Example (SQL Server)

a) Create Operation Example:

string ConnectionString = @"Server=192.168.1.1\InstanceName;Database=EPLive;Persist Security Info=True;User ID=DBUsername;Password=DBPassword;MultipleActiveResultSets=True;";
            using (SqlConnection openCon = new SqlConnection(ConnectionString))
            {
                string queryInsert = "INSERT into Customer (Name,DOB,Age) VALUES (@Name,@DOB,@Age)";
                using (SqlCommand cmdStr = new SqlCommand(queryInsert))
                {
                    cmdStr.Connection = openCon;
                    cmdStr.Parameters.Add("@Name", SqlDbType.VarChar, 50).Value = "Test User 01";
                    cmdStr.Parameters.Add("@DOB", SqlDbType.DateTime).Value = "01-01-1993";
                    cmdStr.Parameters.Add("@Age", SqlDbType.Int).Value = 30;

                    openCon.Open();
                    cmdStr.ExecuteNonQuery();
                    openCon.Close();
                }
            }

I ) string ConnectionString = @”Server=192.168.1.1\InstanceName;Database=EPLive;Persist Security Info=True;User ID=DBUsername;Password=DBPassword;MultipleActiveResultSets=True;” : This line declares and initializes a string variable named ConnectionString. It contains the connection string used to connect to a SQL Server database. The connection string specifies the server, database name, security information, and other parameters required to establish a connection.

II ) using (SqlConnection openCon = new SqlConnection(ConnectionString)) : This line establishes a connection to the SQL Server database using the SqlConnection class. The connection is created based on the provided connection string. The using statement ensures that the connection is properly disposed of after its use, even if an exception occurs.

III ) string queryInsert = “INSERT into Customer (Name,DOB,Age) VALUES (@Name,@DOB,@Age)”; : This line declares and initializes a string variable named queryInsert. It contains an SQL INSERT statement that will be executed against the database. The statement specifies the table (Customer) and the columns (Name, DOB, Age) to insert data into, as well as parameter placeholders (@Name, @DOB, @Age) for the actual values.

IV ) using (SqlCommand cmdStr = new SqlCommand(queryInsert)) : This line creates a new instance of the SqlCommand class named cmdStr, used to execute SQL commands against the database. The constructor of SqlCommand takes the SQL statement (queryInsert) as a parameter.

V ) cmdStr.Connection = openCon; : This line assigns the SqlConnection object (openCon) to the Connection property of the SqlCommand. It associates the command with the previously established database connection.

VI ) cmdStr.Parameters.Add(“@Name”, SqlDbType.VarChar, 50).Value = “Test User 01”; : This line adds a parameter to the command’s parameter collection. It specifies the parameter name (@Name), the data type (SqlDbType.VarChar), the length of the parameter (50), and sets its value to “Test User 01”. This parameter will be used to replace the @Name placeholder in the SQL statement.

VII ) cmdStr.Parameters.Add(“@DOB”, SqlDbType.DateTime).Value = “01-01-1993”; : This line adds another parameter to the command’s parameter collection. It specifies the parameter name (@DOB), the data type (SqlDbType.DateTime), and sets its value to “01-01-1993”. This parameter will be used to replace the @DOB placeholder in the SQL statement.

VIII ) cmdStr.Parameters.Add(“@Age”, SqlDbType.Int).Value = 30; : This line adds a third parameter to the command’s parameter collection. It specifies the parameter name (@Age), the data type (SqlDbType.Int), and sets its value to 30. This parameter will be used to replace the @Age placeholder in the SQL statement.

IX ) openCon.Open(); : This line opens the database connection using the Open method of the SqlConnection object. It establishes a connection to the database.

X ) cmdStr.ExecuteNonQuery(); : This line executes the SQL command represented by the SqlCommand object (cmdStr). In this case, it performs the INSERT operation specified in the SQL statement. It doesn’t return any results.

XI ) openCon.Close(); : This line closes the database connection using the Close method of the

b) Read Operation Example:

string ConnectionString = @"Server=192.168.1.1\InstanceName;Database=EPLive;Persist Security Info=True;User ID=DBUsername;Password=DBPassword;MultipleActiveResultSets=True;";
            DataTable dtCust = new DataTable();
            using (SqlConnection openCon = new SqlConnection(ConnectionString))
            {
                StringBuilder sqlStatement = new StringBuilder();
                sqlStatement.Append(" SELECT Name,DOB,Age ");
                sqlStatement.Append(" FROM EPLive.dbo.Customer ");
                sqlStatement.Append(" WHERE RunningNo = @RunningNo ");

                using (SqlCommand cmd = new SqlCommand(sqlStatement.ToString(), openCon))
                {
                    openCon.Open();
                    cmd.Parameters.Add("@RunningNo", SqlDbType.BigInt).Value = 10000;

                    cmd.ExecuteNonQuery();

                    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                    {
                        da.Fill(dtCust);
                    }
                    openCon.Close();
                }
            }

c) Update Operation Example:

string ConnectionString = @"Server=192.168.1.1\InstanceName;Database=EPLive;Persist Security Info=True;User ID=DBUsername;Password=DBPassword;MultipleActiveResultSets=True;";
            using (SqlConnection openCon = new SqlConnection(ConnectionString))
            {
                string queryUpdate = "UPDATE Customer SET Name = @Name WHERE RunningNo = @RunningNo ";
                using (SqlCommand cmdStr = new SqlCommand(queryUpdate))
                {
                    cmdStr.Connection = openCon;
                    cmdStr.Parameters.Add("@Name", SqlDbType.VarChar, 100).Value = "Test User 02";
                    cmdStr.Parameters.Add("@RunningNo", SqlDbType.BigInt).Value = 10000;

                    openCon.Open();
                    cmdStr.ExecuteNonQuery();
                    openCon.Close();
                }
            }

d) Delete Operation Example:

string ConnectionString = @"Server=192.168.1.1\InstanceName;Database=EPLive;Persist Security Info=True;User ID=DBUsername;Password=DBPassword;MultipleActiveResultSets=True;";
            using (SqlConnection openCon = new SqlConnection(ConnectionString))
            {
                string queryDelete = "DELETE FROM Customer WHERE RunningNo = @RunningNo ";
                using (SqlCommand cmdStr = new SqlCommand(queryDelete))
                {
                    cmdStr.Connection = openCon;
                    cmdStr.Parameters.Add("@RunningNo", SqlDbType.BigInt).Value = 10000;

                    openCon.Open();
                    cmdStr.ExecuteNonQuery();
                    openCon.Close();
                }
            }

3 - Conclusion.

Using the SqlCommand class in C#, you can easily perform CRUD operations on records in a SQL Server database. It provides a flexible and efficient way to interact with the database and manipulate data. Remember to use parameterized queries to ensure security and prevent SQL injection attacks. With the examples and explanations provided in this post, you should be able to implement CRUD functionality in your C# projects effectively.

Page Categories

Subscribe

0 Comments

Inline Feedbacks

View all comments

Search

.

Xiao. tian

.

Piano - Music

.

Recent - Post

【Others】 Top Audio Editing Software Picks for 2024

Elevate your audio game! Explore the finest...
【Others】 Top Video Editing Software Picks for 2024

Discover the crème de la crème of...
【Others】 SQL Server Remove Server Name

SQL Server Management Studio how to remove...
【XAMPP】 XAMPP WordPress Object Not Found

How to solve localhost XAMPP WordPress site...

.