明天的明天 永远的永远 未知的一切 我与你一起承担 ??

是非成败转头空 青山依旧在 几度夕阳红 。。。
  博客园  :: 首页  :: 管理

c#读sql server数据添加到MySQL数据库

Posted on 2017-06-21 11:10  且行且思  阅读(1713)  评论(0编辑  收藏  举报

using System;
using System.Collections.Generic;
using System.Text;
using Console = System.Console;
using Microsoft.Data.Odbc;

using System.Data;
using System.Data.SqlClient;


namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {

            try
            {
                connSqlserver();
            }
            catch (OdbcException MyOdbcException)
            {
                for (int i = 0; i < MyOdbcException.Errors.Count; i++)
                {
                    Console.Write("ERROR #" + i + "\n" +
                    "Message: " + MyOdbcException.Errors[i].Message + "\n" +
                    "Native: " + MyOdbcException.Errors[i].NativeError.ToString() + "\n" +
                    "Source: " + MyOdbcException.Errors[i].Source + "\n" +
                    "SQL: " + MyOdbcException.Errors[i].SQLState + "\n");
                }
            }

            
            Console.ReadLine();

        }

        public void ceshi()
        {
            try
            {
                string MyConString = "DRIVER={MySQL ODBC 3.51 Driver};" +
                "SERVER=localhost;" +
                "DATABASE=jy;" +
                "UID=root;" +
                "PASSWORD=123;" +
                "OPTION=3;CharSet=gb2312;";

                OdbcConnection MyConnection = new OdbcConnection(MyConString);
                MyConnection.Open();

                Console.WriteLine("\n !!! success, connected successfully !!!\n");


                //Create a sample table
                OdbcCommand MyCommand = new OdbcCommand("DROP TABLE IF EXISTS my_odbc_net", MyConnection);
                MyCommand.ExecuteNonQuery();
                MyCommand.CommandText = "CREATE TABLE my_odbc_net(id int, name varchar(20), idb bigint)";
                MyCommand.ExecuteNonQuery();

                //Insert
                MyCommand.CommandText = "INSERT INTO my_odbc_net VALUES(10,'" + filter("中国,''") + "', 300)";
                Console.WriteLine("INSERT, Total rows affected:" + MyCommand.ExecuteNonQuery()); ;

                //Insert
                MyCommand.CommandText = "INSERT INTO my_odbc_net VALUES(20,'mysql',400)";
                Console.WriteLine("INSERT, Total rows affected:" + MyCommand.ExecuteNonQuery());

                //Insert
                MyCommand.CommandText = "INSERT INTO my_odbc_net VALUES(20,'mysql',500)";
                Console.WriteLine("INSERT, Total rows affected:" + MyCommand.ExecuteNonQuery());

                //Update
                MyCommand.CommandText = "UPDATE my_odbc_net SET id=999 WHERE id=20";
                Console.WriteLine("Update, Total rows affected:" + MyCommand.ExecuteNonQuery());

                //COUNT(*)
                MyCommand.CommandText = "SELECT COUNT(*) as TRows FROM my_odbc_net";
                Console.WriteLine("Total Rows:" + MyCommand.ExecuteScalar());

                //Fetch
                MyCommand.CommandText = "SELECT * FROM my_odbc_net";
                OdbcDataReader MyDataReader;
                MyDataReader = MyCommand.ExecuteReader();
                while (MyDataReader.Read())
                {
                    if (string.Compare(MyConnection.Driver, "myodbc3.dll") == 0)
                    {
                        Console.WriteLine("Data:" + MyDataReader.GetInt32(0) + " " +
                        MyDataReader.GetString(1) + " " +
                        MyDataReader.GetInt64(2)); //Supported only by Connector/ODBC 3.51
                    }
                    else
                    {
                        Console.WriteLine("Data:" + MyDataReader.GetInt32(0) + " " +
                        MyDataReader.GetString(1) + " " +
                        MyDataReader.GetInt32(2)); //BIGINTs not supported by Connector/ODBC
                    }
                }

                //Close all resources
                MyDataReader.Close();
                MyConnection.Close();
            }
            catch (OdbcException MyOdbcException)//Catch any ODBC exception ..
            {
                for (int i = 0; i < MyOdbcException.Errors.Count; i++)
                {
                    Console.Write("ERROR #" + i + "\n" +
                    "Message: " + MyOdbcException.Errors[i].Message + "\n" +
                    "Native: " + MyOdbcException.Errors[i].NativeError.ToString() + "\n" +
                    "Source: " + MyOdbcException.Errors[i].Source + "\n" +
                    "SQL: " + MyOdbcException.Errors[i].SQLState + "\n");
                }
            }
        }


        public static void connSqlserver()
        {
            string MyConString = "DRIVER={MySQL ODBC 3.51 Driver};" +
                "SERVER=localhost;" +
                "DATABASE=zs;" +
                "UID=root;" +
                "PASSWORD=123;" +
                "OPTION=3;CharSet=gb2312;";

            OdbcConnection MyConnection = new OdbcConnection(MyConString);
            MyConnection.Open();
            OdbcCommand MyCommand = new OdbcCommand("DROP TABLE IF EXISTS my_odbc_net", MyConnection);
            MyCommand.ExecuteNonQuery();


            //使用轻量级的SqlDataReader显示数据
            //指定Sql Server提供者的连接字符串
            string connString = "Data Source=PC-200908231053\\SQLEXPRESS;database=occupationNew;User id=sa;PWD=123";

            //建立连接对象

            SqlConnection Sqlconn = new SqlConnection(connString);
            //打开连接
            Sqlconn.Open();

            
            string thisCommand = "select * from Article where ID >"+ 1243931905062 +"order by ID";

            //创建SqlDataAdapter对象,有两个参数,一个是查询字符串,一个是连接对象
            SqlDataAdapter SqlDap = new SqlDataAdapter(thisCommand, Sqlconn);

            //创建DataSet对象

            DataSet thisDataset = new DataSet();

            //使用SqlDataAdapter的Fill方法填充DataSet,有两个参数,一个是创建的DataSet实例,一个是填入的表

            SqlDap.Fill(thisDataset, "informations");

            //显示查询结果

            foreach (DataRow theRow in thisDataset.Tables["informations"].Rows)
            {
                //Console.WriteLine(theRow["InformationId"] + "\t" + theRow["companyName"]);
                //MyCommand.CommandText = "INSERT INTO information(companyname,course,major,number,sex,pay,request,informationsource,datetime,job,workprovinci,detailplace,isbin,enddate,existcourse,academymajororder) "
                //    + "VALUES('" + filter(theRow["CompanyName"]) + "','" + filter(theRow["Course"]) + "','" + filter(theRow["Major"]) + "','" + filter(theRow["Number"]) + "','" + filter(theRow["Sex"]) + "','" + filter(theRow["Pay"]) + "','" + filter(theRow["request"]) + "','" + filter(theRow["informationSource"]) + "','" + filter(theRow["DateTime"]) + "','" + filter(theRow["Job"]) + "','" + filter(theRow["WorkProvince"]) + "','" + filter(theRow["DetailPlace"]) + "','" + filter(theRow["IsBin"]) + "','" + filter(theRow["EndDate"]) + "','" + filter(theRow["ExistCourse"]) + "','" + filter(theRow["AcademyMajorOrder"]) + "')";


                Console.WriteLine(theRow["ID"] + "\t" + theRow["Title"]);
                MyCommand.CommandText = "INSERT INTO downloads(title,fenlei,content,lint,updatetime,click) "
                    + "VALUES('" + filter(theRow["Title"]) + "','" + filter(theRow["CategoryID"]) + "','" + filter(theRow["Content"]) + "','" + filter(theRow["Author"]) + "','" + filter(theRow["DateTime"]) + "','" + filter(theRow["Hits"]) + "')";

                //Console.WriteLine(theRow["ID"] + "\t" + theRow["Title"]);
                //MyCommand.CommandText = "INSERT INTO xinwens(title,fenlei,content,username,updatetime,click) "
                //    + "VALUES('" + filter(theRow["Title"]) + "','" + "" + "','" + filter(theRow["Content"]) + "','" + filter("大学生就业指导中心") + "','" + filter(theRow["DateTime"]) + "','" + 0 + "')";
                



                Console.WriteLine("INSERT, Total rows affected:" + MyCommand.ExecuteNonQuery());
            }
            Sqlconn.Close();

            Console.ReadLine();


        }

        public static string filter(object text)
        {
            System.Text.Encoding GB2312 = System.Text.Encoding.GetEncoding("GB2312");
            System.Text.Encoding UTF8 = System.Text.Encoding.UTF8;
            byte[] data = GB2312.GetBytes(text.ToString());
            string msg = GB2312.GetString(data);
            return msg.Replace(",", ",").Replace("'", "’").Replace("―", "-");

        }
    }
}