Dynamic SQL Server database operations in VisualC #

Write database applications Program You can use SQL statements to dynamically create and modify databases and objects. This article describes how to use Visual C # To dynamically operate SQL Server databases, this includes how to create an SQL Server database and how to create database objects such as tables, stored procedures, views, and indexes. This article also describes how to dynamically modify the table mode, read data from tables, stored procedures, and views, and display the data in the database controls of the application.

We know that the most common SQL statements used in database operations are data operations such as select, insert, update, and delete, this article focuses on database and object operations. By mastering these basic operation methods, readers can develop database applications more flexibly.

Development Instance and procedure

1. Interface Design:

This article introduces an instance that uses Visual C # To dynamically operate databases, as shown in interface 1 of the instance.

Figure 1

We can see that our instance contains only 10 button controls and one DataGrid Control. There are three types of button controls:

The first type is the buttons for creating databases and database objects;

The second type is the buttons for viewing data in different database objects;

The third type is the button for modifying database objects. The DataGrid control is used to display data.

2. Add reference and data members:

After designing the interface, we can start programming.Code. First, add necessary references and some data members for our program. Because our program involves database access operations, it must be applied to the data provider object. Under the. NET Framework, we mainly use two types of data providers: SQL and oledb. The namespaces involved are system. Data. sqlclient and system. Data. oledb. Because our program accesses the SQL Server database, the data provider object we use should be of the SQL type. The reason is very simple. This type of object is specially designed for the SQL Server database, so the performance has been greatly optimized. Therefore, add the following statement at the beginning of our program:

Using system. Data. sqlclient;

Add to system. data. after referencing the sqlclient namespace, we will add some necessary data members to the program. These data members are private members of our class, through these operations, we can perform operations on the database and its objects.

Private string connectionstring = "Integrated Security = sspi; initial catalog =; Data Source = localhost;"; private sqlconnection conn = NULL; private sqlcommand cmd = NULL; private string SQL = NULL;

As for the meaning of each data member, I think you should understand it at a glance, so I will not talk about it here.

3. process database connection objects:

Connecting to the corresponding database is an essential step for database applications, and our programs are no exception. Therefore, create a database connection object in the constructor of our program as follows:

Public form1 () {// The required // initializecomponent () is supported by the Windows Forms designer; // create a connection conn = new sqlconnection (connectionstring );}

In this way, we can use this database to connect objects in future database operations.

At the same time, at the end of our program, it is necessary to close the database connection object. Otherwise, unexpected errors may occur. Therefore, you have to add the statement to close the database connection object to the overloaded dispose function of the program. net Framework has the garbage collection function, so we do not need to explicitly destroy this object as in C ++. The specific method is as follows:

Protected override void dispose (bool disposing) {// after the program runs successfully, make sure that the database connection is closed if (Conn. state = connectionstate. open) Conn. close (); If (disposing) {If (components! = NULL) {components. Dispose () ;}} base. Dispose (disposing );}

4. Create an SQL Server database:

After creating a database connection object, we can use it in a program. First, create an SQL Server database dynamically in the program. We create the database in the C: \ mysql directory. to practice this instance, you must create a folder named MySQL under C:; otherwise, an error will occur! The key to creating a database is the SQL object in the function. Through this object, we specify some basic attributes of the database file. Then, we create a new sqlcommand object, through which we actually complete the operations on the database. The function is implemented as follows:

Private void button#click (Object sender, system. eventargs e) {// open the database connection if (conn. State! = Connectionstate. open) Conn. open (); string SQL = "CREATE DATABASE mydb on primary" + "(name = test_data, filename = ''c: \ mysql \ mydb_data.mdf'', size = 3, "+" maxsize = 5, filegrowth = 10%) log on "+" (name = mydbb_log, filename = ''c: \ mysql \ mydb_log.ldf'', size = 3, "+" maxsize = 20, filegrowth = 1) "; cmd = new sqlcommand (SQL, Conn); try {cmd.exe cutenonquery ();} catch (sqlexception AE) {MessageBox. show (AE. message. tostring ());}}

5. Create a table and add records:

After creating a database, we need to create a table for it, which is the basic object in the database. The SQL statement CREATE TABLE is used to create a table. After the table is created, the schema is determined ). Then, four records are added to the table using the insert statement for future use. The function is implemented as follows:

Private void button2_click (Object sender, system. eventargs e) {// open the database connection if (Conn. state = connectionstate. open) Conn. close (); connectionstring = "Integrated Security = sspi;" + "Initial catalog = mydb;" + "Data Source = localhost;"; Conn. connectionstring = connectionstring; Conn. open (); SQL = "CREATE TABLE mytable" + "(myid integer constraint pkeymyid primary key," + "myname char (50), myaddress char (255), mybalance float) "; cmd = new sqlcommand (SQL, Conn); try {cmd.exe cutenonquery (); // Add a record to the table SQL =" insert into mytable (myid, myname, myaddress, mybalance) "+" values (1001, ''puneet nehra '', ''a 449 sect 19, delhi'', 23.98)"; cmd = new sqlcommand (SQL, Conn ); cmd.exe cutenonquery (); SQL = "insert into mytable (myid, myname, myaddress, mybalance)" + "values (1002, ''anoop Singh'', ''lodi Road, delhi '', 353.64)"; cmd = new sqlcommand (SQL, Conn); cmd.exe cutenonquery (); SQL = "insert into mytable (myid, myname, myaddress, mybalance) "+" values (1003, ''rakesh m'', ''nag Chowk, jabalpur M. p. '', 43.43)"; cmd = new sqlcommand (SQL, Conn); cmd.exe cutenonquery (); SQL = "insert into mytable (myid, myname, myaddress, mybalance) "+" values (1004, ''madan Kesh '', ''4th Street, Lane 3, delhi'', 23.00)"; cmd = new sqlcommand (SQL, Conn ); cmd.exe cutenonquery ();} catch (sqlexception AE) {MessageBox. show (AE. message. tostring ());}}

After this operation, you can open the mydb database in the SQL Server Enterprise Manager of the Local Machine and find the table. Figure 2 shows the design diagram of the table:

Figure 2

6. Create a universal SQL statement execution function:

Considering that the program uses a large number of sqlcommand objects to operate databases, we designed a general SQL statement execution function in the program. It creates a sqlcommand object based on the database connection object and SQL object, and then completes operations on the database. The function is implemented as follows:

Private void executesqlstmt (string SQL) {// open the database connection if (Conn. state = connectionstate. open) Conn. close (); connectionstring = "Integrated Security = sspi;" + "Initial catalog = mydb;" + "Data Source = localhost;"; Conn. connectionstring = connectionstring; Conn. open (); cmd = new sqlcommand (SQL, Conn); try {cmd.exe cutenonquery ();} catch (sqlexception AE) {MessageBox. show (AE. message. tostring ());}}

7. Create a stored procedure:

We use the create Procedure SQL statement to create a stored procedure, and the function also calls the preceding general SQL statement execution function. The function is implemented as follows:

Private void button3_click (Object sender, system. eventargs e) {SQL = "create procedure myproc as" + "select myname, myaddress from mytable go"; executesqlstmt (SQL );}

After the stored procedure is created, you can find its attributes in SQL Server Enterprise Manager, as shown in 3.

Figure 3

8. Create a view:

The process of creating a view is similar to that of creating a stored procedure. The SQL statement used is create view. The function is implemented as follows:

Private void button4_click (Object sender, system. eventargs e) {SQL = "create view myview as select myname from mytable"; executesqlstmt (SQL );}

Similarly, after creation, we can find its properties in Enterprise Manager, as shown in figure 4.