Generate SQL statements in batches using regular expressions and C #

 

For example, if you have created a new table and want to insert data into it, the data volume is small. If you need 1000 data records, it is very difficult to use Ctrl + C and CTRL + v. everyone knows that programmers will be lazy and great. Haha, we can use a program to generate SQL statements in batches, write them to a text file, open the text file, CTRL +, CTRL + C, and finally Ctrl + V... you know... (In fact, you can directly run the program and insert it into the database table, but here to demonstrate the execution process, I will use Ctrl + ...)

Let's clarify our ideas:

1. Let's take a look at our requirements and what fields are needed.

2. How can we find the required fields.

3. How to splice the field into the SQL statement we need.

4. Generate a text file and execute it in SQL management tools.

 

First, let's take a look at what table we want to create:

Use itcast201112

Go

Create Table mystudent

(

FID int identity (1, 1) primary key,

Fname nvarchar (10 ),

Fage int,

Fgender nvarchar (10 ),

Fmath int,

Fenglish int

)

Well, we know what we need, so next step, we will first save the previous SQL statement in a batch file and name it table.txt.

Note: Save the text file format, I tested using UTF-8, I did not test the rest, but be sure to pay attention to the format, otherwise there will be garbled Oh (note is Chinese, english is not affected )! I remember that UTF-8 is for Chinese.

 

Now let's look for the fields we need. There are many methods. Here I use a regular expression to generate...

String firstname = system. Io. file. readalltext ("baijia surname .txt", encoding. Default );

List <string> firstnames = new list <string> (); // it is convenient to use a set.

// Extract the surname and Regular Expression

Matchcollection MS = RegEx. Matches (firstname, @ "[(\ W })〕");

Foreach (Match m in MS)

{

If (M. Success)

{

Firstnames. Add (M. Groups [1]. value. tostring ());

}

}

// Get the boy name

String [] boynames = system. Io. file. readalllines ("Hello boy name .txt", encoding. utf8 );

// Obtain the region name

String girlnamestring = system. Io. file. readalltext ("the name of the listener .txt", encoding. Default );

String [] girlnames = girlnamestring. Split (New char [] {','}, stringsplitoptions. removeemptyentries );

// Generate a name based on the user's needs to receive user input

Console. writeline ("Enter the required name !!! ");

String READ = console. Readline ();

Int;

While (! Int. tryparse (read, out ))

{

Console. writeline ("your input is incorrect. Please enter it again! ");

Read = console. Readline ();

}

Stringbuilder sb = new stringbuilder ();

Random r = new random ();

// Name

For (INT I = 0; I <A; I ++)

{

Int gender = R. Next (0, 2 );

If (Gender = 0)

{

String boys = string. format ("{0} {1} \ t {2} \ t {3}", firstnames [R. next (firstnames. count)], boynames [R. next (boynames. length)], R. next (), "male"); // format: Surname + name, age, gender

SB. appendline (boys); // Add it to stringbuilder. Use line breaks to separate each line containing "last name + name, age, and Gender ".

}

Else

{

String girls = string. format ("{0} {1} \ t {2} \ t {3}", firstnames [R. next (firstnames. count)], girlnames [R. next (girlnames. length)], R. next (18,31), "female ");

SB. appendline (girls );

}

}

System. Io. file. writealltext (@ "C: \ name1.txt", SB. tostring ());

...

The file name is name1.txt.

 

Now that you have obtained the name, age, and gender fields, you can easily splice the SQL string!

Create a C # console application: createsql. copy table.txtand name1.txt to the bin-DEBUG directory of the project. The code in the main program is as follows:

String MySQL = "insert into mystudent (fname, Fage, fgender, fmath, fenglish) values ('{0}', {1}, '{2}', {3 }, {4}) "; // write a spliced model first

String table = system. io. file. readalltext (@ "table.txt", encoding. utf8); // read the statement used to create a table. It does not work much here, just to write the statement to a text file later.

String [] namestring = system. Io. file. readalllines (@" name1.txt ", encoding. UTF-8 );

Stringbuilder sb = new stringbuilder (); // It is used because it is a large number of strings.

SB. appendline (table );

Random r = new random (); // declare a random number object to randomly generate mathematical English scores.

Foreach (string names in namestring)

{

String name = names. Split (New char [] {'\ t'}, stringsplitoptions. removeemptyenteies );

String SQL = string. format (MySQL, name [0], name [2], name [1], R. next (1, 150), R. next (150); // is it hard to understand here? Taste it slowly! If you replace MySQL with the strings stored in it, do you understand it!

SB. appendline (SQL); // added to stringbuilder. There is a line break between it and the last one, because the appendline...

SB. appendline ("go ");

}

System. Io. file. writealltext (@ "D: \ database \ insertsql.txt", SB. tostring (); // write the string stored in stringbuilder to the text file...

This is done. Open the generated file, copy what you want, and run it in SQL management tools...