covert code form sql server to my sql

11y990.NET

can any one convert my code into mysql.

i am make code for csv file upload using sql server in asp.net

but now i want make this code for my sql database

my code is bellow

csvupload.aspx.cs-

?using System;

using System.Collections.Generic;

using System.Data;

using System.Data.SqlClient;

using System.IO;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

namespace csvfileupload2

{

public partial class csvupload : System.Web.UI.Page

{

string consString = @"Data Source=ANIRUDDHA-PC;Initial Catalog=sark;Integrated Security=True";

protected void Page_Load(object sender, EventArgs e)

{

BindGrid();

}

protected void Upload(object sender, EventArgs e)

{

//Upload and save the file

string fileName = Path.GetFileName(FileUpload1.PostedFile.FileName);

string csvPath = Server.MapPath("~/Files/") + fileName;

FileUpload1.SaveAs(csvPath);

int file_id = 0;

using (SqlConnection con = new SqlConnection(consString))

{

con.Open();

SqlCommand cmd = con.CreateCommand();

cmd.CommandText = "insert into files (filename,created_date) values('" + fileName + "','" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "')";

int i = cmd.ExecuteNonQuery();

con.Close();

con.Open();

SqlCommand cmd1 = con.CreateCommand();

cmd1.CommandText = "SELECT TOP 1 * FROM files ORDER BY file_id DESC";

SqlDataReader rdr = cmd1.ExecuteReader();

if (rdr.HasRows)

{

rdr.Read();

file_id = Convert.ToInt32(rdr[0]);

}

con.Close();

}

DataTable dt = new DataTable();

dt.Columns.AddRange(new DataColumn[16] {

new DataColumn("file_id", typeof(int)),

new DataColumn("phone_number"),

new DataColumn("title"),

new DataColumn("first_name"),

new DataColumn("middle_initial"),

new DataColumn("last_name"),

new DataColumn("address"),

new DataColumn("city"),

new DataColumn("state"),

new DataColumn("province"),

new DataColumn("postal_code"),

new DataColumn("country_code"),

new DataColumn("gender"),

new DataColumn("date_of_birth"),

new DataColumn("alt_phone"),

new DataColumn("email")});

string csvData = File.ReadAllText(csvPath);

foreach (string row in csvData.Split('\n'))

{

if (!string.IsNullOrEmpty(row))

{

dt.Rows.Add();

dt.Rows[dt.Rows.Count - 1][0] = Convert.ToInt32(file_id);

int i = 1;

foreach (string cell in row.Split(','))

{

dt.Rows[dt.Rows.Count - 1][i] = cell;

i++;

}

using (SqlConnection con = new SqlConnection(consString))

{

using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))

{

//Set the database table name

sqlBulkCopy.DestinationTableName = "dbo.profilesrr";

sqlBulkCopy.ColumnMappings.Add("file_id", "file_id");

sqlBulkCopy.ColumnMappings.Add("phone_number", "phone_number");

sqlBulkCopy.ColumnMappings.Add("title", "title");

sqlBulkCopy.ColumnMappings.Add("first_name", "first_name");

sqlBulkCopy.ColumnMappings.Add("middle_initial", "middle_initial");

sqlBulkCopy.ColumnMappings.Add("last_name", "last_name");

sqlBulkCopy.ColumnMappings.Add("address", "address");

sqlBulkCopy.ColumnMappings.Add("city", "city");

sqlBulkCopy.ColumnMappings.Add("state", "state");

sqlBulkCopy.ColumnMappings.Add("province", "province");

sqlBulkCopy.ColumnMappings.Add("postal_code", "postal_code");

sqlBulkCopy.ColumnMappings.Add("country_code", "country_code");

sqlBulkCopy.ColumnMappings.Add("gender", "gender");

sqlBulkCopy.ColumnMappings.Add("date_of_birth", "date_of_birth");

sqlBulkCopy.ColumnMappings.Add("alt_phone", "alt_phone");

sqlBulkCopy.ColumnMappings.Add("email", "email");

con.Open();

sqlBulkCopy.WriteToServer(dt);

con.Close();

}

public void BindGrid()

{

using (SqlConnection con = new SqlConnection(consString))

{

con.Open();

SqlCommand cmd = con.CreateCommand();

cmd.CommandText = "select * from files";

SqlDataReader rdr = cmd.ExecuteReader();

DataTable dt = new DataTable();

dt.Load(rdr);

GV_List.DataSource = dt;

GV_List.DataBind();

con.Close();

}

protected void Button1_Click(object sender, EventArgs e)

{

using (SqlConnection con = new SqlConnection(consString))

{

con.Open();

DataSet ds = new DataSet();

SqlCommand cmd = new SqlCommand("select * from profilesrr", con);

SqlDataAdapter da = new SqlDataAdapter();

da.SelectCommand = cmd;

da.Fill(ds);

da.Dispose();

cmd.Dispose();

con.Close();

GridView1.DataSource = ds.Tables[0];

GridView1.DataBind();

}

i want to convert this code for my sql database.

my design page code are bellow

csvupload.aspx-

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="csvupload.aspx.cs" Inherits="csvfileupload2.csvupload" %>

Well Come To WildCard Techno Services Pvt Ltd

File Upload
Select File	ToolTip="Select Only Excel File" Height="27px" />

CaptionAlign="Top" HorizontalAlign="Justify"

DataKeyNames="file_id"

ToolTip="Excel FIle DownLoad Tool" CellPadding="4" ForeColor="#333333"

GridLines="None">

Download

HeaderText="phone_number" ItemStyle-Width = "150" />

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

Raja T11y ago
Are you added mysql dll in your project?

Checkout below urls,

http://stackoverflow.com/questions/1598829/parser-error-message-could-not-load-type-testmvcapplication-mvcapplication

http://stackoverflow.com/questions/15071220/parser-error-message-could-not-load-type-sometype
Raja T11y ago
Hi
Except below method remaining working fine in my understanding

using (SqlConnection con = new SqlConnection(consString))
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
//Set the database table name
sqlBulkCopy.DestinationTableName = "dbo.profilesrr";
sqlBulkCopy.ColumnMappings.Add("file_id", "file_id");
sqlBulkCopy.ColumnMappings.Add("phone_number", "phone_number");
sqlBulkCopy.ColumnMappings.Add("title", "title");
sqlBulkCopy.ColumnMappings.Add("first_name", "first_name");
sqlBulkCopy.ColumnMappings.Add("middle_initial", "middle_initial");
sqlBulkCopy.ColumnMappings.Add("last_name", "last_name");
sqlBulkCopy.ColumnMappings.Add("address", "address");
sqlBulkCopy.ColumnMappings.Add("city", "city");
sqlBulkCopy.ColumnMappings.Add("state", "state");
sqlBulkCopy.ColumnMappings.Add("province", "province");
sqlBulkCopy.ColumnMappings.Add("postal_code", "postal_code");
sqlBulkCopy.ColumnMappings.Add("country_code", "country_code");
sqlBulkCopy.ColumnMappings.Add("gender", "gender");
sqlBulkCopy.ColumnMappings.Add("date_of_birth", "date_of_birth");
sqlBulkCopy.ColumnMappings.Add("alt_phone", "alt_phone");
sqlBulkCopy.ColumnMappings.Add("email", "email");
con.Open();
sqlBulkCopy.WriteToServer(dt);
con.Close();
}
}
11y ago
dear@ raja sir,
when i am run your code bellow maintain error will be occur

Parser Error Message: Could not load type 'csvfileupload2.csvuploadmysql'.

please help me
Raja T11y ago
Hi

Try this before you can add given dll in your project.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using MySql.Data.MySqlClient;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace TestDemo
{
public partial class mysqlform : System.Web.UI.Page
{
string connstring = "server="+str_server+";database="+str_userdb+";uid="+str_username+";password="+str_userpass+";";

protected void Page_Load(object sender, EventArgs e)
{
BindGrid();
}
protected void Upload(object sender, EventArgs e)
{
//Upload and save the file
string fileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
string csvPath = Server.MapPath("~/Files/") + fileName;
FileUpload1.SaveAs(csvPath);
int file_id = 0;
using (MySqlConnection con = new MySqlConnection(consString))
{
con.Open();
MySqlCommand cmd = con.CreateCommand();
cmd.CommandText = "insert into files (filename,created_date) values('" + fileName + "','" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "')";
int i = cmd.ExecuteNonQuery();
con.Close();
con.Open();
MySqlCommand cmd1 = con.CreateCommand();
cmd1.CommandText = "SELECT TOP 1 * FROM files ORDER BY file_id DESC";
MySqlDataReader rdr = cmd1.ExecuteReader();
if (rdr.HasRows)
{
rdr.Read();
file_id = Convert.ToInt32(rdr[0]);
}
con.Close();
}
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[16] {
new DataColumn("file_id", typeof(int)),
new DataColumn("phone_number"),
new DataColumn("title"),
new DataColumn("first_name"),
new DataColumn("middle_initial"),
new DataColumn("last_name"),
new DataColumn("address"),
new DataColumn("city"),
new DataColumn("state"),
new DataColumn("province"),
new DataColumn("postal_code"),
new DataColumn("country_code"),
new DataColumn("gender"),
new DataColumn("date_of_birth"),
new DataColumn("alt_phone"),
new DataColumn("email")});
string csvData = File.ReadAllText(csvPath);
foreach (string row in csvData.Split('\n'))
{
if (!string.IsNullOrEmpty(row))
{
dt.Rows.Add();
dt.Rows[dt.Rows.Count - 1][0] = Convert.ToInt32(file_id);
int i = 1;
foreach (string cell in row.Split(','))
{
dt.Rows[dt.Rows.Count - 1][i] = cell;
i++;
}
}
}

}
public void BindGrid()
{
using (MySqlConnection con = new MySqlConnection(consString))
{
con.Open();
MySqlCommand cmd = con.CreateCommand();
cmd.CommandText = "select * from files";
MySqlDataReader rdr = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(rdr);
GV_List.DataSource = dt;
GV_List.DataBind();
con.Close();
}
}
protected void Button1_Click(object sender, EventArgs e)
{
using (MySqlConnection con = new MySqlConnection(consString))
{
con.Open();
DataSet ds = new DataSet();
MySqlCommand cmd = new MySqlCommand("select * from profilesrr", con);
MySqlDataAdapter da = new MySqlDataAdapter();
da.SelectCommand = cmd;
da.Fill(ds);
da.Dispose();
cmd.Dispose();
con.Close();
GridView1.DataSource = ds.Tables[0];
GridView1.DataBind();
}
}
}
}
11y ago
dear sir when i am run your code bellow maintain error will be occur

Parser Error Message: Could not load type 'csvfileupload2.csvuploadmysql'.

please help me
Raja T11y ago
Hi, If you facing any issues back to me.
Raja T11y ago
Hi,

Please find attached file. In that 2 files is there one is mySql.dll another one converted code in mySql.
You just include DLL in ur project.
11y ago
can any one convert this code into my sql???
please help me