Victor's Blog

Live as if you were to die tomorrow. Learn as if you were to live forever


How to use Aspnet_regsql to create Application Services Database?

Aspnet_regsql.exe is a tool for installing the SQL Server database used by the SQL Server providers. The file is located in the "c:\WINDOWS\Microsoft.NET\Framework\versionNumber folder" on your web server. There are too ways to run this tool: the wizard form and the command line.

1. To run this using wizard is very simple. 
Step 1: Run the aspnet_regsql.exe from your command line without any arguments. Then the wizard windows form will appear.  Click the Next Button.

Step 1

Step 2: Choose what you want to do with the sql database. In this example we want to create a new application services database. So we just select the first option then Click the Next Button.

Step 2

Step 3: Enter your sql server name, authentication method and the database name. If the database has already existed, you can select the dropdownlist. If the database is new, you only need to type in the new database name in the dropdownlist. Click the Next Button.

Step 3

Step 4: Confirm the information then then Click the Next Button.

Step 4

Step 5: Done. 

Step 5

After that your database should be created. And it should look likes this.

sql server view

2. Also you can run the tool as a command-line utility. The options for the command are

Option Description
-? Prints Aspnet_regsql.exe tool Help text in the command window.
-W Runs the tool in wizard mode. This is the default if no command line arguments are specified.
-C connection string The connection string to the computer running SQL Server where the database will be installed, or is already installed. This option is not necessary if you only specify the server (-S) and login (-U and -P, or -E) information.
-S server The name of the computer running SQL Server where the database will be installed, or is already installed. The server name can also include an instance name, such as .\INSTANCENAME.
-U login id The SQL Server user id to log in with. This option also requires the password (-P) option. This option is not necessary if you are authenticating using Windows credentials (-E).
-P password The SQL Server password to log in with. This option also requires the login id (-U) option. This option is not necessary if authenticating using Windows credentials (-E).
-E Authenticates using the Windows credentials of the currently logged-in user.
-d database The name of the database to create or modify. If the database is not specified, the default database name of "aspnetdb" is used.
-sqlexportonly filename Generates a SQL script file that can be used to add or remove the specified features. The specified actions are not performed.
-A all|m|r|p|c|w Adds support for one or more features. The following identifiers are used for ASP.NET features.
all All features
m Membership
r Role management
p Profile
c Web Parts personalization
w Web events
Feature identifiers can be specified together or separately, as shown in the following examples. aspnet_regsql.exe -E -S localhost -A mp aspnet_regsql.exe -E -S localhost -A m -A p
-R all|m|r|p|c|w Removes support for one or more features. The identifiers are the same for the add option.. Here is some examples. aspnet_regsql.exe -E -S localhost -R mp aspnet_regsql.exe -E -S localhost -R m -R p
-Q Runs the tool in quiet mode and does not confirm before removing a feature.

So if you want to add database elements for membership and role management in database test on localhost using the current windows user. The command should be:

aspnet_regsql.exe -E -S localhost -d test -A mr

DateTime and String


Convert String to DateTime

1. User CultureInfo class to parse the DateTime string

CultureInfo cultEnUs = new CultureInfo("en-US");
DateTime myDate = Convert.ToDateTime("03/22/2010", cultEnUs.DateTimeFormat);

2. User the DateTimeFormatInfo class to parse the DateTime string

string strDate = "21-07-2006";
DateTimeFormatInfo dtfi = new DateTimeFormatInfo();
dtfi.ShortDatePattern = "dd-MM-yyyy";
dtfi.DateSeparator = "-";
DateTime objDate = Convert.ToDateTime(strDate, dtfi);


Convert DateTime to String

1. Use the DateTime.ToString Method.
This is the easiest way to convert DateTime to a string.



2. User the String.Format Method.

There are following custom format specifies 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 specifies rewritten to the output.

// 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" sec.fraction 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 Separator : (colon). These characters will be rewritten to characters defined in the current DateTimeFormatInfo.DateSeparator and DateTimeFormatInfo.TimeSeparator.

// 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:

// 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.

Here are patterns defined in DateTimeFormatInfo and their values for en-US culture. First column contains format specifies for the String.Format method. (*) = culture independent

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 SortableDateTi mePattern yyyy'-'MM'-'dd'T'HH':'mm':'ss (*)
u UniversalSorta bleDateTimePat tern yyyy'-'MM'-'dd HH':'mm':'ss'Z' (*)

Following examples show usage of standard format specifiers in String.Format method and the resulting output.

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


How to do bulk deletion in SharePoint?

How to bulk delete in SharePoint?

The normal way to delete file programmatically in SharePoint is using the SharePoint API. The code looks like:

foreach(SPList myList in myWeb)
  foreach( SPListItem item in myList )

This works fine for document libraries contain small amount of data. It will be extremely slow when you have document libraries contain millions of records. In this case, you will need bulk deletion.

In order to use bulk deletion you need to know the CAML and how to use SPWeb.ProcessBatchData() methods.
There is the example.

private static String BuildBatchDeleteCommand( SPList list, SPListItemCollection coll )
  StringBuilder sbDelete = new StringBuilder();
  sbDelete.Append( "<?xml version=\"1.0\" encoding=\"UTF-8\"?><Batch>" );
  // We prepare a String.Format with a String.Format, this is why we have a {{0}}
  string command = String.Format( "<Method><SetList Scope=\"Request\">{0}</SetList><SetVar Name=\"ID\">{{0}}</SetVar><SetVar Name=\"Cmd\">Delete</SetVar><SetVar Name=\"owsfileref\">{{1}}</SetVar></Method>", list.ID );
  foreach ( SPListItem item in coll )
    sbDelete.Append( string.Format( command, item.ID.ToString(), item.File.ServerRelativeUrl ) );
  sbDelete.Append( "</Batch>" );
  return sbDelete.ToString();

// While there's something left
while ( list.ItemCount > 0 )
  // We get everything but we limit the result to 100 rows
  SPQuery q = new SPQuery();
  q.RowLimit = 100;
  // We get the results
  SPListItemCollection coll = list.GetItems( q );
  // We process a CAML batch
  String batch = BuildBatchDeleteCommand( list, coll );
  // We execute it
  web.ProcessBatchData( batch );

BTW the methods returns a String, if your code doesn’t work, it would return you an error code. If everything is fine, it just return "0x00000000".