GENERER LES LIGNES INSERT SQL POUR VOS TABLES SQL SERVER

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using System.Data.SqlTypes;
using System.Text;
using System.IO;
namespace InsertGenerator
{
	/// <summary>
	/// Summary description for Form1.
	/// </summary>
	public class Form1 : System.Windows.Forms.Form
	{
		private System.Windows.Forms.CheckedListBox checkedCols;
		private System.Windows.Forms.Label label1;
		private System.Windows.Forms.TextBox textBoxConn;
		private System.Windows.Forms.Label label2;
		private System.Windows.Forms.ComboBox comboBox1;
		private System.Windows.Forms.TextBox textBox2;
		private System.Windows.Forms.FolderBrowserDialog folderBrowserDialog1;
		private System.Windows.Forms.Button buttonWhere;
		private System.Windows.Forms.Button buttonConnect;
		private System.Windows.Forms.Button buttonGen;
		/// <summary>
		/// Required designer variable.
		/// </summary>
		private System.ComponentModel.Container components = null;
		private System.Windows.Forms.Button buttonNewCon;

		SqlConnection con ;
		public Form1()
		{
			//
			// Required for Windows Form Designer support
			//
			InitializeComponent();

			//
			// TODO: Add any constructor code after InitializeComponent call
			//
		}

		/// <summary>
		/// Clean up any resources being used.
		/// </summary>
		protected override void Dispose( bool disposing )
		{
			if( disposing )
			{
				if (components != null) 
				{
					components.Dispose();
				}
			}
			base.Dispose( disposing );
		}

		#region Windows Form Designer generated code
		/// <summary>
		/// Required method for Designer support - do not modify
		/// the contents of this method with the code editor.
		/// </summary>
		private void InitializeComponent()
		{
			System.Resources.ResourceManager resources = new System.Resources.ResourceManager(typeof(Form1));
			this.checkedCols = new System.Windows.Forms.CheckedListBox();
			this.label1 = new System.Windows.Forms.Label();
			this.textBoxConn = new System.Windows.Forms.TextBox();
			this.label2 = new System.Windows.Forms.Label();
			this.comboBox1 = new System.Windows.Forms.ComboBox();
			this.textBox2 = new System.Windows.Forms.TextBox();
			this.folderBrowserDialog1 = new System.Windows.Forms.FolderBrowserDialog();
			this.buttonWhere = new System.Windows.Forms.Button();
			this.buttonConnect = new System.Windows.Forms.Button();
			this.buttonGen = new System.Windows.Forms.Button();
			this.buttonNewCon = new System.Windows.Forms.Button();
			this.SuspendLayout();
			// 
			// checkedCols
			// 
			this.checkedCols.Location = new System.Drawing.Point(8, 16);
			this.checkedCols.Name = "checkedCols";
			this.checkedCols.Size = new System.Drawing.Size(184, 349);
			this.checkedCols.TabIndex = 0;
			// 
			// label1
			// 
			this.label1.Location = new System.Drawing.Point(200, 24);
			this.label1.Name = "label1";
			this.label1.Size = new System.Drawing.Size(96, 16);
			this.label1.TabIndex = 1;
			this.label1.Text = "Connection String";
			// 
			// textBoxConn
			// 
			this.textBoxConn.Location = new System.Drawing.Point(304, 24);
			this.textBoxConn.Name = "textBoxConn";
			this.textBoxConn.Size = new System.Drawing.Size(288, 20);
			this.textBoxConn.TabIndex = 2;
			this.textBoxConn.Text = "Server=(local);Database=PROPHARMADEMO;user=Mana;pwd=Mana;";
			// 
			// label2
			// 
			this.label2.Location = new System.Drawing.Point(208, 88);
			this.label2.Name = "label2";
			this.label2.Size = new System.Drawing.Size(72, 16);
			this.label2.TabIndex = 3;
			this.label2.Text = "Tables List";
			// 
			// comboBox1
			// 
			this.comboBox1.DropDownStyle = System.Windows.Forms.ComboBoxStyle.DropDownList;
			this.comboBox1.Location = new System.Drawing.Point(304, 88);
			this.comboBox1.Name = "comboBox1";
			this.comboBox1.Size = new System.Drawing.Size(288, 21);
			this.comboBox1.TabIndex = 4;
			this.comboBox1.SelectedIndexChanged += new System.EventHandler(this.OnTableSelectedChanged);
			// 
			// textBox2
			// 
			this.textBox2.Location = new System.Drawing.Point(304, 120);
			this.textBox2.Name = "textBox2";
			this.textBox2.ReadOnly = true;
			this.textBox2.Size = new System.Drawing.Size(248, 20);
			this.textBox2.TabIndex = 5;
			this.textBox2.Text = "c:\\";
			// 
			// buttonWhere
			// 
			this.buttonWhere.Image = ((System.Drawing.Image)(resources.GetObject("buttonWhere.Image")));
			this.buttonWhere.Location = new System.Drawing.Point(560, 120);
			this.buttonWhere.Name = "buttonWhere";
			this.buttonWhere.Size = new System.Drawing.Size(32, 24);
			this.buttonWhere.TabIndex = 6;
			this.buttonWhere.Click += new System.EventHandler(this.OnFolderSelect);
			// 
			// buttonConnect
			// 
			this.buttonConnect.Location = new System.Drawing.Point(400, 56);
			this.buttonConnect.Name = "buttonConnect";
			this.buttonConnect.Size = new System.Drawing.Size(88, 24);
			this.buttonConnect.TabIndex = 7;
			this.buttonConnect.Text = "Connect";
			this.buttonConnect.Click += new System.EventHandler(this.OnConnectClick);
			// 
			// buttonGen
			// 
			this.buttonGen.Location = new System.Drawing.Point(480, 328);
			this.buttonGen.Name = "buttonGen";
			this.buttonGen.Size = new System.Drawing.Size(120, 24);
			this.buttonGen.TabIndex = 8;
			this.buttonGen.Text = "Generate";
			this.buttonGen.Click += new System.EventHandler(this.OnGenerateClick);
			// 
			// buttonNewCon
			// 
			this.buttonNewCon.Location = new System.Drawing.Point(496, 56);
			this.buttonNewCon.Name = "buttonNewCon";
			this.buttonNewCon.Size = new System.Drawing.Size(96, 24);
			this.buttonNewCon.TabIndex = 9;
			this.buttonNewCon.Text = "New Connection";
			this.buttonNewCon.Click += new System.EventHandler(this.OnCloseConClick);
			// 
			// Form1
			// 
			this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
			this.ClientSize = new System.Drawing.Size(608, 373);
			this.Controls.Add(this.buttonNewCon);
			this.Controls.Add(this.buttonGen);
			this.Controls.Add(this.buttonConnect);
			this.Controls.Add(this.buttonWhere);
			this.Controls.Add(this.textBox2);
			this.Controls.Add(this.comboBox1);
			this.Controls.Add(this.label2);
			this.Controls.Add(this.textBoxConn);
			this.Controls.Add(this.label1);
			this.Controls.Add(this.checkedCols);
			this.Name = "Form1";
			this.Text = "Form1";
			this.ResumeLayout(false);

		}
		#endregion

		/// <summary>
		/// The main entry point for the application.
		/// </summary>
		[STAThread]
		static void Main() 
		{
			Application.Run(new Form1());
		}
		private void OnConnectClick(object sender, EventArgs e)
		{
			con = new SqlConnection(@textBoxConn.Text.Trim());
			
			try
			{
				con.Open();
			}
			catch(Exception ex)
			{
				MessageBox.Show(ex.Message);
				return;
			}
			SqlCommand cc = new SqlCommand("select * from sysobjects where xtype = 'U'",con);
			SqlDataReader rd = cc.ExecuteReader();
			
			while(rd.Read())
			{
				comboBox1.Items.Add(rd.GetSqlString(0));
				
			}
			rd.Close();
			comboBox1.SelectedIndex = 0;
			textBoxConn.Enabled = false;
			MessageBox.Show("Connected !");
		}
		private void OnCloseConClick(object sender, EventArgs e)
		{
			if (textBoxConn.Enabled == true)return;
			con.Close();
			
			textBoxConn.Enabled = true;
		}
		private void OnTableSelectedChanged(object sender, EventArgs e)
		{
			LoadFields(GetTableFromName(comboBox1.Text));
		}
		private void LoadFields(DataTable _table)
		{
			checkedCols.Items.Clear();
			for (int i =0 ; i < _table.Columns.Count ;i++ )
			{
				if(_table.Columns[i].AutoIncrement)continue;
				checkedCols.Items.Add(_table.Columns[i].ColumnName);
			}
			_gl = _table;
		}
		private DataTable GetTableFromName(string _tableName)
		{
			#region GetTableFromName
			string retour = string.Empty;
			
			SqlDataAdapter da = new SqlDataAdapter("select top 1 * from " +"[" + _tableName + "]",con);
			DataTable t = new DataTable(_tableName);
			da.FillSchema(t,SchemaType.Source);
			da.Fill(t);
			return t;
			#endregion
		}
		private string GetPlaceHolder(Type _colType, int i, bool doForUpdate)
		{
			string retour = String.Empty;
			string _stringVarPlaceHolder = "'{{{0}}}'";
			 string _otherVarPlaceHolder = "{{{0}}}";
			string _castDatetime = "(cast '{0}' as DateTime)";
			
			#region Process String base var
			if (_colType == System.Type.GetType("System.Char"))
				retour = string.Format(_stringVarPlaceHolder,i.ToString());
				
			if (_colType == System.Type.GetType("System.String"))
				retour = string.Format(_stringVarPlaceHolder,i.ToString());
				
			#endregion

			#region Process Integer Base var
			if (_colType == System.Type.GetType("System.Boolean"))
				retour = string.Format( _otherVarPlaceHolder,i.ToString());

			if (_colType == System.Type.GetType("System.DateTime"))
			{
				if (!doForUpdate)
					retour = string.Format( _otherVarPlaceHolder,i.ToString());
				else
					retour = string.Format(String.Format(_castDatetime, _otherVarPlaceHolder),i.ToString());
			}

			if (_colType == System.Type.GetType("System.Decimal"))
				retour = string.Format( _otherVarPlaceHolder,i.ToString());
				
			if (_colType == System.Type.GetType("System.Double"))
				retour = string.Format( _otherVarPlaceHolder,i.ToString());

			if (_colType == System.Type.GetType("System.Int16"))
				retour = string.Format( _otherVarPlaceHolder,i.ToString());

			if (_colType == System.Type.GetType("System.Int32"))
				retour = string.Format( _otherVarPlaceHolder,i.ToString());

			if (_colType == System.Type.GetType("System.Byte"))
				retour = string.Format( _otherVarPlaceHolder,i.ToString());

			if (_colType == System.Type.GetType("System.Int64"))
				retour = string.Format( _otherVarPlaceHolder,i.ToString());
				
			if (_colType == System.Type.GetType("System.SByte"))
				retour = string.Format( _otherVarPlaceHolder,i.ToString());

			if (_colType == System.Type.GetType("System.Byte"))
				retour = string.Format( _otherVarPlaceHolder,i.ToString());

			if (_colType == System.Type.GetType("System.Byte[]"))
				retour = string.Format( _otherVarPlaceHolder,i.ToString());

			if (_colType == System.Type.GetType("System.Single"))
				retour = string.Format( _otherVarPlaceHolder,i.ToString());
			#endregion

			return retour;
		}
		private DataTable _gl;
		private string MakeInsert(string[] cols,DataTable _table) 
		{
			#region MakeInsert
			string _sqlInsert = "insert into  [{0}] (";
			string retour = string.Empty;
			StringBuilder tmp =  new StringBuilder();
			
			tmp.Append(String.Format(_sqlInsert,comboBox1.Text));		
			#region Build Insert sql
			
			for (int i =0 ; i < cols.Length ;i++ )
			{
				if(_table.Columns[cols[i]].AutoIncrement)continue;
				tmp.Append(cols[i]);
				if ((i + 1) == cols.Length)
					tmp.Append(")");
				else
					tmp.Append(",");
			}
			tmp.Append(" values (");
			int positionIndex = 0;
			for (int i =0 ; i < cols.Length ;i++ )
			{
				
				if (_table.Columns[cols[i]].AutoIncrement)
				{
					positionIndex = i;
					continue;
				}
				Type tp = _table.Columns[cols[i]].DataType; 
				tmp.Append(GetPlaceHolder(tp,positionIndex,false));
				
				if ((i + 1) != cols.Length)
					tmp.Append(",");
				positionIndex++;
			}
			
			tmp.Append(")" + "\nGO\n");
			#endregion
			StringBuilder tmp2 = new StringBuilder();

		    SqlCommand cmd = new SqlCommand("select " + string.Join(",",cols) + " from " + _table.TableName,con);
			SqlDataReader rd = cmd.ExecuteReader();
			string[] values = new string[cols.Length];
			while(rd.Read())
			{
				for (int k = 0; k < cols.Length; k++)
				{
					values[k] = rd.GetValue(rd.GetOrdinal(cols[k])).ToString().Replace("'","''").Replace(",",".");
				}
				tmp2.Append(string.Format(tmp.ToString(),values));
			}
			rd.Close();
			retour = tmp2.ToString();	    
			return retour;
			#endregion
		}
		private void MakeCS(string streamF,string dest)
		{
			try
			{
				
				string _header = "//**********************************************************//\n"+
					             "//*                    Table:{0}                            *//\n"+
								 "//*      Généré le : {1} par {2}                           *//\n"+
								"//*********************************************************//\n\n";	          
				StreamWriter Output=new StreamWriter(dest + @"\" + _gl.TableName.Trim() + ".sql");
				Output.WriteLine(string.Format(_header,_gl.TableName,DateTime.Now,"Sodson"));
				Output.WriteLine(streamF);
				Output.Flush();
				//Ferme les flots de données
				Output.Close();
			}
			catch(Exception ex)
			{
				Console.WriteLine(ex.Message);
			}
		}
		private void OnGenerateClick(object sender, EventArgs e)
		{
			string[] cols;
			if (checkedCols.CheckedItems.Count >0)
			{
				cols = new string[checkedCols.CheckedItems.Count];
			}
			else
			{
				return;
			}

			for(int i = 0; i < cols.Length; i++)
			{
				cols[i] = checkedCols.CheckedItems[i].ToString();
			}
			MakeCS(MakeInsert(cols,_gl),textBox2.Text);
			MessageBox.Show("Generation terminé");
		}
		private void OnFolderSelect(object sender,EventArgs e)
		{
			if ( folderBrowserDialog1.ShowDialog() == DialogResult.OK)
			{
				textBox2.Text = folderBrowserDialog1.SelectedPath;
			}
		}

	}
}