Excentric shoot it later

Collection and predicates search elements without cycling

Among the most useful things when you work with collections, are Search methods, allowing us to find an element inside the collection using the value of one or more of his properties as a search criteria.

These methods can be implemented both as Indexer (a property) or a specific method. The second option is necessary because often there are more properties of the same data type used to search a collection and you can implement only one indexer with a certain signature.

Let’s work through some samples to see how to use predicates:

Our Entity class, named AnagraficaAzienda, exposes the following properties:

string DescrizioneRicerca //SearchDescription
string RagioneSociale //Company name
string PartitaIva //Vat Code
string Indirizzo //Address
string CAP //Zip code
string Citta //City
string PV //State or province
int IDTipo //Type
int IDAnagrafica //Identity

To create an Indexer to search through the SearchDescription we can use the following code:

public AnagraficaAzienda this[string pDescrizioneRicerca]
{
    get
    {
        return( Find(item=>item.DescrizioneRicerca==pDescrizioneRicerca);
    }
}

to create a method to get all elements that match a regular expression we can use the following code:

public List<AnagraficaAzienda> FindWithRegEx( string pRegEx )
{
    return (FindAll(items => Regex.Match(items.RagioneSociale, pRegEx).Success));
}

to create a method that returns the data using the Vat Code (PartitaIva) we can use the following code:

public AnagraficaAzienda FindXPartitaIva(string pPartitaIva)
{
    return( Find(item=>item.PartitaIva==pPartitaIva);
}

To create a multi dimensional indexer we can use the following code

public AnagraficaAzienda this[int pIDZona, int pIDAnagrafica]
{
    get
    {
        return( Find(item=>item.IDZona==pIDZona && item.IDAnagrafica==pIDAnagrafica);
    }
}

as you can see, a Predicate allows us to use any kind of binary expression to match the entity fields with the search criteria we want to use.

posted @ venerdì 13 agosto 2010 17.29 | Feedback (0)

How to create a Guid.Empty in SQL

Last friday my colleague Luca was “fighting” with a problem in managing null values and GUIDs. A Guid is a value type so it is not nullable. In my stored procedures when using parameters for filtering, I use the NULL value as the default value to ignore a filter parameter.

Changing the code of several classes to be able to change a Guid.Empty into a DbNull before passing it to a SELECT command was decisely not the best thing to do, so we searched a way to recreate a Guid.Empty value in SQL.

We were able to create the small snippet of SQL in the following function:

CREATE FUNCTION Guid_Empty()
RETURNS UniqueIdentifier
AS
BEGIN
RETURN cast(cast(0 as binary) as uniqueidentifier)
END
GO

After the creation of the above function and a simple table on my Paperinik Database,  I was able to write the following query:

SELECT [ID]
      ,[Descrizione]
      ,[Prezzo]
      ,[Quantità]
      ,[Umi]
      ,IsNull(AltGuid, dbo.Guid_EMPTY()) AS Alt
  FROM [paperinik].[dbo].[TbRighe]

The query produces the following output:

I can use it also to build a query like the one below:

SELECT [ID]
      ,[Descrizione]
      ,[Prezzo]
      ,[Quantità]
      ,[Umi]
      ,IsNull(AltGuid, dbo.Guid_EMPTY()) AS Alt
  FROM [paperinik].[dbo].[TbRighe]
WHERE
IsNull(AltGuid, dbo.Guid_EMPTY()) = dbo.Guid_EMPTY()

That allows me to filter only the Guid Empty rows, or I can use the <> to find only the rows with a non Empty Guid. This way of writing the query, is giving me the possibility to use Guid.Empty in my Code as a SQLParameter value without the need to use a Nullable Guid or to implement complicated control expressions in my C# classes.

posted @ martedì 10 agosto 2010 22.53 | Feedback (0)

Access the Deleted Rows of a Datatable

Today, I’m updating the Code Generator I’ve made in 2006 to build automatically the Data Provider classes for my applications. I’m passing my applications to Framework 4.0 and instead of a simple framework change, I’ve decided to make some cleaning in Old Common Libraries code and add some new features in the Data Classes.

One of the new features in my data classes, will be some events, raised when data operations complete, RowAdded and RowDeleted events are two of them. In both cases, it would probably be useful if you Handle the Event in your code, to have some informations on the Added Row or Deleted Row, so I decided to pass the whole DataRow as a property of the event argument.

Pass the Added row as an argument is very simple, I have the object in my method.

Pass the Deleted row is a little less simple. I’ve made a simple test project to find out how to get it, to be precise, the exact phrase is: How to get a copy of it.

The sample form is the following:

On the form there are 3 buttons – Carica = Load, Test Aggiunta = Test AddRow, Test cancellazione = Test Deletion. There is also a textbox, set as Multiline with vertical scrollbar.

The test code is the following:

        DataTable mDt;
        private const string FLD_ID = "ID";
        private const string FLD_Description = "Description";
        private const string FLD_Date = "Date";
        private const string FLD_Price = "Price";

        public Form1()
        {
            InitializeComponent();
            mDt = new DataTable("Tbtest");
            DataColumn col = new DataColumn(FLD_ID, typeof(int));
            col.AutoIncrement = true;
            col.AutoIncrementSeed = 1;
            col.AutoIncrementStep = 1;
            mDt.Columns.Add(col);
            col = new DataColumn(FLD_Description, typeof(string));
            mDt.Columns.Add(col);
            col = new DataColumn(FLD_Date, typeof(DateTime));
            mDt.Columns.Add(col);
            col = new DataColumn(FLD_Price, typeof(Decimal));
            mDt.Columns.Add(col);
        }

The initialization code, I build a Datatable on the Constructor code of the form.

        private void btnCarica_Click(object sender, EventArgs e)
        {
            mDt.Rows.Clear();

            DataRow row = mDt.NewRow();
            row[FLD_Description] = "Quaderno";
            row[FLD_Date] = DateTime.Now;
            row[FLD_Price] = 1.5;
            mDt.Rows.Add(row);

            row = mDt.NewRow();
            row[FLD_Description] = "Penna";
            row[FLD_Date] = DateTime.Now;
            row[FLD_Price] = 2.8;
            mDt.Rows.Add(row);

            row = mDt.NewRow();
            row[FLD_Description] = "Gomma";
            row[FLD_Date] = DateTime.Now;
            row[FLD_Price] = 0.7;
            mDt.Rows.Add(row);

            row = mDt.NewRow();
            row[FLD_Description] = "Matita";
            row[FLD_Date] = DateTime.Now;
            row[FLD_Price] = 1.1;
            mDt.Rows.Add(row);

            row = mDt.NewRow();
            row[FLD_Description] = "Block notes";
            row[FLD_Date] = DateTime.Now;
            row[FLD_Price] = 2.8;
            mDt.Rows.Add(row);

            row = mDt.NewRow();
            row[FLD_Description] = "Pennarelli 12 colori";
            row[FLD_Date] = DateTime.Now;
            row[FLD_Price] = 6.7;
            mDt.Rows.Add(row);

            mDt.AcceptChanges();

            Mostra();
        }

Here I build some rows, put them in the DataTable and call the Mostra method, wich shows the rows in the textbox.

        private void Mostra()
        {
            this.txtResult.Text = string.Empty;
            for( int i=0; i< mDt.Rows.Count;i++)
            {
                MostraRiga(mDt.Rows[i]);
            }
    
        }

        private void MostraRiga(DataRow pRow)
        {
            this.txtResult.Text += "ID: " + pRow[FLD_ID].ToString() + Environment.NewLine;
            this.txtResult.Text += "Description: " + pRow[FLD_Description].ToString() + Environment.NewLine;
            this.txtResult.Text += "Date: " + pRow[FLD_Date].ToString() + Environment.NewLine;
            this.txtResult.Text += "Price: " + pRow[FLD_Price].ToString() + Environment.NewLine;
            this.txtResult.Text += Environment.NewLine;
        }

This is the Mostra method and the MostraRiga Method, made to show the content of the table rows on the textbox.

        private void btnAdd_Click(object sender, EventArgs e)
        {

            DataRow row = mDt.NewRow();
            mDt.Rows.Add(row);
            row[FLD_Description] = string.Format("Nuova riga {0}", row[FLD_ID]);
            row[FLD_Date] = DateTime.Now;
            decimal pippo = Convert.ToDecimal(row[FLD_ID]);
            row[FLD_Price] = pippo*0.98m;

            mDt.AcceptChanges();

            MostraRiga(row);
        }

Here is the AddRow test, that simply builds a row, adds it to the table and then shows it on the textbox.

        private void btnDelete_Click(object sender, EventArgs e)
        {
            DataRow row = mDt.Rows[0];
            int id = Convert.ToInt32(row[FLD_ID]);
            row.Delete();
            string filter = string.Format("{0} = {1}", FLD_ID, id);
            DataView vi = new DataView(mDt, filter,"", DataViewRowState.Deleted);
            DataTable tt = vi.ToTable();
            mDt.AcceptChanges();
            MostraRiga(tt.Rows[0]);
        }

And finally the most important bit of code, the code that helps us to show the informations of the deleted row.
As you can see, the method simply deletes the first row of the DataTable, saving it’s ID.
After the deletion, to be able to get the whole row, I build a dataview that retrieves the row I’ve deleted and then uses the ToTable method of the DataView to put the data in a new Table. Then I give the Row to the MostraRiga (ShowRow) method and I can see its content on my textbox.

The ToTable passage is very important, because if you try to pass to the function something like:

MostraRiga(vi[0].Row);

Whichis perfectly legal with a non deleted row, you instead receive an Exception, because you cannot access deleted rows data.

posted @ lunedì 9 agosto 2010 17.03 | Feedback (0)

How to create the ASP.NET 2.0 authentication tables on a SQL Server DB

The translation of this post is very simple, because I am not enough fluent in english to write what I wrote in italian, but I just write something to the beginners, the newbies to a new technology. Never stop in front of an obstacle, in our business we got the luck that almost everything has already been solved by someone else. So F1, or Google, or BING or a forum are always not far from you.

My problem, being a beginner in ASP.Net MVC was simple, the base template of this technology in Visual Studio, creates me a fully functional website to experiment with, but even though there is a nice Registration Form and Login Form, they don’t work, because they need that in your database you have a precise schema of tables and stored procedures to make things work.

The solution is a small utility installed by the .Net framework 2.0 in his folder.

I found this article: Creating the Membership Schema in SQL Server, (which was on the ASP.Net, the same where I can find tutorials on MVC) where it is explained not only how to build the tables using the RUN command: %WINDIR%\Microsoft.Net\Framework\v2.0.50727\aspnet_regsql.exe that runs a nice wizard that creates or removes the authentication tables on a SQL Server database, but it explains also how the authentication system works. After the creation of the tables and the set up of the connection string of my web application to the database, the ASP.Net MVC basic site has full registration and login functionalities.

Maybe for those who work with ASP.Net 2.0 from the beginning this thing is probably obvious and elementary. But for a beginner starting with a new technology existing from a long time, usually the first steps are the highest to climb. For me step one is done, now let’s try the step 2.

posted @ domenica 1 agosto 2010 11.07 | Feedback (0)

A small trick to intercept screen capture (printscreen)

It is just a trick, not a solution made to avoid average users from doing things we don’t want. To solve this problem at the root we need to lock the printers, lock the emails and kill all paste keys in our applications. If the user has a screencapture program installed this does not work, but if the user is not able to install things on his PC it can help someway.

On the form where we don’t want the user to capture screen we will activate the KeyPreview option using the property on the Form object. Then add this code to the KeyUp event.

        private void Form1_KeyUp(object sender, KeyEventArgs e)
        {
            if( e.KeyCode == Keys.PrintScreen || e.KeyCode == Keys.Print)
            {
                Clipboard.Clear();
                e.Handled = true;
            }
        }

This way after a Ctrl+ Printscreen or Alt+Printscreen if you go to Paint or Word and click Paste, there won’t be a copy of the screen on your document or bitmap.

If someone know a more serious way to kill this option let us know and we will link it ;o).

posted @ venerdì 30 luglio 2010 10.00 | Feedback (0)

How to determine which Control raised the Item Changed Event on a Currency Manager

A simple and fast post on C#, as usual to glue the solution of a problem  in a place where I know I can find it in the future.

The currency manager object is both loved and hated by those who usually ask questions in the forums where I use to answer, I found it useful, even though sometimes it seems a little bit tricky in its behaviours.

The ItemChanged event, is raised by the Currency manager each time a bound control in the current context modifies the data bound to one of its properties. To determine which control has updated the data, in the ItemChangedEventArgs we have an Index property. It is a 1 (one) based index, strange for the .Net, anyway, using this index in the Bindings collection of the currency manager, we are able to find out which control modified the data. In the sample code below, if a particular textbox has modified the data, we Update the data on the Database using a Data provider Class.

 void mCurMgr_ItemChanged(object sender, ItemChangedEventArgs e)
        {
                if (mCurMgr.Bindings[e.Index - 1].Control.Name == txtPercentualeCons.Name)
                {
                    mDpScenariAziende.Update();
                }
        }

posted @ martedì 27 luglio 2010 11.09 | Feedback (0)

How to read the Nth record of a table in SQL Server

Following a question made me by another developer, I write here (mostly to remember it) how to read a specific row in a table using an arbitrary ordering method.

In TSQL we have just the SELECT TOP to decide a number of records to read but to get the Nth row of a table from the SQL 2005 version we have a nice function called ROW_NUMBER. I’ve used the dear old Pubs database coming as case study with SQL until version 2000.

This is how to build the query, to number the Authors table:

SELECT [au_id]
      ,[au_lname]
      ,[au_fname]
      ,[phone]
      ,[address]
      ,[city]
      ,[state]
      ,[zip]
      ,[contract]
      ,[brthdate]
    , row_number() over( order by au_lname) as rnum
  FROM [pubs].[dbo].[authors]

The rnum column, contains a progressive numbering for the rows based on the author last name. Using Row_Number we can also number groups of rows, for instance:

    , Row_Number() Over(partition by [state] Order by [state]) as RNum

With this syntax we get rows numbered from 1 to n for each state in the list.

To get the Nth record, however, we have to surround our query with another one like in the code below:

SELECT * from (
SELECT [au_id]
      ,[au_lname]
      ,[au_fname]
      ,[phone]
      ,[address]
      ,[city]
      ,[state]
      ,[zip]
      ,[contract]
      ,[brthdate]
    , row_number() over( order by au_lname) as rnum
  FROM [pubs].[dbo].[authors]) aut
WHERE aut.rnum = 15

posted @ mercoledì 14 luglio 2010 17.08 | Feedback (0)

SQL Server Update a Table with values selected from another table

Today, on another step of the job started with the last post, I had the following problem, I created a table with some informations and I needed to add more informations taken from other tables.

A an Example, I created an inventory table, with Article Code, Description, Quantity, to create it I used an INSERT query from the Articles table, then the users of my system inserted the Inventory values and now I need to add Sales Price and Cost of every article, reading them from the PriceList.

For Simplicity my tables are as follows:

TbInventory
   IDInventory int
   IDArticle nvarchar(32)
   Quantity decimal(18,5)
   Price decimal(18,5)
   Cost decimal(18,5)

TbListini
   IDPricelist int
   IDarticle nvarchar(32)
   Price decimal(18,5)
   Cost decimal (18,5)

To update price and cost I’lluse, an Update query, with a Join.

UPDATE TbInventory
    SET Price = isnull(lis.Price,0)
      , Cost = isnull(lis.Cost,0)
FROM TbInventory inv
INNER JOIN
    TbPriceList lis
ON
  inv.IDArticle = lis.IDArticle  

This way, I’m setting a relation between the two tables usin ga JOIN thenI update the Inventory Table with The Price List values.

posted @ lunedì 12 luglio 2010 18.40 | Feedback (0)

Use of Rank function to extract a list of rows with the highest date

Today I had the problem to create a query to get the most recent price from a pricelists table. Every pricelist in the table can contain the same article any number of times with different startup dates. I made some search to find a way to obtain what i needed and I found the RANK function, it was used in a similar scenario. I publish this post as usual to remember how to use this function next time I need it and in the hope to help someone else solving similar problems.

The table:

CREATE TABLE [dbo].[TbPricelistsRg](
    [IDPriceListRg] [int] NOT NULL,
    [IDPriceList] [nvarchar](10) NOT NULL,
    [IDArticle] [nvarchar](32) NULL,
    [Price] [decimal](18, 5) NULL,
    [StartDate] [date] NULL,
 CONSTRAINT [PK_TbPriceListsRg] PRIMARY KEY CLUSTERED 
(
    [IDPriceListRg] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  
     = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Every row is linked to a PriceList through the IDPriceLIst field, we don’t need to use it for the example so it is omitted.

INSERT INTO [dbo].[TbPricelistsRg]
           ([IDPriceList]
           ,[IDArticle]
           ,[Price]
           ,[StartDate])
     VALUES
           ('CLD'
           ,'ABC'
           ,10
           ,CONVERT(date, '20080105', 112))
GO

INSERT INTO [dbo].[TbPricelistsRg]
           ([IDPriceList]
           ,[IDArticle]
           ,[Price]
           ,[StartDate])
     VALUES
           ('CLD'
           ,'OCQ'
           ,14
           ,CONVERT(date, '20080105', 112))
GO

INSERT INTO [dbo].[TbPricelistsRg]
           ([IDPriceList]
           ,[IDArticle]
           ,[Price]
           ,[StartDate])
     VALUES
           ('CLD'
           ,'FYS'
           ,21
           ,CONVERT(date, '20080105', 112))
GO

INSERT INTO [dbo].[TbPricelistsRg]
           ([IDPriceList]
           ,[IDArticle]
           ,[Price]
           ,[StartDate])
     VALUES
           ('CLD'
           ,'MUI'
           ,13
           ,CONVERT(date, '20080105', 112))
GO

INSERT INTO [dbo].[TbPricelistsRg]
           ([IDPriceList]
           ,[IDArticle]
           ,[Price]
           ,[StartDate])
     VALUES
           ('CLD'
           ,'SIO'
           ,42
           ,CONVERT(date, '20080105', 112))
GO

INSERT INTO [dbo].[TbPricelistsRg]
           ([IDPriceList]
           ,[IDArticle]
           ,[Price]
           ,[StartDate])
     VALUES
           ('CLD'
           ,'GOU'
           ,18
           ,CONVERT(date, '20080105', 112))
GO

INSERT INTO [dbo].[TbPricelistsRg]
           ([IDPriceList]
           ,[IDArticle]
           ,[Price]
           ,[StartDate])
     VALUES
           ('CLD'
           ,'HOU'
           ,20
           ,CONVERT(date, '20080105', 112))
GO

The previous script inserts some articles, we can repeat it changing the PriceLIst, the Dates and prices to be able to show how the query meets our needs.

SELECT [IDPriceListRg]
      ,[IDPriceList]
      ,[IDArticle]
      ,[Price]
      ,[StartDate]
      ,RANK() OVER (PARTITION BY IDPriceList, IDArticle ORDER BY StartDate DESC) DATERANK
  FROM [paperinik].[dbo].[TbPricelistsRg]

The RANK function, allows us to indicate one or more grouping fields on which calculate ranking, this is done using the PARTITION BY keywords, in our query we indicate the two fields on which ranking has to break. The ORDER BY  keyword, instead indicates on which field we have to measure ranking. while the DESC keywords tells that we want to start from the most recent date..

This is the result of our query.

So to obtain the most recent prices, we need just to add a filter on DATERANK = 1, and to obtain the less recent price we just need to delete the DESC keyword and filter on rank 1.

posted @ mercoledì 7 luglio 2010 23.45 | Feedback (0)

This assembly is built by a runtime newer than the currently loaded runtime and cannot be loaded.

This error is given by the gacutil.exe if you try to register in GAC an assembly built with Framework 4.0 and you are using the gacutil.exe built for Framework 2.0.

The programs I develop don’t register any assembly in GAC when installed on an end user machine, except if there is the need to expose .Net function to a .COM application.

On the development machines instead, all the general purpouse libraries of my organization are installed in GAC, this is made to avoid versioning problems in projects that are using more than one solution, and are modified in different moments of the developer working time.

To register the libraries in GAC we use the gacutil.exe from a Post Build Event, the gacutil.exe has been conveniently copied in c:\windows to have it in the standard “PATH” environment variable.

Trying to register in GAC an assembly produced with framework 4.0 I’ve received the message put in the title and my assembly was not registered in the GAC.

The problem is due to the fact that the gacutil.exe I’m using was the version built for framework 2.0, which works correctly until framework 3.5. (since 2005). After some searches on the internet, where it is told that the utility ships with Visual Studio, I made a dir /s on my c:\ drive and found the new release of the utility here:

C:\Program Files\Microsoft SDKs\Windows\v7.0A\bin\NETFX 4.0 Tools

Copying the new utility and replacing the old one, my assemblies made with framework 4.0 are correctly registered in GAC. However, there is something different from before, the folder where the GAC was until framework 3.5,

c:\Windows\Assembly

is now replaced by:

C:\Windows\Microsoft.NET\assembly\GAC_MSIL

This is completely transparent for us Users, but probably it is the reason of the gacutil.exe upgrade we need to do.
The new version registers correctly the dlls made with older frameworks too.

posted @ venerdì 18 giugno 2010 15.19 | Feedback (0)

Some News on TFS2010 and Visual Studio 2010

Considering the number of Innovations, new features, revolutions, and more introduced by Visual Studio 2010 and Team Foundation Server 2010, I’ve decided (because I’m the Boss of the Dev team) to migrate our small development team immediately after the RTM become available.

Last wednesday we updated all our components, built and released all our projects, made a copy on development machines of all things contained in the TFS Scource control and started.

The operations we did to upgrade the server from TFS 2008 Workgroup to TFS 2010 were the following:

• Backup of all TFS databases
• Stop of SQL Server
• Phisical copy of all TFS Databases

Having already installed the TFS on a SQL Server 2008 we didn’t have the task to upgrade the server from SQL 2005 but we verified both the Windows Server 2003 Standard OS of the machine and SQL Server 2008 got all the needed patches.

Then, not being rich enough to buy a new server, we went on with upgrade doing the following:

• Uninstall Visual Studio 2008, Team Explorer, TFS 2008 and all things connected to them from the server.
• Restart and check if there were patches needed prior to install 2010.
• Installed Visual Studio 2010 on the server.
• Installed TFS 2010 on the server, configured the TFS throungh the configuration wizard, telling it which was the previous TFS database, and some options, like the name of the new project collection for the converted projects.
• Installed Team Explorer on the server.

And magically, everything was working.

Simultaneously, we uninstalled Visual Studio 2008 and installed Visual Studio 2010 on the Developers machines, Installed Team Explorer and all was working correctly. Users, permissions and access to the projects were working correctly.

We then spent 2 days converting and recompiling all projects and adjusting some things on builds (all relative to the Setup builds) but we had no big troubles at all.

After the conversion to VS2010 what we developers noticed first is that Builds on dev machines are perceptibly faster and the improvements in the User interface of the Development environment together with a gazilion of extensions let us made imediately our environment comfortable.

If you still have doubts “Upgrade or not upgrade that’s the question” feel free to ask, but that’s our experience.

posted @ martedì 11 maggio 2010 14.01 | Feedback (0)

DNN 5.3 Skinning and headaches

In the 5.3 version of DotNetNuke, the Skin format hasn’t been changed from the 4.x versions except I think for enhancements, but the skin management has been drastically changed and what was very simple then is now complicated.

Create a personalized skin for DNN is easy if you know how to do it, it is difficult if you never did it before. There is documentation on the DNN site you can download, but the manual gives you many hints but not a real how to do it guide. What you can do to learn it without big troubles is go to one of the skin shops online, choose a skin you like, buy it, download it and install it, then take a look at the ascx files and the css files and try to edit a CSS class here and a Table there and see what happens.

But this is not a post on how to create a skin, it is a series of notes to help you survive the differences made in version 5.3 skin management.

With version 5.3 some fundamental things about skins are changed, and I found this devastating for my way of using DNN and skinning portals.

1. Skins cannot be installed from the administrator user of a portal, they should be installed by the DNN host user.
2. So if I sell portals on a single DNN, now my customers cannot skin their portals indipendently they have to ask me to install their skins.
   There is however a workaround to this prohibition, a skin or a container package can be installed on a portal as a subfolder on the portals\n\skins or portals\n\containers folders and they appear under the portal skins as it was before. I hope the DNN programmers won’t eliminate this possibility in the future.
3. Skins now can’t be installed from the Skins management page because they are now DNN Extensions. (I presume that calling extension install functions from a page different from the extensions page is not possible).
4. Skins are no more a zip file containing 2 zip files (one containing the skin and the other containing containers). The Skin is a zip file and the Containers is another zip file; besides the ASCX, CSS, and Image files, the zip files need to contain an XML file named SkinName.dnn that is the manifest file for the skin or container, it has a specific format and contains informations on the skin, or containers, on which files they contain and on who is the author of the skin or container.
5. Skins and container should be installed separately as single extensions. On the documentation it is written that old skins work and can be installed. I wasn’t able to do so, installing a skin in the old format gave me error, but maybe it’s my fault.

If you already have portal skins installed and working, and you upload them through FTP as I said in point (2), you can build an extension from it using DNN. Being this operation not very clear I write it here (mainly for myself), to avoid loosing hours next time I need to do it:

1. Login as Host of DNN.
2. From Host menu open the Extensions page.
3. On the Extensions page, from menu or from the link at the bottom select Create Extension.
4. On the page appearing, you will be asked the following things:
• Select Extension type – it is a combobox on top of page already set on the “Authentication System” option, I always forget to update it and because nothing is asked about it I press Next and cause damages. If you forget to set the combo on Skin or Container, you will find inexistent extensions installed, fortunately there is the Uninstall option. I think that if this combo was set on empty or “None” and a FogHorn Hoot played if it wasn’t set by us users when clicking on next step could have been helpful.
• Name – here you have to set the full path of the skin in the format (Portals\10\Skins\Business04_org) and it is not written anywhere.
• Friendly Name – just as written, a friendly name for your skin (Business04_org)
• Description – The skin description
• Version – the skin version, now you can version also skins.
5. Click on next step and pay attention there is not a Back step so if you forget someting you are “fried” (:D)
6. On the next page you will find many things:
   • Owner – for example Sabrina
   • Organization – DotNetWork.it
   • Url – www.dotnetwork.it
   • Email Address – support@dotnetwork.it
7. Click on the next step and you will simply find yourself again on the Extensions page, but if you did all well, on the Skins portion list, you find your skin with the Modify Icon but without the Uninstall icon, I don’t know why and I don’t ask :D.

Now that your skin (and repeating it also the container) is part of DNN as an extension, if you want you can create a package to install and export it. I write below how to do it because I needed 6 attempts to succeed.

1. Log on as Host of DNN.
2. From host menu open the Extensions page.
3. On the Extensions page, go down to the skin section or filter to see only it, click on the Modify Icon of your skin (the pencil).
4. In the skin page, at the bottom, appears the link option Create Package, click on the link to start wizard.
5. First page shows you what you are about to create and shows two checkboxes, leave them as they are and click on the next step.
6. If you are exporting the skin of a portal, this second page has already wrong data, if you look the first textbox that should contain your skin path, it contains something similar to this:
   Portals\_default\Skins\Portals\10\Skins\MyskinFolder
   it is clearly wrong, because it adds to your skin folder the Host skin folder.
7. Update it cutting the initial portion:
   Portals\10\Skins\MyskinFolder 
   and click on Refresh File List link near the textbox.
8. In the multiline textbox under the previous one, are loaded all files of your skin, verify the names and if there is a vti_conf folder delete the files in that folder, they are copies and produce errors creating the skin.
9. Click on the next step link, the new page got a multiline textbox with the XML text of the manifest file of the new skin, you can leave it as is or modify descriptions, as a hint leave it as is and modify it after the skin file is created after downloading it.
10. Click on the next step link, the page appearin has 2 textbox single line with the manifest file name and the archive file name, these two paths on my DNN have the following form:
    MyDnn_Portals\10\Skins\nomeskin.dnn
    MyDnn_Portals\10\Skins\Nomeskin_01.00.00_Install.zip
    They are fundamentally wrong, because there isn’t a folder with this name on my server, leaving them as written produces an error not a skin. It is necessary clear the initial part of the names making them as follows:
    nomeskin.dnn
    Nomeskin_01.00.00_Install.zip
11. Click on the next step link and cross your fingers. If in the next page you see a green circle and the following message
    The Package was created and can be found in the www.MioSito.it/Install/Skin folder
    Going to the indicated folder through ftp or on local folder if you are working on the server, you will find the two files above with the skin and the manifest. and you can install the skin on another portal.
    I haven’t yet tried an install but looking at what is written on the manifest file, it is probably necessary to update the base path inside the manifest prior to install on a different portal using a _default to install the skin at Host level.

In the hope to have been able  help you avoiding Skinning headaches, good work.

posted @ domenica 11 aprile 2010 11.59 | Feedback (0)

Dotnetnuke post update hints

I have just updated my company DNN, it was peacefully working since 2007 with version 04.08, now I’ve upgraded it to the 05.02.03 (74) without big troubles, it has been simple doing it on the online server after a test on a local machine.

WARNING! if anyone as lazy as me needs to do this kind of upgrade, be careful, it is necessary first upgrade to version 04.09.05 and then to version 5 to avoid any troubles.

The links to download both versions from Codeplex are accessible from the DNN page even though not very visible, so search them in the Community Upgrade pages.

Said so, everything was fine after the upgrade, except two things:  I have multilanguage portals and the language links disappeared. The second problem which at the moment I haven’t yet solved is that the system icons on the menus have malformed urls and are not visible, as soon as I solve the problem I’ll post an addendum to this post.

For the first problem, after some deep breathing, I found the solution on a forum thanks to Mr. Sebastian Leopold from germany, it is simple but a little bit hidden, that’s why even searching all the administrative pages I didn’t found it.

On the Site Admin menu Select Languages, on the Languages page Select a Language different from the Default one in the upper left side combobox.

E.g. Italiano Italia (it-IT); Click on the Edit Language Link below the combobox and on the page appearing check the Enabled checkBox. The Language links or language combobox Will immediately appear on your pages. Repeat for each language you need and for each portal you need to enable.

posted @ giovedì 25 marzo 2010 18.18 | Feedback (0)

Where in the world is the DxCore Plugins directory gone?

Maybe it is just a dummy of the month post but…

I’ve installed the RC of Visual studio 2010 because in 2 weeks I am one of the speakers at a Developers conference and I have to speak of the new features of Visual Studio 2010 and C#.

When I develop things, even though they are just Demo projects I need to be comfortable with my environment, so I tried to understand how to be able to have my DxCore plugins also on the new Visual Studio.

I was using version 3.0.5 (if it works why do I have to upgrade?) but for Visual Studio 2010 I had to install the new Beta of version 10.0.0…

When I finished the installation I opened the Program Files folder and searched for the familiar Bin\Plugins folder, but it wasn’t there so I made it and copied the plugins into it but of course they are not working.

After  a few minutes of disheartenment. I started searching around, and finally I found the community site for plugins here: http://code.google.com/p/dxcorecommunityplugins/ and after some more searching around I found out that:

Since version 9.1.1 the plugin folder is: C:\Program Files\DevExpress 2009.1\IDETools\Community\Plugins\ that means: Installfolder\IDETools\Community\Plugins\.

Since version 9.1.3 the plugin folder is: My docs\DevExpress\IDE Tools\Community\PlugIns that means:

Current User Documents\DevExpress\IDE Tools\Community\PlugIns

To avoid my fellows developers to loose two hours just to install a plugin I decided to post it here, in the hope of a better search engine indexing.

posted @ lunedì 8 marzo 2010 15.09 | Feedback (2)

FILESTREAM feature in SQL server 2008

One of the most important new features in SQL Server 2008 is certainly the possibility to use File System to store BLOB data. So if you work with images, binary files and so on and you need to store them in a SQL server database, it is possible to use this feature. It is important for those who are using SQL Server Express edition, because it avoids the problem of the 4GB maximum size for the database giving virtually no limit to the BLOB data growth.

Let’s see how to activate this feature on our server:

1. First step is to activate the feature at server protocol level, so, from Programs menu > Microsoft SQL Server 2008 > Configuration Tools – launch the SQL Server Configuration Manager (icon with the red toolbox).
• Select SQL Services in the left treeview
• On the right window, select SQL Server (MSSQLSERVER) for the default instance or the name of the instance to configure.
• Right click on it, and select properties.
• On the property window there is a series of TABS, select FILESTREAM, activate the T-SQL access and if necessary the API access and the remote client access (I haven’t done a full test so I don’t know if this is necessary, so you have to make some tests on this.)
2. Second step is in SQL Management Studio, connect to the server with an account with Sysadmin rights, on the server, right click and select properties, select Advanced on the left options list and on the first row of the property grid on the right part of the window, activate the Filestream option in T-SQL mode or Full Access.
3. Create a Database able to memorize data on FILESTREAM:

USE [master]
GO

/****** Object:  Database [Paperinik]    Script Date: 03/04/2010 18:09:07 ******/
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'Paperinik')
BEGIN
CREATE DATABASE [Paperinik] ON  PRIMARY 
( NAME = N'Paperinik', FILENAME = N'C:\SQL.DIR\Data\Test\Paperinik.mdf' , 
    SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ), 
 FILEGROUP [Binari] CONTAINS FILESTREAM  DEFAULT 
( NAME = N'Paperinik_Bin', FILENAME = N'c:\sql.dir\data\test\PaperinikFS\paperinik_bin' )
 LOG ON 
( NAME = N'Paperinik_log', FILENAME = N'C:\SQL.DIR\Data\Test\Paperinik_log.ldf' , 
   SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
END

this example made with my favourite database :D creates the database with the Paperinik.mdf as master data file and the file Paperinik_log.ldf as a log file. Adding a FILEGROUP named Binari which defines the FILESTREAM zone, telling the server which is the filesystem folder where to put the data.  in my case, c:\sql.dir\data\test\PaperinikFS\ pay attention that the Service account needs full control on this folder.
4. To proceed with test we need to create a table with a Varbinary(MAX) field with option FILESTREAM

   USE [Paperinik]
   GO
   
   CREATE TABLE [dbo].[TbFiles](
       [IDFile] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
       [IDFileSerial] [int] NOT NULL,
       [DDFile] [nvarchar](255) NULL,
       [FileData] [varbinary](max) FILESTREAM  NULL,
    CONSTRAINT [PK_TbFiles] PRIMARY KEY NONCLUSTERED 
   (
       [IDFile] ASC
   )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  =     OFF, 
       IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON,
       ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
   ) ON [PRIMARY] FILESTREAM_ON [Binari]
   GO
   
   USE [Paperinik]
   /****** Object:  Index [UQ_TbFileSerial]    Script Date: 03/04/2010 17:23:33 ******/
   CREATE UNIQUE CLUSTERED INDEX [UQ_TbFileSerial] ON [dbo].[TbFiles] 
   (
       [IDFileSerial] ASC
   )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
       SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, 
       DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, 
       ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] FILESTREAM_ON [Binari]
   GO

   In this example, I’ve created a table to memorize some files, even though in the example I put into the table something simpler, I will made some more test and let you know how it works later on this blog.
   To be able to contain a FILESTREAM field, it is mandatory that the table has a GUID (uniqueidentifier) ID field, to made it more easy I added an integer unique ID to be able to find things in a more fashionable way.
   
5. Now we need to insert the test data in the table, it is very simple using T-SQL:

INSERT INTO [Paperinik].[dbo].[TbFiles]
           ([IDFile]
           ,[IDFileSerial]
           ,[DDFile]
           ,[FileData])
     VALUES
           (NEWID()
           ,1
           ,'Nullo'
           ,null)
GO

INSERT INTO [Paperinik].[dbo].[TbFiles]
           ([IDFile]
           ,[IDFileSerial]
           ,[DDFile]
           ,[FileData])
     VALUES
           (NEWID()
           ,2
           ,'Vuoto'
           ,cast('' as varbinary(max)))
GO

INSERT INTO [Paperinik].[dbo].[TbFiles]
           ([IDFile]
           ,[IDFileSerial]
           ,[DDFile]
           ,[FileData])
     VALUES
           (NEWID()
           ,3
           ,'Stringa'
           ,cast('Questa stringa è binaria' as varbinary(max)))
GO

INSERT INTO [Paperinik].[dbo].[TbFiles]
           ([IDFile]
           ,[IDFileSerial]
           ,[DDFile]
           ,[FileData])
     VALUES
           (NEWID()
           ,4
           ,'La quarta'
           ,cast('Questa riga contiene questa stringa' as varbinary(max)))
GO

I’ve created four rows one with a NULL binary field, one with an empty string and two with a string inside.

If we open our root folder after this operation on c:\sql.dir\data\test\PaperinikFS\ we will find some new folders, with a GUID as name, digging into the content we will find some files with names like nnnnnnnn-nnnnnnnn-nnnn that when opened with notepad will show the data in our varbinary field, the NULL field has no file. I haven’t yet tried to memorize a binary file inside this field, I’ll tell you what happens in a future post.

What comes out in the end from this test? If we need to store unstructured data in a database, this can be a good solution, because SQL Server gives us transactions, logs, managed purge the possibility to activate indexing, I’ve to try Full text indexing on NON Sql Express servers, the possibility to backup these data as a normal file system directory, eliminates the 4GB database limit but pay attention, even though the BLOB data can be accessed from the file system it is not easy to find things inside this file system, it is very simple to find things and check out/check in binary data using the T-SQL or the direct acces APIs. So if you thought to use FILESYSTEM in SQL Server to be able to access data both from the database and directly from file system, this is not the right way.

If you want to build a database to index informations on your non structured data and be able to keep files on the filesystem, and use them directly from where they are stored, well, you have to build something manual, and put just the “Path” info inside your database.

posted @ giovedì 4 marzo 2010 19.19 | Feedback (0)

How to search text inside Views SQL definition on a SQL Server database

Scenario: There is a problem in SQL 2005 and 2008, due to Legacy functionalities of SQL 2000. In SQL 2000 it was possible to use an ORDER BY clause inside a VIEW putting a SELECT TOP 100 PERCENT in the View definition. Obviously We all used it massively :D, after the SP2 or SP3 in SQL 2005 this feature available for all databases set in SQL 2000 compatibility mode does not work anymore.

I've discovered this on a production site where a nice small Access Function Developed in 2001 stopped working on October 15 2009.

There is an Hot Fix (Made after SP2 but not contained in SP3) that fixes the problem available here, but of course it is opportune for us to find out and change all views using the Order By feature and modify then the code to order things after the View.

After the problem has been discovered and the fix made on the Function, the question was:

How many views with an Order By do I have in this database?

Searching on the internet I've found a nice small script on Stack overflow Forums so I put it here just in case:

SELECT 
    v.name, 
    m.definition 
FROM  
    sys.views v 
INNER JOIN  
    sys.sql_modules m ON v.object_ID = m.object_id 
WHERE m.definition LIKE '%TOP%'

This script allowed me to search the TOP clause in all my views definitions, and can be customized to search probably any piece of sql contained in all object definitions in a database.

Thanks to Mark S. for sharing it.

posted @ venerdì 29 gennaio 2010 12.28 | Feedback (0)

Some Database Modification tests

If you, like me, have the misfortune to work with very creative software architects :D:D:D, you probably need to write Database Modification scripts on a daily base. It is a good practice be sure that a script executed two times in a row doesn’t throw exceptions, mainly if the script can be executed by your customers DBA (or the customers themselves). I write down here some of the most common tests, you can use when you have to Add tables, Drop Tables, Add Columns, Drop Columns or Foreign Keys.

IF NOT EXISTS (
    SELECT 1 FROM sysobjects WHERE xtype='u' 
       AND name='MyTable')
    BEGIN
       CREATE TABLE Mytable …;
    END

Without the NOT this can be used also to Drop Tables.

IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = 
    OBJECT_ID('[FK_MyForeignKeyConstraintName]') 
    AND parent_object_id = OBJECT_ID('[MyTable]'))
    ALTER TABLE [MyTable] DROP CONSTRAINT [FK_MyForeignKeyConstraintName]

If you need to delete a table with Relations, it is better drop the Constraints before dropping the table. If you Add a Constraint you can set a NOT before Exists and avoid trying to create a constraint already present.

IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE object_id=object_id('MyTable') AND name='MyColumnName')
    ALTER TABLE MyTable ADD MyColumnName int NULL;

This one is useful when Adding, or without NOT, Dropping columns.

posted @ giovedì 31 dicembre 2009 11.43 | Feedback (0)

A snippet to send an e-mail through a SMTP Server

Today I ‘ve been developing a small application that, among other things, has the job to notify to some users the arrival of data from an ftp server.

The application watches the ftp folder directly using a file system watcher and when a File is received, puts it in the right place and it notifies the users of the arrival of the new data.

The notification is sent by an e-mail. Sending an e-mail using .NET  is very simple, but searching a snippet on the web I was not able to find a “simple sample” so I’ve written it here, just in case someone else needs it:

              //Send mail method
              public void Send( string pUser, string pPassword )
              {
                //Set the from, sender, replyto address
                MailAddress from = new MailAddress("noreply@mydomain.com");
                //Set the to address, if you have more recipients you can add them all
                //to the To, CC or BCC collections of MailMessage
                MailAddress to = new MailAddress("recipientuser@hisdomain.com");
                //Create the message
                MailMessage msg = new MailMessage();
                //Set from, sender and reply addresses
                msg.From = from;
                msg.Sender = from;
                msg.ReplyTo = from;
                //set the to address
                msg.To.Add(to);
                //set the subject
                msg.Subject = "Subject of my message";
                //set the priority
                msg.Priority =  MailPriority.Normal;
                //Indicate if it is an html or text message
                msg.IsBodyHtml = false;
                //Set the message body
                msg.Body = "Simple text message body";
                //Add a sample attachment
                if (File.Exists("c:\\myattachment.xml")
                {
                    msg.Attachments.Add(new Attachment("c:\\myattachment.xml"));
                }
                //Generate the smtp client indicating the server address
                SmtpClient smtp = new SmtpClient("mail.mydomain.local");
                //Set the method of delivery
                smtp.DeliveryMethod = SmtpDeliveryMethod.Network;
                //You can set also the Port if you need it
                                
                //If the server requires credentials, you can set them here
                if (pUser != null && pUser.Trim().Length > 0)
                {
                    smtp.UseDefaultCredentials = false;
                    smtp.Credentials = new NetworkCredential(pUser, pPassword);
                }
                //And now Send the message
                smtp.Send(msg);
               }    

If you need to send an HTML message, just change these two rows:

                //Indicate if it is an html or text message
                msg.IsBodyHtml = true;
                //Set the message body
                msg.Body = "<html><body><p>Simple HTML message body.</p></body></html>";

posted @ mercoledì 30 dicembre 2009 18.55 | Feedback (0)

Deactivate Automatic Loading of Controls in the Toolbox

If like me you own Visual Studio solutions with projects containing several Components (from 30 to 200), the Visual Studio Feature that automatically adds the Components as Tools in the toolbox every time you rebuild or open the solution, and every time you add or edit a new class, can result annoying, first because during this operation there aren’t hourglass cursors so it looks like Visual Studio is Hanged, second because the eventuality to be in the need to put all the controls on a form dragging them from the Toolbox is usually very rare. Except Perhaps to do some tests, anyway I think you are grown enough to be able to write a line of code to declare the component as member variable.

To disable the Automatic loading of the Controls you have to modify the Visual Studio Options this way:

Tools>Options>Windows Forms Designer> Toolbox> AutoToolboxPopulate = false.

Of course if instead you need this feature and it is not enabled, look at this option and change it to true.

I remember you that the classes this option consider are those derived from Control and Component.

Tags: VisualStudio Toolbox

posted @ domenica 27 dicembre 2009 21.24 | Feedback (0)

Shortcut generated by Setup of Visual Studio and ORCA

Sometimes the names chosen by Microsoft developers for applications make me think they are natives from my neighborhoods, in fact “orca miseria” is one of the favourite curses here where I live :D:D:D. In this case however the object named this way is very useful to avoid cursing.

ORCA has nothing to do with ORCAS, It is an editor made to look and edit what is inside a compiled msi file and it is part of the Windows Installer SDK.

I’ve discovered its existence by chance, searching a solution to an annoying problem, how to generate a shortcut in the SendTo folder of the user installing an application or the All Users SendTo folder. It is not possible using the Visual Studio .NET setup projects, because this kind of projects are able to generate only Advertised Shortcuts, these shortcuts got a different structure from the ones usually generated using the Right Key of the mouse both in XP and Vista (and I hope also in Windows Seven). It is a known problem since Visual Studio 2003 and part of a wish list since then, but it is not said the wish will ever come true.

Of course there is a workaround given by ORCA; After you generate an msi setup file, if you want it to generate NON Advertised shortcuts, we can achieve this opening the msi file with ORCA (I’ve found it here). Go to Property using the Left side List, right click the Right window and add a property as follows:

Name: DISABLEADVTSHORTCUTS  Value: 1

This way, the shortcuts are generated with the normal format, of course, this operation has to be done every time you rebuild the msi, which is a little bit annoying but very simple.

Tags: msi, Installer, Orca, Setup

posted @ domenica 27 dicembre 2009 21.00 | Feedback (0)

WdSeekView Word Interop

Title a post afrter an Enumeration is strange but I think this is the best way to index this post in the search engines. This post originates from my compassion for those who, like me today, have to work with Word and the .NET Interop libraries. This enumeration is quite important, because it allows to move the selection (thus the virtual cursor we move using code) through the various “Views” composing a document.

MSDN contains this enumeration through its pages, (here) exactly in the same way it contains all the knowledge on any library, class, object or whatever developed through time by the Microsoft Developers, but knowing well the developers, (my hand rises first) and their little inclination to documentation and description of things, the description of the enumeration options is the english plain writing of their names; unfortunately not enough to answer the questions raising in the head of those who need to use it in real coding.

Therefore, I had to develop a test application to understand how this thing works and being a nice human being… (well my fellow developers call me the snike :-P) I share the informations I found in the hope to help those who need to drive data writing in a Word document through .Net code.

A word document can be generated with  four different Headers and Footers types, that is:

1. Different for first page.
2. Different for first page and the even and odd pages.
3. Different for even and odd pages.
4. All the same.

To access the Headers and Footers content you need to write something like this:

        public Word.Range SelectWholeViewPlace(Word.WdSeekView pSeekPlace)
        {
            try
            {
                Doc.Activate();
                Word.Range rng = null;
                try
                {
                    Doc.Application.ActiveWindow.View.SeekView = pSeekPlace;
                    object units = Word.WdUnits.wdStory;
                    object extend = Word.WdMovementType.wdMove;
                    Doc.Application.Selection.HomeKey(ref units, ref extend);
                    extend = Word.WdMovementType.wdExtend;

                    Doc.Application.Selection.EndKey(ref units, ref extend);
                    rng = Doc.Application.Selection.Range;
                }
                catch (Exception)
                {
                    rng = null;
                }
                
                
                return (rng);
            }
            catch (Exception ex)
            {
                EventLogger.SendMsg(mClassName,
                    System.Reflection.MethodBase.GetCurrentMethod(), ex, MessageType.Error);
                throw new ApplicationException(GlobalConstants.TXT_SPACE + mClassName + GlobalConstants.TXT_DOT
                    + System.Reflection.MethodBase.GetCurrentMethod().Name, ex);
            }
        }

This method selects all content in one of the Word Document “Views”, and if the view does not exist (for example the document is in mode 4 with no Even and Odd or First page Headers and Footers returns a null Range object.

But which option do I need to use in which case? Here are the descriptions:

Have an happy Interop everybody.

posted @ domenica 27 dicembre 2009 20.33 | Feedback (0)

SQL Server, plain security

A colleague, moving it’s first steps into .NET and SQL Server to move it’s applications in 21st century, taking a look at a database I made asked me the following questions:

Why do I found the SQL User (MyUser) both in Server Security and in your Database? And Why did you tell me you granted permission to a Database Role (db_MyRole) instead of giving them to the user?

How the security works in SQL Server:

To answer his questions I’ve decided to explain the basics here in the hope to be helpful to more beginners.
In order to be able to connect an Application to SQL Server, the Application needs to know a Username and a Password (in Sql Server mode) or the Windows Domain User that uses the application or a Domain Group of which it is member needs to be mapped as a trusted User or Group in SQL Server.

This can be made manually by the SQL Server Administrator using SQL Management Studio and going to the Security Folder Using the Add Login feature, or it can be done using the following code in a query (connecting to the SQL Server with Administrator rights).

CREATE LOGIN [MyUser] WITH PASSWORD=N'MyPassword'
    , DEFAULT_DATABASE=[master]
    , DEFAULT_LANGUAGE=[us_english]
    , CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

CREATE LOGIN [MyWindowsUserOrGroup] 
    FROM WINDOWS WITH DEFAULT_DATABASE=[master]
    , DEFAULT_LANGUAGE=[us_english]

But this is not enough, with this credentials an Application can open a connection to the SQL Server, but the server gives no automatic access to it’s data to a new login.

To access a Database, the logins (Sql or trusted) first need to be mapped on the database (or databases), this can be achieved from SQL Management Studio, going to the Database Security Folder and adding the user, or through the Usermapping feature in the Login Mask on server security, otherwise it can be done using the following T-SQL queries.

CREATE USER [MyUser] 
    FOR LOGIN [MyUser] WITH DEFAULT_SCHEMA=[dbo]

CREATE USER [MyWindowsUserOrGroup] 
    FOR LOGIN [MyWindowsUserOrGroup] WITH DEFAULT_SCHEMA=[dbo]

After this operation is done, the application can connect to the server and access the database, but still it can’t see any data, because we didn’t grant it any permission on the database objects, such as Tables, Stored Procedures, Views, Functions and more.

We can grant permission on the objects to our logins in two ways, mapping them directly to the User or Windows User, generate a database Role, give permissions to the role and then assign the role to the users. The second way is the one I choose because a user can be changed for any reason, so the role can be passed to another user and we are sure the user has all permissions he needs. The role can also be assigned to more than one user and again we are sure the users have all the correct permissions and if we need to add some permissions or remove some permission we do it on the role and all users benefit of the modifications.

Again, this can be done both from the SQL management studio and by code.

USE [MyDatabase]
GO
CREATE ROLE [MyRole] AUTHORIZATION [sa]
GO
use [MyDatabase]
GO
GRANT SELECT ON [dbo].[MyTable1] TO [MyRole]
GO
use [MyDatabase]
GO
GRANT EXECUTE ON [dbo].[MySpSelect1] TO [MyRole]
GO
use [MyDatabase]
GO
GRANT EXECUTE ON [dbo].[MySpSelect2] TO [MyRole]
GO
use [MyDatabase]
GO
GRANT SELECT ON [dbo].[MyTable2] TO [MyRole]
GO

After the role is made, we made the users member of the role and we have reached our goal. Also this operation can be done using SSMS or the following query.

USE [FyRepository]
GO
EXEC sp_addrolemember N'MyRole', N'Myuser'
GO

In Short

To access the data of a SQL Server database, an Application needs to be able to Connect to the Server through a Login, the Login needs to be mapped as a Database User on the database (s) needed, and the Database User mapped needs to be member of one or more Database Roles that grant it permissions on the database objects.

posted @ domenica 29 novembre 2009 11.55 | Feedback (0)

How to generate rapidly a copy of a table in SQL Server

Today I've found a helpful little piece of SQL I wasn't aware of so I share this with the whole Internet in the hope to help someone else. The scenario was the following, I have a remote server from which I need to import a few tables on my local SQL Server for further processing. The Two servers are connected through a VPN. The connection is good but I can’t kill the band with a long run series of data transferts through the network. So I first set a linked server from the local machine to the remote server that allows me to query the remote server on the Database I'm interested in. Then, to transfer the table I was interested in I wrote this code:

USE MyNewLocalDb

SELECT FIELD1, FIELD2, FIELD3 .... FIELDn INTO dbo.MynewTable FROM
[MyRemoteServer].[MyRemoteDb].[Dbo].[MyremoteTable]

In this case I had just a simple select from table, but If you need to build the table from a a Join, in the from clause you can write a complex select with joins or whatever you need. The Table is very raw, but then you can set PK, indexes and whatever else you need on it.

Obviously you need to have the proper rights on the two databases to make this work, you have to be at least Db_Owner of the destination db and Db_Datareader on the source db.

posted @ giovedì 26 novembre 2009 11.35 | Feedback (0)

Who am i

Education

Diploma of Non graduate engineer specialization Industrial Electronics, graduate in 1984 at I.T.I. A. Malignani Udine.
Post diploma refresher courses in structured programming (C language) and Object Oriented programming (C++Language) at the Regional ENAIP institute.
Microsoft Official Curriculum courses to acquire certification as Microsoft certified professional, at Global Knowledge (Milan) and Teorema Engineering (Trieste).
Microsoft certified professional since 2001 with certification on software development, design and implementation of databases on SQL Server and Administration of databases on SQL Server.

Foreign Languages

English spoken and written, able to comprehend and carry on a conversation and translate even simultaneously italian to english and english to italian.

Professional Experiences

• 1987
  Developer, my job was to develop software to process data acquired through electronic timecard puncher clocks, using the first chip cards developed.
• 1988/89
  Analyst and developer, my job was to design and develop the firmware to manage the electronic timecard puncher clocks and the read and write of data on the chip cards.
  
• 1989/90
  Analyst and developer, my job was to develop vertical softwares to process and store data on databases in single pc systems.
• 1990/2000
  Analyst and developer, my job was to develop ERP software to acquire, store and process data on a network multiuser system. I also was a supervisor on local area network systems and in charge of tutoring users in the process of learning the use of the ERP software we produced. Installing and mantaining Servers and PC was also part of the job. at that time everyone knew everything on our customers Information technology systems.
• 2000/2004
  Analyst and developer, my job was to design and develop vertical software application made to integrate customer specific data processing needs with the general purpouse ERP systems used for accounting, warehouse and production management. The vertical softwares made many different jobs, some of them are: Connecting Production Management and ERP exchanging data in different formata; Integrate customer specific data not managed by the ERP system but needed by the company; Process data to build reporting systems for management team usage.
  I was also in charge of tutoring users in the process of learning the new ERP systems and the vertical softwares specifically developed.
  I've also developed a few ASP and ASP.Net small application mainly on internal or external websites.
• 2004/now
  Project manager, analyst and developer, my job was and is to develop software made to acquire, process, store accounting data, production data, performance data, and documents. Obviously there is not a single all purpouse software.
  We developed a Warehouse and production statistics management software, able to supply value added services to production managers and warehouse managers to be able to access all informations they need on past, present and future of the goods they manage. It is working since 2006.
  We are currently developing a knowledge management platform which is able to store and made available to users all documents issued from the company or received from the company automatically classified through company business rules created and managed by the companies managers with the help of our consulting division.
  We have also developed and already installed and tested a Management Reporting system which is able to collect, process and made available to the company managers all balance sheets and performance data, generate consolidated balances and give a wide variety of reports to satisfy the needs of decision makers.
  I am also responsible for the design and maintenance of the Databases used by the applications we develop and the DBA on several of our customers SQL Servers

Programming languages known and used in chronological order

• Assembler on Motorola family x80 (z80,64180 etc) microprocessors.
• C - on firmware development platform for x80 processors.
• DBIII on proprietary platform.
• DBIII on Clipper.
• C – on Microsoft development platforms from 2.0 to 7.0
• C++ - on  Microsoft VisualStudio 6.0 development platform
• Visual Basic - on Microsoft VisualStudio 6.0 development platform
• Visual Basic for Applications - on Microsoft Office development platform
• Javascript on web development in HTML and ASP
• Visual Basic - on ASP development platform
• Visual Basic .NET - on Microsoft Visual Studio .NET development platforms for development of Windows Froms and ASP.Net applications.
• Visual C# .NET - on Microsoft Visual Studio .NET development platforms for development of Windows Froms and ASP.Net applications.

Development systems known and used in chronological order

• C Compiler and Microprocessor emulator for motorola - 64180
• Clipper compiler for Database applications.
• Microsoft C Compilers from 2.0 to 7.0
• Visual Studio 6.0
• Visual Studio professional 2003
• Visual Studio professional 2005
• Visual Studio professional 2008
• Visual Studio Team Foundation Server 2008
• Visual Studio Team System 2008
• Visual Studio 2010
• Team Foundation Server 2010

Markup languages known and used to develop applications

• HTML
• XML
• XSLT
• CSS

Operational Systems known and deeply used through the years

• MS-DOS (da 2.0 a 6.x)
• Windows 3.x
• Windows'95
• Windows'98
• Windows ME
• Windows 2000 professional
• Windows XP professional
• Windows 2003 Server
• Windows Vista
• Novell Netware da 2.x a 4.x

General purpouse applications known and used through the years

• Microsoft Word da 2.0 a 2007
• Microsoft Excel da 1.0 a 2007
• Microsoft Powerpoint da 2000 a 2007
• Micrographics Designer
• Frontpage da '97 a 2000
• Internet Explorer 3.x and following
• Photoshop 5.5  and following
• Axialis Iconworkshop 5.0  and following
• Macromedia Dreamweaver 3.0  and following
• Microsoft Expression Web 1.0  and following
• Microsoft Groove

Interests and hobbies

Cinema, Pop music, Folk music, English language, Irish Gaelic, webs, blogs and hypertext language games. Sports: Wu Shu.

Tags: Info

posted @ domenica 8 novembre 2009 11.15 | Feedback (0)

Copyright © Sabrina C.

Design by Bartosz Brzezinski

Design by Phil Haack Based On A Design By Bartosz Brzezinski