Social Icons

Pages

Monday, December 23, 2013

Solving SharePoint Server configuration issues

503. The service is unavailable, after installation
Quite a simple fix for this error
First go to IIS and click on Application Pools
Then right click on SharePoint Central Administration and open the Advanced Settings
In the Advanced Settings screen, change the identity details which is typically located under the Process Model (Process Model--Identity--Click--Application Pool Identity-- Current Account--Set--Enter details)
Click OK right click on SharePoint Central Administration, Start.

Resolve HTTP 500 Internal Server Error in SharePoint 2013 Site 

Wednesday, December 4, 2013

Common table expressions

Common table expressions (CTE) introduced in SQL Server 2005. It is a temporary result set that can be used within the scope of another query or a data manipulation statement, such as an INSERT, UPDATE , DELETE,Create View statement. This table's contents can be referenced multiple times in the main query or update command.

References:
http://www.blackwasp.co.uk/SQLCTEs.aspx
http://blog.sqlauthority.com/2008/07/28/sql-server-simple-example-of-recursive-cte/
http://blog.sqlauthority.com/2009/08/08/sql-server-multiple-cte-in-one-select-statement-query/

Accessing SQL Server through C# - Beginners guide

In this article, I am going to demonstrate how to access SQL server through C#.

Adding Namespaces 
 
      using System.Data.SqlClient; // For the database connections and objects. 

Inserting & Retrieving data using CommandText

      public void CommandTextConnectionTest()
        {
            try
            {
                string ConnectionString = "ConnectionString";

                SqlConnection sqlConnection = new SqlConnection();
                SqlCommand sqlCommand = new SqlCommand();
                sqlConnection.ConnectionString = ConnectionString;//
                sqlConnection.Open();
                sqlCommand.Connection = sqlConnection;

                //Insert Data
                //The ExecuteNonQuery method is ideal for when you are inserting data
                sqlCommand.CommandType = CommandType.Text;
                sqlCommand.CommandText = "INSERT INTO Student values('" + "StudentID" + "','" + "StudentName" + "','" + "Address" + "','" + "Tpnum" + "')";
                if (sqlCommand.ExecuteNonQuery() > 0)
                {
                    MessageBox.Show("Successfully Inserted");
                }

                //Retrive Data
                //SqlDataReader is a fast way to read table data
                sqlCommand.CommandText = "select * from student where name='" + "StudentID" + "'";
                sqlCommand.CommandType = CommandType.Text;
                SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
                while (sqlDataReader.Read())
                {
                    student.StudentID = sqlDataReader["StudentID"].ToString();// Assign StudentID to student class
                    student.StudentName = Convert.ToInt32(sqlDataReader["StudentName"]);

                }
                sqlConnection.Close();
            }
            catch (Exception)
            {
               
                 MessageBox.Show("Error");
            }

        }
 Inserting data using Stored procedure 

     public void StoredProcedureConnectionTest()
        {
            try
            {
                string ConnectionString = "ConnectionString";
                SqlConnection sqlConnection = new SqlConnection();
                SqlTransaction sqlTransaction;
                SqlCommand sqlCommand = new SqlCommand();
                sqlConnection.Open();
                sqlCommand.Connection = sqlConnection;
                sqlTransaction = sqlConnection.BeginTransaction();
                {
                    sqlCommand.Connection = sqlTransaction.Connection;
                    sqlCommand.Transaction = sqlTransaction;
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.CommandText = "SP_SPNAME";
                    //Adding Parameters
                    sqlCommand.Parameters.AddWithValue("@Parameters1", "Parameters1 value");
                    sqlCommand.Parameters.AddWithValue("@Parameters2", "@Parameters2 value");
                    //Adding Return Parameter
                    sqlCommand.Parameters.Add("@ReturnParameter", SqlDbType.VarChar, 50).Direction = ParameterDirection.Output;
                    sqlCommand.ExecuteNonQuery();
                    string reurnValue = (sqlCommand.Parameters["@Str_Service_out_Invoice"].Value.ToString());
                }
                sqlTransaction.Commit();
                sqlConnection.Close();
                MessageBox.Show("Successfully Inserted");
            }

            catch (Exception ex)
            {
                MessageBox.Show("Error");
            }

        }
Retrieving data using Stored procedure 

    public DataTable StoredProcedureConnectionTest()
        {
            try
            {
                string ConnectionString = "ConnectionString";
                SqlConnection sqlConnection = new SqlConnection();
                sqlConnection.Open();
                //The using statement is excellent for handling important system resources
                using (SqlCommand sqlCommand = new SqlCommand())
                {
                    sqlCommand.CommandText = "SP_SPNAME";
                    sqlCommand.CommandType = CommandType.StoredProcedure;

                    //Adding Parameter1
                    SqlParameter SqlParameter1 = new SqlParameter("@SqlParameter1", "SqlParameter1value1");
                    sqlCommand.Parameters.Add(SqlParameter1);
                    //Adding Parameter2
                    SqlParameter SqlParameter2 = new SqlParameter("@SqlParameter2", "SqlParameter1value2");
                    sqlCommand.Parameters.Add(SqlParameter1);

                    DataTable dataTable = new DataTable();
                    SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
                    sqlDataAdapter.Fill(dataTable);
                    return dataTable;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error");
                return null;
            }

        }
References :
http://www.dotnetperls.com/sqlclient

Thursday, November 14, 2013

SQL Data Archive

Storing obsolete data reduces the performance of a database server. A well-designed archival strategy minimizes the performance problems that are caused by maintaining excess data.

Archiving is the process of getting rid of obsolete data from the main production databases. By archiving data, you can improve query performance, decrease the disk space usage and reduce the maintenance window. From a performance point of view, if a production database has obsolete data that is never or rarely used, query execution can be time-consuming. This is because queries also scan the obsolete data. To improve query performance, you can move the obsolete data from the production database to another server.
Some of the main reasons for archive:
•  Improve query performance
•  Decrease the disk space usage
•  Faster backup and restore times
•  Easier manageability
•  Better user experience

By archiving old data, you can keep your databases smaller and more manageable. With smaller tables, your index rebuilds times and backup/restore times will be under control.When designing an archival strategy, you should first determine how much data can be archived, then choose an appropriate structure for the archival data, and finally create a plan for archiving the data.

There is no built-in command or tool for archiving databases. It’s depending on the business needs.
Method 1:
Create a separate script file that will move data from live database to Archive database and delete data from live database.
For example we have records from 2000 to 2013.We can keep data from 2005 to till date and move older data to archive database, maintain a table for archive dates and change the SP in such a way. When we pass dates to SP, it will check with the dates in Archive date table and if the date is less than the archive date then bring the data from archive database otherwise get data from live database.

Method 2:
SQL Partition
Partitioning is a physical database design technique. Partitioning refers to splitting what is logically one large table into smaller physical pieces.Main goal is to reduce the amount of data read for particular SQL operations so that overall response time is reduced.
Partition function can only be created in Enterprise edition of SQL Server. Only Enterprise edition of SQL Server supports partitioning.

References :

Friday, November 1, 2013

Generate and write to excel file in C#

This article provides a very simple method to generate excel and write data to spreed sheet.

Below is a sample code snippet showing a method to generate a excel and write data to spreed sheet.I have used Microsoft.Office.Interop.Excel to do it using C#

public void GenerateExcel()
        {
            try
            {
                Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
                if (app == null)
                {
                    MessageBox.Show("EXCEL could not be started. Check that your office installation.");
                    return;
                }
                // creating new WorkBook within Excel application
                Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);

                // creating new Excelsheet in workbook
                Microsoft.Office.Interop.Excel._Worksheet worksheet = null;

                // see the excel sheet behind the program

                // get the reference of first sheet. By default its name is Sheet1.
                // store its reference to worksheet
                worksheet = workbook.Sheets["Sheet1"];
                worksheet = workbook.ActiveSheet;
                if (worksheet == null)
                {
                    MessageBox.Show("Worksheet could not be created. Check that your office installation and project references are correct.");
                }
                // changing the name of active sheet
                worksheet.Name = "Test sheet";

                DataTable dtTest = new DataTable();
                // Add column headings...
                int iCol = 0;
                foreach (DataColumn c in dtTest.Columns)
                {
                    iCol++;
                    worksheet.Cells[1, iCol] = c.ColumnName;
                    // Adding Borders
                    worksheet.Cells[1, iCol].Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
                    //adding Interior color
                    worksheet.Cells[1, iCol].Interior.Color = Color.Yellow;
                }

                // for each row of data...
                int iRow = 0;
                foreach (DataRow r in dtTest.Rows)
                {
                    iRow++;
                    // add each row's cell data...
                    iCol = 0;
                    foreach (DataColumn c in dtTest.Columns)
                    {
                        iCol++;
                        worksheet.Cells[iRow + 1, iCol] = r[c.ColumnName];
                        // Adding Borders
                        worksheet.Cells[iRow + 1, iCol].Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
                        //Change the number format
                        worksheet.Cells[iRow + 1, iCol].NumberFormat = "0.00";
                    }
                }
                app.Visible = true;
                MessageBox.Show("Completed!", "Export");
            }
            catch (Exception ex )
            {
                
                MessageBox.Show(ex.Message, "Error");
            }

Thursday, September 12, 2013

FTP and file handling in C#

In this article I am going to discuss some helpful code snippets in C# to accomplish FTP File operations. I hope this will make your coding easier.

To connect to the FTP server we can use the FtpWebRequest C# object under the System.Net namespace.

Check whether file exists
private bool FtpDirectoryExists(string Directory, string Username, string Password)
        {
            //Directory = "ftp://" + FTPpath + "/" + Selectedfile;
            FtpWebRequest fwr = (FtpWebRequest)WebRequest.Create(Directory);
            FtpWebResponse fRes = default(FtpWebResponse);
            fwr.Credentials = new NetworkCredential(Username, Password);
            fwr.Method = WebRequestMethods.Ftp.GetFileSize;
            try
            {
                fRes = (FtpWebResponse)fwr.GetResponse();
                //no error occured then the file is exists 
                return true;
            }
            catch (WebException ex)
            {
                fRes = (FtpWebResponse)ex.Response;
                //Error occured then the file doesn't exists 
                if (FtpStatusCode.ActionNotTakenFileUnavailable == fRes.StatusCode)
                {
                    return false;
                }
                else
                {
                    //Any other errors you need to handle here 
                    return false;
                }
            }
        }
 Listing all file Names
public DataTable GetFTPFile(string Directory, string Username, string Password)
        {
            DataTable dtFtpFileDetails = new DataTable();
            dtFtpFileDetails.Columns.Add("FileName", typeof(string));

            try
            {
                //Get Directory Details
                FtpWebRequest ftpClientdir;
                ftpClientdir = (FtpWebRequest)FtpWebRequest.Create(Directory);
                ftpClientdir.Credentials = new NetworkCredential(Username, Password);
                ftpClientdir.Method = WebRequestMethods.Ftp.ListDirectoryDetails;
                WebResponse response = ftpClientdir.GetResponse();
                StreamReader reader = new StreamReader(response.GetResponseStream());

                //File names
                FtpWebRequest ftpClient;
                ftpClient = (FtpWebRequest)FtpWebRequest.Create(Directory);
                ftpClient.Credentials = new NetworkCredential(Username, Password);
                ftpClient.Method = WebRequestMethods.Ftp.ListDirectory;
                WebResponse response2 = ftpClient.GetResponse();
                StreamReader reader2 = new StreamReader(response2.GetResponseStream());

                //read file/directory names into arraylist
                string lsdirectory = reader2.ReadLine();
                ArrayList lsnames = new ArrayList();
                while (lsdirectory != null)
                {
                    lsnames.Add(lsdirectory);
                    lsdirectory = reader2.ReadLine();
                }

                //read through directory details response
                string line = reader.ReadLine();
                while (line != null)
                {
                    if (!line.StartsWith("d") && !line.EndsWith(".")) //"d" = dir don't need "." or ".." dirs
                    {
                        foreach (String chk in lsnames) //compare basic dir output to detail dir output to get dir name
                        {
                            if (line.EndsWith(chk))
                            {
                                DataRow dr = dtFtpFileDetails.NewRow();
                                dr["FileName"] = chk;
                                dtFtpFileDetails.Rows.Add(dr);
                            }
                        }
                    }
                    line = reader.ReadLine();
                }
                return dtFtpFileDetails;
            }
            catch (Exception)
            {
                return dtFtpFileDetails;
            }
        }
Download Files

public bool downloadfile(string Directory, string Username, string Password, string Localpath, string filename)
        {
            try
            {
                string localPath = Localpath + "\\";
                FtpWebRequest requestFileDownload = (FtpWebRequest)WebRequest.Create(Directory + "/" + filename);
                requestFileDownload.Credentials = new NetworkCredential(Username, Password);
                requestFileDownload.Method = WebRequestMethods.Ftp.DownloadFile;

                FtpWebResponse responseFileDownload = (FtpWebResponse)requestFileDownload.GetResponse();

                Stream responseStream = responseFileDownload.GetResponseStream();
                FileStream writeStream = new FileStream(localPath + filename, FileMode.Create);

                int Length = 2048;
                Byte[] buffer = new Byte[Length];
                int bytesRead = responseStream.Read(buffer, 0, Length);

                while (bytesRead > 0)
                {
                    writeStream.Write(buffer, 0, bytesRead);
                    bytesRead = responseStream.Read(buffer, 0, Length);
                }

                responseStream.Close();
                writeStream.Close();

                requestFileDownload = null;
                responseFileDownload = null;
                return true;
            }
            catch (Exception ex)
            {
             
                return false;
            }
        }
Upload File
        private bool UploadfiletoFTP(string LocalfilePath, string FileName, string Directory, string Username, string Password)
        {

            try
            {
                FtpWebRequest ftpClient = (FtpWebRequest)FtpWebRequest.Create(Directory + "/" + FileName);
                ftpClient.Credentials = new System.Net.NetworkCredential(Username, Password);
                ftpClient.Method = System.Net.WebRequestMethods.Ftp.UploadFile;
                ftpClient.UseBinary = true;
                ftpClient.KeepAlive = true;
                System.IO.FileInfo fi = new System.IO.FileInfo(LocalfilePath + "/" + FileName);
                ftpClient.ContentLength = fi.Length;
                byte[] buffer = new byte[4097];
                int bytes = 0;
                int total_bytes = (int)fi.Length;
                System.IO.FileStream fs = fi.OpenRead();
                System.IO.Stream rs = ftpClient.GetRequestStream();
                while (total_bytes > 0)
                {
                    bytes = fs.Read(buffer, 0, buffer.Length);
                    rs.Write(buffer, 0, bytes);
                    total_bytes = total_bytes - bytes;
                }
                //fs.Flush();
                fs.Close();
                rs.Close();
                FtpWebResponse uploadResponse = (FtpWebResponse)ftpClient.GetResponse();
                string value = uploadResponse.StatusDescription;
                uploadResponse.Close();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
Delete File
        private bool DeleteFtpFile(string Directory, string Username, string Password, string Filename)
        {

            try
            {
                FtpWebRequest requestFileDelete = (FtpWebRequest)WebRequest.Create(Directory + "/" + Filename);
                requestFileDelete.Credentials = new NetworkCredential(Username, Password);
                requestFileDelete.Method = WebRequestMethods.Ftp.DeleteFile;
                FtpWebResponse responseFileDelete = (FtpWebResponse)requestFileDelete.GetResponse();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

References:
http://www.techrepublic.com/blog/how-do-i/how-do-i-use-c-to-upload-and-download-files-from-an-ftp-server/ 
http://khanrahim.wordpress.com/2010/09/03/file-download-upload-delete-in-ftp-location-using-c/