Tag: csharp
Connectionstrings.
by mysticslayer on Apr.02, 2008, under Programming
Well I know that for VS 2005 or VS 2008 can maintain your web or app configuration file with alot of easy. Well adding shouldn’t be a harsh, but updating still is a somekind of problem. Allright, normally you have a static connectionstring for your (web)application. Well the company that I work for has an application that isn’t static at all.
Everything is dynamic, even the connectionstring. You know why? Well the application that someone made was used for multiple databases. And it is used for importing data. You can change the connectionstring by editing the web or app config. But that wasn’t an option. The people that use this application should change the connectionstring by the application itself.
Allright, the fellow that created this nice application wasn’t aware of the connectionstring configsection. So he didn’t used that part, but I said, well if you change a connectionstring, you can maintain it by ease with code. So I had to write two simple functions. Adding and updating of connectionstrings.
The first part of the code is adding the connectionstring. Well I used this code the Windows Application so I didn’t include any for web applications.
Well opening of the configuration file is used by the configurationmanager. The ConfigurationManager.OpenExeConfiguration is used for opening the app.config, with ConfigurationUserLevel none. So no user level is needed.
Then you take out the ConnectionString section.
-
/// Add a connection string to the connection
-
/// strings section and store it in the
-
/// configuration file.
-
/// </summary>
-
/// <param name="csName">The name of the property.</param>
-
/// <param name="connectionString">The connectionstring as specified.</param>
-
public static void AddConnectionStrings(string csName, string connectionString)
-
{
-
-
// Get the configuration file.
-
System.Configuration.Configuration config =
-
ConfigurationManager.OpenExeConfiguration(
-
ConfigurationUserLevel.None);
-
-
// Add the connection string.
-
ConnectionStringsSection csSection = config.ConnectionStrings;
-
csSection.ConnectionStrings.Add(
-
new ConnectionStringSettings(csName,
-
connectionString, "System.Data.SqlClient"));
-
-
// Save the configuration file.
-
config.Save(ConfigurationSaveMode.Full);
-
}
Well for updating it was hard to find any usefull code to update a connectionstring. It wasn’t possible to update a connectionstring, so I had the remove the connectionstring, and add a new one. Well isn’t that odd?? Microsoft left something undone… Well maybe it isn’t usefull to change your app.config, but well, I had to make it anyhow. So dynamic app.config or web.config files is little bit strange, because you can’t dynamically update it. No you have to remove it first by the configurationmanager.
<pre lang=”csharp”>
-
/// First remove the old connectionstring and after that
-
/// add a connection string to the connectionstrings
-
/// section and store it in the configuration file.
-
/// </summary>
-
/// <param name="csName">The name of the property.</param>
-
/// <param name="connectionString">The connectionstring as specified.</param>
-
public static void UpdateConnectionStrings(string csName, string connectionString)
-
{
-
// Get the configuration file
-
System.Configuration.Configuration config =
-
ConfigurationManager.OpenExeConfiguration(
-
ConfigurationUserLevel.None);
-
-
// Remove the existing connectionstring.
-
config.ConnectionStrings.ConnectionStrings.Remove(csName);
-
// Add the connectionstring
-
ConnectionStringsSection csSection = config.ConnectionStrings;
-
csSection.ConnectionStrings.Add(
-
new ConnectionStringSettings(csName,
-
connectionString, "System.Data.SqlClient"));
-
-
// Save the configuration file
-
config.Save(ConfigurationSaveMode.Full);
-
}
-
</pre>
Generating Excel (XmlSpreadSheet) in C# Part 1.
by mysticslayer on Nov.09, 2007, under Programming
If you want to export data with VB.NET of C# you can do it with the Office API’s delivered with your Office API’s, but I wasn’t to happy with the Excel Object. After doing some research on the internet I discovered alot of ways to write your own manner to Generate an Excel document with the XmlSpreadsheet. Well after doing some tryouts with Excel I discovered that Excel has alot of options how to save your data.
- Excel Workbook (*.xlsx) Office 2007 Format
- Excel Macro-Enabled Workbook (*.xlsm) Office 2007 Format
- Excel Binary Workbook (*.xlsb) Office 2007 Format
- Excel 97-2003 Workbook (*.xls)
- XML Data (*.xml)
- Single File Web Page (*.mht; *.mhtml)
- Web Page (*.htm; *.html)
- Excel Template (*.xltx) Office 2007 Format
- Excel Macro-Enabled Template (*.xltm) Office 2007 Format
- Excel 97-2003 Template (*.xlt)
- Text (Tab Delimited) (*.txt)
- Unicode Text (*.txt)
- XML Spreadsheet (*.xml)
- Microsoft Excel 5.0/95 Workbook (*.xls)
- CSV (Comma Delimited) (*.csv)
- Formatted Text (Space Delimited) (*.prn)
- Text (Macintosh) (*.txt)
- Text (MS-DOS) (*.txt)
- CSV (Macintosh) (*.csv)
- CSV (MS-DOS) (*.csv)
- DIF (Data Interchange Format) (*.dif)
- SYLK (Symbolic Link) (*.slk)
- Excel Add-In (*.xlam) Office 2007 Format
- Excel 97-2003 Add-In (*.xla)
You’ll see that the XML Spreadsheet normally would be saved in the Extension of *.xml, however you can save this also as *.xls. Why is that? You would ask? Well Excel has alot of manners how to open or save data. If you would have a CSV file delimited in Comma’s or Tab delimited files you can also open this with Excel. And you can see that the data is perfectly rendered in the Spreadsheet.
Let’s see where a XML SpreadSheet starts with:
-
< ?xml version=“1.0”?>
-
< ?mso-application progid=“Excel.Sheet”?>
-
<workbook xmlns=“urn:schemas-microsoft-com:office:spreadsheet”
-
xmlns:o=“urn:schemas-microsoft-com:office:office”
-
xmlns:x=“urn:schemas-microsoft-com:office:excel”
-
xmlns:ss=“urn:schemas-microsoft-com:office:spreadsheet”
-
xmlns:html=“http://www.w3.org/TR/REC-html40”>
-
<documentproperties xmlns=“urn:schemas-microsoft-com:office:office”>
-
<author>Author</author>
-
<lastauthor>LastAuthor</lastauthor>
-
<created>11-09-2007</created>
-
<version>12.00</version>
-
</documentproperties>
-
<excelworkbook xmlns=“urn:schemas-microsoft-com:office:excel”>
-
<protectstructure>False</protectstructure>
-
<protectwindows>False</protectwindows>
-
</excelworkbook>
-
</workbook>
After this section you will include you styles. When writing your styles you can build a Excel Workbook manually and make some different styles init. After that you can save this Workbook to a XML Spreadsheet. When you finally saved a Workbook it will generate for example the following styles:
-
<styles>
-
<style ss:ID=“Default” ss:Name=“Normal”>
-
<alignment ss:Vertical=“Bottom”/>
-
<borders />
-
<font ss:FontName=“Calibri” x:Family=“Swiss” ss:Size=“11” ss:Color=“#000000”/>
-
<interior />
-
<numberformat />
-
<protection />
-
</style>
-
<style ss:ID=“s62”>
-
<numberformat ss:Format=“Fixed”/>
-
</style>
-
<style ss:ID=“s63”>
-
<numberformat ss:Format=“"€"#,##0.00”/>
-
</style>
-
<style ss:ID=“s65”>
-
<numberformat ss:Format=“0%”/>
-
</style>
-
<style ss:ID=“s66”>
-
<numberformat ss:Format=“Short Date”/>
-
</style>
-
<style ss:ID=“s67”>
-
<numberformat ss:Format=“[$-F400]h:mm:ss\ AM/PM”/>
-
</style>
-
<style ss:ID=“s68”>
-
<numberformat ss:Format=“Percent”/>
-
</style>
-
<style ss:ID=“s69”>
-
<numberformat ss:Format=“#\ ?/?”/>
-
</style>
-
<style ss:ID=“s70”>
-
<numberformat ss:Format=“Scientific”/>
-
</style>
-
<style ss:ID=“s71”>
-
<numberformat ss:Format=“@”/>
-
</style>
-
<style ss:ID=“s72”>
-
<numberformat ss:Format=“00.00.00.000”/>
-
</style>
-
<style ss:ID=“s73”>
-
<numberformat ss:Format=“Standard”/>
-
</style>
-
</styles>
What you can do is giving alot of extra options to your excel sheet. Every above style has a particular function. You can specify a column and give it a specific style. So that the column will be formatted by for example Scientific functions. After the styles there will be the data for your spreadsheet.
First we start with a new Worksheet. Every worksheet has a name . Let’s start with that:
-
<worksheet ss:Name=“ExampleSheet”>
-
</worksheet>
When we have done that we can add our table with data in our Worksheet. It will look like this.
-
<worksheet ss:Name=“ExampleSheet”>
-
<table ss:ExpandedColumnCount=“12” ss:ExpandedRowCount=“1” x:FullColumns=“1”
-
x:FullRows=“1” ss:DefaultRowHeight=“15”>
-
<column ss:Index=“2” ss:StyleID=“s62” ss:AutoFitWidth=“0”/>
-
<column ss:StyleID=“s63” ss:Width=“53.25”/>
-
<column ss:StyleID=“s65” ss:AutoFitWidth=“0”/>
-
<column ss:StyleID=“s66” ss:AutoFitWidth=“0” ss:Width=“67.5”/>
-
<column ss:StyleID=“s67” ss:Width=“54.75”/>
-
<column ss:StyleID=“s68” ss:AutoFitWidth=“0”/>
-
<column ss:StyleID=“s69” ss:AutoFitWidth=“0”/>
-
<column ss:StyleID=“s70” ss:AutoFitWidth=“0”/>
-
<column ss:StyleID=“s71” ss:AutoFitWidth=“0”/>
-
<column ss:StyleID=“s72” ss:AutoFitWidth=“0”/>
-
<column ss:StyleID=“s73” ss:AutoFitWidth=“0”/>
-
<row>
-
<cell><data ss:Type=“String”>General</data></cell>
-
<cell><data ss:Type=“Number”></data></cell>
-
<cell><data ss:Type=“Number”>12345</data></cell>
-
<cell><data ss:Type=“String”>€</data></cell>
-
<cell><data ss:Type=“DateTime”>2007-03-14T00:00:00.000</data></cell>
-
<cell><data ss:Type=“DateTime”>1899-12-31T13:30:00.000</data></cell>
-
<cell><data ss:Type=“Number”>1</data></cell>
-
<cell><data ss:Type=“Number”>0.33333333333333331</data></cell>
-
<cell><data ss:Type=“Number”>0.33333333333333331</data></cell>
-
<cell><data ss:Type=“String”>text</data></cell>
-
<cell><data ss:Type=“String”>Breda</data></cell>
-
<cell><data ss:Type=“Number”>12345</data></cell>
-
</row>
-
</table>
-
</worksheet>