In the garden, csdn-Chinese is the most discussed in the past two days.It communityThe spread of 6 million user data on the Internet, as a technician, has put aside their own ideas for the moment. From a technical perspective, we can analyze and test our programming basics.
Release the downloaded compressed file to the hard disk to obtain the file www.csdn.net. SQL. As a technician, I am curious about this file and have an impulse to program it. The following two programming questions are proposed:
1. How to import 6 million data to SQL server? 2. How do I select an appropriate password encryption method?
This SQL file is 273 mb. Opening it with SQL Server Management studio will generate an outofmemory exception and it will be very slow to open it with notepad. We recommend that you use ultra EditSource codeOpen the editing software.
It is estimated that everyone's hard drive has this file, so I don't have to add a shadow to the password and email.
Find your account and password by using the email you have registered. It is clear.
Analyze the SQL file, which is separated by line breaks. Separate the user accounts, passwords, and email formats. This is similar to the comma-separated file format, so you can import it to SQL Server. See the following two linesCode
Create TableCsdnuser ([User] Nvarchar (Max))BulkInsert csdnuserFromN'F: \ www.csdn.net. SQL'
Then there is a long wait, on a 4G memory, 4-core Windows 7 x64 machine, it took nearly five minutes to import data from a text file to the SQL Server csdnuser table. I usually like to study performance and performance improvement. After the execution is complete, the message is displayed
(6428632 row (s) affected), there are indeed more than 6 million rows of data, the disk for storing database files all of a sudden consumes nearly 6 GB of space.
Run the SQL statement select [user] From csdnuser, and the user data is stored in the SQL Server database file.
This completes the process of importing text files to SQL Server. If I want to further break down the data of each row, as shown in the following result:
Select DBO. getsplitofindex (user, '', 1) as [userid]
DBO. getsplitofindex (user, '', 2) as [Password]
DBO. getsplitofindex (user, '', 3) as [email]
From csdnuser
You also need to write a split function to break down user data separated. The getsplitofindex function is from the Internet and the address is
Http://www.fengfly.com/plus/view-168590-1.html, author rank name, thanks to his wisdom. The source code of the function is as follows:
Create Function [DBO]. [getsplitofindex] (@ string nvarchar ( Max ), -- The string to be split @ split nvarchar (10), -- separator number @ Index Int -- Take the nth element) Returns Nvarchar (1024) As Begin Declare @ Location Int Declare @ Start Int Declare @ Next Int Declare @ SeedInt Set @ String = ltrim (rtrim (@ string )) Set @ Start = 1 Set @ Next = 1 Set @ Seed = Len (@ split) Set @ Location = charindex (@ split, @ string) While @ Location <> 0 And @ Index > @ Next Begin Set @Start = @ Location + @ Seed Set @ Location = charindex (@ split, @ string ,@ Start ) Set @ Next = @ Next + 1 End If @ Location = 0 Select @ Location = Len (@ string) + 1 Return Substring (@ String ,@ Start , @ Location -@ Start ) End
Since SQL Server 2005, CLR has been integrated, allowing. Net-managed code to run in SQL Server. With the familiar. Net API, tasks can be easily completed. Open visual stdio 2010 and create an SQL Server CLR project. The source code list of the SQL Server CLP function is as follows:
[Microsoft. sqlserver. server. sqlfunction] Public static sqlstring split ( string userline, int index) { If (! string . isnullorempty (userline) { string [] lines = RegEx. split (userline, "#" ); If (lines. length = 3) return New sqlstring (lines [Index]); else throw New applicationexception ( "invalid input line" );} return New sqlstring ( ""
);}
The call method is the same as that of the tsql function, as shown in
Select DBO. Split (user, 0) as [user id]
DBO. Split (user, 1) as [Password]
DBO. Split (user, 2) as [email]
From csdnuser
If you have a better method, please leave a message.
Whether the password is in plain text or encrypted is justified in various cases. Without password encryption, plaintext transmission is not recommended, but many systems use plain text to store data in databases. If you are worried about poor plaintext and are easy to obtain, you can adopt reversible encryption. Please refer to myArticleData Encryption gadgets, which encrypt the data to be protected, and easily reverse the original plaintext
In the user feedback function of data solution in my knowledge management system, the user inputs the feedback content and sends it to the specified mailbox, because SMTP is used, therefore, you need to write the user name and passwordProgramBut in order to prevent others from easily reading the account and password of my email from the Il code, I used the above reversible encryption method, after encrypting the email account and password, you can add them to the program code to increase the difficulty of cracking. It does not include important information in the email account used to receive user feedback. After all, it may be cracked by others.
Further, you can use the. NET-providedAlgorithmTo perform hash operations or encryption. MD5 is recognized as an irreversible encryption algorithm. Its calling method is as follows:
Byte[] Original = encoding. Default. getbytes (txt_source.text); MD5 S1 = md5.create ();// Use MD5Byte[] Change = s1.computehash (original); encrypted txt_result.text = convert. tobase64string (change );
Refer to the article "use MD5 to encrypt strings in C # vs2005 sample code" to learn how to call MD5.
If you need the MD5 call example code, the following code can help you. The code is used by friends in the garden,
Address is http://www.cnblogs.com/konooo/archive/2009/01/22/1379920.html
/// The MD5 16-bit encrypted password is capitalized /// </Summary> /// <Param name = "convertstring"> </param> /// <Returns> </returns> Public Static String Getmd5str (String Convertstring) {md5cryptoserviceprovider MD5 = New Md5cryptoserviceprovider (); String T2 = bitconverter. tostring (md5.computehash (utf8encoding. Default. getbytes (convertstring), 4, 8); t2 = t2.replace ( "-" , "" ); Return T2 ;} /// <Summary> /// The MD5 16-bit encrypted password is in lower case /// </Summary> /// <Param name = "convertstring"> </param> /// <Returns> </returns> Public Static String Getmd5str ( String Convertstring) {md5cryptoserviceprovider MD5 = New Md5cryptoserviceprovider (); String T2 = bitconverter. tostring (md5.computehash (utf8encoding. Default. getbytes (convertstring), 4, 8); t2 = t2.replace ( "-" , "" ); T2 = t2.tolower (); Return T2 ;} /// <Summary> /// MD5 32-bit encryption /// </Summary> /// <Param name = "str"> </param> /// <Returns> </returns> Static String Usermd5 ( String Str ){ String CL = STR; String Pwd = "" ; MD5 MD5 = md5.create (); // Instantiate an MD5 object // After encryption, it is an array of the byte type. Pay attention to the selection of UTF-8/Unicode encoding. Byte [] S = md5.computehash (encoding. utf8.getbytes (CL )); // Convert an array of the byte type into a string by using a loop. This string is obtained by regular character formatting. For ( Int I = 0; I <S. length; I ++ ){// Use the hexadecimal format of the obtained string. The characters in the format are lowercase letters. If uppercase letters (x) are used, the characters in the format are uppercase letters. Pwd = PWD + s [I]. tostring ( "X" );} Return PWD ;}
In the past few years, I visited csdn frequently and liked its download channels. I could find many resources. After that, I could not download resources that require points, therefore, you have registered multiple csdn accounts to download resources only. Here I have two micro-words. Since it is for sharing purposes, why should we restrict others' downloads? Sina's knowledgeable people do not have such complicated download rules. Even if the downloaded file requires points, you can find resources that do not need to be divided, without restrictions. There is also a rule. If your csdn account is not enough, you can only upload but cannot delete the files you have uploaded. I have also released software programs on csdn, and later I have released a new version, so I cannot delete the old version of the program myself. I have to post a post on the Forum and delete it only by the Administrator, I gave up csdn as a channel for publishing program code. However, csdn is still of great help to our technology. When encountering problems, the headlines found by Baidu are usually the posts of csdn forums, which should be appreciated and paid tribute to csdn.