MYSql Split

CREATE FUNCTION SplitString
(
      @Input NVARCHAR(MAX),
      @Character CHAR(1)
)
RETURNS @Output TABLE (
      Item NVARCHAR(1000)
)
AS
BEGIN
      DECLARE @StartIndex INT, @EndIndex INT
      SET @StartIndex = 1
      IF SUBSTRING(@Input, LEN(@Input) – 1, LEN(@Input)) <> @Character
      BEGIN
            SET @Input = @Input + @Character
      END
      WHILE CHARINDEX(@Character, @Input) > 0
      BEGIN
            SET @EndIndex = CHARINDEX(@Character, @Input)
            INSERT INTO @Output(Item)
            SELECT SUBSTRING(@Input, @StartIndex, @EndIndex – 1)
            SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
      END
      RETURN
END
GO
How to use::
SELECT Item
FROM dbo.SplitString(‘Apple,Mango,Banana,Guava’, ‘,’)
Using Procedure::
CREATE PROCEDURE GetEmployees
      @EmployeeIds VARCHAR(100)
AS
BEGIN
      SELECT FirstName, LastName
      FROM Employees
      WHERE EmployeeId IN(
            SELECT CAST(Item AS INTEGER)
            FROM dbo.SplitString(@EmployeeIds, ‘,’)
      )
END
Advertisements

Read excel file

private void ReadxlsFile(string _FileName)
{
try
{
System.Data.OleDb.OleDbConnection MyConnection ;
System.Data.DataSet DtSet ;
System.Data.OleDb.OleDbDataAdapter MyCommand ;
MyConnection = new System.Data.OleDb.OleDbConnection(“provider=Microsoft.Jet.OLEDB.4.0;Data Source='”+_FileName+”‘;Extended Properties=Excel 8.0;”);
MyCommand = new System.Data.OleDb.OleDbDataAdapter(“select * from [Sheet1$]”, MyConnection);
MyCommand.TableMappings.Add(“Table”, “TestTable”);
DtSet = new System.Data.DataSet();
MyCommand.Fill(DtSet);
MyConnection.Close();
}
catch (Exception ex)
{
Response.Write(ex.Message.ToString()+”<br >”+ex.StackTrace.ToString());
Response.Flush();
}
}

FileUpload

ASPX Page Code:

<asp:FileUpLoad id=”FileUpLoad” runat=”server” />

Add Regular expression to validate file extention.

<asp:RegularExpressionValidator
id=”FileUpLoadValidator” runat=”server”
ErrorMessage=”Upload Jpegs and Gifs only.”
ValidationExpression=”^(([a-zA-Z]:)|(\\{2}\w+)\$?)(\\(\w[\w].*))(.jpg|.JPG|.gif|.GIF)$”

ControlToValidate
=”FileUpload”>
</asp:RegularExpressionValidator>

<asp:Button id=”UploadBtn” Text=”Upload File” OnClick=”UploadBtn_Click” runat=”server” Width=”105px” />

 

Code behind :

protected void UploadBtn_Click(object sender, EventArgs e)
{
if
(FileUpLoad1.HasFile)
{

FileUpLoad1.SaveAs(@”C:\temp\” + FileUpLoad1.FileName);
Label1.Text =
“File Uploaded: “
+ FileUpLoad1.FileName ;
}
else
{
Label1.Text =
“No File Uploaded.”
;
}
}

 

Common Data Class for MySql Connection

Asp.net Database connection Class File, Which can be used as a common connection class file to create connection, inserting, updating and deletion of data from MySQL database .
Don’t write db connection code on every page.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Odbc;
using System.Collections;
using System.Configuration;

public class DataClass : IDisposable
{
    private OdbcCommand _objCommand = new OdbcCommand();
    public string strConnectionString = "";
    private OdbcConnection objConnection;
    public DataClass()
    {
        OdbcConnection _objConnection = new OdbcConnection();
        strConnectionString = ConfigurationManager.ConnectionStrings["AUTHENTICATE"].ConnectionString;
        _objConnection.ConnectionString = strConnectionString;
        _objCommand.Connection = _objConnection;
    }

    public DataClass(OdbcConnection objConnection)
    {
        _objCommand.Connection = objConnection;
    }


    public string ConnectionString
    {
        get
        {
            return strConnectionString;
        }
        set
        {
            strConnectionString = value;
        }
    }
    public void Open()
    {
        _objCommand.Connection.Open();
    }
    public void Close()
    {

        _objCommand.Connection.Close();
    }
    public void Dispose()
    {
        _objCommand.Dispose();
    }
    public int Insert(string _commandText)
    {
        int i = -1;
        try
        {
            if (_objCommand.Connection.State == ConnectionState.Closed)
                this.Open();
            _objCommand.CommandText = _commandText;
            i = _objCommand.ExecuteNonQuery();

        }
        catch (Exception)
        {

            throw;
        }
        finally
        {
            if (_objCommand.Connection.State == ConnectionState.Open)
            {
                this.Close();
                this.Dispose();
            }
        }
        return i;
    }
    public int InsertStored(string _commandText)
    {
        int i = -1;
        try
        {
            if (_objCommand.Connection.State == ConnectionState.Closed)
                this.Open();
            _objCommand.CommandText = _commandText;
            _objCommand.CommandType = CommandType.StoredProcedure;
            i = _objCommand.ExecuteNonQuery();
            this.Close();
            this.Dispose();
        }
        catch (Exception)
        {
            throw;
        }
        finally
        {
            if (_objCommand.Connection.State == ConnectionState.Open)
            {
                this.Close();
                this.Dispose();
            }
        }
        return i;
    }
    public OdbcDataReader ExecuteReader(string _commandText)
    {
        OdbcDataReader _objDtReader = null;
        try
        {
            if (_objCommand.Connection.State == ConnectionState.Closed)
                this.Open();
            _objCommand.CommandText = _commandText;
            _objDtReader = _objCommand.ExecuteReader(CommandBehavior.CloseConnection);
        }
        catch (Exception)
        {
            throw;
        }
        finally
        {
            if (_objCommand.Connection.State == ConnectionState.Open)
            {
                this.Close();
                this.Dispose();
            }
        }
        return _objDtReader;
    }
    public OdbcDataReader ExecuteReaderStored(string _commandText)
    {
        OdbcDataReader _objDtReader = null;
        try
        {
            if (_objCommand.Connection.State == ConnectionState.Closed)
                this.Open();
            _objCommand.CommandText = _commandText;
            _objCommand.CommandType = CommandType.StoredProcedure;
            _objDtReader = _objCommand.ExecuteReader(CommandBehavior.CloseConnection);
        }
        catch (Exception)
        {
            throw;
        }
        finally
        {
            if (_objCommand.Connection.State == ConnectionState.Open)
            {
                this.Close();
                this.Dispose();
            }
        }
        return _objDtReader;
    }
    public DataSet ExecuteAdapterStored(string _commandText)
    {
        DataSet _ds = null;
        OdbcDataAdapter _objDtAdapter = null;
        try
        {
            if (_objCommand.Connection.State == ConnectionState.Closed)
                this.Open();
            _objCommand.CommandText = _commandText;
            _objCommand.CommandType = CommandType.StoredProcedure;
            _objDtAdapter = new OdbcDataAdapter();
            _objDtAdapter.SelectCommand = (OdbcCommand)_objCommand;
            _ds = new DataSet();
            _objDtAdapter.Fill(_ds);
        }
        catch (Exception)
        {
            throw;
        }
        finally
        {
            if (_objCommand.Connection.State == ConnectionState.Open)
            {
                this.Close();
                this.Dispose();
            }
        }
        return _ds;
    }
    public int ExecuteScalarStored(string _commandText)
    {
        int i;
        try
        {
            if (_objCommand.Connection.State == ConnectionState.Closed)
                this.Open();
            _objCommand.CommandText = _commandText;
            _objCommand.CommandType = CommandType.StoredProcedure;
            i = Convert.ToInt32(_objCommand.ExecuteScalar());
        }
        catch (Exception)
        {
            throw;
        }
        finally
        {
            if (_objCommand.Connection.State == ConnectionState.Open)
            {
                this.Close();
                this.Dispose();
            }
        }
        return i;
    }
    public string ExecuteScalarString(string _commandText)
    {
        string var;
        try
        {
            if (_objCommand.Connection.State == ConnectionState.Closed)
                this.Open();
            _objCommand.CommandText = _commandText;

            var = Convert.ToString(_objCommand.ExecuteScalar());
        }
        catch (Exception)
        {
            throw;
        }
        finally
        {
            if (_objCommand.Connection.State == ConnectionState.Open)
            {
                this.Close();
                this.Dispose();
            }
        }
        return var;
    }
    public int ExecuteScalarInt(string _commandText)
    {
        int var;
        try
        {
            if (_objCommand.Connection.State == ConnectionState.Closed)
                this.Open();
            _objCommand.CommandText = _commandText;

            var = Convert.ToInt32(_objCommand.ExecuteScalar());
        }
        catch (Exception)
        {
            throw;
        }
        finally
        {
            if (_objCommand.Connection.State == ConnectionState.Open)
            {
                this.Close();
                this.Dispose();
            }
        }
        return var;
    }
    public DataTable ExecuteSelect(string _commandText)
    {
        DataSet _ds = null;
        OdbcDataAdapter _objDtAdapter = null;
        try
        {
            if (_objCommand.Connection.State == ConnectionState.Closed)
                this.Open();
            _objCommand.CommandText = _commandText;
            _objDtAdapter = new OdbcDataAdapter();
            _objDtAdapter.SelectCommand = (OdbcCommand)_objCommand;
            _ds = new DataSet();
            _objDtAdapter.Fill(_ds);
            return _ds.Tables[0];
        }
        catch (Exception ex)
        {
            throw;
        }
        finally
        {
            if (_objCommand.Connection.State == ConnectionState.Open)
            {
                this.Close();
                this.Dispose();
            }
        }
    }



    public Boolean InsertUpdateData(OdbcCommand cmd)
    {
      
        String strConnString = strConnectionString;

        OdbcConnection con = new OdbcConnection(strConnString);

        cmd.CommandType = CommandType.Text;

        cmd.Connection = con;

        try
        {

            con.Open();

            cmd.ExecuteNonQuery();

            return true;

        }

        catch (Exception ex)
        { 
            return false;
        }
        finally
        {
            con.Close();
            con.Dispose();
        }

    }

}