Hi again.
It's a kind of tricky to save the dataset or datatable to database using SQLClient or OleDB objects.
Here is a sample from my DLL library. I strongly advise you to make these kind of stuff as functions in a dll, so that you easily use them in your projects without any effort.
In the function below connection object parameter is optional because if nothing comes to function I use dll class's connection instead.
I think I have to explain what is going on here.
Until the line of stars it's just connection issues.
TempDS is a temporary dataset. I am using it while I am getting shema to the data adapter. SaveDA.FillSchema is the important trick here. By getting the schema to data adapter I can save my dataset to the database. The SqlSelectStr parameter is something like "SELECT TOP 1 * FROM TargetTable"
Remember I don't need that data but I strongly need schema.
Then another most important part, the CommandBuilder line : it seems meaningless. We create the object but never use but this line is essential. If you omit it you can't save your dataset.
Good luck.
Public Sub SQLSaveData(ByVal DS As Data.DataSet, ByVal TableName As String, ByVal SqlSelectStr As String, Optional ByVal Connection As Data.OleDb.OleDbConnection = Nothing)
If Connection Is Nothing Then
Connection = Conn
End If
If Connection.State <> ConnectionState.Open Then
Try
Connection.Open()
Catch ex As Exception
MsgBox("Error in Database Connection. ", MsgBoxStyle.Critical, "Pool")
End Try
End If
' ***************************
Dim TempDS As New Data.DataSet
Dim SaveDA As New System.Data.OleDb.OleDbDataAdapter
SaveDA.SelectCommand = New System.Data.OleDb.OleDbCommand(SqlSelectStr, Connection)
SaveDA.FillSchema(TempDS, System.Data.SchemaType.Source, TableName)
SaveDA.Fill(TempDS, TableName)
Dim cb As New System.Data.OleDb.OleDbCommandBuilder(SaveDA)
SaveDA.Update(DS, TableName)
End Sub
Wednesday 15 February 2012
Data connection and a simple read in VB.NET
Hi again.
This time I'm writing down a simple code block. For a vb.net developer I embed those steps mostly to a dll file and never deal with those codes for about 4-5 years. Now I can't carry my dll to this job. So When I need to connect to a database I open my dll source codes and look for how I did it in the past.
It's again nothing new but keeping them around may be handy. This is a SqlClient type of connection but if you want to connect thru OleDB then you should change SqlClient stuff to OleDb stuff.
You should change red words with yours before you use, I painted connection object to blue to show where you should use
Dim Conn As New Data.SqlClient.SqlConnection("Password=DBPassword;User ID=DBUser;Initial Catalog=DatabaseName;Data Source=DBServerAddress;")
Dim DA As New Data.SqlClient.SqlDataAdapter
Dim DS As New Data.DataSet
Conn.Open()
DA.SelectCommand = New Data.SqlClient.SqlCommand("SELECT * FROM MyTable')", Conn)
DA.Fill(DS, "JustGiveAName")
Do something, do something
Do something, do something
Do something, do something
Do something, do something
DA = Nothing
Conn = Nothing
This time I'm writing down a simple code block. For a vb.net developer I embed those steps mostly to a dll file and never deal with those codes for about 4-5 years. Now I can't carry my dll to this job. So When I need to connect to a database I open my dll source codes and look for how I did it in the past.
It's again nothing new but keeping them around may be handy. This is a SqlClient type of connection but if you want to connect thru OleDB then you should change SqlClient stuff to OleDb stuff.
You should change red words with yours before you use, I painted connection object to blue to show where you should use
Dim Conn As New Data.SqlClient.SqlConnection("Password=DBPassword;User ID=DBUser;Initial Catalog=DatabaseName;Data Source=DBServerAddress;")
Dim DA As New Data.SqlClient.SqlDataAdapter
Dim DS As New Data.DataSet
Conn.Open()
DA.SelectCommand = New Data.SqlClient.SqlCommand("SELECT * FROM MyTable')", Conn)
DA.Fill(DS, "JustGiveAName")
Do something, do something
Do something, do something
Do something, do something
Do something, do something
DA = Nothing
Conn = Nothing
Wednesday 8 February 2012
Creating Custom ASP.NET Control using VB.NET
Today I'm developing a custom ASP.NET control.
It's usefull when you develop those libraries if you use them again and again. I know it makes you slow in the beginning but later you easily compansate that time. If you are a web developer and develope sites again and again how many times you deal with user authentication, authorisation kind of stuff. Copy paste can not be the exact solution many times.If you have other collegues working on the same project then copy paste operation sometimes can be a real mess. This time I'm developing Server Controls for a web-designer friend who has no knowledge about programing. He will open Visual Web Developer and install my server controls and insert them where he wants to. My controls here can create datatables, modify web.config and do everything he needed with just drag drop and property assignments. I also supply him an admin page to fill them in runtime. It will make my workload nearly zero. I only interfere in very complex or critical jobs.
Anyway, although I did this 4-5 years ago I couldn't remember most of the things. So I began with searching and reading articles about it. Then I started from zero.
1 - In Visual Studio I've started a new web project using ASP.NET Server Control project type
2 - In server controls there isn't any visual designer. so I have an empty code page. well not empty there are some imports.
3 - I started to build up a simple login screen.
4 - After some trials and fails I found a way to do it correctly. Here I attached the simplest code of it. I put some notes on important parts. You may use it as a template for beginning. I divide the code into regions to make it more understandable.
5 - Where I strugled most ?
Default Values. (look how I did in render section)
Event / Method stuff. ( I put comments on important parts)
Well it's just a beginning. Good Luck
BTW Adding an icon to the controls is tricky. Here is the link Microsoft explains how as walkthrough.
http://msdn.microsoft.com/en-us/library/yhzc935f(d=printer).aspx
I can't simply believe this. After reading and trying to implement those rubbish in Microsoft I discovered a very simple way to add icons to controls. here it is :
Public ReadOnly Property MenuItems() As List(Of MenuItem)
Get
Return _menuItems
End Get
End Property
End Class
<ToolboxItem(False)> _
<ParseChildren(True, "MenuItems")> _
Public Class MenuItem
Private _clientClick As String
Private _menuItems As New List(Of MenuItem)()
<Localizable(True)> _
Public Property Title() As String
Get
Return m_Title
End Get
Set(ByVal value As String)
m_Title = value
End Set
End Property
Private m_Title As String
Public Property Href() As String
Get
Return m_Href
End Get
Set(ByVal value As String)
m_Href = value
End Set
End Property
Private m_Href As String
Public Property Id() As String
Get
Return m_Id
End Get
Set(ByVal value As String)
m_Id = value
End Set
End Property
Private m_Id As String
<PersistenceMode(PersistenceMode.InnerDefaultProperty)> _
Public Property MenuItems() As List(Of MenuItem)
Get
Return _menuItems
End Get
Set(ByVal value As List(Of MenuItem))
_menuItems = value
End Set
End Property
It's usefull when you develop those libraries if you use them again and again. I know it makes you slow in the beginning but later you easily compansate that time. If you are a web developer and develope sites again and again how many times you deal with user authentication, authorisation kind of stuff. Copy paste can not be the exact solution many times.If you have other collegues working on the same project then copy paste operation sometimes can be a real mess. This time I'm developing Server Controls for a web-designer friend who has no knowledge about programing. He will open Visual Web Developer and install my server controls and insert them where he wants to. My controls here can create datatables, modify web.config and do everything he needed with just drag drop and property assignments. I also supply him an admin page to fill them in runtime. It will make my workload nearly zero. I only interfere in very complex or critical jobs.
1 - In Visual Studio I've started a new web project using ASP.NET Server Control project type
2 - In server controls there isn't any visual designer. so I have an empty code page. well not empty there are some imports.
3 - I started to build up a simple login screen.
4 - After some trials and fails I found a way to do it correctly. Here I attached the simplest code of it. I put some notes on important parts. You may use it as a template for beginning. I divide the code into regions to make it more understandable.
5 - Where I strugled most ?
Default Values. (look how I did in render section)
Event / Method stuff. ( I put comments on important parts)
Well it's just a beginning. Good Luck
BTW Adding an icon to the controls is tricky. Here is the link Microsoft explains how as walkthrough.
http://msdn.microsoft.com/en-us/library/yhzc935f(d=printer).aspx
I can't simply believe this. After reading and trying to implement those rubbish in Microsoft I discovered a very simple way to add icons to controls. here it is :
- Right click project in solurtion explorer and choose add existing item
- select a 16x16 bmp image
- Rename it and make the image name and control name same.
- Click image once in Solution explorer and change the Build Action property to Embedded Resource
After digging internet and books here are the tips you may hard to find.
Using URL as property
To use a URL path as a property you need to add reference to System.Design.DLL first.
Then you should import System.Drawing.Design at the top of the page like :
Imports System.Drawing.Design
Then here is a sample code to do the job
<EditorAttribute(GetType(System.Web.UI.Design.UrlEditor), GetType(UITypeEditor))> _
Public Property URL() As String
Get
Return http_url
End Get
Set(ByVal value As String)
http_url = value
End Set
End Property
Public Property URL() As String
Get
Return http_url
End Get
Set(ByVal value As String)
http_url = value
End Set
End Property
Private http_url As String
And when you click this property you'll get this screen
Menu like Items Property (nested)
This is one another though job to do while you're developing an ASP.NET server control. If you want an Items box for user to fill items you should do something like this.
It took so much time to find the exact solution.
Here is the complete code block
Private _menuItems As New List(Of MenuItem)()
<PersistenceMode(PersistenceMode.InnerProperty)> _Public ReadOnly Property MenuItems() As List(Of MenuItem)
Get
Return _menuItems
End Get
End Property
End Class
<ToolboxItem(False)> _
<ParseChildren(True, "MenuItems")> _
Public Class MenuItem
Private _clientClick As String
Private _menuItems As New List(Of MenuItem)()
<Localizable(True)> _
Public Property Title() As String
Get
Return m_Title
End Get
Set(ByVal value As String)
m_Title = value
End Set
End Property
Private m_Title As String
Public Property Href() As String
Get
Return m_Href
End Get
Set(ByVal value As String)
m_Href = value
End Set
End Property
Private m_Href As String
Public Property Id() As String
Get
Return m_Id
End Get
Set(ByVal value As String)
m_Id = value
End Set
End Property
Private m_Id As String
<PersistenceMode(PersistenceMode.InnerDefaultProperty)> _
Public Property MenuItems() As List(Of MenuItem)
Get
Return _menuItems
End Get
Set(ByVal value As List(Of MenuItem))
_menuItems = value
End Set
End Property
When you click that small button near MenuItem you are now have this screen to make the entry easier.:
Tuesday 7 February 2012
Some Usefull Code Samples For VB.NET Developers
Here I wrote some very useful code samples and some are in form of functions. Hope you like.
This function creates an empty Access database
Public Shared Function CreateAccessDatabase(ByVal DatabaseFullPath As String) As Boolean
Dim bAns As Boolean
Dim cat As New ADOX.Catalog()
Try
Dim sCreateString As String
sCreateString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DatabaseFullPath
cat.Create(sCreateString)
bAns = True
Catch Excep As System.Runtime.InteropServices.COMException
bAns = False
Finally
cat = Nothing
End Try
Return bAns
End Function
Collection Class Sample
Public Class DataPairCollection
Inherits System.Collections.CollectionBase
Public Sub Add(ByVal NewPair As DataPair)
List.Add(NewPair)
End Sub
Public Sub Add(ByVal NDataName As Object, ByVal NScreenVal As Object)
List.Add(New DataPair(NDataName, NScreenVal))
End Sub
End Class
Public Class DataPair
Public DataName As Object
Public ScreenVal As Object
Public Sub New(ByVal NDataName As Object, ByVal NScreenVal As Object)
DataName = NDataName
ScreenVal = NScreenVal
End Sub
End Class
Dynamically Create Object From String
Public Function DynamicallyLoadedObject(ByVal objectName As String, Optional ByVal args() As Object = Nothing) As Object
Dim returnObj As Object = Nothing
Dim type As Type = Assembly.GetExecutingAssembly().GetType(Application.ProductName & "." & objectName)
If Not type Is Nothing Then
returnObj = Activator.CreateInstance(type, args)
End If
Return returnObj
End Function
This function creates an empty Access database
Public Shared Function CreateAccessDatabase(ByVal DatabaseFullPath As String) As Boolean
Dim bAns As Boolean
Dim cat As New ADOX.Catalog()
Try
Dim sCreateString As String
sCreateString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DatabaseFullPath
cat.Create(sCreateString)
bAns = True
Catch Excep As System.Runtime.InteropServices.COMException
bAns = False
Finally
cat = Nothing
End Try
Return bAns
End Function
Collection Class Sample
Public Class DataPairCollection
Inherits System.Collections.CollectionBase
Public Sub Add(ByVal NewPair As DataPair)
List.Add(NewPair)
End Sub
Public Sub Add(ByVal NDataName As Object, ByVal NScreenVal As Object)
List.Add(New DataPair(NDataName, NScreenVal))
End Sub
End Class
Public Class DataPair
Public DataName As Object
Public ScreenVal As Object
Public Sub New(ByVal NDataName As Object, ByVal NScreenVal As Object)
DataName = NDataName
ScreenVal = NScreenVal
End Sub
End Class
Dynamically Create Object From String
Public Function DynamicallyLoadedObject(ByVal objectName As String, Optional ByVal args() As Object = Nothing) As Object
Dim returnObj As Object = Nothing
Dim type As Type = Assembly.GetExecutingAssembly().GetType(Application.ProductName & "." & objectName)
If Not type Is Nothing Then
returnObj = Activator.CreateInstance(type, args)
End If
Return returnObj
End Function
Some Utilities Make Your Life Easier
My installation of a new computer takes so much time. I always hesitate to format and install my development computer.I have many utilities that I want under my hand.
Here I'm introducing some of them;
My top utility is editplus it's actually a simple text editor but it can load large text files . When you begin to use this editor it's hard to quit. Because there are features for web developers and also it's handy when you just want a quick peek your specific codes in a large project. The evaluation period actually never ends but if you want to get rid of the registration screen here is a link for you
Number two is Microsoft Image Composer. it's a very old program. I think it was distrubuted with Frontpage 98 CD set. Then I don't know why but Microsoft stop distributing it. It's basically an image editor without layers. Photoshop is always a bit complex for me. To get quick results I prefer to use it for years.
MS Access. This is a must have one. I use this for many purposes along use as a simple database. For example I always keep an empty Access Database in my web mail. To transfer MS SQL tables it's handy to export them to access and carry those exports in mdb format.
I especially use MS Access for one specific occasion. When I develop a multi user windows desktop application with multilanguage features. I keep language dictionaries in MS Access in clients. So I don't go to MS SQL over network for translations and don't create traffic over network.
ColorPicker is a simple one I like. Just activate it and put mouse cursour on an image to see the color codes of that pixel. If you develop a web application without any graphics support this small software can save your life.
The Snippet Editor. This small software can make you write your codes nearly two times faster if you use Visual Studio. You can download it free on MSDN Site which I linked. You can add new snippets to visual studio and after you write 2-3 letters and pressing tab can bring lines of code.
I had developped some utilities for myself too. I have one for database - screen fields matcher. I use it when I develop database applications. I wish I can give it to you but it's completely reflects my coding characteristics. If you read my naming style. I use a slightly different way of ID naming in database tables.
I shared my Folder Watcher before.
I also have a large DLL. There are Database, communication, file system, image and many other libraries in it. Whenever I face a problem I always think if this is a one time problem or can I face again in the future. If I decide that I can face again I put the code to my DLL. So it grows day by day. But I may say I forgat how to save screen fields to database bacause I use my own functions to do this easily. I suggest you to develop this kind of library for yourself. development time can be longer in the begining but later you'll code incredibly fast. Also you will use it in other development projects. without any effort.
I also have some reporting tools which I developed to create reports in distance. This tool has commercial value, thats why I can't share it with you.
A Rich Text Editor is essential in developing ASP.NET sites isn't it. Here is the link for you. It's free and also you can download the source codes as well. http://rte.codeplex.com/
I will continue to write others in time.
Here I'm introducing some of them;
My top utility is editplus it's actually a simple text editor but it can load large text files . When you begin to use this editor it's hard to quit. Because there are features for web developers and also it's handy when you just want a quick peek your specific codes in a large project. The evaluation period actually never ends but if you want to get rid of the registration screen here is a link for you
Number two is Microsoft Image Composer. it's a very old program. I think it was distrubuted with Frontpage 98 CD set. Then I don't know why but Microsoft stop distributing it. It's basically an image editor without layers. Photoshop is always a bit complex for me. To get quick results I prefer to use it for years.
MS Access. This is a must have one. I use this for many purposes along use as a simple database. For example I always keep an empty Access Database in my web mail. To transfer MS SQL tables it's handy to export them to access and carry those exports in mdb format.
I especially use MS Access for one specific occasion. When I develop a multi user windows desktop application with multilanguage features. I keep language dictionaries in MS Access in clients. So I don't go to MS SQL over network for translations and don't create traffic over network.
ColorPicker is a simple one I like. Just activate it and put mouse cursour on an image to see the color codes of that pixel. If you develop a web application without any graphics support this small software can save your life.
The Snippet Editor. This small software can make you write your codes nearly two times faster if you use Visual Studio. You can download it free on MSDN Site which I linked. You can add new snippets to visual studio and after you write 2-3 letters and pressing tab can bring lines of code.
I had developped some utilities for myself too. I have one for database - screen fields matcher. I use it when I develop database applications. I wish I can give it to you but it's completely reflects my coding characteristics. If you read my naming style. I use a slightly different way of ID naming in database tables.
I shared my Folder Watcher before.
I also have a large DLL. There are Database, communication, file system, image and many other libraries in it. Whenever I face a problem I always think if this is a one time problem or can I face again in the future. If I decide that I can face again I put the code to my DLL. So it grows day by day. But I may say I forgat how to save screen fields to database bacause I use my own functions to do this easily. I suggest you to develop this kind of library for yourself. development time can be longer in the begining but later you'll code incredibly fast. Also you will use it in other development projects. without any effort.
I also have some reporting tools which I developed to create reports in distance. This tool has commercial value, thats why I can't share it with you.
A Rich Text Editor is essential in developing ASP.NET sites isn't it. Here is the link for you. It's free and also you can download the source codes as well. http://rte.codeplex.com/
I will continue to write others in time.
Friday 3 February 2012
Object Oriented vs Object Spagetti
Novadays I'm dealing with some other developer's software. It's a bit old software. Nobody here knows who wrote the codes. It's written in .NET Framework 1.1 and using Visual Studio 2003.
Yes yes I know migrate the codes to current framwork instead of talking you're saying probably. Wait a little and be patient my friends.
I think the developer was new to .NET and probably this is his first codes in .NET. You know when .NET first introduced "Object Orientation" was the first think people talked. But as I always say "you have to balance the theory and practice". Technology is a kind of fashion for male programmers.
"Ohh this year Object Oriented Programming is so popular",
"Are you still using tables... I am using divs "
"3 Tier application is old fashioned"
If you are old enough you may know server strategies in time. Once there were 1 huge server for everything Then people think that it's dangerous to keep all the eggs in one basket. We switch to multi server environment. File Server, Mail Server, Database Server, Domain Server.... Then again somehow we changed our mind and a huge server came back with the virtual servers.
Lets come back to our subject. I am digging the codes to understand why he did something like a spagetti.
A process started in an application and I follow the codes in 10 different functions in 5 different application written in 2 different languages PLUS Stored Procedures, using user defined nested functions in 4 level. It's like an adventure. I never know where I'm going to end when I start from a point.
My first manager told me some golden rules of IT.
1 - One day we may die. Write the codes clear, understandable, standardised and documented. so that one other programmer can continue your job immedialtely
2 - Always keep it simple. This is not an arena for you to show your talents.
3 - Updating a code is harder. Always write codes to change anything easily.
I will tell you the rest of them in future. But he is right. You may write in C# and VB but you don't need to use both of them in one project. (Actually I don't understand why we need to use C# anyway.)
Now I'll tell what I did to add a simple field to a screen.
I opened the insert screen and added a textbox
I opened the database and added the field
I opened the codes (in VB) of the insert screen and try to add this field to save buttons click event. but I realized that the screen values are loaded into variables and sended to a functions in a dll.
I found the dll application and open the source and began editing.(dll was in C# )
I follow the dll and saw there is another reference to another dll which is directly related to insert operation.
I opend other dll application and update the lines. But I saw that the screen values now sended to a stored procedure as parameters.
I open SQL server and updated stored procedure.
Finally I finished the insert but I also did the similar things for the display and update screens (I don't know why but there were 2 screens for insert and updates.
So now I have to compile them in order and include the dll's to main application.
Come on.. This is just a waste of time. It's the third challanging job in my carrier.
1 - I saw a man storing the data like this : first he creates the line then he converts the line into binary then he wrote to a database table. it's true and he did it for the whole application and NO the data was not sensitive. I realized that he used his binary files for his database needs for his whole life. When He found SQL server in front of him he didn't understand about query, relation etc. He did everything with his best knowledge.
2 - There was a ERP system written by a rookie. Front screens and gui was good. but in codes and database it was unbelievible. ID fields were character based. If you want to create a relation between 3 table you had to know which characters are the link part of the which table. Even worse part was he had no idea about cross reference tables, he had no clue about field naming. Also he had no idea about object orientation he repeat many code parts in many places in his code.
After them this is less challanging for me. :)
Final Word : Please my friends. Keep your codes simple and updatable. Reading standards will take your 2 hours maximum. But will save days in the future.
Yes yes I know migrate the codes to current framwork instead of talking you're saying probably. Wait a little and be patient my friends.
I think the developer was new to .NET and probably this is his first codes in .NET. You know when .NET first introduced "Object Orientation" was the first think people talked. But as I always say "you have to balance the theory and practice". Technology is a kind of fashion for male programmers.
"Ohh this year Object Oriented Programming is so popular",
"Are you still using tables... I am using divs "
"3 Tier application is old fashioned"
If you are old enough you may know server strategies in time. Once there were 1 huge server for everything Then people think that it's dangerous to keep all the eggs in one basket. We switch to multi server environment. File Server, Mail Server, Database Server, Domain Server.... Then again somehow we changed our mind and a huge server came back with the virtual servers.
Lets come back to our subject. I am digging the codes to understand why he did something like a spagetti.
A process started in an application and I follow the codes in 10 different functions in 5 different application written in 2 different languages PLUS Stored Procedures, using user defined nested functions in 4 level. It's like an adventure. I never know where I'm going to end when I start from a point.
My first manager told me some golden rules of IT.
1 - One day we may die. Write the codes clear, understandable, standardised and documented. so that one other programmer can continue your job immedialtely
2 - Always keep it simple. This is not an arena for you to show your talents.
3 - Updating a code is harder. Always write codes to change anything easily.
I will tell you the rest of them in future. But he is right. You may write in C# and VB but you don't need to use both of them in one project. (Actually I don't understand why we need to use C# anyway.)
Now I'll tell what I did to add a simple field to a screen.
I opened the insert screen and added a textbox
I opened the database and added the field
I opened the codes (in VB) of the insert screen and try to add this field to save buttons click event. but I realized that the screen values are loaded into variables and sended to a functions in a dll.
I found the dll application and open the source and began editing.(dll was in C# )
I follow the dll and saw there is another reference to another dll which is directly related to insert operation.
I opend other dll application and update the lines. But I saw that the screen values now sended to a stored procedure as parameters.
I open SQL server and updated stored procedure.
Finally I finished the insert but I also did the similar things for the display and update screens (I don't know why but there were 2 screens for insert and updates.
So now I have to compile them in order and include the dll's to main application.
Come on.. This is just a waste of time. It's the third challanging job in my carrier.
1 - I saw a man storing the data like this : first he creates the line then he converts the line into binary then he wrote to a database table. it's true and he did it for the whole application and NO the data was not sensitive. I realized that he used his binary files for his database needs for his whole life. When He found SQL server in front of him he didn't understand about query, relation etc. He did everything with his best knowledge.
2 - There was a ERP system written by a rookie. Front screens and gui was good. but in codes and database it was unbelievible. ID fields were character based. If you want to create a relation between 3 table you had to know which characters are the link part of the which table. Even worse part was he had no idea about cross reference tables, he had no clue about field naming. Also he had no idea about object orientation he repeat many code parts in many places in his code.
After them this is less challanging for me. :)
Final Word : Please my friends. Keep your codes simple and updatable. Reading standards will take your 2 hours maximum. But will save days in the future.
Subscribe to:
Posts (Atom)