http://www.dotnetspider.com/resources/Category528.aspx
http://viratsarswat.blogspot.com/2009/04/group-by-on-dataset-using-dataview.html?zx=9fee7d41e2a09e38
------------------------------------------------------
Validate Email Using Java Script
-----------------------------------
function validate()
{
//Validate Email.....
validRegExp = /^([a-zA-Z0-9_\.\-])+\@(([a-zA-Z0-9\-])+\.)+([a-zA-Z0-9]{2,4})+$/i;
strEmail = document.getElementById("txtEmail").value;
if (strEmail.search(validRegExp) == -1)
{
alert(" A valid E-mail is required");
document.getElementById("txtEmail").focus();
return false;
}
}
______________________________________________________________________
How to Validate Indian Phone Number
----------------------------------
Concepts Used:
1. Namespace: System.Text.RegularExpressions for pattern creation and matching.
2. KeyPress event of TextBox to check characters by KeyChar property.
3. Calling event handler of Check button from Textbox KeyPress event handler.
Code Snippet:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Text.RegularExpressions; //namespace to be used to match patterns
namespace dsn_PhoneNumberValidation
{
public partial class Form1 : Form
{
//Defining a regular expression and pattern
Regex phoneRegex = new Regex("^\\+[9][1][-][\\d]{10}$");
public Form1()
{
InitializeComponent();
}
private void btnCheck_Click(object sender, EventArgs e)
{
string strPhone = txtPhone.Text;
Match getMatch = phoneRegex.Match(strPhone);
if (getMatch.Success)
{
lblInfo.ForeColor = System.Drawing.Color.Blue;
lblInfo.Text = "MESSAGE: Absolutely correct phone number!";
}
else
{
lblInfo.ForeColor = System.Drawing.Color.Red;
lblInfo.Text = "MESSAGE: Wrong phone number.Phone number \nshould be 10 digit and format as +91-1234567890.";
}
}
private void txtPhone_KeyPress(object sender, KeyPressEventArgs e)
{
if (txtPhone.Text != String.Empty)
{
//KeyChar(45) for hypen key
//KeyChar(13) for Enter key
//KeyChar(8) for Backspace key
if ((e.KeyChar <> 57) && e.KeyChar != 8 && e.KeyChar != 45 && e.KeyChar!=13)
{
lblInfo.ForeColor = System.Drawing.Color.Red;
lblInfo.Text = "ONLY DIGITS ALLOWED. LETTERS NOT PERMITTED!";
}
else
{
lblInfo.Text=String.Empty;
}
}
//Calling btnCheck_Click event handler on Enter key pressed
if (e.KeyChar == 13)
{
btnCheck_Click(this, EventArgs.Empty);
}
}
}
}
Regular Expression Pattern Explained:
^\\+[9][1][-][\\d]{10}$
^ - Front anchor
\\+ - For checking + in begining of phone number
[9] - Next number should be 9 only
[1] - Next number should be 1 only
[\\d] - Later there should be digits only
{10} - Digits should be ten in numbers
$ - Ending anchor
___________________________________
Date Validation using Regular Expression
----------------------------------------
Display="None">
_______________________________
/* Open Page in New Windowin C# */
OnClick="btnNewEntry_Click" OnClientClick="aspnetForm.target ='_blank';"/>
protected void btnNewEntry_Click(object sender, EventArgs e)
{
Response.Redirect("New.aspx");
}
___________________________________
/* Send Email */
http://www.aspsnippets.com/Articles/Send-SMTP-Emails-using-System.Net-Class-in-C.aspx
______________________________________________________
/* Generate Random Numer with a certain range */
Random RandomClass = new Random()
int RandomNumber = RandomClass.Next(4, 14);
________________________________________________________
/* String Format For time */
String Format for DateTime [C#]
This example shows how to format DateTime using String.Format method. All formatting can be done also using DateTime.ToString method.
Custom DateTime Formatting
There are following custom format specifiers y (year), M (month), d (day), h (hour 12), H (hour 24), m (minute), s (second), f (second fraction), F (second fraction, trailing zeroes are trimmed), t (P.M or A.M) and z (time zone).
Following examples demonstrate how are the format specifiers rewritten to the output.
[C#]
// create date time 2008-03-09 16:05:07.123
DateTime dt = new DateTime(2008, 3, 9, 16, 5, 7, 123);
String.Format("{0:y yy yyy yyyy}", dt); // "8 08 008 2008" year
String.Format("{0:M MM MMM MMMM}", dt); // "3 03 Mar March" month
String.Format("{0:d dd ddd dddd}", dt); // "9 09 Sun Sunday" day
String.Format("{0:h hh H HH}", dt); // "4 04 16 16" hour 12/24
String.Format("{0:m mm}", dt); // "5 05" minute
String.Format("{0:s ss}", dt); // "7 07" second
String.Format("{0:f ff fff ffff}", dt); // "1 12 123 1230" sec.fraction
String.Format("{0:F FF FFF FFFF}", dt); // "1 12 123 123" without zeroes
String.Format("{0:t tt}", dt); // "P PM" A.M. or P.M.
String.Format("{0:z zz zzz}", dt); // "-6 -06 -06:00" time zone
You can use also date separator / (slash) and time sepatator : (colon). These characters will be rewritten to characters defined in the current DateTimeFormatInfo.DateSeparator and DateTimeFormatInfo.TimeSeparator.
[C#]
// date separator in german culture is "." (so "/" changes to ".")
String.Format("{0:d/M/yyyy HH:mm:ss}", dt); // "9/3/2008 16:05:07" - english (en-US)
String.Format("{0:d/M/yyyy HH:mm:ss}", dt); // "9.3.2008 16:05:07" - german (de-DE)
Here are some examples of custom date and time formatting:
[C#]
// month/day numbers without/with leading zeroes
String.Format("{0:M/d/yyyy}", dt); // "3/9/2008"
String.Format("{0:MM/dd/yyyy}", dt); // "03/09/2008"
// day/month names
String.Format("{0:ddd, MMM d, yyyy}", dt); // "Sun, Mar 9, 2008"
String.Format("{0:dddd, MMMM d, yyyy}", dt); // "Sunday, March 9, 2008"
// two/four digit year
String.Format("{0:MM/dd/yy}", dt); // "03/09/08"
String.Format("{0:MM/dd/yyyy}", dt); // "03/09/2008"
Standard DateTime Formatting
In DateTimeFormatInfo there are defined standard patterns for the current culture. For example property ShortTimePattern is string that contains value h:mm tt for en-US culture and value HH:mm for de-DE culture.
Following table shows patterns defined in DateTimeFormatInfo and their values for en-US culture. First column contains format specifiers for the String.Format method.
Specifier DateTimeFormatInfo property Pattern value (for en-US culture)
t ShortTimePattern h:mm tt
d ShortDatePattern M/d/yyyy
T LongTimePattern h:mm:ss tt
D LongDatePattern dddd, MMMM dd, yyyy
f (combination of D and t) dddd, MMMM dd, yyyy h:mm tt
F FullDateTimePattern dddd, MMMM dd, yyyy h:mm:ss tt
g (combination of d and t) M/d/yyyy h:mm tt
G (combination of d and T) M/d/yyyy h:mm:ss tt
m, M MonthDayPattern MMMM dd
y, Y YearMonthPattern MMMM, yyyy
r, R RFC1123Pattern ddd, dd MMM yyyy HH':'mm':'ss 'GMT' (*)
s SortableDateTimePattern yyyy'-'MM'-'dd'T'HH':'mm':'ss (*)
u UniversalSortableDateTimePattern yyyy'-'MM'-'dd HH':'mm':'ss'Z' (*)
(*) = culture independent
Following examples show usage of standard format specifiers in String.Format method and the resulting output.
[C#]
String.Format("{0:t}", dt); // "4:05 PM" ShortTime
String.Format("{0:d}", dt); // "3/9/2008" ShortDate
String.Format("{0:T}", dt); // "4:05:07 PM" LongTime
String.Format("{0:D}", dt); // "Sunday, March 09, 2008" LongDate
String.Format("{0:f}", dt); // "Sunday, March 09, 2008 4:05 PM" LongDate+ShortTime
String.Format("{0:F}", dt); // "Sunday, March 09, 2008 4:05:07 PM" FullDateTime
String.Format("{0:g}", dt); // "3/9/2008 4:05 PM" ShortDate+ShortTime
String.Format("{0:G}", dt); // "3/9/2008 4:05:07 PM" ShortDate+LongTime
String.Format("{0:m}", dt); // "March 09" MonthDay
String.Format("{0:y}", dt); // "March, 2008" YearMonth
String.Format("{0:r}", dt); // "Sun, 09 Mar 2008 16:05:07 GMT" RFC1123
String.Format("{0:s}", dt); // "2008-03-09T16:05:07" SortableDateTime
String.Format("{0:u}", dt); // "2008-03-09 16:05:07Z" UniversalSortableDateTime
_______________________________________________________________________________
/* String Format for Int */
String Format for Int [C#]
Integer numbers can be formatted in .NET in many ways. You can use static method String.Format or instance method int.ToString. Following examples shows how to align numbers (with spaces or zeroes), how to format negative numbers or how to do custom formatting like phone numbers.
Add zeroes before number
To add zeroes before a number, use colon separator „:“ and write as many zeroes as you want.
[C#]
String.Format("{0:00000}", 15); // "00015"
String.Format("{0:00000}", -15); // "-00015"
Align number to the right or left
To align number to the right, use comma „,“ followed by a number of characters. This alignment option must be before the colon separator.
[C#]
String.Format("{0,5}", 15); // " 15"
String.Format("{0,-5}", 15); // "15 "
String.Format("{0,5:000}", 15); // " 015"
String.Format("{0,-5:000}", 15); // "015 "
Different formatting for negative numbers and zero
You can have special format for negative numbers and zero. Use semicolon separator „;“ to separate formatting to two or three sections. The second section is format for negative numbers, the third section is for zero.
[C#]
String.Format("{0:#;minus #}", 15); // "15"
String.Format("{0:#;minus #}", -15); // "minus 15"
String.Format("{0:#;minus #;zero}", 0); // "zero"
Custom number formatting (e.g. phone number)
Numbers can be formatted also to any custom format, e.g. like phone numbers or serial numbers.
[C#]
String.Format("{0:+### ### ### ###}", 447900123456); // "+447 900 123 456"
String.Format("{0:##-####-####}", 8958712551); // "89-5871-2551"
_______________________________________________________________
/* String Format for Double */
String Format for Double [C#]
The following examples show how to format float numbers to string in C#. You can use static method String.Format or instance methods double.ToString and float.ToString.
Digits after decimal point
This example formats double to string with fixed number of decimal places. For two decimal places use pattern „0.00“. If a float number has less decimal places, the rest digits on the right will be zeroes. If it has more decimal places, the number will be rounded.
[C#]
// just two decimal places
String.Format("{0:0.00}", 123.4567); // "123.46"
String.Format("{0:0.00}", 123.4); // "123.40"
String.Format("{0:0.00}", 123.0); // "123.00"
Next example formats double to string with floating number of decimal places. E.g. for maximal two decimal places use pattern „0.##“.
[C#]
// max. two decimal places
String.Format("{0:0.##}", 123.4567); // "123.46"
String.Format("{0:0.##}", 123.4); // "123.4"
String.Format("{0:0.##}", 123.0); // "123"
Digits before decimal point
If you want a float number to have any minimal number of digits before decimal point use N-times zero before decimal point. E.g. pattern „00.0“ formats a float number to string with at least two digits before decimal point and one digit after that.
[C#]
// at least two digits before decimal point
String.Format("{0:00.0}", 123.4567); // "123.5"
String.Format("{0:00.0}", 23.4567); // "23.5"
String.Format("{0:00.0}", 3.4567); // "03.5"
String.Format("{0:00.0}", -3.4567); // "-03.5"
Thousands separator
To format double to string with use of thousands separator use zero and comma separator before an usual float formatting pattern, e.g. pattern „0,0.0“ formats the number to use thousands separators and to have one decimal place.
[C#]
String.Format("{0:0,0.0}", 12345.67); // "12,345.7"
String.Format("{0:0,0}", 12345.67); // "12,346"
Zero
Float numbers between zero and one can be formatted in two ways, with or without leading zero before decimal point. To format number without a leading zero use # before point. For example „#.0“ formats number to have one decimal place and zero to N digits before decimal point (e.g. „.5“ or „123.5“).
Following code shows how can be formatted a zero (of double type).
[C#]
String.Format("{0:0.0}", 0.0); // "0.0"
String.Format("{0:0.#}", 0.0); // "0"
String.Format("{0:#.0}", 0.0); // ".0"
String.Format("{0:#.#}", 0.0); // ""
Align numbers with spaces
To align float number to the right use comma „,“ option before the colon. Type comma followed by a number of spaces, e.g. „0,10:0.0“ (this can be used only in String.Format method, not in double.ToString method). To align numbers to the left use negative number of spaces.
[C#]
String.Format("{0,10:0.0}", 123.4567); // " 123.5"
String.Format("{0,-10:0.0}", 123.4567); // "123.5 "
String.Format("{0,10:0.0}", -123.4567); // " -123.5"
String.Format("{0,-10:0.0}", -123.4567); // "-123.5 "
Custom formatting for negative numbers and zero
If you need to use custom format for negative float numbers or zero, use semicolon separator „;“ to split pattern to three sections. The first section formats positive numbers, the second section formats negative numbers and the third section formats zero. If you omit the last section, zero will be formatted using the first section.
[C#]
String.Format("{0:0.00;minus 0.00;zero}", 123.4567); // "123.46"
String.Format("{0:0.00;minus 0.00;zero}", -123.4567); // "minus 123.46"
String.Format("{0:0.00;minus 0.00;zero}", 0.0); // "zero"
Some funny examples
As you could notice in the previous example, you can put any text into formatting pattern, e.g. before an usual pattern „my text 0.0“. You can even put any text between the zeroes, e.g. „0aaa.bbb0“.
[C#]
String.Format("{0:my number is 0.0}", 12.3); // "my number is 12.3"
String.Format("{0:0aaa.bbb0}", 12.3); // "12aaa.bbb3"
_________________________________________________________________
/* Indent String with Spaces */
Indent String with Spaces [C#]
This example shows how to indent strings using method for padding in C#. To repeat spaces use method String.PadLeft. If you call „hello“.PadLeft(10) you will get the string aligned to the right: „ hello“. If you use empty string instead of the „hello“ string the result will be 10× repeated space character. This can be used to create simple Indent method.
The Indent method:
[C#]
public static string Indent(int count)
{
return "".PadLeft(count);
}
Test code:
[C#]
Console.WriteLine(Indent(0) + "List");
Console.WriteLine(Indent(3) + "Item 1");
Console.WriteLine(Indent(6) + "Item 1.1");
Console.WriteLine(Indent(6) + "Item 1.2");
Console.WriteLine(Indent(3) + "Item 2");
Console.WriteLine(Indent(6) + "Item 2.1");
Output string:
List
Item 1
Item 1.1
Item 1.2
Item 2
Item 2.1
__________________________________________________________________
/* Sorting Arrays */
Sorting Arrays [C#]
This example shows how to sort arrays in C#. Array can be sorted using static method Array.Sort which internally use Quicksort algorithm.
Sorting array of primitive types
To sort array of primitive types such as int, double or string use method Array.Sort(Array) with the array as a paramater. The primitive types implements interface IComparable, which is internally used by the Sort method (it calls IComparable.CompareTo method). See example how to sort int array:
[C#]
// sort int array
int[] intArray = new int[5] { 8, 10, 2, 6, 3 };
Array.Sort(intArray);
// write array
foreach (int i in intArray) Console.Write(i + " "); // output: 2 3 6 8 10
or how to sort string array:
[C#]
// sort string array
string[] stringArray = new string[5] { "X", "B", "Z", "Y", "A" };
Array.Sort(stringArray);
// write array
foreach (string str in stringArray) Console.Write(str + " "); // output: A B X Y Z
Sorting array of custom type using delegate
To sort your own types or to sort by more sophisticated rules, you can use delegate to anonymous method. The generic delegate Comparison
Following example demonstrates how to sort an array of custom type using the delegate to anonynous comparison method. The custom type in this case is a class User with properties Name and Age.
[C#]
// array of custom type
User[] users = new User[3] { new User("Betty", 23), // name, age
new User("Susan", 20),
new User("Lisa", 25) };
[C#]
// sort array by name
Array.Sort(users, delegate(User user1, User user2) {
return user1.Name.CompareTo(user2.Name);
});
// write array (output: Betty23 Lisa25 Susan20)
foreach (User user in users) Console.Write(user.Name + user.Age + " ");
[C#]
// sort array by age
Array.Sort(users, delegate(User user1, User user2) {
return user1.Age.CompareTo(user2.Age); // (user1.Age - user2.Age)
});
// write array (output: Susan20 Betty23 Lisa25)
foreach (User user in users) Console.Write(user.Name + user.Age + " ");
Sorting array using IComparable
If you implement IComparable interface in your custom type, you can sort array easily like in the case of primitive types. The Sort method calls internally IComparable.CompareTo method.
[C#]
// custom type
public class User : IComparable
{
// ...
// implement IComparable interface
public int CompareTo(object obj)
{
if (obj is User) {
return this.Name.CompareTo((obj as User).Name); // compare user names
}
throw new ArgumentException("Object is not a User");
}
}
Use it as you sorted the primitive types in the previous examples.
[C#]
// sort using IComparable implemented by User class
Array.Sort(users); // sort array of User objects
___________________________________________________________
/* youtube downloader */
http://keepvid.com/
_______________________________
/* Download from Web */
This example shows how to download files from any website to local disk. The simply way how to download file is to use WebClient class and its method DownloadFile. This method has two parameters, first is the url of the file you want to download and the second parameter is path to local disk to which you want to save the file.
Download File Synchronously
The following code shows how to download file synchronously. This method blocks the main thread until the file is downloaded or an error occur (in this case the WebException is thrown).
[C#]
using System.Net;
WebClient webClient = new WebClient();
webClient.DownloadFile("http://mysite.com/myfile.txt", @"c:\myfile.txt");
Download File Asynchronously
To download file without blocking the main thread use asynchronous method DownloadFileAsync. You can also set event handlers to show progress and to detect that the file is downloaded.
[C#]
private void btnDownload_Click(object sender, EventArgs e)
{
WebClient webClient = new WebClient();
webClient.DownloadFileCompleted += new AsyncCompletedEventHandler(Completed);
webClient.DownloadProgressChanged += new DownloadProgressChangedEventHandler(ProgressChanged);
webClient.DownloadFileAsync(new Uri("http://mysite.com/myfile.txt"), @"c:\myfile.txt");
}
private void ProgressChanged(object sender, DownloadProgressChangedEventArgs e)
{
progressBar.Value = e.ProgressPercentage;
}
private void Completed(object sender, AsyncCompletedEventArgs e)
{
MessageBox.Show("Download completed!");
}
________________________________________________________
/* Distinct in Datatable */
DataTable distinctTable = originalTable.DefaultView.ToTable( /*distinct*/ true);
__________________________________
/* Take Sql Backup from code behind */
http://www.codeproject.com/KB/database/SQL_Server_2005_Database.aspx
__________________________________
/* Webservice Calling */
http://www.codeguru.com/csharp/csharp/cs_webservices/tutorials/article.php/c5477
http://www.west-wind.com/presentations/dotnetwebservices/DotNetWebServices.asp
________________________________
/* Group by in data table */
http://viratsarswat.blogspot.com/2009/04/group-by-on-dataset-using-dataview.html?zx=9fee7d41e2a09e38
http://arstechnica.com/civis/viewtopic.php?f=20&t=250424
___________________________________________________
/* Search a domain name */
in google : who is lookup
_____________________________________
/* Datatable Datarow */
DataTable dtDest = new DataTable();
dtDest = dsActivity.Tables[0].Clone();
foreach(DataRow dr in dsSrc.Tables[0].Rows)
{
DataRow newRow = dtDest .NewRow();
newRow.ItemArray = dr.ItemArray;
dtDest.Rows.Add(newRow);
}
________________________________
DataTable dt1 = ds.Tables[0];
DataTable dt2 = new DataTable();
dt2 = dt1.Clone();
foreach(DataRow row in dt1.Rows){
if(row["Column1"] == 10){
// Import the Row into dt2 from dt1
dt2.ImportRow(row);
}
}
________________________________________
http://mcablindia.com/
/********* Chart using asp.net ********/
http://www.codeproject.com/KB/aspnet/Creating3DBarChart.aspx
http://www.asp101.com/articles/jayram/exceldotnet/default.asp
http://wiki.asp.net/page.aspx/685/export-image-to-excel-using-c/
http://www.spreadsheetgear.com/support/samples/charting.aspx
VVI
http://www.411asp.net/home/tutorial/howto/graphics/charts
http://www.codeproject.com/KB/aspnet/Creating3DBarChart.aspx
____________________________________________________________________
http://www.adrive.com/home/downloadfile/314788597
____________________________________________________________
Target Blank
-------------------
___________________________________________________________________________________________--
/********** validation false with a particular button ********/
causevalidation=false //in button
___________________________________________________________________________
/* Calender */
SetFocusOnError="True" Display="None" ControlToValidate="txtDateFrom">
________________________________________________________________________________
/* List Search Extender */
___________________________________________________________________________________________
/* Data table */
http://msdn.microsoft.com/en-us/library/y06xa2h1(VS.80).aspx
______________________________________________________________________________
/* Erp Reports Sale Purchase Based */
http://software.informer.com/getfree-excel-stock-report-format/
http://www.eresourceerp.com/eresourceerpweb/UI/ProductsByCategory.aspx?Category=9
http://www.exinfm.com/free_spreadsheets.html
__________________________________________________
Payroll excel (.xls)
----------------
http://www.citehr.com/128383-complete-payroll-administration.html
________________________________________________________________________
/* Web Service Video */
http://www.asp.net/learn/videos/video-280.aspx
http://download.microsoft.com/download/3/3/d/33d335e7-6a4f-4d6e-91f7-1ccf13dc331a/WinVideo-ASP-SimpleWebService.wmv
_____________________________________________________________________
SELECT distinct TOP (100) PERCENT L1, L1Pos, L2, L2Pos, L3, L3Pos, L4, AcctId, ValueDr, ValueCr, CASE WHEN (ValueDr - ValueCr) > 0 THEN (ValueDr - ValueCr)
ELSE 0 END AS NetValueDr, CASE WHEN (ValueDr - ValueCr) < 0 THEN (ValueDr - ValueCr) * - 1 ELSE 0 END AS NetValueCr
FROM (SELECT TOP (100) PERCENT 'Sundry Creditors' AS L1, 2 AS L1Pos, 'Sundry Creditors' AS L2, 1 AS L2Pos, 'Sundry Creditors' AS L3, 1 AS L3Pos,
dbo.AcctMaster.AcctAlias AS L4, dbo.AcctMaster.AcctId, CASE WHEN
(SELECT SUM(LedAmount) AS Expr1
FROM dbo.Ledger AS Ledger_1
WHERE (LedDrCr = 'true') AND (AcctId = dbo.AcctMaster.AcctId) AND cast(dbo.Ledger.LedDate as datetime) >= convert(varchar,'1/4/2010',103)) IS NULL THEN 0 ELSE
(SELECT SUM(LedAmount) AS Expr1
FROM dbo.Ledger AS Ledger_1
WHERE (LedDrCr = 'true') AND (AcctId = dbo.AcctMaster.AcctId) AND cast(dbo.Ledger.LedDate as datetime) >= convert(varchar,'1/4/2010',103)) END AS ValueDr,
CASE WHEN
(SELECT SUM(LedAmount) AS Expr1
FROM dbo.Ledger AS Ledger_1
WHERE (LedDrCr = 'false') AND (AcctId = dbo.AcctMaster.AcctId) AND cast(dbo.Ledger.LedDate as datetime) >= convert(varchar,'1/4/2010',103)) IS NULL THEN 0 ELSE
(SELECT SUM(LedAmount) AS Expr1
FROM dbo.Ledger AS Ledger_1
WHERE (LedDrCr = 'false') AND (AcctId = dbo.AcctMaster.AcctId) AND cast(dbo.Ledger.LedDate as datetime) >= convert(varchar,'1/4/2010',103)) END AS ValueCr
FROM dbo.Ledger INNER JOIN
dbo.AcctMaster ON dbo.Ledger.AcctId = dbo.AcctMaster.AcctId
WHERE (dbo.AcctMaster.AcctType = 'SC') AND (dbo.AcctMaster.AcctGroup = 2) AND (cast(dbo.Ledger.LedDate as datetime) BETWEEN convert(varchar,'1/4/2010',103) AND convert(varchar,'1/4/2010',103))
GROUP BY dbo.AcctMaster.AcctAlias, dbo.AcctMaster.AcctId, dbo.Ledger.LedDate) AS QSC
_________________________________________________________________________
/* Group sum in datatable*/
public static DataTable GroupBy(DataTable table, string[] aggregate_columns, string[] aggregate_functions, Type[] column_types, string[] group_by_columns)
{
DataView view = new DataView(table);
DataTable grouped = view.ToTable(true, group_by_columns);
for (int i = 0 ; i < aggregate_columns.Length ; i++)
{
grouped.Columns.Add(aggregate_columns[i], column_types[i]);
}
foreach (DataRow row in grouped.Rows)
{
List
for (int i = 0; i < group_by_columns.Length; i++)
{
filter_parts.Add(string.Format("[{0}] = '{1}'", group_by_columns[i], row[group_by_columns[i]].ToString().Replace("'", "''")));
}
string filter = string.Join(" AND ", filter_parts.ToArray());
for (int i = 0; i < aggregate_columns.Length; i++)
{
row[aggregate_columns[i]] = table.Compute(aggregate_functions[i], filter);
}
}
return grouped;
}
And you should use something like
string[] column_names = new string[] { "number", "avg_price", "total" };
string[] column_functions = new string[] { "count(products)", "avg(price)", "sum(charge)" };
Type[] column_types = new Type[] { typeof(int), typeof(decimal), typeof(decimal) };
string[] group_by = new string[] { "order_id" };
DataTable grouped = GroupBy(data, column_names, column_functions, column_types, group_by);
Which will be the equivilant of the SQL command:
select order_id, count(products) as number, avg(price) as avg_price, sum(charge) as total from data group by order_id
it may need some extra handling to account for DBNull values in the columns that you are grouping by.
_________________________________________________________-
/* Sql injection */
username: any
password: hi' or 1=1--
___________________________________
/* Last Entered Row in sqlserver */
Select * from table_name where id=IDENT_CURRENT('table_name')
0 comments:
Post a Comment