pass table name as variable in stored procedure in mysql

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_getdataforexport`(in tablename varchar(100),in tblcityname varchar(100))

BEGIN

    

      set @citycode= (select citycode from ea_citymaster where cityname=tblcityname);    

      SET @s = CONCAT('select * from ', tablename, ' where CityCode=''',@citycode,'''');

      PREPARE stmt FROM @s;

      EXECUTE stmt; 

    END$$

Read more ...

bulk upload or insert data into database in mysql

public void Insertbulkdata(DataTable dt, string fileName, string tablename)

    {

        MySql.Data.MySqlClient.MySqlConnection con = new MySql.Data.MySqlClient.MySqlConnection(ConfigurationManager.ConnectionStrings["MySQLConnectionString"].ToString());

        MySql.Data.MySqlClient.MySqlBulkLoader bulkloder = new MySql.Data.MySqlClient.MySqlBulkLoader(con);

        bulkloder.FieldTerminator = "|";

        bulkloder.LineTerminator = @"\n";

        bulkloder.NumberOfLinesToSkip = 1;       

        bulkloder.FileName = @“C:\filename.txt”;

        bulkloder.TableName = tablename;

  try

{

con.Open();

// Upload data from file

int count = bulkloder.Load();

con.Close();

}

catch (Exception ex)

{

}

finally

{

if (System.IO.File.Exists(savefilepath))

{

System.IO.File.Delete(savefilepath);

     }

            }

}

Read more ...

Writing a DataTable into a text file in asp.net c#

    public void Writewowfileintxt(DataTable dt, string filePath)

    {

        int i = 0;

        StreamWriter sw = null;

        try

        {

            TextInfo UsaTextInfo = new CultureInfo("en-US", false).TextInfo;

            sw = new StreamWriter(filePath, false);

            DataTable dtcolunm = clsd.getTablecolumn(tablename);

            for(i = 0; i < dt.Columns.Count - 1; i++)

                {

                    sw.Write(dt.Columns[i].ColumnName + ” | ”);

                }

                sw.Write(dt.Columns[i].ColumnName);

                sw.WriteLine();

            char[] charsToTrim = { ' ' };

            foreach (DataRow row in dt.Rows)

            {

                object[] array = row.ItemArray;

                for (i = 0; i < array.Length - 1; i++)

                {

       sw.Write(array[i].ToString);                       

                }

                sw.Write(array[i].ToString);

                sw.WriteLine();

            }

            sw.Close();

        }

        catch (Exception ex)

        {

        }

    }

Read more ...

export to excel in asp.net from multipal datatable and multipal sheet

protected void btnexportexcel_Click(object sender, EventArgs e)

{

DataTable TableName1= new DataTable();

DataTable TableName2= new DataTable();

DataSet ds = new DataSet();

ds.Merge(TableName1);

ds.Merge(TableName2);

ds.Tables[0].TableName = "TableName1";

ds.Tables[1].TableName = "TableName2";

ToExcel(ds, "" + filename + ".xls", Page.Response);

}

private static string getWorkbookTemplate()

{

var sb = new StringBuilder(818);

sb.AppendFormat(@"{0}", Environment.NewLine);

sb.AppendFormat(@"{0}", Environment.NewLine);

sb.AppendFormat(@", Environment.NewLine);

sb.AppendFormat(@" xmlns:o=""urn:schemas-microsoft-com:office:office""{0}", Environment.NewLine);

sb.AppendFormat(@" xmlns:x=""urn:schemas-microsoft-com:office:excel""{0}", Environment.NewLine);

sb.AppendFormat(@" xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet""{0}", Environment.NewLine);

sb.AppendFormat(@" xmlns:html=""http://www.w3.org/TR/REC-html40"">{0}", Environment.NewLine);

sb.AppendFormat(@" {0}", Environment.NewLine);

sb.AppendFormat(@"  {0}", Environment.NewLine);

sb.AppendFormat(@"  {0}", Environment.NewLine);

sb.AppendFormat(@"  {0}", Environment.NewLine);

sb.AppendFormat(@" {0}", Environment.NewLine);

sb.Append(@"{0}\r\n");

return sb.ToString();

}

private static string replaceXmlChar(string input)

{

input = input.Replace("&", "&amp");

input = input.Replace("<", "<");

input = input.Replace(">", ">");

input = input.Replace("\"", """);

input = input.Replace("'", "'");

return input;

}

private static string getCell(Type type, object cellData)

{

var data = (cellData is DBNull) ? "" : cellData;

if (type.Name.Contains("Int") || type.Name.Contains("Double") || type.Name.Contains("Decimal")) return string.Format("{0}", data);

if (type.Name.Contains("Date") && data.ToString() != string.Empty)

{

return string.Format("{0}", Convert.ToDateTime(data).ToString("yyyy-MM-dd"));

}

return string.Format("{0}", replaceXmlChar(data.ToString()));

}

private static string getWorksheets(DataSet source)

{

var sw = new StringWriter();

if (source == null || source.Tables.Count == 0)

{

sw.Write("\r\n

\r\n\r\n \r\n");

return sw.ToString();

}

foreach (DataTable dt in source.Tables)

{

if (dt.Rows.Count == 0)

sw.Write(" + replaceXmlChar(dt.TableName) + "\">\r\n

\r\n  ss:StyleID=\"s62\">\r\n \r\n");

else

{

//write each row data

var sheetCount = 0;

for (int i = 0; i < dt.Rows.Count; i++)

{

if ((i % rowLimit) == 0)

{

//add close tags for previous sheet of the same data table

if ((i / rowLimit) > sheetCount)

{

sw.Write("\r\n\r\n");

sheetCount = (i / rowLimit);

}

sw.Write("\r\n + replaceXmlChar(dt.TableName) +

(((i / rowLimit) == 0) ? "" : Convert.ToString(i / rowLimit)) + "\">\r\n

");

//write column name row

sw.Write("\r\n");

foreach (DataColumn dc in dt.Columns)

sw.Write(string.Format("{0}", replaceXmlChar(dc.ColumnName)));

sw.Write("");

}

sw.Write("\r\n");

foreach (DataColumn dc in dt.Columns)

sw.Write(getCell(dc.DataType, dt.Rows[i][dc.ColumnName]));

sw.Write("");

}

sw.Write("\r\n

\r\n");

}

}

return sw.ToString();

}

public static string GetExcelXml(DataTable dtInput, string filename)

{

var excelTemplate = getWorkbookTemplate();

var ds = new DataSet();

ds.Tables.Add(dtInput.Copy());

var worksheets = getWorksheets(ds);

var excelXml = string.Format(excelTemplate, worksheets);

return excelXml;

}

public static string GetExcelXml(DataSet dsInput, string filename)

{

var excelTemplate = getWorkbookTemplate();

var worksheets = getWorksheets(dsInput);

var excelXml = string.Format(excelTemplate, worksheets);

return excelXml;

}

public static void ToExcel(DataSet dsInput, string filename, HttpResponse response)

{

var excelXml = GetExcelXml(dsInput, filename);

response.Clear();

response.Buffer = true;

response.BufferOutput = true;

// response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");

response.Charset = "UTF-8";

response.ContentEncoding = System.Text.Encoding.UTF8;

//response.ContentType = "application/vnd.ms-excel";

response.AppendHeader("Content-Type", "application/ms-excel");

response.AppendHeader("Content-disposition", "attachment; filename=" + filename);

response.Write(excelXml);

response.Flush();

response.End();

}

public static void ToExcel(DataTable dtInput, string filename, HttpResponse response)

{

var ds = new DataSet();

ds.Tables.Add(dtInput.Copy());

ToExcel(ds, filename, response);

}

Read more ...