Mohammed Atef’s Technical blog

Running windows command shell using xp_cmdshell

Introduction

In this post i am going to share with you how to use xp_cmdshell stored procedure for running windows command shell.

xp_cmdshell is system stored procedure that used to run Windows command shell and passes in a string for execution. Any output is returned as rows of text.

Synatx

xp_cmdshell { ‘command_string’ } [ , no_output ]

where command_string Is the string that contains a command to be passed to the operating system. command_string is varchar(8000) or nvarchar(4000) and no_output Is an optional parameter, specifying that no output should be returned to the client.

Enable XP_cmdshell

If you tried to run this stored procedure and you have error says, access denied, you have to enable xp_cmdshell using below SQL script.

   1: EXEC
   2: sp_configure 'show advanced options', 1
   3: GO
   4: RECONFIGURE
   5: GO
   6: EXEC
   7: sp_configure 'xp_cmdshell', 1
   8: GO
   9: RECONFIGURE
  10: GO

Executing vb application without parameter

If you have a .net windows console application named helloworld.exe and it requires to run it from SQL you can use below list of SQL script

   1: declare @cmd varchar(500)
   2: select @param1=rolename,@param2=solutionname from roles where id in
   3: set @cmd='cmd /C "C:\helloworld.exe'
   4: exec xp_cmdshell  @cmd

Executing vb application with two parameters

otherwise, if you have an exe requires two parameters to run it, you can use below SQL Script.

   1: declare @cmd varchar(500)
   2: declare @param1 varchar(50)
   3: declare @param2 varchar(50)
   4: select @param1='param1',@param2='param2'
   5: set @cmd='cmd /C "C:\helloworld.exe '+@param1+' '+@param2+'"'
   6: exec xp_cmdshell  @cmd

Conclusion

above post we saw how to use xp_cmdshell with different modes of needs, hope it is helped.

May 30, 2013 Posted by | .Net 2010, C#,VB.Net, Developement | , , , , | Leave a comment

Create Generic Collection with Generic Binding Method for Dropdown List

Introduction

At this post i am going to show you how to create generic collection class with generic method for binding any Dropdown List.

Solution

I will describe my code in steps as follows

Step one Create Generic class

here we will create generic class as shown below

   1: Imports Microsoft.VisualBasic
   2: Public Class GenericCollection(Of T)
   3:     Inherits CollectionBase
   4:     Public Function Add(ByVal value As T) As Integer
   5:         Return List.Add(value)
   6:     End Function
   7:     Public Sub Remove(ByVal value As T)
   8:         List.Remove(value)
   9:     End Sub
  10:     Public ReadOnly Property Item(ByVal index As Integer) As T
  11:         Get
  12:             Return CType(List.Item(index), T)
  13:         End Get
  14:     End Property
  15: End Class

In the above code you need to note the below step:

1- Our class inherits from CollectionBase

2- Add method take any kind of object T and add it to collection list

3- Remove method delete kind of object T and remove it from collection list

4- Item property return object of T

Step two Create Generic Method to Bind any Dropdown List

find code for this method here

   1: Public Shared Sub FillDDL(ByRef ddl As DropDownList, ByVal col As CollectionBase, ByVal strval As String, ByVal strname As String)
   2:         ddl.DataSource = col
   3:         ddl.DataTextField = strname
   4:         ddl.DataValueField = strval
   5:         ddl.DataBind()
   6:     End Sub

I think any one can understand the above code but note that col parameter is from try collectionbase to path any type of object class.

Step Three how to use this code

find code for using Generic class and method

   1: Dim col As GenericCollection(Of Priority) = priorityprov.GetAllPrioritysGeneric()
   2: CtrlHelper.FillDDL(ddlpriority, col, "PriorityID", "PriorityArName")

in the above code assume the following

1- CtrlHelper is the class which contain FillDDL generic method

2- GetAllPrioritysGeneric() is method return genericcollection of type Priority

conclusion

at the end now you have common class for any collection and common method for binding any Dropdown List in any solution.

I hope this helped

October 6, 2009 Posted by | Asp.net, C#,VB.Net | , | 1 Comment

Master Details GridView with Details inside same GridView

Introduction

I have been worked for implementing Gridview which contain linkbutton in each row that link must be show user details in same row using Detail View Control.

Solution

I have developer normal Gridview and did the following steps

1-drag GridView and Detail view control into my page

2- bind Gridview in page load event

3- add 3 template fields and one command select field

4-add labels with unique names in each template field

5-add the following code in GridView RowCommand Event
 

   1: If e.CommandName = "Select" Then
   2:             Dim userid As String = CType(GridView1.Rows(Integer.Parse(e.CommandArgument)).Cells(0).FindControl("lbluserid"), Label).Text
   3:             DetailsView1.DataSource = GenerateUserDetTable(GenerateUserDetTable(Nothing).Select("userID=" + userid)(0))
   4:             DetailsView1.DataBind()
   5:             GridView1.Rows(Integer.Parse(e.CommandArgument)).Cells.RemoveAt(0)
   6:             GridView1.Rows(Integer.Parse(e.CommandArgument)).Cells.RemoveAt(1)
   7:             GridView1.Rows(Integer.Parse(e.CommandArgument)).Cells(0).Text = ""
   8:             GridView1.Rows(Integer.Parse(e.CommandArgument)).Cells(0).ColumnSpan = 2
   9:             GridView1.Rows(Integer.Parse(e.CommandArgument)).Cells(0).HorizontalAlign = HorizontalAlign.Center
  10:             GridView1.Rows(Integer.Parse(e.CommandArgument)).Cells(0).Controls.Add(DetailsView1)
  11:             Dim lnk As LinkButton
  12:             lnk = New LinkButton
  13:             lnk.CommandName = "Hide"
  14:             lnk.Text = "Hide"
  15:             GridView1.Rows(Integer.Parse(e.CommandArgument)).Cells(1).Text = ""
  16:             GridView1.Rows(Integer.Parse(e.CommandArgument)).Cells(1).Controls.Add(lnk)
  17:         End If

Understanding Code

the whole idea is to remove not needed cells and reset the Column span for the remaining cell and adding detailView into this cell lets see how you can do this

at line 1 i get user is from row that user click show linkbutton there

at lines 5,6 i have removed not needed cells

at lines 8,9,10 i have update remaining cell properties and add Detailview to it

at lines 11 to 16 i have create new linkbutton and replace it with the Show link button

note that GenerateUserDetTable is a method that generating UserDetails Table

you can download the whole project from here


Hope that helped.

September 30, 2009 Posted by | .Net 2010, C#,VB.Net | , , , , , | Leave a comment

Generate and Compile Csharp files using CodeDOM

Hello, I was trying to create .net utility to merge two CSharp files or more into one CSharp file, while working into this task I had use CodeDOM, really it’s very useful .So, I want to give you simple brief about CodeDOM.

CodeDOM

The CodeDOM provides types that represent many common types of source code elements. You can design a program that builds a source code model using CodeDOM . The CodeDOM can also be used to compile source code into a binary assembly.

Common uses

1- Template code generation: generating code for ASP.NET, XML Web services client proxies, code wizards, designers, or other code-emitting mechanisms.
2- Dynamic compilation: supporting code compilation in single or multiple languages.

After understanding CodeDOM let’s learn how to use CodeDOM to generate CSharp class and compile the generated source code into assembly.
It’s very simple if you are programmer and you want to write class you will follow steps.
First step
Create NameSpace and and import class NameSpaces.

 

 

 

CodeNamespace namespaceObj=new CodeNamespace();

 

namespaceObj.Imports.Add(new CodeNamespaceImport(“System”));

 

namespaceObj.Imports.Add(new CodeNamespaceImport(“System.Xml”));

Second step
Create Class or Type.

 

 

 

CodeTypeDeclaration clsobj = new CodeTypeDeclaration();

 

clsobj.Name = “Classname”;

 

clsobj.IsClass = true;           

 

clsobj.IsEnum = false;

 

clsobj.TypeAttributes = TypeAttributes.Public|TypeAttributes.Serializable; 

 

namespaceobj.Types.Add(cls);

Third Step

Create property and member

 

 

 

CodeTypeReference teftype = new CodeTypeReference(System.String.ToString());

 

CodeMemberField memberfield = new CodeMemberField();

 

memberfield.Type = teftype;

 

memberfield.Name = “firstname”;

 

memberfield.Attributes = MemberAttributes.Public;

 

clsobj.Members.Add(memberfield);

//create property

 

 

 

CodeMemberProperty property = new CodeMemberProperty();

 

property.Name = “Firstname”;

 

CodeTypeReference teftype = new CodeTypeReference(System.String.ToString());

 

property.Type = teftype;                       

 

property.Attributes = MemberAttributes.Public;           

 

clsobj.Members.Add(property);

 

CodeSnippetExpression getsnippet = new CodeSnippetExpression(“return firstname”);

 

CodeSnippetExpression setsnippet = new CodeSnippetExpression(“firstname=value”);

 

property.GetStatements.Add(getsnippet);

 

property.SetStatements.Add(setsnippet);

Last Step
Compile code and create new assembly

 

 

 

 

CodeCompileUnit assembly = new CodeCompileUnit();

 

assembly.Namespaces.Add(namespaceobj);

 

CSharpCodeProvider prov = new CSharpCodeProvider();

 

ICodeCompiler icc = prov.CreateCompiler();    

 

CompilerParameters options = new CompilerParameters();

 

options.GenerateInMemory = true;

 

options.ReferencedAssemblies.Add(“System.Xml.dll”);

 

options.ReferencedAssemblies.Add(“System.dll”);                   

 

 

options.OutputAssembly = “c:\\Temp\Sample.dll”;

 

options.GenerateExecutable = false;

 

options.IncludeDebugInformation = false;

 

CompilerResults compres = icc.CompileAssemblyFromDom(options, assembly); 

 

The previous code used when you need to generate new CSharp class and compile it.
But sometimes you need to compile existing CSharp class!!
Don’t worry you can do this task using this few line of code

 

 

 

 

CodeNamespace namespaceObj=new CodeNamespace();

 

namespaceObj.Imports.Add(new CodeNamespaceImport(“System”));

 

namespaceObj.Imports.Add(new CodeNamespaceImport(“System.Xml”));

 

Stream codeFile = File.Open(“c:\\Temp\Sample.cs”,FileMode.Create);

 

StreamWriter sw = new StreamWriter(codeFile);

 

CSharpCodeProvider cscp = new CSharpCodeProvider();

 

ICodeGenerator codeGenerator = cscp.CreateGenerator(sw);

 

CodeGeneratorOptions cgo = new CodeGeneratorOptions();

 

codeGenerator.GenerateCodeFromNamespace(namespaceobj, sw, cgo);           

 

sw.Close();

 

codeFile.Close();

 

 

Conclusion
The System.CodeDom namespace in .net allows the users to dynamically compile and create assemblies, I hope this article is helpful for you.

 

 

February 19, 2009 Posted by | C#,VB.Net, Developement | , | Leave a comment

Send Email with attachment file

With the few line of codes you can send an e-Mail with attachment file. via .net application

Dim data As New Attachment(strFileName, System.Net.Mime.MediaTypeNames.Application.Octet)

‘Add time stamp information for the file.

Dim disposition As System.Net.Mime.ContentDisposition

disposition = data.ContentDisposition

disposition.CreationDate = System.IO.File.GetCreationTime(strFileName)

disposition.ModificationDate = System.IO.File.GetLastWriteTime(strFileName)

disposition.ReadDate = System.IO.File.GetLastAccessTime(strFileName)

‘ Add the file attachment to this e-mail message.

myMessage.Attachments.Add(data)

myMessage.Priority = MailPriority.High

‘Send the message.

Dim client As New System.Net.Mail.SmtpClient(“10.0.0.27”)

‘Add credentials if the SMTP server requires them.

client.Credentials = New System.Net.NetworkCredential(“matef”, “123”)

client.Send(myMessage)

 

February 8, 2009 Posted by | Asp.net, C#,VB.Net, Developement | | Leave a comment