SELECT MasterCustomerId, DuplicateCustomerId
FROM DuplicateCustomers WHERE IsMerged = 0 DECLARE @MergeDate Datetime
DECLARE @MasterId Int
DECLARE @DuplicateId Int DECLARE merge_cursor CURSOR FAST_FORWARD FOR
SELECT MasterCustomerId, DuplicateCustomerId
FROM DuplicateCustomers WHERE IsMerged = 0
DECLARE cursor_name CURSOR
[ LOCAL | GLOBAL ]
[ FORWARD_ONLY | SCROLL ]
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
[ TYPE_WARNING ]
FOR select_statement
[ FOR UPDATE [ OF column_name [ ,...n ] ] ] OPEN merge_cursor FETCH NEXT FROM merge_cursor INTO @MasterId, @DuplicateId FETCH
[ [ NEXT | PRIOR | FIRST | LAST
| ABSOLUTE { n | @nvar }
| RELATIVE { n | @nvar }
]
FROM
]
{ { [ GLOBAL ] cursor_name } | @cursor_variable_name }
[ INTO @variable_name [ ,...n ] ] | 0 | FETCH statement was successful |
| -1 | FETCH statement failed or the row was beyond the result set |
| -2 | Row fetched is missing |
DECLARE @MergeDate Datetime
DECLARE @MasterId Int
DECLARE @DuplicateId Int
SELECT @MergeDate = GetDate()
DECLARE merge_cursor CURSOR FAST_FORWARD FOR SELECT MasterCustomerId, DuplicateCustomerId FROM DuplicateCustomers WHERE IsMerged = 0
OPEN merge_cursor
FETCH NEXT FROM merge_cursor INTO @MasterId, @DuplicateId
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC MergeDuplicateCustomers @MasterId, @DuplicateId
UPDATE DuplicateCustomers
SET
IsMerged = 1,
MergeDate = @MergeDate
WHERE
MasterCustomerId = @MasterId AND
DuplicateCustomerId = @DuplicateId
FETCH NEXT FROM merge_cursor INTO @MasterId, @DuplicateId
END
CLOSE merge_cursor
DEALLOCATE merge_cursor
SELECT email,
COUNT(email) AS NumOccurrences
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )
SELECT email
FROM users
GROUP BY email
HAVING ( COUNT(email) = 1 )
| Name | Description |
|---|---|
ASCII() | Return numeric value of left-most character |
BIN() | Return a string representation of the argument |
BIT_LENGTH() | Return length of argument in bits |
CHAR_LENGTH() | Return number of characters in argument |
CHAR() | Return the character for each integer passed |
CHARACTER_LENGTH() | A synonym for CHAR_LENGTH() |
CONCAT_WS() | Return concatenate with separator |
CONCAT() | Return concatenated string |
ELT() | Return string at index number |
EXPORT_SET() | Return a string such that for every bit set in the value bits, you get an on string and for every unset bit, you get an off string |
FIELD() | Return the index (position) of the first argument in the subsequent arguments |
FIND_IN_SET() | Return the index position of the first argument within the second argument |
FORMAT() | Return a number formatted to specified number of decimal places |
HEX() | Return a hexadecimal representation of a decimal or string value |
INSERT() | Insert a substring at the specified position up to the specified number of characters |
INSTR() | Return the index of the first occurrence of substring |
LCASE() | Synonym for LOWER() |
LEFT() | Return the leftmost number of characters as specified |
LENGTH() | Return the length of a string in bytes |
LIKE | Simple pattern matching |
LOAD_FILE() | Load the named file |
LOCATE() | Return the position of the first occurrence of substring |
LOWER() | Return the argument in lowercase |
LPAD() | Return the string argument, left-padded with the specified string |
LTRIM() | Remove leading spaces |
MAKE_SET() | Return a set of comma-separated strings that have the corresponding bit in bits set |
MATCH | Perform full-text search |
MID() | Return a substring starting from the specified position |
NOT LIKE | Negation of simple pattern matching |
NOT REGEXP | Negation of REGEXP |
OCTET_LENGTH() | A synonym for LENGTH() |
ORD() | Return character code for leftmost character of the argument |
POSITION() | A synonym for LOCATE() |
QUOTE() | Escape the argument for use in an SQL statement |
REGEXP | Pattern matching using regular expressions |
REPEAT() | Repeat a string the specified number of times |
REPLACE() | Replace occurrences of a specified string |
REVERSE() | Reverse the characters in a string |
RIGHT() | Return the specified rightmost number of characters |
RLIKE | Synonym for REGEXP |
RPAD() | Append string the specified number of times |
RTRIM() | Remove trailing spaces |
SOUNDEX() | Return a soundex string |
SOUNDS LIKE | Compare sounds |
SPACE() | Return a string of the specified number of spaces |
STRCMP() | Compare two strings |
SUBSTR() | Return the substring as specified |
SUBSTRING_INDEX() | Return a substring from a string before the specified number of occurrences of the delimiter |
SUBSTRING() | Return the substring as specified |
TRIM() | Remove leading and trailing spaces |
UCASE() | Synonym for UPPER() |
UNHEX() | Convert each pair of hexadecimal digits to a character |
UPPER() | Convert to uppercase |
NULL if the length of the result would be greater than the value of the max_allowed_packet system variable. See Section 8.9.2, “Tuning Server Parameters”.ASCII(str)str. Returns 0 if str is the empty string. Returns NULL if str is NULL. ASCII() works for 8-bit characters.mysql>SELECT ASCII('2');
-> 50
mysql>SELECT ASCII(2);
-> 50
mysql>SELECT ASCII('dx');
-> 100
ORD() function.BIN(N)N, where N is a longlong (BIGINT) number. This is equivalent to CONV(N,10,2). Returns NULL if N is NULL.mysql>SELECT BIN(12);
-> '1100'
BIT_LENGTH(str)str in bits.mysql>SELECT BIT_LENGTH('text');
-> 32
CHAR(N,... [USING charset_name])CHAR() interprets each argument N as an integer and returns a string consisting of the characters given by the code values of those integers. NULL values are skipped.mysql>SELECT CHAR(77,121,83,81,'76');
-> 'MySQL'
mysql>SELECT CHAR(77,77.3,'77.3');
-> 'MMM'
CHAR() arguments larger than 255 are converted into multiple result bytes. For example, CHAR(256) is equivalent to CHAR(1,0), and CHAR(256*256) is equivalent to CHAR(1,0,0):mysql>SELECT HEX(CHAR(1,0)), HEX(CHAR(256));
+----------------+----------------+
| HEX(CHAR(1,0)) | HEX(CHAR(256)) |
+----------------+----------------+
| 0100 | 0100 |
+----------------+----------------+
mysql>SELECT HEX(CHAR(1,0,0)), HEX(CHAR(256*256));
+------------------+--------------------+
| HEX(CHAR(1,0,0)) | HEX(CHAR(256*256)) |
+------------------+--------------------+
| 010000 | 010000 |
+------------------+--------------------+
CHAR() returns a binary string. To produce a string in a given character set, use the optional USING clause:mysql>SELECT CHARSET(CHAR(0x65)), CHARSET(CHAR(0x65 USING utf8));
+---------------------+--------------------------------+
| CHARSET(CHAR(0x65)) | CHARSET(CHAR(0x65 USING utf8)) |
+---------------------+--------------------------------+
| binary | utf8 |
+---------------------+--------------------------------+
USING is given and the result string is illegal for the given character set, a warning is issued. Also, if strict SQL mode is enabled, the result from CHAR() becomes NULL.CHAR_LENGTH(str)str, measured in characters. A multi-byte character counts as a single character. This means that for a string containing five two-byte characters, LENGTH() returns 10, whereas CHAR_LENGTH() returns 5.CHARACTER_LENGTH(str)CHARACTER_LENGTH() is a synonym for CHAR_LENGTH().CONCAT(str1,str2,...)SELECT CONCAT(CAST(int_colAS CHAR),char_col);
CONCAT() returns NULL if any argument is NULL.mysql>SELECT CONCAT('My', 'S', 'QL');
-> 'MySQL'
mysql>SELECT CONCAT('My', NULL, 'QL');
-> NULL
mysql>SELECT CONCAT(14.3);
-> '14.3'
mysql>SELECT 'My' 'S' 'QL';
-> 'MySQL'
CONCAT_WS(separator,str1,str2,...)CONCAT_WS() stands for Concatenate With Separator and is a special form of CONCAT(). The first argument is the separator for the rest of the arguments. The separator is added between the strings to be concatenated. The separator can be a string, as can the rest of the arguments. If the separator is NULL, the result is NULL.mysql>SELECT CONCAT_WS(',','First name','Second name','Last Name');
-> 'First name,Second name,Last Name'
mysql>SELECT CONCAT_WS(',','First name',NULL,'Last Name');
-> 'First name,Last Name'
CONCAT_WS() does not skip empty strings. However, it does skip any NULL values after the separator argument.ELT(N,str1,str2,str3,...)str1 if N = 1, str2 if N = 2, and so on. Returns NULL if N is less than 1 or greater than the number of arguments. ELT() is the complement of FIELD().mysql>SELECT ELT(1, 'ej', 'Heja', 'hej', 'foo');
-> 'ej'
mysql>SELECT ELT(4, 'ej', 'Heja', 'hej', 'foo');
-> 'foo'
EXPORT_SET(bits,on,off[,separator[,number_of_bits]])bits, you get an on string and for every bit not set in the value, you get an off string. Bits in bits are examined from right to left (from low-order to high-order bits). Strings are added to the result from left to right, separated by the separator string (the default being the comma character “,”). The number of bits examined is given by number_of_bits, which has a default of 64 if not specified. number_of_bits is silently clipped to 64 if larger than 64. It is treated as an unsigned integer, so a value of –1 is effectively the same as 64.mysql>SELECT EXPORT_SET(5,'Y','N',',',4);
-> 'Y,N,Y,N'
mysql>SELECT EXPORT_SET(6,'1','0',',',10);
-> '0,1,1,0,0,0,0,0,0,0'
FIELD(str,str1,str2,str3,...)str in the str1, str2, str3, ... list. Returns 0 if str is not found.FIELD() are strings, all arguments are compared as strings. If all arguments are numbers, they are compared as numbers. Otherwise, the arguments are compared as double.str is NULL, the return value is 0 because NULL fails equality comparison with any value. FIELD() is the complement of ELT().mysql>SELECT FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo');
-> 2
mysql>SELECT FIELD('fo', 'Hej', 'ej', 'Heja', 'hej', 'foo');
-> 0
FIND_IN_SET(str,strlist)N if the string str is in the string list strlist consisting of N substrings. A string list is a string composed of substrings separated by “,” characters. If the first argument is a constant string and the second is a column of type SET, the FIND_IN_SET() function is optimized to use bit arithmetic. Returns 0 if str is not in strlist or if strlist is the empty string. Returns NULL if either argument is NULL. This function does not work properly if the first argument contains a comma (“,”) character.mysql>SELECT FIND_IN_SET('b','a,b,c,d');
-> 2
FORMAT(X,D)X to a format like '#,###,###.##', rounded to D decimal places, and returns the result as a string. If D is 0, the result has no decimal point or fractional part.mysql>SELECT FORMAT(12332.123456, 4);
-> '12,332.1235'
mysql>SELECT FORMAT(12332.1,4);
-> '12,332.1000'
mysql>SELECT FORMAT(12332.2,0);
-> '12,332'
HEX(str), HEX(N)str, HEX() returns a hexadecimal string representation of str where each character in str is converted to two hexadecimal digits. The inverse of this operation is performed by the UNHEX() function.N, HEX() returns a hexadecimal string representation of the value of N treated as a longlong (BIGINT) number. This is equivalent to CONV(N,10,16). The inverse of this operation is performed by CONV(HEX(N),16,10).mysql>SELECT 0x616263, HEX('abc'), UNHEX(HEX('abc'));
-> 'abc', 616263, 'abc'
mysql>SELECT HEX(255), CONV(HEX(255),16,10);
-> 'FF', 255
INSERT(str,pos,len,newstr)str, with the substring beginning at position pos and len characters long replaced by the string newstr. Returns the original string if pos is not within the length of the string. Replaces the rest of the string from position pos if len is not within the length of the rest of the string. Returns NULL if any argument is NULL.mysql>SELECT INSERT('Quadratic', 3, 4, 'What');
-> 'QuWhattic'
mysql>SELECT INSERT('Quadratic', -1, 4, 'What');
-> 'Quadratic'
mysql>SELECT INSERT('Quadratic', 3, 100, 'What');
-> 'QuWhat'
INSTR(str,substr)substr in string str. This is the same as the two-argument form of LOCATE(), except that the order of the arguments is reversed.mysql>SELECT INSTR('foobarbar', 'bar');
-> 4
mysql>SELECT INSTR('xbar', 'foobar');
-> 0
LCASE(str)LCASE() is a synonym for LOWER().LEFT(str,len)len characters from the string str, or NULL if any argument is NULL.mysql>SELECT LEFT('foobarbar', 5);
-> 'fooba'
LENGTH(str)str, measured in bytes. A multi-byte character counts as multiple bytes. This means that for a string containing five two-byte characters, LENGTH() returns 10, whereas CHAR_LENGTH() returns 5.mysql>SELECT LENGTH('text');
-> 4
LOAD_FILE(file_name)FILE privilege. The file must be readable by all and its size less than max_allowed_packet bytes. If the secure_file_priv system variable is set to a nonempty directory name, the file to be loaded must be located in that directory.NULL.character_set_filesystem system variable controls interpretation of file names that are given as literal strings.mysql>UPDATE t
SET blob_col=LOAD_FILE('/tmp/picture')
WHERE id=1;
LOCATE(substr,str), LOCATE(substr,str,pos)substr in string str. The second syntax returns the position of the first occurrence of substring substr in string str, starting at position pos. Returns 0 if substr is not in str.mysql>SELECT LOCATE('bar', 'foobarbar');
-> 4
mysql>SELECT LOCATE('xbar', 'foobar');
-> 0
mysql>SELECT LOCATE('bar', 'foobarbar', 5);
-> 7
LOWER(str)str with all characters changed to lowercase according to the current character set mapping. The default is latin1 (cp1252 West European).mysql>SELECT LOWER('QUADRATICALLY');
-> 'quadratically'
LOWER() (and UPPER()) are ineffective when applied to binary strings (BINARY, VARBINARY, BLOB). To perform lettercase conversion, convert the string to a nonbinary string:mysql>SET @str = BINARY 'New York';
mysql>SELECT LOWER(@str), LOWER(CONVERT(@str USING latin1));
+-------------+-----------------------------------+
| LOWER(@str) | LOWER(CONVERT(@str USING latin1)) |
+-------------+-----------------------------------+
| New York | new york |
+-------------+-----------------------------------+
LPAD(str,len,padstr)str, left-padded with the string padstr to a length of len characters. If str is longer than len, the return value is shortened to len characters.mysql>SELECT LPAD('hi',4,'??');
-> '??hi'
mysql>SELECT LPAD('hi',1,'??');
-> 'h'
LTRIM(str)str with leading space characters removed.mysql>SELECT LTRIM(' barbar');
-> 'barbar'
MAKE_SET(bits,str1,str2,...),” characters) consisting of the strings that have the corresponding bit in bits set. str1 corresponds to bit 0, str2 to bit 1, and so on. NULL values in str1, str2, ... are not appended to the result.mysql>SELECT MAKE_SET(1,'a','b','c');
-> 'a'
mysql>SELECT MAKE_SET(1 | 4,'hello','nice','world');
-> 'hello,world'
mysql>SELECT MAKE_SET(1 | 4,'hello','nice',NULL,'world');
-> 'hello'
mysql>SELECT MAKE_SET(0,'a','b','c');
-> ''
MID(str,pos,len)MID(str,pos,len) is a synonym for SUBSTRING(str,pos,len).OCTET_LENGTH(str)OCTET_LENGTH() is a synonym for LENGTH().ORD(str)str is a multi-byte character, returns the code for that character, calculated from the numeric values of its constituent bytes using this formula:(1st byte code)
+ (2nd byte code * 256)
+ (3rd byte code * 256
) ...
ORD() returns the same value as the ASCII() function.mysql>SELECT ORD('2');
-> 50
POSITION(substr IN str)POSITION(substr IN str) is a synonym for LOCATE(substr,str).QUOTE(str)\”), single quote (“'”), ASCII NUL, and Control+Z preceded by a backslash. If the argument is NULL, the return value is the word “NULL” without enclosing single quotation marks.mysql>SELECT QUOTE('Don\'t!');
-> 'Don\'t!'
mysql>SELECT QUOTE(NULL);
-> NULL
mysql_real_escape_string()”.REPEAT(str,count)str repeated count times. If count is less than 1, returns an empty string. Returns NULL if str or count are NULL.mysql>SELECT REPEAT('MySQL', 3);
-> 'MySQLMySQLMySQL'
REPLACE(str,from_str,to_str)str with all occurrences of the string from_str replaced by the string to_str. REPLACE() performs a case-sensitive match when searching for from_str.mysql>SELECT REPLACE('www.mysql.com', 'w', 'Ww');
-> 'WwWwWw.mysql.com'
REVERSE(str)str with the order of the characters reversed.mysql>SELECT REVERSE('abc');
-> 'cba'
RIGHT(str,len)len characters from the string str, or NULL if any argument is NULL.mysql>SELECT RIGHT('foobarbar', 4);
-> 'rbar'
RPAD(str,len,padstr)str, right-padded with the string padstr to a length of len characters. If str is longer than len, the return value is shortened to len characters.mysql>SELECT RPAD('hi',5,'?');
-> 'hi???'
mysql>SELECT RPAD('hi',1,'?');
-> 'h'
RTRIM(str)str with trailing space characters removed.mysql>SELECT RTRIM('barbar ');
-> 'barbar'
SOUNDEX(str)str. Two strings that sound almost the same should have identical soundex strings. A standard soundex string is four characters long, but the SOUNDEX() function returns an arbitrarily long string. You can use SUBSTRING() on the result to get a standard soundex string. All nonalphabetic characters in str are ignored. All international alphabetic characters outside the A-Z range are treated as vowels.SOUNDEX(), you should be aware of the following limitations:utf-8.mysql>SELECT SOUNDEX('Hello');
-> 'H400'
mysql>SELECT SOUNDEX('Quadratically');
-> 'Q36324'
expr1 SOUNDS LIKE expr2SOUNDEX(expr1) = SOUNDEX(expr2).SPACE(N)N space characters.mysql>SELECT SPACE(6);
-> ' '
SUBSTR(str,pos), SUBSTR(str FROM pos), SUBSTR(str,pos,len), SUBSTR(str FROM pos FOR len)SUBSTR() is a synonym for SUBSTRING().SUBSTRING(str,pos), SUBSTRING(str FROM pos), SUBSTRING(str,pos,len), SUBSTRING(str FROM pos FOR len)len argument return a substring from string str starting at position pos. The forms with a len argument return a substring len characters long from string str, starting at position pos. The forms that use FROM are standard SQL syntax. It is also possible to use a negative value for pos. In this case, the beginning of the substring is pos characters from the end of the string, rather than the beginning. A negative value may be used for pos in any of the forms of this function.SUBSTRING(), the position of the first character in the string from which the substring is to be extracted is reckoned as 1.mysql>SELECT SUBSTRING('Quadratically',5);
-> 'ratically'
mysql>SELECT SUBSTRING('foobarbar' FROM 4);
-> 'barbar'
mysql>SELECT SUBSTRING('Quadratically',5,6);
-> 'ratica'
mysql>SELECT SUBSTRING('Sakila', -3);
-> 'ila'
mysql>SELECT SUBSTRING('Sakila', -5, 3);
-> 'aki'
mysql>SELECT SUBSTRING('Sakila' FROM -4 FOR 2);
-> 'ki'
len is less than 1, the result is the empty string.SUBSTRING_INDEX(str,delim,count)str before count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. SUBSTRING_INDEX() performs a case-sensitive match when searching for delim.mysql>SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
-> 'www.mysql'
mysql>SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
-> 'mysql.com'
TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str), TRIM([remstr FROM] str)str with all remstr prefixes or suffixes removed. If none of the specifiers BOTH, LEADING, or TRAILING is given, BOTH is assumed. remstr is optional and, if not specified, spaces are removed.mysql>SELECT TRIM(' bar ');
-> 'bar'
mysql>SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
-> 'barxxx'
mysql>SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
-> 'bar'
mysql>SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');
-> 'barx'
UCASE(str)UCASE() is a synonym for UPPER().UNHEX(str)str, UNHEX(str) performs the inverse operation of HEX(str). That is, it interprets each pair of characters in the argument as a hexadecimal number and converts it to the character represented by the number. The return value is a binary string.mysql>SELECT UNHEX('4D7953514C');
-> 'MySQL'
mysql>SELECT 0x4D7953514C;
-> 'MySQL'
mysql>SELECT UNHEX(HEX('string'));
-> 'string'
mysql>SELECT HEX(UNHEX('1267'));
-> '1267'
'0' .. '9', 'A' .. 'F', 'a' .. 'f'. If the argument contains any nonhexadecimal digits, the result is NULL:mysql>SELECT UNHEX('GG');
+-------------+
| UNHEX('GG') |
+-------------+
| NULL |
+-------------+
NULL result can occur if the argument to UNHEX() is a BINARY column, because values are padded with 0x00 bytes when stored but those bytes are not stripped on retrieval. For example, '41' is stored into a CHAR(3) column as '41 ' and retrieved as '41' (with the trailing pad space stripped), so UNHEX() for the column value returns 'A'. By contrast '41' is stored into a BINARY(3) column as '41\0' and retrieved as '41\0' (with the trailing pad 0x00 byte not stripped). '\0' is not a legal hexadecimal digit, so UNHEX() for the column value returns NULL.N, the inverse of HEX(N) is not performed by UNHEX(). Use CONV(HEX(N),16,10) instead. See the description of HEX().UPPER(str)str with all characters changed to uppercase according to the current character set mapping. The default is latin1 (cp1252 West European).mysql>SELECT UPPER('Hej');
-> 'HEJ'
LOWER() for information that also applies to UPPER(), such as information about how to perform lettercase conversion of binary strings (BINARY, VARBINARY, BLOB) for which these functions are ineffective.SELECT * FROM Film where REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE
(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE
(Film.name,':',''),'~',''),'!',''),'@',''),'#',''),'$',''),'%',''),'*',''),'(',''),')',''),'_',''),'+',''),
'{',''),'}',''),'[',''),']',''),';',''),'''',''),':',''),'"',''),'<',''),'>',''),'?','') = ? or SELECT * FROM Film where REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE
(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE
(REPLACE(Film.name,':',''),'~',''),'!',''),'@',''),'#',''),'$',''),'%',''),'*',''),'(',''),')',''),'_',''),
'+',''),'{',''),'}',''),'[',''),']',''),';',''),'''',''),':',''),'"',''),'<',''),'>',''),'?',''),' ','') = ? | DB | Connection String |
|---|---|
| Access | Access ODBC Connection String Driver{Microsoft Access Driver (*.mdb)};Dbq=C:\demo.mdb;Uid=Admin;Pwd=; Access OLEDB Connection String DriverProvider=Microsoft.Jet.OLEDB.4.0;Data Source=\directory\demo.mdb;User Id=admin;Password=; |
| DB2 | DB2 ODBC Connection Stringdriver={IBM DB2 ODBC DRIVER};Database=demodb;hostname=myservername;port=myPortNum;protocol=TCPIP; uid=myusername; pwd=mypasswd DB2 OLEDB Connection StringProvider=IBMDADB2;Database=demodeb;HOSTNAME=myservername;PROTOCOL=TCPIP;PORT=50000;uid=myusername;pwd=mypasswd; |
| DBase | DBase ODBC Connection StringDriver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=c:\directory; DBase OLEDB Connection StringProvider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\directory;Extended Properties=dBASE IV;User ID=Admin;Password= |
| Excel | Excel ODBC Connection StringDriver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=C:\MyExcel.xls;DefaultDir=c:\directory; Excel OLEDB Connection StringProvider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties='"Excel 8.0;HDR=Yes;IMEX=1"' |
| Exchange | Exchange OLEDB Connection StringoConn.Provider = "EXOLEDB.DataSource" oConn.Open = "http://myServerName/myVirtualRootName" |
| Firebird | Firebird ODBC Connection StringDRIVER=Firebird/InterBase(r) driver;UID=SYSDBA;PWD=mypasswd;DBNAME=c:\directory\demo.fdb Firebird OLEDB Connection StringUser=SYSDBA;Password=mypasswd;Database=demo.fdb;DataSource=localhost;Port=3050;Dialect=3;Charset=NONE;Role=;Connection lifetime=15;Pooling=true;MinPoolSize=0;MaxPoolSize=50;Packet Size=8192;ServerType=0 |
| FoxPro | FoxPro ODBC Connection StringDriver={Microsoft Visual FoxPro Driver};SourceType=DBC;SourceDB=c:\demo.dbc;Exclusive=No;NULL=NO;Collate=Machine;BACKGROUNDFETCH=NO;DELETED=NO FoxPro OLEDB Connection StringProvider=vfpoledb.1;Data Source=c:\directory\demo.dbc;Collating Sequence=machine |
| Informix | Informix ODBC Connection StringDriver={Informix-CLI 2.5 (32 Bit)};Server=demoservername;Database=demodb;Uid=myusername;Pwd=mypasswd Informix OLEDB Connection StringProvider=Ifxoledbc.2;User ID=myusername;password=mypasswd;Data Source=demodb@demoservername;Persist Security Info=true |
| MySQL | MySQL ODBC Connection StringDRIVER={MySQL ODBC 3.51 Driver};SERVER=myservername;PORT=3306;DATABASE=mydemodb; USER=myusername;PASSWORD=mypasswd;OPTION=3; MySQL OLEDB Connection StringProvider=MySQLProv;Data Source=mydemodb;User Id=myusername;Password=mypasswd; |
| Oracle | Oracle ODBC Connection StringDriver={Microsoft ODBC for Oracle};Server=myservername;Uid=myusername;Pwd=mypassword; Oracle OLEDB Connection StringProvider=msdaora;Data Source=mydemodb;User Id=myusername;Password=mypasswd; Oracle .Net Connection StringData Source=mydemodb;User Id=myusername;Password=mypasswd;Integrated Security=no; |
| SQL Server | SQL Server ODBC Connection String - Database LoginDriver={SQL Server};Server=myservername;Database=mydemodb;Uid=myusername;Pwd=mypasswd; SQL Server ODBC Connection String - Trusted ConnectionDriver={SQL Server};Server=mysername;Database=mydemodb;Trusted_Connection=yes; SQL Server OLEDB Connection String - Database LoginProvider=sqloledb;Data Source=myservername;Initial Catalog=mydemodb;User Id=myusername;Password=mypasswd; SQL Server OLEDB Connection String - Trusted ConnectionProvider=sqloledb;Data Source=myservername;Initial Catalog=mydemodb;Integrated Security=SSPI; SQL Server .Net Connection String - Database LoginServer=myservername;Database=mydemodb;User ID=myusername;Password=mypasswd;Trusted_Connection=False SQL Server .Net Connection String - Trusted ConnectionServer=myservername;Database=mydemodb;Integrated Security=SSPI; |
| Sybase | Sybase ODBC Connection StringDriver={SYBASE ASE ODBC Driver};Srvr=myservername;Uid=myusername;Pwd=mypasswd Sybase OLEDB Connection StringProvider=Sybase.ASEOLEDBProvider;Server Name=myservername,5000;Initial Catalog=mydemodb;User Id=myusername;Password=mypassword |
public void CreateCSVfile(DataTable dtable, string strFilePath)
{
StreamWriter sw = new StreamWriter(strFilePath, false);
int icolcount = dtable.Columns.Count;
for (int i = 0; i < icolcount; i++)
{
sw.Write(dtable.Columns[i]);
if (i < icolcount - 1)
{
sw.Write(",");
}
}
sw.Write(sw.NewLine);
foreach (DataRow drow in dtable.Rows)
{
for (int i = 0; i < icolcount; i++)
{
if (!Convert.IsDBNull(drow[i]))
{
sw.Write(drow[i].ToString());
}
if (i < icolcount - 1)
{
sw.Write(",");
}
}
sw.Write(sw.NewLine);
}
sw.Close();
}
Insert, Update, Select, Delete) throughout the article to show how to query the database using C#, and in the end I will show you how to backup your database and save it in a .sql file from our application, and how to restore it back.create database ConnectCsharpToMysql;use ConnectCsharpToMysql;create table tableInfo
(
id INT NOT NULL AUTO INCREMENT,
name VARCHAR(30),
age INT,
PRIMARY KEY(id)
);mysql Reference in our project. To do so, we right click our project name, and choose Add Reference:
//Add MySql Library
using MySql.Data.MySqlClient;connection: will be used to open a connection to the database.server: indicates where our server is hosted, in our case, it's localhost.database: is the name of the database we will use, in our case it's the database we already created earlier which is connectcsharptomysql.uid: is our MySQL username.password: is our MySQL password.connectionString: contains the connection string to connect to the database, and will be assigned to the connection variable.class DBConnect
{
private MySqlConnection connection;
private string server;
private string database;
private string uid;
private string password;
//Constructor
public DBConnect()
{
Initialize();
}
//Initialize values
private void Initialize()
{
server = "localhost";
database = "connectcsharptomysql";
uid = "username";
password = "password";
string connectionString;
connectionString = "SERVER=" + server + ";" + "DATABASE=" +
database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";";
connection = new MySqlConnection(connectionString);
}
//open connection to database
private bool OpenConnection()
{
}
//Close connection
private bool CloseConnection()
{
}
//Insert statement
public void Insert()
{
}
//Update statement
public void Update()
{
}
//Delete statement
public void Delete()
{
}
//Select statement
public List <string> [] Select()
{
}
//Count statement
public int Count()
{
}
//Backup
public void Backup()
{
}
//Restore
public void Restore()
{
}
}//open connection to database
private bool OpenConnection()
{
try
{
connection.Open();
return true;
}
catch (MySqlException ex)
{
//When handling errors, you can your application's response based
//on the error number.
//The two most common error numbers when connecting are as follows:
//0: Cannot connect to server.
//1045: Invalid user name and/or password.
switch (ex.Number)
{
case 0:
MessageBox.Show("Cannot connect to server. Contact administrator");
break;
case 1045:
MessageBox.Show("Invalid username/password, please try again");
break;
}
return false;
}
}
//Close connection
private bool CloseConnection()
{
try
{
connection.Close();
return true;
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
return false;
}
}Insert, update and delete are used to write or change data in the database, while Select is used to read data.ExecuteNonQuery: Used to execute a command that will not return any data, for example Insert, update or delete.ExecuteReader: Used to execute a command that will return 0 or more records, for example Select.ExecuteScalar: Used to execute a command that will return only 1 value, for example Select Count(*).Insert, update and delete, which are the easiest. The process to successfully execute a command is as follows:Connection and the CommandText methods in the MySqlCommand class.//Insert statement
public void Insert()
{
string query = "INSERT INTO tableinfo (name, age) VALUES('John Smith', '33')";
//open connection
if (this.OpenConnection() == true)
{
//create command and assign the query and connection from the constructor
MySqlCommand cmd = new MySqlCommand(query, connection);
//Execute command
cmd.ExecuteNonQuery();
//close connection
this.CloseConnection();
}
}
//Update statement
public void Update()
{
string query = "UPDATE tableinfo SET name='Joe', age='22' WHERE name='John Smith'";
//Open connection
if (this.OpenConnection() == true)
{
//create mysql command
MySqlCommand cmd = new MySqlCommand();
//Assign the query using CommandText
cmd.CommandText = query;
//Assign the connection using Connection
cmd.Connection = connection;
//Execute query
cmd.ExecuteNonQuery();
//close connection
this.CloseConnection();
}
}
//Delete statement
public void Delete()
{
string query = "DELETE FROM tableinfo WHERE name='John Smith'";
if (this.OpenConnection() == true)
{
MySqlCommand cmd = new MySqlCommand(query, connection);
cmd.ExecuteNonQuery();
this.CloseConnection();
}
}Select statement, we add a few more steps, and we use the ExecuteReader method that will return a dataReader object to read and store the data or records.Connection and the CommandText methods in the MySqlCommand class.MySqlDataReader object to read the selected records/data.//Select statement
public List< string >[] Select()
{
string query = "SELECT * FROM tableinfo";
//Create a list to store the result
List< string >[] list = new List< string >[3];
list[0] = new List< string >();
list[1] = new List< string >();
list[2] = new List< string >();
//Open connection
if (this.OpenConnection() == true)
{
//Create Command
MySqlCommand cmd = new MySqlCommand(query, connection);
//Create a data reader and Execute the command
MySqlDataReader dataReader = cmd.ExecuteReader();
//Read the data and store them in the list
while (dataReader.Read())
{
list[0].Add(dataReader["id"] + "");
list[1].Add(dataReader["name"] + "");
list[2].Add(dataReader["age"] + "");
}
//close Data Reader
dataReader.Close();
//close Connection
this.CloseConnection();
//return list to be displayed
return list;
}
else
{
return list;
}
}Select Count(*) from tableinfo;, in this case, we will have to use the method ExecuteScalar that will return one value.ExecuteScalar is as follows:Connection and the CommandText methods in the MySqlCommand class.//Count statement
public int Count()
{
string query = "SELECT Count(*) FROM tableinfo";
int Count = -1;
//Open Connection
if (this.OpenConnection() == true)
{
//Create Mysql Command
MySqlCommand cmd = new MySqlCommand(query, connection);
//ExecuteScalar will return one value
Count = int.Parse(cmd.ExecuteScalar()+"");
//close Connection
this.CloseConnection();
return Count;
}
else
{
return Count;
}
}mysqldump -u username -p password -h localhost ConnectCsharpToMysql > "C:\Backup.sql"mysql -u username -p password -h localhost ConnectCsharpToMysql < "C:\Backup.sql"using System.Diagnostics;Process.Start("IExplore.exe");ProcessStartInfo is used in conjunction with Process, to setup the process before it starts.ProcessStartInfo psi = new ProcessStartInfo();
psi.FileName = "IExplore.exe";
psi.Arguments = "www.codeproject.com";
Process.Start(psi);RedirectStandardInput and RedirectStandardOutput properties in the ProcessStartInfo component to cause the process to get input from or return output to a file or other device. If we use the StandardInput or StandardOutput properties on the Process component, we must first set the corresponding value on the ProcessStartInfo property. Otherwise, the system throws an exception when we read or write to the stream.RedirectStandardOutput to true, and read the output from the process into a string and save it to a file.//Backup
public void Backup()
{
try
{
DateTime Time = DateTime.Now;
int year = Time.Year;
int month = Time.Month;
int day = Time.Day;
int hour = Time.Hour;
int minute = Time.Minute;
int second = Time.Second;
int millisecond = Time.Millisecond;
//Save file to C:\ with the current date as a filename
string path;
path = "C:\\MySqlBackup" + year + "-" + month + "-" + day +
"-" + hour + "-" + minute + "-" + second + "-" + millisecond + ".sql";
StreamWriter file = new StreamWriter(path);
ProcessStartInfo psi = new ProcessStartInfo();
psi.FileName = "mysqldump";
psi.RedirectStandardInput = false;
psi.RedirectStandardOutput = true;
psi.Arguments = string.Format(@"-u{0} -p{1} -h{2} {3}",
uid, password, server, database);
psi.UseShellExecute = false;
Process process = Process.Start(psi);
string output;
output = process.StandardOutput.ReadToEnd();
file.WriteLine(output);
process.WaitForExit();
file.Close();
process.Close();
}
catch (IOException ex)
{
MessageBox.Show("Error , unable to backup!");
}
}string, then set the RedirectStandardInput property to true, and write the input from the string to the process.//Restore
public void Restore()
{
try
{
//Read file from C:\
string path;
path = "C:\\MySqlBackup.sql";
StreamReader file = new StreamReader(path);
string input = file.ReadToEnd();
file.Close();
ProcessStartInfo psi = new ProcessStartInfo();
psi.FileName = "mysql";
psi.RedirectStandardInput = true;
psi.RedirectStandardOutput = false;
psi.Arguments = string.Format(@"-u{0} -p{1} -h{2} {3}",
uid, password, server, database);
psi.UseShellExecute = false;
Process process = Process.Start(psi);
process.StandardInput.WriteLine(input);
process.StandardInput.Close();
process.WaitForExit();
process.Close();
}
catch (IOException ex)
{
MessageBox.Show("Error , unable to Restore!");
}
}insert, update, delete and select statements.private void button1_Click(object sender, System.EventArgs e)
{
string MyConString = "SERVER=localhost;" +
"DATABASE=mydatabase;" +
"UID=testuser;" +
"PASSWORD=testpassword;";
MySqlConnection connection = new MySqlConnection(MyConString);
MySqlCommand command = connection.CreateCommand();
MySqlDataReader Reader;
command.CommandText = "select * from mycustomers";
connection.Open();
Reader = command.ExecuteReader();
while (Reader.Read())
{
string thisrow = "";
for (int i= 0;i<Reader.FieldCount;i++)
thisrow+=Reader.GetValue(i).ToString() + ",";
listBox1.Items.Add(thisrow);
}
connection.Close();
}
No GUI tool is available for SQL Server 2005 Express Edition to configure the server. You can do this manually. If your SQL server server is configured to use Windows NT authentication, you can change this to Mixed mode as below.EXEC xp_loginconfig 'login mode'
On the right, look for an entry named LoginMode. The default value, when installed is 1. Update it to 2. The next step is to restart the service.
We can create a new user using the management studio express edition as shown in the figure.osql -E -S .\SQLEXPRESS
1> exec sp_addlogin 'username', 'password'
2> go
1> exec sp_addsrvroleadmin 'username', 'sysadmin'
// above command will not work with SQL Server 2005 express edition
2> go
1> quit
osql -S .\SQLExpress -U username
One it is done , you have to add the specific user to the specific database by selecting the database and right click add new user to bring up a dialog box which makes available the existing users with logins.
