TheOneAndOnlyNoobofCSharp TheOneAndOnlyNoobofCSharp - 1 month ago 9
C# Question

writing OracleDataReader to excel C# .net

I need help. How should I write each row from a table into an Excel file?

Question #1: how do I get the values of each cell or each row from

OracleDataReader
?

Question #2: number of columns in tables might vary. Should I read each cell and write it to Excel or by row?

Code:

try
{
string connectionString = ConfigurationManager.ConnectionStrings["OracleConnectionString"].ConnectionString;

OracleConnection connection = new OracleConnection();
connection.Open();

if(connection.ToString().Equals("Open"))
{
OracleCommand cmd = new OracleCommand();
cmd.Connection = connection;

cmd.Parameters.Add (dict_name);

cmd.CommandText = "SELECT * from :dict_name";
cmd.CommandType = CommandType.Text;

OracleDataReader dataReader = cmd.ExecuteReader();

while (dataReader.Read())
{
}
}

connection.Close();
}
catch (OracleException e)
{
if (e.ErrorCode != 2000)
{
Console.WriteLine("Oracle Error -> {0} {1}", DateTime.Now.ToString("dd mm yyyy HH:mm:ss"), e.StackTrace);
}
}
catch (Exception e)
{
Console.WriteLine("Erorr -> {0} {1}", DateTime.Now.ToString("dd mm yyyy HH:mm:ss"), e.StackTrace);
}

Answer

So I figured it out. Here is the code that is working for me now. I tried it with different tables in Database.

Question: Anyone has idea how to read dynamically from excel and load data into database? I mean like, to construct INSERT command dynamically

    [WebMethod]
    public string dictToExcel(string dict_name)
    {
        string connectionString = ConfigurationManager.ConnectionStrings["OracleConnection"].ConnectionString;
        OracleConnection connection = new OracleConnection(connectionString);

        try
        {
            connection.Open();

            OracleCommand cmd = new OracleCommand();
            cmd.Connection = connection;
            cmd.CommandText = "SELECT * from " + dict_name;
            cmd.CommandType = CommandType.Text;

            OracleDataReader dataReader = cmd.ExecuteReader();

            IWorkbook workbook;
            workbook = new XSSFWorkbook();

            ISheet sheet = workbook.CreateSheet("Sheet 1");

            int b = 0;
            while (dataReader.Read())
            {
                int n = 0;
                IRow row = sheet.CreateRow(b);
                while (n < dataReader.FieldCount)
                {
                    ICell cell = row.CreateCell(n);

                    String columnName = dataReader.GetValue(n).ToString();
                    cell.SetCellValue(columnName);

                    n++;
                }

                b++;
            }
Comments