booleanExpression ? trueValue : falseValue;
Example: string itemText = count > 1 ? "items" : "item";
-
--Make the System ignore the constraint
ALTER TABLE PK_ConstraintsTableName NOCHECK CONSTRAINT ALL
--Now Delete
DELETE
FROM TableName
WHERE WhereClause
--Put the contraints back
ALTER TABLE PK_ConstraintsTableName CHECK CONSTRAINT ALL
0Add a comment
-
SQL - Using Case Statement To Update A Table:
CREATE TABLE #t1 (ID INT, name VARCHAR(10), val1 INT, val2 INT)INSERT #t1 (ID , name , val1 , val2 )SELECT 1, 'aaa01', 10, 100UNION ALLSELECT 2, 'bbb01', 20, 200UNION ALLSELECT 3, 'aaa02', 30, 300UNION ALLSELECT 4, 'bbb02', 40, 400SELECT *FROM #t11 aaa01 10 1002 bbb01 20 2003 aaa02 30 3004 bbb02 40 400--Update rows 1 and 3UPDATE #t1SET val2 = CASEWHEN ID = 1 THEN 1000WHEN ID =3 THEN 3000ELSE Val2ENDSELECT *FROM #t1ID name val1 val21 aaa01 10 10002 bbb01 20 2003 aaa02 30 30004 bbb02 40 400DROP TABLE #t10Add a comment
-
Here is an example of what different join produce in SQL.
Create and populate two tables:
CREATE TABLE #t1 (ID INT, name VARCHAR(10), val1 INT, val2 INT)
CREATE TABLE #t2 (ID INT, name VARCHAR(10), val1 INT, val2 INT)
INSERT #t1 (ID , name , val1 , val2 )
SELECT 1, 'aaa01', 10, 100
UNION ALL
SELECT 2, 'bbb01', 20, 200
UNION ALL
SELECT 3, 'aaa02', 30, 300
UNION ALL
SELECT 4, 'bbb02', 40, 400
INSERT #t2 (ID , name , val1 , val2 )
SELECT 3, 'ccc03', 10, 100
UNION ALL
SELECT 4, 'ddd03', 20, 200
UNION ALL
SELECT 5, 'ccc04', 30, 300
UNION ALL
SELECT 6, 'ddd04', 40, 400
_________________________________________________________________________
LEFT JOIN:
SELECT #t1.*, #t2.*
FROM #t1
LEFT JOIN #t2 ON #t1.id = #t2.id
ID name val1 val2 ID name val1 val2
1 aaa01 10 100 NULL NULL NULL NULL
2 bbb01 20 200 NULL NULL NULL NULL
3 aaa02 30 300 3 ccc03 10 100
4 bbb02 40 400 4 ddd03 20 200
_________________________________________________________________________
RIGHT JOIN:SELECT #t1.*, #t2.*FROM #t1RIGHT JOIN #t2 ON #t1.id = #t2.idID name val1 val2 ID name val1 val23 aaa02 30 300 3 ccc03 10 1004 bbb02 40 400 4 ddd03 20 200NULL NULL NULL NULL 5 ccc04 30 300NULL NULL NULL NULL 6 ddd04 40 400_________________________________________________________________________INNER JOIN:
SELECT #t1.*, #t2.*FROM #t1INNER JOIN #t2 ON #t1.id = #t2.idID name val1 val2 ID name val1 val23 aaa02 30 300 3 ccc03 10 1004 bbb02 40 400 4 ddd03 20 200_________________________________________________________________________JOIN:SELECT #t1.*, #t2.*FROM #t1JOIN #t2 ON #t1.id = #t2.idID name val1 val2 ID name val1 val23 aaa02 30 300 3 ccc03 10 1004 bbb02 40 400 4 ddd03 20 200_________________________________________________________________________FULL OUTER JOIN:SELECT #t1.*, #t2.*FROM #t1FULL OUTER JOIN #t2 ON #t1.id = #t2.idID name val1 val2 ID name val1 val21 aaa01 10 100 NULL NULL NULL NULL2 bbb01 20 200 NULL NULL NULL NULL3 aaa02 30 300 3 ccc03 10 1004 bbb02 40 400 4 ddd03 20 200NULL NULL NULL NULL 5 ccc04 30 300NULL NULL NULL NULL 6 ddd04 40 4000Add a comment
-
SQL Complex Grouping using group sub-string.
Data:
ID name val1 val2
1 aaa01 10 100
2 bbb01 20 200
3 aaa02 30 300
4 bbb02 40 400
Result:
aaa 40 400
bbb 60 600
Solution:
CREATE TABLE #t (ID INT, name VARCHAR(10), val1 INT, val2 INT)
INSERT #t (ID , name , val1 , val2 )
SELECT 1, 'aaa01', 10, 100
UNION ALL
SELECT 2, 'bbb01', 20, 200
UNION ALL
SELECT 3, 'aaa02', 30, 300
UNION ALL
SELECT 4, 'bbb02', 40, 400
SELECT SUBSTRING( name, 1, 3),
SUM (val1),
SUM (val2)
FROM #t GROUP BY SUBSTRING(name, 1, 3)
DROP TABLE #t
0Add a comment
-
The Comma Separated Value (CSV) file contains data that (as the name suggests) are separated by commas. In C# they can easily be split into an array by using Data.Split(','). The problem occurs when data contains a comma. To overcome this problem, any data that contains comma is enclosed with double quotes ("").
The following function can split a csv data into array and preserve the double quoted fields. This is a sample function, you pass a single comma separated line of data. By default the separator is a comma.
I have used this in a project and seems to be working. Please do your own testing before using it and if you encounter any problem or issues let me know, so I can fix it.
private string[] csvParser(string csv, char separator = ',')
{
List<string parsed = new <string> - >
();
string[] temp = csv.Split(separator);
int counter = 0;
string data = string.Empty;
while (counter < temp.Length)
{
data = temp[counter].Trim();
if (data.Trim().StartsWith("\""))
{
bool isLast = false;
while (!isLast && counter < temp.Length)
{
data += separator.ToString() + temp[counter + 1];
counter++;
isLast = (temp[counter].Trim().EndsWith("\""));
}
}
parsed.Add(data);
counter++;
}
return parsed.ToArray();
}
Test data:
Only One Field
First, Second
"First-A, First-B", Second, Third, Fourth, Fifth
First, "Second-A, Second-B", Third, Fourth, Fifth
First, "Second-A, Second-B, Second-C", "Third-A, Third-B", Fourth, Fifth
First, Second, Third, Fourth, "Fifth-A, Fifth-B"
"First-A, First-B, First-C", "Second-A, Second-B, Second-C", Third, Fourth, "Fifth-A, Fifth-B, Fifth-C"
"First-A, First-B, First-C", "Second-A, Second-B, Second-C", "Third-A, Third-B, Third-C, Third-D", "Fourth-A, Fourth-B, Fourth-C, Fourth-D, Fourth-E", "Fifth-A, Fifth-B, Fifth-C"2View comments
-
For a project I needed to create a string wrapper function.
The wrapper function had to follow these rules.
1: Each line has a prefix.
2: Each line may not exceed 80 charachters, including the prefix.
3: If the data on a line is less than 80 charachters (including prefix), then buffer it with space characters.
4: Total number of lines may not exceed 3, so there could only be three lines. We drop the rest of the characters if it exceeds three lines.
Here is the function I came up with:
private List <string>DataWrapper(string data, string prefix, int maxLineSize, int numberOfLines, char buffer)
{
List <string> result = new List <string>();
int dataSize = maxLineSize - prefix.Length ;
int line = 0;
int counter = 0;
//If number of lines is less or equal to zero then just make it same size as the data
if (numberOfLines <= 0)
numberOfLines = data.Length;
if (String.IsNullOrEmpty(data))
result = null;
else if (maxLineSize <= 0)
result = null;
else
{
string temp = data;
while ((counter < data.Length) && (line < numberOfLines))
{
temp = data.Substring(counter);
if (temp.Length < maxLineSize)
{
result.Add(prefix + temp.PadRight(dataSize, buffer));
}
else
{
temp = (prefix + temp.PadRight(dataSize, buffer).Substring(0, dataSize));
result.Add(temp);
}
line++;
counter+=dataSize;
}
}
return result;
}
//Calling function:
string[] input = new string [5];
string prefix = "Lines:";
input[0] = String.Empty;
input[1] = "0123456789"; //10;
input[2] = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789012345678901234567890123456789"; //92
input[3] = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789012345678901234567890123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789012345678901234567890123456789"; //184
input[4] = "TO BE, OR NOT TO BE, THAT IS THE QUESTION: WHETHER 'TIS NOBLER IN THE MIND TO SUFFER THE SLINGS AND ARROWS OF OUTRAGEOUS FORTUNE, OR TO TAKE ARMS AGAINST A SEA OF TROUBLES, AND BY OPPOSING END THEM: TO DIE, TO SLEEP NO MORE; AND BY A SLEEP, TO SAY WE END THE HEART-ACHE, AND THE THOUSAND NATURAL SHOCKS THAT FLESH IS HEIR TO? 'TIS A CONSUMMATION DEVOUTLY TO BE WISHED. TO DIE TO SLEEP, TO SLEEP, PERCHANCE TO DREAM; AYE, THERE'S THE RUB, FOR IN THAT SLEEP OF DEATH, WHAT DREAMS MAY COME, WHEN WE HAVE SHUFFLED OFF THIS MORTAL COIL, MUST GIVE US PAUSE. THERE'S THE RESPECT THAT MAKES CALAMITY OF SO LONG LIFE: FOR WHO WOULD BEAR THE WHIPS AND SCORNS OF TIME, THE OPPRESSOR'S WRONG, THE PROUD MAN'S CONTUMELY, THE PANGS OF DESPISED LOVE, THE LAW’S DELAY, THE INSOLENCE OF OFFICE, AND THE SPURNS THAT PATIENT MERIT OF THE UNWORTHY TAKES, WHEN HE HIMSELF MIGHT HIS QUIETUS MAKE WITH A BARE BODKIN? WHO WOULD FARDELS BEAR, TO GRUNT AND SWEAT UNDER A WEARY LIFE, BUT THAT THE DREAD OF SOMETHING AFTER DEATH, THE UNDISCOVERED COUNTRY, FROM WHOSE BOURN NO TRAVELLER RETURNS, PUZZLES THE WILL, AND MAKES US RATHER BEAR THOSE ILLS WE HAVE, THAN FLY TO OTHERS THAT WE KNOW NOT OF. THUS CONSCIENCE DOES MAKE COWARDS OF US ALL, AND THUS THE NATIVE HUE OF RESOLUTION IS SICKLIED O'ER, WITH THE PALE CAST OF THOUGHT, AND ENTERPRISES OF GREAT PITH AND MOMENT, WITH THIS REGARD THEIR CURRENTS TURN AWRY, AND LOSE THE NAME OF ACTION. SOFT YOU NOW, THE FAIR OPHELIA? NYMPH, IN THY ORISONS BE ALL MY SINS REMEMBERED."; // 1495
foreach (string line in input)
{
string msg = line.ToUpper();
List<string> result= this.DataWrapper(msg, prefix, 80, 3, ' ');
if (results != null)
{
foreach (string result in results)
{
//Handle the result.
}
}
}0Add a comment
-
private DataTable ReadAsDataTable(string file, bool hasHeader, char seperator)
{
DataTable dt = new DataTable();
StreamReader reader = new StreamReader(file);
string line = string.Empty;
string[] data = null;
int counter = 0;
while (reader.Peek() != -1)
{
line = reader.ReadLine();
data = line.Split(seperator);
if (hasHeader)
{
if (counter == 0)
{
foreach (string item in data)
{
dt.Columns.Add(item);
}
}
else
dt.Rows.Add(data);
}
else
dt.Rows.Add(data);
counter++;
}
reader.Close();
return dt;
}
Calling function
DataTable result = ReadAsDataTable(@"C:\temp\data.csv", true, ',');0Add a comment
-
//A simple RegEx validotor.
private bool RegExValidator(string expression, string toMatch, RegexOptions options = RegexOptions.None)
{
bool isValid = true;
try
{
Regex validator = new Regex(expression, options);
isValid = validator.IsMatch(toMatch);
}
catch (Exception ex)
{
//error handler can go here;
isValid = false;
}
return isValid;
}
Calling method:
bool isValid = RegExValidator(@"^[-+]?[0-9]\d{0,11}(\.\d{1,2})?%?$", "1234", RegexOptions.None);
C# Regular Expressions Cheat Sheet
Cheat sheet for C# regular expressions metacharacters, operators, quantifiers etc
(Source: http://www.mikesdotnetting.com/Article/46/CSharp-Regular-Expressions-Cheat-Sheet)
Character Description
\ Marks the next character as either a special character or escapes a literal. For example, "n" matches the character "n". "\n" matches a newline character. The sequence "\\" matches "\" and "\(" matches "(".
Note: double quotes may be escaped by doubling them: ""
^ Depending on whether the MultiLine option is set, matches the position before the first character in a line, or the first character in the string.
$ Depending on whether the MultiLine option is set, matches the position after the last character in a line, or the last character in the string.
* Matches the preceding character zero or more times. For example, "zo*" matches either "z" or "zoo".
+ Matches the preceding character one or more times. For example, "zo+" matches "zoo" but not "z".
? Matches the preceding character zero or one time. For example, "a?ve?" matches the "ve" in "never".
. Matches any single character except a newline character.
(pattern) Matches pattern and remembers the match. The matched substring can be retrieved from the resulting Matches collection, using Item [0]...[n]. To match parentheses characters ( ), use "\(" or "\)".
(?pattern) Matches pattern and gives the match a name.
(?:pattern) A non-capturing group
(?=...) A positive lookahead
(?!...) A negative lookahead
(?<=...) A positive lookbehind .
(?<!...) A negative lookbehind .
x|y Matches either x or y.
For example, "z
wood" matches "z" or "wood". "(z
w)oo" matches "zoo" or "wood".
{n} n is a non-negative integer. Matches exactly n times. For example, "o{2}" does not match the "o" in "Bob," but matches the first two o's in "foooood".
{n,} n is a non-negative integer. Matches at least n times. For example, "o{2,}" does not match the "o" in "Bob" and matches all the o's in "foooood." "o{1,}" is equivalent to "o+". "o{0,}" is equivalent to "o*".
{n,m} m and n are non-negative integers. Matches at least n and at most m times. For example, "o{1,3}" matches the first three o's in "fooooood." "o{0,1}" is equivalent to "o?".
[xyz] A character set. Matches any one of the enclosed characters. For example, "[abc]" matches the "a" in "plain".
[^xyz] A negative character set. Matches any character not enclosed. For example, "[^abc]" matches the "p" in "plain".
[a-z] A range of characters. Matches any character in the specified range. For example, "[a-z]" matches any lowercase alphabetic character in the range "a" through "z".
[^m-z] A negative range characters. Matches any character not in the specified range. For example, "[m-z]" matches any character not in the range "m" through "z".
\b Matches a word boundary, that is, the position between a word and a space. For example, "er\b" matches the "er" in "never" but not the "er" in "verb".
\B Matches a non-word boundary. "ea*r\B" matches the "ear" in "never early".
\d Matches a digit character. Equivalent to [0-9].
\D Matches a non-digit character. Equivalent to [^0-9].
\f Matches a form-feed character.
\k A back-reference to a named group.
\n Matches a newline character.
\r Matches a carriage return character.
\s Matches any white space including space, tab, form-feed, etc. Equivalent to "[ \f\n\r\t\v]".
\S Matches any nonwhite space character. Equivalent to "[^ \f\n\r\t\v]".
\t Matches a tab character.
\v Matches a vertical tab character.
\w Matches any word character including underscore. Equivalent to "[A-Za-z0-9_]".
\W Matches any non-word character. Equivalent to "[^A-Za-z0-9_]".
\num Matches num, where num is a positive integer. A reference back to remembered matches. For example, "(.)\1" matches two consecutive identical characters.
\n Matches n, where n is an octal escape value. Octal escape values must be 1, 2, or 3 digits long. For example, "\11" and "\011" both match a tab character. "\0011" is the equivalent of "\001" & "1". Octal escape values must not exceed 256. If they do, only the first two digits comprise the expression. Allows ASCII codes to be used in regular expressions.
\xn Matches n, where n is a hexadecimal escape value. Hexadecimal escape values must be exactly two digits long. For example, "\x41" matches "A". "\x041" is equivalent to "\x04" & "1". Allows ASCII codes to be used in regular expressions.
\un Matches a Unicode character expressed in hexadecimal notation with exactly four numeric digits. "\u0200" matches a space character.
\A Matches the position before the first character in a string. Not affected by the MultiLine setting
\Z Matches the position after the last character of a string. Not affected by the MultiLine setting.
\G Specifies that the matches must be consecutive, without any intervening non-matching characters.
0Add a comment
-
private DateTime GetDate(DateTime inputDate, string differenceType, bool add, int difference)
{
DateTime result = inputDate;
if (!add)
{
//Substraction
difference = -1 * difference;
}
switch (differenceType.ToUpper())
{
case "DAYS":
result = new DateTime(inputDate.Year, inputDate.Month, inputDate.Day).AddDays(difference);
break;
case "MONTHS":
result = new DateTime(inputDate.Year, inputDate.Month, inputDate.Day).AddMonths(difference);
break;
case "YEARS":
result = new DateTime(inputDate.Year, inputDate.Month, inputDate.Day).AddYears(difference);
break;
}
return result;
}
Calling function:
DateTime result = GetDate(DateTime.Now, "DAYS", false, 15);0Add a comment
-
private void AddImageToThumbnail(string[] images)
{
int top = 0;
int ht = 150;
int counter = 0;
PictureBox[] pictures = new PictureBox[images.Length];
if (images.Length > 0)
{
//The panel pnlThumbnail will hold thumbnail pictures.
pnlThumbnail.AutoScroll = true;
foreach (string image in images)
{
try
{
pictures[counter] = new PictureBox();
pictures[counter].Parent = pnlThumbnail;
pictures[counter].Top = top;
pictures[counter].Height = ht;
pictures[counter].Width = pnlThumbnail.Width - 10;
pictures[counter].Image = new Bitmap(image);
pictures[counter].BorderStyle = BorderStyle.FixedSingle;
top += pictures[counter].Size.Height + 5;
pictures[counter].SizeMode = PictureBoxSizeMode.StretchImage;
pictures[counter].Show();
//If user clicks on a picture then this event gets triggered, it can be used to display the current image.
pictures[counter].Click += new EventHandler(fImage_Click);
pictures[counter].ImageLocation = image;
counter++;
textThumbnailCount.Text = counter.ToString();
}
catch
{
//Error handler
}
}
//Show the first image in the thumbnail
//fImage_Click(pictures[0], null);
}
}
Calling function:
string[] images = Directory.GetFiles(@"C:\Temp\Images\", "*.tif");
AddImageToThumbnail(images);0Add a comment
View comments