JaeYa Blog

This is another walkthrough, like my last post, demonstrating how to use some new Visual Studio 2010 features available in the October VS2010 CTP. It provides steps to follow in the CTP build; however, you may find that you are able to give feedback on the descriptions below without actually downloading and executing the steps yourself. Please leave your feedback on this feature set at the end of this post, or at the following forum:

http://social.msdn.microsoft.com/Forums/en-US/vs2010ctpvbcs/thread/6172efc9-3075-4426-a773-cf2504f51dca

Thanks!
Lisa

Walkthrough: Office Programmability in Visual Basic and C#

This walkthrough demonstrates several new features in Visual Basic and C#. This walkthrough focuses on how the new features, when used in conjunction, can greatly simplify Office development, although most of these new features are useful in other contexts.

In this walkthrough, you will create a class that represents a bank account. You will then create collections of objects of that class. Next, you will create an Office Excel worksheet, populate it with data from a collection, and embed the worksheet into an Office Word document. Finally, you will modify the build process so that end users can run your program without having the Office primary interop assembly (PIA) installed on their computers.

This walkthrough demonstrates the following new language features:

New features in Visual Basic:

• Auto-implemented properties
• Statement lambdas
• Collection initializers
• Implicit line continuation

New features in Visual C#:

• Optional and named parameters
• Optional ref parameter modifier
• Dynamic dispatch on COM calls returning Object

New feature in both languages:

• No-PIA deployment

Prerequisites: You must have Excel 2007 and Word 2007 installed on your computer to complete this walkthrough.

To create a new console application

1. On the File menu, point to New and then click Project. In the New Project dialog box, in the Project types pane, expand Visual Basic or Visual C#, and then click Windows. In the upper right-hand corner, make sure that .NET Framework 4.0 is selected. Then click Console Application and click OK.

2. In Solution Explorer, right-click the project node and then click Add Reference. On the .NET tab, select Microsoft.Office.Interop.Excel, version 12.0. Hold down CTRL and click Microsoft.Office.Interop.Word, version 12.0.

3. Click OK to close the Add Reference dialog box.

To create the bank account class

In this section, you will create a simple class that represents a bank account.

1. In Solution Explorer, right-click the project node, point to Add, and then click Class to open the Add New Item dialog box. Name the file Account.vb (for Visual Basic) or Account.cs (for C#) and click Add.

2. Add the following code to the new class. Note that when you declare a property, it is no longer necessary to also create an explicit backing field because the compiler will add one automatically. This is called an auto-implemented property, and it is new to Visual Basic 10.0:

Visual Basic code

Visual C# code

Note: Be sure to delete any namespace declarations before pasting in this code. To simplify the rest of the walkthrough, the Account class should be outside of any namespace.

To import the Office namespaces:

There is nothing new in this step. You are just adding Imports statements or using directives so that you do not have to fully qualify the names of the Excel and Word objects each time you reference them.

At the top of the Module1.vb or Program.cs file, add the following code:

Visual Basic code

Visual C# code

To add data to the account class

This step demonstrates collection initializers, which provide a convenient and expressive way to populate a collection like a list or array with elements when you first create the object. This feature was introduced in C# in Visual Studio 2008 and is introduced in Visual Basic in Visual Studio 2010.

In the Main method of your application, add the following code:

To display the account data in Excel

This step demonstrates how to create a new Excel workbook and populate it with data from the List<Account> or List (Of Account) that was initialized in the previous step. Action is a delegate type; several Action delegates are defined that have differing numbers of input parameters, but they all return void. In a later step, you will use a statement lambda when calling DisplayInExcel to supply the inline method that matches the Action delegate signature.

1. Declare the DisplayInExcel method, as shown in the following code:

2. At the bottom of the Main method, call the DisplayInExcel method by using the following code. Note the use of the statement lambda, which colors the Excel cell red if the balance is negative.

3. To automatically adjust the width of these columns to fit their contents, insert the following code at the end of the DisplayInExcel method:

Notice that the AutoFit method is being called on the result of the indexed call to Columns, which has a type of Object. Return values of type Object from COM hosts such as Office are automatically treated as Dynamic in C# 4.0, which allows dynamic dispatch (late binding) and avoids the casts that would be required in C# 3.0:

Visual C# code

To embed the Excel spreadsheet into a Word document

In this step, you will create an instance of Word and paste a link to the Excel worksheet into the document. There is nothing new in the Visual Basic code, because Visual Basic has supported named and optional parameters for a long time. Note, however, that C# 4.0 now supports this feature. The PasteSpecial method actually has seven parameters, but they are all optional, so in C# it is no longer necessary to supply arguments for all parameters.

Insert the following code at the end of the Main method:

Finally, in the definition for PasteSpecial, note that all of its parameters are ByRef (ref in C#). C# 4.0 allows you to make calls to COM components without having to specify ref in front of each parameter. What can now be done in one line of code used to take about 15 (for this particular function) in C# 3.0:

To run the application

• Press F5 to run the application. First, Excel will open and display a worksheet. Next, Word will open and display a document that contains an embedded link to the Excel worksheet. It should look something like this:

To remove the PIA dependency

1. Start a Visual Studio command prompt (from the Visual Studio Tools folder on the Start menu). Type ildasm and press ENTER. Open your assembly. (It will be in your project’s bin directory, by default: My Documents\Visual Studio 10\Projects\project name\bin)

2. Double-click Manifest. You should see the following entry for Excel in the list. (There will also be a similar entry for Word.)

This is an assembly reference to the Excel primary interop assembly (PIA). Because this assembly is referenced by your application, it needs to exist on the end user's computer.

3. The No-PIA feature enables you to compile your application in such a way that references to a PIA are no longer required; the compiler will import whatever types you use from the PIA into your own assembly. This results in a much smaller assembly and easier deployment; the PIAs no longer have to be present on the user's computer. Also, this application can now work with multiple versions of Office (because it does not require a specific version of a PIA).

4. In Solution Explorer, click the Show All References button. Expand the References folder and select Microsoft.Office.Interop.Excel. Press F4 to display the Properties window.

5. Change the Embed Interop Types property from False to True.

6. Repeat step 5 for Microsoft.Office.Interop.Word.

7. Be sure to close Ildasm. Press F5 to rebuild the project. Verify that everything still runs correctly.

8. Repeat steps 1 and 2. Notice that this time the entries for Excel and Word are gone. You no longer need to distribute these PIA DLLs to the user’s computer.


http://blogs.msdn.com/b/vbteam/archive/2008/12/15/walkthrough-office-programmability-in-visual-basic-and-c-in-vs-2010-lisa-feigenbaum.aspx

뭔가 하나만 하면 될 것 같았는데

역시나 OPENQUERY로 인한 문제가 있었다.ㅜㅜ

MS-SQL에서 정의된 SP에서 ORACLE DB에 데이터 삽입이 필요해서 OPENQUERY를 사용하였는데

이게 이렇게 큰 걸림돌이 될줄이야 생각도 못했다.

Multi Query 실행 시 오류가 발생했을 때 Rollback 시킬려는 기능을 추가하기 위해

쥥일 이거만 잡고 있었는데 퇴근 30분전에 이게 무슨 쾌삽질이란 말인가.ㅡㅡ;;

이런 우라질레이션.. 오늘 꼭 해결하고 말테다!! 자 오늘 저녁은 뭘로 먹을까.. 야근 준비하자.!!! 아뾰!!

정답을 아시는 분 살포시 답변 부탁드립니다. (--)(__)

노트북을 샀는데 가방이 없다. 아무리 무겁다고 해도 그래도 들고는 다녀야 하지 않겠는가.!!

4kg의 무게를 견딜만한 녀석을 크로스 백에 넣고 다니는건 아~~주 무리가 있어보여서 백팩을 찾던 중

눈에 들어오는 한녀석 바로 Incase제품의 백팩 이였다. 음.. 에어리언 가방은 너무 무식하게 크고 아동틱(?)한

에어리언 얼굴이 싫어서 바로 제외. 비슷한 가격으로 따진다면 Incase를 추천하고 싶다.

한국 Incase에서는 이 제품은 모두 품절이다. 뭐.. 닉쿤 가방이라서 불티나게 팔렸다나 뭐라나. 나는 우결은 보지 않는 관계로

몰랐지만 이 정도일 줄은 몰랐다. 음냐 제질이며, 디자인, 수납 공간을 보면 오홍!! 아이패드와 같이 이런저런 부속품과 함께

개인 물품까지 동반하며 들고다닐 수 있다는게 딱 이거다 싶어서 바로 질렀다.ㅡㅡ;;

ebay에서 $118에 구매를 할 수 있었으며, 진품인지 가품인지는 받아보면 알 것이고 이제 낡은 가방은 버려야겠다..음하하.!!

이 녀석들로 인해 나의 Car에 대한 꿈은 무너져 버렸다는..ㅜㅜ 다시 돈 모으자.. 꾸역꾸역...

참.. 국제 배송도 가능하니 한국에서 구매 하실분은 언릉 잡으셔요!! 제품 2개 남아있던데요.ㅋㅋ

배송비 포함해도 한국에서 구매대행하는 것 보다 싸다는 장점.!!!

폴더형 데탑이라는 말이 어울리는 노트북이다. 바로 Dell사의 Alienware M15x이다.

그런데 무게가 4kg... (너..넌 뭐냐!! 우루사를 좀 먹어줘야 겠군..)

예전부터 눈독 들이고 있던 녀석인데 가격의 압박에 시달리다가 이번에 드디어 구입을 하게 되었다.

현재 Dell에서는 $1880에 팔리고 있는 녀석을 $300이상의 Discount event를 하고 있다. 오오~~ 쁘라보!!

그러나 구입은 한국 Dell에서..ㅡㅡ;; 므~~냐!!

아무튼 A/S나 사양등을 비교하면 한국에서 사는 가격이랑 비슷해진다는거.

사양은 대충 이렇다. (Core i7 840QM, 4GB, 320GB, Graphic 1GB, FHD지원 등등 계약서 꺼내기 귀찮아..ㅜㅜ)

이제 데탑이여 안녕~~!! 그냥 집에서 서버로 쓰기에는 사양이 아깝고 전기세 나가고 해서 입양 보내기로 결정을 했다. Bye Bye~

개발도 해야하고 게임도 해야하고 데탑, 회사 노트북 왔다갔다 하는 것도 귀찮고 해서 질렀다.. 후회가 없기를..ㅠㅠ

근데 욘석은 참으로 웃긴게 견적서를 받고 주문을 하면 그때서야 제작에 들어간다는거다. 한마디로 결제를 해야

노트북이 생산에 들어간다는거다.. 허허..

아..그래두 빨리 와라!! 무거워도 좋다!! 내가 얼마든지 들고 다녀주마~~ 젊으니깐 캬캬캬~

2주후 개봉기를 기대하시라.~~

 

현재 진행중인 프로젝에서 MSSQL Server 2008을 사용하고 있다.

그외의 mysql, oracle 등등 외부 서버가 기세등등 대기 하고 있지만 웬지 친근감이 들지 않는다ㅋㅋ

가끔 오로코로 보고 싶은 녀석들이 있을때 외친다. Hey~~ come on!!  또 헛소리를 ㅜㅜ

SQL Server에서는 Linked Servers 방식을 이용해서 Oracle에 접근하여 데이터를 가져오도록 설정이 되어있다.

지금까지 그냥 아무생각 없이 OPENQUERY() 를 사용하여 쑥쑥 가져왔다. 음.. 음.. 음..

과연 performance는 어땠을까.??  So Bad!!!!!!!

자 그럼 어떤 점이 문제가 있었는지 간단한 예제를 통해서 알아보자.


예) Insert OPENQUERY (TEST, 'select * from test_query')
     value ('first_name', 'last_name', 'address1', 'address2')

결과는 자~~~알 된다. ㅡㅡa

그런데 한가지 문제가 있다!! 바로 Oracle의 test_query 테이블 전체를 쭉 긁어 버린다. 허허

난 단지 데이터를 Insert를 할려고 하는데 전체 Select라.. 이건 불필요한거다.

자자 이래서 이런 것들은

예) Insert OPENQUERY (TEST, 'select * from test_query where 1=0')
     value ('first_name', 'last_name', 'address1', 'address2')

으로 where 1=0 절을 사용함으로써 Oracle에서 실제 Select가 일어나지 않게 하는 것이다.

그리고 Delete나, Update같은 경우 OPENQUERY()문 안에 조건 밑 기능을 추가하여야 한다.

왜?? 그렇게 해야 좋으니깐..ㅋㅋ

에헴.. 더 자세한 내용들은 다음 시간에 생각나면 올리도록 할께요.~~

또 다시 미국에 입성하다..^^/

한국의 시원한 가을 날씨를 뒤로 한채 다시 미국행 비행기에 몸을 싣고 열심히 날아왔지만

나를 반기는건 따닷한 소세지로 온몸을 마구 후려치는 듯한 후덥지근한 날씨뿐이였다..ㅜㅜ

하지만 Jennifer, Warren이 마중을 나와 주신 덕분에 편하게 회사로 갈 수 있었다. Thanks!!

그렇게 하루의 시간은 모두 지나가고 어느덧 하늘은 붉게 물들어 있었다.

입에서는 "와~~"라는 감탄사 뿐 다른 말은 필요가 없었다. Califonia의 저녁 노을은 언제 봐도 아름답다..ㅠㅠ

나의 오오댕디로 붉은 하늘을 담았보았다. 오오~~~ 멋져!!!

이제 또 다른 시작이라구나!! 힘내봅세다~ㅎ

그렇게 자주 안쓰는 구문이라 가끔 쓰게 되면 깜빡하게 된다..ㅜㅜ

Column names

If a column name contains any of these special characters ~ ( ) # \ / = > < + - * % & | ^ ' " [ ], you must enclose the column name within square brackets [ ]. If a column name contains right bracket ] or backslash \, escape it with backslash (\] or \\).

[C#]

dataView.RowFilter = "id = 10";      // no special character in column name "id"
dataView.RowFilter = "$id = 10";     // no special character in column name "$id"
dataView.RowFilter = "[#id] = 10";   // special character "#" in column name "#id"
dataView.RowFilter = "[[id\]] = 10"; // special characters in column name "[id]"

Literals

String values are enclosed within single quotes ' '. If the string contains single quote ', the quote must be doubled.

[C#]

dataView.RowFilter = "Name = 'John'"        // string value
dataView.RowFilter = "Name = 'John ''A'''"  // string with single quotes "John 'A'"

dataView.RowFilter = String.Format("Name = '{0}'", "John 'A'".Replace("'", "''"));

Number values are not enclosed within any characters. The values should be the same as is the result of int.ToString() or float.ToString() method for invariant or English culture.

[C#]

dataView.RowFilter = "Year = 2008"          // integer value
dataView.RowFilter = "Price = 1199.9"       // float value

dataView.RowFilter = String.Format(CultureInfo.InvariantCulture.NumberFormat,
                     "Price = {0}", 1199.9f);

Date values are enclosed within sharp characters # #. The date format is the same as is the result of DateTime.ToString() method for invariant or English culture.

[C#]

dataView.RowFilter = "Date = #12/31/2008#"          // date value (time is 00:00:00)
dataView.RowFilter = "Date = #2008-12-31#"          // also this format is supported
dataView.RowFilter = "Date = #12/31/2008 16:44:58#" // date and time value

dataView.RowFilter = String.Format(CultureInfo.InvariantCulture.DateTimeFormat,
                     "Date = #{0}#", new DateTime(2008, 12, 31, 16, 44, 58));

Alternatively you can enclose all values within single quotes ' '. It means you can use string values for numbers or date time values. In this case the current culture is used to convert the string to the specific value.

[C#]

dataView.RowFilter = "Date = '12/31/2008 16:44:58'" // if current culture is English
dataView.RowFilter = "Date = '31.12.2008 16:44:58'" // if current culture is German

dataView.RowFilter = "Price = '1199.90'"            // if current culture is English
dataView.RowFilter = "Price = '1199,90'"            // if current culture is German

Comparison operators

Equal, not equal, less, greater operators are used to include only values that suit to a comparison expression. You can use these operators = <> < <= > >=.

Note: String comparison is culture-sensitive, it uses CultureInfo from DataTable.Localeproperty of related table (dataView.Table.Locale). If the property is not explicitly set, its default value is DataSet.Locale (and its default value is current system culture Thread.Curren­tThread.Curren tCulture).

[C#]

dataView.RowFilter = "Num = 10"             // number is equal to 10
dataView.RowFilter = "Date < #1/1/2008#"    // date is less than 1/1/2008
dataView.RowFilter = "Name <> 'John'"       // string is not equal to 'John'
dataView.RowFilter = "Name >= 'Jo'"         // string comparison

Operator IN is used to include only values from the list. You can use the operator for all data types, such as numbers or strings.

[C#]

dataView.RowFilter = "Id IN (1, 2, 3)"                    // integer values
dataView.RowFilter = "Price IN (1.0, 9.9, 11.5)"          // float values
dataView.RowFilter = "Name IN ('John', 'Jim', 'Tom')"     // string values
dataView.RowFilter = "Date IN (#12/31/2008#, #1/1/2009#)" // date time values

dataView.RowFilter = "Id NOT IN (1, 2, 3)"  // values not from the list

Operator LIKE is used to include only values that match a pattern with wildcards. Wildcardcharacter is * or %, it can be at the beginning of a pattern '*value', at the end 'value*', or at both '*value*'. Wildcard in the middle of a patern 'va*lue' is not allowed.

[C#]

dataView.RowFilter = "Name LIKE 'j*'"       // values that start with 'j'
dataView.RowFilter = "Name LIKE '%jo%'"     // values that contain 'jo'

dataView.RowFilter = "Name NOT LIKE 'j*'"   // values that don't start with 'j'

If a pattern in a LIKE clause contains any of these special characters * % [ ], those characters must be escaped in brackets [ ] like this [*], [%], [[] or []].

[C#]

dataView.RowFilter = "Name LIKE '[*]*'"     // values that starts with '*'
dataView.RowFilter = "Name LIKE '[[]*'"     // values that starts with '['

The following method escapes a text value for usage in a LIKE clause.

[C#]

public static string EscapeLikeValue(string valueWithoutWildcards)
{
  StringBuilder sb = new StringBuilder();
  for (int i = 0; i < valueWithoutWildcards.Length; i++)
  {
    char c = valueWithoutWildcards[i];
    if (c == '*' || c == '%' || c == '[' || c == ']')
      sb.Append("[").Append(c).Append("]");
    else if (c == '\'')
      sb.Append("''");
    else
      sb.Append(c);
  }
  return sb.ToString();
}

[C#]

// select all that starts with the value string (in this case with "*")
string value = "*";
// the dataView.RowFilter will be: "Name LIKE '[*]*'"
dataView.RowFilter = String.Format("Name LIKE '{0}*'", EscapeLikeValue(value));

Boolean operators

Boolean operators AND, OR and NOT are used to concatenate expressions. Operator NOT has precedence over AND operator and it has precedence over OR operator.

[C#]

// operator AND has precedence over OR operator, parenthesis are needed
dataView.RowFilter = "City = 'Tokyo' AND (Age < 20 OR Age > 60)";

// following examples do the same
dataView.RowFilter = "City <> 'Tokyo' AND City <> 'Paris'";
dataView.RowFilter = "NOT City = 'Tokyo' AND NOT City = 'Paris'";
dataView.RowFilter = "NOT (City = 'Tokyo' OR City = 'Paris')";
dataView.RowFilter = "City NOT IN ('Tokyo', 'Paris')";

Arithmetic and string operators

Arithmetic operators are addition +, subtraction -, multiplication *, division / and modulus %.

[C#]

dataView.RowFilter = "MotherAge - Age < 20";   // people with young mother
dataView.RowFilter = "Age % 10 = 0";           // people with decennial birthday

There is also one string operator concatenation +.

Parent-Child Relation Referencing

A parent table can be referenced in an expression using parent column name with Parent.prefix. A column in a child table can be referenced using child column name with Child. prefix.

The reference to the child column must be in an aggregate function because child relationships may return multiple rows. For example expression SUM(Child.Price) returns sum of all prices in child table related to the row in parent table.

If a table has more than one child relation, the prefix must contain relation name. For example expression Child(OrdersToItemsRelation).Price references to column Price in child table using relation named OrdersToItemsRe lation.

Aggregate Functions

There are supported following aggregate functions SUM, COUNT, MIN, MAX, AVG (average), STDEV(statistical standard deviation) and VAR (statistical variance).

This example shows aggregate function performed on a single table.

[C#]

// select people with above-average salary
dataView.RowFilter = "Salary > AVG(Salary)";

Following example shows aggregate functions performed on two tables which have parent-child relation. Suppose there are tables Orders and Items with the parent-child relation.

[C#]

// select orders which have more than 5 items
dataView.RowFilter = "COUNT(Child.IdOrder) > 5";

// select orders which total price (sum of items prices) is greater or equal $500
dataView.RowFilter = "SUM(Child.Price) >= 500";

 

By  http://dotnet.fibo.us/?p=331